Total of unknown categories in SSRS 2005

Question

Hi,

I am working with SSRS2005. I have requirement to display total in the footer. We have to display the total of each category. What I used to do is, write expression for all category names and hide those totals that are not having any value in the current selection.

Mango Count = sum(iif(fields!Category.Value = “Mango”,0,1))
Apple Count = sum(iif(fields!Category.Value = “Apple”,0,1))

However, in the new requirement, I don’t have the knowledge of categories. It could be any number of categories. Is it possible to write an expression for this?

Please help

Thanks

Lijo Cheeran Joseph

Answer 1

Remove the footer from your table. Create a second dataset that sums by group (Select Fruit, Count(*) AS FruitCount From Fruits Group by Fruit), then add another table below your current table which uses this new dataset and displays the results.