SSRS Row Groups and Details - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS Row Groups and Details

Author

Topic

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-19 : 09:44:24

I have a new report I'm working on. It's basically a matrix. The input columns are like this:

Project Name
Project Start State
Project Status
Project Phase Name
Project Phase Start date
Project Phase Status
Actual Cost
Month

The Column group is Month on the sum of the Actual Cost. No surprise or question there. My question is about the row groups. Even though there are six columns before the Actual Cost, there are really only two logical groupings:

Project (Name, Start Date, Status)
Phase (Name, Start Date, Status)

I'm trying to figure out how do do this in SSRS. If I want to use just two row groups, how do I add the extra details (e.g. date and status) to each of those groups?

Or, must I define six row groups?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 02:20:27

you can add the groups on composite column combinations
ie for each of groups add all the columns which are to be a part of it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-21 : 09:10:34

quote:
Originally posted by visakh16

you can add the groups on composite column combinations
ie for each of groups add all the columns which are to be a part of it

That's what I hope to do. I just can't see how to do it. I have a workaround:

1. Add each column as a group
2. Delete the groups that are part of the logical groups but keep the column, that is:

a. add Project Name, Projert Start State, Project Status as groups
b. Delete the grouops Project Start State, Project Status, but KEEP the column.

This approach gives me the desired effect, but seems backwards to me.

Can you tell me how to this part: "for each of groups add all the columns which are to be a part of it
"

I just can't seem to find it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 09:29:29

you can right click on groups shown on bottom of report and click properties. it will show tab with columns. you can click add button and add as many fields as you want to be part of the group.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-21 : 10:12:17

quote:
Originally posted by visakh16

you can right click on groups shown on bottom of report and click properties. it will show tab with columns. you can click add button and add as many fields as you want to be part of the group.

Really? I don't see that. I right-click on row group and I see these tabs:

General, Page Breaks, Sorting, Visibility, Filters, Variables, Advanced.

No tab with columns, unfortunately.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 06:49:23

quote:
Originally posted by gbritton

quote:
Originally posted by visakh16

you can right click on groups shown on bottom of report and click properties. it will show tab with columns. you can click add button and add as many fields as you want to be part of the group.

Really? I don't see that. I right-click on row group and I see these tabs:

General, Page Breaks, Sorting, Visibility, Filters, Variables, Advanced.

No tab with columns, unfortunately.

which SSRS version are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-22 : 11:52:01

2008 R2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-23 : 10:03:41

see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-25 : 10:30:00

OK -- Tried that. It's not really what I was looking for.

I'm looking to add the "extra" columns as data that is just "carried along" with the group.

e.g. the grouping is on Project Name. However I know that at least two other columns map 1x1 with the name: Project Start Date and Project Status.

Adding them in the grouping tab as per your suggestion appears to extend the grouping expression, but I don't need that. I only need to show the two extra fields at the same level as the Project Name, since they are just additional data about a project.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 13:38:56

quote:
Originally posted by gbritton

OK -- Tried that. It's not really what I was looking for.

I'm looking to add the "extra" columns as data that is just "carried along" with the group.

e.g. the grouping is on Project Name. However I know that at least two other columns map 1x1 with the name: Project Start Date and Project Status.

Adding them in the grouping tab as per your suggestion appears to extend the grouping expression, but I don't need that. I only need to show the two extra fields at the same level as the Project Name, since they are just additional data about a project.

Unless you add them to group what sense does it make to show the value directly? As once you group by a field effectively you're merging multiple rows into a single row. In that case if you want to show any of the other column you need to apply some kind of aggregation to them like Min,Max,Sum etc which will give you only single value. If you want to show all their values then they also have to be a part of the group.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-26 : 09:16:31

quote:
Originally posted by visakh16

quote:
Originally posted by gbritton

OK -- Tried that. It's not really what I was looking for.

I'm looking to add the "extra" columns as data that is just "carried along" with the group.

e.g. the grouping is on Project Name. However I know that at least two other columns map 1x1 with the name: Project Start Date and Project Status.

Adding them in the grouping tab as per your suggestion appears to extend the grouping expression, but I don't need that. I only need to show the two extra fields at the same level as the Project Name, since they are just additional data about a project.

Unless you add them to group what sense does it make to show the value directly?

The business user wants to see the value. Anyway I discovered a method:

1. add the Project Name as a group
2. Right-click on the Project Name textbox, and select Insert Right Inside Group
3. Choose the Project Start Date column

Do the same with the Project Status. This produces (albeit a little clumsily) the result the user wants.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources