January 14, 2011 at 11:10 am
I am having a real dilema with grouping and making the display of a report show what I wish to see. I want a report to have rows that are dates, and columns that are product types. However I want to show the products as drill downs on their detail. Meaning if I have a date I would see a single detail column that has a '+' you could click on to get an expansion of more items that happened on that date. I would assume you would do this grouping as a column grouping and use a matrix since it allows the column grouping and the table only offers the row grouping but I am open to hearing ideas. I keep running into I can group two sets of data but it then groups by the number combination NOT the date.
EG I have a data set that has this:
Date CR1BCR3BCR
2011/01/05112
2011/01/06123
2011/01/07112
2011/01/08011
2011/01/09134
I would like to see
GROUP1
2011/01/05 + 2
2011/01/06+ 3
2011/01/07+ 2
2011/01/08+ 1
2011/01/09+ 4
Where I could hit the '+' and expand the groups. I am sure I am doing something simple wrong as maybe my data set would be better in a relational table with integers that point to other inferences. Like I would show a date, a Product Type, an amount. I did a pivot table to do all this for a SQL report with just CSV but now that I want to go SSRS I would prefer to not have to do that if possible and use the existing function. Anyways I have example code to provide to better help someone help me.
Any help is much appreciated.
Link to zip on my dropbox to get the solution. BE AWARE my data source does not use (local) so you must change this or connection will not work.
http://dl.dropbox.com/u/7944039/SSRSExample.zip
Creating the data in SQL:
-- create some test data in the tempdb, or make your own db if you like I use Test DB myself a bit
use test; --use tempdb;
if object_id('SSRSColumnHeader') is not null drop table SSRSColumnHeader ;
create table SSRSColumnHeader (Dt Smalldatetime, CR1B int, CR3Bint);
insert into SSRSColumnHeader values ('1-5-11',1,1),('1-6-11',1,2),('1-7-11',1,1),('1-8-11',0,1),('1-9-11',1,3)
;
-- this is going to be my super simple data set for example
select
CONVERT(varchar, dt, 111) as Date
,CR1B
,CR3B
,CR1B + CR3B as CR
from SSRSColumnHeader
January 19, 2011 at 1:50 am
Hi Brett,
As attachment you find the proposed solution based on your code and rdl.
Hope this helps.
Kind regards,
Linda
January 31, 2011 at 3:06 pm
Yeah, that's very similar to what I want for your second option. The only problem is that the group of the column on the top goes by the result not the name. EG: It groups 1, 2, 3, 4 and then when you click on it it shows the breakdown.
I would like to see the Title 'CR' and then the sum by the correct dates and then be able to hit the '+' sign and expand them but have it be on the column. It seems like from what you are showing this is easier accomplished by doing Row Grouping but I would prefer Columns showing subsets that can break into ever expanding tree branches if possible.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply