Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
teleute00
Starting Member
2 Posts |
 Posted - 2012-05-02 : 23:42:23
|
| 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's a few more, but that's enough for this example.Let's say some records are:John Smith, Company ABC, Project X, Activity X, 10John Smith, Company ABC, Project X, Activity Y, 5John Smith, Company ABC, Project Y, Activity X, 8John Smith, Company FGH, Project Y, Activity Y, 6Jane Jones, Company ABC, Project X, Activity X, 4Jane Jones, Company ABC, Project Y, Activity Y, 8Jane Jones, Company FGH, Project Y, Activity X, 7Jane Jones, Company FGH, Project X, Activity Y, 6and 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 14Jane Jones Project X 10 Project Y 14Or 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! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
teleute00
Starting Member
2 Posts |
Posted - 2012-05-04 : 12:10:09
|
| It's not a hierarchy, though - the grouping are completely dynamic, based on what the user selects.I did get it figured out, though - turns out the nested groups aren't necessary. I had to go back to my earlier strategy of putting two groups on the same table, which once I fiddled with it a lot more, ended up being fine.Thanks! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-05 : 01:42:36
|
quote:
Originally posted by teleute00
It's not a hierarchy, though - the grouping are completely dynamic, based on what the user selects.I did get it figured out, though - turns out the nested groups aren't necessary. I had to go back to my earlier strategy of putting two groups on the same table, which once I fiddled with it a lot more, ended up being fine.Thanks!
ok in that case its just a matter of putting two groups and adding an expression for grouping based on parameter selection------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Double grouping + export in SSRS