none
How can i have a grouped column after a total RRS feed

  • Question

  • Hello,

    I have a matrix , i have a column with 6 values (Value1,Value2,Value3,Value4,Value5,Value6) and in Data i have the count of product and in rows i have the store name.

    What i want to display the 4 values(Value1,Value2,Value3,Value4) then the Total of them and after that i want display the other values (value5,value6) with the total of them.

    Well, i did the first step i displayed  the 4 values(Value1,Value2,Value3,Value4) with the Total but when i want to display the other column i can't display all of them it display only the Value5  because i don't have a group of column and i can't have 2 groups columns in the same matix 

    How can i fix that ?

    Thanks 

    Wednesday, November 6, 2019 2:40 PM

Answers

  • Hi YassirCool,

    How have you implement the approach?

    As I tested, you need to add group first for the column, group them on "Group" field. Then add total for the EnglishProductName column. Then delete the Group row, row only.

    See my test step by step is as follow:

    The test result is:

    Regards,
    Lukas

     


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by YassirCool Tuesday, November 12, 2019 9:40 AM
    Tuesday, November 12, 2019 6:21 AM

All replies

  • Hi YassirCool,

    For this kind of scenario, if you can not add group for the rows, you could try use report items to add them cell by cell directly.

    You could right click to find the textbox name then, try

    =reportitems!value1name.value+reportitems!value2name.value+reportitems!value3name.value+reportitems!value4name.value

    use this expression for the summation cell.

    Hope this could help. If this is not your case, you could add picture about you design and more details to clarify you intention.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 7, 2019 2:42 AM
  • Hello,

    Thanks for you replying.

    For example i have this query from database AdventureWorksDW2012

    select sum(fact.OrderQuantity) Quantity,Year(fact.OrderDate),dimPS.EnglishProductSubcategoryName,dimPC.EnglishProductCategoryName from FactInternetSales fact
    inner join DimProduct dimPro
    on fact.ProductKey=dimPro.ProductKey
    inner join DimProductSubcategory dimPS
    on dimPro.ProductSubcategoryKey=dimPS.ProductSubcategoryKey
    inner join DimProductCategory dimPC
    on dimPC.ProductCategoryKey=dimPS.ProductCategoryKey
    group by dimPS.EnglishProductSubcategoryName,dimPC.EnglishProductCategoryName ,Year(fact.OrderDate)

    I have a report like as bellow :

    What i want is insert a new column after Bikes called Total and this column have the Total of Accessories and Bikes and also have another column Total after Clothing that contain the Total of Clothing

    Thanks for help !


    • Edited by YassirCool Sunday, November 10, 2019 3:53 PM
    Sunday, November 10, 2019 3:48 PM
  • Hi YassiCool,

    Thanks for your clarification, now we understand the situation well.

    As my first post, you could insert one new column behind the Bikes, use ReportItems to add the frontier columns together. 

    See: 

    This should work as you want.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 7:28 AM
  • Hello ,

    Thanks for your replying.

    Just in my case i have a Matrix with a column Group  . i want something that can works dynamically. 

    Because in Real query i have another column Query that Called GROUPS , this column the contain the groups of Total in my case, Accessories and Bikes have a value 1 in GROUPS column and Clothing have value 2 .

    Supposing i have a new Category calling Shoes in this case the GROUPS of this Category will Be 2, then in my reports i want to have a total dynamical with Clothing.

    May be your solution solve the problem but if i have another Category i need also change the report

    Thanks for help !

    Monday, November 11, 2019 7:59 AM
  • Hi YassirCool,

    Thanks for the words, I get your case this time. Have you tried the Add total function? It would add total for column groups dynamically.

    See:

    Hope this works now.

    Lukas 


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 8:11 AM
  • Hello Lukas,

    Thanks for your support.

    Unfortunately, your solution doesn't resolve my problem . As bellow what i want see in report

    As bellow the query that i use from AdventureWorksDW

    select sum(fact.OrderQuantity) Quantity,Year(fact.OrderDate) as OrderDate,dimPC.EnglishProductCategoryName,
    case when dimPC.EnglishProductCategoryName ='Clothing' then 2 else 1 end as Groups
    from FactInternetSales fact
    inner join DimProduct dimPro
    on fact.ProductKey=dimPro.ProductKey
    inner join DimProductSubcategory dimPS
    on dimPro.ProductSubcategoryKey=dimPS.ProductSubcategoryKey
    inner join DimProductCategory dimPC
    on dimPC.ProductCategoryKey=dimPS.ProductCategoryKey
    group by dimPS.EnglishProductSubcategoryName,dimPC.EnglishProductCategoryName ,Year(fact.OrderDate)

    Thanks

    Monday, November 11, 2019 1:36 PM
  • Hi YassirCool,

    How have you implement the approach?

    As I tested, you need to add group first for the column, group them on "Group" field. Then add total for the EnglishProductName column. Then delete the Group row, row only.

    See my test step by step is as follow:

    The test result is:

    Regards,
    Lukas

     


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by YassirCool Tuesday, November 12, 2019 9:40 AM
    Tuesday, November 12, 2019 6:21 AM
  • Thanks for your support 
    Tuesday, November 12, 2019 9:40 AM