/onestopreporting/help/report-designer/creating-reports section 2024-07-04T17:39:28+02:00 # Designing reports /onestopreporting/help/report-designer/creating-reports/report-building-options page 2024-07-04T17:39:28+02:00 # Report building options Through **Report Designer**, there are different ways of building a report: - Build a report from scratch in the **Report Designer** by [creating a new report template](./../../../live-reporting/create-report-template) from the **Reporting** module in the **OneStop Reporting** portal. - [Edit a report template](./../../../live-reporting/edit-report-template) by duplicating an existing report template from the **Reporting** module in the OneStop Reporting portal. - Upload a report template that is locally stored to the OneStop Reporting portal by clicking **Upload** in the **Reporting** module. /onestopreporting/help/report-designer/creating-reports/modules-from-the-erp-system page 2024-08-07T11:22:23+03:00 # Modules from the ERP system The modules in your connected system can be found in the Report Designer menu when you are in Design mode. This is indicated by blue cubes, as shown in the figure below. The system modules represent the top level and contain dimensions, attributes, and measured values that can be dragged into the report definition.  *Figure: ERP modules in the Report Designer menu* /onestopreporting/help/report-designer/creating-reports/data-items page 2026-01-16T14:27:01+01:00 # Data items Data items are located on the **Design** tab in the **Report Designer** area of the screen. Data items are considered subgroups and contain information about the specific module. When designing a report template, you will drag different types of data items into the Excel worksheet, and these fields form the basis for the data presented in the report. *Figure: Data items in the Report Designer* The three types of data items are:  Dimensions - *Dimensions* represent a table in the connected system. If you expand the dimension by clicking the button to the left of the dimension name, all attributes belonging to the dimension are displayed. All dimensions can be used as filters, for grouping, or pulled out as fields to be displayed in the report.  Attributes - *Attributes* represent a descriptive field in the transaction table for the connected system. All attributes can be used as filters, for grouping, or pulled out as fields to be displayed in the report. ####  Measures {#measures} - *Measures* represent a value field in the transaction table for the connected system. Measurement values can be used as a filter or pulled out as fields to be displayed in the report. > [!NOTE] > The different types of data items will trigger certain functions, such as grouping or filters found in the Layout Editing window. /onestopreporting/help/report-designer/creating-reports/period-functions-2 page 2024-08-02T13:54:26+02:00 # Period Functions Period functions are predefined period functions, such as year to date, this period last year etc., that easily allows you to compare data from different periods. You find the accessible period functions under **Period Functions** in the **Report Designer** menu. To insert period functions, which are found under **Period functions** in the **Report Designer** menu, you have two options: - Method 1: Drag the period function into a cell of a report. Dragging a period function into a report will automatically create a function for the column. - Method 2: Drag the function into the **Layout Editor** window under the **Filters** tab. Both methods are shown in the figures below. **Method 1:**  **Method 2:**  Currently, all functions are monthly based and allow you to control the group's displayed data by month. You can change period function by clicking the drop-down list on the **Filters** tab. *Figure: Period function options on the Filters tab* /onestopreporting/help/report-designer/creating-reports/report-parameters section 2024-08-02T13:54:26+02:00 # Report parameters The parameters created in the report are displayed in the **Report Parameters** pane. These parameters can be dragged into the report definition so that the reader can identify which parameters were used to generate the report. It is important to note that the **Report Parameters** pane is different from the **Report Parameters Window** which can be accessed through the **OneStop Reporting** ribbon.  /onestopreporting/help/report-designer/creating-reports/report-parameters/report-parameters-wizard section 2024-07-04T17:39:28+02:00 # Report Parameters Wizard Report parameters allow designers to create a dynamic report so that the same report template can be used for multiple reports with minimal adjustments. For instance, a departmental budget report can be created for multiple departments by changing the *Department* parameter at the time of execution. /onestopreporting/help/report-designer/creating-reports/report-parameters/report-parameters-wizard/manage-parameters page 2024-08-07T11:22:23+03:00 # Report Parameters button ## Manage parameters via the Report Parameters button Report parameters can be managed in two different ways. The first method is to access the **Report Parameters** button in the **OneStop Reporting** Excel ribbon. The **Report Parameters** window allows you to create, modify, and delete parameters.  To create a parameter: - Drag a data item from the **Designer** menu into the **Report Parameter** window. To modify a parameter; - Click the **Pencil** button . To delete a parameter; - Click the red x button  . To reorder parameters; - Use the blue arrow button , which in turn affects the display order of parameters in the **Run** tab in the **Report Designer***.* This also affects the display order of parameters when executing the report from the **Reporting** module in the **OneStop Reporting** portal. Whether you are creating or modifying a parameter, you will access the **Report Parameter Wizard**. The wizard is composed of three pages, which are explained below. On the first page, specify the name of the parameter and the prompt text that should be displayed when executing the report from the **Run** tab in the **Report Designer**. This text will also be displayed for the report in the **Reporting** module in the **OSR Portal**. *Figure: Report Parameter Wizard: Name and Prompt text* **!**The parameter name cannot be changed after the initial creation of the parameter. The next page allows you to select the parameter prompt style. By default, **Lookup Edit** is selected, but you can modify this to fit the requirements of the report. *Figure: Report Parameter Wizard: Prompt style* On the last page of the **Report Parameter Wizard**, you can modify these options: *Figure: Report Parameter Wizard: Other options* | Option | Function | |----|----| | Default value | The value or values entered into the text box or those selected through the Lookup button will automatically be inserted for the parameter when the Run tab is accessed. However, the value can be changed at runtime. | | Lookup filter | Report Designer users can manage what the data users are able to view in the Lookup window for the particular parameter. For example, this feature may be useful when reports are supposed to be run only for the last two periods. Keep in mind that this feature does not override restrictions set in the Administration settings in the OneStop Reporting portal. If a value is excluded in the Administration settings for the user, it will never be displayed in the Lookup window for the particular user. | | Hidden | Enabling this feature will hide the parameter in the Run tab. This feature is useful when the parameter is required for testing, but in actual deployment, the parameter will always be the same value. **NOTE**: If the Hidden feature is enabled, users are required to choose a default value. | | Allow empty (=All) | If the checkbox is selected, the report can be executed even if this parameter is left empty. If the parameter is left empty, the report will contain all values. | | Allow multi-selection | If the checkbox is selected, multiple values can be selected in the parameter prompt (that is, multiple projects vs. one project) | | Create sheet per value | If this checkbox is selected, Report Designer will create a new Excel sheet in the same workbook for each of the unique values chosen for the parameter (for example, selection of multiple departments will result in a sheet for each selected department). Please note that in order for this to work, you must have the value, for example department, on the Sheet filter. | | Sheet name field | If the Create sheet per value is active, users can specify the name for the new sheets by using the Lookup option. | /onestopreporting/help/report-designer/creating-reports/report-parameters/report-parameters-wizard/layout-editor page 2025-05-21T14:33:14+02:00 # Report Parameters Layout Editor ## Managing parameters via the Layout Editor The second method for managing parameters is through the **Layout Editor.** When filters are created on sheet, column, or row level, you can specify parameters by using the **Lookup ** button and accessing the **Parameters** tab. You can create a parameter by assigning a parameter to the filter or clicking **Create a new Parameter** which will open the **Report Parameter Wizard**.  > [!NOTE] > Existing parameters can be found by selecting the **Report Parameters** button in the **OneStop Reporting** Excel ribbon or by selecting the **Report Parameters** module in the **Designer** menu. /onestopreporting/help/report-designer/creating-reports/layout-editor section 2024-08-07T11:22:23+03:00 # Layout Editor The **Layout Editor** is primarily used for managing filters, grouping/sorting and functions for cell, row, column, and sheet level business rules, as well as [Storage rules](./../../../budgeting/create-budget-template-in-report-designer). Click the **Layout Editor** button in the **OneStop Reporting** Excel ribbon to open the **Layout Editor.** The various functions in the **Layout Editor** are explained in the next sections.  *Figure: Layout Editor - Report Designer* /onestopreporting/help/report-designer/creating-reports/layout-editor/filters section 2024-08-02T13:54:26+02:00 # Filters Creating and managing filters is a key feature in designing a report definition. You can apply filters to groups created at sheet, row, column, or cell level. To create a filter; - Drag an item from the **Report Designer** menu into the appropriate area on the **Filters** tab in the **Layout Editor** (area below the text *Drag fields into area below to create filters*). An example is shown in the figure below. *Figure: Apply filters* You can access existing filters by clicking on the boxes in the respective row or column, as shown in the figure below. From the **Layout Editor** window, you can modify and delete filters. *Figure: Accessing filters in the Layout Editor* The next sections cover the basics of configuring filters: /onestopreporting/help/report-designer/creating-reports/layout-editor/filters/dimension-lookup page 2026-01-16T14:27:01+01:00 # Dimension Lookup Filters can further be configured by using the *dimension lookup* function: 1. Click the **D****imension lookup** button  to display the attributes available to filter for the specific dimension.  2. In the **Lookup** dialog box, you can specify the dimension to remain *static* or *dynamic* depending on the report requirements. To create a static filter, select the **List** tab in the **Lookup** window. Select the desired account, department, entity, etc. and click **OK**. **Use ranges** checkbox: If this checkbox is selected and multiple data items are selected, the selected data are displayed as a range in the text box at the bottom of the window. If this checkbox is not selected, all selected data are displayed individually in the bottom text box.  To manually create a parameter, enter {@Dimension_Name}. For *Period* dimensions, you can specify the value *current period +/-* for single period parameters. This allows **Report Designer** to select the correct period based on the computer date; therefore, report parameters do not need to be updated manually prior to running a scheduled report. /onestopreporting/help/report-designer/creating-reports/layout-editor/filters/operators page 2026-01-16T14:27:01+01:00 # Operators Using operators is one way you can create a customized filter. Similar to operators in mathematics or programming languages, the operators dictate an action or procedure taken by the **Report Designer**. *Figure: Filter operators in Report Designer* In the first drop-down box, these two options are available: | **Option** | **Function** | |---|---| | *blank* | Will include all data inside the specified filter. | | **Not** | Will include all data outside the specified filter. | If there are multiple filters in the same group, these options will also be available in the first drop-down box: | **Option** | **Function** | |---|---| | **And** | Will filter the first condition AND the second condition. | | **Or** | Will filter the first condition OR the second condition. | | **And Not** | Will filter the first condition AND NOT the second condition. | | **Or Not** | Will filter the first condition OR NOT the second condition. | In the second drop-down box, these filter operators are available: | **Option** | **Function** | |---|---| | **In** | *dimension* is In... (the selected parameters) | | **\<\>** | *dimension* is Not equal to... (the selected parameters) | | **\<** | *dimension* is Less than... (for numerical values) | | **\>** | *dimension* is Greater than... (for numerical values) | | **\<=** | *dimension* is Less than or equal to... (for numerical values) | | **\>=** | *dimension* is Greater than or equal to... (for numerical values) | | **Begins with** | *dimension* Begins with... (for any phrase contained in the dimension) | | **Ends with** | *dimension* Ends with... (for any phrase contained in the dimension) | | **Contains** | *dimension* Contains... (any part of the phrase contained in the dimension) | | **Like** | **OSR Report Designer** will retrieve any data using "LIKE ..." in SQL.\* | \*The **Like** operator is for advanced users and requires knowledge of SQL. /onestopreporting/help/report-designer/creating-reports/layout-editor/grouping-and-sorting section 2026-01-16T14:27:01+01:00 # Grouping and sorting Access grouping and sorting by clicking the **Grouping/Sorting** tab in the **Layout Editor** window. > [!TIP] > The *Grouping/Sorting* tab is not available if the report level is selected. In addition, grouping will not occur if the *Expanding* checkbox is deselected. *Figure: Grouping and sorting options* | | **Icon** | **Function** | |---|---|---| | **1** |  | Increase or decrease the precedence of the data item in the sorting order. | | **2** |  | Select the display option of the row, column, or cell. The available options are *None*, *Group*, *Sum*, *Min*, *Max*, and *Count*. | | **3** |  | If this icon is displayed, the sorting is in ascending order for the group. Click on this icon to cycle through the sorting options. | | **3** |  | If this icon is displayed, the sorting will not exist for the group. Click on this icon to cycle through the sorting options. | | **3** |  | If this icon is displayed, the sorting is in descending order for the group. Click on this icon to cycle through the sorting options. | | **4** |  | Delete the data item from the **Grouping/Sorting** tab. | /onestopreporting/help/report-designer/creating-reports/layout-editor/grouping-and-sorting/grouping page 2024-08-02T13:54:26+02:00 # Grouping **Report Designer** will automatically display data items on row or column level if a *dimension* or *light dimension* is dragged into a cell and a group already exists on the row or column level. An example is shown in the figure below.  In essence, grouping the *account* dimension displays results at the account level even if there are detailed sub-accounts. If the account data field is not grouped, the report will display all sub-accounts at the time of execution. For more details on grouping, see [Expanding Groups.](./../../expanding-groups) /onestopreporting/help/report-designer/creating-reports/layout-editor/grouping-and-sorting/sorting page 2024-08-02T13:54:26+02:00 # Sorting  On column and row level, dimensions can be reordered to create a hierarchy of sorting by clicking the arrow buttons next to the dimension name. For example, in the settings shown in the illustration above, the generated report will be presented in numerical order of account number (as shown in the illustration on the left below). However, if the *Description* dimension is moved to take precedence over *Account*, the generated report will be presented in alphabetical order of the account description (as shown in the illustration on the right below).  In addition, **Report Designer** will automatically display data items in the **Layout Editor** on cell level if a *measure* has been dragged into a cell and a group already exists on the row or column level. The same options are available for cell level grouping and sorting. The illustration below shows an example.  /onestopreporting/help/report-designer/creating-reports/layout-editor/period-aggregation page 2024-07-04T17:39:28+02:00 # Period aggregation From the **Period Aggregation** tab, you have the option to aggregate data. Aggregation of data involves displaying *total* financial figures for each month, instead of displaying financial figures for *one* month. /onestopreporting/help/report-designer/creating-reports/layout-editor/expanding-groups section 2026-01-16T14:27:01+01:00 # Expanding groups *Expanding groups* are an essential part of the **Report Designer**. It involves, for example, creating a list of each account rather than grouping the accounts together and displaying a total for all accounts. ## Create expanding groups To create expanding groups; 1. In the **Layout Editor** window, select the **Expanding** checkbox to create expanding groups in the specified row or column for data items that have been set to **Group** in the **Grouping/Sorting** tab. > [!TIP] > The quickest way to create an expanding group is by dragging a data item, such as *Sales Person* into the report definition and selecting *Create a new selection group on the Row* or *Create a new selection group on the Column*. By default, *Report Designer* creates an expanding group and shows the group in the *Grouping/Sorting* tab in the *Layout Editor* window.  *Figure: The process of creating expanding groups in the Layout Editor* > [!TIP] > If the row/column is an expanding group, it will be indicated by a small green plus icon  in the box of the respective row or column. If the report is generated and an expanding group is *not* created, the report will result in consolidation of all data in the account, as shown in the figure below. *Figure: Report with no expanding group* On the other hand, if an expanding group is created, the details of the account will be given, as shown in the figure below. *Figure: Report with an expanding group* /onestopreporting/help/report-designer/creating-reports/layout-editor/expanding-groups/expanding-groups-in-expanding-groups page 2024-08-07T11:22:23+03:00 # Expanding Groups in an Expanding Group To create more flexible reporting, you can create expanding groups inside other expanding groups to create *nested* expanding groups. An expanding group inside another expanding group can be thought of as a hierarchy. Imagine there are two data sets, one alphabetical and the other numerical (A, B, C... & 1, 2, 3...), and one is an attribute of the other. ## Create a nested expanding group 1. Drag the relevant data items into the report definition. 2. In the **OneStop Reportin**g dialog box, click **Create a new selection group on the Row**. **NOTE:** The two data items must be placed in consecutive rows.  By default, **Report Designer** creates two separate expanding groups. However, you want to create a nested expanding group. 3. In the **Layout Editor** window, drag the expanding group box so that it extends over multiple rows.  The reports for separate expanding groups (left) and a nested expanding group (right) are shown in the figure below.  /onestopreporting/help/report-designer/creating-reports/layout-editor/advanced-period-formulas page 2026-01-16T14:27:01+01:00 # Advanced period formulas In **Report Designer,** you have the possibility to use predefined period functions during the report building. In cases where these features do not meet your needs, you can use advanced period formulas to determine the year, period(s) or day(s) in the report. This guide explains how to use such formulas in Report Designer. ## Advanced period formulas When you work on advanced period formulas, you often have to relate to two variables in each formula. Example: {PeriodCalc.GetPeriod(Parameter).AddYears(X).WholeYear} *Parameter* is the period parameter created in the report, and *X* is the variable that determines the number of years, months/periods and days that the formula should add or subtract. > [!NOTE] > In some cases, there will be a third variable, represented by Y in the example with a rolling interval further down in this document. > [!IMPORTANT] > Advanced period functions are calculations based on the parameter value that is selected when the report is run. Due to this, users can only select a single period to run the report for when advanced and the predefined period functions are used. Figure 1 shows where to find and replace the parameter for the period. 1. In the **Layout Editor**, click the group to enter the expansion. 2. Click the **Lookup** button to do a lookup. In the **Lookup** dialog, you can see the name of the parameter (marked in blue). 3. Copy the text within {}. 4. Paste this in to replace the text *Parameter* in the formulas described in this document.   *Figure 1 Finding and replacing the parameter for the period* ## Period formulas based on a monthly ID (e.g. Period=201801 for January 2018) {#period-formulas-based-on-a-monthly-id-e.g.-period201801-for-january-2018} The table below shows various period formulas where you can replace (Parameter) with (PostingPeriods)^1^ and replace the variable X with the number of years/months you want to add or subtract. The formula element *Extend* determines how many months to add to an interval. More examples of built-in period functions will follow in the section *Overview of the built-in period functions in Report Designer* below. | Period function | Period formula | |----|----| | Whole year in year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X).WholeYear}'``` | | First period in year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X).YearStart}'``` | | Last period in year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X).YearEnd}'``` | | Q1 of year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X).YearStart.Extend(2)}'``` | | Q2-Q4 in year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X).YearStart.SetIndex(X).Extend(2)}'``` | | Remaining periods in year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X).Add(1)}':'{PeriodCalc.GetPeriod(Parameter).AddYears(X).YearEnd}'``` | | This period year X | ```'{PeriodCalc.GetPeriod(Parameter).AddYears(X)}'``` | | Current month | ```'{PeriodCalc.CurrentPeriod}'``` | | Rolling from X to Y | ```'{PeriodCalc.GetPeriod(Parameter).Add(X).Extend(Y)}'``` | ^1^The term used varies between ERP systems. Look for the period parameter, such as Periods, Posting Periods, Accounting Periods, etc. in the **Layout Editor**. (See Figure 1 above for where to look.) ## Example of formula for rolling interval from X to Y If you want to show 2 years of rolling history, then you must first subtract 1 month (from the period parameter you select when you run the report), and then extend (Extend) with 23 months. Here, X gets the value -1 and Y gets the value -23. The formula would like this: {PeriodCalc.GetPeriod(PostingPeriods).Add(-1).Extend(-23)} ## Period Formulas based on date ID (format dd.mm.yyyy -- e.g. 01.01.2019) {#period-formulas-based-on-date-id-format-dd.mm.yyyy-e.g.-01.01.2019} The table below shows various period formulas where you can replace (DateParameter) with a date parameter^2^ and replace the variable X with years/months you want to add or subtract. You find the parameter in the same way as in the example above. The only difference is that you have a period dimension based on day level, for example, posted date. | 0 | 1 | |------------------|----------------------------------------------------| | Date parameter | Period formula | | Year | ```'{DateParameter.AddYears(X).SqlDate}'``` | | Months | ```'{DateParameter.AddMonths(X).SqlDate}'``` | | Days | ```'{DateParameter.AddDays(X).SqlDate}'``` | | Day Y, in year X | ```'{DateParameter.AddYears(X).AddDays(Y).SqlDate}'``` | > **NOTE:** When the period formula is based on a date, as opposed to a period (month), you must include ' before and after the formula: ```'{DateParameter.AddYears(X).SqlDate}'``` ^2^The term used varies between ERP systems. Look for date parameters such as Due Date, Transaction Date, etc. ## Example of formula for day Y in year X In this example, you want to find yesterday last year. You can do this by replacing -1 Y with -1. The formula will then subtract 1 year minus 1 day for the date you set when you run the report. ```'{DateParameter.AddYears(-1).AddDays(-1).SqlDate}'``` '{DateParameter.AddYears(-1).AddDays(-1).SqlDate}' This is a period formula based on date. It calculates date based on current date/today's date ## Example of formula with Due Date In this example, you want to show invoices that are due in the next 30 days, based on a date parameter, @DueDate. You can do this by using this formula: ```'{@DueDate.AddDays(30).SqlDate}'``` ## Overview of the built-in period functions in Report Designer | Name | Display Name | Expression | |----|----|----| | ALL2Yago | All Year 2 Years Ago | ```'{PeriodCalc.GetPeriod([]).AddYears(-2).WholeYear}'``` | | ALL2Yfor | All Year 2 Years Forward | ```'{PeriodCalc.GetPeriod([]).AddYears(2).WholeYear}'``` | | All3YAgo | All Year 3 Years Ago | ```'{PeriodCalc.GetPeriod([]).AddYears(-3).WholeYear}'``` | | All3YFor | All Year 3 Years Forward | ```'{PeriodCalc.GetPeriod([]).AddYears(3).WholeYear}'``` | | AllTimeTD | All Time to Date | ```'{PeriodCalc.GetPeriod([0])}':'{PeriodCalc.GetPeriod([])}'``` | | FPLY | First Period Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).YearStart}'``` | | FPNY | First Period Next Year | ```'{PeriodCalc.GetPeriod([]).AddYears(1).YearStart}'``` | | FPTY | First Period This Year | ```'{PeriodCalc.GetPeriod([]).YearStart}'``` | | LPLY | Last Period Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd}'``` | | LPNY | Last Period Next Year | ```'{PeriodCalc.GetPeriod([]).AddYears(1).YearEnd}'``` | | LPTY | Last Period This Year | ```'{PeriodCalc.GetPeriod([]).YearEnd}'``` | | LY | Last Year (all) | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).WholeYear}'``` | | LYTD | Last year to date | ```'{PeriodCalc.LastYearToDate([])}'``` | | NY | Next Year (all) | ```'{PeriodCalc.GetPeriod([]).AddYears(1).WholeYear}'``` | | Q1LY | Q1 Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).YearStart.Extend(2)}'``` | | Q1TY | Q1 This Year | ```'{PeriodCalc.GetPeriod([]).YearStart.Extend(2)}'``` | | Q2LY | Q2 Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).SetIndex(4).Extend(2)}'``` | | Q2TY | Q2 This Year | ```'{PeriodCalc.GetPeriod([]).SetIndex(4).Extend(2)}'``` | | Q3LY | Q3 Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).SetIndex(7).Extend(2)}'``` | | Q3TY | Q3 This Year | ```'{PeriodCalc.GetPeriod([]).SetIndex(7).Extend(2)}'``` | | Q4LY | Q4 Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).SetIndex(10)}':'{PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd}'``` | | Q4TY | Q4 This Year | ```'{PeriodCalc.GetPeriod([]).SetIndex(10)}':'{PeriodCalc.GetPeriod([]).YearEnd}'``` | | RB12M | 12 Month Rolling (-1 -> -12) | ```'{PeriodCalc.GetPeriod([]).Add(-1).Extend(-11)}'``` | | RBC12M | 12Month Rolling Current (0 > -12) | ```'{PeriodCalc.GetPeriod([]).Add(0).Extend(-11)}'``` | | RF12M | 12 Months Rolling (+1 -> +12) | ```'{PeriodCalc.GetPeriod([]).Add(1).Extend(11)}'``` | | RF3M1 | 3 Months Rolling (+1 -> +3) | ```'{PeriodCalc.GetPeriod([]).Add(1).Extend(2)}'``` | | RF3M2 | 3 Months Rolling (+4 -> +6) | ```'{PeriodCalc.GetPeriod([]).Add(4).Extend(2)}'``` | | RF3M3 | 3 Months Rolling (+7 -> +9) | ```'{PeriodCalc.GetPeriod([]).Add(7).Extend(2)}'``` | | RF3M4 | 3 Months Rolling (+10 -> +12) | ```'{PeriodCalc.GetPeriod([]).Add(10).Extend(2)}'``` | | RFC12M | 12 Months Rolling Current (0 > 12) | ```'{PeriodCalc.GetPeriod([]).Add(0).Extend(11)}'``` | | RPLY | Remaining Periods Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1).Add(1)}':'{PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd}'``` | | RPTY | Remaining Periods This Year | ```'{PeriodCalc.GetPeriod([]).Add(1)}':'{PeriodCalc.GetPeriod([]).YearEnd}'``` | | TPLY | This Period Last Year | ```'{PeriodCalc.GetPeriod([]).AddYears(-1)}'``` | | TPNY | This Period Next Year | ```'{PeriodCalc.GetPeriod([]).AddYears(1)}'``` | | TPTY | This Period This Year | ```'{PeriodCalc.GetPeriod([])}'``` | | TYALL | This Year (all) | ```'{PeriodCalc.GetPeriod([]).WholeYear}'``` | | YTD | Year to Date | ```'{PeriodCalc.FiscYearToDate([])}'``` | ## Additional Period Syntax The following syntax can be applied within the period filter. *DateParameter = name of period parameter* | Expression Syntax | Example | |----|----| | AddDays(n) | 1. ```'{DateParameter.AddDays(30).SqlDate}'``` 2. ```'{DateParameter.YearEnd.AddDays(1).SqlDate}':'2020-12-31 AddMonths(n)``` | | AddYears(n) | ```'{DateParameter.AddYears(-1).SqlDate}'``` | | WeekStart | ```'{PeriodCalc.Now.WeekStart.SqlDate}':'{PeriodCalc.Now.SqlDate}'``` | | MonthEnd | ```'{DateParameter.MonthEnd.SqlDate}'``` | | MonthStart | ```'{DateParameter.MonthStart.SqlDate}'``` | | YearEnd | ```'{DateParameter.YearEnd.SqlDate}'``` | | YearStart | ```'{DateParameter.YearStart.SqlDate}'``` | The functions may be combined: ```'{DateParameter.YearEnd.AddDays(1).SqlDate}':'2020-12-31``` /onestopreporting/help/report-designer/creating-reports/property-hierarchy page 2024-08-02T13:54:26+02:00 # Property hierarchy Before designing a report with the **Report Designer**, it is important to understand how the **Report Designer** applies dimensions so that reports can be created efficiently. **Report Designer** allows you to insert filters at *four* levels: - **Sheet:** Applies to the entire sheet - ***Row*:** Applies to all cells on the applicable row - ***Column*:** Applies to all cells in the applicable column - ***Cell*:** Applies only to the applicable cell A typical example would be a financial statement with the following location for filters: - *Department/company parameters (filters)*: **Sheet level** - *Account selections*: **Row level** - *Period selections (Current Period, Year-to-Date*, etc.): **Column level** - *Sum and variance formulas*: **Cell level** You can see business rules in the **Layout Editor** window by clicking on the boxes displayed for the sheet level and the row or column level as shown in the figure. To see the cell level business rule, click in the particular cell in the Excel worksheet.  /onestopreporting/help/report-designer/creating-reports/copy-and-paste page 2026-01-16T14:27:01+01:00 # Copy and paste In a report definition created through the **Report Designer**, copying and pasting can be different than in a regular workbook in Excel. This is because filters are applied not only on the cell level, but also on the report, row, and column level. This section will explain the rules for copy and paste for various filter levels. #### Cell level The copy and paste rules in **Report Designer** are the same as for a regular workbook. By default, formulas and groupings are preserved. #### Row/Column level The copy and paste rules in **Report Designer** are the same as for a regular workbook when working in a single worksheet. By default formulas and groupings are preserved. > [!IMPORTANT] > If rows/columns are pasted into a different tab than the original source, groupings will not be preserved. > [!TIP] > If you want to copy and paste an existing group, you must copy the whole row/column, not just the cells where the information is. #### Report level The copy and paste rules in **Report Designer** for an entirely new tab require you to use a special copy menu. By using this menu, all formulas, groupings, and parameters are preserved. 1. Right-click the desired tab to copy. 2. Select **OneStop Reporting** \> **Copy**. Right-click on a blank tab, then select **OneStop Reporting***\>***Paste as new**.  > [!CAUTION] > Pasting the tab will overwrite all information on the tab that was selected when taking the third step. It is very important to create a blank worksheet so that no data are lost. /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis section 2024-08-02T13:54:26+02:00 # Expressions and KPIs In **Report Designer**, you can create Expressions and KPIs to save time when you are building your reports. /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/kpis section 2026-01-16T14:27:01+01:00 # KPIs *KPIs* can be created and managed through the **Report Designer** menu under **KPIs**.  A KPI is a calculation/key figure that you can reuse across multiple reports. KPIs are used in the same way as measures (amount, quantity). Instead of dragging an amount or quantity into the report, you drag out the complete KPI. KPIs can be based on: - Expressions (simple or complex selection of dimensions) - Measures (amount, quantity) #### KPIs based on expressions - When a KPI is based on an expression, it must include information about which measure (amount, quantity) it will have an effect on. Example: GrossProfit is Sales -- Costs. When creating this KPI, it will be significant whether you choose the actual amount or budget amount as your measure. - A KPI has a formula. The formula is based on expressions and normal mathematical operators (+, -, /, \*) and constants. - It is also possible to add period functions to a KPI. #### KPIs based on measures - For KPIs based on measures, you must select a Factset (module) that the measures you want to use are included in. For example, if you want to use the *Net amount* from General Ledger, you must select the General Ledger factset. An example of a measure-based KPI is *average price*, which is the amount divided by quantity.  From the **KPI** dialog, you have access to the following options to manage the KPIs: | **Button** | **Function** | |---|---| |  | Click to create a new KPI group. In the **KPI Groups** dialog, make sure to enter a KPI group name before starting to select values for the KPI. You may also enter a description for the KPI. | |  | Click to open the **KPI Groups** dialog and edit the currently selected KPI group. | |  | Click to delete the selected KPI group. | |  | Click to create a copy of the selected KPI group. The newly created expression group may be modified with the **Change...** function. | |  | Click to open a network browser window and import a KPI group. Valid files are other OSR expressions with the \*.xml format. | |  | Click to open a network browser window and export a KPI group to the location. The newly exported expression group will be in the \*.xml format. | /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/kpis/use-kpi-in-reports page 2024-08-02T13:54:26+02:00 # Use KPIs in reports You can use KPIs in the same manner as any other measure; instead of dragging an amount or quantity into the report, you drag out the complete KPI. To use a KPI in a report; 1. In the **Report Designer** menu, click **KPI**. 2. Click the KPI you want to use, and drag it into your report.  /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis section 2024-07-04T17:39:28+02:00 # Examples: Create Expressions and KPIs To illustrate the creation of expressions and KPIs, we will look at some examples. First, we will create two expressions called *INCOME* and *COGS*. We will then create a KPI called GROSS MARGIN % which uses these two expressions to show the gross margin in %. Finally, we have an example where we create a KPI based on measures. /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis/combine-expressions-income-and-cogs-in-the-kpi-grossmargin page 2024-08-07T11:22:23+03:00 # Create KPI based on Expressions In this example, we create a KPI that shows gross margin in %. This KPI will be based on the expressions *INCOME* and *COGS*. 1. In the **Report Designer** menu, select **KPIs** and then **Manage KPIs**.  2. In the dialog that opens, click **New**. 3. In the next dialog, enter a KPI group name (in the example *Financial KPIs*). You can also add a description. 4. Click **Add** and enter a name of the KPI; *GROSS MARGIN %*. You may also enter a comment.  5. This KPI will be based on expressions, so make sure that the **Based on Expressions** option is selected. Next, you must indicate which measure the expressions that will be included in the KPI are based on. 6. Click the **Edit measure** button in the *Measure* box. 7. Find and select the measure that the expressions *INCOME* and *COGS* are based on. In our case, this is **Net Amount** (under *General Ledger Transactions)*. 8. Click **OK** to confirm.  9. Click the **Edit** button in the **Formula** field.  The **KPI Editor** opens: | | **Name** | |-----|--------------------| | 1 | Expressions | | 2 | **Formula** area | | 3 | **Operators** area | The KPI *GROSS MARGIN %* will consist of the expression *INCOME* minus the expression *COGS* divided by *INCOME* to get the result in percent. The formula will look as shown in the figure below:  10. To build the formula, perform the following steps: a. In the **Operators** section, click **(** (left parenthesis) twice. b. Drag the expression *INCOME* into the **Formula** area*.* c. In the **Operators** area, click **\*** (multiply by) and type -1. This is to convert the income amounts that are negative in the ERP system into positives so that the entities that will be calculated are both positive in order to give the correct result. d. In the **Operators** area, click **)** (right parenthesis). e. In the **Operators** area, click **--** (minus). f. Drag the expression *COGS* into the **Formula** area*.* g. In the **Operators** area, click **)** (right parenthesis). h. In the operation section, click **/** (divide by). i. Drag the expression *INCOME* into the **Formula** area. j. In the **Operators** area, click **\*** (multiply by) and type -1. (Again to convert the income amounts into positives.) k. In the **Operator** section, click **)** (right parenthesis). l. Click **OK** to confirm. 11. Click **OK** and then **Close** to exit the **KPI** dialog. /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis/create-kpi-based-on-measure page 2024-08-02T13:54:26+02:00 # Create KPI based on Measure In this example, we create a KPI based on measures. This time, we will create a KPI that divides a *Line Amount* measure by a *Quantity* measure. 1. In the **Report Designer** menu, click **KPIs** and then click **Manage KPIs**.  2\. In the **KPI** dialog, click the **Financial KPIs** group that you created in the previous example. 3\. Click **Change**.  4\. Click the **Add** button to add a new KPI. 5\. In the **Name** field, enter a name. In this example, we have named the KPI *Average amount*. 6\. Select the **Based on Measure** option. 7\. Click the **Edit** button.  8\. In the **KPI Editor**, take the following steps: a\. Open the **Sales** module, and the **Receivable Invoice Lines** folder. b\. Drag the **Line Amount** measure into the **Formula** area. c\. Click the **Divide by** button (/). d\. Drag the **Quantity** measure into the **Formula** area. e\. Click **OK**.  9\. In the **KPI** dialog box, click **OK**. 10\. In the **KPI group** dialog, click **Close**. The KPI is now created and ready to use in reports.  /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/examples-use-of-expressions-and-kpis/create-expressions-for-income-and-cogs page 2024-08-02T13:54:26+02:00 # Create expressions: INCOME and COGS 1. In the **Report Designer** menu, click **Expressions** and then **Manage Expressions**.  Expressions are organized in groups, so you must first create a group. 2\. Click **New** to create a new expression group. (Our two expressions *INCOME* and *COGS* (Cost of Goods Sold) will be added in this group.)  3\. In the **Expression Editor** dialog, enter an expression group name. (In the example we use *Financial Expressions*.) 4\. Click the **Add** button. 5\. Replace the default text *New expression* with the name of the first expression; *INCOME*.  6\. Click the **INCOME** expression. 7\. Locate your account dimension and drag it into the **Filters** area. 8\. Click the **Lookup** button and select the required accounts that should be part of the *INCOME* expression. (In this example, accounts 200:270.)  9\. Click the **Add** button again and name this expression *COGS*. 10\. Repeat the process of dragging your account dimension into the filter area and enter the relevant account information. (In the example, account 310.)  11\. Click **OK** to confirm and exit the dialog. 12\. Click **Close** to exit the **Expressions Groups** dialog*.*  /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/expressions section 2026-01-16T14:27:01+01:00 # Expressions *Expressions* can be created and managed through the **Report Designer** menu under **Expressions**.  Using Expressions is a way of referring to dimension values outside single reports and is typically used to refer to a selection that will be used in multiple reports. By using an expression instead of an ordinary selection within the report, you can easily update multiple reports by editing the expression instead of having to edit each report individually. For example, if you add an account to your general ledger and you need to include this new account in your reports, it is very time-consuming having to open multiple reports and making sure the new account is included in the selection. If, however, you use an expression and you need to add an account, you simply edit the expression, and all the reports where this expression is used will automatically be updated. It is possible to create expressions on all dimensions and multiple dimensions can be combined in one expression. Expressions are organized in groups. An expression group can consist of one or multiple expressions.  From the **Expression Groups** dialog box, you have access to the following options to manage expressions: | Button | Function | |---|---| |  | Click to create a new expression in the **Expression** dialog. Make sure to enter an expression name before starting to select fields for the expression. You can also enter a description for the expression. | |  | Click to open the **Expression** dialog and edit the currently selected expression group. | |  | Click to delete the selected expression group. | |  | Click to open a network browser window and import an expression group. Valid files are other OSR expressions with the \*.xml format. | |  | Click to open a network browser window and export an expression group to the location. The newly exported expression group will be in the \*.xml format. | ### Expressions and Chart of Accounts You can also use the predefined Expressions from the chart of accounts you have set up in [Chart of Accounts](./../../../../chart-of-accounts) . > [!TIP] > The expressions from *Chart of Accounts* can be edited in the *Chart of Account*s module in the portal. /onestopreporting/help/report-designer/creating-reports/expressions-and-kpis/expressions/use-expressions-in-reports page 2026-01-16T14:27:01+01:00 # Use expressions in reports To use an expression instead of creating an ordinary selection/filter, you do the following: 1. Drag out the relevant dimension from the list of dimensions as usual (for example, **Account**). 2. In the **Layout Editor**, click the X button to delete the filter that was created when you dragged in the dimension. 3. Open **Expressions** from the **Report Designer** menu and drag the relevant expression into the filter area.  > [!TIP] > You can edit expressions that are created from the *Layout Editor*; click the *Edit Tree* button to go to the *Expressions Editor*. /onestopreporting/help/report-designer/creating-reports/drilldown section 2024-08-02T13:54:26+02:00 # Drilldown The simple *Drilldown* option allows users to see the underlying detail of the value presented in the report. To enable this feature; 1. Select a *measure* field in the report definition. 2. In the **Layout Editor**, click the **Drill-To** tab, and select the **Use Default Drilldown** checkbox.  After you have executed the report, you can drill-down on any number to see the underlying journal entry level details from the same module; 1. Right-click any number in the executed report. 2. In the right-click menu, choose **OneStop Reporting** and then **Drilldown**. The result of the drill-down will appear in a new worksheet in Excel named *\#DrillDown* and each resulting column will automatically be given filters.  ### Customizing the default drilldown view You can customize the default drilldown view to display fields of your choice and in the order of your preference. [Drill-To](./drill-to) /onestopreporting/help/report-designer/creating-reports/drilldown/drill-to section 2024-08-02T13:54:26+02:00 # Drill-To **Drill-To** is an advanced drilldown feature that allows users to drill down across multiple modules. For example, users can begin with a report showing values from the *General Ledger*, and then drill down to see the underlying values from the *Accounts Receivable* sub-ledger. To use the *Drill-To* feature, you must configure the *Drill-To Definition* in the report template. *Drill-To Definitions* must be created on the cell level, by selecting a cell that will be the common dimension between the two different modules. For instance, the shared dimension could be the *Account* number or the *Voucher* number. When the *Drill-To* feature is used, **Report Designer** will use the specified dimension to retrieve entries from the sub-ledger. The figure below shows the interfaces involved in configuring the **Drill-To** feature.  | | **Button** | **Function** | |----|----|----| | **1** | **Drill-To tab** | This tab is only available when you have selected a cell. | | **2** | **Use default drilldown** | Select this checkbox to apply the default drilldown option. Deselect this checkbox to create a customized drill-to definition. | | **3** | **Drill-To item** | A list of *Drill-To definitions* applied to the specific cell is generated. You can edit or delete the definition by clicking on the appropriate buttons. | | **4** | **Create** | This button will create a new *Drill-To* definition and open the Drill-To Builder. | You can configure the *Drill-To Definition* through the **Drill-To Builder**. The configuration is required so that **Report Designer** is able to filter through to the proper sub-ledger using parameters or drill source dimensions.  *Figure: Drill-To Builder dialog box* | | **Interface Name** | **Function** | |----|----|----| | **1** | **Name** | Create a descriptive name so you can easily identify the *Drill-To Definition*. | | **2** | **Search** | Search for data items in your ERP system. | | **3** | **Module list** | In this section, the selection of *Modules* is displayed. From the list of *Modules*, drag dimensions into the *Drill-To* details and filters section to configure the *Drill-To Definition*. | | **4** | **Drill-To details** | Data items can be dragged into this section to create column headers in the resulting *Drill-To* worksheet. The column headers can be reordered by dragging the header title. Moreover, the data item can be removed by clicking on the header title once. | | **5** | **Drill-To filters** | Data items can be dragged into the filters section so that the *Drill-To Definition* can be configured to filter to the correct detailed transactions. | When using the **Lookup** button in the *Drill-To* filters, you have these options:  *Figure: Drill-To Lookup dialog box* | **Tab** | **Description** | |----|----| | **List** | On this tab, you can select a filter based on a constant value. | | **Parameter** | On this tab, you can select a filter based on a parameter from the report definition. | | **Drill-To** | On this tab, you can select a filter based on the attribute context of the *Drill-To* originating cell. The available fields on this tab are based on the dimensions and attributes that are used in the particular row or column in the report definition. | /onestopreporting/help/report-designer/creating-reports/drilldown/drill-to/how-to-configure-a-simple-drill-to-definition page 2024-08-02T13:54:26+02:00 # Configure a Drill-To definition In this section, a simple *Drill-To Definition* will be created that allows users to run a P&L statement and drill to the HR sub-ledger. In the HR sub-ledger, there are details of the employee salaries. However, in the simple P&L statement, only a summary value exists. Begin with a simple P&L statement as shown in the figure below. The *Drill-To Definition* will be created in cell E12 and will be configured to drill to the HR sub-ledger.  Follow these steps to configure this *Drill-To Definition*: 1\. Click on the cell that corresponds to the Amount field for the Expenses section. 2\. In the **Layout Editor**, on the **Drill-To** tab, click the **Create** button  to create a new definition*.*  The **Drill-To Builder** dialog box opens. 3\. In the **Name** field, enter GL to HR Drill. 4\. In the Module list, open the *Payroll* sub-ledger. 5\. From the *Payroll* sub-ledger, drag the dimensions you want to display in the Drill-To report into the **Drill-To details** area*.*  Next, you must configure the filters used to drill to the values in the sub-ledger. These filters are crucial for setting up the correct *Drill-To Definition*. The dimensions used in the original report template can be used so that data from unrelated dimension codes can be filtered out.  For this particular example, the *Account* and *Period* filters reference the drill source account and periods. This means that the **Report Designer** will reference the dimension code found in the particular row or column when the *Drill-To* feature is used. The *Category* dimension uses a static filter, whereas the *Department, Entity,* and *Scenario* filters use the parameters from the report definition. The *Drill-To Definition* has now been configured and the report is ready to be run. ### Use the Drill-To option in an executed report 1. Right-click on cell E15. 2. In the right-click menu, click the **OneStop Reporting \> Drill-To** menu option.  *Figure: Generated report with the OSR Drill-To right-click option* The Drill-To result creates a new sheet and displays values based on the Drill-To details and filters that were configured in the above steps.  *Figure: Drill-To sheet in an executed report* /onestopreporting/help/report-designer/creating-reports/drilldown/drill-to/drill-to-definitions page 2026-01-16T14:27:01+01:00 # Drill-To Definitions Drill-to definitions created in the report definition are displayed in the **Drill-To Definitions** pane. Drill-to definitions created in the **Layout Editor** can be reused throughout various sections of the **Report Designer** by dragging them from the **Drill-To Definitions** pane into the **Drill-To** tab of the **Layout Editor**.  *Figure: Drill-To Definitions pane and the Drill-To tab on the Layout Editor* ### Edit a Drill-To Definition 1. In the **Drill-To Definitions** pane, select the definition. 2. Click the **Edit** option at the bottom of the pane. ### Delete a Drill-To Definition 1. In the **Drill-To Definitions** pane, select the definition. 2. Click the **Delete** option at the bottom of the pane. New Drill-To definitions must be created from the **Layout Editor**. > [!TIP] > For more details on how to use the *Drill-To* feature, see [Drill-To](../drill-to-definitions) and [Configure a Drill-To Definition](../how-to-configure-a-simple-drill-to-definition) /onestopreporting/help/report-designer/creating-reports/linking-sheets page 2024-08-07T11:22:23+03:00 # Linking sheets Linking sheets in the **OSR Report Designer** cannot be done in the same manner as in a regular workbook. To link sheets in the **Report Designer**, formulas are required because of the complexities of expanding groups and references between worksheets. This section will explain the steps for setting up a simple linked sheet. The concept can be applied to design more complex reports. ### Link sheets In this example, two identical sheets will be created, but the second sheet will reference cells from the first sheet. 1. Create a simple report with these items: - From the **General Ledger** folder, insert d*epartment, account, description* (under the **Account** dimension), and *amount* into cells B8 through E8. - Drag the **Period** dimension into cell E6 - Drag the **Year to date** function from the **Period Functions** module into cell E3. The report should look like the figure below shown below. > **NOTE:** Make sure that a group is created on row 8 and column E.  2. Right-click the worksheet and select **OneStop Reporting** *\>* **Copy**. 3. Create a new worksheet. 4. Right-click on the new sheet tab and select **OneStop Reporting** *\>* **Paste as new**.  Now, you must create reference names on the first sheet. 5. Access the first sheet (Sheet 1) and highlight cells E6 and F6. In the name box, rename this set of cells Period2.  > **NOTE:**The reason for selecting an extra cell to the right is because the group in column E contains an expanding group that will result in an unknown number of columns. Selecting the extra cell ensures that all columns created by the expansion are included in the reference. The same would apply if an expanding group existed in the row group. However, for an expanding group on a row, the extra cell selected should be one cell below. 6. Access the copied version of the report in *Sheet1(1)*. Delete the contents of cell E6 and link the period from the original sheet by entering the formula =INDIRECT("Sheet1!Period2").  You can now execute the report and the sheets will look identical because the *Period* dimension in the second sheet is referencing the *Period* dimension in the original sheet. /onestopreporting/help/report-designer/creating-reports/formatting-data-with-leading-zeros-in-excel page 2024-08-02T13:54:26+02:00 # Formatting data with leading zeros in Excel If data generated in the report contains leading zeros (for example, Department numbered 000), Excel will only display a single 0. However, you can properly display leading zeros by simply adding parenthesis before and after the OSR function in the formula bar. See the figure below.    /onestopreporting/help/report-designer/creating-reports/static-mode-for-reports-remove-or-keep-formulas-in-run-report page 2026-01-16T14:27:01+01:00 # Static mode for reports: Remove or keep formulas in run report Some of our users have very large reports, containing numerous cells with formulas, many that are referring to other cells and functions. This might cause performance issues when running reports in the portal. Therefore, we have made it possible to run reports in "static mode". This means that all formulas are removed in the workbook and the values are replaced with actual cell values. Users can control the formula settings for a report template from the **Report Properties** in **Report Designer**. On the **Reporting** tab, there is now a **Keep formulas in run report** checkbox. If this checkbox is deselected, the formulas are removed from the report. - The default setting for existing reports is selected (True). - The default setting for new reports is deselected (False). > [!NOTE] > This option will not have any impact on budgets. ## Keep formulas in run reports 1. In Report **Designer**, on the **OneStop Reporting** tab, click **Report Properties**. 2. Click the **Reporting** tab. 3. Select the **Keep formulas in run report** checkbox. 4. Click **OK**. All formulas in the generated report are kept during processing. ## Remove formulas in run reports 1. In Report **Designer**, on the **OneStop Reporting** tab, click **Report Properties**. 2. Click the **Reporting** tab. 3. Clear the **Keep formulas in run report** checkbox. 4. Click **OK**. All formulas in the generated report are now removed during processing, and the values are replaced with actual cell values. When formulas are removed from a report, it is no longer possible to make calculations in run reports in **Reporting**. > [!NOTE] > When formulas are removed from the report template, the drilldown will function as normal.  /onestopreporting/help/report-designer/creating-reports/use-of-macros page 2024-07-04T17:39:28+02:00 # Use of macros OneStop Reporting **Report Designer** does not support the use of macros. We use a third-party application to generate the reports in the portal and this application does not support any use of macros. /onestopreporting/help/report-designer/creating-reports/built-in-excel-security page 2024-08-02T13:54:26+02:00 # Built-in Excel security Excel has two different built-in security features: - The first is security on the Excel *file level*. You can protect the entire file by requiring a password to open and modify the workbook. This type of security does not interact with **Report Designer** and purely restricts who can access the report definition. For more information on security on the file level, refer to the appropriate [Microsoft Excel](https://support.office.com/en-us/article/protect-an-excel-file-7359d4ae-7213-4ac2-b058-f75e9311b599?ui=en-US&rs=en-US&ad=US) documentation. - The second type of built-in security for Excel is the *protect worksheet* or *protect workbook* feature. By using either of these features, you can *lock* the worksheet cells so that users may not modify the contents of locked cells unless the worksheet or workbook is unprotected. If this feature is used, there is a direct interaction with the **Report Designer** reading the protected report definition. Therefore, report designers *must* provide the password used to lock the worksheet/workbook in the **Report Properties** -\> **Protection** tab (see figure below). Otherwise, the report will not be generated and an error message will be shown.  /onestopreporting/help/report-designer/creating-reports/page-setup-for-reports page 2026-01-16T14:27:01+01:00 # Page setup for reports 1. (If not already open in Report Designer.) In **Reporting**, click  **Edit** to open the report in **Report Designer**. 2. Mark the area of the report that you want to include, for example, for PDF outputs. **NOTE:** Make sure you do not include areas with hidden rows or columns because these will appear as large white areas in the report. 3. On the **Page Layout** tab, in the **Page Setup** group, click **Print Area**. 4. Click **Set Print Area**. 5. In the **Scale to Fit** group, select **1 page** from the **Width** list. 6. In the **Scale to Fit** group, select **1 page** from the **Height** list. **NOTE:** If you have a very long report, for example, reports with large amounts of transaction listings, you should consider setting this option to **Automatic**. 7. In the **Page Setup** group, click **Orientation** and then click **Landscape. NOTE:** Even though this option is usually best for most reports, you should also consider using the **Portrait** option. 8. Upload (Ctrl+S) the report to OneStop Reporting.  > [!TIP] > It is a good idea to check your report in the portal; 1. In **Reporting**, run the report. 2. Click the lower part of the  **Export to Excel** button and click **Download as PDF**. /onestopreporting/help/report-designer/creating-reports/best-practices-for-creating-consolidated-reports page 2026-01-16T14:27:01+01:00 # Best practices for creating consolidated reports > [!IMPORTANT] > By "consolidated" we mean; collecting data across entities with similar constructs and summarize into a consolidated report. This is not true financial consolidation. Also note that currency translations are not handled automatically and must be set up in the template (using Report Designer). To be able to consolidate reports from multiple companies, you must: - **Have all companies in the same environment in the OneStop Reporting portal:** That means, all the companies must be within the same *Client* in **Administration** as shown in this example:  - **Have the module *GroupReporting* in your report.** In the example below we use dimensions and attributes in the module GroupReporing. This might be different in your accounting system.  - **Have a method for elimination implemented in your report template:** Generally, there are three methods you can use in **Report Designer**: (**The method you choose to use in OneStop Reporting, depends on how your company handles eliminations.**) 1. In your accounting system, you can use a specific *dimension* to tag internal transactions. In Report Designer, you must filter on this dimension in the elimination column. 2. If you have specific *accounts* used for internal transactions, you must filter on these accounts in the elimination column in the report template. 3. If you have a specific *company* for eliminations, you must use this to filter on in the elimination column in the report template > [!IMPORTANT] > The consolidated report templates from OneStop Reporting do not have an elimination method implemented, and this must be included in the template. > Also note that the eliminations must be done for each company.