sql server reporting services: Alternating line shading in a matrix -- sql server reporting services

Re: Alternating line shading in a matrix

Bruce Johnson [MSFT]
8/5/2004 3:00:30 PM

sql server reporting services: See the sample report at the end of this posting.

--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services

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

[quoted text, click to view]

MatrixGreenbar.rdl

<?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>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qty">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Qty</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Qty.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="Category">
<GroupExpressions>

<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.125in</Top>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Right>None</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothin
g) Mod 2, "AliceBlue", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value & " " &
RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="Count">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Color">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Left>None</Left>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=Value</BackgroundColor>
<FontSize>1pt</FontSize>
<Color>=Value</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>

<Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2,
"AliceBlue", "White")</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">

<rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>

Re: Alternating line shading in a matrix

Michael Carr
8/5/2004 9:04:48 PM

Bruce,

Thanks a lot, that works really well!!

It would be nice if I could get at the ReportItem.Style.BackgroundColor
directly rather than relying on a hidden ReportItem.Value ... then I could
implement the necessary computations on the row header's BackgroundColor and
then just reference that property from the cells in the matrix. I see from a
little poking around with Reflector that I can't currently get at that value
through ReportItem ... :)

While I'm on the subject ... how about a tri-state for textbox visibility
that will "Show All", "Hide Text", and "Hide All". There are many instances
(such as this one, for example) where I'd like to hide the text only and
allow the styles to come through.

Thanks,
Michael Carr

[quoted text, click to view]

Re: Alternating line shading in a matrix

Bruce Johnson [MSFT]
8/6/2004 12:51:21 PM

Thank your for your comments. Glad the report worked out for you.

Your first issue is a known issue and will be addressed sometime in the
future by exposing the group number.

Your tri state can be achieved via an expression by setting the value to
=iif(condition, "text", Nothing).

Bruce

--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services

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

[quoted text, click to view]

Re: Alternating line shading in a matrix

Michael Carr
8/6/2004 7:02:07 PM

Bruce,

The tri-state expression doesn't work in this case because you need the
value to be the color you want to use as the background color for the other
text boxes. Using your tri-state, the value would be Nothing and thus would
not work to describe the background color you really wanted. In your example
report, you implemented "hidden" text by making it the same color as the
background and 1pt size... it works, but it's not as elegant as it could be.

Thanks,
Michael Carr

[quoted text, click to view]

Re: Alternating line shading in a matrix

elad NO[at]SPAM cydoor.com
8/11/2004 9:08:51 AM

Is it possible to describe the steps you perform in the development

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 in the sql server reporting services group.