Paginated Report

Power BI Software & Tools
1

Hello,

Not sure if it is the correct category to ask paginated report questions.

An expression is created for a calculated column as below.

=IIf(
(Fields!Deductible_Override.Value - Sum(Fields!Total_Deductible.Value)) > Sum(Fields!NetPaid.Value)
,Sum(Fields!NetPaid.Value)
,(Fields!Deductible_Override.Value-Sum(Fields!Total_Deductible.Value))
)

e.g. iif(A -Sum(B) >Sum(B), Sum(B), A)

The expression is grouped by Claim# column. Fields!Deductible_Override.Value is a static value per claim#. On the row level it’s calculated correctly per claim but the on the group lever the sum total is wrong on the column level.

Any ideas?

Thanks in advance,

Wendy

2

Hi @WendyZhang. It’s very difficult to diagnose your issue from just code and without supporting files and screenshots. Please upload your work-in-progress Report Builder file and a marked-up screenshot of the visual in question showing the current state and your desired outcome.
Greg

3

Thank you Greg.

I’ve attached the file.

scenario.xlsx (12.6 KB)
.

4

Sorry. Please use this one.

scenario.xlsx (12.6 KB)

5

Hi @WendyZhang. Thanks for the Excel mock-up, but unfortunately it is not sufficient to illustrate your issue. Please upload your work-in-progress Paginated Report file.
Greg

6

Thank you Greg.

I’ll remove some sensitive data in the report and upload it later.

7

Hello @WendyZhang, Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

8

Hi Greg,

Thank you for the video. However the company has certain policy on uploading the sensitive data online.

Anyways, the issue is the column contains the expression mentioned above. It works fine on row level calculation but the total on the column at the end is not working as expected. I basically sum the expression which may not be the right thing to do.

Thanks,

Wendy

9

Hi @WendyZhang. The only thing I can think of to move forward on your issue is for you to create a stand-alone sample dataset in Excel and paginated report that only references this dataset, then upload both the .xlsx and .rdl file. Once we have those, along with your screenshot of the current visual and the desired outcome (marked-up screenshot), we could continue our investigations.
Greg

2 Likes
10

Hi Greg,

Per your suggestion, I have created the dataset in Excel and the paginated report based on the Excel data.

Hope the information provided here could help explain the issue better. Please do let me know if you need further info.

Thank you so much in advance.

Wendy
scenario.xlsx (13.1 KB)
Wendy’s Test Scenario.rdl (32.4 KB)
Results.xlsx (12.4 KB)

11

Hi @WendyZhang.

Your expression can calculate row values correctly as they all have a values for the claim deductible amount and the total discount. The total is calculated using your second IF condition (where the difference between the claim deductible amount and the total discount is zero) as returns zero.

One way around this might be to do the calculation in your “My Calculation” column in your data source, then add the new field to your dataset and report; the automatic grouping and summing should then return the correct value.

I’m assuming you chose a paginated report as there is a large data volume; using Power BI instead would make this easy.

Hope it helps.
Greg

12

Morning Greg,

Thank you for the prompt response.

You are right Power BI is much easier to handle the calculation, however paginated reporting is required here. I’ll see what we can do next.

Appreciated your help.

Wendy

13

Bumping this post for more visibility.

14

Hi @WendyZhang,

Given my work with SSRS (i.e. paginated reports) over many years, I gave this a go. The short answer is that it does not appear to be possible to calculate the grand total for an expression field of the complexity you are using because paginated reports cannot determine the correct scope for a combination of a specific field and an aggregate. Scope is equivalent to “context” in Power BI. As an FYI, I develop paginated reports in Visual Studio.

I first made some cleanups in the provided data to get a local refresh to work as expected, including removing the totals row in Excel and converting the Claim Deductible Amount to a number. I also removed the calculated field in the report dataset and renamed the Claim Deductible Amount dataset field to Deductible_Amount. Because RDL/SSRS can be a bugger, I also had to monkey with the RDL in Notepad to fix up the dataset field datatypes: it would not refresh them in the RDL no matter how many times I refreshed the data source. All three remaining money columns were set to datatype “System.Decimal” in the RDL.

I gave the Details group a group name of “grpDetails” just to make sure I had an established scope name.

The following link describes the “standard” approach to sum an expression column (scroll down to the part that starts with “UPDATE:”). This did not work, even with named scopes. My total kept ending up as $533,367.24. For the record, I have used named scopes quite often in the past, and even tried group and report variables with this situation to no avail.

Here are the modified Excel source file and RDL report definition file that I worked on. Check the Expression for the “My Calculation” column’s Totals cell for the use of Sum() and the named scopes.

scenario.xlsx (14.1 KB)
Wendy’s Test Scenario.rdl (44.9 KB)

Based on what I have seen so far, I think you might be able to make this work IF you change the data source to have one row per group. This means removing the Accounting Date column and adding up the Net Paid and Total Discount values per grouping level currently used in the report. That will simplify the My Calculation expression and thus allow the Sum() method described in the article above to work.

John C. Pratt

1 Like
15

Hi @WendyZhang, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

16

Hi John,

Thank you for the detailed solution. I’ll try it out and respond to the post.

Wendy

17

@WendyZhang,

I got it working with the existing data and RDL! My brain was a little fried from the heat yesterday, so of course the answer popped into my head once I cooled down - you can do the required pre-aggregation in the SELECT statement out of the Excel file.

First I had to rename the Excel columns to the desired column names for the SQL statement. Then I modified the SELECT to specify the specific grouping field names and did SUMs for Net_Paid and Total_Discount. The SQL is as follows:

select Company, File_No, Claim_No, File_Year, Deductible_Amount, SUM(Net_Paid) As Sum_Net_Paid, SUM(Total_Discount) As Sum_Total_Discount
from [Sheet1$]
GROUP BY Company, File_No, Claim_No, File_Year, Deductible_Amount

Then I left the group in place in the report but deleted all the Group By fields so every group row was effectively a detail row. The group is still needed so the group totals can be calculated (i.e. it tells the paginated report that the scope for the totals row is the whole group).

Finally, I modified the detail row-level expression to get rid of the inline Sums, and then wrapped the Totals version of the expression in a Sum(). With this simpler solution design, the scope names were not needed in the expressions.

The final Totals expression looks like this (spaced out for my sanity, but the viewer is removing the indenting):

=Sum( IIf(
IIf( (Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)>Fields!Sum_Net_Paid.Value,
Fields!Sum_Net_Paid.Value,
(Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)
)
<=0, 0,
IIf( (Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)>Fields!Sum_Net_Paid.Value,
Fields!Sum_Net_Paid.Value,
(Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)
)
)
)

And the report generates the expected values for the totals:

EDNA_Paginated Report_SUM Expression Column_Results
EDNA_Paginated Report_SUM Expression Column_Results833Ă—412 20.7 KB

Here are the V2 modified Excel and RDL files:
scenario.xlsx (14.1 KB)
Wendy’s Test Scenario.rdl (44.3 KB)

4 Likes
18

Hi @WendyZhang, we’ve noticed that no response has been received from you since the 4th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

19

Morning John,

Thank you for the solution.

The data provided in the mockup is a simplified version. The real dataset is from the tabular model which is impossible to manipulate the query. Is there a way to write the expression without changing the underlying query?

Thank you for looking into it.

Wendy

1 Like
20

Bumping this post for more visibility

1 Like