March 6, 2012 at 5:19 pm
Hi All,
I've a table with data similar to below. One point to note here is that each category (X,Y in below example) will have a column with subcategory null and NoOfItems = sum of the no of items in the subcategory field.
For instance, if you look at the first row ('X',null,35), 35 is the sum of the no of items of its subcategories 10(a) + 5(b) + 20(c).
Similary the null row for Category Y, has number of items 45 which is sum of 20(p) + 25(q)
CREATE TABLE #temp(Category VARCHAR(10), subcategory VARCHAR(10),NoOfItems INT)
GO
INSERT INTO #temp VALUES('X',null,35)
INSERT INTO #temp VALUES('X','a',10)
INSERT INTO #temp VALUES('X','b',5)
INSERT INTO #temp VALUES('X','c',20)
GO
INSERT INTO #temp VALUES('Y',null,45)
INSERT INTO #temp VALUES('Y','p',20)
INSERT INTO #temp VALUES('Y','q',25)
GO
SELECT * FROM #temp
When I create a SSRS report and put subcategory as child group of Category and enable toggling of subcategory by Category i get below when expanded.
Categorysubcategory NoOfItems
X 35
a 10
b 5
c 20
Y 45
p 20
q 25
When I collapse, I'm getting it as below. in which case NoOfItems values are incorrect below when collapsed the report is thinking that first (null) row for each category as it subcategory and adding that to the below subgroup NoOfitems.
Categorysubcategory NoOfItems
X 70
Y 80
Now, I'm wondering if there is any way to get the results as below when collapsed instead of rolling up all the items under subcategory?
Categorysubcategory NoOfItems
X 35
Y 40
Sorry for such a lengthy message. I appreciate if someone could help!
March 6, 2012 at 6:03 pm
Please see the attachment for screen shots and step by step instructions. Essentially:
Create the dataset
Insert a matrix
drag Category field into Row group
drag NoOfItems into Data cell
drag subcategories between Category and the data cell, making sure it creates a new grouping
set the visibility properties for the subcategory group to toggle on the category field
March 7, 2012 at 5:18 pm
You are really awesome!! I've never seen this detailed explanation for a question on forum with screenshots. I owe you a lot!
Thanks again!
March 8, 2012 at 1:29 pm
Hi,
When I elimiate NULL values using WHERE clause in the data set or by creating filter expression, i dont get that subcategory = NULL row on the report. I would like to get that one as well on the report. When i collapse this is what i want to get
Category | Subcategory | NoOfItems
X | | 35
Y | | 45
Along with that I would add a row totals column I would like to get the below when collapsed
Category | Subcategory | NoOfItems | Totals
X | | 35 | 35
Y | | 45 | 45
March 8, 2012 at 3:20 pm
I'll be in a meeting for a few hours. I'll try to take a look at this when done.
March 8, 2012 at 8:15 pm
Thank you so much!
March 13, 2012 at 9:10 am
Hi,
Just wanted to check if there is any way to achieve this. Thanks a lot in advance for your help!
Thanks,
March 15, 2012 at 5:59 pm
Darn it! I'm sorry. Lost track.
OK - continue where we left off. I assumed that you wanted to eliminate the nulls because the sample data that you presented didn't jive with your required output. If you add the Null subcategories, your totals will be for X: 35 + 10 + 5 + 20, or 70. In your results, you have 35 as the total for X. is the NULL supposed to be its own subcategory, or the total for the category?
XNULL35
Xa10
Xb5
Xc20
YNULL45
Yp20
Yq25
Either way, if you want the Null subcategory to be included in the groups, don't filter out the Nulls. I'd suggest something in the sub-category group label expression, to label it other than NULL:
=iif(isNothing(Fields!subcategory.Value)=True, "Empty Sub", Fields!subcategory.Value)
To add the subtotal column, right click in the NoOfItems textbox, select Add Total >> Column
March 19, 2012 at 1:42 am
Thanks for the reply.
I've added Column group total and getting the totals correctly when the report is fully expanded. But when collapsed i'm getting the total for X category as 70 (35+10+5+20) and Y category as 90 (45+20+25). Is it possible to get the totals as 35 and 45 when collapsed?
Thanks so much
March 19, 2012 at 7:54 am
Why do you need the Null category at all? Does it exist simply for roll up? If it is just for roll up then drop the null category and roll up your detail as you normally would with SSRS.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply