none
SSRS Expression to calculate percentage with the denominator being the first value in the same column group in a matrix report

    Question

  • Hello, Expert,

    Could you please help me with the following problem? Thanks in advance!

    I have designed the matrix report (SSRS 2012) with database table structure looks like below.    

    Week             

    Version

         Count

    Week1

    V1.1

    4000

    Week1

    V1.2

    4000

    Week1

    V1.3

    4000

    Week2

    V1.1

    3000

    Week2

    V1.2

    3000

    Week2

    V1.3

    3000

    Week3

    V1.1

    2000

    Week3

    V1.2

    2000

    Week3

    V1.3

    2000

    Week4

    V1.1

    1000

    Week4

    V1.2

    1000

    Week4

    V1.3

    1000


         

    The matrix report displays like below. Column grows based on no of Week in the table.   

     

         Week1

         Week2

         Week3

         Week4

    V1.1

    4000

    3000

    2000

    1000

    V1.2

    4000

    3000

    2000

    1000

    V1.3

    4000

    3000

    2000

    1000

    What I want to do now is: instead of the Count values, I need to calculate the percentage of each week over the Count in Week1 so that each cell displays Count(Week2)/Count(Week1), Count(Week3)/Count(Week1), etc. What is the SSRS expression for that? I’m new to SSRS, Please help!

    Tuesday, December 31, 2013 9:23 PM

Answers

All replies

  • Hi QQFA,

    To achieve your goal, please refer to the expression below:
    =iif(Fields!Week.Value="Week1",Fields!Count.Value,
    FormatPercent(Fields!Count.Value/First(Fields!Count.Value,"Version"),0))
    If we add the above expression in the matrix control, we can get the screenshots as shown below:

    Hope this helps.

    Regards,
    Heidi Duan

    Heidi Duan
    TechNet Community Support

    Wednesday, January 01, 2014 9:20 AM
    Moderator
  • Heidi,

    Thank you so much for your reply and solution! Now I know how to get the first field in a group. But I can't apply your solution directly just yet because in order to explain my problem I simplified my data structure. My real data structure has a nested group like this:

    Week  VersionMajor VersionMinor Count

    ----- ------------ ------------ -----------

    Week1  V1           V1.1         2000

    Week2  V1           V1.1         1500

    Week3  V1           V1.1         800

    Week1  V1           V1.2         1000

    Week2  V1           V1.2         500

    Week3  V1           V1.2         200

    Week1  V1           V1.1         2000

    Week2  V1           V1.1         1500

    Week3  V1           V1.1         800

    Week1  V1           V1.2         1000

    Week2  V1           V1.2         500

    Week3  V1           V1.2         200

    Here is the query for the above data:

    SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 2000 AS Count
    UNION ALL
    SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 1500 AS Count
    UNION ALL
    SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 800 AS Count
    UNION ALL
    SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 1000 AS Count
    UNION ALL
    SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 500 AS Count
    UNION ALL
    SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 200 AS Count
    UNION ALL
    SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 2000 AS Count
    UNION ALL
    SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 1500 AS Count
    UNION ALL
    SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 800 AS Count
    UNION ALL
    SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 1000 AS Count
    UNION ALL
    SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 500 AS Count
    UNION ALL
    SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 200 AS Count

    And my matrix table looks like this:

    What I need to do is to calculate the percentage of each week over week1 for

    1) V1 Total

    2) V1.1

    3) V1.2

    I can't use your solution directly because I need to apply Sum function for each cell and the Sum function gives me error when I try to use it on the denominator First(Fields!Count.Value,"VersionMinor"). If you could help me further to get to the final solution, I'd really appreciate it!!

    Happy New Year!


     

    Wednesday, January 01, 2014 11:39 PM
  • Hi QQFA,

    Thanks for your posting.

    This is a relative thread for your reference. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/86977868-2c29-435c-b7c6-10eca231ef79/ssrs-expression-compare-columns-to-first-column-in-group?forum=sqlreportingservices .

    Hope this helps.

    Regards,
    Heidi Duan


    Heidi Duan
    TechNet Community Support

    Tuesday, January 07, 2014 6:49 AM
    Moderator