locked
Get % value in matrix table RRS feed

  • Question

  •       SK Qty   % SKU Qty %
      A AA Item 1 2 2/4    
        Item 2 1 1/4    
      Item 3 1 1/4    
      AA Total:  4 4/ 10    
      AB Item 1 2 2/6    
    Item 4 3 1/6    
        Item 5 1 1/6    
      AB Total:  6 6/10    
    A Total: 10 10 / 25    
      B BA Item 6 2      
        Item 7 2      
      Item 9 3      
      BA Total: 6      
      BB Item 10 2      
    Item 11 3      
        Item 13 4      
      BB Total: 9      
      B Total: 15      
    Total:                          25   25/25  

     

    =======================================================================================

    I have a matrix table above, a "%" value is needed to present in the "%" column. But i can't get those value in red color in every row accordingly.

     

    Pls help and thanks so much in advance!

    Saturday, March 15, 2008 11:03 AM

Answers

  • Yeah I see the problem.. didn't notice it earlier.

    Absurdly enough, looks like the value in the hidden textbox changes as soon as it is referenced via reportitems. When you remove the reference, it shows the correct value!

     

    Can't understand whether it is by design or some kind of a bug in the matrix control.

    Anyhow unfortunately I cannot think of any way around this problem!

     

    Btw just out of curiosity, in your matrix are you using any column group also.. or is it only the three row groups? If you aren't using any column group, you might as well use a Table instead of a Matrix.

    Wednesday, March 19, 2008 10:52 AM

All replies

  • What is the expression you are using for the "%" calculation. It sounds like a grouping issue. For the values in red, do you want them to be totaled at the first subgroup (AA, AB) or the primary group (A)?   If 10/25 for the primary group is what you are getting then I would think your expression is using the entire dataset scope and not the grouping within the matrix. Once we see the expression and more of what you expect we can help a bit more.

    Thanks,

    Rodney Landrum

     

    Sunday, March 16, 2008 2:08 PM
  • Hi, Rodney.

    You are right, this is a grouping issue. now i can solve the 10/25, but (2/4) in red is quite difficult. how to get the value. Let me explain more detail about the grouping inside.

     

    The entire dataset :            dsMaterial

    There 3 row groups :          rgp_Cate                       (A, B, ..... ) 

    rgp_SubCate                 (AA, AB, .....  

    rgp_Material                  (Item1, Item2, ...........)

     

      A AA Item 1 2 2/4    
        Item 2 1 1/4    
      Item 3 1 1/4    
      AA Total: 4 4/ 10    
      AB Item 1 2 2/6    
    Item 4 3 1/6    
        Item 5 1 1/6    
      AB Total: 6 6/10    
    A Total: 10 10 / 25

     

     

    For the value of (2/4) in red, I use the expression "=CountDistinct(Field1 & Field2 & Field3, "rgp_SubCate")"  in order to get the value of 4, but it return an unexpected value of 10 to me.

     

    Pls help!

    Monday, March 17, 2008 1:27 AM
  • Since you want the total count of fields in the third row group, shouldn't you be using

    =CountDistinct(Field1 & Field2 & Field3, "rgp_Material") 

    instead of

    =CountDistinct(Field1 & Field2 & Field3, "rgp_SubCate")?

    That should give you 4 instead of 10.

     

    -Aayush

    Monday, March 17, 2008 7:04 AM
  • HI, Aayush

     

    i had tried the expression:

    (1) =CountDistinct(Field1 & Field2 & Field3, "rgp_Material") 

    (2) =CountDistinct(Field1 & Field2 & Field3, "rgp_SubCate") 

    (3) =CountDistinct(Field1 & Field2 & Field3, "rgp_Cate") 

     

    All expression will also return the value of 10....why ?

     

    Pls help again.

     

     

    Monday, March 17, 2008 7:38 AM
  • Any particular reason as to why you are using CountDistinct in your expression? What expression are you using for the "SK Qty" field?
    Monday, March 17, 2008 8:30 AM
  • HI, Aayush

     

    The value of "SK Qty" column is from the expression of 

    CountDistinct(Fields!ItemCode.Value),

     

    The value of  "%" column SHOULD BE CountDistinct(Fields!ItemCode.Value) / CountDistinct(Fields!ItemCode.Value, "rgp_SubCate") in order to get the value of 2/4.

     

    But the expression of CountDistinct(Fields!ItemCode.Value, "rgp_SubCate") returns unexpected10 instead of 4.

     

    Pls help again

    Tuesday, March 18, 2008 1:56 AM
  • Ok, I was able to reproduce the issue & it does return 10 regardless of whatever scope you give in the CountDistinct function. I was however, able to find some sort of a workaround.

    I assume that you are using subtotal fields for all the 3 row-groups to get the respective totals.

     

    You will have to place an additional small hidden textbox in your row-group 3 header which contains the value expression:

    CountDistinct(Fields!ItemCode.Value, "rgp_SubCate")

     

    Use ReportItems!<the-hidden-textbox-name>.value in the textbox for your % column, to get 4.

    Use CountDistinct(Fields!ItemCode.Value,"rgp_SubCate") in the textbox for your % column, to get 10.

    Use CountDistinct(Fields!ItemCode.Value,"DataSet1") in the textbox for your % column, to get 25.

     

    Let me know if this works.

    -Aayush

    Tuesday, March 18, 2008 6:50 AM
  • Thanks,  Aayush.

    I think this is a solution for my situation. But I don't know how to place an additional small hidden textbox in row-group header in MATRIX?

     

    Any tutorial about this issue online.

     

    Tuesday, March 18, 2008 8:07 AM
  • In your matrix, there is a cell from which you get values for 'rgp_Material' (Item1, Item2, ...........). This cell is the group header for your third row-group. You will need to place another small textbox alongside the textbox which is already present in this cell.

     

    - Go to the toolbox window, select the rectangle control and drag it onto the matrix cell (A rectangle is a container within which you can place multiple textboxes etc).

    - Now you can put the two textboxes within this cell.

     

    I am not aware of any tutorial which illustrates how to do this. It is really not that difficult and you should get the hang of it after a couple of attempts.

    Tuesday, March 18, 2008 8:44 AM
  • You can try this

    Code Snippet

    CountDistinct(Fields!ItemCode.Value) & "/" & IIF(InScope("rgp_Cate"),CountDistinct(Fields!ItemCode.Value, "rgp_SubCate"),CountDistinct(Fields!ItemCode.Value, "rgp_Cate"))

     

     

    Tuesday, March 18, 2008 8:51 AM
  • Hi, Roger.

    Unfortunately, the unexpected value returned with your method. Anway, Thanks!

     

     

    Otherwise, I had tested the method from Aayush. It works to get the value of 4 in every non-subtotal row (Item1, Item2.....).

     

     

    But i also need to handle the related "Subtotal" row getting value of 10 instead of 4?

     

    *****************************************

            How to clarify those subtotal row and non-sutotal row in matrix table... Becasue 

            my situation had already presented 3 row groups of subtotal in 3 layers.

    *****************************************

    Wednesday, March 19, 2008 1:49 AM
  • Hi elmerli,

     

    The InScope function can be used to distinguish between a sub-total and non-subtotal row in a matrix. The logic will be similar to what Roger has used in his post.

     

    Use an expression similar to the following:

    =CountDistinct(Fields!ItemCode.Value) & "/" &

    IIf(InScope("rgp_Material"),ReportItems!<the-hidden-textbox-name>.Value,
    IIf(InScope("rgp_SubCate"),CountDistinct(Fields!ItemCode.Value,"rgp_SubCate"),
    CountDistinct(Fields!ItemCode.Value,"DataSet1")))

     

    -Aayush

    Wednesday, March 19, 2008 6:31 AM
  • Hi, Aayush,

     

    It will be a solution for my situation. However i encounter another little problem about running value of the hidden textbox in row-group (Item1, Item2.....).

     

    A      AA Item 1 2     2/4                value of hidden textbox = 4 ok     
        Item 2 1     1/4                value of hidden textbox = 4 ok    
      Item 3 1     1/4                value of hidden textbox = 4 ok    
      AA Total: 4     4/ 10    
            AB Item 1 2

        2/6                value of hidden textbox = 4  NOT OK

       
    Item 4 3     1/6                value of hidden textbox = 6 ok    
        Item 5 1     1/6                value of hidden textbox = 6 ok
     

    An error will appear in every first row of the successor data group. How to solve it ?

     

     

     

    Wednesday, March 19, 2008 7:27 AM
  • Yeah I see the problem.. didn't notice it earlier.

    Absurdly enough, looks like the value in the hidden textbox changes as soon as it is referenced via reportitems. When you remove the reference, it shows the correct value!

     

    Can't understand whether it is by design or some kind of a bug in the matrix control.

    Anyhow unfortunately I cannot think of any way around this problem!

     

    Btw just out of curiosity, in your matrix are you using any column group also.. or is it only the three row groups? If you aren't using any column group, you might as well use a Table instead of a Matrix.

    Wednesday, March 19, 2008 10:52 AM
  • Hi, Aayush,

     

    Actually, i have to present the table layout given. Although only 3 row-group here, I don't think Table can achieve my goal.

     

    Now i will find other solution.

     

    Thanks a lot.

    Wednesday, March 19, 2008 1:34 PM
  • I think maybe you can calculate the result in SP, then send the result to report server.

    Wednesday, March 19, 2008 2:21 PM
  • The difference between a Table and a Matrix is that a Matrix can grow horizontally i.e. you can have column-groups in a matrix. Otherwise vertical expansion via row-groups can be done using a Table.

    Since you do not have any column groups, I think you can easily achieve what you need by using a Table.

     

    You will need to have the same three row-groups in the Table. The non-total fields can be put in a single Detail row & the group totals can be placed in the respective Group Footers.

     

    Just a suggestion. Maybe you should give it a shot.

    Thursday, March 20, 2008 5:53 AM
  • Hi, Aayush Goyal,

     

    Finally, I can solve it using table.

     

    Thank you for all.

     

     

     

     

    Thursday, March 27, 2008 3:10 AM