1Getting started #
- Data : pick the table or view the report reads - or start from a data object's tables. Add more tables and say which columns they meet on.
- Columns : click a field to add it, or add a worked-out column such as {L.Qty} * {L.Rate} . Set each one's heading, total, format and alignment.
- Options : what users fill in before it runs - a date range, a list to pick from, a text to look for - and the column each one narrows.
- Filter & order : a fixed condition, up to three group levels with subtotals, and the sort.
- Colours : rules that colour a row or one figure, e.g. {Balance} < 0 in red.
- Preview (F8) runs the draft on real data; Publish puts it on the menu under Designed Pages > Reports and in the report launcher.
2Names in expressions #
- {A.Column} - a column of the table with alias A (filter and worked-out columns).
- {Option.Key} - what the user gave an option (filter only).
- {Key} - a report column by its key (colour rules).
- Totals are not written as SUM(...): each column has its own Total setting, used by subtotals and the grand total.
3What a report may read #
- Any table or view of this company, except those holding sign-ins, passwords, permissions, sessions, mail or SMS account details, licences and delete codes - they are never offered.
- The SQL views and functions C-Land approved for this company, under their titles. A function shows Parameters : fill each from an option of the report or a context value (today, the user, the branch, the financial year, the company code, the report option, the reporting period). A view or function changed by hand after approval is not read.
- Columns named like a password, secret or token are never offered either.
- A blank option does not narrow the rows; a branch option is added only where you want one.
4Rights and the licence #
- Designing needs Create / Alter on Studio Designer ; publishing needs Execute.
- Each published report has its own row in User Manager -> Permissions -> InvoiceNET® Studio: View runs it and puts it on the menu, Print prints it, Export exports and e-mails it.
- Reports are counted on the licence apart from pages: one comes with InvoiceNET® Studio, more are bought per report.
5Keys #
- F5 saves the draft, F8 previews it, Esc goes back to InvoiceNET® Studio.
6If something does not work #
"Data, table ...: say what fills @... of ... (an option of the report, or a context value)." / "... is taken from the option ..., which the report does not have."
- Why
- The table is an SQL function C-Land approved: each of its parameters must be filled from an option of the report or a context value (today, the user, the branch, the financial year, the company code, the report option, the reporting period).
- What to do
- Add the option in Options first, then choose it under Parameters of the table.
"Data, table ...: only an SQL function takes parameters - remove them." / "... has no parameter @..."
- Why
- The table was changed from a function to another table or view, or the approved function was changed, and parameters chosen before are left over.
- What to do
- Choose the table again; for a function, choose what fills each parameter it has now.
"Option ...: its choices cannot be read from an SQL function (...)"
- Why
- A Select option lists the distinct values of a column, and a function's rows depend on its parameters, so its columns cannot give the choices.
- What to do
- Give the option fixed choices, or take them from a column of another table (or an ERP list).
"The SQL source ... was changed in the database after it was approved, so the report does not read it ..."
- Why
- The view or function the report reads is no longer the one C-Land approved - somebody altered it by hand - so the report refuses to run.
- What to do
- Ask C-Land to apply it again from Licence Admin (Apply again); the report then runs as before.
"Requested action denied due to security restrictions. This needs the ... right on 'Studio Designer' in User Manager (InvoiceNET® Studio tab)."
- Why
- Report designs use InvoiceNET® Studio's own right: Create, Alter and Delete work on them; Execute publishes.
"This licence allows ... InvoiceNET® Studio reports ... Buy more reports ..."
- Why
- Reports are counted apart from pages: one comes with InvoiceNET® Studio, more are bought per report. Every report design counts from the moment it is made, draft or published.
- What to do
- Buy more reports in Admin > Subscription and Add-ons, or delete a report design you no longer need - its report becomes free.
"The licence server could not be reached, so a new report cannot be made just now."
- Why
- Reports are counted on the licence server; while it cannot be reached no new report design is made. Existing ones keep working.
- What to do
- Check the connection to the licence server and try again.
A table is not in the list
- Why
- Tables holding sign-ins, passwords, permissions, sessions, mail or SMS account details, licences and delete codes are never offered, and neither are columns named like a password, secret or token.
- What to do
- Report on another table or a view that holds what you need without the secrets.
"Data, table ...: ... holds sign-in, permission, licence or account details and cannot be reported on."
- Why
- The design names a table on the deny list - usually typed or copied from elsewhere.
- What to do
- Choose another table or view.
"Data, table ...: say which columns it meets the tables before it on." / "... it can only be joined to a table listed before it ..."
- Why
- A joined table needs at least one pair: a column of it that equals a column of a table above it.
- What to do
- On the Data tab press Add a column pair and pick both columns.
"Data, table ...: the alias ... is used twice." / "'Option' is kept for the report's options"
- Why
- Each table needs its own short alias; Option is reserved for reading options in the filter.
- What to do
- Give the table another alias - the columns that used the old one follow it.
"Column ...: take it from one table column, or work it out with an expression - one of the two."
- Why
- A column shows a table column or a worked-out value, never both and never neither.
- What to do
- Pick the field, or write the expression.
"There is no {...}" / "... write a table's column as {Alias.Column}."
- Why
- In a report, table columns are written with their table's alias: {M.Amount}. Options are {Option.Key}; colour rules read the report's own columns by key: {Amount}.
- What to do
- Pick the name from the list beside the expression.
"SUM cannot be used in a report - totals come from the column's Total setting."
- Why
- The database adds up the rows itself; a column's subtotal and grand total come from its Total setting.
- What to do
- Remove SUM(...) and set the column's Total to Sum.
"ROUND: the decimals must be a plain number ..." / "MIN in a report compares two or more values ..."
- Why
- In a report ROUND takes a fixed number of decimals, and MIN/MAX compare values of the same row.
- What to do
- Write ROUND({L.Amount}, 2) or MAX({A.Value}, {B.Value}).
"Option ...: choose the column it narrows, or use {Option....} in the filter."
- Why
- An option that is meant to narrow the rows must say which column - or the filter must read it.
- What to do
- Pick the column it narrows, or set How to 'Only through the filter' and use it in the filter.
"Option ...: a several-pick option narrows with 'is one of'." / "a yes/no option narrows with 'only where yes'." / "'contains' and 'starts with' need a text option."
- Why
- Each kind of option compares in its own ways.
- What to do
- Pick a 'How' from the list - it offers only the ways that fit the kind.
"Option ...: give its choices, or the list they come from." / "... its choices come from ..., which is not a column of the report's tables."
- Why
- A pick-list option needs something to pick from: fixed choices, a column's own values, or an ERP list.
- What to do
- Type the choices separated by commas, or choose where they come from.
"Option ...: write the line shown under it."
- Why
- Every option carries one plain sentence under it on the options panel (house rule).
- What to do
- Fill in 'Line under it' - say what to put there.
"Groups: at most three levels." / "a column is grouped twice" / "Sort: there is no column ..."
- Why
- Groups and sorts name report columns by key; a column removed or renamed elsewhere leaves them pointing at nothing.
- What to do
- Pick the column again on the Filter & order tab.
"Colour rule ...: ..."
- Why
- A colour rule needs a condition over the report's columns (by key) and a colour.
- What to do
- Write the condition with the field list beside it, e.g. {Balance} < 0, and choose the colour.
"The draft cannot be previewed yet - fix the ... problem(s) listed under Check." / "Not published - fix the ... problem(s) listed under Check first."
- Why
- Preview and publish run only a report with no problems; a draft can still be saved.
- What to do
- Work down the Check list; each line names the tab and the item. The tabs show how many problems each holds.
"There is already a design with the code ..."
- Why
- Pages and reports share one list of codes.
- What to do
- Choose another code.
The preview is slow or stops at the row limit
- Why
- The preview reads at most 200 rows; a report over a large table with no options still has to be read by the database.
- What to do
- Add a date range or another option that narrows the rows, and join only the tables you need.
A published report is not on anyone's menu
- Why
- The menu shows a designed report only to people who hold View on it.
- What to do
- Give View (and Print, Export as wanted) on the report's row in User Manager > Permissions > InvoiceNET® Studio. Then refresh the menu.
This is the help InvoiceNET® ERP shows beside the Report design screen. Whether you have the screen depends on the modules of your licence and the rights your administrator has given you.
See this screen for yourself
The demo is the real product, with a company of your own.