softlogger Latest Articles ::SQL-Reporting-Service-SSRS

What does an exported PerformancePoint scorecard look like in Reporting Services report designer?

Russell Christophers Semi-Useful BI Musings — 2007-11-28T00:00:00

I was curious what would happen when I opened an RDL file created by PerformancePoint in Report Designer, so I tried it.

Here is what I discovered:

1. For whatever reason, PPS exports RDL using the SSRS 2000 RDL spec (xmlns=http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition). When you attempt to open the report up with BIDS, you’ll be prompted to upgrade it.

2. If you click the Data tab, you’ll hit the error: “The designer extension ScorecardDPE could not be loaded. Check the configuration file RSReportDesigner.config”

I found there were two ways to (sort of) solve the second problem.

The first workaround is rather old-fashioned. Just keep clicking OK 3-4 times and the error message eventually gives up. Then, switch the query designer into generic mode and dismiss the error message you get there 3-4 times. After that you can run the query with the “!” button, and things work.

If you want to be a little bit more creative, go ahead and edit (carefully, of course) RSReportDesigner.Config. You’ll find the file in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies, and you’ll want to place the following element in the <Designer> block of the file:

Extension Name="ScorecardDPE" Type="Microsoft.ReportingServices.QueryDesigners.VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners" />

If you go this route, you’ll STILL get an error (“Format of the initialization string does not confirm to the specification starting at index 0”), but you only have to dismiss it once at which point you can go into the generic query designer mode (without additional generic query designer errors to get rid of).

Both solutions aren’t perfect, but then we don’t live in a perfect world, eh?

In terms of modifying the report layout itself, you can change the header colors from “Office 2007 Blue” to whatever you’d like, remove any of the Value, Goal/Status, Trend columns, etc.

Reporting Services Login dialog appears when deploying to MOSS/WSS

Russell Christophers Semi-Useful BI Musings — 2007-02-07T00:00:00

Brian Welcker does it again! In this post he describes a situation that's been causing me a great deal of angst. Essentially, after getting MOSS/SSRS integration working, you get prompted to login when publishing from BIDS - and entering credentials doesn't get you past the dialogm either.

I'm not big on "link wrangling", but over the past few days there have been too many good ones to pass up. Read this. Now.

Reporting Services: Why aren’t all my report columns exporting to CSV and/or XML?

Russell Christophers Semi-Useful BI Musings — 2007-02-07T00:00:00

Don’t be surprised if reports exported to CSV or XML are missing several columns of data you otherwise see when viewing the report as HTML, PDF, etc.

PDF, HTML, and Excel export leverage “layout renderers” – a generally more expensive export process which attempts to mimic the “look” you created in Report Designer. CSV and XML are “data renderers”, and only care about data. The difference is reflected in what a layout renderer typically exports (basically everything) and what data renderers save (not necessarily all of your columns).

Here is a semi-complete list of the factors that can impact whether or not your columns are exported when saving to CSV:

· Static Values – If a column in your report contains hard-coded static data that doesn’t change from row to row (like the value “hello”), a data render won’t bother exporting it.

· Expressions on Visibility– If you conditionally flip the Hidden property of a textbox or column in your report, data renderers will automatically omit this data from export, even if the expression evaluates to False (False meaning “don’t hide”). This is a by-design behavior.

The happy ending is that you can ultimately control the export behavior on a textbox-by-textbox basis with the DataElementOutput property. By default, this sucker is set to “Auto”, which allows the data renderer to think. If you don’t want it to think, but just OBEY, you can set this value to Yes or No.

To get to the right dialog, right-click any textbox, choose Properties on the context menu, then select the Data Output tab

Tons of thanks to Denis Levin for much of this information!

Post-publishing edits:

removed bullet re: many NULL values in a column causing data not to be exported - misunderstanding on my part

Publishing Reporting Services reports to MOSS

Russell Christophers Semi-Useful BI Musings — 2007-01-16T00:00:00

(Many, many thanks to Heidi Steen of the super-duper UE team at Microsoft for providing the material below. Hell, she even created screen shots!)

Now that lots of folks are happily playing around with SSRS/MOSS integration, it’s important to understand exactly how you publish the things to MOSS via Business Intelligence Studio. You still use the same dialogs you’re used to, but plug different values into them. Here are some nice, step-by-step instructions:

1. In SQL Server Business Intelligence Development Studio,

open an existing or new Report Server project.

2. From the Project menu, click Properties. The <project>

Property Pages dialog box opens.

3. Choose the Configuration you use to publish to a SharePoint site.

For example, Active(Debug) or Production.

4. In the Deployment section, for the optional TargetDataSourceFolder,

type a fully qualified URL to a library or library folder. For example,

http://server01/mycustomreportingsite/Shared Documents.

If left blank, this defaults to the TargetReportFolder value.

5. For TargetReportFolder, type a fully qualified URL to a

library or library folder. For example,

http://server01/mycustomreportingsite/Shared Documents.

6. For TargetServerURL, type a URL to a SharePoint

top-level site or sub-site. If you do not specify

a site, the default top-level site is used. For

example, http://servername, http://servername/site

or http://servername/site/subsite.

7. Click OK.

8. In Solution Explorer, right-click on the report

you want to publish, and click Deploy. The report

is published to the location specified in

TargetReportFolder. Deployment errors appear in the Output window.

NOTE: In order to upload data sources and reports to MOSS, the user in question must have “Contribute” or “Full Control” permissions.

Below, I’ve attached some screenshots which demonstrate publishing to a library in a custom and default MOSS site (again, thanks to Heidi!)

Happiness is automatic SQL Reporting Services localization

Russell Christophers Semi-Useful BI Musings — 2007-01-11T00:00:00

Many people understand that you can leverage User!Language in order to grab the regional / language settings of a user, then use this information to localize your report data & label text. If you don’t, no worries. Check this topic out:

http://msdn2.microsoft.com/en-us/library/ms156493.aspx

While leveraging User!Language certainly will work in terms of allowing you localize your reports, doing so is admittedly a pain in the tail – you essentially have to replace all your label text with expressions that call into code that you write to do “label localization” ala:

= MyNameSpace.MyConvertingClass.MyConverter(“someLabelName”, User!Language)

Your code takes the name of a label and the language to use, then goes off to a resource file to pull and return the correct string.

Localizing your data is less painful, you just store your “description text” in multiple languages (see the Product.ProductDescription table in AdventureWorks to see what I mean) in a table and use a parameterized query to yank the correct record based on User!Language.

Leveraging the SQL Analysis Services Translations feature makes all this a thing of the past, however. Assuming that your data is moving through a cube, you can use this great feature to localize all your text, including captions, data, etc: See the AdventureWorks DW OLAP sample in C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise\ . Once you get it open, click the Translation tab and explore a bit.

Anyway, if you have translations in place, all you have to do is tell SSRS to use ‘em, and your life is good. To do so, utilize the LocaleIdentifier connection string property of ADOMD.NET 9.0 to set a locale (like 1036 for France). See the attached (very simple) RDL document which utilizes the Adventure Works DW cube.

In Excel 2007, you get this behavior “for free”, but in SSRS you must add the LocaleIdentifier connection string property yourself, and give it a numerical value (1033, 1036, etc.) Here’s the part which makes it all work – you use an expression-based connection string to sense the user’s language (in this case “Are you French? If not, you’re getting English”) and feed in the correct LocaleIdentifier:

(Looks like I can't post two files at the same time...go to the "Here's the RDL" page if you really want to see the RDL...I'll post it there.(

Reporting Services - Add a logo to the Report Manager

JonGalloway.ToString() — 2006-12-13T00:00:00

The SQL Server Reporting Services Report Manager is functional, but it's not very customizable. The ASPX pages just reference compiled assemblies, so the only real way to modify them is via CSS.

What makes that more difficult is that the SSRS HTML is poorly constructed so that the tags you'd most want to customize don't have Id's or Classes assigned. For instance, the most obvious customization anyone would want to make is to add a corporate logo, and there's no hook for that at all. The folder image is a simple <img> tag, nestled between two other spacer <img> tags, packed inside an unidentified <td>, and all those elements are written out in code¹. The problem there is that there's no way, even with some descendant selector trickery, to target that folder image.

That's not entirely true, part 1:

IE7 adds support for some combinators (e.g. child, adjacent, first:sibling) which allow you to directly target elements without classes or id's. Why I'm not using that approach here:

IE6 doesn't support it.
IE7's support is kind of quirky - it sees HTML comments as page elements, for instance. That means it's difficult to write cross browser combinator based CSS rules.
Targeting deeply nested elements with combinators is pretty difficult.
Combinator based rules are inherently unstable, since any changes to the page's structure (including HTML comments, as mentioned above) can really mess up your page. Structure changes can cause your rule to be ignored or - much worse - point it at another page element.

That's not totally true, part 2:

You could replace the image files (C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager\images\48folderopen.jpg
as well as all the other files in that directory named 48*.jpg)² with your corporate logo. Problems with that approach:

You're constrained to a 48x48 px jpg
Subsequent upgrades could overwrite it
Those images do serve some use - they change depending on the current function you're performing in the site.

Who needs an Upper Title, anyway?

I went for the next best target - the rather useless "upper title" - since it's a div which actually has a class (div.msrs-uppertitle)assigned. First, let's look at the style changes, then we'll talk about the different ways to add them.

.msrs-uppertitle
{
    BACKGROUND: url(http://www.sitename.com/images/logo.gif) no-repeat; 
    HEIGHT: 35px;
    WIDTH: 120px; 
    TEXT-INDENT: -5000px; 
}

Some things to notice:

We set a background image by URL. That will keep the reports up to date with our company logo as it may change in the future. Make sure to pick an image that's an appropriate size for that space.
We set the background to no-repeat so it just displays once rather than tiling. That's handy even if the logo image has a border, since we don't have to be so precise on your height and width settings.
We set a height and width which are large enough to display the logo. These should be just larger than your image size; you can just see via trial and error what looks best. This is one of those times where on the fly CSS editing tools like the IE DevToolbar or the Firefox Web Developer extension come in really handy.
Now we need to get rid of the site title. I didn't want to mess with trying to blank it out in the SSRS Site Settings since it might be useful elsewhere, or someone might fill it in without knowing that it would conflict with the logo. The easiest solution is to use the Phark Image Replacement technique and hide the text by setting the indent to -5000px.

Done and done.

Oh, wait. I said I'd be talking about where to add this style, didn't I? A few options:

Edit ReportingServices.css (in the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles folder).
Use another stylesheet - method 1: change the HTMLViewerStyleSheet parameter in RSReportServer.config
Use another stylesheet - method 2: specify a stylesheet in rc:StyleSheet parameter of the report url:
http://localhost/reportserver?/AdventureWorksSampleReports/Product+Line+Sales&rs:Command=Render&rc:Stylesheet=MyStyleSheet

I went with a variation of option 1 - I edited ReportingServices.css, but just to add an import statement that points to another CSS file where I'll put all my Reporting Services CSS customizations:

Add the following to the top of ReportingServices.css:
@import url(customizations.css);
Save the block of CSS (the bit that starts with ".msrs-uppertitle") to a file called customizations.css to the same folder as ReportingServices.css. You might want to add other customizations to it while you're at it, like this CSS fix for Firefox.

That's it! We're done!

Great, but the logo should link to the home page of our intranet!

What!? Feature creep! Version 2!

Oh, okay. It's not easy, though...

Changing style isn't a piece of cake, but at least there's a supported hook for it. You can't add a link via CSS, though. CSS2 allows for some limited content generation, but it doesn't support adding links; even if it did we'd be out of luck because IE7 doesn't support it.

Bill Vaughn and Peter Blackburn proposed a solution using DHTML Behaviors. That's a pretty slick workaround - behaviors allow you to tie Javascript functionality to DOM elements in CSS. Unfortunately, only IE supports behaviors. Plus, if you're going to hack Javascript, why not edit the ReportingServices.js file...³

C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportManager\js\ReportingServices.js

There it is... the fabled treasure... a way to modify the content of the Reporting Services Manager...

I added the following code to the top of the JS file (after backing it up, of course):

addLoadEvent(SetLogoUrl);

function addLoadEvent(fn)
{
    if (window.addEventListener)
        window.addEventListener('load', fn, false)
    else if (window.attachEvent)
        window.attachEvent('onload', fn);
}

function SetLogoUrl()
{
    var header = document.getElementById('ui_sharedArea');
    if (!header) return;
    var headerDivs = header.getElementsByTagName('div');
    for (var i=0;i<headerDivs.length;i++)
    {
        if(headerDivs[i].className == 'msrs-uppertitle')
        {
            headerDivs[i].onclick = new Function('top.location="/"');
            headerDivs[i].style.cursor = 'pointer';
            //headerDivs[i].style.backgroundImage = 'url(http://images.slashdot.org/topics/topiccommunications.gif)';
            //headerDivs[i].style.backgroundRepeat = 'no-repeat';
            //headerDivs[i].style.width = '96px';
            //headerDivs[i].style.height = '62px';
        }
    }
}

Let's talk about it:

That addLoadEvent business is there because we can't modify the page until it has finished loading, but this script is included at the top of the page. A less sophisticated solution is to set a timeout to call SetLogoUrl, but addLoadEvent leverages the built in event model to call the function after the page has finished loading. IE uses attachEvent, while everyone else uses addEventListener, so we'll include some logic to make sure the right one gets called.
SetLogoUrl's job is to modify the Upper Title DIV. This would be easier if that DIV had an ID assigned, since Javascript doesn't have built in support for retrieving an element by class name. Most sample code which returns an element by class just iterates all elements in the DOM looking for a class name match, but since we know a bit about the page structure we can be a little more efficient. We narrow the search by grabbing the nearest parent element with an ID assigned and only searching through its children.
Once we've got our DIV, we can just set the onclick behavior to navigate to the correct URL. We'll need to change the cursor to indicate that it's a link, too.

Hey, since we're messing with that DIV, why not just change the image in Javascript and simplify things? Well, I've left some commented code there to get you started if that's what you want to do, but I think it's better to keep the image modification in CSS.

Why? Well, there are a few good reasons. The biggest reason is that the Javascript doesn't execute until the page has finished loading, so the original DIV text is displayed, then changes to the logo. Ugh. Plus, from a maintenance and architectural perspective, it's a much better practice to keep your presentation information in CSS and your behaviors in Javascript.

And that's it.

But, can we change the navigation to use WPF/E with Ajax and pull in some Google Analytics?

No.

¹ reflector://Microsoft.ReportingServices.UI.SharedArea.InitTitleArea()

² Reporting Service installs into different directories depending on the order you install SQL Server services. A default installation will put Reporting Services in \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services, but that MSSQL.3 folder may be different on your machine.

³ I actually stumbled across the HTC technique after I'd finished making this work as a Javascript include.

SYSK 219: Solution for SQL Reporting Services Problem Described in SYSK 203

AppDev: Something You Should Know by Irena Kennedy — 2006-10-16T00:00:00

In http://blogs.msdn.com/irenak/archive/2006/09/22/766164.aspx post, the following problem was described: if you have a SSRS report parameter that is multi-value and of one of the labels in the “available values” list exceeds 25 characters, the dropdown list with values for this parameter will be positioned in such a way that the end user won’t be able to see and thus select the first item.

It came to my attention that there is a hotfix available that resolves this and some other issues. For more information, visit http://support.microsoft.com/?kbid=919478.

Reporting Service Updates in SQL 2005 Service Pack 2

William Lis Enterprise Reporting — 2006-10-13T00:00:00

SQL Server 2005 Service Pack 2 is coming out soon. The very important piece of SP2 is support of SSRS and SharePoint 2007 Integration. There are many bug fixes and enhancements in the service pack but here I want to highlight the integration with SharePoint 2007.

The key functionalities include:

SSRS & SharePoint Integration

The existing integration between Reporting Service and SharePoint are very limited. It is primarily done through two web parts – Report Explorer for listing the reports and Report Viewer for execution of the report. Security, Management of the UI and others are not integrated. In this coming Service Pack 2, SSRS not only starts to support SharePoint 2007 which is coming out before end of this year as part of Office 2007 product release, it also enhances the degree of the integrations.

Seamless integration with SharePoint Document Library in Office SharePoint Server 2007
- Report server namespace and security is hosted in WSS content database
- SharePoint database stores Report Server reports, data sources, models, resources etc.
- Report Server database used for additional metadata (schedules, caching, subscriptions)
- Enables collaboration, workflow, versioning
Centralized user interface for Report Management through Office SharePoint Server 2007
- Upload, manage, render and deliver Reports all through Office SharePoint Server 2007 UI
- Administration using Office SharePoint Server 2007
Rich reporting experience to "light up" Office SharePoint Server 2007
- Filter web parts enhancing dashboards in Office SharePoint Server 2007

In SP2, Reporting Service also integrates with Hyperion, and Report Builder supports for Oracle Data Sources.

Use Reporting Service Itself to Monitor Customers' Reporting Activities

William Lis Enterprise Reporting — 2006-10-02T00:00:00

The list of reports requested in Requirement or Functional Specification by customers is one thing, the actual list of reports used for day-to-day business after Production is another thing. While we know customers have the challenges of changing business needs, how do we understand and measure what are the reports that are actually being used by customers after Production? To some degrees, it is important to understand this fact so that future iteration of developments and/or maintenance can be placed in the right order of priorities.

I found an easy way to do that - if you are using SQL Server 2005 Reporting Service. The idea is to upload the IIS log for the Reporting Service into an IT or operation oriented database (anywhere in the internal network) and then organize the data for a few handy and useful reports through Reporting Service. To do that, there are two tools you should download:

Log Parser 2.2: Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory. You can download the Log Parser 2.2 here.

SQL 2005 Reporting Pack for Internet Information Service: a set of 12 predefined reports and a sample database to easily visualize and author managed reports such as site summary statistics, time period, page views and user statistics, based on information extracted from IIS log files.
Download it here.

Install them both on your Reporting Server. Create a database for your IT reporting, say name it ITRPT. Use the following scripts to populate the log data into the database:

C:\Program Files\Log Parser 2.2>logparser -i:IISW3C -o:SQL "SELECT * INTO ITRPT.IISLog FROM \\myreportingserver\REPORT_LOGS\*.*" -oConnString:"Driver={SQL Server};Server=Mydatabase;db=ITRPT" -database:ITRPT -createTable:ON

The above scripts will upload the IIS Log file (located in \\myreportingserver\REPORT_LOGS\*.* into a backend database ITRPT in a server Mydatabase. A new table IISLog will be created automatically.

You can now have access to a set of pre-defined reports to manage your Reporting Service site. The reports available including when users visit your Reports, where the users coming from, etc. In addition, by leveraging the IIS Log database table, you can perform some transformation to extract useful information:

SELECT replace(replace(substring(csUriQuery, 10, 300), '%2f', '/'), '+', ' ') as Report, count(*) as total
FROM iislog
WHERE csUriQuery like 'ItemPath=%' and csUriQuery not like '%ViewMode=%' and csUriQuery not like '%SelectedSubTabId=%' and csUriQuery not like '%SelectedTabId=%'
GROUP BY csUriQuery
ORDER BY Total DESC

Basically what the above scripts does, is to check the Report folders in your home pages and then perform calculations on the total visits on the reports. Once you got there, by using Report Designer, you can create some very useful reports. For example, Top 20 Most Active Reports within 30 Days, Most Active Reports by Project, etc.

Can't install SQL Reporting Services 2005 on Vista?

Russell Christophers Semi-Useful BI Musings — 2006-09-25T00:00:00

See this article:

http://support.microsoft.com/kb/920201

SYSK 203: Mind That Label Length in SQL Reporting Services

AppDev: Something You Should Know by Irena Kennedy — 2006-09-22T00:00:00

Another possible (but unconfirmed) bug in SQL 2005 Reporting Services:

If you have a SSRS report parameter that is multi-value and of one of the labels in the “available values” list exceeds 25 characters, the dropdown list with values for this parameter will be positioned in such a way that the end user won’t be able to see and thus select the first item.

This problem does not exist when viewing this report from Visual Studio, but is easily seen after deploying the report to SSRS web server.

So, for now, you may want to trim your parameter labels to 25 characters. If you’re doing filtering based on the full name instead of some sort of id, you can use the value field to store the full parameter label.