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:
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.
If you are having issues with the Excel calculations when executing the report, you can use the information below to determine how the cells are expanding.
The OSRRef formula has some extra parameters, such as OSRRef(“Cell”,2). The second parameter can have four values; 0,1,2,3 which gives us cell reference for expanding columns OR rows.
Here are the definitions for the second parameters:
0 = auto (which is the same as the regular =OSRref(Cell) formula)
1= Expanding on rows OSRRef(“Cell”,1)
2= Expanding on columns OSRRef(“Cell”,2)
3= Expanding on both rows and columns OSRRef(“Cell”,3)
These formulas are quite useful in large reports where the OSRRef formula is not able to find out how the cells are expanding.
Problem: I rapportdefinisjoner så benyttes Excel-formelen INDIREKTE for å hente opp verdier på tvers av arkfaner. Ofte benytter man navngitte områder i kombinasjon med formelen. Dette er fordelmessig ved ekspansjoner for å vise verdier på tvers av ark. Dette fungerer fint ved kjøring i Report Designer, men ofte får man en #REF?-feil ved kjøring i OSR Portal med denne kombinasjonen.
Årsak: SpreadJS (leverandør av grensesnittet for OSR Portal) har annen logikk ved håndtering av kombinasjonen INDIREKTE og navngitte områder enn hva Excel har.
Et navngitt område kan enten opprettes med Område:Arbeidsbok eller Område:Ark. Generelt, er det ikke mulig å lage flere navngitte områder med samme navn dersom den er satt med Område:Arbeidsbok Det kan lages flere navngitte områder med samme navn dersom den er definert med Område:Ark (gitt at de legges på ulike ark).
Man kan benytte seg av Navnebehandling for å legge til navngitte områder, hvor Område kan defineres. Hvis man ikke legger disse til via Navnebehandling (direkte i Excel-arket) så vil de automatiske legge seg med Område:Arbeidsbok.
Det er ikke mulig å endre Område på allerede opprettede navngitte områder. Disse må slettes og defineres på nytt.
Eksempel tatt fra Navnebehandling. Scope = Område
For å henvise til navngitte områders og generelt andre ark i RD så benytter man INDIREKTE-formelen i excel. Hvordan man skal hente opp navngitte områder avhenger av hvilke Område det aktuelle navngitte området har
Eksempel 1:
Navngitt område: Omløpsmidler ; Område: Workbook.
Hentes via formel: INDIREKTE(“Omløpsmidler”)
-> Vil fungere
Eksempel 2:
Navngitt område: Omløpsmidler ; Område: Ark2
Hentes via formel: INDIREKTE(“’Ark2’!Omløpsmidler”)
-> Vil fungere
Eksempel 3:
Navngitt område: Omløpsmidler ; Område: Workbook.
Hentes via formel: INDIREKTE(“’Ark2’!Omløpsmidler”)
-> Vil ikke fungere, #REF?-feil i portalen
Eksempel 4:
Navngitt område: Omløpsmidler ; Område: Ark2
Hentes via formel: INDIREKTE(“Omløpsmidler”)
-> Vil ikke fungere, #REF?-feil i portalen
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:
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.
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;
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;
If OneStop Reporting Designer doesn’t show when Excel is open, follow the steps:
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:
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.
OSR Designer has a search area, where you can search after attributes.
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 FALSE. 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. |
See the Microsoft Office documentation (external link) for more information.
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.
Use IFERROR:
=IFERROR(E7+E8,0)
Result:
Q: I have Office 365 and want to install OSR Report Designer, but the installation fails with an error message saying “OSR Report Designer v5 cannot be installed on systems without Microsoft Excel 2013 or higher“.
A: This is probably because you have the Office web application – and not the desktop version. OSR Report Designer requires you to install the desktop application. You can check this by going to Control panel >> Add / remove programs. Can you see the Microsoft Office installed there? If not, then you probably have the web application. To install Report Designer, you must first install the Desktop applications for Microsoft Office.