tags:

views:

379

answers:

1
Q: 

Complex graph in Reporting Services?

I'm trying to imitate this graph, or get as close as feasible:

Features:

  • Codes at the bottom (Need to have)
  • Rate at the side (Need to have)
  • Both this year's dataset and last year's dataset (Need to have)
  • Up-bars and downbars to indicate improvement / deterioration (Nice to have)

I know how to create it in Excel:

  • Create a line graph for the two datasets
  • In the graph's properties, remove lines
  • Insert then format the up bars and down bars as appropriate

I've managed to get this close in the Layout view of Reporting Services:

It's a line graph with the trend lines disabled. No up-bars / down-bars, but that's acceptable.

The problem is, when I switch to Preview / Publish view, this is what it becomes:

The lines all appear for no apparent reason.

I posted an earlier (now deleted) question for advice on this, and received the suggestion of using a scatter graph. But unless I'm missing something, the scatter graph requires two numeric axes? When I try to drop the "Rate" value on the "Y value" field, I get the error "Value cannot be null. Parameter name: value" (There is no report parameter "Value".) It also doesn't seem to be able to have two datasets on the one axis?

So, what should I be doing? Is there some way to get the trendlines to go away in the preview / publish view? Alternatively, is there some way to get scatter plots to do what I'm after? Or some other graph that would do it simply?

EDIT: This is in Reporting Services 2005, so you know.

+1  A: 

RDL below. I just use defaults of everything. For some reason I had to set the colours for the graph to show anything. And you have to fill the high and low as well. I just put in the average of the open and close so they did not show.

EDIT: Modified to include the crosses and diamonds. I set all the values of a new series to lastyear then turned on only the markers. Then I did the same again for the thisyear. They do not seem to be positioned ideally but that is the best I could do. 

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"&gt;
  <DataSources>
    <DataSource Name="DataSource1">
      <rd:DataSourceID>892866e1-c006-4497-9545-251d2a43feb0</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=rover4;Initial Catalog=master</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
    </DataSource>
  </DataSources>
  <InteractiveHeight>29.7cm</InteractiveHeight>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>21cm</InteractiveWidth>
  <rd:GridSpacing>0.25cm</rd:GridSpacing>
  <rd:SnapToGrid>true</rd:SnapToGrid>
  <RightMargin>2.5cm</RightMargin>
  <LeftMargin>2.5cm</LeftMargin>
  <BottomMargin>2.5cm</BottomMargin>
  <rd:ReportID>55a6b44d-b38d-4f58-b9bf-fd26c6f80e4a</rd:ReportID>
  <PageWidth>21cm</PageWidth>
  <DataSets>
    <DataSet Name="master">
      <Fields>
        <Field Name="codename">
          <DataField>codename</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="thisyear">
          <DataField>thisyear</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="lastyear">
          <DataField>lastyear</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>Select 'code1'  as codename, 45 as thisyear, 46 as lastyear
union
Select 'code2'  as codename, 42 as thisyear, 55 as lastyear
union
Select 'code3'  as codename, 57 as thisyear, 40 as lastyear
union
Select 'code4'  as codename, 10 as thisyear, 11 as lastyear</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
  </DataSets>
  <Width>16cm</Width>
  <Body>
    <ColumnSpacing>1cm</ColumnSpacing>
    <ReportItems>
      <Chart Name="chart1">
        <Legend>
          <Style>
            <BorderStyle>
              <Default>Solid</Default>
            </BorderStyle>
          </Style>
          <Position>RightCenter</Position>
        </Legend>
        <CategoryAxis>
          <Axis>
            <Title />
            <MajorGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MajorGridLines>
            <MinorGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MinorGridLines>
            <MajorTickMarks>Outside</MajorTickMarks>
            <Min>0</Min>
            <Visible>true</Visible>
          </Axis>
        </CategoryAxis>
        <DataSetName>master</DataSetName>
        <PlotArea>
          <Style>
            <BackgroundColor>LightGrey</BackgroundColor>
            <BorderStyle>
              <Default>Solid</Default>
            </BorderStyle>
          </Style>
        </PlotArea>
        <ThreeDProperties>
          <Rotation>30</Rotation>
          <Inclination>30</Inclination>
          <Shading>Simple</Shading>
          <WallThickness>50</WallThickness>
        </ThreeDProperties>
        <PointWidth>0</PointWidth>
        <SeriesGroupings>
          <SeriesGrouping>
            <StaticSeries>
              <StaticMember>
                <Label>Value2</Label>
              </StaticMember>
              <StaticMember>
                <Label>This year markers</Label>
              </StaticMember>
              <StaticMember>
                <Label>last year markers</Label>
              </StaticMember>
            </StaticSeries>
          </SeriesGrouping>
        </SeriesGroupings>
        <Top>0.25cm</Top>
        <Subtype>Candlestick</Subtype>
        <ValueAxis>
          <Axis>
            <Title />
            <MajorGridLines>
              <ShowGridLines>true</ShowGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MajorGridLines>
            <MinorGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MinorGridLines>
            <MajorTickMarks>Outside</MajorTickMarks>
            <Min>0</Min>
            <Max>100</Max>
            <Margin>true</Margin>
            <Visible>true</Visible>
            <Scalar>true</Scalar>
          </Axis>
        </ValueAxis>
        <Type>Stock</Type>
        <Width>15cm</Width>
        <CategoryGroupings>
          <CategoryGrouping>
            <DynamicCategories>
              <Grouping Name="chart1_CategoryGroup1">
                <GroupExpressions>
                  <GroupExpression>=Fields!codename.Value</GroupExpression>
                </GroupExpressions>
              </Grouping>
              <Label>=Fields!codename.Value</Label>
            </DynamicCategories>
          </CategoryGrouping>
        </CategoryGroupings>
        <Palette>Default</Palette>
        <ChartData>
          <ChartSeries>
            <DataPoints>
              <DataPoint>
                <DataValues>
                  <DataValue>
                    <Value>=(Fields!thisyear.Value+Fields!lastyear.Value)/2</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=(Fields!thisyear.Value+Fields!lastyear.Value)/2</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!lastyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!thisyear.Value)</Value>
                  </DataValue>
                </DataValues>
                <DataLabel />
                <Style>
                  <BackgroundColor>SandyBrown</BackgroundColor>
                  <BackgroundGradientEndColor>Lime</BackgroundGradientEndColor>
                  <BorderColor>
                    <Default>Red</Default>
                  </BorderColor>
                </Style>
                <Marker>
                  <Size>6pt</Size>
                </Marker>
              </DataPoint>
            </DataPoints>
          </ChartSeries>
          <ChartSeries>
            <DataPoints>
              <DataPoint>
                <DataValues>
                  <DataValue>
                    <Value>=Sum(Fields!thisyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!thisyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!thisyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!thisyear.Value)</Value>
                  </DataValue>
                </DataValues>
                <DataLabel />
                <Style />
                <Marker>
                  <Type>Diamond</Type>
                  <Size>6pt</Size>
                </Marker>
              </DataPoint>
            </DataPoints>
          </ChartSeries>
          <ChartSeries>
            <DataPoints>
              <DataPoint>
                <DataValues>
                  <DataValue>
                    <Value>=Sum(Fields!lastyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!lastyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!lastyear.Value)</Value>
                  </DataValue>
                  <DataValue>
                    <Value>=Sum(Fields!lastyear.Value)</Value>
                  </DataValue>
                </DataValues>
                <DataLabel />
                <Style />
                <Marker>
                  <Type>Cross</Type>
                  <Size>6pt</Size>
                </Marker>
              </DataPoint>
            </DataPoints>
          </ChartSeries>
        </ChartData>
        <Style>
          <BackgroundColor>White</BackgroundColor>
        </Style>
        <Title />
        <Left>0.5cm</Left>
      </Chart>
    </ReportItems>
    <Height>10.36905cm</Height>
  </Body>
  <Language>en-US</Language>
  <TopMargin>2.5cm</TopMargin>
  <PageHeight>29.7cm</PageHeight>
</Report>
jimconstable  2010-01-01 00:10:41
Out of curiosity, is there any way to include the Xs and Os or something to point out this year vs last year? I played around with the settings, and didn't see anything obvious...
Margaret  2010-01-03 22:19:04
edited to include Xs and 0s
jimconstable  2010-01-05 22:18:40

related questions

Strip HTML from string in SSRS 2005 (VB.NET)
SQL Server 2008 Reporting Services Control
How do I resolve a System.Security.SecurityException with custom code in SSRS?
Sending e-mail from a Custom SQL Server Reporting Services Delivery Extension
How to aggregate data from SQL Server 2005
Is there a Box Plot graph avalible for Reporting Services 2005?
How do I set a textbox to multi-line in SSRS?
SQL 2005 Reporting Services custom report item (CRI) - what are the limits?
SQL Reporting Services viewer for webpage - can you move the View Report button?
Custom font in SQL Server 2005 Reporting Services
I can share a SQL Server Reporting Services Data SOURCE... what about a Data SET?
Enterprise Reporting Solutions
Internationalization in SSRS
Easy way for Crystal Reports to MS SQL Server Reporting Services conversion
SSRS - Post Publishing Tasks
SSRS - Process dies/goes to sleep after not being used
SSRS - RSS Feeds
SSRS - Uninstall Trial Version of VS Business Intelligence
Optimizing the PDF Export of Huge Reports in Sql Reporting Services 2005
SSRS scheduled reports not working.
Using Parameters in MS Reporting Services (SQL 2008) against an ODBC data source
Conditional Visibility and Page Breaks with SQL Server 2005 Reporting Services
How do you handle audit logging with SSRS?
Reading recommendations for: SQL Server 2005 Reporting Services
How can I dynamically center an image in a MS Reporting Services report?