locked
Displaying Count of records in the data field RRS feed

  • Question

  • User-59962394 posted

    Hi

    This is my first reporting services report. Requirement is to display a pivot model report displaying like below

                              -5 days          -4 days        -3 days        -2 days        -1 days         0 days
    -----------------------------------------------------------------------------------------------------------------
    Area Code1        OrderDesc1   5               8             0               1              10       2      
    
                      OrderDesc2    1               2            5                8              7           5
                     
                      OrderDesc3   2                3           4                  5 
    
    Area Code2        OrderDesc1
    
                      OrderDesc2
    
                      OrderDesc3

    Numbers displayed in the data fields are count of the records which are from AreaCode1 with OrderDesc1 and target date was morethan 5 days  in the past

    I managed to get teh Area Code and OrderDesc to be displayed but not sure how to display each column and the data fields based on the count.

    Please let me know how can I do this.

    Thanks in advance.

     

     

     

    Friday, June 29, 2012 6:21 AM

Answers

  • User-59962394 posted

    I figured the solution for this. This may help some one with same requirement, please see below how the expressions needs to be selected.

    1. I have added new report in my project and placed a matrix report to display data.

    2.Added a dataset with the query which has all the required fields like areaid, orderstatuscode, target days etc.

    3.On the Row grop added the group on  with AreaID which is first group I need to consider.

    4. Then added another row group, grouping on Order status code which is the 2nd level of grouping.

    5. Added multiple column groups based on the days which needs to show only count based on target days. For this added a group on expression below

    Fields!TargetDays.Value = -5 --This value changes based on the column I need to group on for each column like -4, -3,-2,-1,0, 1 etc

    also selected a filter with the same expression but I am not sure whether this is required or not when we have the group on expression. My report is working with both so didn't changed it.

    6. Then in the data cell where each count needs to be displayed, rightclick on the cell assiciated to the column and row group click on Expression and placed the below code.

    =Count(Fields!TargetDays.Value)

    Each cell will already have the column group and row group applied to it as this a matrix report.

    Now looking at the preview will show the results.

    Hope this helps.

     

    Thanks,

    Swapna

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 4, 2012 6:29 AM

All replies

  • User-59962394 posted

    Any help on this.....

    Monday, July 2, 2012 5:32 AM
  • User-59962394 posted

    I figured the solution for this. This may help some one with same requirement, please see below how the expressions needs to be selected.

    1. I have added new report in my project and placed a matrix report to display data.

    2.Added a dataset with the query which has all the required fields like areaid, orderstatuscode, target days etc.

    3.On the Row grop added the group on  with AreaID which is first group I need to consider.

    4. Then added another row group, grouping on Order status code which is the 2nd level of grouping.

    5. Added multiple column groups based on the days which needs to show only count based on target days. For this added a group on expression below

    Fields!TargetDays.Value = -5 --This value changes based on the column I need to group on for each column like -4, -3,-2,-1,0, 1 etc

    also selected a filter with the same expression but I am not sure whether this is required or not when we have the group on expression. My report is working with both so didn't changed it.

    6. Then in the data cell where each count needs to be displayed, rightclick on the cell assiciated to the column and row group click on Expression and placed the below code.

    =Count(Fields!TargetDays.Value)

    Each cell will already have the column group and row group applied to it as this a matrix report.

    Now looking at the preview will show the results.

    Hope this helps.

     

    Thanks,

    Swapna

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 4, 2012 6:29 AM