Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouping Values in a Report??


Grouping Values in a Report??

Author
Message
a4apple
a4apple
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 406
Hi,

I have an SSRS report to build that looks like this.

Name Count Total
BIKE COMPANIES 50 3000
HELMET COMPANIES 10 200

The problem is I have 5 different bikes Companies (BC1, BC2, BC3, BC4 and BC5) and the same with Helmet Companies. I am pulling this data from the CUBE.
When I pull data from the Cube I see it as

BC1 15 400
BC2 15 1600
BC3 0 0
BC4 20 1000
BC5 0 0
HC1 1 20
HC2 39 165
HC3 8 10
HC4 0 0
HC5 2 5

Now, my task is to group BC1, BC2, BC3, BC4, BC5 as one group and HC1, HC2, HC3, HC4, HC5 as one group and get the report as required. Please suggest me a way or a good tutorial to work. I am totally new to SSRS and SSAS, so please bear with my terminology. I know there is something called NamedSets, but is that the way we do it?

Thanks in advance

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
H4K
H4K
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 567
You could add a calculated field on which you should group on.
You can use mid function in the report expression for the new field and extract only first 2 words like 'BC' AND 'HC' from the fields whose values are BC1, BC2,etc.

If you can google for 'add calculated field in SSRS' you will find the tutorial and same for mid function.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
davdam8
davdam8
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 668
Can you show us the SQL code?
I had a similar problem, but what I wanted was to group 2 different fields, and all I had to do was to add an UNION operator.
So this is a shot in the dark, if you have an UNION operator just remove it.
vani_r14
vani_r14
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 410
Hi there

can you please clarify - if my understanding is correct -

You would like to group BC1, BC2, BC3, BC4, BC5 as one group

if that is the case then you need to add a calculated field. You can do it either in your sql query or in your report

Or

Would you like to have them listed as individual groups

Cheers
a4apple
a4apple
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 406
vani_r14 (9/19/2012)
Hi there

can you please clarify - if my understanding is correct -

You would like to group BC1, BC2, BC3, BC4, BC5 as one group

if that is the case then you need to add a calculated field. You can do it either in your sql query or in your report

Or

Would you like to have them listed as individual groups

Cheers


Hi Vani,

Yes I want to add them up as a group. I am using a cube to get this data from so can you tell me based on that? I know its like a CASE statement in SQL but I am not sure what to do if coming from a cube.

Thanks

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search