dynamic resizing of chart in IE window

I've got an ASPX page with four iFrames defined (soft of like a
dashboard).
Each one of the IFrames' source is set to another ASPX page that only
contains a ReportViewer control.
I am trying to make it so that when user dynamically sizes the main
(encompassing) window, it resizes everything contained, including the
Reporting Services Chart. I thought setting the width and height to
100% for the web stuff and to 100pc for the RS Chart would do the
trick, now the chart only displays the entire size of my monitor.
Any thoughts?
I'm not a great web developer, by the way. <grin>
Thanks in advance,
Mark FefermanHi Mark,
There is several way to have the dashboard..
1) instead of using iFrames in asp pages.. you can create a dasboard.rdl
page with four charts with drillthrough to detail reports.. you can avoid the
reportmanager and tools above the report by using a URL based report
execution that way it will display only the dashboard report in the IE. im
currently using this approach..
2)Also instead of using ReportViewer control did you tried using URL based
access to reports in all the 4 iFrames
let me know your thoughts and your progress so far..
Thanks
Bava
"mark.feferman@.gmail.com" wrote:
> I've got an ASPX page with four iFrames defined (soft of like a
> dashboard).
> Each one of the IFrames' source is set to another ASPX page that only
> contains a ReportViewer control.
> I am trying to make it so that when user dynamically sizes the main
> (encompassing) window, it resizes everything contained, including the
> Reporting Services Chart. I thought setting the width and height to
> 100% for the web stuff and to 100pc for the RS Chart would do the
> trick, now the chart only displays the entire size of my monitor.
> Any thoughts?
> I'm not a great web developer, by the way. <grin>
> Thanks in advance,
> Mark Feferman
>

Dynamic labels in chart.

Hi
Is there a way in which I can set the labels for the X and Y axes for a chart programatically? The values for the labels are returned from the database...

I already have a stored proc which acts as the source for the chart. I might have the labels retrieved as a seperate query from the DB.

ThanksThe Y-axis labels are determined by the chart control and are numeric values. You can set the format code property to get special numeric formatting and locale settings.

The X-axis has two modes: numeric ("timescale or numeric values" checkbox) and non-numeric (default). In the non-numeric mode, the labels are determined by the category grouping value expression or the category grouping label expression (if explicitly specified).

-- Robert|||Thanks....

Can we also display the Titles for the X and Y-axes from the database? I have one query that generates the data for the report...

I was wondering if I could set the Titles for these axes from a seperate query from the Database.

Thanks
Kannan

Dynamic labels for X and Y-axis in a chart

Hi
Is there a way in which I can set the labels for the X and Y axes for a
chart programatically? The values for the labels are returned from the
database...
I already have a stored proc which acts as the source for the chart. I might
have the labels retrieved as a seperate query from the DB.
ThanksThe Y-axis labels are determined by the chart control and are numeric
values. You can set the format code property to get special numeric
formatting and locale settings.
The X-axis has two modes: numeric ("timescale or numeric values" checkbox)
and non-numeric (default). In the non-numeric mode, the labels are
determined by the category grouping value expression or the category
grouping label expression (if explicitly specified).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:15141351-1BB5-45D4-8364-B132948612D8@.microsoft.com...
> Hi
> Is there a way in which I can set the labels for the X and Y axes for a
> chart programatically? The values for the labels are returned from the
> database...
> I already have a stored proc which acts as the source for the chart. I
> might
> have the labels retrieved as a seperate query from the DB.
> Thanks|||Thanks...
Can we also display the Titles for the X and Y-axes from the database? I
have one query that generates the data for the report...
I was wondering if I could set the Titles for these axes from a seperate
query from the Database.
Thanks
Kannan
"Robert Bruckner [MSFT]" wrote:
> The Y-axis labels are determined by the chart control and are numeric
> values. You can set the format code property to get special numeric
> formatting and locale settings.
> The X-axis has two modes: numeric ("timescale or numeric values" checkbox)
> and non-numeric (default). In the non-numeric mode, the labels are
> determined by the category grouping value expression or the category
> grouping label expression (if explicitly specified).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "PV" <PV@.discussions.microsoft.com> wrote in message
> news:15141351-1BB5-45D4-8364-B132948612D8@.microsoft.com...
> > Hi
> > Is there a way in which I can set the labels for the X and Y axes for a
> > chart programatically? The values for the labels are returned from the
> > database...
> >
> > I already have a stored proc which acts as the source for the chart. I
> > might
> > have the labels retrieved as a seperate query from the DB.
> >
> > Thanks
>
>|||The x-axis title and y-axis title can be expressions. By using aggregate
functions, you can reference fields from other datasets, e.g.
=First(Fields!XAxisDescription.Value, "OtherDataset")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:7C67DBA0-DD8B-4403-B49B-11B16A51D80B@.microsoft.com...
> Thanks...
> Can we also display the Titles for the X and Y-axes from the database? I
> have one query that generates the data for the report...
> I was wondering if I could set the Titles for these axes from a seperate
> query from the Database.
> Thanks
> Kannan
> "Robert Bruckner [MSFT]" wrote:
>> The Y-axis labels are determined by the chart control and are numeric
>> values. You can set the format code property to get special numeric
>> formatting and locale settings.
>> The X-axis has two modes: numeric ("timescale or numeric values"
>> checkbox)
>> and non-numeric (default). In the non-numeric mode, the labels are
>> determined by the category grouping value expression or the category
>> grouping label expression (if explicitly specified).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "PV" <PV@.discussions.microsoft.com> wrote in message
>> news:15141351-1BB5-45D4-8364-B132948612D8@.microsoft.com...
>> > Hi
>> > Is there a way in which I can set the labels for the X and Y axes for a
>> > chart programatically? The values for the labels are returned from the
>> > database...
>> >
>> > I already have a stored proc which acts as the source for the chart. I
>> > might
>> > have the labels retrieved as a seperate query from the DB.
>> >
>> > Thanks
>>

Dynamic Expressions

Hello all,
With the new chart palette features in SP1 I'm trying to create a dynamic switch expression of colors that is passed from a database, but I can't get the chart to render correctly based on the expression I pass through.
Can this be done?
What I've done so far is create a db table of people with a color assigned to them,
Tom Black
John Yellow
David Orange
I'm then dynamically creating a switch statement that outputs the recordset like so,
=switch (Fields!User.Value = "Tom","Black",Fields!User.Value="John","Yellow",Fields!User.Value="David","Orange",true, "Transparent")
This is then passed through to the Chart's series Style expression from a dataset.
This all works fine, and the chart doesnt error like it can't evaluate the expression, but its not rendering the chart colours either.
If it won't evalutate the dataset field as an expression is there some other way I can dynamically create the expresssion from a db table?
--
Thankyou,
Tim WraggWhat chart type are you using? On which style property do you use the
switch-function? E.g. for a column chart you can modify the fill color and
the border line color. For a line chart you would want to change the border
line color.
Note: are you actually grouping on Fields!User.Value? You might need to
modify your expression to use the First() aggregate function:
=switch (First(Fields!User.Value) = "Tom","Black",
First(Fields!User.Value)="John","Yellow", First(Fields!User.Value)
="David","Orange", true, Nothing)
I also added an example for a pie chart below. You might want to investigate
the example and compare it with your report.
* copy & paste the RDL on the bottom into an empty report (in code view)
* switch back to the report designer layout view
* double-click on the chart
* double-click on the "Units In Stock" button which represents the chart
values - you should now see the "Edit chart values" dialog
* select "Appearance" tab, and click on "Series styles"
* on the "Styles properties" dialog click on "Fill"
* investigate the expression used for determining colors:
=Choose(First(Fields!SupplierID.Value), "Red", "Yellow", Nothing)
MSDN docs for Choose function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
Explanation: if SupplierID = 1, then the pie slice will be shown in Red, if
SupplierID = 2, the pie slice will be Yellow, otherwise (because of the use
of Nothing) the default color defined by the chart color palette will apply.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
==================================================
<?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>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundColor>=Choose(First(Fields!SupplierID.Value),
"Red", "Yellow", Nothing)</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<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>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Pie</Type>
<Top>0.125in</Top>
<Title />
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.25in</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>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 7 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Hi Robert,
Thanks for your prompt reply.
I'm using the Pie Chart and the Series Style:Fill Property.
I know this expression works as I've hardcoded it into this property and the chart renders with the colors fine.
But what I'm trying to do is keep away from the hardcoding of names and colors in the expression and pass the whole expression (including the =switch) from my dataset.
So I've got a dataset called 'Formatting' that returns one field called 'ColorExpression' which is my expression string,
(=switch (First(Fields!User.Value) = "Tom","Black",First(Fields!User.Value)="John","Yellow",First(Fields!User.Value)="David","Orange", true, Nothing)
and im outputting that in the Fill property like so.
=First(Fields!ColorExpression.Value, "Formatting")
This is where the problem is, I've gathered that the Fill property wont see my outputted field as an expression and hence I can't think how I can keep both names and colors dynamic.
Thankyou,
Tim Wragg
"Robert Bruckner [MSFT]" wrote:
> What chart type are you using? On which style property do you use the
> switch-function? E.g. for a column chart you can modify the fill color and
> the border line color. For a line chart you would want to change the border
> line color.
> Note: are you actually grouping on Fields!User.Value? You might need to
> modify your expression to use the First() aggregate function:
> =switch (First(Fields!User.Value) = "Tom","Black",
> First(Fields!User.Value)="John","Yellow", First(Fields!User.Value)
> ="David","Orange", true, Nothing)
>
> I also added an example for a pie chart below. You might want to investigate
> the example and compare it with your report.
> * copy & paste the RDL on the bottom into an empty report (in code view)
> * switch back to the report designer layout view
> * double-click on the chart
> * double-click on the "Units In Stock" button which represents the chart
> values - you should now see the "Edit chart values" dialog
> * select "Appearance" tab, and click on "Series styles"
> * on the "Styles properties" dialog click on "Fill"
> * investigate the expression used for determining colors:
> =Choose(First(Fields!SupplierID.Value), "Red", "Yellow", Nothing)
> MSDN docs for Choose function:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
> Explanation: if SupplierID = 1, then the pie slice will be shown in Red, if
> SupplierID = 2, the pie slice will be Yellow, otherwise (because of the use
> of Nothing) the default color defined by the chart color palette will apply.
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> ==================================================> <?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>BottomCenter</Position>
> <Layout>Table</Layout>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitsInStock.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> <Visible>true</Visible>
> </DataLabel>
> <Style>
> <BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
> <BackgroundColor>=Choose(First(Fields!SupplierID.Value),
> "Red", "Yellow", Nothing)</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <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>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Pie</Type>
> <Top>0.125in</Top>
> <Title />
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="chart1_CategoryGroup1">
> <GroupExpressions>
> <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ProductName.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <Left>0.25in</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>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>5in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SupplierID">
> <DataField>SupplierID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CategoryID">
> <DataField>CategoryID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="QuantityPerUnit">
> <DataField>QuantityPerUnit</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="UnitsInStock">
> <DataField>UnitsInStock</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="UnitsOnOrder">
> <DataField>UnitsOnOrder</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="ReorderLevel">
> <DataField>ReorderLevel</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="Discontinued">
> <DataField>Discontinued</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="NullUnits">
> <DataField>NullUnits</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT TOP 7 *, NULL AS NullUnits
> FROM [Alphabetical list of products]
> WHERE (UnitsOnOrder > 0)</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>|||You should modify your dataset. Since it looks like your chart dataset
contains a User field and the Formatting table also contains a User field
you should join the two tables in the chart dataset query. Through the join,
the colors would be joined to the dataset and you could use an expression
like =Fields!ColorCode.Value in the series/datapoint styles.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Wragg" <TimWragg@.discussions.microsoft.com> wrote in message
news:3540FA53-F0BE-495E-959C-BEFA91A86CAE@.microsoft.com...
> Hi Robert,
> Thanks for your prompt reply.
> I'm using the Pie Chart and the Series Style:Fill Property.
> I know this expression works as I've hardcoded it into this property and
the chart renders with the colors fine.
> But what I'm trying to do is keep away from the hardcoding of names and
colors in the expression and pass the whole expression (including the
=switch) from my dataset.
> So I've got a dataset called 'Formatting' that returns one field called
'ColorExpression' which is my expression string,
> (=switch (First(Fields!User.Value) ="Tom","Black",First(Fields!User.Value)="John","Yellow",First(Fields!User.Val
ue)="David","Orange", true, Nothing)
> and im outputting that in the Fill property like so.
> =First(Fields!ColorExpression.Value, "Formatting")
> This is where the problem is, I've gathered that the Fill property wont
see my outputted field as an expression and hence I can't think how I can
keep both names and colors dynamic.
>
> --
> Thankyou,
> Tim Wragg
>
> "Robert Bruckner [MSFT]" wrote:
> > What chart type are you using? On which style property do you use the
> > switch-function? E.g. for a column chart you can modify the fill color
and
> > the border line color. For a line chart you would want to change the
border
> > line color.
> >
> > Note: are you actually grouping on Fields!User.Value? You might need to
> > modify your expression to use the First() aggregate function:
> > =switch (First(Fields!User.Value) = "Tom","Black",
> > First(Fields!User.Value)="John","Yellow", First(Fields!User.Value)
> > ="David","Orange", true, Nothing)
> >
> >
> > I also added an example for a pie chart below. You might want to
investigate
> > the example and compare it with your report.
> > * copy & paste the RDL on the bottom into an empty report (in code view)
> > * switch back to the report designer layout view
> > * double-click on the chart
> > * double-click on the "Units In Stock" button which represents the chart
> > values - you should now see the "Edit chart values" dialog
> > * select "Appearance" tab, and click on "Series styles"
> > * on the "Styles properties" dialog click on "Fill"
> > * investigate the expression used for determining colors:
> > =Choose(First(Fields!SupplierID.Value), "Red", "Yellow", Nothing)
> >
> > MSDN docs for Choose function:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
> >
> > Explanation: if SupplierID = 1, then the pie slice will be shown in Red,
if
> > SupplierID = 2, the pie slice will be Yellow, otherwise (because of the
use
> > of Nothing) the default color defined by the chart color palette will
apply.
> >
> > --
> > Robert M. Bruckner
> > Microsoft SQL Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > ==================================================> >
> > <?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>BottomCenter</Position>
> > <Layout>Table</Layout>
> > </Legend>
> > <Palette>Default</Palette>
> > <ChartData>
> > <ChartSeries>
> > <DataPoints>
> > <DataPoint>
> > <DataValues>
> > <DataValue>
> > <Value>=Sum(Fields!UnitsInStock.Value)</Value>
> > </DataValue>
> > </DataValues>
> > <DataLabel>
> > <Style />
> > <Visible>true</Visible>
> > </DataLabel>
> > <Style>
> >
> > <BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
> >
<BackgroundColor>=Choose(First(Fields!SupplierID.Value),
> > "Red", "Yellow", Nothing)</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > <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>
> > <Margin>true</Margin>
> > <Visible>true</Visible>
> > </Axis>
> > </CategoryAxis>
> > <DataSetName>Northwind</DataSetName>
> > <PointWidth>0</PointWidth>
> > <Type>Pie</Type>
> > <Top>0.125in</Top>
> > <Title />
> > <CategoryGroupings>
> > <CategoryGrouping>
> > <DynamicCategories>
> > <Grouping Name="chart1_CategoryGroup1">
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Label>=Fields!ProductName.Value</Label>
> > </DynamicCategories>
> > </CategoryGrouping>
> > </CategoryGroupings>
> > <Subtype>Plain</Subtype>
> > <PlotArea>
> > <Style>
> > <BackgroundColor>LightGrey</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </PlotArea>
> > <Left>0.25in</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>
> > <Visible>true</Visible>
> > <Scalar>true</Scalar>
> > </Axis>
> > </ValueAxis>
> > </Chart>
> > </ReportItems>
> > <Style />
> > <Height>5in</Height>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> > <rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=(local);initial
> > catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>6.5in</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="ProductID">
> > <DataField>ProductID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > <Field Name="ProductName">
> > <DataField>ProductName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="SupplierID">
> > <DataField>SupplierID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > <Field Name="CategoryID">
> > <DataField>CategoryID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > <Field Name="QuantityPerUnit">
> > <DataField>QuantityPerUnit</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="UnitPrice">
> > <DataField>UnitPrice</DataField>
> > <rd:TypeName>System.Decimal</rd:TypeName>
> > </Field>
> > <Field Name="UnitsInStock">
> > <DataField>UnitsInStock</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="UnitsOnOrder">
> > <DataField>UnitsOnOrder</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="ReorderLevel">
> > <DataField>ReorderLevel</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="Discontinued">
> > <DataField>Discontinued</DataField>
> > <rd:TypeName>System.Boolean</rd:TypeName>
> > </Field>
> > <Field Name="CategoryName">
> > <DataField>CategoryName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="NullUnits">
> > <DataField>NullUnits</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT TOP 7 *, NULL AS NullUnits
> > FROM [Alphabetical list of products]
> > WHERE (UnitsOnOrder > 0)</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
> > <BottomMargin>1in</BottomMargin>
> > </Report>
> >
> >
> >
> >

Dynamic Dataset for Chart in Report

Is it possible to dynamically set the dataset for a chart on a report?

What I'm trying to do is generate a single chart on a report. Depending on what items a user selects as parameters would determine the dataset used. Unfortunately, one dataset comes from Oracle the other from SQLServer.

Thanks!

You could have two charts - one per dataset. Set the chart.Visibility property so that only one chart is visible depending on the selected parameter value.

-- Robert

Dynamic chart labels (changing the colors on the fly)

I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...

Any help?

Thanks in advance,
Pete

1. Right click the field and select properties.

2. Click the point labels tab.

3. Click the label style button.

4. In the color expression, enter something like this:

=IIf(Fields!Year.Value = 1900, "Black", "Black")

Do the same for the Qtr field.

=IIf(Fields!Qtr.Value = " ", "Blue", "Blue")

The expressions may not be 100% accurate, but if you play around with this, you should get what you want.

Dynamic chart labels (changing the colors on the fly)

I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...

Any help?

Thanks in advance,
Pete

1. Right click the field and select properties.

2. Click the point labels tab.

3. Click the label style button.

4. In the color expression, enter something like this:

=IIf(Fields!Year.Value = 1900, "Black", "Black")

Do the same for the Qtr field.

=IIf(Fields!Qtr.Value = " ", "Blue", "Blue")

The expressions may not be 100% accurate, but if you play around with this, you should get what you want.

Dynamic change of number of series on the chart

Depending on the result of my stored procedure logic I need to display
different number of lines on the chart in my report. I can not drop the data
fileds into the chart design as suggested in the Help, because at design time
I do not know their names and how many of them I will have when report is
called. Is it possible? What is involved?
Thanks.Not sure I understand what you are trying to do. It sounds like you want a
dynamic series grouping expression which is basically just based on the
field that comes from the stored procedure.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> Depending on the result of my stored procedure logic I need to display
> different number of lines on the chart in my report. I can not drop the
> data
> fileds into the chart design as suggested in the Help, because at design
> time
> I do not know their names and how many of them I will have when report is
> called. Is it possible? What is involved?
> Thanks.|||Thank you Robert,
You indirectly answered my question. I just could not make sense of grouping
capabilities of the chart. The Help on this topic is not very "helpful".
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand what you are trying to do. It sounds like you want a
> dynamic series grouping expression which is basically just based on the
> field that comes from the stored procedure.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > Depending on the result of my stored procedure logic I need to display
> > different number of lines on the chart in my report. I can not drop the
> > data
> > fileds into the chart design as suggested in the Help, because at design
> > time
> > I do not know their names and how many of them I will have when report is
> > called. Is it possible? What is involved?
> > Thanks.
>
>|||I am having to do the same thing like dynamically having series elements
depending on the query results... is this possible to do?
"Simon Gold" wrote:
> Thank you Robert,
> You indirectly answered my question. I just could not make sense of grouping
> capabilities of the chart. The Help on this topic is not very "helpful".
> "Robert Bruckner [MSFT]" wrote:
> > Not sure I understand what you are trying to do. It sounds like you want a
> > dynamic series grouping expression which is basically just based on the
> > field that comes from the stored procedure.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > Depending on the result of my stored procedure logic I need to display
> > > different number of lines on the chart in my report. I can not drop the
> > > data
> > > fileds into the chart design as suggested in the Help, because at design
> > > time
> > > I do not know their names and how many of them I will have when report is
> > > called. Is it possible? What is involved?
> > > Thanks.
> >
> >
> >|||Yes, It is possible to do that. The "Help" does not have a good wordage on
it, but here how you do it.
Imagine that you want to display stock prices for several different
companies for a say 10 days period. Create a table 'Prices' that would have
at least three columns: 'DayNumber', 'StockPrice' and 'CompanyName'. Populate
that table with your data. Create a dataset for your chart where run a
"SELECT DayNumber,StockPrice, CompanyName FROM Prices".
Om the chart drug and drop 'DayNumber field under the horizontal axis
(Category fields), drop StockPrice field above the chart into 'data fields'
and drop 'Companyname' filed to the right of the chart into 'Series fileds'.
This will create a chart that will have as many series as many company names
you have in your table.
Good luck...
Simon.
StockPrice
"Mathi" wrote:
> I am having to do the same thing like dynamically having series elements
> depending on the query results... is this possible to do?
> "Simon Gold" wrote:
> > Thank you Robert,
> > You indirectly answered my question. I just could not make sense of grouping
> > capabilities of the chart. The Help on this topic is not very "helpful".
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Not sure I understand what you are trying to do. It sounds like you want a
> > > dynamic series grouping expression which is basically just based on the
> > > field that comes from the stored procedure.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > > Depending on the result of my stored procedure logic I need to display
> > > > different number of lines on the chart in my report. I can not drop the
> > > > data
> > > > fileds into the chart design as suggested in the Help, because at design
> > > > time
> > > > I do not know their names and how many of them I will have when report is
> > > > called. Is it possible? What is involved?
> > > > Thanks.
> > >
> > >
> > >