January 23, 2014 at 9:50 pm
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]
January 23, 2014 at 9:55 pm
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.
January 24, 2014 at 8:31 am
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.
January 24, 2014 at 9:31 am
Hey, I gave your tip a little more thought, tried it and it did the trick!
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply