filtered total on a table
-
I am running this version, in case it matters.
Microsoft SQL Server Reporting Services Version 11.0.6251.0
I will describe my data using a simple example. Let's say my dataset returns 3 rows.
I create a table and link it to this dataset. And I add a total at the bottom... very simple. When I run the report, it shows the 3 rows and a total row that has the sum of the numbers associated with each row.
Next, I want to filter the data, WITHOUT editing the SQL in the dataset. So I add a filter on the "Details"... I am talking about the thing that appears under Row Groups section of the report designer. There, I right click and create a filter to filter out one of the 3 rows.
When I run this report, I see the row filtered out correctly (so now I see only 2 rows), but the total still shows the total for all 3 rows.
How do I get the total to be the total of the filtered detail?
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
In your total columns - you are referencing the dataset column, which totals across the full dataset. Try referencing the text box name instead of the dataset column.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I'm not sure what you mean when you say "In your total columns - you are referencing the dataset column..."
Attached is a screen shot of the Design screen. I am using a table. The last 3 columns are what I am summing. The way I am summing them is by right mouse clicking on the thing that say "Details" under the Row Groups section, and choosing "Add Total After".
If I look at the expression in each of these total boxes, it simply says =Sum(Fields![columname].Value)
Now.... in that thing that says "Details" you can apply a filter. I wanted to leave out certain values of [Operating_Expense_Buckets] so I made a filter in the "Details". BUT, the total boxes still show the total of everything. It doesn't seem to know about the filter.
Your reply makes me wonder if you understood my question so I am basically repeating with a picture.
-
Yes - I understood your question. There is a difference between the name of a textbox - and the column name coming from the dataset. In your tablix - right-click on the textbox in the detail section listed as [PcfOfAvgNAV]. Find the name of that textbox and try using: SUM(ReportItems!{textboxnamehere}.Value).
Not sure if that is allowed though - but something to try first.
If that doesn't work, then push the filtering up to the dataset instead of in the group.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply