Frequently Asked Questions about Report Designer

  • How do I add period functions in Report Designer?

    Compared to Report Designer version 4.x the logic on how to add period functions has changed. In order to add for example YTD (year to date) function on a column you need to follow the steps below:

    1. Drag Period into the report.
    2. Add Expaning on the column.
    3. Go to Period function


    4. Drag the Year to date period function into the Filter tab within the Layout Editor (on the column):

    If you want aggregated data you can drag the Period function into the Period Aggregation tab.

  • Issues with Last Year – This Period

    If you are having issues with the period function Last Period This Year when a column doesn’t expand, you can get around the issue by setting «Max» on the attribute on cell level.


  • Filter out rows that contains SUM(Period Change)=0


    If you want to filter out rows that contain SUM(Period Change)=0, it will not work if you are using Period Change <> 0.

    You can do the following;

    1. Insert two Period Change parameters.
    2. In the first one, select the Less than (<) operator and enter -1.
    3. In the second one, use the OR operator,  the Greater than (>) operator, and enter 1.

  • Deleted groups do not disappear from the Layout Editor

    If you delete groups and they do not disappear from the Layout Editor in Report Designer, before you refresh/click Run and go back to Designer mode, you can try the following:

    Try to set Excel to run in Compatibility mode for multiple displays;

    1. Click File > Options.
    2. On the General tab, under User interface options, select the Optimize for compatibility option.

  • Add-ins

    If OneStop Reporting Designer doesn’t show when Excel is open, follow the steps:


    1. Open Excel.
    2. Click File and then Options.
    3. Click Add-ins. From here, you will be able to add OneStop Reporting if it has been deactivated.
    4. In the Manage drop-down list, click COM Add-ins.
    5. Click Go.

    6. In the Add-ins available list, select the OneStop Reporting 5.0 check box.

    7. Click OK.


    8. Close Excel and try to open it again from the OneStop Reporting.



    If you still experience problem, try the following steps:



    1. Click the Search button in Windows.
    2. In the Search field, enter Regedit.
    3. Press Enter.
    4. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\AddIns\OneStop.ReportDesigner
    5. If LoadBehavior has the value 0, you must change it to 3.
    6. Right-click and select Edit.
    7. Change the value from 0 to 3.




  • Graphs

    OSR Portal uses Spreadsheetgear (SPG) for rendering instead of Excel which is used in OSR Designer. Because SPG has fewer rendering features, objects such as graphs might appear differently when reports are run in the Portal.

    You can resolve this expansion issue by marking the empty box in the data source.


  • How to find attributes?

    OSR Designer has a search area, where you can search after attributes.


  • Useful formulas in Excel

    SUMIF Summarizes the given cells according to the defined condition/logical test.
    VLOOKUP You can use VLOOKUPto search for the first column in an area of cells and then return a value from any column in the same row.
    SUBTOTAL Returns a subtotal in a list or database. (Can take into account hidden rows.)
    IFERROR Return a value that you specify, if a formula evaluates to an error. Otherwise it returns the result of the formula. Use the IFERROR function to catch and handle errors in a formula.
    IF The IF function returns one value if the condition you enter returns TRUE and a different value if the condition return FALSE. For example, the formula =IF(A1&gt10,"Over 10","10 or less") returns «Over 10» if A1 is bigger than 10 and «10 or less» if A1 is smaller than or equal to 10.
    INDIRECT Returns the reference specified in a text string. References are immediately calculated so that the content is shown. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.



    See the Microsoft Office documentation (external link) for more information.

  • How can I retrieve a value of one cell in another sheet (within the same workbook)?

    If you have a value in a named cell in another sheet in the OSR workbook, and you are trying to collect that value into the OSR report sheet, you need to do the following:


    If it is in another sheet you’ll have to give a reference to that sheet.


    Where Sheet = name of the sheet you’re looking into.

    Cell = Name of the cell you’re looking into.


    If for example I want to reference to cell B5 in sheet2, then the formula would be



    If you renamed the sheet2 to Test, and the cell b5 to Cell, then the formula would look like this


    If the sheet you wish to refer to includes expanding groups, you have to rename the relevant cell, like shown above.

  • How can I show the value zero where the formula doesn’t give a valid result?

    Use IFERROR:



  • Create dependencies between two parameters


    Create dependencies between two parameters

    It is possible, under certain circumstances, to set dependencies between parameters. For example, if end users choose to run a report for a specific department, then they will get information about the projects that belong to that department.

    To achieve this, the following formula must be entered in the parameter Lookup filter in the Report Parameter Wizard, for the parameter where the lookup filter is to be limited:

    ‘{Parameters.Parameter for Reference}’: ‘{Parameters.Parameter for Reference}’

    Using this formula, a calculation is performed to retrieve parameter values ​​for a dimension, for example, Project, based on another dimension, for example, Department. When an end user does a lookup in the parameter, for example Project, then the Project values are calculated based on which Department value the user has selected. The function that is entered into the Lookup filter in this case will then be:

    ‘{Parameters. Department}00‘: ‘{Parameters. Department}99


    For such a dependency between two parameters to work, the following conditions must be met:

    • There must be a connection between the dimensions, for example between departments and projects, in the ERP system.
    • This relationship between the dimensions must be based on the same value / number. Example: Department number 22, is linked to projects starting at number 22: Department number 22 is linked to project numbers 221, 222, 223,224, etc. This is because OneStop Reporting uses a calculation to retrieve the values.
    • Dimensions must have a fixed value string.
      Example: There can only be departments with one value / number (department number 1-9), OR two values ​​/ two numbers (10-99), OR three values ​​/ numbers (100-999). A combination of these will not work.
      The reason is that we have to add numbers 0 and 9 according to how many more numbers the projects have than departments in the formula – ‘{Parameters.Department}00′:'{Parameters.Department}99