none
Display number on top of report

    Question

  • Hi All,

    I would like to display number of ITServices which are RED colour and number of ITServices which are YELLOW on top of the tablix. like i represented in the below image.I have a FILTER condition on the ROWGROUP(ITServices). Please suggest how to implement this.


    Maruthi...

    Sunday, July 28, 2013 6:53 PM

Answers

  • Hi Maruthi,

    According to your description ,you want to calculate the number of the indicators in your report .

    You can follow following steps try to resolve your problem :
      1.     Add three textboxs into the report body where you want to display the values.
      2.     Right click first  textbox ,and specify the value expression of the textbox as follows:
              =Sum(IIF(Fields!Above.Value>[target])1,0) 
      3.     Specify the value expression for other textbox which display the calculated values based on your condition .
      4.     Then preview the report ,to check if it is success .

    If you have any questions about it ,please let me know .

    Thanks,
    Alisa Tang

    Monday, July 29, 2013 11:03 AM
    Moderator
  • Hi Maruthi,

    According to your reply ,you had created two filter conditions in rowgroup . When we specify  value expression  in the textbox above the Table data region, the default scope is dataset. In that case, the Filter condition on Row Group is ignored.

    We can try to add the Filter conditions to the expression ,you can refer to the following example :

    1.Right click the Red image corresponding textbox ,and specify the value expression of the textbox as follows:

    =Sum(IIF( "Filter conditions" AND Fields!Target.Value >= Round(IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,100, 
    ((Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value - SUM(Fields!DTDOWNTIMEEFFECTIVEMINUTES.Value))*100) / 
    IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,1, Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value )),2),1,0))

    2.Right click the Yellow image corresponding textbox ,and specify the value expression of the textbox as follows:

    =Sum(IIF( "Filter conditions" AND Fields!Target.Value < Round(IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,100, 
    ((Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value - SUM(Fields!DTDOWNTIMEEFFECTIVEMINUTES.Value))*100) / 
    IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,1, Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value )),2),1,0))

    3.Right click the Green image corresponding textbox ,and specify the value expression of the textbox as follows:

    =Sun(IIF("Filter conditions" AND Fields!Target.Value = 0,1,0))

    (Note: Replacing “Filter conditions” with your Filter conditions you had created .)

    If there is any misunderstanding ,please let me know .

    Thanks ,
    Alisa Tang


    Tuesday, July 30, 2013 3:36 AM
    Moderator

All replies

  • What is the location of these links ? i.e Header or Body.

    If it is inside Body, Add 3 text box and set the expression as below. Make sure that you add a new column that highlights as Yellow,Red and Green. Then you can hide this column lateron. The idea is to read new column value count base on the appreance of "Yellow","Red" and "Green" values.


    =Sum(IIF(Fields!NewColumn.Value = "Yellow", 1 , 0))
    =Sum(IIF(Fields!NewColumn.Value = "Red", 1 , 0))
    =Sum(IIF(Fields!NewColumn.Value = "Green", 1 , 0))
    
    Example: 

    Regards, RSingh




    • Edited by RSingh() Tuesday, July 30, 2013 4:04 AM add image
    Monday, July 29, 2013 3:53 AM
  • Hi Maruthi,

    According to your description ,you want to calculate the number of the indicators in your report .

    You can follow following steps try to resolve your problem :
      1.     Add three textboxs into the report body where you want to display the values.
      2.     Right click first  textbox ,and specify the value expression of the textbox as follows:
              =Sum(IIF(Fields!Above.Value>[target])1,0) 
      3.     Specify the value expression for other textbox which display the calculated values based on your condition .
      4.     Then preview the report ,to check if it is success .

    If you have any questions about it ,please let me know .

    Thanks,
    Alisa Tang

    Monday, July 29, 2013 11:03 AM
    Moderator
  • Hi Alisa and Singh,

    I tried as you suggested but couldn't solve the problem.

    just to make you understand more about the report:

    1. I have 131 rows from the dataset.

    2. I created 2 filter conditions in rowgroup.

    3. Based on the filter conditions, Tablix displays around 66 rows. After that based on the the below expression I am displaying RED,YELLOW AND GREEN image.

    =IIF(Fields!Target.Value = 0,"Green",
    IIF( Fields!Target.Value >= Round(iif(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,100, 
    ((Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value - SUM(Fields!DTDOWNTIMEEFFECTIVEMINUTES.Value))*100) / 
    iif(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,1, Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value )),2),
    "Red", "Yellow"))

    4. All the above steps are working fine. On top of the Tablix, I have a table or text box to display ITServices number of RED, YELLOW and green.

    I am not able to dispaly the right number of ITServices with RED and YELLOW images.

    I have to catch the colour values(RED,YELLOW and GREEN) at the run time of the report and display the count of ITServices. I am not sure, how to catch the values at run time of the report in reporting services or is there any way to write custom code to catch the count of colours or may be there might be other way to fix :(


    Maruthi...






    Monday, July 29, 2013 3:16 PM
  • Hi Maruthi,

    According to your reply ,you had created two filter conditions in rowgroup . When we specify  value expression  in the textbox above the Table data region, the default scope is dataset. In that case, the Filter condition on Row Group is ignored.

    We can try to add the Filter conditions to the expression ,you can refer to the following example :

    1.Right click the Red image corresponding textbox ,and specify the value expression of the textbox as follows:

    =Sum(IIF( "Filter conditions" AND Fields!Target.Value >= Round(IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,100, 
    ((Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value - SUM(Fields!DTDOWNTIMEEFFECTIVEMINUTES.Value))*100) / 
    IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,1, Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value )),2),1,0))

    2.Right click the Yellow image corresponding textbox ,and specify the value expression of the textbox as follows:

    =Sum(IIF( "Filter conditions" AND Fields!Target.Value < Round(IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,100, 
    ((Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value - SUM(Fields!DTDOWNTIMEEFFECTIVEMINUTES.Value))*100) / 
    IIF(Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value = 0,1, Fields!SERVICE_OPENINGHOURS_IN_PERIOD_MIN.Value )),2),1,0))

    3.Right click the Green image corresponding textbox ,and specify the value expression of the textbox as follows:

    =Sun(IIF("Filter conditions" AND Fields!Target.Value = 0,1,0))

    (Note: Replacing “Filter conditions” with your Filter conditions you had created .)

    If there is any misunderstanding ,please let me know .

    Thanks ,
    Alisa Tang


    Tuesday, July 30, 2013 3:36 AM
    Moderator