jetlounge.net - technews : Reporting Services

Suppress Auto Update of MDX Parameter Datasets in Reporting Services 2008

david — Thu, 25 Jun 2009 20:56:00 GMT

With the MDX Query Designer in SSRS 2008 Report Designer and Report Builder 2.0, changing the main dataset overwrites the parameter datasets, which will kill any manual adjustments made. Bummer.

The hack to fix is to add a SuppressAutoUpdate =true to the parameter dataset:

<Query>

<DataSourceName>DataSource1</DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<rd:Hidden>false</rd:Hidden>

</Query>

FIX: An unexpected error has occurred, Web Parts Maintenance Page

david — Fri, 27 Feb 2009 14:03:00 GMT

I received this error on a web part page that used the SQL Server Reporting Services native web parts for Report Browsing and report Viewing. Every time I attempted to "connect" the Viewer control to the Browser, I was kicked out to an error page with the following error message:

An unexpected error has occurred.

Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

Troubleshoot issues with Windows SharePoint Services.

You may receive an error with a SharePoint page that has other web parts. The immediate fix is to delete the web part, but that's not a real workaround. Changing the trust level for the site worked for me.

This is the complete fix:

1. Open web.config file of current web application

2. Find the node that begins <trust level=

3. Change the trust level from WSS_Minimal to WSS_Medium

4. Save the file

5. Drop to command prompt and execute : iisreset

Error When Deploying a Large RS Report Model

david — Fri, 30 May 2008 15:14:00 GMT

You may recieve the error during the deployment of a Reporting Services model:

There was an exception running the extensions specified in the config file.
Maximum request length exceeded.

To correct, you need to increase the file size limit currently in place in the web.config on the RS Server.
Find the web.config file located in the directory:

[SQL Server Directory]/[MSSQL RS Instance]/Reporting Services/ReportServer

Locate the node <httpRuntime> and add the attribute maxRequestLength and a number, or simply modify the value until you increase the limit such that you can deploy.

Mine looked like this when I was done:
<httpRuntime executionTimeout = "9000" maxRequestLength = "102400" />

Save the file and redeploy. You can switch it back if you like when you are done deploying.

Full attribute listing for httpRuntime here:
http://msdn.microsoft.com/en-us/library/e1f13641(VS.80).aspx

FIX: Visual Studio cannot shut down because a modal dialog is open

david — Wed, 09 Apr 2008 15:53:00 GMT

You might get this error message after some report development in BIDS (Business Intelligence Development Studio). It's actually a Visual Studio issue..something that has to do with VSS...and you may be afflicted even if you aren't using VSS.

The Error Message states exactly:

---------------------------
Microsoft Visual Studio
---------------------------
Microsoft Visual Studio cannot shut down because a modal dialog is active. Close the active dialog and try again.

Then you have to shut down the entire process and hope that work has been saved.

A fix is now out for this issue as a hotfix. You first must have SP1 applied to Visual Studio 2005.

Learn about the issue here from the MS Support Site:
http://support.microsoft.com/kb/936971

Download the hotfix here from the MS Connect Site:
https://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=7259

SSRS: Report Server Installation Is Not Initialized

david — Thu, 27 Mar 2008 16:21:00 GMT

I received this error message during model deployment:

The report server installation is not initialized. (rsReportServerNotActivated) (rsRPCError)

To fix, I launched "Reporting Services Configuration Manager"

On the Initialization (which did have a green check mark) page, I noticed two IDs, one had a check and the other did not. After attempting several scenarios, I had to actually delete both Instance IDs and then click the Initialize button to create a new instance.

After that, all was well in Reporting Services 2005 land.

Adding Custom Code & Assemblies in Reporting Services

david — Mon, 07 Jan 2008 13:51:00 GMT

A few good links I keep referring to regarding the addition of a custom code assembly in RDL for a SQL Reporting Services report (SSRS).

Technet:
Using Custom Assemblies in Reports

MS Support:
How To Use Custom Assemblies or Embedded Code in RS

Brian Like's Blog:
Writing Custom Code in Reporting Services

Report Model Filtering & Security

david — Fri, 28 Dec 2007 02:09:00 GMT

Filtering data in a SQL Server Report Model is rather persnickety, and there is limited instruction available online or in BOL.You use Report Models in Report Builder to develop ad-hoc SQL Reporting Services reports. This is a followup to the bug I discovered in Model Item Security as posted here and provides a workaround below.

First a brief explanation of Report Model filters and security would be in order:

Filter
A filter on an entity like Sales where Order Date =2004. This is defined in the model builder.

SecurityFilter
The filters defined must be attached to the entity in order to be applied. Each is applied in Union. Lockdown or access to each filter can be set by defining Model Item Security in SQL Server Management Studio. Defined in the model builder.

DefaultSecurityFilter
The default filter to use if no SecurityFilters are defined.

Model Item Security
Provides access at the attribute level. Access to individual fields or filters (though not explicitly stated is controlled)

A Practical Exercise

To get started, use the Microsoft tutorial called "Applying Security Filters to Model Items." It does a decent job with the basics of security filter but does not cover simple filtering, nor is there a description of how to setup the admin level "all rows" type of override that is mentioned.

Let's assume you have the Tutorial completed and the model built on Adventureworks as described. Delete the security filters they had you add in the Employee role. I'll come back to that. Let's start with something simple.

Simple Data Filters

So lets say you want to limit the output of data in reports built on this model to June 2004. Select the Sales Order entity and in the named attributes section, right click and Add a New Filter.

Drag Order date out to the Filter pad and set it to limit the dates from 6/1/2004 and 6/30/2004.

Click OK, then rename the filter to Filter 2004.

Next, select the Sales Order entity, and in the Properties window find the SecurityFilters property.
Click the elipsis (...) in the SecurityFilters property.
Find the Filter2004 filter and add this to the reference collection. Note that this dalog window is very confusing. You can only select attributes with IsFilter=True (Filters), and while you can select filters from other entities, you may be tied to certain constraints..like only aggregates on joins. Unfortunately, this dialog displays every single entity in your model, so try to remember your filter name. If you happen to choose an item that is not a filter or is an incorrect filter choice, your will only receive an error at the time of model deployment.

Save and Deploy your Model.

I created an abbreviated report based upon the example in the Tutorial. I don't need 300 pages to test out a sample. Below is what it looks like.

So what happened? Basically a SecurityFilter is able to filter the entire entity. As long as a single item is pulled from that entity (in this case, I used the Order Year field), the set will be filtered.

Note that we have not gone to SSMS to make any changes to apply the security. It is not needed for this type of filter.

Model Item Security

In the previous setup, we added a filter that applies to all users. Now we can add one that is conditional based upon the user running the report.
In the Sales Order entity add a new filter, but do not add any filter criteria. Just hit OK and rename it to AllDates. A filter without criteria will always evaluate to true. In this case we want to create an override to the filter for 2004 for an administrative user.

Next, add the filter to the SecurityFilters collection. The dialog showing this is found below.

Next, fire up SQL Server Management Studio (SSMS). We need to assign permissions by user to these filter attributes.
Connect to your Report Server, then browse to Models and find the Adventure Works Model. Double click to open the Properties window.
Select Model Item Security.
Check the box next to "Secure individual model items..." By default, there is no model item security defined. By selecting the box, we claim an override.

Click the Add Group or User button and add the group Everyone. Then click Ok and make sure "Model Item browser is selected.
Go to the Model item Sales Orders, then find AllDates.
Select "Use these roles for each group..." and 'Add Group or User"
Enter your servername\Rachel0 then select Model Item Browser.

We have just provided Rachel0 access to all years of data, and users who are not in this group will be relegated to 2004 informaton.
Click OK to save.

Run your report as two different users. Depending upon your configuration in SSRS you can simply login differently in two browsers. One as a normal user, and another as Rachel0. If you are not prompted for login, right click the IE icon before running and select the Run As... option.

You will notice that 1 user gets to see 2004 data and Rachel0 will see all years.

Some important caveats:

A Content Manager in SSRS is NOT confined by Model Item Security. This appears to be a bug, that I have reported to the Microsoft Connect site. I have also blogged about it here. So if you test any other user that is a Content Manager, they will have the same access as Rachel0
Subscriptions CANNOT be made on reports that use Model Item Security.

Since we have two very big challenges as mentioned above, I thought hard on a work around. Being able to subscribe to reports is more important to me than item level security. Besides, model item security was really only being used here to provide conditional access to a filter.

If we could place that conditional user access in the Report Model itselft without having to touch model item security we'd take care of both of those challenges. Here is the quick and dirty:

Go to your "all rows" filter on your model. Rather than leaving that blank, add your condition on user security there. You'll find below that we make TRUE equal to a conditional statement that also evaluates UserID.
Here I happen to use TRUE=IF(FIND(GETUSERID(),"Rachel") > 0, TRUE, FALSE)

Save and redeploy. Then go to SSMS and remove the Item level security.

When you rerun the report only users named Rachel will have access to all data. Others will be limited to data based upon the filter. This is a rather simplistic example, you probably won't name each user, but you can see the potential. Store user or group lookups in your tables for "managers" or others who need expanded access to data.

Before you fret that your user logic is tied to a database table, understand that it was alread stored there in the tutorial. And rather than individually set user access in SSMS, we are now storing it in a conditional statement in the model (or table if you prefer). So you still have security, we just shifted it's placement.

The benefit of course is not having to use SSMS to apply security, and the ability to now subscribe to reports even if they are filtered by user. Lastly, we bypass the "Content Manager has all access" bug.

Model Item Security Not Consistent for Content Managers

david — Thu, 27 Dec 2007 20:19:00 GMT

There seems to be a bug or miscommunication as it pertains to securing SQL Server Report Models using model item security from SQL Server Management Studio.

There is a fairly sparce Tutorial regarding row-level security on MSDN. Their Tutorial 3 goes over Row Level Security on Report Models. It states "Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model."

This is not true and is a rather confusing. An Administrator is a Windows security permission concept. A Content Manager is a Reporting Services security concept. Yes, the Administrator ID or AD Group from Windows will be evaluated for security when rendering a report on this model. If you have not be provided access specifically, you will be denied access to the report.

This differs from Content Manager. I cannot tell if this is a bug, or by design..but most likely a bug. Here's the kicker. If your user is a Content Manager, they will see all data regardless of SecurityFilter. Since the definition of a Content Manager is one who may manage folders, reports and resources...basically the objects that serve up data I believe this to be a bug.

To see this behavior, follow the tutorial to add security that filters based upon user id. Then add an "All Data" filter, basically a filter with no criteria that will evaluate as true to provide all data regardless of user. In order to lock this down, go to Model Item Security in SSMS and define only 1 user with access to this attribute.

The way securityfilters are handled in SSRS is the UNION of all applied. By partitioning the user rights in SSMS you disallow access to certain users.

However, if your user is a Content Manager they will be able to see everything. Basically the UNION remains in tact for them. I have logged this as a bug on Microsoft Connect with steps to reproduce. We'll see what they say.

Red X Instead of Image in Reporting Services

david — Wed, 26 Dec 2007 17:01:00 GMT

I've run into this annoyance a few times in SSRS. Instead of seeing your image on a deployed report, you see only a Red X. This typically occurs when your image is in a virtual directory. The workaround is to make the virtual folder available to ANONYMOUS access.

Microsoft has acknowledged the bug and released a HotFix for it.

Gantt Charting with Reporting Services

david — Fri, 21 Dec 2007 13:52:00 GMT

I've got an article over at SQL Server Central that describes how to get Gantt charts in SSRS using native controls, not third party. And it has a holiday spin.

This is the abstact:

Gantt charts in SSRS aren’t included in the 2005 release. You can purchase a third-party add-on, or, if you can wait, these powerful project tools might make it in the 2008 release. Or Alternatively, you can do this now with the native RS Chart control by using the methods David Leibowitz provides with the native RS Chart control.

This is the URL: http://www.sqlservercentral.com/articles/Reporting+Services/Santa+gets+an+RDL+present/61782/

The RDL example file is found in the Attachment link below.

Reporting Services Data Source Connection Examples

david — Thu, 20 Dec 2007 17:50:00 GMT

The following table lists examples of connections string for various data sources.

Data Source	Example	Description
SQL Server OLTP database	data source="(local)";initial catalog=AdventureWorks	Set data source type to SQL Server.
Analysis Services database	data source=localhost;initial catalog=Adventure Works DW	Set data source type to SQL Server Analysis Services.
Report model data source	Server=http://myreportservername/reportserver; datasource=/models/Adventure Works	Specify the report server or document library URL and the path to the published model in the report server folder or document library folder namespace.
SQL Server 2000 Analysis Services server	provider=MSOLAP.2;data source=<remote server name>;initial catalog=FoodMart 2000	Set the data source type to OLE DB Provider for OLAP Services 8.0. You can achieve a faster connection to SQL Server 2000 Analysis Services data sources if you set the ConnectTo property to 8.0. To set this property, use the Connection Properties dialog box, Advanced Properties tab.
Oracle server	data source=myserver	Set the data source type to Oracle. The Oracle client tools must be installed on the Report Designer computer and on the report server. For more information, see Defining Report Datasets for Relational Data from an Oracle Database.
SAP NetWeaver BI data source	DataSource=http://mySAPNetWeaverBIServer:8000/sap/bw/xml/soap/xmla	Set the data source type to SAP NetWeaver BI. For more information, see Defining Report Datasets for Multidimensional Data from an SAP NetWeaver BI System.
Hyperion Essbase data source	Data Source=http://localhost:13080/aps/XMLA; Initial Catalog=Sample	Set the data source type to Hyperion Essbase. For more information, see Defining Report Datasets for Multidimensional Data from a Hyperion Essbase Database.
XML data source	data source=http://adventure-works.com/results.aspx	Set the data source type to XML. The connection string is a URL to the XML document. For more information, see Defining Report Datasets for XML Data.

If you fail to connect to a report server using localhost, check that the network protocol for TCP/IP protocol is enabled.

Passing Parameters to Reporting Services using an URL

david — Wed, 19 Dec 2007 03:39:00 GMT

When launching SSRS reports by URL, it is often necessary to include parameters. To do so, simply include the parameter name, an equal sign, followed by the parameter.

So param=value for a report where CustomerID is 1 would look like:

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2ReportFolder%2DemoReport&rs:Command=Render&CustomerID=1

Now, let's say you want to pass in a NULL value for CustomerID. That would look like:

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2ReportFolder%2DemoReport&rs:Command=Render&CustomerID:isNull=True

Common SSRS URL Commands

david — Tue, 18 Dec 2007 03:38:00 GMT

&rc:Parameters=Collapsed
• This collapses the parameter bar but sends the parameters in the URL in the browser
&rc:Parameters=true
• This shows the parameter bar and is the default
&rc:Parameters=false
• This prevents the parameters from being passed in the browser and expanded
&rc:toolbar=false
• This hides the toolbar
&rc:format=PDF
• This will open up the report as a PDF
&rs:Format=EXCEL&rc:OmitFormulas=true
• This will open the report in Excel
rs:ClearSession=true
• This clears the session state for the user. Used where the caching of the report prevents the designer from seeing their updated report and/or data. * This is a very important parameter to know as more often than not, the report data doesn't update immediately unless parameters are changed, and your report may show cached data if this is not set.
&rc:StyleSheet=MyCustomStyle
• Note don’t add the .css extension. And this style sheet must be in the folder Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerStyles folder.
• Use the HTMLViewer.css as your base template.

Get Ranking of Groups in SSRS - Top X Ranking

david — Mon, 17 Dec 2007 03:36:00 GMT

Often you'd like to view a row number, or rank on a report in SQL Server Reporting Services. This is simple enough on a line item detail report with the RowNumber function. The reference to Nothing sets our scope.
Just use:
=RowNumber(Nothing)

But now how about something a little more challenging. Perhaps we have a list of 1000 Customers and we group them by State on a table. Perhaps the results will be sorted in order of Sales in decending order by State. Perhaps this is a Top 10 Report? Generally on these types of reports, it's nice to illustrate a ranking next to each row, not just the resulting data. To do so we'll need a different combination of functions to get the desired result.
First, create your detail, and a simple grouping by State. In the State Row, add a column. To this we will add the expression:

=RunningValue(THEFIELDYOUGROUPON, CountDistinct, Nothing)

or, in this example

=RunningValue(Fields!State.Value, CountDistinct, Nothing)

What might have you shaking your head is the use of CountDistinct. That's essentially what a grouping will do, in T-SQL, in SSRS...it's going to distinct your values and do some magic. Samples on the web usually illustrate the use of the Count function, but that will not produce the desired result in any grouping row.

Enforce Perspective Use in SQL Report Builder Models

david — Sun, 16 Dec 2007 03:34:00 GMT

Typically you'll lock down access to a SQL Reporting Services Model by creating a Perspective, either for security or to limit the amount of attributes available for a report. By default, the main model will be available in Report Builder with children listed for your available Perspectives.
But Report Builder is meant for ad-hoc generation by non-technical users. They might click the parent model instead of a perspective since it's the first entity they see on the screen or since they just don't know any better.
To help guide those users and to enforce the use of a Perspective you can disable the ability to build reports from the main model.

From the Report Model builder in MS BIDS (Visual Studio), create your perspectives.
Click the main model and look for Properties
Select the elipsis (...) in the Custom Properties item
When the Popup appears, click Add and create a new property with the following values:
DataType: Boolean
LocalName: MustUsePerspective
Namespace: http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign
Value: True
Save and deploy your model