Business Central Report Builder / Designer
The Design page is where you build or make changes to Business Central reports. You Have three main layouts.
Matrix where columns can be one of Day, Week, Month, Year or Fiscal Period.
Multiple column layout selection choices like YTD, MTD, WTD see full list below.
Data Modeling with Table Area Option. Learn how to pick your own tables with the table designer. click here
Move columns in designer. click here
Format decimals and dates. click here
Column Layouts. Learn how to add different columns for Year to date, Month to date etc. click here
Calculations. Learn how to use formulas to create calculated columns. click here
Duplicate Reports. click here
Copy Reports between Companies. click here
Merge Reports. Learn how to combine two reports that share many of the same fields. click here
A matrix view shows columns are monthly, weekly, bi-monthly, semi-monthly or Fiscal. date periods. The Column Layout field, allows you to select how the report is displayed. Up to 16 periods are displayed. For step-by-step instructions click here
Beginning Balance Column
This option goes with the Date Period option. When selected, a column showing a cumulative sum is automatically added as the first column. Areas where this is useful are accounts payable, receivable and general ledger.
Formatting allows you to tailor how decimals and dates are displayed. For detailed instructions, click here
NoSign Option allows you to show the absolute value of a number.
ReverseSign This is often used on revenue or invoice amount reporting where the amounts are stored with a negative sign. Use this option to reverse the sign. All credit amounts will show a positive number.
Decimals option allows you to display or remove decimals from the display. Note that all calculations are performed with the original amount.
Percent option displays a % sign. It assumes that the value is a ratio and simply multiplies it with 100
UnitAmountDecimalPlaces Use this option to match the decimal precision to the G/L unit amount decimal places setup.
Date Day There are several options that format the date to show only the Day portion of the date
Date Month The month options display month as a number or text (first 3 characters)
Date Month Year Display the month and year together, as a number or text.
Date Year Display the year only as a number.
You can add calculated columns and do calculations using an Excel like syntax. The following calculations are possible. Add, Subtract. Multiply, Divide. For detailed instructions click here
Below are examples of how to build calculation
D - C = Subtract column D from column C
(D - C)/E = Subtract columns D from column C, then divide by E
(D * C) / E - Multiply D and C then divide by E
D / 10 - Divide value from D by 10
Date B - A = The Date keyword allows you to calculate the difference in days for date columns
Subtotals on the column. Currently only the first two columns can be subtotaled
Use the visibility control to hide a column. If you have a column that was selected only to do a calculation, use this setting to hide the column.
This function allows you to change caption on any column. For example, if you select the Resource Quantity for a Jobs report, you may want to change the caption to say Hours. This option also comes in handy for calculated columns. For instructions click here
Dynamic Date Filter
When you design a report, filters are an important way to get the final result and remove un wanted rows. These filters are stored and presented as defaults to users when they run a report. Keep in mind that each reporting area has a list of available filters but some filters below are available for all areas.
Date Filter - Setting a date filter is important to reduce the amount of data retrieved and ultimately affects performance.
Dimension Filters - All dimension filters are applied to the ledger entries.
Auto Date Filter - Use the auto date filter option to have the system default a date filter at runtime.
For step-by-step instructions click here
Click the assist edit button to select the column. Next click one of the buttons described below.
Move Left - This will swap position with the field before the selected column
Move Right = This will swap position with the field after the selected column
Insert Before - This will add a blank column before the selected field
Insert After - This option will add a blank after the selected field
For detailed instructions click here
The refresh option allows you to schedule reports to refresh data. This available options are
Always - Each time you run the report the latest data is retrieved and the report is presented
Ask - Allows you to re-open a report based on the last time it was run. Use this option if you closed the screen by mistake and do not need to refresh data.
Nightly- This option adds the report to the job scheduler. The report is created nightly and is best suited for scenarios where it takes a long time to generate the report and your analysis is not dependent of current day's data.
On the options page, you will find the ability to select your column layout. To select your column layout, set the Date period to custom. Select up to four columns. For details on what's available and how to use column layouts click here