The Many-to-many Revolution

/0 Comments/in /by

One of the major UDM enhancements that transcends the boundaries of traditional OLAP is flexible dimension relationships, including many-to-many, referenced, and fact relationships. Marco Russo, who helped me tremendously with my Analysis Services book, ha s just published a very comprehensive whitepaper (shall we call it a mini book?) about many-to-many dimension relationships. I had the pleasure to be one of the reviewers.

For those who are not familiar with his work, Marco is one of the few people on this planet that has deep understanding and real world experience with Analysis Services since its early stages. Thus, this whitepaper is a valuable resource that discusses practical implications of the UDM many-to-many relationships. The real-life scenarios presented in the whitepaper unlocks the mysteries of this revolutionary OLAP concept.

Don't forget to check Marco's blog and his SQLBI.EU website for more UDM insights. Great work, Marco, and looking forward to a book from you! [:O]

Long Lines

/0 Comments/in /by

You will probably never run into this issue unless you have very wide tables but here it is. If use the RS table region and the table has many columns and groups, the number of textboxes in the table cell can increase dramatically and exceed the maximum limit. This situation manifests itself with a rather cryptic error message upon uploading the report definition:

[BC30494] Line is too long

What's going on? SSRS compiles the report definition to prepare the report intermediate language which gets saved in the report catalog. If the N*M (where N is the number of textboxes in a table and M is the size of the textbox name) exceed 64K, the VB.NET compiler throws this error because a VB.NET source file cannot exceed 64K. The workaround is trivial:

  1. Reduce the number of textboxes in a table by removing columns and/or groups.
  2. Shorten the textbox names, e.g. instead of textbox1 rename to t1.

No one likes long lines…

Ampersands Gone Wild

/0 Comments/in /by

Thanks for the Geoff’s feedback on the discussion list, today I was able to demystify one of the SSRS-SSAS integration “gotchas” that has been pestering me for quite some time.

Sometimes a report may need the Jump to URL navigational feature to open a parameterized OLAP report. Since the UDM member unique names contain & (to designate the key), I have been unable to find a way to construct a Javascript link that correctly escapes & in the report parameters, e.g.:

="javascript:void(window.open('http://localhost/ReportServer?/OLAP/Daily Product Sales&DateTimeIndex=[Date].[Time Index].&[2003]&SalesTerritoryGroup=[Sales Territory].[Group].&[North America]&rs:Command=Render’))"

Here, the Daily Product Sales report takes two parameters (DateTimeIndex and SalesTerritoryGroup). As I mentioned in Chapter 8 of my book, even if you use the escape code of ampersand %26 (or the escape Javascript function), the browser will “helpfully” unescape the value back to & and the Report Server will choke. The trick is to use %2526 instead of just %26, as the next example shows:

="javascript:void(window.open('http://localhost/ReportServer?/OLAP/Daily Product Sales&DateTimeIndex=[Date].[Time Index].&2526[2003]&SalesTerritoryGroup=[Sales Territory].[Group].&2526[North America]&rs:Command=Render’))"

%2526 is needed in a javascript call with Jump to URL because it gets processed by the browser twice. %2526 goes to %26 which goes to &.

Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services

/0 Comments/in /by

As I mentioned in a previous post, one of the most exciting (but not well known and documented) extensibility areas of SSRS 2005 is custom report items (CRI). Read my latest article "Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services reports" published in the October issue of MSDN Magazine to learn how to implement a progress bar CRI.

Cizer.Net Reporting 4.0 Released

/0 Comments/in /by

Cizer Software today announced the release to market of Cizer.Net Reporting 4.0 – a web-based report designer for Microsoft Reporting Services. According to the company "both Standard and Enterprise editions enjoy more functionality than ever before within the Cizer Quick Query module to allow enhanced formatting, with the addition of charting and logos, as well as the addition of Summary Reporting – all via point-and-click design with no knowledge of SQL required."

BI Market Share Report

/0 Comments/in /by

The latest research by IDC shows Microsoft is ahead of the pack in terms of growth in the business intelligence (BI) tools market. Microsoft ranked fourth in the overall market share and had the highest growth (25%) for the period 2004-2005. The release of SQL Server 2005 and the acquisition of ProClarity (evaluated separately on the report) will most likely gain additional momentum.

Dundas Map for Reporting Services

/0 Comments/in /by

Tuning again on the Dundas wave, the same company announced the pre-release availability of the Dundas Map for Reporting Services 2005. Designed as a custom report item (new extensibility feature with SSRS 2000) the Dundas Map allows report authors to present geographic data graphically in reports. The Dundas Map for Reporting Services with be included free in the Dundas Reporting Services Dashboard Bundle alongside chart, gauge, and calendar controls.

Dundas Chart for OLAP Services

/0 Comments/in /by

If you are on a lookout for a web-based smart chart that can browse SSAS 2005 cubes, don’t look further. Enter Dundas Chart for OLAP Services! Despite the name (what’s Dundas anyway?), I really fell in love with this control after playing it with its demos for a while. The chart can connect to both server and local cubes. The attached image shows the Dundas Chart connected to the Adventure Works DW cube.

The beauty of the Dundas Chart is that it’s more than a chart. It is a web-based OLAP browser. And it’s AJAX-enabled so the page doesn’t re-post as result of user actions! From an end-user perspective, authoring a chart is a matter of dragging and dropping dimensions and measures. The same experience as creating an OLAP-based pivot or chart report in Excel.

Given the void left by OWC and the lack of Microsoft OLAP browser controls, the Dundas Chart for OLAP Services is definitely something to consider when planning for multi-dimensional web reports.

Timeout Quest

/0 Comments/in /by

I was on an interesting quest today. I had to troubleshoot why some long-running SSRS reports time out after a fixed period of time. An SSRS report can time out for at least three different reasons. First, the dataset query could have been set to time out (see DataSet Query tab). The default query timeout is 30 seconds. You can set the query timeout to 0 to prevent the query from timing out.

Second, the entire report may be set to time out . By default (see the Site Settings page in the Report Manager), all reports are set to time out after 1,800 seconds (30 minutes) but the report timeout can be overridden at the report level by using the report execution properties. When the report timeout is up, the Report Server simply terminates the report.

Finally, if your application calls down to the RS Web service to run a report (the Render API), the web service call may timeout. That’s because in .NET, the web service proxy has a Timeout property.

As usual, as one of the first step of troubleshoot an SSRS issue, I fired up the SysInternals DebugView tracer to watch the output from the Report Server. It turned out that the reports would time out after exactly three minutes. When this happened, the Report Server would gently complain that the ASP.NET thread had terminated without throwing an exception. This led me to believe that this issue was outside the Report Server. After further digging, it turned out that the proxy Timeout was set to 180 seconds. Setting it to -1 solved the problem. Keep in mind though that the IIS could also terminate a long-running report, e.g. if the IIS 6.0 application pool is set to terminate long-running threads.

Installing Reporting Services 2005 on Named Instance

/0 Comments/in /by

Here are the steps I’ve recently followed to install successfully SRRS 2005 on a named instance.




  1. Follow the steps in the “To Install Reporting Services Side-by-Side With an Earlier Version” section in BOL.


  2. Navigate to http://localhost/reportserver/. If you get access denied error to rsreportserver.config (or another folder/fiile), add the SSRS Windows Services account (e.g. NT AUTHORITY\SYSTEM) and the Web service (ASPNET) to the SQLServer2005ReportServerUser$<machine>$<instance> and  SQLServer2005ReportingServicesWebServiceUser$<machine>$<instance> Windows groups respectively.


  3. Try to open Report Manager. If you get an error that the Report Server cannot open a connection to the database, go back to the Reporting Services Configuration tool.



  • Switch to the Database Setup tab.


  • Select the Server Name and Database Name (ReportServer) and click the Script button.


  • In the Create Scripts dialog, select the Grant Rights Script option.
    Type in the Web service account (e.g. ASPNET) in the User Name field and select the Windows User Account checkbox. Click OK to generate the script.


  • Double-click the script file to open it in the SQL Server Management Studio and execute it. This should grant the necessary permissions to the Web service to connect.

Strangely, on a Windows 2000 box, the Reporting Configuration Tool was showing the following error:


ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070005   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)


I wasn’t able to figure out how to fix the above error message but it didn’t seem to affect the Report Server.