Dynamic Expressions -- sql server reporting services

sql server reporting services : Dynamic Expressions

Tim Wragg
6/30/2004 9:42:02 PM

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,

Robert Bruckner [MSFT]
6/30/2004 11:12:22 PM

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>

Tim Wragg
6/30/2004 11:47:01 PM

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

[quoted text, click to view]

Robert Bruckner [MSFT]
7/1/2004 9:04:09 PM

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.

[quoted text, click to view]
colors in the expression and pass the whole expression (including the
=switch) from my dataset.
[quoted text, click to view]
"Tom","Black",First(Fields!User.Value)="John","Yellow",First(Fields!User.Val
ue)="David","Orange", true, Nothing)
[quoted text, click to view]
see my outputted field as an expression and hence I can't think how I can
keep both names and colors dynamic.
[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