Integrated Security: matrix

Showing posts with label matrix. Show all posts

Monday, March 19, 2012

InteractiveHeigt and 6.000 rows on first HTML page?

First the good news:
A Matrix receives around 55.000 Datarows and produces 1.300 pages of PDF.
This happens in:
- SQL-Query: 5 Seconds
- IIS rendering: 10 Seconds for the first HTML-page
- PDF export: 120 seconds
Really great product.
OK, now the problem:
When the data of a customer in the database is rendered with:
- this customer has around 6.000 rows in the matrix
- AND the first column in the Matrix is continuous
(that means: all other about 10 columns are subdata to the first column)
--> it happens the unexpected:
ALL the 6.000 rows appear on the first HTML-page
Changes in InteractiveHeight do not affect this HTML-Rendering on this first
page.
The InteractiveHeight works perfectly from the second page as expected, but
not on this first.
What happens:
Case 1 in IE (6 or 7):
- SQL-Query 5 Seconds
(sqlserv.exe goes up to 150 MB)
- IIS rendering: 10 seconds
(w3wp.exe goes up to 135 MB)
- IExplorer.exe runs for 60 seconds with 100% CPU-usage
(iexplorer.exe memory footprint 100 MB)
After this the first page appears and all is almost well.
Because of 6.000 rows in one IE-page, scrolling is worse than on an Intel
286, but exporting to PDF is no pain.
Case 2 the same in ReportViewerControl (Redistributable 2005):
- SQL-Query 5 Seconds as above
(sqlserv.exe goes up to 150 MB)
- IIS rendering: indefinitely <-- = the problem
w3wp.exe consumes indefinitely 250 MB
- ReportViewer-Control message after 10 minutes:
"The request was aborted: The operation has timed out."
I can shut down the ReportViewer-Control WinApp, IIS continues with 250 MB
and 100% CPU. Poor Windows Enterpise Server 2003R2
----
Questions:
A: How do I achieve pagebreaks for the first 6.000 rows in the Matrix
despite the first column being continuous?
(There is a bug in the HTML-Render-Engine that doesn't accept
InteractiveHeight in this case.)
B: Please supply me with the newest ReportViewer.exe Redistributable March
2007
(it's ready, but will only be distributed to the public in one week
because of the need to write a KB-article)
We need to know, if this will remedy our problem!
(There is probably a bug in the SOAP implementation or the render-engine
of the old ReportViewer-Control.)
Thanks in advance, HenryHello Henry,
I will answer your second question first.
We did not have the new Redistributable package for public now. I will
provide you the download link once I got the information from the internal
team.
As for the first question,
I would like to get the report definition so that I can provide further
assistance on this.
Could you please send the report to me?
To reach me, please remove the ONLINE in my display email address.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hallo Wei Lu,
any news about the thousands of rows in the first HTML page?
Greetings, Henry|||Hi Wei Lu,
new Info:
Test with the new Microsoft Report Viewer Redistributable 2005 SP1,
published on 03/27/2007 under
http://www.microsoft.com/downloads/details.aspx?FamilyId=35F23B3C-3B3F-4377-9AE1-26321F99FDF0&displaylang=en
unfortunately did NOT alleviate the problem.
Greetings, Henry|||Hello Henry,
Since I did not have the datasource, I could not reproduce this issue.
My suggestion is that you may increase the page Size of the report.
The page size of the report is the InteractiveSize plus the Margins. So
since your interactivesize is 29.7cm * 21cm, and your margin setting is 1cm
top and 0.5cm bottom, could you please increase your Page Size to 29.7cm *
22.5cm to have a try?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
thanks for the tip with the page size, but it didn't work.
I've made lots of test with varying page sizes and different combinations of
ReportViewer-Control Versions (042=original DLLs, 762=VSSP1 and 817=Report
Viewer Distr.2005 SP1) and SQL-Server 2005-Versions (SP1 and SP2).
No way.
I've compiled a test-RDL with Access-DB that I've sent you by e-mail.
Did you receive it?
Yours Henry|||Hello Herry,
I reproduce this issue that the HTML render will cost a long time and only
one page are generated.
Currently, I found a workaround is that you may try to add a group
expression in the matrix1_intCustomerID group.
=Int((RowNumber(nothing)-1)/30)
And I use the following in the statement
select * from t0blhbu1502 order by intCustomerID,
intInvoiceNo,DtmInvoiceDate,intOrderNo
Then the report will only show 30 records in one page. You could modify the
setting to allow more records.
Hope this helps!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you very much for this information.
Weeks ago I already tried this but couldn't achieve what I'd have liked and
abandoned it.
I thought it'd exist some shortcut or official way.
Since there's not, I returned to dig in this grouping direction.
In our case we use a matrix inside a list with subtotals, so your proposal
didn't work.
After another lots of hours fighting with variable scope problems, I figured
it finally out, how to get it done.
Put the following Expression to group on it in the first matrix column:
=code.setRowAggregate()
The associated function:
Function setRowAggregate() As Integer
Static Dim a As Integer = 0
Static Dim b As Integer = 1
If a < 100 Then
a += 1
Else
a = 1
b += 1
End If
Return b
End Function
and yapadapadu it is finally working.
Saludos, Henry|||Hi Henry,
Glad to hear you resolved this issue. And your detailed information of your
resolution is helpful.
If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Interactive sorting in matrix

How could I accomplish interactive sorting inside the matrix control? I have matrix like this:

=Fields!Time_Calculations.Value (Current, Prior Year)
Forecast Sale
=Fields!Month xxxxx xxx

And I need to sort by Forecast and Sale.

Thnaks

The matrix region is somewhat special when it comes to sorting due to the way it aggregates data. In short, you need to use the corner textbox to set up your user sort, as this report definition demonstrates.

<?xml version="1.0" encoding="utf-8"?>

<ConnectString>Data Source=localhost;Initial Catalog=Northwind</ConnectString>

</ConnectionProperties>

<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>ProductName</Value>

</Values>

</DefaultValue>

<Prompt>RowGroup</Prompt>

<Value>ProductName</Value>

<Label>By Product Name</Label>

</ParameterValue>

<Value>SupplierID</Value>

<Label>By Supplier ID</Label>

</ParameterValue>

<Value>CategoryID</Value>

<Label>By Category ID</Label>

</ParameterValue>

</ParameterValues>

</ValidValues>

</ReportParameter>

<DataType>String</DataType>

<Value>ReorderLevel</Value>

</Values>

</DefaultValue>

<Prompt>ColumnGroup</Prompt>

<Value>ReorderLevel</Value>

<Label>By Reorder Level</Label>

</ParameterValue>

<Value>UnitsInStock</Value>

<Label>By Stock</Label>

</ParameterValue>

<Value>SupplierID</Value>

<Label>By Supplier ID</Label>

</ParameterValue>

</ParameterValues>

</ValidValues>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Style>

</Style>

<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>

</Textbox>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>

</Textbox>

</MatrixColumn>

</MatrixColumns>

<rd:DefaultName>CategoryID</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields(Parameters!RowGroup.Value).Value</Value>

</Textbox>

</ReportItems>

<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<rd:DefaultName>ReorderLevel</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>

</Textbox>

</ReportItems>

<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet1</DataSetName>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

</Style>

<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>

<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>

</UserSort>

</Textbox>

</ReportItems>

</Corner>

<rd:DefaultName>ProductID</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Count(Fields!ProductID.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

</Body>

<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from products</CommandText>

<DataSourceName>northwind</DataSourceName>

</Query>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>ProductID</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ProductName</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>SupplierID</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>CategoryID</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>QuantityPerUnit</DataField>

</Field>

<rd:TypeName>System.Decimal</rd:TypeName>

<DataField>UnitPrice</DataField>

</Field>

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>UnitsInStock</DataField>

</Field>

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>UnitsOnOrder</DataField>

</Field>

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ReorderLevel</DataField>

</Field>

<rd:TypeName>System.Boolean</rd:TypeName>

<DataField>Discontinued</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Interactive Sort within a Matrix

Can't seem to make any kind of interactive sort work within a Matrix. Is it possible? I've got the interactive sort to work in a Table.

In a Matrix, the sort buttons come up, but they don't have any affect on the presentation of the data (columns). In the matrix, I think I've used every possible combination of Current Scope, Choose Data Region or Grouping, Detail Scope and the second Choose Data Region or Grouping.

Any help or suggestions would be most appreciated.

I'm wondering - how did you make the interactive sort work within a table? Perhaps my problem is that my table is a drill-down. I can't find an interactive sort feature or property.|||Please Ddisregard my post, I found the Interactive Sort property. Interstingly, Interactive Sort is not in the help file. I love this product, it's so great that I think 2.0 will be a true firestorm.|||

I've also noted that the "Subtotal" feature in a Matrix does not appear to work logically. It seems when you have a variable amount of columns returned in your query, the "Subtotal" does not sum the columns, only the first column is sum'd (which is worthless).

There is an open ticket with Microsoft on the "Matrix / Interactive Sort" issue (SRX060307603449). Been waiting a week now...

|||Interactive sort may not work for Matrix report.

Interactive Sort within a Matrix

Can't seem to make any kind of interactive sort work within a Matrix. Is it possible? I've got the interactive sort to work in a Table.

Any help or suggestions would be most appreciated.

There is an open ticket with Microsoft on the "Matrix / Interactive Sort" issue (SRX060307603449). Been waiting a week now...

|||Interactive sort may not work for Matrix report.

Monday, March 12, 2012

Interactive sort in matrix not working on 9.00.3054.00

I'm having problem with sorting in matrix. My report looks something like below.

Name* Avg.* | Test1 Test2 Test3

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

Jim 50% | 100% 40% 10%

John 28% | 5% 40% 40%

The column with * need to be sorted. I can sort the name and average fine in Microsoft SQL Server Reporting Services Version 9.00.2047.00. BUt when I run this in another environment, sorting on Avg doesn't work. The version on that is Microsoft SQL Server Reporting Services Version 9.00.3054.00.

I put together a sample code against the northwinddatabase. Sorting works in 9.00.2047.00 but not 9.00.3054.00. Any ideas?

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>DataSource1</DataSourceReference>

<rdataSourceID>632d2cbd-3d9c-44fe-97ac-fbdef687ef47</rdataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>Student</Value>

</Values>

</DefaultValue>

<Prompt>Object Type</Prompt>

</ReportParameter>

<DataType>Integer</DataType>

</Values>

</DefaultValue>

<Prompt>testid</Prompt>

</ReportParameter>

<DataType>String</DataType>

<Value>Element</Value>

</Values>

</DefaultValue>

<Prompt>groupby</Prompt>

</ReportParameter>

<DataType>String</DataType>

<Value>6453bybbyq</Value>

</Values>

</DefaultValue>

<Prompt>usertestdoc</Prompt>

</ReportParameter>

<DataType>String</DataType>

<Value>Data Source=10.88.22.00;Initial Catalog=xxx;User ID=sa;Password=xxxxx</Value>

</Values>

</DefaultValue>

<Prompt>dbserver</Prompt>

</ReportParameter>

<DataType>String</DataType>

</Values>

</DefaultValue>

<Prompt>copyright</Prompt>

</ReportParameter>

<DataType>String</DataType>

<Value>Teacher</Value>

</Values>

</DefaultValue>

<Prompt>levelby</Prompt>

</ReportParameter>

<DataType>Integer</DataType>

</Values>

</DefaultValue>

<Prompt>objectid</Prompt>

</ReportParameter>

</ReportParameters>

<rd:GridSpacing>0.0625in</rd:GridSpacing>

<Body>

</MatrixColumn>

</MatrixColumns>

<rdefaultName>shipcountry_1</rdefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>Gainsboro</BackgroundColor>

</Style>

<Value>=count(Fields!orderid.value)</Value>

</Textbox>

</ReportItems>

<GroupExpression>=Fields!shipcountry.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<rdefaultName>customerid</rdefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<BackgroundColor>Gainsboro</BackgroundColor>

</Style>

<Value>=Fields!customerid.Value</Value>

</Textbox>

</ReportItems>

<GroupExpression>=Fields!customerid.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>dataset</DataSetName>

<rdefaultName>textbox1</rdefaultName>

<Style>

</Style>

<SortExpression>=count(Fields!orderid.Value)</SortExpression>

<SortExpressionScope>matrix1_RowGroup1</SortExpressionScope>

</UserSort>

<Value>SORTME</Value>

</Textbox>

</ReportItems>

</Rectangle>

</ReportItems>

</Corner>

<rdefaultName>textbox4</rdefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>=count(Fields!orderid.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

</Body>

<rd:ReportID>98ed88ab-a457-4446-8b4c-ff275e2b5e03</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select top 30 shipcountry, customerid, orderid from orders</CommandText>

<DataSourceName>DataSource11</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>shipcountry</DataField>

</Field>

<rd:TypeName>System.String</rd:TypeName>

<DataField>customerid</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>orderid</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</PageFooter>

</Report>

I ran into the same problem a while back but we ended up rebuilding our reports to use tables. I'm kinda curious as to what caused this issue and if anyone ever found a fix.|||

You know it looks like they changed it so that it won't sort based on an aggregate. For example prior to the SP update

I could have a report like this

SortBox Year

Carrier Sum(Passengers)

sort expression Sum(Passengers)

sort expression scope Carrier

Now that still sucks because you can't sort on the Passengers for a given year but at least it sorts on total pax....

Now after the SP(and I checked this in VS05 9.00.3054,VS08 beta 2 RDLC, and Katmai Designer) only non aggregate fields work

sort expression Fields!Carrier, or Fields!Passengers

sort expression scope Carrier

The tablix in Katmai allows you to put something like this together

Year

(Label with sort Passengers)

Carrier Sum(Passengers)

The sort label:

sort expression Fields!Passengers or Sum(Passengers)

sort expression scope Carrier

I'm hopeful that the fact that it doesn't give me an error indicates that this excel type sort will work in the future.

In anycase one possible bypass for the .3054 change appears to be embedding a table in the matrix cell which allows you to sort the "row group" relative to that specific column group. While this doesn't work across all the columns, in some situations it might help out.

Interactive Sort in Matrix

Hi,

I making a report using Matrix with 2 row groups and 1 column group. The group names are:

Row : CompanyName, TotalUsers

Column: UserType

Detail Group has a count of number of user in each type.

I want to use interactive sort feature on my column group so that when I click on my column header, it sort my detail group data ascending or decending and companyName and total users based on datail group.

Thanks a lot,

-Rohit

I tried different options for the scope but non of them works. Does any one know how to do this?

Thanks.

Interactive Sort in Matrix

I have a Matrix in a report - created like below. Each row has a different dataset and I need to make each Column to be sortable so they can click on the column header say "Summary of Accounts" and sort them and do that for each column header. So far I havent seen anything that looks promising that can do this. Is there away this can be done?

6 Columns and 14 Rows

--headers

Summary of Accounts No debts origBal AccrInter PaidDate CurrBal

Activity count() Sum() Sum() First() Sum()

phase count() Sum() Sum() First() Sum()

attorney etc etc

collection

not inititated

resolved

interrupted

cancel

closed

revised

letter service

Totals:

The combination of Interactive Sort and Matrix are not good! I managed to get it working but only for row groups.|||I know they are not how about how did you do the row groups? If they can sort by row then fine that is good enough for me then!

Interactive sort in a matrix

I've modified the RDL from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525433&SiteID=1 to illustrate the problem I am having setting Interactive Sorts on columns in a Matrix. Using the rdl below you'll see that the 2 interactive sorts in the matrix corner named Cols and Rows are working well. My problem is in adding the Interactive Sorts to the individual CompanyName columns. I’d like to be able to click on a CompanyName and have the CategoryName (rows) sort by the Quantities shown for the clicked CompanyName. Any help in correcting the rdl below would be appreciated.

<?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>30d8ee62-a72d-48fa-ad77-66fdebc3f620</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.5in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontSize>8pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CompanyName">
<rd:DefaultName>CompanyName</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontSize>8pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortTarget>matrix1_CompanyName</SortTarget>
<SortExpression>=Fields!Quantity.Value</SortExpression>
</UserSort>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.5in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Width>2.5in</Width>
<Corner>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox12">
<Top>0.25in</Top>
<ZIndex>1</ZIndex>
<Width>0.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontSize>8pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup1</SortExpressionScope>
</UserSort>
<Value>Rows</Value>
</Textbox>
<Textbox Name="textbox11">
<Left>0.75in</Left>
<rd:DefaultName>textbox11</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontSize>8pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<UserSort>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
<SortExpressionScope>matrix1_CompanyName</SortExpressionScope>
</UserSort>
<Value>Cols</Value>
</Textbox>
</ReportItems>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Rectangle>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Quantity">
<rd:DefaultName>Quantity</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontSize>8pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.75in</Height>
</Body>
<rd:ReportID>87afdd90-7b33-49bc-8880-0df212c6637a</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT TOP 300 Products.ProductName, Products.UnitPrice, Customers.CompanyName, [Order Details].Quantity, Categories.CategoryName,
Products.CategoryID
FROM Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="CompanyName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CompanyName</DataField>
</Field>
<Field Name="Quantity">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>Quantity</DataField>
</Field>
<Field Name="CategoryName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CategoryName</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="RowTotal">
<Value>=Fields!Quantity.Value</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code />
<Width>6.50001in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

Clicking on a column header to sort the rows is not supported. Because an end-user sort on a column header means you want to sort all details (when the SortExpressionScope is detail scope) or all inner groups (that correspond to the SortExpressionScope if it's not detail) under that column. Since the matrix row hierarchy is not under the columns, this would be an invalid scenario.

Interactive Sort in a matrix

I cannot get the interactive sort to work in a matrix (SQL Server 2005, Sept
CTP). Does anyone have examples on how to do this? Should interactive
sorting work in matricies -- or is it only set up to work with tables?
--
LaurieTYes, you can do that - but you have to understand what you want to sort on.
Typically you want to sort on the matrix row groupings. If you add the
InteractiveSort option on the matrix row grouping heading textbox, it will
repeat for every matrix row grouping instance at runtime and this is not
what you typically want.
You would rather want just one textbox that controls the sort direction of
all matrix groups => e.g. create a textbox outside the matrix or use the
matrix corner. If you use the matrix header, it is a bit easier to set up:
You want to sort the "current scope" (i.e. the matrix) and you want to
evaluate the sort expression in the scope of the matrix row grouping (i.e.
type in the name of the matrix row grouping).
An example for a completely dynamic matrix is attached. The sample does more
than what you are looking for - just check out the interactive sort in the
matrix corner.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"LaurieT" <LaurieT@.discussions.microsoft.com> wrote in message
news:9983D528-A4E8-4525-BAA3-F3E583379072@.microsoft.com...
>I cannot get the interactive sort to work in a matrix (SQL Server 2005,
>Sept
> CTP). Does anyone have examples on how to do this? Should interactive
> sorting work in matricies -- or is it only set up to work with tables?
> --
> LaurieT
====================================================
<?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>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.875in</Top>
<Width>3.125in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
</UserSort>
<Value>Sort rows</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products</CommandText>
<DataSourceName>northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>3.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||Thank you very much. I tried the sort in the matrix header and was able to
get it to work.
--
LaurieT
"Robert Bruckner [MSFT]" wrote:
> Yes, you can do that - but you have to understand what you want to sort on.
> Typically you want to sort on the matrix row groupings. If you add the
> InteractiveSort option on the matrix row grouping heading textbox, it will
> repeat for every matrix row grouping instance at runtime and this is not
> what you typically want.
> You would rather want just one textbox that controls the sort direction of
> all matrix groups => e.g. create a textbox outside the matrix or use the
> matrix corner. If you use the matrix header, it is a bit easier to set up:
> You want to sort the "current scope" (i.e. the matrix) and you want to
> evaluate the sort expression in the scope of the matrix row grouping (i.e.
> type in the name of the matrix row grouping).
> An example for a completely dynamic matrix is attached. The sample does more
> than what you are looking for - just check out the interactive sort in the
> matrix corner.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "LaurieT" <LaurieT@.discussions.microsoft.com> wrote in message
> news:9983D528-A4E8-4525-BAA3-F3E583379072@.microsoft.com...
> >I cannot get the interactive sort to work in a matrix (SQL Server 2005,
> >Sept
> > CTP). Does anyone have examples on how to do this? Should interactive
> > sorting work in matricies -- or is it only set up to work with tables?
> > --
> > LaurieT
>
> ====================================================> <?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>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="matrix1_ColumnGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.875in</Top>
> <Width>3.125in</Width>
> <Corner>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <UserSort>
> <SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
> <SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
> </UserSort>
> <Value>Sort rows</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <MatrixRows>
> <MatrixRow>
> <Height>0.25in</Height>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <rd:DefaultName>ProductID</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!ProductID.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> </MatrixRow>
> </MatrixRows>
> </Matrix>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select * from products</CommandText>
> <DataSourceName>northwind</DataSourceName>
> </Query>
> <Fields>
> <Field Name="ProductID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>ProductID</DataField>
> </Field>
> <Field Name="ProductName">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>ProductName</DataField>
> </Field>
> <Field Name="SupplierID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>SupplierID</DataField>
> </Field>
> <Field Name="CategoryID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>CategoryID</DataField>
> </Field>
> <Field Name="QuantityPerUnit">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>QuantityPerUnit</DataField>
> </Field>
> <Field Name="UnitPrice">
> <rd:TypeName>System.Decimal</rd:TypeName>
> <DataField>UnitPrice</DataField>
> </Field>
> <Field Name="UnitsInStock">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsInStock</DataField>
> </Field>
> <Field Name="UnitsOnOrder">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsOnOrder</DataField>
> </Field>
> <Field Name="ReorderLevel">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>ReorderLevel</DataField>
> </Field>
> <Field Name="Discontinued">
> <rd:TypeName>System.Boolean</rd:TypeName>
> <DataField>Discontinued</DataField>|||Robert,
I am able to see how the example works however a co-worker of mine has a
matrix and I am not able to get it to work in there. Of course the examples
provided are always less complicated than what we end up working with - the
matrix he has includes 5 row groups and a static row group and 2 column
groups. He wants to sort by one of the row groups. In addition the row
group has expand/collapse criteria. Here is the deal. The first 3 columns
are hidden and he wants to interactively sort the 4th column ascending or
descending. He already had the groups being sorted (on the sort tab within
the group) so I removed those thinking that might work. Basically the data
was already sorted ... it just was sorted in ascending order and he wants to
be able to let them flip it to descending order. The example you provided
used a parameter ... is that necessary ' I just chose the field that I
wanted to sort and tried putting that in the textbox at the heading of the
matrix (as that seemed to work for Laurie she mentioned in her reply).
"Robert Bruckner [MSFT]" wrote:
> Yes, you can do that - but you have to understand what you want to sort on.
> Typically you want to sort on the matrix row groupings. If you add the
> InteractiveSort option on the matrix row grouping heading textbox, it will
> repeat for every matrix row grouping instance at runtime and this is not
> what you typically want.
> You would rather want just one textbox that controls the sort direction of
> all matrix groups => e.g. create a textbox outside the matrix or use the
> matrix corner. If you use the matrix header, it is a bit easier to set up:
> You want to sort the "current scope" (i.e. the matrix) and you want to
> evaluate the sort expression in the scope of the matrix row grouping (i.e.
> type in the name of the matrix row grouping).
> An example for a completely dynamic matrix is attached. The sample does more
> than what you are looking for - just check out the interactive sort in the
> matrix corner.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "LaurieT" <LaurieT@.discussions.microsoft.com> wrote in message
> news:9983D528-A4E8-4525-BAA3-F3E583379072@.microsoft.com...
> >I cannot get the interactive sort to work in a matrix (SQL Server 2005,
> >Sept
> > CTP). Does anyone have examples on how to do this? Should interactive
> > sorting work in matricies -- or is it only set up to work with tables?
> > --
> > LaurieT
>
> ====================================================> <?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>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="matrix1_ColumnGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.875in</Top>
> <Width>3.125in</Width>
> <Corner>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <UserSort>
> <SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
> <SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
> </UserSort>
> <Value>Sort rows</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <MatrixRows>
> <MatrixRow>
> <Height>0.25in</Height>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <rd:DefaultName>ProductID</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!ProductID.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> </MatrixRow>
> </MatrixRows>
> </Matrix>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select * from products</CommandText>
> <DataSourceName>northwind</DataSourceName>
> </Query>
> <Fields>
> <Field Name="ProductID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>ProductID</DataField>
> </Field>
> <Field Name="ProductName">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>ProductName</DataField>
> </Field>
> <Field Name="SupplierID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>SupplierID</DataField>
> </Field>
> <Field Name="CategoryID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>CategoryID</DataField>
> </Field>
> <Field Name="QuantityPerUnit">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>QuantityPerUnit</DataField>
> </Field>
> <Field Name="UnitPrice">
> <rd:TypeName>System.Decimal</rd:TypeName>
> <DataField>UnitPrice</DataField>
> </Field>
> <Field Name="UnitsInStock">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsInStock</DataField>
> </Field>
> <Field Name="UnitsOnOrder">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsOnOrder</DataField>
> </Field>
> <Field Name="ReorderLevel">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>ReorderLevel</DataField>
> </Field>
> <Field Name="Discontinued">
> <rd:TypeName>System.Boolean</rd:TypeName>
> <DataField>Discontinued</DataField>

Monday, March 19, 2012

InteractiveHeigt and 6.000 rows on first HTML page?

Interactive sorting in matrix

Interactive Sort within a Matrix

Interactive Sort within a Matrix

Monday, March 12, 2012

Interactive sort in matrix not working on 9.00.3054.00

Interactive Sort in Matrix

Interactive Sort in Matrix

Interactive sort in a matrix

Interactive Sort in a matrix

Integrated Security

Blog Archive

About Me