Passing Parameter To OLAP As A Free Text And Interactive Sorting In Matrix

Apr 30, 2007

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.

View 4 Replies

Interactive Sorting In A Matrix

Oct 16, 2006

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

View 7 Replies View Related

Interactive Sorting

Jun 14, 2007

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

View 5 Replies View Related

Can I Use The Parameter Value To A Text Box On A Matrix Report?

May 15, 2008

I am creating this OLAP matrix report and I tried to speed up the time needed for the OLAP dataset by limiting the number of columns in the dataset. I have two parameters, year and month, for the dataset which are set to multi-select values. Year and month are not assigned to the dataset as for the reason mentioned above (performance). However I do want to show the year and month in the matrix report. Can I use the parameter value when assigning the grouping for the year and month like "Parameters!DateCalendarYear.Value" etc and the same to the text box in the matrix report?

I tried it to the report and it returned with an error saying "The Group expression used in grouping 'matrix_....' returned a data type that is not valid"

Thanks

View 4 Replies View Related

Interactive Sorting Icon

May 14, 2008

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.

View 6 Replies View Related

Interactive Sorting/Execution Of Query

Aug 13, 2007

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

View 5 Replies View Related

Implementing Interactive Sorting Without The Botton.

Jun 8, 2007

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.

View 4 Replies View Related

Passing LinkButton Text Value As Sql Query Parameter!!!

Oct 24, 2006

Hi,Could you inform me programmatically how can I pass LinkButton text value as Sql Query parameter?I tried the 1 command.CommandText = "SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = " & SqlDbType.Text = LinkButton12.Text & ")"
but it does not work!!! Thanks in advance!!!!

View 2 Replies View Related

Reporting Services :: Error After Interactive Sorting And Drill Down In PPS

Aug 28, 2013

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.

View 3 Replies View Related

Free MS SQL OLAP Tools?

Jul 18, 2007

Can anyone recommend an OLAP tool for MS SQL Server 2005?

View 2 Replies View Related

Embedded Mage In Subreport Not Displaying When Report Has Interactive Sorting.

Feb 14, 2008

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

View 7 Replies View Related

Passing Parameter Values To Full Text Search Stored Procedure

Oct 24, 2006

I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle

(@ProductTitle varchar(255))

AS

SELECT ProductTitle

FROM tbl_Product

WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle)
My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R

View 4 Replies View Related

Interactive Sort In Matrix

Jan 24, 2007

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

View 2 Replies View Related

Reporting Services :: Subreport Fails If Main Report Does Interactive Sorting

Apr 16, 2013

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)

View 3 Replies View Related

Reporting Services :: Interactive Sorting With Drill Down Reports In Report Builder 3.0

Aug 20, 2015

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.

View 3 Replies View Related

Matrix Column Interactive Sort

Feb 27, 2008

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.

View 5 Replies View Related

Interactive Sort In Matrix Not Working On 9.00.3054.00

Aug 7, 2007

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>

View 2 Replies View Related

Interactive Sort On Column Headers In Matrix

Dec 7, 2007

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

View 3 Replies View Related

BUG: Interactive Sort In Matrix Causes Blank Area In HTML

Apr 24, 2008

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

View 1 Replies View Related

Sorting A Matrix

May 19, 2006

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

View 1 Replies View Related

Matrix Sorting

Jun 27, 2007

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.

View 1 Replies View Related

OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter

Apr 17, 2008

Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?

I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent

View 2 Replies View Related

Sorting Matrix Report

Jan 23, 2008

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.

View 3 Replies View Related

Sorting A Matrix On The Rowtotal

Jul 24, 2006

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

View 3 Replies View Related

Passing Parameters To OLAP Sub Report

May 12, 2008

I am developing a report against OLAP. I have a main report and a sub report. The parameters to both are exactly the same. I am passing parameters to sub report from main. Whenever I use Field!Fieldname.Value as a parameter value to the sub report, I get an error saying one or more parameters required to run the report have not been specified. I used the UniqueName, but that doesnt do my grouping right ( I dont think this works for multi-value report)

I am lost...I need to figure this out ASAP. Does somebody know an article or do you know as how to deal with OLAP reports as sub reports and passing parameters as multi-value? I tried to attach the code, but I ran out of maximum characters...

View 4 Replies View Related

Sorting A Matrix Subtotal Column

Oct 30, 2007

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

View 5 Replies View Related

SQL Server/ Text Fields - Cannot Add Free Text

Sep 25, 2007

I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.

View 2 Replies View Related

Sorting Matrix Column In Random Order

Aug 14, 2007

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

View 6 Replies View Related

Report Builder - How To Design Free Form Reports Other Than Tabular Or Matrix Or Charts

Nov 8, 2007

Hi

Does any body know that creating free form rports using Report Builder is possible?

Free-form report - in the sense - that user should be able to drag the fields from the leftside fields pane and drop on body as we do in report designer using BI studio.

I could not figure out a way to do so. Is this a limitation?

Thanks
Raj Yennam

View 5 Replies View Related

How Can I Apply Dynamic Sorting For Columns In Matrix Reports

Apr 30, 2008

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

View 3 Replies View Related

Reporting Services :: Matrix Report Column Sorting

Oct 29, 2015

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.

View 6 Replies View Related

SSRS In Sharepoint, WrapperReportRenderingException/JobCanceledException For Large Matrix (OLAP) Reports

Jan 25, 2007

Hi all - please help!

I'm running SSRS 2005 (9.00.3027.00) in Sharepoint integrated mode on a Win2003 SP1 box. This works fine for most reports, including matrix reports sourcing data from SSAS 2005.

However, I have a couple of large matrix reports (7 columns per group, 12 column groups, 150 rows, 1 row per group, data from SSAS) that run fine when I run them in VS2005, but as soon as I try and run them from within Sharepoint/Report Server I get "An unexpected error has occurred.". Sometimes this happens after 60 seconds, sometimes more (up to 3 minutes)

If I filter them down to 7 column groups they work perfectly (altbeit slowly,1-2 mins), 8 groups works sometimes, 9 never works. I've checked that the report execution timeout is set to 10 minutes.

The SSRS log shows the errors as WrapperReportRenderingException and JobCanceledException. I'm not sure whether the the rendering error causes the cancelled error or vice versa. does anyone know what I can do about this?

By the way, from looking at the CPU usage of the server, it seems as though SSRS does carry on rendering the report long after Sharepoint has given up and returned with the helpful error.

Many thanks in advance

Alex

Log extract as follows:

w3wp!library!7!01/25/2007-11:11:44:: Call to GetPermissionsAction(http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl).
w3wp!library!7!01/25/2007-11:11:44:: Call to ListParentsAction(http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl).
w3wp!library!7!01/25/2007-11:11:49:: Call to GetPermissionsAction(http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl).
w3wp!library!1!01/25/2007-11:11:50:: Call to GetPermissionsAction(http://uk-ls-it-02/Reports).
w3wp!library!13!01/25/2007-11:11:50:: Call to GetSystemPropertiesAction().
w3wp!library!13!01/25/2007-11:11:51:: i INFO: Call to RenderFirst( 'http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl' )
w3wp!runningjobs!7!1/25/2007-11:13:20:: i INFO: Adding: 1 running jobs to the database
w3wp!runningjobs!7!1/25/2007-11:14:20:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!reportrendering!13!01/25/2007-11:14:20:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.Threading.ThreadAbortException: Thread was being aborted.
   at Microsoft.SharePoint.Library.SPRequest.OpenWebInternal(String bstrUrl, Guid& pguidID, String& pbstrRequestAccessEmail, UInt32& pwebVersion, String& pbstrServerRelativeUrl, UInt32& pnLanguage, UInt32& pnLocale, String& pbstrDefaultTheme, String& pbstrDefaultThemeCSSUrl, String& pbstrAlternateCSSUrl, String& pbstrCustomizedCssFileList, String& pbstrCustomJSUrl, String& pbstrAlternateHeaderUrl, String& pbstrMasterUrl, String& pbstrCustomMasterUrl, String& pbstrSiteLogoUrl, String& pbstrSiteLogoDescription, Object& pvarUser, Boolean& pvarIsAuditor, Int32& plSiteFlags)
   at Microsoft.SharePoint.SPWeb.InitWebPublic()
   at Microsoft.SharePoint.SPWeb.get_ServerRelativeUrl()
   at Microsoft.SharePoint.SPWeb.get_Url()
   at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemMetaDataAndContent(String path, UserContext userContext, Boolean returnContent, Guid& id, ISecurableObject& secObj, SharePointImpersonatedWeb& wrapper, Byte[]& content, String& mimeType)
   at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext, Guid& id, ISecurableObject& secObj, SharePointImpersonatedWeb& wrapper)
   at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext, Guid& id)
   at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext)
   at Microsoft.ReportingServices.SharePoint.Server.SharePointServiceHelper.Microsoft.ReportingServices.Diagnostics.IPathManager.IsSupportedUrl(String path, Boolean checkProtocol, Boolean& isInternal)
   at Microsoft.ReportingServices.Diagnostics.CatalogItemUrlBuilder.IsReportServerPathOrUrl(String pathOrUrl, Boolean checkProtocol, Boolean& isPath)
   at Microsoft.ReportingServices.ReportRendering.ReportUrl..ctor(RenderingContext reportContext, String initialUrl, Boolean checkProtocol, NameValueCollection unparsedParameters, Boolean useReplacementRoot)
   at Microsoft.ReportingServices.ReportRendering.Action.get_DrillthroughReport()
   at Microsoft.ReportingServices.ReportRendering.Action.get_DrillthroughID()
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.GetActionUri(Action action)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.HasMultipleActions(ActionCollection actions, Int32& actionIndex, RenderActionInfo& firstActionInfo)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderTextBoxPercent(TextBox textBox, Boolean renderId)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderReportItem(ReportItem reportItem, Int32 borderContext, Boolean renderId)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderRangeCellsContent(MatrixCell lastCell, Int32 cellBorderContext)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderRangeCells(Matrix matrix, Int32 indexRow, Int32 startCell, Int32 endCell, IntList hiddenColumns, Int32 borderContext, Boolean dirLefRight)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderNonSplitMatrixLevel(Matrix matrix, MatrixMember currMember, Boolean& newRow, Boolean partialItem, Int32 level, IntList hiddenColumns, SizeCollection cellHeights, Boolean changableRows, Int32 borderContext, Boolean dirLefRight, MatrixContext matrixContext)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderNonSplitMatrix(Matrix matrix, MatrixMember member, Boolean& newRow, Boolean partialItem, Int32 level, IntList hiddenColumns, SizeCollection cellHeights, Boolean changableRows, Int32 borderContext, Boolean dirLefRight, MatrixContext matrixContext)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderMatrixContent(Matrix matrix, Boolean partialItem, Int32 borderContext, Boolean dirLefRight, Boolean expandLayout, MatrixContext matrixContext)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderMatrix(Matrix matrix, Boolean partialItem, Int32& borderContext, Boolean renderId)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderReportItem(ReportItem reportItem, Int32 borderContext, Boolean renderId)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderCellItems(ReportItemCollection repItemCol, Hashtable partialItems, Hashtable expandItems, PageTableCell currCell, Int32 borderContext, Int32 linkToChild, Boolean layoutExpand)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.GenerateTableLayoutContent(PageTableLayout rgTableGrid, Hashtable partialItems, ReportItemCollection repItemCol, Boolean bfZeroRowReq, Boolean bfZeroColReq, String docMapId, Boolean inDocMap, Boolean renderHeight, Int32 borderContext, Int32 linkToChild, Boolean layoutExpand)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.GenerateHTMLTable(ReportItemCollection repItemCol, Double dxParent, Double dyParent, PageData pageData, String docMapId, Boolean inDocMap, Int32 borderContext, Int32 linkToChild, Boolean expandLayout)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderRectangle(Rectangle rectangle, PageData pageData, Int32& borderContext, Boolean renderId)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderPage(Int32 index)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderPages()
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderBody(HtmlTextWriter htmlTextWriter)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.ViewerReportArea.Render(HtmlTextWriter outputWriter)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension.InternalRender(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.RenderingExtensionBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.Rendering.HtmlRenderer.RenderingExtensionBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(IRenderingExtension renderer, DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc, CreateReportChunk cacheDataCallback, Boolean& dataCached)
   --- End of inner exception stack trace ---
w3wp!runningjobs!13!01/25/2007-11:14:20:: i INFO: CancelableJobExecution.Execute caught our thread abort exception
w3wp!library!13!01/25/2007-11:14:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.JobCanceledException: An administrator has canceled the job., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.JobCanceledException: An administrator has canceled the job. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.Threading.ThreadAbortException: Thread was being aborted.
   --- End of inner exception stack trace ---
w3wp!webserver!13!01/25/2007-11:14:20:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.JobCanceledException: An administrator has canceled the job. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.Threading.ThreadAbortException: Thread was being aborted.

View 6 Replies View Related

Reporting Services :: Interactive Sort On A Text Field Having Decimal Numbers

Jun 19, 2015

I have the following text field in SSRS report:

Version
2.2.32.7
1.0.0.0
2.0.0.0
1.2.0.0
2.1.8.8
1.4.11.0

I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show

Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7

View 3 Replies View Related