Grouping Values in a Report??

Question

Grouping Values in a Report??

a4apple

SSCrazy

Points: 2620
More actions
September 7, 2012 at 6:28 pm

#393136

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 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

H4K Hall of Fame Points: 3374 More actions · Answer 1

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 Hall of Fame Points: 3620 More actions · Answer 2

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 SSCrazy Points: 2843 More actions · Answer 3

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 SSCrazy Points: 2620 More actions · Answer 4

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 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.