SSRS Group Total does not match Excel
-
Am trying to create an SSRS report based off an Excel query. The source is an SSAS database. The hierarchy is a parent-child account structure, with the rollup controlled by unary operators. The Excel correctly uses the number is the sales line. This is what all the other numbers roll up to. SSRS adds all of the numbers in the column, including those hidden by collapsed hierarchies. I have tried unsuccessfully several methods to just have it show the amount in the Sales line. Any suggestions?
-
Screen shots aren't really going to help here, they don't show the full picture.
What does you dataset that SSRS look like, are you able to provide sample data and/or your rdl file? I suspect that your groupings are incorrect, but I've no idea what your data looks like, or how you've set up your grouping.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I don't know what information can be gathered from an .rdl file, so I'm hesitant to provide one. Assume the groups are set up correctly as the SSAS cube has been in use for a long time. It's the report that's new, and in any case I have tried many different approaches. I'm not even certain it's possible, but it seems like it should be possible. Have you created a report under similar circumstances and had the totals line report correctly? If so, what formula did you use?
- RonKyle - Tuesday, January 24, 2017 12:31 PM
.rdl files don't give any data, they just consist of the report layout, and where to get the data.
As for creating a report that works the way you want it to, yes, many, and i've not had a problem. The formula would simply be something like =sum(Fields!GWP.Value). The Grouping does all the work for you.
If you can give me a little while, I can probably create a sample report for you.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, January 24, 2017 12:59 PM
The where to get the data would seem to me sensitive. It occurred to me that by proper setup that you may have been referring to the recursion entries. If so, they are correctly set up. The parent child aspects functions properly. But it's as if the unary operators are not there. What you have is almost exactly the formula that is entered in the report. Only the GWP is different, and that's just the column name, so that can't be the issue. Just want to clarify again that I'm talking about an SSAS parent-child relationship. If so, is your total inside or outside the group, and is it before or after. I've tried both ways for the first one, and the second one seems to me to not be important for this.
I'm not in a hurry so if you can create a small sample report to illustrate what needs to be done I can wait.
-
Ok, I've attached a sample file to this post, with groupings. You'll need to amend the DataSource and dataset to use a valid server and dataset, but other than that it'll run.
Feel free to check the SQL inside the dataset, however., this is what is is running:
CREATE TABLE #Sales (SaleID INT IDENTITY(1,1),
StoreSection VARCHAR(20),
DepartmentName VARCHAR(20),
SaleValue DECIMAL(12,2));INSERT INTO #Sales (StoreSection, DepartmentName, SaleValue)
VALUES ('Parts', 'Revenue', 44.00),
('Parts', 'Revenue', 100.00),
('Parts', 'Revenue', 250.00),
('Parts', 'Revenue', 1050.00),
('Parts', 'Revenue', 2500.00),
('Parts', 'Revenue', 99.00),
('Parts', 'Revenue', 708.00),
('Parts', 'Revenue', 990.00),
('Parts', 'Revenue', 403.00),
('Parts', 'Inter-department', 0.00), --As I am not creating proper look up tables, just a simple 0 value
('Parts', 'TEPS', -66.00),
('Parts', 'TEPS', -100.00),
('Parts', 'TEPS', -500.00),
('Parts', 'TEPS', -200.00);SELECT *
FROM #Sales;DROP TABLE #Sales;
Please let me know if you have any questions, but hopefully, this'll answer show where you're going wrong.
Notice, the important part, SSRS is doing all of the aggregation, this is very important, and if you are doing any outside of it (which I suspect you have done), this is where you start to get results like you have.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, January 24, 2017 1:27 PM
Actually, I think you have that backwards. SSAS has already done all of the aggregation, and you shouldn't be doing it in SSRS. Reporting off of cubes is different from reporting off of transactional data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Reporting off of cubes is different from reporting off of transactional data.
Yes, we have found that to be the case. The solution provided seems to use transactional data. SSAS is obviously aggregating the data correctly, because it displays correctly in Excel. And if we remove the parent-child dimension from the report and only use a regular dimension, the totals perform as expected. We have found using an SSAS parent-child in SSRS to be a "challenge." This is the latest one.
-
This turned out to be more complicated than it should be.
=IIF(SUM(Fields!Prior_Year_Actual.Value)=0, 0, (1 - SUM(Fields!Actual.Value)/SUM(Fields!Prior_Year_Actual.Value))*-1) -- For all except grand total
=IIF(SUM(Fields!Prior_Year_Actual.Value)=0, 0,(SUM(Fields!Actual.Value)-SUM(Fields!Prior_Year_Actual.Value))/SUM(Fields!Prior_Year_Actual.Value)) -- For grand total onlyThe same formula did not work in all cases. Although SSRS could have handled the divide by zero, we also discovered that without the zero check the report after export to Excel would open needing a repair.
-
So funny to see this post today! Just yesterday I had what may be the same problem. I have a Tabular model where I am aggregating a distinct count. In Excel it works fine. However, in SSRS the numbers don't match. Have you checked out the Aggregate function in SSRS? This tells the SSAS engine to do the calculation rather than SSRS doing it.
-
I did try to aggregate it, but it didn't like that command. I'd be interested to see your function equation with that command.
-
Hey Ron,
Sorry I missed the email notification on your post. Here is an example of what worked for me.
=IIF(Aggregate(Fields!RM_Total_Distinct2.Value) = 0, 0, Aggregate(Fields!RM_Total_Distinct_Complete.Value) / Aggregate(Fields!RM_Total_Distinct2.Value))
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply