A Couple of Report Builder – UDM Hacks

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:










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”>






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

Companion for MS Analysis Server

SQL Minds has released Companion for MS Analysis Server. Companion for MS Analysis Server is a product that helps with the health monitoring and performance tuning of Microsoft SSAS 2005. The product offers historical baseline, custom aggregations and their usage, meta data browsing, cube browsing, various reports on SSAS inner workings, trace information on slowest SQL, DMX, MDX and XMLA requests, processing information broken down by steps, capacity planning and forecasting, and more…

Not All Hyperlinks are Born Equal

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

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

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.