Chart, drawing a line across all value for one value -- sql server reporting services

sql server reporting services : Chart, drawing a line across all value for one value

Ravi Mumulla (Microsoft)
8/24/2004 8:26:53 AM

See the attached example at the end of this post.

--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%2337cdfeiEHA.1040@TK2MSFTNGP11.phx.gbl...
[quoted text, click to view]

<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Freight.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!Expr1.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<Style>
<FontSize>8pt</FontSize>
</Style>
<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>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Column</Type>
<Title />
<Width>5.25in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Left(Format(Fields!OrderDate.Value, "MMM"), 1) & "
[" & Format(Fields!OrderDate.Value, "MMM yyyy") & "]"</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Freight</Label>
</StaticMember>
<StaticMember>
<Label>Expr1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<Style>
<Format>c</Format>
<FontSize>8pt</FontSize>
</Style>
<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>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">

<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>

Robert Bruckner [MSFT]
8/24/2004 1:07:30 PM

You don't need the extra column in the dataset or a calculated field.
Instead of using =Fields!Expr1.Value as value expression in Ravi's sample,
use e.g. =50 as value expression. You could even use the first row of
another dataset, e.g. =First(Fields!Threshold.Value, "OtherDataSetName")

--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:OaumtIhiEHA.636@TK2MSFTNGP12.phx.gbl...
[quoted text, click to view]

Tibor Karaszi
8/24/2004 4:37:16 PM

Can I specify an expression somewhere so a horizontal line is drawn for a
particular row in the dataset?
This would be easier to draw than explain, but I'll try:

Imagine you have a bar chart displaying both detailed information (say sales
per month for each department) and also sales per month for the whole
company. I do not perform any aggregation in the chart control, this is done
in the dataset/stored procedure (see below why). Say that the bars are
vertical. I now want a horizontal reference line be drawn across the whole
plot surface, showing the average sales for the whole company. This is so
that each department quickly can visualize if their particular bar is below
or above this company average value. Can I specify an expression somewhere
so a horizontal line is drawn for a particular row in the dataset?

More information: I do not aggregate in the chart as I actually have
department, region and the whole company. The chart should display average
for the whole company, for each region and also for each department, *but*
only for the region that this particular department belongs to (which is a
parameter to the report). So the company and each region's average is based
on all underlying data, where not all region detailed information is to be
displayed.

Note, the bars are actually horizontal, shouldn't affect the question, I
hope.

Thanks
Tibor Karaszi

Tibor Karaszi
8/24/2004 9:40:12 PM

Thanks Ravi. You spotted the presentation I want. One thing, if I might:

In the dataset, you return an extra column with the same value for each row and use that value for the line
(clever :-) ). I can do that using a scalar subquery in the column list, no problem.

Unless I hear anything back, I take it that I cannot take the value from a *row* to accomplish this...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]
<snip>

Tibor Karaszi
8/24/2004 10:16:53 PM

Ouch, I spoke too soon. I just realized that the option "Plot data as line" is not available for a "Simple
Bar" chart.

Any other options?

(I have a feeling that I'm out of luck with this one... :-( )

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

Tibor Karaszi
8/24/2004 11:11:23 PM

Interesting options, thanks.
A constant is not good in this case, as the value is the average for the whole company, i.e., it changes with
each execution (over time).

If the row were returned as the first row for the dataset, I could use that, but for some reason, the customer
want each region first, then the average for the whole company, then each department.

But yes, I could add another dataset returning one row and use First for that. Thanks!

Any thoughts on whether this is possibly for a Bar chart? ("Plot data as line" or the same effect.)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn