Diagonal line on SSRS line chart


Hi all,

I'm struggling with a charting issue that I was hoping someone here might be able to help with - I've looked through various blogs without success. I need to insert a diagonal line running the full width of a line chart, representing the linearly increasing target value over a month. The actual data will then be a secondary series on the same vertical axis, showing progress against the target based on today's data.

This report has several limitations that I should emphasise before someone says "Well, why can't you just generate the target data in the SQL query?". It's for Dynamics CRM Online, so the only type of data source and query allowed is FetchXML. I can't even use a simple SQL query that returns constant values, let alone an actual SQL DB query. It also doesn't seem to be possible to have any custom code in the report, which makes it even more frustrating. FetchXML is just not set up to do things like return constant values in a query, or return a dataset which is the set of days in the current month.

What I do have is two datasets, one of which returns the actual data, which I've used as the first series in the table, and a second dataset that returns a single value that represents the target for the month. There's no way, as far as I'm aware, to cross-join that with a set of dates and then use a calculated field in the dataset to turn it into a linear series.

I've set the min and max values of the X-axis to show me all dates, and my initial solution to the problem was to have a horizontal line at the target value, as shown below (red dotted line). However, I've been asked to change that to the solid red diagonal line as shown:

What I need

Series 1 (Total order value) is the RunningValue of the daily order values, summed across the dates. 

Series 1 data looks something like this:

[Date]  [Order Value]
1 Mar	100

3 Mar	20

3 Mar	35

...

24 Mar	400

Series 2 data just has

[Target Name]    [Target Value]
Sales Target     25000

Does anyone have ideas on how I can do this? I'm happy to take any suggestions; the line doesn't have to be interactive or be an action, so it could even be rendered directly onto the chart, if that's possible. The only limitation is that it needs to be viewed on screen, and be printable. I'd prefer some sort of elegant approach that allows me to do it as two series on the same chart, but frankly at this point I'm beyond caring how it's done - so long as it works, it can be held together with chewing gum and string.

March 24th, 2015 7:30am

Hi Anuruddha,

When you want to see the diagonal line, you should divide the target value with number of values in x-axis,

let say Month March  has 31 days, your target is 25000

then 25000/31=806.4516129032258

But to do dynamic you need to get the number of days in month

=day(DateAdd("d",-1,DateAdd("M",1,cdate(cstr(Month(First(Fields!workday.Value, "DataSet3")) )+"/01/" + cstr(Year(First(Fields!workday.Value, "DataSet3")))))))

Create another series like below shown:-

a Above expression  :-

= (Sum(Fields!target.Value, "DataSet2")/ (day(DateAdd("d",-1,DateAdd("M",1,cdate(cstr(Month(First(Fields!workday.Value, "DataSet3")) )+"/01/" + cstr(Year(First(Fields!workday.Value, "DataSet3")))))))))*Fields!id.Value

Run the report:-

Orange Line is yours previously, Red Line is what you want.

I am doing calculation in series, so there is no problem for you as you can't write SQL query.

Below is the RDL code , which will help you to check the expressions.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Textbox Name="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Sparkline</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>12pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.37in</Height>
        <Width>5in</Width>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Chart Name="Chart1">
        <ChartCategoryHierarchy>
          <ChartMembers>
            <ChartMember>
              <Group Name="Chart1_CategoryGroup">
                <GroupExpressions>
                  <GroupExpression>=Fields!workday.Value</GroupExpression>
                </GroupExpressions>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!workday.Value</Value>
                </SortExpression>
              </SortExpressions>
              <Label>=Fields!workday.Value</Label>
            </ChartMember>
          </ChartMembers>
        </ChartCategoryHierarchy>
        <ChartSeriesHierarchy>
          <ChartMembers>
            <ChartMember>
              <Label>value</Label>
            </ChartMember>
            <ChartMember>
              <Label>target</Label>
            </ChartMember>
            <ChartMember>
              <Label>id</Label>
            </ChartMember>
          </ChartMembers>
        </ChartSeriesHierarchy>
        <ChartData>
          <ChartSeriesCollection>
            <ChartSeries Name="value">
              <ChartDataPoints>
                <ChartDataPoint>
                  <ChartDataPointValues>
                    <Y>=RunningValue(Fields!value.Value,Sum,"DataSet3")</Y>
                  </ChartDataPointValues>
                  <ChartDataLabel>
                    <Style />
                  </ChartDataLabel>
                  <Style />
                  <ChartMarker>
                    <Style />
                  </ChartMarker>
                  <DataElementOutput>Output</DataElementOutput>
                </ChartDataPoint>
              </ChartDataPoints>
              <Type>Line</Type>
              <Style />
              <ChartEmptyPoints>
                <Style />
                <ChartMarker>
                  <Style />
                </ChartMarker>
                <ChartDataLabel>
                  <Style />
                </ChartDataLabel>
              </ChartEmptyPoints>
              <ValueAxisName>Primary</ValueAxisName>
              <CategoryAxisName>Primary</CategoryAxisName>
              <ChartSmartLabel>
                <CalloutLineColor>Black</CalloutLineColor>
                <MinMovingDistance>0pt</MinMovingDistance>
              </ChartSmartLabel>
            </ChartSeries>
            <ChartSeries Name="IDDetails">
              <ChartDataPoints>
                <ChartDataPoint>
                  <ChartDataPointValues>
                    <Y>=Sum(Fields!target.Value, "DataSet2")</Y>
                  </ChartDataPointValues>
                  <ChartDataLabel>
                    <Style />
                  </ChartDataLabel>
                  <Style />
                  <ChartMarker>
                    <Style />
                  </ChartMarker>
                  <DataElementOutput>Output</DataElementOutput>
                </ChartDataPoint>
              </ChartDataPoints>
              <Type>Line</Type>
              <Style />
              <ChartEmptyPoints>
                <Style />
                <ChartMarker>
                  <Style />
                </ChartMarker>
                <ChartDataLabel>
                  <Style />
                </ChartDataLabel>
              </ChartEmptyPoints>
              <ValueAxisName>Primary</ValueAxisName>
              <CategoryAxisName>Primary</CategoryAxisName>
              <ChartSmartLabel>
                <CalloutLineColor>Black</CalloutLineColor>
                <MinMovingDistance>0pt</MinMovingDistance>
              </ChartSmartLabel>
            </ChartSeries>
            <ChartSeries Name="Series">
              <ChartDataPoints>
                <ChartDataPoint>
                  <ChartDataPointValues>
                    <Y>= (Sum(Fields!target.Value, "DataSet2")/ (day(DateAdd("d",-1,DateAdd("M",1,cdate(cstr(Month(First(Fields!workday.Value, "DataSet3")) )+"/01/" + cstr(Year(First(Fields!workday.Value, "DataSet3")))))))))*Fields!id.Value</Y>
                  </ChartDataPointValues>
                  <ChartDataLabel>
                    <Style />
                  </ChartDataLabel>
                  <Style />
                  <ChartMarker>
                    <Style />
                  </ChartMarker>
                  <DataElementOutput>Output</DataElementOutput>
                </ChartDataPoint>
              </ChartDataPoints>
              <Type>Line</Type>
              <Style />
              <ChartEmptyPoints>
                <Style />
                <ChartMarker>
                  <Style />
                </ChartMarker>
                <ChartDataLabel>
                  <Style />
                </ChartDataLabel>
              </ChartEmptyPoints>
              <ValueAxisName>Primary</ValueAxisName>
              <CategoryAxisName>Primary</CategoryAxisName>
              <ChartSmartLabel>
                <CalloutLineColor>Black</CalloutLineColor>
                <MinMovingDistance>0pt</MinMovingDistance>
              </ChartSmartLabel>
            </ChartSeries>
          </ChartSeriesCollection>
        </ChartData>
        <ChartAreas>
          <ChartArea Name="Default">
            <ChartCategoryAxes>
              <ChartAxis Name="Primary">
                <Style>
                  <FontSize>8pt</FontSize>
                </Style>
                <ChartAxisTitle>
                  <Caption>Axis Title</Caption>
                  <Style>
                    <FontSize>8pt</FontSize>
                  </Style>
                </ChartAxisTitle>
                <ChartMajorGridLines>
                  <Enabled>False</Enabled>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                    </Border>
                  </Style>
                </ChartMajorGridLines>
                <ChartMinorGridLines>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                      <Style>Dotted</Style>
                    </Border>
                  </Style>
                </ChartMinorGridLines>
                <ChartMinorTickMarks>
                  <Length>0.5</Length>
                </ChartMinorTickMarks>
                <CrossAt>NaN</CrossAt>
                <Minimum>NaN</Minimum>
                <Maximum>NaN</Maximum>
                <ChartAxisScaleBreak>
                  <Style />
                </ChartAxisScaleBreak>
              </ChartAxis>
              <ChartAxis Name="Secondary">
                <Style>
                  <FontSize>8pt</FontSize>
                </Style>
                <ChartAxisTitle>
                  <Caption>Axis Title</Caption>
                  <Style>
                    <FontSize>8pt</FontSize>
                  </Style>
                </ChartAxisTitle>
                <ChartMajorGridLines>
                  <Enabled>False</Enabled>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                    </Border>
                  </Style>
                </ChartMajorGridLines>
                <ChartMinorGridLines>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                      <Style>Dotted</Style>
                    </Border>
                  </Style>
                </ChartMinorGridLines>
                <ChartMinorTickMarks>
                  <Length>0.5</Length>
                </ChartMinorTickMarks>
                <CrossAt>NaN</CrossAt>
                <Location>Opposite</Location>
                <Minimum>NaN</Minimum>
                <Maximum>NaN</Maximum>
                <ChartAxisScaleBreak>
                  <Style />
                </ChartAxisScaleBreak>
              </ChartAxis>
            </ChartCategoryAxes>
            <ChartValueAxes>
              <ChartAxis Name="Primary">
                <Style>
                  <FontSize>8pt</FontSize>
                </Style>
                <ChartAxisTitle>
                  <Caption>Axis Title</Caption>
                  <Style>
                    <FontSize>8pt</FontSize>
                  </Style>
                </ChartAxisTitle>
                <ChartMajorGridLines>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                    </Border>
                  </Style>
                </ChartMajorGridLines>
                <ChartMinorGridLines>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                      <Style>Dotted</Style>
                    </Border>
                  </Style>
                </ChartMinorGridLines>
                <ChartMinorTickMarks>
                  <Length>0.5</Length>
                </ChartMinorTickMarks>
                <CrossAt>NaN</CrossAt>
                <Minimum>NaN</Minimum>
                <Maximum>NaN</Maximum>
                <ChartAxisScaleBreak>
                  <Style />
                </ChartAxisScaleBreak>
              </ChartAxis>
              <ChartAxis Name="Secondary">
                <Style>
                  <FontSize>8pt</FontSize>
                </Style>
                <ChartAxisTitle>
                  <Caption>Axis Title</Caption>
                  <Style>
                    <FontSize>8pt</FontSize>
                  </Style>
                </ChartAxisTitle>
                <ChartMajorGridLines>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                    </Border>
                  </Style>
                </ChartMajorGridLines>
                <ChartMinorGridLines>
                  <Style>
                    <Border>
                      <Color>Gainsboro</Color>
                      <Style>Dotted</Style>
                    </Border>
                  </Style>
                </ChartMinorGridLines>
                <ChartMinorTickMarks>
                  <Length>0.5</Length>
                </ChartMinorTickMarks>
                <CrossAt>NaN</CrossAt>
                <Location>Opposite</Location>
                <Minimum>NaN</Minimum>
                <Maximum>NaN</Maximum>
                <ChartAxisScaleBreak>
                  <Style />
                </ChartAxisScaleBreak>
              </ChartAxis>
            </ChartValueAxes>
            <Style>
              <BackgroundGradientType>None</BackgroundGradientType>
            </Style>
          </ChartArea>
        </ChartAreas>
        <ChartLegends>
          <ChartLegend Name="Default">
            <Style>
              <BackgroundGradientType>None</BackgroundGradientType>
              <FontSize>8pt</FontSize>
            </Style>
            <ChartLegendTitle>
              <Caption />
              <Style>
                <FontSize>8pt</FontSize>
                <FontWeight>Bold</FontWeight>
                <TextAlign>Center</TextAlign>
              </Style>
            </ChartLegendTitle>
            <HeaderSeparatorColor>Black</HeaderSeparatorColor>
            <ColumnSeparatorColor>Black</ColumnSeparatorColor>
          </ChartLegend>
        </ChartLegends>
        <ChartTitles>
          <ChartTitle Name="Default">
            <Caption>Chart Title</Caption>
            <Style>
              <BackgroundGradientType>None</BackgroundGradientType>
              <FontWeight>Bold</FontWeight>
              <TextAlign>General</TextAlign>
              <VerticalAlign>Top</VerticalAlign>
            </Style>
          </ChartTitle>
        </ChartTitles>
        <Palette>BrightPastel</Palette>
        <ChartBorderSkin>
          <Style>
            <BackgroundColor>Gray</BackgroundColor>
            <BackgroundGradientType>None</BackgroundGradientType>
            <Color>White</Color>
          </Style>
        </ChartBorderSkin>
        <ChartNoDataMessage Name="NoDataMessage">
          <Caption>No Data Available</Caption>
          <Style>
            <BackgroundGradientType>None</BackgroundGradientType>
            <TextAlign>General</TextAlign>
            <VerticalAlign>Top</VerticalAlign>
          </Style>
        </ChartNoDataMessage>
        <DataSetName>DataSet3</DataSetName>
        <Top>0.59667in</Top>
        <Left>0.24667in</Left>
        <Height>3.55in</Height>
        <Width>4.53667in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <Border>
            <Color>LightGrey</Color>
            <Style>Solid</Style>
          </Border>
          <BackgroundColor>White</BackgroundColor>
          <BackgroundGradientType>None</BackgroundGradientType>
        </Style>
      </Chart>
      <Textbox Name="Textbox2">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>=day(DateAdd("d",-1,DateAdd("M",1,cdate(cstr(Month(First(Fields!workday.Value, "DataSet3")) )+"/01/" + cstr(Year(First(Fields!workday.Value, "DataSet3")))))))</Value>
                <Style />
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox2</rd:DefaultName>
        <Top>4.33833in</Top>
        <Left>2.21333in</Left>
        <Height>0.25in</Height>
        <Width>1in</Width>
        <ZIndex>2</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>4.71333in</Height>
    <Style />
  </Body>
  <Width>5in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>DataSource1</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>6abd5341-c043-4ab1-b449-c32e00efc4ac</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>SELECT 1 ID, 1 MONTH, 50 VALUE
UNION  
SELECT 1 ID, 2 MONTH, 30 VALUE
UNION
SELECT 1 ID, 3 MONTH, 20 VALUE
UNION  
SELECT 1 ID, 4 MONTH, 10 VALUE
UNION  
SELECT 1 ID, 5 MONTH, 25 VALUE
UNION
SELECT 1 ID, 6 MONTH, 50 VALUE
UNION  
SELECT 1 ID, 7 MONTH, 10 VALUE
UNION  
SELECT 1 ID, 8 MONTH, 20 VALUE
UNION
SELECT 1 ID, 9 MONTH, 30 VALUE
UNION  
SELECT 1 ID, 10 MONTH, 40 VALUE
UNION  
SELECT 1 ID, 11 MONTH, 45 VALUE
UNION
SELECT 1 ID, 12 MONTH, 50 VALUE
UNION  
SELECT 2 ID, 1 MONTH, 25 VALUE
UNION  
SELECT 2 ID, 2 MONTH, 30 VALUE
UNION
SELECT 2 ID, 3 MONTH, 10 VALUE
UNION  
SELECT 2 ID, 4 MONTH, 30 VALUE
UNION  
SELECT 2 ID, 5 MONTH, 45 VALUE
UNION
SELECT 2 ID, 6 MONTH, 35 VALUE
UNION  
SELECT 2 ID, 7 MONTH, 10 VALUE
UNION  
SELECT 2 ID, 8 MONTH, 50 VALUE
UNION
SELECT 2 ID, 9 MONTH, 20 VALUE
UNION  
SELECT 2 ID, 10 MONTH, 14 VALUE
UNION  
SELECT 2 ID, 11 MONTH, 40 VALUE
UNION
SELECT 2 ID, 12 MONTH, 45 VALUE</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="ID">
          <DataField>ID</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="MONTH">
          <DataField>MONTH</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="VALUE">
          <DataField>VALUE</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet2">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>Select 25000 target</CommandText>
      </Query>
      <Fields>
        <Field Name="target">
          <DataField>target</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet3">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>
select 1 id, '03/01/2015' workday , 100 value
union
select 2 id, '03/02/2015' workday , 50 value
union
select 3 id, '03/03/2015' workday , 400 value
union
select 4 id, '03/04/2015' workday , 300 value
union
select 5 id, '03/05/2015' workday , 250 value
union
select 6 id, '03/06/2015' workday , 200 value
union
select 7 id, '03/07/2015' workday , 500 value
union
select 8 id, '03/08/2015' workday , 700 value
union
select 9 id, '03/09/2015' workday , 50 value
union
select 10 id, '03/10/2015' workday , 450 value
union
select 11 id, '03/11/2015' workday , 350 value
union
select 12 id, '03/12/2015' workday , 100 value
union
select 13 id, '03/13/2015' workday , 100 value
union
select 14 id, '03/14/2015' workday , 100 value
union
select 15 id, '03/15/2015' workday , 200 value
union
select 16 id, '03/16/2015' workday , 700 value
union
select 17 id, '03/17/2015' workday , 450 value
union
select 18 id, '03/18/2015' workday , 100 value
union
select 19 id, '03/19/2015' workday , 700 value
union
select 20 id, '03/20/2015' workday , 300 value
union
select 21 id, '03/21/2015' workday , 100 value
union
select 22 id, '03/22/2015' workday , 200 value
union
select 23 id, '03/23/2015' workday , 700 value
union
select 24 id, '03/24/2015' workday , 100 value
union
select 25 id, '03/25/2015' workday , 100 value
union
select 26 id, '03/26/2015' workday , 450 value
union
select 27 id, '03/27/2015' workday , 200 value
union
select 28 id, '03/28/2015' workday , 100 value
union
select 29 id, '03/29/2015' workday , 450 value
union
select 30 id, '03/30/2015' workday , 300 value
union
select 31 id, '03/31/2015' workday , 700 value</CommandText>
      </Query>
      <Fields>
        <Field Name="id">
          <DataField>id</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="workday">
          <DataField>workday</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="value">
          <DataField>value</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>59e8101c-8585-409d-82e7-c6719e928567</rd:ReportID>
</Report>

Thanks
Prasad
------------------------------------------------------------
Mark this as Answer if it helps you to proceed on further.

Free Windows Admin Tool Kit Click here and download it now
March 24th, 2015 8:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics