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
Thank you Greg.
I’ll remove some sensitive data in the report and upload it later.
Hello @WendyZhang, Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:
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
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
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)
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
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
Bumping this post for more visibility.
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
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!
Hi John,
Thank you for the detailed solution. I’ll try it out and respond to the post.
Wendy
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:
Here are the V2 modified Excel and RDL files:
scenario.xlsx (14.1 KB)
Wendy’s Test Scenario.rdl (44.3 KB)
4 Likes
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.
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
Bumping this post for more visibility
1 Like
Hi @wendyzhang! We noticed that your inquiry was left unsolved for quite some time now.
We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!
1 Like
Thank you for all your help and suggestions.
We have decided to create the report in Power BI to resolve the issue in calculation.
Wendy
1 Like
Hi @WendyZhang,
The paginated format requires the query change so that all detail data is pre-aggregated, which then allows the scope-specific aggregations to work as required. I’m glad you were able to pursue a multi-dimensional solution.
John
1 Like
Absolutely. We’ve figured the query needs to be optimized first.
Thank you John.
Wendy