Bill,
You say, "we enjoy the option of specifying sorting for
multiple columns within the matrix, table or list within which we
are working." But, when I add interactive sorting to the headers of a matrix, the up and down toggles appear in the preview, but the rows are not sorted after clicking.
This works perfectly fine on tables, as you say, but I have yet to see it on a matrix.
Thanks,
Tom
Dear All,
I am facing one problem with reporting services 2005 (SSRS), When i try to pass a parameter to Cube which is built in SSAS, i can not use a parameter where i can type the value instead of choosing it from a dimension.
Any way we can type the value for parameter, instead choosing from the drop down? If can, then how can we create the parameter? and how can i write a MDX to read it?
Thanks.
Regards,
Swarna.
I know this is not available now, but is it plan in the near future:
I want to be able to set the initial sort order on the interactive sort.
for example, I have a report sorted Asc by Rank. If I click on the interactive sort button on the Rank column, it resorts ascending... totally pointless, and my customers point this out on a weekly basis...
Any plans to add this feature? If not, I will go to connect and suggest it.
Also, does anyone else find this annoying?
Thanks!
BobP
Is there any way to change the interactive sorting icon or even adjust it? When I add the functionality to a column, it skews the column heading to make room for it. This means that reports that previously displayed properly no longer look correct. If I adjust the width of the report to allow room for the icon, then the report overflows its boundary in terms of printing.
Also, the icon does not display evenly all the time. Sometimes, it displays directly next to the text and sometimes it is off to the right more.
Does clicking interactive sort button in a column reporting services 2005 result re-execution of the query.
Or will it just re-print the rendered data in the layout and so perform better in comparison to the implementation which can be done using drill down to same report with the help of some extra parameters
Priyank
Hi,
I want to implement interactive sorting on few columns of my report. That is working fine but the the button coming for interactive sort is taking extra space.
I have some space constrains due to too many columns in the report and so i want that implementation without the button(its taking extra space and also ruining the alignment) , may be with a underline in the column header
like hyperlink or something.
Is it possible.
Thanks in advance.
Regards,
Priyank Pandey.
I have a report that has a drill down option that shows all workgroups within a specific parent group and an interactive sorting ass well on both columns parent group & workgroup
This is displayed in pps combined with a SharePoint
The problem is that after sorting the parent group column by name I then press the drill through icon (+) next to it hopping to see all workgroups related to each parent group - but instead
I receive this Reporting Services Error :
The value for parameter 'SortItem' is not specified. It is either missing from the function call, or it is set to null. (rsMissingParameter)
If I don't press the sorting and just drill through it works fine. the sorting itself also works fine.
Hi!
I have a report that contains a table with certain columns that have interactive sorting. At the top of the report there is a subreport that contains an embedded image. When I first run the report, the image in the subreport displays. But, when I sort the table interactively, the image turns into a red asterisk inside a white box. When I click the 'refresh' button, the image redisplays correctly. This is occurring in the Visual Studio 2005 Report Designer when you preview the report. I believe I have the most current version of Visual Studio. I'm not sure what to do about this. Any suggestions?
Karen
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've been reading a lot of posts on this subject. I understand that the issue is due to the fact that the subreport is only rendered once. So after the main report is sorted on another column, the cell with the subreport produces an error.
What I want to know is whether or not this is considered a defect. And if it is a defect, is it fixed in another version?
I am currently running SSRS SQL2008R2 SP2 (20.50.4000.0)
I am using Report Builder 3.0 and i have report which has a drill down group. The report opens in a collapsed form.This report has interactive sorting on a column. But,when we drill down and sort the column, the group collapses and the details disappear.
In some posts, i have seen that instead of interactive sorting, people recommended a parameter. But i have to do interactive sorting only.
I looked around quite a bit but couldn't get around to the issue I've at hand.
I've a matrix report with one row group and one column group. I want to apply interactive sort so that when a user clicks on the column header based on the values in the column the report is rearranged.
The report shows:
Name 02/02/2008 01/31/2008
Test1 15 12
Test2 9 15
Now if the user clicks on 02/02/2008 then it will show
Name 02/02/2008 01/31/2008
Test2 9 15
Test1 15 12
And toggle if clicked again. Hope I'm clear.
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"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource11">
<DataSourceReference>DataSource1</DataSourceReference>
<rdataSourceID>632d2cbd-3d9c-44fe-97ac-fbdef687ef47</rdataSourceID>
</DataSource>
</DataSources>
<BottomMargin>0.25in</BottomMargin>
<RightMargin>0.25in</RightMargin>
<PageWidth>11in</PageWidth>
<ReportParameters>
<ReportParameter Name="objecttype">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Student</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Object Type</Prompt>
</ReportParameter>
<ReportParameter Name="testid">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>481</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>testid</Prompt>
</ReportParameter>
<ReportParameter Name="groupby">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Element</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>groupby</Prompt>
</ReportParameter>
<ReportParameter Name="usertestdoc">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>6453bybbyq</Value>
</Values>
</DefaultValue>
<Prompt>usertestdoc</Prompt>
</ReportParameter>
<ReportParameter Name="dbserver">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Data Source=10.88.22.00;Initial Catalog=xxx;User ID=sa;Password=xxxxx</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>dbserver</Prompt>
</ReportParameter>
<ReportParameter Name="copyright">
<DataType>String</DataType>
<Nullable>true</Nullable>
<DefaultValue>
<Values>
<Value>Copyright 2007 </Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>copyright</Prompt>
</ReportParameter>
<ReportParameter Name="levelby">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Teacher</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>levelby</Prompt>
</ReportParameter>
<ReportParameter Name="objectid">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>objectid</Prompt>
</ReportParameter>
</ReportParameters>
<rdrawGrid>true</rdrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.0625in</rd:GridSpacing>
<rdnapToGrid>true</rdnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1.73189in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.5in</Left>
<RowGroupings>
<RowGrouping>
<Width>2.3125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="shipcountry_1">
<rdefaultName>shipcountry_1</rdefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=count(Fields!orderid.value)</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!shipcountry.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="customerid">
<rdefaultName>customerid</rdefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!customerid.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_shipcountry">
<GroupExpressions>
<GroupExpression>=Fields!customerid.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.4375in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>dataset</DataSetName>
<Top>0.625in</Top>
<Width>4.04439in</Width>
<Corner>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox1">
<Left>0.0625in</Left>
<Top>0.1875in</Top>
<rdefaultName>textbox1</rdefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=count(Fields!orderid.Value)</SortExpression>
<SortExpressionScope>matrix1_RowGroup1</SortExpressionScope>
</UserSort>
<Value>SORTME</Value>
</Textbox>
</ReportItems>
<ZIndex>3</ZIndex>
</Rectangle>
</ReportItems>
</Corner>
<Height>0.88597in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.44847in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox4">
<rdefaultName>textbox4</rdefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=count(Fields!orderid.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2.5625in</Height>
</Body>
<rd:ReportID>98ed88ab-a457-4446-8b4c-ff275e2b5e03</rd:ReportID>
<LeftMargin>0.25in</LeftMargin>
<DataSets>
<DataSet Name="dataset">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select top 30 shipcountry, customerid, orderid from orders</CommandText>
<DataSourceName>DataSource11</DataSourceName>
</Query>
<Fields>
<Field Name="shipcountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>shipcountry</DataField>
</Field>
<Field Name="customerid">
<rd:TypeName>System.String</rd:TypeName>
<DataField>customerid</DataField>
</Field>
<Field Name="orderid">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>orderid</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code />
<Width>10.3125in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<PageFooter>
<Height>0.1875in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>
<TopMargin>0.25in</TopMargin>
<PageHeight>8.5in</PageHeight>
</Report>
Hi there,
I'm trying to implement Interactive Soring on the column headers in a matrix in a report. Is it actually possible to do this? I've read several internet posts and stories of implementing the Interactive Sorting in the upper-left corner of the matrix, but this is not what I want, I want to implement it on the column headers .
The goal I'm trying to achieve is to give the user the possibillity to click on a column header to sort the rows below in asc- or descending direction.
Please tell me if this is possible, because all my efforts have not been succesfull! If it's possible, please provide the solution to do this.
If you need more information, please don't hesitate to ask.
Thanks in advance,
Dave Ruijter
BI Consultant
This must be a first, something that works in pdf, but not in html view....
I have 3 interactive sorts on 3 row groupings in a Matrix
When sorting the data in the 'preview' tab of report designer, all works well. On deploying the reports and trying to sort, I find that the report contains huge areas of white space (1 very wide empty column) and no data whatsoever, although the row grouping labels are present. Upon exporting to PDF all is ok.
Is this a known bug?
RS version = 9.00.3042.00
IE = 7.0.5730.11
I am trying to sort a matrix by column. I first tried adding an interactive sort to the column heading, but it either gave me a run-time error, or there was not response. I then tried adding sorting to the row groups, and that doesn't seem to work either. I feel I have done every combination of groupings and sort expressions. Has anyone been able to accomplish this?
Thanks,
Kris
Hi. I need to implement a matrix that supports column sorting just like a table.
For example, given a matrix that contains...
Col1 Col2Row1 1 8Row2 2 6Row3 3 7
...if the user clicks on "Col2", I need the matrix to look like this:
Col1 Col2Row2 2 6
Row3 3 7Row1 1 8
I cannot use the interactive sort feature since the matrix must be WYSIWYG with the printed copy and the built-in interactive sort arrows do appear in printed output.
Here's my partial solution; my hope is that someone can point me in the right direction from here:
When the user clicks on a column heading I will jump to a URL such as:
http://myserver/myreport.aspx?SortField=Col2&SortDirection=Ascending
SortField and SortDirection are parsed by the web app and passed as parameters to my report.
So, after the user clicks on Col2 the report knows the following:
Parameters!SortField.Value = "Col2"
and
Parameters!SortDirection.Value = "Ascending"
Given this information, is there a way to tell the report's matrix to actually display the data in the order shown in the example above.
Thanks for any help you can provide.
I have created a Matrix report with 5 row groups (Company and ProductName, ProductEndDate), one column group (Status) and two data columns(Amount & Count).
The report works fine except that it does not sort properly. I want to sort the data by Company, ProductEndDate, ProductName.
I took the following steps -- I highlighted the matrix and in the properties dialog box, selected the groups tab. In groups I selected each row group, hit the edit button, clicked the sorting tab and Company, ProductEndDate, ProductName.
Is there a way to have a matrix sort his rows based on one of the total-values of the row ?
Ex
Col1 Col2 Col3 Total
Row1 10 25 35
Row2 1 10 9 20
Row3 2 2 2 6
Kind Regards
I have a matrix with the rows consisting of customers and the columns consisting of different revenue streams. The revenue streams are totalled using the matrix subtotal feature. I would like to sort, either interactively or statically (if that is the only option) by the subtotal column. I've read through everything I can find online and even the solutions that appear to be appropriate do not work. Can anyone give me a suggestion?
Thanks,
Zack Gallinger
Hi,
I have a matrix report...the column results
are as follows
Con Std , Con Access, SF Std, SF Acc, Broadband, Pay TV
how would i make the columns appear in the above order when displaying as it is default alphetically sorted...I have tried putting numbers at the front which work till I get to the number 10 which alphetically sorted is next to 1 not 9?
is there a better way off sorting matrix column which have no specific criteria to sort from?
thanks
Hi,
We have a matrix report which displays columns in a default sorting order. This report columns vary dynamically depending on the user input.
e.g. If user wants to see the report for column Alfa, Beta , Gama then a report will be genarted with column Alfa, Beta , Gama sorted in alphabetical order.
Site
%Risk
Alfa
Beta
Gama
X
2
1
2
3
Y
10
4
5
6
However the users want the Columns to be sorted in the order which they provide the inputs e.g. if the user entered Gama, Alfa, Beta the report should display the columns in the same order instead of applying the default sorting order.
Site
%Risk
Gama
Alfa
Beta
X
2
3
1
2
Y
10
6
4
5
Any thoughts on ways to achieve this in SSRS matrix report would really help.
Cheers,
Viv
I am creating a Matrix report using SSRS/Visual Studio 2008 as below:
Jan
Feb
March
April
May
June
July
Aug
Sept
Oct
A & B are customers. The values represent sales for each month.I have got most parts in place except the Month are displayed in random order (1,10,2,3,4,5,6 and so on).I have tried the option to create a calculated column with month numbers and change group properties but that did not work.how to get it to sort display as 1,2,3,4,5,6,7,8,9,10.
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Any suggestions would help.
Thanks
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
View 6 Replies View RelatedI got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
How? Why does the right chart move?
Thanks in advanced
Kind Regards
Hello.
I hope to explain myself well - I want to make a matrix with two rows.
Lats say my data is this:
I hava a list of months and in every month I have a number of pepole and there age.
How can I show this in a matrix?
It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
The data should look like this:
10/06 11/06 12/06 01/07 02/07 03/7 04/07 .....
num 5 1 2 5 4 5 7 .....
age 16.1 25 18.5 14.8 25.5 20.5 18.5 .....
Thanks for any help.
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
View 1 Replies View RelatedHi,
I am migrating few my oracle codes to T-SQL. Currently am using & (ampersand) interactive prompt of SQLPlus. Is there any equivalent for the above said in SQL Server.
-Cheeku
Software packages like Microsoft Small Business Accounting and Quickbooks offer a very powerful reporting module that lets end users change grouping, filtering, sorting, etc at run time (having it change the report dynamically infront of them). More importantly, their reporting tools let users click on details on the reports which opens the data in the form based portion of their software.
For example: If the end user pulls up a financial report, lets say "All Bills for February 07", the user get a report of all the bills that have gone out in that time frame. The end user can then click on the actual details in the report, and the Bill will come up in the Windows Forms portion of their software so they edit the bill, or create a new bill.
I have done a very limited amount of reporting in SQL Server, so I am not sure of how they were able to achieve this. If someone could give me some key words or ideas that I can bring up more information from in google, or even on here, I'd appreciate it.
Thanks in advance!
The Items Display is like this.
Project Name Acronym ID
7ked
SedRich Sed 123
Diercrom Dife 256
Fireup Fip 563
8IOP
Hiluke Hik 478
Jikerty Jer 125
Kilert Kre 458
The Report idea, u Got Right, now I want to implement Interactive sorting to Name ,Acronym,ID
Please help me