Static & Dynamic Columns in One Matrix? -- sql server reporting services

sql server reporting services : Static & Dynamic Columns in One Matrix?

glb1975
9/23/2004 11:07:04 PM

I am trying to create a Matrix that contains two static columns followed by x
number of dynamic columns.

Ex. (x, y and z just fill in space and are not relavent to my problem)

staticCol1 staticCol2 dynamicCol1 dynamicCol2 ...
row1 x x x x
row2 y y y y
row3 z z z z

Is this possible?

I can get the following, but this is not what I want.

dynamicCol1 dynamicCol2
staticCol1 staticCol2 staticCol1 staticCol2
row1 x x x x
row2 y y y y
row3 z z z z

I realize that I could change my sql to always return the static data as
rows in the result set appended to the dynamic data but that is not what I
want to do.

Can someone please help?

Scott Nichols
9/24/2004 4:05:02 PM

I'm not sure of a way to add the columns in, since those are based of data in
the result set.

This might not be what you're looking for, but you can achieve the same
effect of static columns by adding row groups to the matrix, with the
grouping value equal to the static data, or an expression based on another
field if the data changes somewhat.

Then add a rectangle to the matrix header above the row groups, and place
textboxes in the rectangle to simulate the static columns.

Here's some example RDL, but you'll need to update the datasource properties:

<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Rectangle Name="rectangle1">
<ZIndex>5</ZIndex>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="staticCol2"</Value>
<Left>2in</Left>
</Textbox>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>1in</Width>
<CanGrow>true</CanGrow>
<Value>="staticCol1"</Value>
<Left>1in</Left>
</Textbox>
</ReportItems>
<Style />
</Rectangle>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Val">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Val</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Val.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_Col">
<GroupExpressions>
<GroupExpression>=Fields!Col.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Col">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>Col</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Col.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>4in</Width>
<Top>0.125in</Top>
<Left>0.25in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Row">
<GroupExpressions>
<GroupExpression>=Fields!Row.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Row">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Row</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Row.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup2">
<GroupExpressions>
<GroupExpression>="static"</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>

Neil Gould
10/29/2004 9:42:11 AM

When I try to run the example provided, I get this error:

Deserialization failed: Guid should contain 32 digits with 4 dashes
(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

The guid does look correct below. What else is it?
Thanks,
Neil

[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