Posts

The Perfect Host

/0 Comments/in /by

This is my first blog about SSRS 2008. The July CTP (CTP4) of SQL Server 2008 (aka Katmai) includes the new hosting model of Reporting Services. Gone is the dependency to IIS and all the management headaches associated with it. Instead, the SSRS Windows service pulls a nice trick by hosting the http.sys kernel-mode device driver which listens for HTTP requests. Behind the scenes, the SSRS Windows service hosts application domains for the Report Manager and Report Server and forwards the incoming requests to them.

You reserve URL addresses for Report Manager and Report Server by using the Reporting Services Configuration utility. If you have ever set up a web site in IIS, you know everything you need to reserve an URL address. The URL address consists of IP address, TCP port, and vroot. You can specify any unreserved port (in Vista and Windows Server 2003+ you can have SSRS and another application listen on the same port, e.g; IIS and Report Server both listening on port 80). For example, if you reserve port 8080 and accept the defaults, the report server URL address will be http://<servername>:8080/ReportServer. You can optionally use the Advanced tab to specify additional settings, such as host headers. When the SSRS Windows service starts it registers the URL addresses with http.sys. Http.sys listens for http requests and forwards them accordingly.

080407_1538_ThePerfectH1

While we are still on the Reporting Services Configuration topic, here is a nice tip. CTP4 doesn’t officially support upgrading from SSRS 2005. However, you may need to test old reports with SSRS 2008. You can upgrade an old catalog by using the Database tab and pointing to a SQL Server 2005 instance that hosts the SSRS 2005 catalog. The Reporting Services Configuration utility will upgrade the catalog in place. Later, you can back up and restore the catalog on the SQL Server 2008 instance if you need to remove the dependency on the SQL Server 2005 instance.

SSRS Setup Woes

/1 Comment/in /by

I was setting up Reporting Services 2005 today on a clean Windows XP machine and I came across the infamous error:

The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError). Access to the path ‘c:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\RSReportServer.config’ is denied.

Here is how I fixed this horrible problem. First, I open the Computer Manager and verified that the following two groups exist: SQLServer2005ReportingServicesWebServiceUser$<machinename>$MSSQLSERVER and SQLServer2005ReportServerUser$<machinename>$MSSQLSERVER.

Next, I used the Windows Explorer to verify that these groups have read ACL permissions to the Report Server folder (C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer) by right-clicking on the this folder and checking the Security tab. Finally, I verified that the following users have been assigned to these groups.

  • SQLServer2005ReportingServicesWebServiceUser$<machinename>$MSSQLSERVER should have the ASP.NET account assigned to it. In Windows XP, the default ASPNET account is <MachineName>\ASPNET. In Windows Server 2003 and Vista, this should be the built-in NT AUTHORITY\Network Service account.
  • The SQLServer2005ReportServerUser$<machinename>$MSSQLSERVER should have the account the SSRS Windows Service is running under, e.g.; NT Authority\SYSTEM, if it is running under local system.

In my case, the issue was caused by the fact that for some obscure reason, the SQL Server setup program had added <domain>\ASPNET to SQLServer2005ReportingServicesWebServiceUser$<machinename>$MSSQLSERVER instead of the local ASPNET account. So, I removed this account and added machinename\ASPNET. This took care of rsServerConfigurationError.

Since I wanted to use my old ReportServer database I followed the steps in this KB article to restore it over the existing catalog database. But then when I browse to localhost/reportserver I had another error that SSRS cannot connect to the ReportServer database because login failed for the ASPNET account. So, I tried the Reporting Services Configuration utility and hit the Apply button on the Database tab but I got an error status message that the ASPNET account already exists in the ReportServer database. To solve this error, I used SQL Server Management Studio to drop the existing ASPNET logins and associated schemas from the ReportServer and ReportServerTemp databases and hit the Apply button on the Database tab in the Reporting Services Configuration utility again.

How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer

/0 Comments/in /by

I spent a couple of hours today trying to get SSRS 2005 working on Vista. I was getting the infamous IIS 500 error when browsing the Report Manager (http://localhost/reports) and Report Server (http://localhost/reportserver) virtual roots. I triple-verified the Brian Welcker’s recommendations and the Vista considerations in the SQL Server 2005 readme file. I couldn’t figure what’s going on especially given the fact that I installed SSRS on my home Vista machine with no problems. The only difference this time was that I performed file-only install of SSRS because I wanted to use an existing report catalog. In a moment of a Google eureka, I came across the How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer KB article. It turned out that the Reporting Services Configuration Utility put the IIS applications in the default application pool which in IIS 7.0 is running in Integrated Managed Pipeline Mode. I created a new ReportServer application pool in Classic Managed Pipeline Mode, put the ReportServer and Reports applications in, and the issue went away.

Aggregate Rows and SP2

/2 Comments/in /by

Back to my When (null) is not Null post, there is a undocumented breaking change in SQL Server 2005 SP2 which you should be aware of. In response to the customer demand, SP2 treats aggregate rows as detail rows.  As a result, if the report doesn’t use the Aggregate() function, the report will show all rows. For those using the Aggregate function, there will be no change.

TechEd 2007 US Memoirs

/0 Comments/in /by

I am back from an one-week vacation to Clearwater, Florida followed by TechEd 2007 US in Orlando. Overall, nothing earth – shattering on the BI arena from TechEd 2007. Product groups are between releases so most of the content was SQL Server 2005-based with a few sneak previews of some of the good stuff to come in Katmai.


My presentation attracted a fairly large crowd. Almost 500 folks joined my presentation “Applied Microsoft Reporting Services 2005” which was scheduled for the first breakout slot after the keynote on Monday. Thank you! I’ve made the source code and slides available for download on my website. Courtesy to Marco Russo, I’ve also uploaded a couple of photos to capture the moment of glory.


Now that I can open my mouth about some of the cool features coming up in the Katmai release of Reporting Services that were officially announced, here are the most important ones:



  1. Standalone report designer – both the VS.NET Report Designer and the Report Builder will share the same full-featured designer. Note that “standalone” doesn’t mean “embeddable”. While a post-Katmai release may bring us a designer which can be hosted in a custom application this will probably not happen in Katmai. Sigh…

  2. Tablix region – The table and matrix regions unite and give a birth to a brand new region called tablix. This versatile control can handle it all. By changing a few properties, the tablix can morph itself into a table, crosstab, or list report! The tablix region will enable reports that were not possible or required hacks in the past to get around the matrix limitations, such as stacked columns in a crosstab reports, multiple subtotals, and multiple pivot columns. I wish I had this baby a year ago…

  3. RDL Object Model – The Katmai release will ship with public RDL Object Model. This is a great news for developers (myself included) that need to generate RDL programmatically.

  4. Acquisition of the Dundas RS controls – MS acquired the Dundas chart, gauge, and map for Reporting Services. This will sure spice up the report graphical experience and position MS to compete better with other report vendors.

  5. Acquisition of the Soft Artisans Office Writer technology – This will let business users embed reports in Microsoft Word and Excel documents.

  6. Many designer enhancements – HTML inline support, better designer experience, export to Word 2000-2007, etc.

I hope I haven’t missed something important. The first Katmai CTP build to include some of the new RS enhancement is due in July.


Nothing too exciting on the SSAS side of things as the main team of the next release is “don’t rock the boat”. It will focus mainly on improving the server performance, manageability, and robustness.

A Couple of Report Builder – UDM Hacks

/0 Comments/in /by

If you target the SSRS Report Builder as an UDM client you may find the following two workarounds (aka hacks) useful. Both hacks require manually updating the SMDL definition. Alas, so much about model auto-generation L.

Multi-grain Relationships Don’t Appear

If a dimension joins a measure group at a higher grain the corresponding Report Builder role is excluded from the model. Steps to repro:

  1. Open the Adventure Works SSAS project in the BIDS cube designer and switch to the Dimension Usage tab.
  2. Note that the Date dimension joins the Sales Targets measure group at the Calendar Quarter level (not the Date level). That’s because the FactSalesQuota (the fact table Sales Targets is based on) stores the sales quotas at the quarter level in AW.
  3. Generate a report model from a data source pointing to the Adventure Works SSAS database
  4. Open the Report Builder and connect to the Adventure Works cube.
  5. From the Sales Targets folder, drag the Sales Amount Quota on the report canvas.
  6. Note that the Entities pane excludes the Date dimension.

Hack: Change the SMDL definition manually to include the dropped relationship. To do so, compare the SMDL definitions side-by-side before and after the grain was changed using a file comparison tool such as SourceSafe or ExamDiff. For example, the first missing role entry should look like the one below:

<RoleID>udm:Role_MeasureGroupDimension_Entity_MeasureGroup_RPM_Profitability_Measures_Entity_Dimension_Date_Entity_Dimension_RPM_Date</RoleID>

</RolePathItem>

</Path>

<AttributeID>udm:Attribute_Hierarchy_Date.Date</AttributeID>

</AttributeReference>

<AttributeReference>

<Path>

<RolePathItem>

 

In my case, I had to restore three entries caused by raising the grain of a dimension relationship. Once the entries have been restored manually and the model was deployed, the reports worked as expected.

Text-based Calculated Members Are No-Show

An UDM text-based calculated member comes back as empty in a Report Builder report. That’s because the Report Builder defaults the data type of the member to Float. Steps to repro:

  1. In the Adventure Works sample cube add the following calculated member:

    Create Member CurrentCube.[Measures].[Hello]
    As “Hello”,
    VISIBLE = 1;

  2. Assign the member to a measure group so it will show up in the Report Builder.
  3. Create a table report and uses the Hello member. The column comes back empty.

Hack: Overwrite the data type of the member in the auto-generated SMDL:

<Attribute ID=”udm:Attribute_Measure_Hello”>

<Name>Hello</Name>

<DataType>String</DataType>

<Nullable>true</Nullable>

…

</Attribute>

 

Deploy the model and observe that the text-based calculated member now does show.

Not All Hyperlinks are Born Equal

/0 Comments/in /by

The ASP.NET ReportViewer control has a HyperlinkTarget property which may give false expectations. You may believe that this property applies to all hyperlinks, including links to drillthrough reports. For example, you may attempt to set the HyperlinkTarget to _blank to open a drillthrough report in a new window. However, you will be disappointed to find that drillthrough reports ignore the HyperlinkTarget property completely. That’s because they require a page postback.


As it stands, the HyperlinkTarget property applies only to “regular” (Jump to URL) hyperlinks. Propagating HyperlinkTarget to drillthrough links is on the wish list for a next release. Meanwhile, if you need to open a drillthrough report in a new window, consider using a Jump To URL link that points to an ASP.NET page hosting the ReportViewer control (or reuse the same one that displays the master report). The ASP.NET page would parse the link and load the drillthrough report in the ReportViewer control. If you set the HyperlinkTarget property to _blank, the drillthrough report will open in a new window because it now launched from a regular link.

SQL Server 2005 SP2 Brings Self-upgrading Catalogs

/0 Comments/in /by

A little known but very useful SSRS feature which debuted in SQL Server 2005 SP2 is that the SSRS Windows service automatically upgrades the report catalog (ReportServer database) if required. As I discussed in this blog, you may need to swap the report catalog for testing or troubleshooting purposes. In the past, you had to manually upgrade the catalog from a previous version using the Report Services Configuration Utility or using the rskeymgmt utility.

Starting with SQL Server 2005 SP2, when the SSRS Windows Service connects to the report catalog it runs the stored procedure to get the database version of the catalog. If the versions don’t match, SSRS Windows Service calls down to WMI to generate an upgrade script and applies it. The service executes the catalog version check every time it connects to the catalog.

My TechEd Session Scheduled

/0 Comments/in /by

My breakout TechEd session BIN305 – Applied Microsoft SQL Server 2005 Reporting Services is scheduled for June 4 at 10:30 AM – 11:45 AM in room S320 A. That’s right – let’s talk reporting right after the keynote!

I hope to see some of you there.

Excel Web Access Drill-through to SSRS Report

/1 Comment/in /by

I’ve been having fun lately with a dashboard page which has an Excel Web Access (EWA) web part connected to a PivotTable report. The PivotTable report is nothing to brag about. It displays a few KPI-related measures coming from an SSAS cube. PivotTable was converted to formulas to achieve a free-form report layout. So far, pretty straightforward stuff. That is, until the customer wanted the ability to drill through the chart to see a more detailed view from the cube.

Since to my understanding cross-EWA drillthrough is not possible because Excel Services is not URL-addressable (shame [:@]), I implemented this scenario by allowing the user to drill through from the Excel spreadsheet to an SSRS report. To implement this, I converted the Excel cell to a hyperlink which has the URL address of the SharePoint report using the Excel HYPERLINK function:

=HYPERLINK(“http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “:8080/ReportServer?http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “/Reports/ReportsLibrary/Top 50 Customers.rdl&DateCalendar=[Date].[Calendar].[Calendar Year].%26[” & PreviousPeriod & “]&rs:Command=Render”, “Growth in Customer Base”)

There are a few interesting things going on in this nasty expression that deserve more explanation. First, instead of hardcoding the server name, I added a ServerName calculated member to the cube that is mapped to the following SSAS stored procedure:

public static string ServerName() {

     return Context.CurrentServerID;

}

Second, note the URL address of the report. Since SSRS is configured for SharePoint integration, the report path points to the WSS library where the report is deployed. Finally, I had to solve the nasty issue with ampersands in the member names b/c the drillthrough SSRS report takes a member as a parameter. If I just use & the Report Server will choke b/c it will think that it is a parameter placeholder. The hack is to escape & to %26 .

Here is what the link looks when rendered in the browser.