RS2K & Lines Chart

Hello,
I'm trying to make RS2K chart values from a SQL request.
Datas to chart are contained in the only row returned by a SQL request
like the following :
SELECT TOP 1 A.cyl, A.ind, A.usi, A.dt1, A.dt2, A.dt3, A.dt4, A.dt5,
A.dt6, A.dt7, A.dt8, A.dt9, A.dt10, A.dt11, A.dt12, B.tmin, B.tmax,
B.rmin, B.rmax
FROM A
INNER JOIN B ON A.mach = B.mach
WHERE (A.cyl = @.Cyl)
ORDER BY A.ind DESC, A.usi DESC
I'd like to make the Chart component draw a line using values A.dt1 to
A.dt12 and so far, I have not succeeded.
Any idea ?
Thanks in advance
RegardsWhere are you stuck trying to do that ?
"Emss" <emss@.free.fr> wrote in message
news:45ffcd66$0$21229$426a74cc@.news.free.fr...
> Hello,
> I'm trying to make RS2K chart values from a SQL request.
> Datas to chart are contained in the only row returned by a SQL request
> like the following :
> SELECT TOP 1 A.cyl, A.ind, A.usi, A.dt1, A.dt2, A.dt3, A.dt4, A.dt5,
> A.dt6, A.dt7, A.dt8, A.dt9, A.dt10, A.dt11, A.dt12, B.tmin, B.tmax,
> B.rmin, B.rmax
> FROM A
> INNER JOIN B ON A.mach = B.mach
> WHERE (A.cyl = @.Cyl)
> ORDER BY A.ind DESC, A.usi DESC
> I'd like to make the Chart component draw a line using values A.dt1 to
> A.dt12 and so far, I have not succeeded.
> Any idea ?
> Thanks in advance
> Regards|||Julien Bonnier a écrit :
Hello,
> Where are you stuck trying to do that ?
I've dragged the chart component on the report layout and dropped fields
in the Data Fields, Series Fields or Categories Fields without any result.
Regards

RS2005- Pie Chart Percentages and Filter

I have a pie chart with the following filter expression:
=Parameters!Year.Value
The point labels for the pie chart are determined from the following
expression:
=sum(Fields!Expr1.Value)/sum(Fields!Expr1.Value, "SalesDM")
My problem is that the above calulation for percentage is incorrect for the
filtered dataset; the percentage is of the whole dataset. How do I get the
percentage to honor the filter?Hi,
welcome to the MSDN newsgroup.
From your description, I understand you're using the SSRS 2005 to create
some reports, and when using the Pie chart together with some filters on
the data, you found the pie chart always display the value from unfiltered
data, correct?
Based on my local testing, this is likely a project or environment specific
issue. I've tested a Pie chart report with filters setting in both Chart
region and in Dataset and both of them work. Are you using the filters in
chart region or in dataset? Or have you tried other report or datasource to
see whether this is a datasource specific issue.
Please feel free to let me know if there's anything I missed.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you for responding, Steven.
My dataset, SalesDM, returns data for several categories over two years.
There are no filters on the dataset. The dataset is used for a table, and is
unfiltered- it returns results as expected. I then use the dataset for a pie
chart. I set up a chart filter for one year's worth of data from the
dataset.- it properly returns results. When I calculate the point labels for
the pie chart, I use the expression in my original post. That calculation
does not take into account the filter applied to the chart.
I'm guessing that I need to reformulate my expression, but I don't know what
the proper formulation should be.
"Steven Cheng[MSFT]" wrote:
> Hi,
> welcome to the MSDN newsgroup.
> From your description, I understand you're using the SSRS 2005 to create
> some reports, and when using the Pie chart together with some filters on
> the data, you found the pie chart always display the value from unfiltered
> data, correct?
> Based on my local testing, this is likely a project or environment specific
> issue. I've tested a Pie chart report with filters setting in both Chart
> region and in Dataset and both of them work. Are you using the filters in
> chart region or in dataset? Or have you tried other report or datasource to
> see whether this is a datasource specific issue.
> Please feel free to let me know if there's anything I missed.
>
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Thanks for your response.
Yes, I agree with you that the problem is specific to the Point Label's
data expression. And I think the problem is likely due to the denominator
in your expression.
the "sum(Fields!Expr1.Value)" will return the current item's sum value on
Expr1 field. However, as for the "sum(Fields!Expr1.Value, "SalesDM")", I'm
not sure what is "SalesDM" exactly, but I guess it is the Dataset's
name(which is bound to your chart), correct? If so, this will cause the
data be retrieved directly from DataSet which is not filtered by the filter
you defined in chart. If this is the case, you can consider specific the
Chart's name in the sum expression. e.g:
sum(Fields!Expr1.Value, "PieChart1")
this will make the data be retrieved from the chart's dataregion which is
filled by the chart's filters. Or alternatively, you can also define
another dataset which use the same filters like your chart control and make
the sum expression specified that dataset as the scope value.
Hope this helps,
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you, Steven! Referencing the chart's dataregion is exactly the answer
I was looking for.
"Steven Cheng[MSFT]" wrote:
> Thanks for your response.
> Yes, I agree with you that the problem is specific to the Point Label's
> data expression. And I think the problem is likely due to the denominator
> in your expression.
> the "sum(Fields!Expr1.Value)" will return the current item's sum value on
> Expr1 field. However, as for the "sum(Fields!Expr1.Value, "SalesDM")", I'm
> not sure what is "SalesDM" exactly, but I guess it is the Dataset's
> name(which is bound to your chart), correct? If so, this will cause the
> data be retrieved directly from DataSet which is not filtered by the filter
> you defined in chart. If this is the case, you can consider specific the
> Chart's name in the sum expression. e.g:
> sum(Fields!Expr1.Value, "PieChart1")
> this will make the data be retrieved from the chart's dataregion which is
> filled by the chart's filters. Or alternatively, you can also define
> another dataset which use the same filters like your chart control and make
> the sum expression specified that dataset as the scope value.
> Hope this helps,
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||You're welcome!
Also glad that we've got the problem resolved.
Have a good day!
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

RS Charts - possible to add more types?

I'm creating a chart showing different types of work hours registered per
person during a given period. I'm using a "Simple Column" chart type. It
works nicely. But I'm quite sure that our customer wants a different chart
type, that can be found in Excel 2003. It's a 3D Column chart, that shows
the different values related to each other. Not stacked, but as columns
behind each other. There is no such chart in Reporting Services SP1.
Is it possible to add more chart types to RS SP1? How? Or is it possible to
hand code the RDL file to create a view that is like what the customer
wants?
The person in charge is very probably going to ask me about the layout of
the chart, so I just want to check...
Kaisa M. LindahlRS 2000 does not provide any additional chart types. However, it allows you
to use so-called static categories when you hand-code (!) the RDL file. Keep
in mind - a chart works similar to a matrix - static categories would be
static column groupings in a matrix. Chart series groupings are comparable
to row groupings in a matrix. It may be useful to first play with the data
in a matrix till you have the right structure, and then generate the chart
RDL from there. You may be interested in carefully reading this related
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=e384f9b6-d6d4-468d-b7c5-2ec0b680af14&sloc=en-us
In your case it sounds like you are looking for the ability to use static
categories in the chart. You will find a trivial example at the bottom of
this posting. Note: the report designer layout mode does not support static
categories - if you modify the layout it will reserialize the RDL and remove
static categories from the chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
All code samples are provided "AS IS" without warranty of any kind, either
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:egVx4QU$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> I'm creating a chart showing different types of work hours registered per
> person during a given period. I'm using a "Simple Column" chart type. It
> works nicely. But I'm quite sure that our customer wants a different chart
> type, that can be found in Excel 2003. It's a 3D Column chart, that shows
> the different values related to each other. Not stacked, but as columns
> behind each other. There is no such chart in Reporting Services SP1.
> Is it possible to add more chart types to RS SP1? How? Or is it possible
to
> hand code the RDL file to create a view that is like what the customer
> wants?
> The person in charge is very probably going to ask me about the layout of
> the chart, so I just want to check...
> Kaisa M. Lindahl
===================================================<?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>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!FY2004.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!FY2005.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<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>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Column</Type>
<Top>0.125in</Top>
<Title />
<Width>3in</Width>
<Height>3in</Height>
<CategoryGroupings>
<CategoryGrouping>
<StaticCategories>
<StaticMember>
<Label>FY2004</Label>
</StaticMember>
<StaticMember>
<Label>FY2005</Label>
</StaticMember>
</StaticCategories>
</CategoryGrouping>
</CategoryGroupings>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!Series.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.5in</Left>
<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>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>47e7de6a-2997-40c7-aa4c-e4eaacc48fe0</rd:DataSourceID>
<DataSourceReference>Northwind</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="FY2004">
<DataField>FY2004</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="FY2005">
<DataField>FY2005</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Series">
<DataField>Series</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select 191 as FY2004, 195 as FY2005, 'A' as Series
union all
select 188 as FY2004, 183 as FY2005, 'B' as Series</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>0a301010-0cf6-4e19-b90b-73a955e4965b</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>