Querying SSRS Reports, their Properties and Parameters

March 26, 2013

If you use SSRS’s WSDL interface, you have the ListChildren() and FindItems() methods to retrieve the uploaded report definition files (.rdl) in ReportService (2005), but I am not sure how to retrieve the parameter list.

Of course, there is the LoadReport() method in ReportExecutionService, the return value of which gives you access to the Parameter list, but that’s a different web service (URL and WSDL).

Originally I was looking for a solution that would involve only one of the two web services.

Back to square one: why not query the Report Service database directly? It contains a table [Catalog] which stores all reports (and more) along with their properties and parameters.

The Property and Parameter columns are defined as NTEXT, but their contents is XML, so we need to CAST() to XML because being able to access their contents.

The following TSQL example queries the reports for the language setting and the list of parameters:

with c as (
    select Name, Path, 
        CAST(Property as xml) as Property, 
        CAST(Parameter as xml) AS Parameter, 
        Type, CreationDate, ModifiedDate
    from Catalog
)
select Name, Path, Type, CreationDate, ModifiedDate,
    convert(nvarchar, 
        property.query('/Properties/Language/text()')) as Language,
    convert(nvarchar, p2.p.query('Name/text()')) as PName,
    convert(nvarchar, p2.p.query('Type/text()')) as PDatatype,
    convert(nvarchar, p2.p.query('Nullable/text()')) as PNullable,
    convert(nvarchar, p2.p.query('Prompt/text()')) as PPrompt
from c
outer apply Parameter.nodes('/Parameters/Parameter') as p2(p)
--where Type = 2

Notes:

  • Use CROSS APPLY rather than OUTER APPLY to retrieve only report definitions with at least one parameter.
  • Filter for Type according to this enum definition.
  • Change NVARCHAR to NVARCHAR(length) if XML data is longer than supported by plain NVARCHAR.

Struggling with SSRS 2008 Error rsAccessedDenied on Win7

February 15, 2013

After successfully deploying a RDL report definition to SSRS 2008 running on Win7, I tried to browse the Report Server web under http://localhost/ReportServer but was greeted by the error message

Die dem Benutzer ‘machine\username’ erteilten Berechtigungen reichen zum Ausführen des Vorgangs nicht aus. (rsAccessDenied)

The permissions granted to user ‘machine\username’ are insufficient for performing this operation. (rsAccessDenied)

Navigating to the Report Manager under http://localhost/Reports/Pages/Folder.aspx gave me the message

Der Benutzer ‘machine\username’ verfügt nicht über die erforderlichen Berechtigungen. Stellen Sie sicher, dass ausreichende Berechtigungen erteilt und die Einschränkungen der Windows-Benutzerkontensteuerung (UAC) behandelt wurden.

User ‘machine\username’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

Of course, the suggested Online “help” had not only no useful information, but really contained *nothing*at*all*.

I found this blog on rcAccessedDenied but at first could not really figure out what it meant. Especially since the described workaround (run browser as administrator, also mentioned by an illustrated DBA tip) did not work for me. At least, I figured out that I needed a “real” administrator account to proceed.

So I started Control Panel, Computer Administration, Local Users and Groups and double-click the Administrator account. I activated the account and set a password.

After this, I managed to log in to the Report Server web using the Administrator’s credentials. There I could assign roles to my usual Windows user, but the user was still unable to connect to both sites.

Using the Administrator login I could at least grant rights to browse the data sources folder under http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2fData+Sources&ViewMode=List and the deployed reports under http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2f[SolutionName]&ViewMode=List.

By the way, I tried running the browser as administrator on another computer (SQL Server 2012, SSRS, Win7) and was immediately able to browse the Reports/Pages/Folder.aspx. I really have no idea what’s going on here 😦


The Amazing FMTONLY Trick

January 15, 2013

I created a stored procedure to generate data for a report which involved several temp tables. To check the intermediate results, I thought I’d output the results of the various temp tables only during development, but skip the temp tables in production:

DECLARE @dev BIT = 0;    -- set to 1 during development
IF @dev = 1 SELECT * from @temp

This works fine in SSMS.

But trying to set the SP as report DataSource in Report Designer (.rdl Editor), VS offered the columns of the first SELECT statement, ignoring the value of @dev.

As you may know, when you define a data source in a VS IDE tool, the IDE invokes the SQL statement using the SET FMTONLY ON setting to retrieve information on the columns of the data source.

One feature of the FMTONLY mode is how IF statements are processed: BOTH the IF and the ELSE branch are analyzed independent of the result of the IF condition. This explains why the IF … SELECT always returns the column data of every SELECT statement in the SP.

I searched and found this article on SQLServerCentral on how to output only specific data sets in FMTONLY mode. What I did not know was that you can set FMTONLY anywhere inside a T-SQL procedure!

My variant of the code in the referenced article looks like this:

– Detect whether FMTONLY is set (only then the IF(1=0) will be executed):

DECLARE @fmtonlyON BIT = 0;
IF (1=0) SET @fmtonlyON = 1;
SET FMTONLY OFF;

– Create and SELECT temp tables as you like, IF statements will be handled correctly

– Reset FMTONLY where required

IF @fmtonlyON = 1 SET FMTONLY ON;

A comment on the article also mentions that this method allows #Temp tables in SPs without raising the error message

Msg 208, Level 16, State 0, Line {line}
Invalid object name '#TempTable'.

*Yes, the title of this blog was inspired by this great video. Enjoy 😉