The Many-to-many Revolution

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

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

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

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

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."