/onestopreporting/help/report-designer section How to install Report Designer and get an introduction on how to design and run your own reports in Report Designer 2026-01-16T14:27:01+01:00 # Report Designer How to install Report Designer and get an introduction on how to design and run your own reports in Report Designer **OneStop Reporting Report Designer** is the report design application in OneStop Reporting. **Report Designer** is a full-blown Excel-based design tool enabling the user to create report and budget templates on top of their organization's data in the OneStop Reporting. OneStop Reporting users can create presentation-quality reports, such as financial statements, operational reports and KPI reports with charts, and graphical elements. Users can also execute reports from the **Report Designer**. To access **Report Designer** the users are required to have the user level *Design user*. ------------------------------------------------------------------------ > [!IMPORTANT] > Report Designer: > Provides complete financial and operational report creation based on the familiar Microsoft Excel user interface. > As long as a *data connection* to the ERP-system exists in the OneStop Reporting portal, users can use **OneStop Reporting Report Designer** to create reports on their organization's data. > For further description of how to create a data connection to the ERP-system in the OneStop Reporting, please go to [Manage Data connection](../management/Data-connections/) /onestopreporting/help/report-designer/introduction section 2024-08-02T13:54:26+02:00 # Introduction Using **Report Designer**, you can create report and budget templates that other users can access, run and execute in OneStop Reporting. The **Report Designer** uses a familiar Microsoft Excel user interface and drag-and-drop functionality for creating high-quality templates. Furthermore, Excel formatting, calculations, and charts can be applied to the reports. With each execution of the report, you filter information based on the report parameters, making the reports dynamic. /onestopreporting/help/report-designer/introduction/connecting-to-the-osr-cloud page 2024-11-28T14:29:41+01:00 # Report Configuration When you create a new report from **OneStop Reporting**, you select the company you want to use when building your report. However, you can change this in the **Report Configuration** dialog box. *Figure: Report Configuration button on the OneStop Reporting tab* In the **Select data connection** dialog box, in the **Companies** drop-down menu, you can select from the available companies. *Figure: Select data connection dialog box* If no connection has been configured*,* a connection must be created in the **Administration** module in the **OneStop Reporting portal**. For information on how to create connections, see [Create a connection](../../../datahub/connectors). /onestopreporting/help/report-designer/introduction/report-designer-interface-2 section 2026-01-16T14:27:01+01:00 # Report Designer user interface The next sections give you an overview of the three components of OSR Report Designer: the **OneStop Reporting** Excel ribbon, the **Layout Editor** window, and the **Report Designer** menu.  | **Number** | **Component** | |---|---| | 1 | OneStop Reporting Excel ribbon | | 2 | Report Designer menu | | 3 | Layout Editor window | /onestopreporting/help/report-designer/introduction/report-designer-interface-2/layout-editor page 2026-01-16T14:27:01+01:00 # Layout Editor The **Layout Editor** allows you to create, edit, and delete business rules when designing reports. Click the **Layout Editor** button  in the **OneStop Reporting** Excel ribbon to open this window. The **Layout Editor** is primarily used for managing filters, grouping/sorting and functions for cell, row, column, and sheet level business rules. The **Layout Editor** window contains the elements shown in the figure: *Figure: Layout Editor interface elements* ## Report Level (Sheet filters) (1) Selects the entire report so that rules (parameters) can be applied to the entire Excel report. ## Column/ Row Level (Column selections/Row selections) (2) You can select a column or row to create, edit, or delete business rules at the column or row level. > [!TIP] > For more information about the property hierarchy, see [Property hierarchy](./../../../creating-reports/property-hierarchy). ## Data display settings (3) You can specify the settings for displaying the data in the report. | **Option** | **Function** | |---|---| | *Expanding* | If selected, the report will list the details of dimensions and create what is known as an *expanding group;* that is, list each account rather than grouping the accounts together and displaying a total. Only available at row or column level. | | *Display blank records* | If selected, the report will display dimensions with blank records in the report; that is, if the checkbox is cleared, empty datasets will not be displayed. Only available at row or column level. | | *Limit* | If selected, the report will display dimensions with blank records in the report; that is, if the checkbox is cleared, empty datasets will not be displayed. Only available at row or column level. | ## Edit area for filters, grouping/sorting and period aggregation (4) In this area, you can specify settings for the [filters](./../../../creating-reports/layout-editor/filters), [grouping/sorting](./../../../creating-reports/layout-editor/grouping-and-sorting) and period aggregation. ## Tabs for Filters, Grouping/Sorting, Period Aggregation (5) The tabs allow you to configure different rules for the selected level. For more details on the various options, see the [Layout Editor](./../../../creating-reports/layout-editor) section. /onestopreporting/help/report-designer/introduction/report-designer-interface-2/osr-excel-ribbon section 2024-08-07T11:22:23+03:00 # One Stop Reporting Excel ribbon The **OneStop Reporting** Excel ribbon is found at the top of the Excel window and provides easy navigation and access to the key functions in the **Report Designer.** | Excel ribbon button | Function | |----|----| |  | Allows you to upload a report to the OneStop Reporting portal. NOTE: Reports are automatically uploaded to the portal when you save. | |  | Opens the Report Configuration dialog box, which allows you to configure the company. | |  | Allows you to input optional additional information, such as the report title and description. Also allows you to enter the password for protected worksheets. | |  | Provides access to report behavior settings, details on the version of Report Designer, and miscellaneous options. | |  | Opens and closes the Designer menu on the left side of the screen, where you find the integrated fields from the ERP system as well as pre-defined period functions. | |  | Opens and closes the Layout Editor window, where you can apply filters, sorting, and grouping for the report. | |  | Enables and disables layout comments that display the filters, groupings, and functions used in the sheet, row, column, or cell level of the report. | |  | Opens and closes the Report Parameters dialog box, where you can manage the properties for the parameters that have been applied to the report definition. | |  | Enables and disables automatic creation of parameters. When disabled, report parameters will not be created unless you explicitly create one using the Report Parameters dialog box. | /onestopreporting/help/report-designer/introduction/report-designer-interface-2/osr-excel-ribbon/options section 2024-07-04T17:39:28+02:00 # Options #### **Options** Clicking the **Options** button opens the **Report Designer Options** dialog box. This dialog contains two tabs: /onestopreporting/help/report-designer/introduction/report-designer-interface-2/osr-excel-ribbon/options/1-the-designer-tab page 2024-08-07T11:22:23+03:00 # 1. Designer tab ## Designer tab  | Option | Function | |----|----| | Always open Report Designer in Run mode | If the checkbox is selected and a saved report is opened, Report Designer will always open with the Run tab active. | | Insert field title above cell on drop | If the checkbox is selected, when a data field is dragged into an Excel sheet, Report Designer will automatically create a title one cell above the data field. The title can be modified or deleted in the cell. | | Show tooltips in modules | If the checkbox is selected, you will get tooltips with extra technical information from the ERP system when hovering over a data item in the Report Designer menu. | | Create Group Dialog | The vertical slider has three settings (Always, Conditionally, and On CTRL). Depending on its position, Report Designer will always, conditionally, or never prompt the user to create a new group when a data field is dragged into the report definition. If the slider is set to On CTRL, OSR Report Designer will never prompt the user and the user must manually create a group by holding the CTRL key while dragging the field.  | | Application | Allows you to select a language for the OSR application using a drop-down list. | | Integration Dictionary | Allows you to select a language for the integration package using a drop-down list. | /onestopreporting/help/report-designer/introduction/report-designer-interface-2/osr-excel-ribbon/options/2-the-application-tab page 2024-08-07T11:22:23+03:00 # 2. Application tab ## Application tab  | Option | Function | |----|----| | Auto-connect | If selected, Report Designer will always connect when Excel is run. However, if the checkbox is cleared, you are required to manually connect Report Designer before designing or running a report. | | Auto-start report | Opening Excel by double-clicking a OneStop Reporting report via Windows Explorer overrides the setting under Auto-connect, and the OSR Report Designer Excel COM add-in starts automatically when Excel is opened. To prevent the OSR Report Designer Excel COM add-in from starting even when Excel is opened by double-clicking a OneStop Reporting report, you must select the Auto-start report checkbox. To prevent the OSR Report Designer Excel COM add-in from starting automatically no matter how Excel is started, both the Auto-connect and the Auto-start report checkboxes must be cleared. | | Sign in automatically | If the checkbox is selected, OSR Report Designer will automatically sign in with the previously logged-on user. | | Portal URL | Shows your OneStop Reporting portal URL. | | Licensed to | Displays the license information. | | About... | Click this button to get information about the Report Designer version, build number and the license information. | /onestopreporting/help/report-designer/introduction/report-designer-interface-2/osr-excel-ribbon/report-properties page 2025-06-13T15:38:13+02:00 # Report Properties The **Report Properties** are available from the OneStop Reporting ribbon in Excel. They can be accessed by clicking the **Report Properties** button. ### General tab You can view and change information, such as, title, description, author, and get information on when the report was created and last modified. ### Protection tab This security property enables you to specify the password used in Excel's built-in protection, so that it allows **Report Designer** to unprotect the workbook as needed. Excel has two different built-in security features: - The first is security on the Excel file level. Users may protect the entire file by requiring 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 documentation. - The second type of built-in security for Excel is the protect worksheet or protect workbook feature. By using either of these features, users 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 **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. Otherwise, the report will not be generated and an error message will be shown. ### Reporting tab The Report properties for **Reporting** are: - **Keep formulas in run report:** - When selected, all formulas are kept when running the report in OneStop Reporting. This allows you to make calculations after the report has been executed in **Reporting**. **NOTE**: This option does not apply to budget templates - When deselected, all formulas in the generated report are removed during processing, and the values are replaced with actual cell values. This option may improve the performance of the report and reduce run time. **NOTE**: The drilldown function works as normal even though the formulas are removed from the report template. - **Replace indirect references**: - When selected, all indirect references, i.e. INDIRECT() are replaced with direct references in order to improve performance in formulas. This does not work for complex reference arguments to INDIRECT. **NOTE**: This option will also affect budget templates. ### Budgeting tab The Report properties for **Budgeting** are: - **Force refresh when opening report in Portal:** - When selected, this property will force a report (or a budget template) instance in the portal to be executed each time the report is opened. - Some conditions that warrant its use would be: - 1\) to ensure the user is working with the latest data within the budget form. If left unchecked, the data in a history item report will display the data the last time the report was executed by that user. - 2\) if budgeting data has been updated since the last time the report/form was executed, the report should be refreshed to show the latest status. **Note**: Checking this property means that the execution will also happen when clicking on a history item (previously run report) in the left menu of the report page in the Portal. You should keep this in mind if reports are heavy and take a long time to run. - **Protect non-input cells:** - When selected, it automatically protects all cells in the budget template that do not have any defined budgeting storage rule. This protection applies when running the budget template in **Budgeting** or **Assignments**, but does not take effect in **Report Designer**. This means that end-users will be prevented from editing other cells than data input cells. - For advanced budgeting templates with a combination of storage rules with cells requiring user input and/or cells with calculated values where input is not required, you will need to use Excel functionality to protect the sheet and unlock input cells. To do this, deselect the automatic OSR protection property. - **Allow Line Item Detail:** The **Budgeting** module allows for specifying line item details of budget transactions. For example, as an administrator, you would like the End-user to specify additional details about travel expenses. To enable this, you need to make sure this option is checked. Read more about [Line Item Detail here](./../../../../../budgeting/report/edit-budget-data/add-line-item-details) /onestopreporting/help/report-designer/introduction/report-designer-interface-2/report-designer-menu page 2026-01-16T14:27:01+01:00 # Report Designer menu ## Report Designer tabs The **Report Designer** contains two tabs: - The **Design** tab generates the ERP modules and dimensions based on the integration that you are connected to. - The **Run** tab allows you to select parameters and execute reports. *Figure: Report Design tabs* Depending on which tab is active, certain functions within **Report Designer** will be deactivated. The **Design** tab contains the areas shown in the figure:  **Design** tab | | **Option** | **Function** | |---|---|---| | **1** | Data items | A list of data items from the ERP system. Drag data items into the Excel sheet to create a report template. When you mouse over a data item name, you can see the system name of the data item. For more information, see [Data Items](./../../../creating-reports/data-items). | | **2** | ERP modules | A list of ERP modules integrated from the ERP system. The modules contain the dimensions and measures that can be used to create a report. In addition, period functions and report parameters are included below the list of modules. When you hold the cursor over a module name, you can see the system name of the module. | | **3** | OneStop Reporting functionality | A list that includes functionality, such as period functions, expressions, KPIs, and drill-to definitions. | **Run** tab | | **Option** | **Function** | |---|---|---| | **3** | Parameters | Parameters defined in the report design will be listed. You can specify a parameter by using the **Lookup** button. | | **4** | **Run** button | To execute the report, click the **Run** button. You can run the report in **Report Designer** or it can be published to a regular Excel worksheet by clicking the drop-down arrow.  | /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. /onestopreporting/help/report-designer/running-reports section 2024-08-02T13:54:26+02:00 # Running reports After a report definition is completed, you can run (execute) it in the **Report Designer***.* From **Report Designer** a report can also be published as a static Excel file for distribution to individuals without a **Report Designer** license. Reports can also be run in the **OneStop Reporting** portal, from the **Reporting** module. When the **Run** tab is active in **Report Designer** you cannot save any changes made to the document. Report definitions can only be saved while the **Design** tab is active. /onestopreporting/help/report-designer/running-reports/publishing-a-static-reporting page 2026-01-16T14:27:01+01:00 # Publishing a static report Static reports are normal Excel reports without a connection to the OSR Cloud that can be e-mailed to non-licensed users. ### Create a static report 1. On the **Run** tab, click the drop-down arrow in the **Run** button and select **Publish**. After clicking the **Publish** button, the **Designer** menu disappears. 2. Save the static report as a regular Excel file.  > [!CAUTION] > If the connection to OneStop Reporting is stopped or disconnected at any point, the report will not be generated correctly. This will occur even if the connection is reestablished.  /onestopreporting/help/report-designer/running-reports/running-a-report page 2024-08-07T11:22:23+03:00 # Running a report From **OSR Report Designer** the report definition can be executed from the **Run** tab **(1)** in the **Designer** menu as shown in the figure below. *Figure: Run tab* Depending on the report definition, there may be multiple parameters **(2)**. For each parameter, you can use the **Lookup** button  to select the desired parameter. (See [Report parameters button](./../../creating-reports/report-parameters/report-parameters-wizard/manage-parameters) for more details). After you have selected the parameters, click **Run** to execute the report **(3)**. /onestopreporting/help/report-designer/installing-report-designer page 2026-01-16T14:27:01+01:00 # Install Report Designer The first time you are going to use Report Designer, you must install the OneStop Reporting add-on to your Excel application. Make sure your pc meets the [technical requirements listed here](./../../technical-requirements). > [!IMPORTANT] > You must have administrator rights on your PC to be able to install Report Designer. If you don\'t, you must contact your IT department to get help. > [!TIP] > For technical requirements, see [Technical Requirements.](./../../technical-requirements) for technical requirements For installation file: [Download Report Designer](https://portal.onestopreporting.com/reportdesigner/download). ## Install Report Designer If you have Excel open, close the application. 1. In the Portal, go to **Reporting**. 2. Click on a report and click **Edit**. A new window opens in your browser. 3\. Click the **Download the latest version of OSR Report Designer** link. 4\. Click **Run**. The **OSR Report Designer Setup Wizard** opens. 5\. Click **Next**.  6\. In the **Choose a file location** dialog box, use the default file location or choose another. 7\. Click **Install**.  The installation is done. 8\. Click **Close**.  9\. Back in the portal, go to **Reporting**. 10\. Click a report and click **Edit.** The report opens in Excel and you now have an extra **OneStop Reporting** tab.