I am trying to write a query that would allow me to switch the order of the result set by two columns and whether the order is ascending or descending. So far I have:
Code
------------------------------
declare @.Case char(1)
set @.Case = 'A'
select * from marketdata.dbo.dictionary
order by case when @.Case = 'A' then ParentID ASC else ParentID DESC end
, case when @.Case = 'A' then MemberID ASC else MemberID DESC end
------------------------------
My problem is that I get an error on the "ASC" or "DESC".
Thanks for any help offered!Hi, We do it a little differently, we have a stored procedure that accepts a sort variable. The sort variable determines the select used for the query. Here's part of the stored procedure
CREATE Procedure GetAllEmployees
(
@.sort char(1)
/* F = First, Last
L = Last, First
D = Deparment, First, Last
O = Location, First, Last
*/
)
As
if @.sort = 'D' begin
Select
PL.UID ,
PL.NTUserID,
PL.FirstName ,
PL.LastName ,
PL.Department,
LO.LocationName ,
DP.DepartmentName ,
PL.Extension ,
PL.FaxNumber,
PL.Email ,
PL.PagerEmail ,
PL.PagerNumber,
PL.HomePhone1,
PL.HomePhone2,
PL.CellPhone,
PL.bManager,
PL.bVP
from Employees PL inner join Locations LO on PL.Location = LO.UID inner join Departments DP on PL.Department = DP.DepartmentNumber
where bActive="Y"
order by PL.department, firstname, lastname
end
else if @.sort = 'L' begin
Select|||I currently have the functional equivilant of your code. The problem is that someone made a change to two of the selects but missed the last one. I was trying to consolidate the code to improve maintance. Thanks!
Showing posts with label switch. Show all posts
Showing posts with label switch. Show all posts
Thursday, March 22, 2012
Friday, March 9, 2012
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>
> >
> >
> >
> >
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>
> >
> >
> >
> >
Sunday, February 26, 2012
Dynamic connections rsInvalidDataSourceCredentialSetting
Hello,
Im having a few issues with a report this should use dynamic connections.
I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.
This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:
The format of connection string i am using is :
Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"
I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.
as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error
I have also tried it on another server and get the same problem.
Any suggestions?
Thanks
Paul
Did you find an answer for this? I have the same problem.|||No ive tried a number of different work arrounds but no luck
Just coming to terms with the fact i may have to have 10 reports instead of the 1
|||Hello,
I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.
To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.
Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.
Regards,
Sonu.
|||Added an execution account and the report runs like a dream!!
Cheers
Paul
Dynamic connections rsInvalidDataSourceCredentialSetting
Hello,
Im having a few issues with a report this should use dynamic connections.
I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.
This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:
The format of connection string i am using is :
Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"
I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.
as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error
I have also tried it on another server and get the same problem.
Any suggestions?
Thanks
Paul
Did you find an answer for this? I have the same problem.|||No ive tried a number of different work arrounds but no luck
Just coming to terms with the fact i may have to have 10 reports instead of the 1
|||Hello,
I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.
To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.
Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.
Regards,
Sonu.
|||Added an execution account and the report runs like a dream!!
Cheers
Paul
Dynamic connections rsInvalidDataSourceCredentialSetting
Hello,
Im having a few issues with a report this should use dynamic connections.
I have a connection based on an expresssion which uses a switch to evaluate a input and select the appropriate connection string for the item.
This all works fine when in BIDS it happily switches between servers no problems. when i then deploy the report to a SSRS server and try and view it i get the following error:
The format of connection string i am using is :
Data Source=<<server>>;Initial Catalog=staging;uid=<<username>>;pwd=<<password>>"
I have checked the report properties on the SSRS server and the connection correctly reports the conection is expression based and no credentials are required.
as a trouble shooting exersize i tryed a report which you just pass the connection string into instead of using a switch and got the same error
I have also tried it on another server and get the same problem.
Any suggestions?
Thanks
Paul
Did you find an answer for this? I have the same problem.|||
No ive tried a number of different work arrounds but no luck
Just coming to terms with the fact i may have to have 10 reports instead of the 1
|||Hello,
I had a similar issue with Dynamic connections. In the Report Manager, "No credentials are required" option was set. On searching I got to know that we need an unattended execution account in such cases.
To create an unattended execution account, take Reporting Services Configuration Utility -> Execution Account.
Add an existing account. Doing the above procedure solved my issue. Hope this helps. Let me know how it goes.
Regards,
Sonu.
|||Added an execution account and the report runs like a dream!!
Cheers
Paul
Subscribe to:
Posts (Atom)