locked
Group on multiple fields RRS feed

  • Question

  • Is there a way to create a tablix group using multiple database fields?

    I think the old PUBS database (book sales and authors) would be a good example for this. I no longer have PUBS so here's an example of the type of SQL data:

    select 'Fiction' as FictNon ,'US' as Country, 'Online' as RetailerType, 'English' as BookLanguage, 'Horror' as Category, 'Soft' as BookCover, 12.95 as Sales
    union all 
    select 'Fiction' as FictNon ,'FR' as Country, 'Retail' as RetailerType, 'French' as BookLanguage, 'Comedy' as Category, 'Hard' as BookCover, 19.95 as Sales
    union all 
    select 'NonFiction' as FictNon ,'US' as Country, 'Retail' as RetailerType, 'English' as BookLanguage, 'Biography' as Category, 'Hard' as BookCover, 22.89 as Sales
    union all 
    select 'Fiction' as FictNon ,'CA' as Country, 'Retail' as RetailerType, 'English' as BookLanguage, 'Horror' as Category, 'Soft' as BookCover, 12.95 as Sales
    union all 
    select 'Fiction' as FictNon ,'CA' as Country, 'Retail' as RetailerType, 'French' as BookLanguage, 'Horror' as Category, 'Soft' as BookCover, 15.95 as Sales
    union all 
    select 'Fiction' as FictNon ,'FR' as Country, 'Retail' as RetailerType, 'French' as BookLanguage, 'Romance' as Category, 'Hard' as BookCover, 19.95 as Sales
    union all 
    select 'NonFiction' as FictNon ,'US' as Country, 'Online' as RetailerType, 'English' as BookLanguage, 'Biography' as Category, 'Soft' as BookCover, 12.59 as Sales



    This data is for sales of books. Each book can belong to many dimensions that we want to track. The actual data comprises several years and is over 200,000 rows. The end users want a SSRS report on the various dimensions like:

    Fiction/Non-Fiction
    Fiction    81.75
    NonFiction    35.48

    Country    Sales
    CA    28.90
    FR    39.90
    US    48.43

    BookLanguage    Sales
    English    61.38
    French    55.85

    I could create multiple datasets but that would just be querying and returning the same data over and over.

    The only way I can think of to accomplish this with one query is to create a report with many separate tablixes and set the group per tablix to group on the desired dimension. The above example output is very simplified, there is more complexity to it. If I have to use a separate tablix per dimension then any changes or error correction must be done many times. There are currently about 12 dimensions with more likely to be added in the future.

    So is there a way to create a tablix group based on multiple dataset fields?




    • Edited by RD_vb Monday, January 5, 2015 10:23 PM
    Monday, January 5, 2015 7:58 PM

Answers

  • Hi ,

    Using your Sample Value I create  SSRS Report .


    You can follow the same steps to create report.

    1. Take one Tablix and Group by using first category(dimension) as I have used FictNon column
    2. Right Click On Details form Row Group Area -> Delete Group -> Delete Group Only

    3.  Right Click On Row Group(FictNon) -> Add Group -> Add ajacent After
    4. Select you second Group (dimension).
    5. Repeat 4 and 5 step for all groups.
    6. Next column use =Sum(Fields!Sales.Value).



    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    • Proposed as answer by Vicky_Liu Tuesday, January 6, 2015 10:53 AM
    • Marked as answer by Vicky_Liu Tuesday, January 13, 2015 7:58 AM
    Tuesday, January 6, 2015 9:46 AM
  • Your designed report will look like this .


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    • Proposed as answer by Vicky_Liu Tuesday, January 6, 2015 10:53 AM
    • Marked as answer by Vicky_Liu Tuesday, January 13, 2015 7:58 AM
    Tuesday, January 6, 2015 9:46 AM

All replies

  • By default you can group on multiple columns in a tablix. The dataset can be used for multiple tablixes. You also can create one tablix with multiple row- or columngroups. Both options are possible.

    The way you describe it you have many dimensions and more stil to come, consider to create one datamart with all booklanguages and country combinations (and other dimension members) with the aggregated sales facts.. This way you have to define the report once.

    The country and booklanguage appear in the same column in the datamart with their own aggregated facts.


    Monday, January 5, 2015 10:04 PM
  • Thanks for the reply.

    If by datamart you mean Analysis Services, we don't even have that installed. That would be a whole new project.

    You did clue me into perhaps using multiple Adjacent groups in a tablix.

    Since the desired output has a lot of repetition with only the dimension changing, is there a way to reference the actual dimension being grouped? In Crystal Reports this would simply be the Group Name. If I could refer to a group name, then the various tablixes or sections by dimension could be more easily managed and updated.

    The end user really want a specific format. Column groups would be better from a developer standpoint but it's not what the boss wants.

    Monday, January 5, 2015 10:27 PM
  • I thought you ment Analysis Services (since you were talking about dimensions!). Multiple adjacedent groups wil work fine, one way or another you will have to maintain the report or the datamart when you have a new dimension that you want to add to the report!
    • Proposed as answer by Ron Verhoeven Tuesday, January 6, 2015 2:32 PM
    Tuesday, January 6, 2015 9:07 AM
  • Hi ,

    Using your Sample Value I create  SSRS Report .


    You can follow the same steps to create report.

    1. Take one Tablix and Group by using first category(dimension) as I have used FictNon column
    2. Right Click On Details form Row Group Area -> Delete Group -> Delete Group Only

    3.  Right Click On Row Group(FictNon) -> Add Group -> Add ajacent After
    4. Select you second Group (dimension).
    5. Repeat 4 and 5 step for all groups.
    6. Next column use =Sum(Fields!Sales.Value).



    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    • Proposed as answer by Vicky_Liu Tuesday, January 6, 2015 10:53 AM
    • Marked as answer by Vicky_Liu Tuesday, January 13, 2015 7:58 AM
    Tuesday, January 6, 2015 9:46 AM
  • Your designed report will look like this .


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    • Proposed as answer by Vicky_Liu Tuesday, January 6, 2015 10:53 AM
    • Marked as answer by Vicky_Liu Tuesday, January 13, 2015 7:58 AM
    Tuesday, January 6, 2015 9:46 AM