Group BY Question

Question

Group BY Question

scotsditch

Ten Centuries

Points: 1020
More actions
January 23, 2014 at 9:50 pm

#281461

Hi, I'm working on a query for an SSRS report dataset.
In the report I group by [Category]
I have the query below which gives me the correct "Request Count" when I group by Category
]SELECT
[Category]
,ISNULL(Count(distinct([RawReqTableID])),0) as "Request Count"
FROM [Chase].[dbo].[DataSource]
GROUP BY [Category]
In other parts of the report I need to be able to group by other fields. So I created the query below which groups by additional fields. The problem I'm having is that when I use the query below in the ssrs report and filter only on [category] it gives much higher totals for request count. I've attached sample data, any tips on how to fix this would be greatly appreciated.
Query 2:
SELECT [Date]
,[Month]
,[Day]
,[Year]
,[MMM-YY]
,[Field]
,[Source]
,[Category]
,[SubCategory]
,ISNULL(Count(distinct([RawReqTableID])),0) as "Request Count"
FROM [Chase].[dbo].[DataSource]
GROUP BY [Date]
,[Month]
,[Day]
,[Year]
,[MMM-YY]
,[Field]
,[Source]
,[Category]
,[SubCategory]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Maybe a naive question, but why most if not all of the grouping in SSRS? Then you can drill up and down in your report. So you would create a stored procedure that had the minimum level of grouping (if you were going to do any), and then you would create a dataset based on the stored procedure. Once in SSRS, you could enable drilldown.

scotsditch Ten Centuries Points: 1020 More actions · Answer 2

Thanks for getting back to me on this.

The report I'm creating is for a client, they have a specified format that doesn't include drill down.

What I can't figure out is how I can get the correct counts if I group by only month, but how the counts get much larger if I group by several additional fields in the dataset but only use month to filter on in the ssrs row group.

scotsditch Ten Centuries Points: 1020 More actions · Answer 3

Hey, I gave your tip a little more thought, tried it and it did the trick!

Thanks