Frequently Asked Questions about Designer

  • I receive an “Object reference not set to an instance of an object” error while working with Drill-to

    If you encounter an error while working with a drill-to, make sure to check that it has a filter (as it doesn’t inherit filters from the row).

    Otherwise it may attempt to show too much data, leading to a time-out problem and a resulting “object reference not set to an instance of an object”.

  • How to 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.

    =INDIRECT(“Sheet!Cell”)

    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

    =INDIRECT(“Sheet2!B5”)

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

    =INDIRECT(“Test!Cell”)

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

  • How do I add period functions in Designer 5?

    Compared to 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:

    • Drag period into the report.
    • Add expaning on the column.
    • Go to Period function:
    • 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.

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

    Use IFERROR:

    =IFERROR(E7+E8;0)

    Result:

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

    =INDIRECT(“Sheet!Cell”)

    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

    =INDIRECT(“Sheet2!B5”)

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

    =INDIRECT(“Test!Cell”)

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

  • Useful formulas in Excel

    SumIf Summarizes the given cells according to the defined condition/logical test.
    Vlookup You can use Vlookup to 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 UNTRUE. For example, the formula =IF(A1>10;”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.