Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grouping Values in a Report?? Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 6:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1356319
Posted Monday, September 10, 2012 12:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
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
Posted Wednesday, September 12, 2012 5:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, 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.

Post #1357916
Posted Wednesday, September 19, 2012 4:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 12:03 AM
Points: 125, Visits: 408
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
Posted Thursday, September 20, 2012 1:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1362260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse