Forum Discussion
Paginated Reports DIV/0 Error
Good morning PhilipTreacy ,
First off thanks for the quick reply! I tried to use that but it doesn't work. It gave me the following error.workingnot working
The Value expression for the textrun 'Textbox10.Paragraphs[0].TextRuns[0]' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing.
A draw back with Power BI is that the expression will still try to calculate a value even though the IIF asks it not to.
I believe the answer provided by Philip is for DAX in Power BI Dashbord reports i.e. Power BI Desktop. As I understand it, in your case, you are using Power BI Report Builder (SSRS for the old folks) to build a paginated report.
Power BI Report Builder has no built in Divide function. However, you can create one.
- With your report opened in Power BI Report Builder, right click on the dark grey area surronding your report and choose Report Properties from the context menu. This will display the Report Properties dialog box.
- On the Report Properties dialog box, click the Code tab. This will display the Custom code textbox.
- In the Custom code textbox, enter the following function declaration:
Public Function Divide(ByVal Numerator As Double, ByVal Denominator As Double, ByVal AltResult As Double) AS Double
If IsNothing(Denominator) Or Denominator = 0
Return AltResult
Else
Return Numerator/Denominator
End If
End Function
Once this is created, you can call it in your Expression like this:
=IIf(Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value) = 0, "",
code.Divide(Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value/4),
Sum(Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value/4), 0))
Notes:
- When calling the function, you must put "code" before your function name.
- The function call is saying, if zero is the Denominator, return zero as the result. You can supply any alternative number you want (e.g. -1).
- I am assuming that the issue is that the second SUM in the division (the denominator) is causing the issue. Zero as the numerator should return zero.
- Multiple functions can be created in the Custom code field. Just add the latest code to the top or bottom of the field.
- This is not verified code, so anyone is welcome to improve it.
I hope this helps someone.