SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS Row Groups and Details
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gbritton
Constraint Violating Yak Guru

410 Posts

Posted - 11/19/2013 :  09:44:24  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 11/20/2013 :  02:20:27  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Constraint Violating Yak Guru

410 Posts

Posted - 11/21/2013 :  09:10:34  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/21/2013 :  09:29:29  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Constraint Violating Yak Guru

410 Posts

Posted - 11/21/2013 :  10:12:17  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/22/2013 :  06:49:23  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Constraint Violating Yak Guru

410 Posts

Posted - 11/22/2013 :  11:52:01  Show Profile  Reply with Quote
2008 R2
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/23/2013 :  10:03:41  Show Profile  Reply with Quote
see



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

Edited by - visakh16 on 11/23/2013 10:05:33
Go to Top of Page

gbritton
Constraint Violating Yak Guru

410 Posts

Posted - 11/25/2013 :  10:30:00  Show Profile  Reply with Quote
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.

Edited by - gbritton on 11/25/2013 10:38:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/25/2013 :  13:38:56  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Constraint Violating Yak Guru

410 Posts

Posted - 11/26/2013 :  09:16:31  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000