How to add groups to an already existing report SSRS 2014

rwheeler23 used Ask the Experts™
I have been given a report that is currently group by these fields:

They now what me to add not only the location and department but to have the ability at runtime to be able to select whether to print in location/department/employee/project/cost category order or department/location//employee/project/cost order.  The SQL query has been modified to handle this, however the report was originally written using groups for the
EmployeeID,Project,CostCategory values. Should I scrap those groups and start over? How do I insert the location/department groups at the beginning? Do I create a parent group? How do I get the report to sort in two different orders?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Not clear what you are asking.

IMHO! Without knowing how the current report is being used, using the query here as a baseline while starting a new report.


My apologies.  I do not do much with SSRS and when I do grouping has always been confusing. After spending over four hours on this last night I believe the proper way to do this is to start by dropping the table onto the report. You then either add groups above or below that table. This will create the row group detail. Down there you then click on the respective group and click Add Total either above or below the group.  What I still do not understand is the parent/child relationship. I see reports where there is an extended left parenthesis that goes up several rows. I was never able to accomplish this. All I get are all the left parentheses on the same row.  The required result is to how subtotals within subtotals within totals. Can you point me to any videos that demonstrate the proper way to accomplish this?
Someone created the SSRS report by:
1. adding at least one data-source
2. connecting the data-source to one or more datasets
3. they inserted at least one tablix on the report
4. dragged one/more fields from dataset to the tablix (tablix is now bound to that dataset; tablix has focus)
5. under row-groups is the (Details) group:
    a. they right-clicked on (Details) group, clicked add-parent-group, and picked EmployeeID from a fields-list**
    b. they right-clicked on (Details) group, clicked add-parent-group, and picked Project from a fields-list**
    c. they right-clicked on (Details) group, clicked add-parent-group, and picked CostCategory from a fields-list**

Now you are asked to add more groups, but you don't want it to go directly above Details, you want them to go above EmployeeID: right-click on EmployeeID, click add-parent-group, and pick Location from a fields-list**.  Right-click on Location, click add-parent-group, and pick Department from a fields-list**.

** add a check-mark to add-header, and add-footer; if you don't need them then they are easy to delete, but difficult to insert later

If you click on a group under row-groups, the group will be highlighted in the tablix.

If you right-click on the left-most band of a row then you can pick an option for row-visibility, you can add an expression on whether or not you want to show a group header/footer/detail.

Aggregate functions are based on where you place them, if you copy the expression containing the aggregate-function to each group-footer then each expression acts on that group (scope).

Sorting is at the tablix level, you can graphically add one or more fields (or expressions) to sort.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial