June 7, 2012 at 5:05 am
Not sure Buried is a very good word there but I can't think of a better one so I'll try and explain.
I have a set of data that looks something like this (can't post the real data because it's sensitive)
Type SubType1 SubType2 Qty
1 1 1 5
1 1 2 5
1 2 1 7
1 2 2 6
1 3 1 4
1 3 2 5
2 1 1 3
2 1 2 5
2 2 1 6
2 2 2 5
2 3 1 9
2 3 2 5
3 1 1 7
3 1 2 5
3 2 1 9
3 2 2 4
3 3 1 5
3 3 2 6
4 1 1 6
4 1 2 5
4 2 1 3
4 2 2 5
4 3 1 6
4 3 2 7
5 1 1 9
5 1 2 5
5 2 1 7
5 2 2 5
5 3 1 2
5 3 2 3
I want a tablix that shows a row for each type but only including rows that have a given SubType1. So if, for example I want SubType1 of 2 I would see the following output:-
Type Qty
1 13
2 11
3 13
4 8
5 12
I cannot filter the dataset because it's already used in other tablixes on the report. I think my only option is to filter the group on the tablix. My data is grouped by Type which is right because that's what I want my SUM to apply across. But that means I can't filter on SubType1 because Subtype1 has already been subsumed into the main group. If I put a filter on of SubType1 = 2 I see no records because none of the grouped records have that value in there SubType1 column once they're grouped.
Is there any way I can achieve this?
June 7, 2012 at 6:58 am
Based on the data and the expected output the following should do the trick
declare @sampledata table (Type int, SubType1 int, SubType2 int, Qty int)
insert into @sampledata values
(1,1,1,5),
(1,1,2,5),
(1,2,1,7),
(1,2,2,6),
(1,3,1,4),
(1,3,2,5),
(2,1,1,3),
(2,1,2,5),
(2,2,1,6),
(2,2,2,5),
(2,3,1,9),
(2,3,2,5),
(3,1,1,7),
(3,1,2,5),
(3,2,1,9),
(3,2,2,4),
(3,3,1,5),
(3,3,2,6),
(4,1,1,6),
(4,1,2,5),
(4,2,1,3),
(4,2,2,5),
(4,3,1,6),
(4,3,2,7),
(5,1,1,9),
(5,1,2,5),
(5,2,1,7),
(5,2,2,5),
(5,3,1,2),
(5,3,2,3)
select type, SUM(qty) from @sampledata where SubType1 = 2 group by type
June 7, 2012 at 11:23 am
Sorry, I obviously wasn't very clear. The sql query is going to have to return me all the data, regardless of SubType1, because it is all used elsewhere in the report. I can't put a filter on the dataset, either by applying a where clause or by applying a filter to the dataset in the report.
The only place where I want the data filtered by SubType1 is in one particular tablix. But I can't seem to filter the data properly at that point because the SubType1 value has already been subsumed into the grouping by Type. Essentially I want to filter the data BEFORE it's grouped for the tablix and the only place I can see where I could do that would be in the dataset itself, which isn't an option because that would then filter it in all the other tablixes.
I could just create another dataset but that seems wasteful because it'll introduce another round trip to the server and, since I've had to go and get the data anyway, it would seem to make sense to filter it client side.
June 8, 2012 at 1:07 am
In the other tablix where you only want to see specfic subtype, can you not change the grouping on that tablix to group by type and subtype1, then do a filter on the tablix for a specific subtype1.
Other than that, can you not create a second dataset within the report and then use that dataset within the filtering tablix?
June 8, 2012 at 2:29 am
In the other tablix where you only want to see specfic subtype, can you not change the grouping on that tablix to group by type and subtype1, then do a filter on the tablix for a specific subtype1.
Sadly no. Sometimes I'm going to want more than 1 SubType1 included and wouldn't want to see them as separate rows. I really should have stated that up front. Sorry.
I also tried grouping it as you described and then adding a parent groupe by Type with a summary row but that doesn't work either. I get exactly the same problem as if I just had the Type grouping. It applies the filter after the grouping and, at that point the field I want to filter by has already lost any meaning. Using SQL as a metaphor, the filter is applied as a HAVING when what I want is a WHERE.
Other than that, can you not create a second dataset within the report and then use that dataset within the filtering tablix?
Yes but I was hoping to save a server trip. I've already retrieved all the data I need and it seems daft to go back to the DB to retrieve a sub set of it again. If I could create a second dataset based on my main data set rather than a query or SP that would be perfect. I can understand the implementation SSRS is offering and it makes total sense, I just want to insert an extra 'layer' in the middle.
I think at this point that I'm going to fall back on just creating a second dataset as you describe. It seems a shame returning to the database to retrieve the same data twice but at least it'll work and I'm probably being a bit anal about avoiding unneccessary traffic.
Thanks for your help on this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply