Grouping Values in a Report??

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Grouping Values in a Report??

Grouping Values in a Report??

Rate Topic

Display Mode

Topic Options

Author

Message

a4apple

Posted Friday, September 07, 2012 6:28 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:05 PM
Points: 39, Visits: 87

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

Post #1356319

H4K

Posted Monday, September 10, 2012 12:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1356572

davdam8

Posted Wednesday, September 12, 2012 5:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 156, Visits: 567

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.

Post #1357916

vani_r14

Posted Wednesday, September 19, 2012 4:13 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:48 PM
Points: 123, Visits: 399

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

Post #1361683

a4apple

Posted Thursday, September 20, 2012 1:08 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:05 PM
Points: 39, Visits: 87

vani_r14 (9/19/2012)

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

Post #1362260

« Prev Topic | Next Topic »

Permissions