none
Write Expression on other pivot reprot

    Question

  • Please go through above image and help me how to write expression in pivot2 table based on pivot1 table.

    Actually i want write expression in pivot2 and use value of pivot1.

    For example : If i want "emp Count " for month January in pivot2 then write expression COUNTA(M4:M15)  AS PER EXCEL CALCULATION. I WANT IMPLEMENT SAME CALCULATION IN OUR SSRS REPORT.

    pLEASE HELP ME.

    Saturday, March 30, 2013 7:56 AM

Answers

  • Hello,

    Please referto the following steps:
    1. Add a Matrix data region into the report design surface and delete the row group column.
    2. Add the "Month" as column group in the Matrix and add a new row inside the group.
    3. Using the following expression to calculated (M4-M15) in first row:
    =CountDistinct(Fields!EMPNAME.value)
    4.Using the expression to calculated(J4-L15) in second row:
    =Count(Fields!percentvalue.value)

    If I have any misunderstanding, Could you please post both the report and the dataset with sample data to us by the following E-mail address?  It is benefit for us to do further analysis.
    E-mail: sqltnsp@microsoft.com

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, April 03, 2013 3:38 AM
    Moderator

All replies

  • You should use Matrix in SSRS.

    Add the Emp Name column as "Rows" input.
    Add the Month column as "Columns" input.
    Add emp count column as your "Data" input.

    Could be like this as below.

    Matrix sample


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.


    • Edited by Sachi_SG Saturday, March 30, 2013 3:43 PM example
    • Proposed as answer by Sachi_SG Saturday, March 30, 2013 3:43 PM
    • Unproposed as answer by Ramakant Singh Monday, April 01, 2013 11:34 AM
    Saturday, March 30, 2013 3:36 PM
  • HI Sachi,

    Thanks for reply,

    I know matrix report and i have worked many matrix reports, But I want generate above  reprot2 based on report1 .

    I am able to calculate "EVAL COUNT" Which is count of all average value.

    But i am not able tp calculate "Emp Count" which is count of all Total average like January Total , Feb Total.

    Please see my calculation as per excel.

    Thanks

    Monday, April 01, 2013 10:53 AM
  • Hello Ramakant,

    You can add another row within your pivot 1 to write the desired formula.

    i.e. in row 17 of your pivot 1, just write the formula for count (COUNTA) below each row. Then simply refer to these cells from your other sheet/reports.

     


    Vishal Soni

    Tuesday, April 02, 2013 5:30 AM
  • HI Vishal,

    Thanks for reply,

    Please let me know how to write custom expression on grant Total/ Count/ Average in matrix report.

    Thanks

    Ramakant Singh

    Tuesday, April 02, 2013 9:35 AM
  • Can you please share your working sheet@   soni.vs@gmail.com

    Vishal Soni

    Tuesday, April 02, 2013 9:52 AM
  • Hello,

    Please referto the following steps:
    1. Add a Matrix data region into the report design surface and delete the row group column.
    2. Add the "Month" as column group in the Matrix and add a new row inside the group.
    3. Using the following expression to calculated (M4-M15) in first row:
    =CountDistinct(Fields!EMPNAME.value)
    4.Using the expression to calculated(J4-L15) in second row:
    =Count(Fields!percentvalue.value)

    If I have any misunderstanding, Could you please post both the report and the dataset with sample data to us by the following E-mail address?  It is benefit for us to do further analysis.
    E-mail: sqltnsp@microsoft.com

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, April 03, 2013 3:38 AM
    Moderator