sql server reporting services: Can Users choose Rows & columns for a matrix?? -- sql server reporting services

You're in the

sql server reporting services

group:

Can Users choose Rows & columns for a matrix??

pmud
10/24/2005 10:26:07 AM

sql server reporting services:

Hi,

Is it possible to create a report which allows users to select the fields
for the matrix in a report. To explain in detail, can we allow the users to
select the X and Y axis for a matrix in a report?

For ex., there is a report containing a matrix which shows the total sales(
data cell) by month (X axis) and by Company( Y axis). Can we have some option
so that if the users select Year as the X axis and SalesPerson as the Y axis
then they can view the same report but by SalesPerson and Year instead od
Month and Company?

Any help is highly appreciated.

Thanks
--

Re: Can Users choose Rows & columns for a matrix??

Robert Bruckner [MSFT]
10/24/2005 9:11:55 PM

Yes, this is possible by using dynamic field references for the grouping
expression and the matrix group header textbox expression. E.g.
=Fields(Parameters!RowGroup.Value).Value
Note: the actual field name in the expression above will be determined by
the parameter's value at runtime. You can use this for the row and the
column grouping of the matrix.

A RS 2005 sample report (which also includes InteractiveSort on the dynamic
matrix row groups) is attached. Note: you cannot load this sample in the RS
2000 report designer.

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

[quoted text, click to view]

=====================================================

<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="northwind">
<DataSourceReference>northwind</DataSourceReference>
<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="RowGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ProductName</Value>
</Values>
</DefaultValue>
<Prompt>RowGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ProductName</Value>
<Label>By Product Name</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
<ParameterValue>
<Value>CategoryID</Value>
<Label>By Category ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ColumnGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ReorderLevel</Value>
</Values>
</DefaultValue>
<Prompt>ColumnGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ReorderLevel</Value>
<Label>By Reorder Level</Label>
</ParameterValue>
<ParameterValue>
<Value>UnitsInStock</Value>
<Label>By Stock</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.125in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
</Textbox>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>2.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!RowGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>

Re: Can Users choose Rows & columns for a matrix??

pmud
11/8/2005 9:26:06 AM

Hi Robert,

I dont have RS 2005. So the way you told ( by having
=Fields(Parameters!RowGroup.Value)) will work for RS 2000 too?

Thanks
--
pmud

[quoted text, click to view]

Re: Can Users choose Rows & columns for a matrix??

Brian Seery
11/8/2005 10:31:57 AM

Robert -

I have been trying something very similar to this except that it takes
it one step further. My matrix has multiple RowGroups and multiple
ColumnGroups. I'd like the user to be able to turn some off, but I
cannot keep the RowGroup fields from always appearing in the matrix.

I use expressions to turn the RowGrouping on/off. When the RowGroup is
not to appear, the expression evaluates to "".

I also use expressions to set the RowGroup visibility to false.

Is there any way to do this? Maybe I am doing something wrong?

Thanks.

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.

home · blog · groups

Questions? Comments? Contact the dn