none
how to join only null values between the data points in the line chart but not show a line for a series which has all the null values using ssrs

    Question

  • I am rendering two charts with 4 series on  each chart and some of the series were empty values (null)

    if I apply EmptyPoint for all the series it was showing series for null values and I think it is taking zero value for emtpy points and rendering series on the chart this is only happening when there is data for second chart and first chart did not have data just null values ( I am using same rows for data series for each chart except different columns for different series)

    My question is how to avoid series not to display for empty values for a series completely and same time another chart should display the series with null values in between the data values (some of the values in the series were null)?

    here is the data I am using

    Compartment  ID Name BeginTime EndTime WhenOccurred Temperature  SetPointTemperature  Probe1 Probe2 Probe3 Probe4
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:33 -21.63 -21.66 -21.19  -20 NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:35 -21.31 -21.66 -21  -21 NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:40 -19.22 -21.66 -20.06  -22 NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:45 -17.22 -21.66 -18.88   -20 NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:50 -16.03 -21.66 -18.19  NULL NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:55 -15.06 -21.66 -17.19  NULL NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 18:55 -14.91 -21.66 -17.13  NULL NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 19:00 -20.09 -21.66 -20.06  NULL NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 19:01 -19.22 -21.66 NULL NULL NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 19:05 -20.59 -21.66 -20.13  -16 NULL NULL
    C1 16233  name1 20/10/2013 18:30 21/10/2013 5:17 20/10/2013 19:10 -17.94 -21.66 -19.25  -21.1 NULL NULL
    C2 20550 name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:17 2.03 1.09 0.69  NULL NULL NULL
    C2 20550 name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:20 1.09 1.09 0.88  NULL NULL NULL
    C2 20550  name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:25 1.84 1.09 1.38  NULL NULL NULL
    C2 20550  name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:25 1.88 1.09 1.38  NULL NULL NULL
    C2 20550  name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:25 1.88 1.09 1.38  NULL NULL NULL
    C2 20550  name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:30 2.09 1.09 1.63  NULL NULL NULL
    C2 20550  name2 21/10/2013 17:41 22/10/2013 2:35 22/10/2013 2:35 2.84 1.09 3.19  NULL NULL NULL

    First Senario:

    According to data I applied C1 row values applied for 1 chart and C2 row values applied in another chart so when probe2 for C1 has data with some null values so in this case I need to show continuous line chart for C1 with series of probe2

    Second Senario:

    I do not want to show series for probe2 on C2 row if it contains null values

    Currently my chart is showing straight line for probe2 on C2 




    • Edited by Vishwanadhu Tuesday, October 22, 2013 10:00 PM
    Tuesday, October 22, 2013 9:39 PM

Answers

  • Hi Vishwanadhu,

    According to your description, I create a similar report in my test environment. I can reproduce the same issue as your post. In my test, I used a list in the chart to separate two chart. The list is Group on: [CompartmentID ] field.

    Because of the Null value of Probe, we cannot get the continuous line in the chart. Null value in the chart means nothing, not a zero value. You can refer to the steps below to work around the issue:
    1. Click the chart, click the field below Values in the “Chart Data” dialog box.
    2. In the Properties dialog box, expand the “EmptyPoint” node. Select a color value for the “Color” Property.
    3. In the “EmptyPoint” node, expand the Marker node. Select a marker type for the “MarkerType” property. Set the value of the empty point.
    4. In the Properties pane, expand the “CustomAttributes” node. Set the “EmptyPointValue” Property with “Average” value.

    For the second chart, we can add an expression to control the visibility of the line.
    1. Right click the field below Values in the “Chart Data” dialog box. Select “Series Properties”.
    2. Click “Visibility” in the left pane. Select “Show or hide based on an expression” below “When the report is initially run:” .
    3. Click the expression icon, and type below expression:
    4. =IIF(Fields!CompartmentID.Value="C2" and InStr("Null",Fields!Probe2.Value),True,False)

    You can refer to the screenshot below:

    If you have any questions, please feel free to let me know.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.


    Wednesday, October 23, 2013 10:15 AM
    Moderator
  • Hi Vishwanadhu,

    If I understand correctly, you want to display the data of C1 in one chart, and C2 data in another chart. You need to show continuous line in C1 chart with series of probe2. In the C2 chart, if probe2 contains null values, it will not show in the C2 chart.

    Sorry for my unclear description in above reply. Suppose we have a dataset named Demo. You can refer to the steps below:
    1. We just need add one list in the report body. And set the list with dataset Demo, and Group on: [CompartmentID] field.
    2. Drag a chart in the list. Add the fields we need in the chart “Chart Data” dialog box.
    3. Click Probe2 field in the “Chart Data” dialog box. Use the same method I post above to get a continuous line in the chart.
    4. Right-click Probe2 field, and select “Series Properties”.
    5. Click “Visibility” in the left pane. Select “Show or hide based on an expression” below “When the report is initially run:” .
    6. Click the expression icon, and type below expression:
    =IIF(Fields!CompartmentID.Value="C2" and InStr("Null",Fields!Probe2.Value),True,False)
    (Note: This steps is based on the data your first post, you can according to your data adjust the chart.)

    You can refer to the screenshots below:

    Hope this helps. If there also have any unclear or misunderstanding, please feel free to ask.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 25, 2013 1:57 AM
    Moderator

All replies

  • Hi Vishwanadhu,

    According to your description, I create a similar report in my test environment. I can reproduce the same issue as your post. In my test, I used a list in the chart to separate two chart. The list is Group on: [CompartmentID ] field.

    Because of the Null value of Probe, we cannot get the continuous line in the chart. Null value in the chart means nothing, not a zero value. You can refer to the steps below to work around the issue:
    1. Click the chart, click the field below Values in the “Chart Data” dialog box.
    2. In the Properties dialog box, expand the “EmptyPoint” node. Select a color value for the “Color” Property.
    3. In the “EmptyPoint” node, expand the Marker node. Select a marker type for the “MarkerType” property. Set the value of the empty point.
    4. In the Properties pane, expand the “CustomAttributes” node. Set the “EmptyPointValue” Property with “Average” value.

    For the second chart, we can add an expression to control the visibility of the line.
    1. Right click the field below Values in the “Chart Data” dialog box. Select “Series Properties”.
    2. Click “Visibility” in the left pane. Select “Show or hide based on an expression” below “When the report is initially run:” .
    3. Click the expression icon, and type below expression:
    4. =IIF(Fields!CompartmentID.Value="C2" and InStr("Null",Fields!Probe2.Value),True,False)

    You can refer to the screenshot below:

    If you have any questions, please feel free to let me know.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.


    Wednesday, October 23, 2013 10:15 AM
    Moderator
  • Hi Alisa Tang,

    Thanks for your help.

    I used 2 Lists instead of 2 Tablix  for charts and did same way you mentioned 

    but it was displaying another 3 charts for null values due to grouping on Compartmentid

    and used expression on grouping for =Fields!CompartmentID.Value="C1" to make sure only gets data for compartment and I did emptypoint for markertype with circle and it showing for only probe3 series, this is weired, other than that everthing is working as expected

    so I am planning to create different columns for different compartments like C1Temp, C2Temp, C1Probe1, C2Probe1 so that I can use that series only in the chart

    see the output

    


    • Edited by Vishwanadhu Wednesday, October 23, 2013 11:56 PM
    Wednesday, October 23, 2013 11:01 PM
  • Hi Vishwanadhu,

    If I understand correctly, you want to display the data of C1 in one chart, and C2 data in another chart. You need to show continuous line in C1 chart with series of probe2. In the C2 chart, if probe2 contains null values, it will not show in the C2 chart.

    Sorry for my unclear description in above reply. Suppose we have a dataset named Demo. You can refer to the steps below:
    1. We just need add one list in the report body. And set the list with dataset Demo, and Group on: [CompartmentID] field.
    2. Drag a chart in the list. Add the fields we need in the chart “Chart Data” dialog box.
    3. Click Probe2 field in the “Chart Data” dialog box. Use the same method I post above to get a continuous line in the chart.
    4. Right-click Probe2 field, and select “Series Properties”.
    5. Click “Visibility” in the left pane. Select “Show or hide based on an expression” below “When the report is initially run:” .
    6. Click the expression icon, and type below expression:
    =IIF(Fields!CompartmentID.Value="C2" and InStr("Null",Fields!Probe2.Value),True,False)
    (Note: This steps is based on the data your first post, you can according to your data adjust the chart.)

    You can refer to the screenshots below:

    Hope this helps. If there also have any unclear or misunderstanding, please feel free to ask.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 25, 2013 1:57 AM
    Moderator