top of page

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.

​

How-to Links

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

Matrix Reports

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.png

Formatting

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.

Calculations

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

Visibility

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.

Caption

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

Report Filters




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

Move Columns
Insert Columns

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

Refresh
Nightly

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.

Column Layout

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

bottom of page