1

I'm trying to create a report that allows the user to select not only a field to group by, but a second level grouping as well. To demonstrate - the records being queried are people's time sheet entries. So each record has fields such as staff (the staff member who worked the time), company (the company the time was worked for), project (the project worked on), activity (type of work done), and of course the hours worked. There are more fields, but that's enough for this example.

Let's say some records are:

John Smith, Company ABC, Project X, Activity X, 10
John Smith, Company ABC, Project X, Activity Y, 5
John Smith, Company ABC, Project Y, Activity X, 8
John Smith, Company FGH, Project Y, Activity Y, 6
Jane Jones, Company ABC, Project X, Activity X, 4
Jane Jones, Company ABC, Project Y, Activity Y, 8
Jane Jones, Company FGH, Project Y, Activity X, 7
Jane Jones, Company FGH, Project X, Activity Y, 6

and so on...

The report user can select to group by Staff, and secondarily by Project, and they'd get this:

John Smith
                          Project X                15
                          Project Y                14

Jane Jones
                          Project X                10
                          Project Y                14

Or they can choose by Project then by Activity, or whatever combination of fields they like. I've done this by nesting a table within another table, and putting grouping on both, and it works beautilfully.

EXCEPT...it doesn't export to Excel. We're on 2005, and from what I can see it still doesn't work in 2008. Not sure about after that. Regardless, there's no way we're upgrading by the time I need to publish this report.

How else can I achieve this? I'm trying to come up with some way to do the first level of grouping in the query, but even using dynamic SQL I can't come up with how to make it work for any field passed in by the parameter, since it needs to be able to return the other fields for the first level grouping.

I think a subreport would theoretically work, except that this whole thing is a subreport and I don't know if you can layer them. Plus I suspect the overhead would be awful...

Thank you!

teleute00
  • 561
  • 6
  • 25

2 Answers2

0

Try doing all the grouping in a dynamic SQL query - something like:

="select " + 
 Parameters!ReportParameter1.Value + " firstCol, " +
 Parameters!ReportParameter2.Value + " secondCol, " +
 "sum(hours_worked) total_hours from timesheets " +
 "group by " + 
 Parameters!ReportParameter1.Value + ", " +
 Parameters!ReportParameter2.Value
  • Hmmm...I tried this with just two set grouping values (project and activity, for the moment) just to test the idea of grouping in the query as opposed to on the table itself. It is working, although I'll still need to test out doing it dynamically. I guess what I didn't know/still don't understand is - does grouping in the query automatically apply to how it shows/formats itself in the table? It seems to, but I just want to be sure I get what it's doing. Thanks! – teleute00 May 03 '12 at 20:15
  • I can't figure out the syntax for making this dynamic. My understanding was that it would be something like (note - using (at) in place of at symbol in code): `DECLARE (at)TmpQuery varchar(800) set (at)TmpQuery = 'SELECT Timesheet.Minutes AS Time, ''' + (at)Group_by + ''' AS Group_by FROM Timesheet' exec((at)TmpQuery) ` (I know this isn't great for injections, but 1. I can't figure out sp_executesql in SSRS and 2. this is only on machines that aren't on the net) However, it just returns the name of my field for group_by instead of the value in that field. – teleute00 May 03 '12 at 22:17
  • Ugh. Can't figure out how to format it better than that. Sorry! Anyway, I also tried it with Parameter!Group_by.value in place of (at)Group_by, and it says 'Incorrect syntax near !'. – teleute00 May 03 '12 at 22:19
  • @teleute00: Rather than try to formulate the dynamic query in t-sql, what I was suggesting was to do all the dynamic parts in an expression for the query in SSRS (as above - the two parameters correspond to the two grouping items). So for example, if the two parameters had the values `userName` and `companyName`, the query string would evaluate to `select userName firstCol, companyName secondCol, sum(hours_worked) total_hours from timesheets group by userName, companyName` . The fields available to the report would always be `firstCol`, `secondCol` and `hours_worked`. –  May 04 '12 at 05:48
  • For now I got it working the way above, but I think I'll come back to this when I have time because I'm curious whether it would be faster to group in the query rather than the table. So basically you're saying - don't use Dynamic SQL as such...basically treat the SSRS dataset window as if I were formulating the query in PHP or some other scripting language? (Sorry if that's a dumb question - I'm very new to SSRS, and just picking things up as I go...) – teleute00 May 04 '12 at 16:01
  • @teleute00: That was pretty much what I meant. –  May 04 '12 at 17:04
  • gotcha. I definitely misread, then. I didn't realise you could formulate the queries that way! I'll definitely have to play with it more when I have time. Thanks! – teleute00 May 04 '12 at 20:19
0

You shouldn't need to use nested tables to achieve this. You just need a single table with two groups and two parameters "Group By 1" and "Group By 2" where the parameter values are the names of columns in your dataset.

e.g. Group By 1 parameter

value         label
------        -------
staff         Staff Member
project_name  Project

You then set the Group expression for the first group to:

=Fields(Parameters!GroupBy1.Value).Value

and the group expression for the second group to:

=Fields(Parameters!GroupBy2.Value).Value
Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • I should add that this assumes your data is not pre-grouped, i.e. your report dataset looks like the records in your question. – Nathan Griffiths May 04 '12 at 06:18
  • How do you put two groupings without nested tables? That was the only way I could get it to work... – teleute00 May 04 '12 at 15:10
  • Whoops - never mind. Got it sorted. It's basically what I'd been trying to do originally, but couldn't get it quite right. I gave up because I didn't even know it would work. Now that I know it's the right direction, and now that I'm a bit more familiar with SSRS, I got it working as needed. Thanks! – teleute00 May 04 '12 at 15:56