Posts

Referencing Parameters in SSRS Calculated Members

Issue: You author an SSRS report that connects to a cube. You want to create a calculated member that uses a report parameter. For example, the Selector calculated member in the following query returns 1 if a given employee has exceeded 1 million in sales for the dates specified by the user in the report parameter.

WITH MEMBER [Measures].[Selector] AS SUM (

StrToSet(@DateCalendar), iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)

)

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,

non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1

from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])

 

However, when you run the query you get the following error if you target Analysis Services 2005:

The DateCalendar parameter cannot be resolved because it was referenced in an inner subexpression.

Obviously, the built-in SSAS data provider has an issue parsing parameters in a calculated member. This issue is apparently fixed in SQL Server 2008 where the above query executes just fine.

Workaround: As a workaround with SQL Server 2005, try defining a set that references the parameter and use the set in the calculated members, as follows:

WITH SET [SchoolYears] AS STRTOSET(@DateCalendar)

MEMBER [Measures].[Selector] AS SUM (

[SchoolYears], iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)

)

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,

non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1

from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])

Report Builder 2.0 RTM’ed

Today, Microsoft released Report Builder 2.0 (build 10.00.1600.60) , as Robert Bruckner announced in his blog. This is a major milestone in the Reporting Services roadmap. In my opinion, Report Builder 2.0 is one of the most important and exciting features of Reporting Services 2008 as it bridges the gap between standard and end-user (ad hoc) reporting. You should definitely consider Report Builder and Analysis Services for your ad-hoc reporting projects.

Extending Report Builder 2.0

As I mentioned in my previous blog, Report Builder 2.0 RC1 has arrived. Although perhaps too early in the game, you may wonder how you can get the most out of Report Builder 2.0 and extend it when the built-in features are not enough. As Report Designer and Report Builder 2.0 share the same design surface, you may believe that they are equally extensible. Unfortunately, this is not quite the case. Specifically, due to time constraints, Report Builder 2.0 supports the following RS extensibility features only in server mode:

  • Custom Data Processing Extensions
  • Custom Security
  • Custom Report Items

Server mode is triggered when you open a report from the server or when the report references a shared data source whose definition must reside on the server. When you preview a report in server mode, the report is processed and rendered on the server. The above extensibility areas are not available in client mode. For example, the user can open a server report that has a custom report item, sets its properties (assuming that the right configuration settings have been made to the Report Builder 2.0 configuration files), and preview the report, as shown in the screenshot. However, the Report Builder 2.0 ribbon will not be extended with the custom report item. Nor the user will be able to author a report from scratch and add the custom report item to the report.

083108_1819_ExtendingRe1

The unfortunately side effect is that if the business user needs to use a CRI on a report, such as the Dundas Map CRI, a developer must author a “shim” report with Report Designer, add the CRI to the report, and deploy the report to the server so the business user can use the report as a starting point.

The only extensibility mechanism that seems to be working in client mode in RC1 is custom code (embedded and external). More than likely, Report Builder 2.0 will get the entire extensibility support of Reporting Services at some point of time. If you want to expedite the process, log your wish on connect.microsoft.com and vote for extensibility features you need Report Builder 2.0 to provide.

Report Builder 2.0 RC1 is Here

Thanks to Robert Bruckner’s blog, I’ve learned that the Report Builder 2.0 (previously known as Report Designer Preview) Release Candidate 1 is available for download. This should be a feature-complete build of Report Builder 2.0, which is scheduled for official release in October.

IMO, Report Builder 2.0 is one of most exciting features of Reporting Services. I expect it to blur the “great divide” between standard and ad-hoc reporting. Based on some preliminary feedback from our business customers, they are very excited about Report Builder 2.0 and they favor it instead of Report Builder 1.0. Despite the unfortunate name, Report Builder 2.0 has very little to do with its predecessor. Instead, it’s very close (almost identical) to the BIDS Report Designer as it supports the full RDL feature set.

You should definately evaluate Report Builder 2.0 for ad hoc business reporting.

Where is Report Builder 2.0?

With the official release of SQL Server 2008, Microsoft retired the RC0 Feature Pack and added the SQL Server 2008 Feature Pack, August 2008 page. Unfortunately, the Report Builder 2.0 link got dropped along the way. Microsoft is aware of this issue and is planning to add a release candidate page for Report Builder 2.0 feature pack available for download toward the end of August. The RTM version of Report Builder 2.0 is expected in Oct’2008 timeframe.

Rules Have Changed

An interesting question has pop up on the Katmai RS 2008 discussion list today about how to implement a “green bar” group-level report that alternates background color for all rows in a group instance instead of doing this for each row. I attach the report sample I posted. What makes it interesting is that it demonstrates report variables – a new feature in RS 2008.

Before you start analyzing it, you should know that expression evaluation rules in RS 2008 have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics. I know this may sound to you like Greek, so let me jump into the implementation details:

1. The report has a EvenRow code-behind function that toggles each time it’s executed.

2. In the Category group (double-click it to access its properties), an EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.

3. The rest is easy. I set the BackgroundColor property for each textbox to use this variable. BTW, the report uses the SSAS Adventure Works cube. [View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/greenbargroup.zip]

Yet Another Post About Analysis Services HTTP Connectivity

With risk to iterate the obvious, here is a lesson learned from the trenches:

Scenario: You want to let external users browse an Analysis Services cube over the Internet.

Setup: You have followed the steps in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 article by Edward Melomed to set up Analysis Services for HTTP connectivity.

Issue: Windows integrated security doesn’t work.

After a long battle where countless options have been tried, I have to admit a defeat. In our case, we’ve set up identical Windows local accounts on the SSAS server. This worked fine when connecting Report Builder 2.0 to the cube from our home machines. However, it appeared that the firewalls that our clients use make a minced meat of Windows integrated security. Strangely, Excel connects without a problem to the cube using Windows integrated security. However, Report Builder 2.0 chokes big time. I guess this has to do with differences between OLE DB (which Excel uses) and ADOMD.NET (used by Report Builder 2.0).

So, we had to give up on Windows integrated security over Internet. Instead, we went for Basic security with SSL, as follows:

  1. Configure the msmdpump virtual folder for Basic security (this should be the only available security option).
  2. Install the SSL certificate on the IIS server.
  3. In Report Builder 2.0, set up a new data source that uses the Microsoft SQL Server Analysis Services. Click the Edit button on the Data Source Properties dialog box.

080208_0230_YetAnotherP1

  1. In the Connection Properties dialog, enter the URL to the msmdpump.dll, such as https://adventure-works/olap/msmdpump.dll.
  2. Enter the user credentials. Click the Save My Password to avoid being asked to retype the password.
  3. Here is an important step if you want to populate the Connect to a Database drop-down list. Click the Advanced button and locate the Integrated Security setting. Select and clear the SSPI default setting. Click OK.
  4. Back to the Connection Properties dialog, expand the Connect to a Database drop-down list. You should see the list of the SSAS databases the user has rights to access. Select a database.
  5. Click the Test Connection dropdown. The connection should succeed.

The data source is set up now. You can proceed with setting up a dataset with the MDX Query Designer.

A Must-read Reporting Services Blog

Robert Bruckner on the Reporting Services team has started a blog. Robert oversees several key areas of Reporting Services, including Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability. Many of you have gotten help from Robert on the Reporting Services discussion lists to which he frequently contributes. So, this sure it’s going to be a must-read blog as his first posts (report variables and scalability) show.

RS Blog Roll

Bob Meyers’ Blog

(http://blogs.msdn.com/bobmeyers)—Bob Meyer is a Program Manager on the Reporting Services team who is responsible for Report Builder 1.0.

Brian Welcker’s Blog

(http://blogs.msdn.com/bwelcker)—Brian Welcker is a former Group Program Manager on the Reporting Services team.

Chris Baldwin’s Blog

(http://blogs.msdn.com/chrisbal)—Chris Baldwin is a Program Manager on the Reporting Services team who oversees the report rendering area.

Chris Hays’s

(http://blogs.msdn.com/chrishays)—Chris Hays is an architect on the Reporting Services team and oversees the Report Definition Language.

John Gallardo’s Blog

(http://blogs.msdn.com/jgalla)—John Gallardo is a Software Development Engineer on the Reporting Services team who is responsible for the report server.

Lukasz Pawlowski’s Blog

(http://blogs.msdn.com/lukaszp)—Lukasz Pawlowski is a Program Manager on the Reporting Services team who is primarily responsible for the management feature of the product.

Reporting Services Team Blog

(http://blogs.msdn.com/sqlrsteamblog/)—A collective blog of the Reporting Services team.

Reporting Services User Education Blog

(http://blogs.msdn.com/rosettaue)—From the user documentation team which is responsible for creating all the documentation that ships with the product.

Robert Bruckner’s Blog

(http://blogs.msdn.com/robertbruckner)— Robert Bruckner is a Software Development Engineer on the Reporting Services team who oversees Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability.

Russell Christopher’s Blog

(http://blogs.msdn.com/bimusings)—Russell Christopher is a Business Intelligence consultant with Microsoft who posts great insights about working with Reporting Services.


It’s Done

A quick update on my book Applied Microsoft SQL Server 2008 Reporting Services. I am happy to announce that the work on the manuscript is over and the manuscript RTM’d (released to manufacturing). The paper copy should be available on the reseller sites and brick and mortar stores around the publication date (August 15th). Expect the ebook version around that time as well. Of course, the good thing about the ebook version besides being searchable and portable is that it includes color images. As you would probably agree, color is a good thing when you are studying report design. Video demos are even better, of course, which brings me to the next topic.

While waiting, take a look at the book web page that just went live and check the available resources. Among other things, you’ll find two sample chapters (chapters 1 and 3), the book source code, and video demos, which I captured using the awesome TechSmith Camtasia. The videos are bonus material and are an experimental feature. Video demos are provided for a subset of the report authoring practices covered in the book. I am looking forward your feedback about this feature. Enjoy and I hope you don’t mind my thick as a brick accent J

Finally, the book web page includes a link to a discussion list, where you can make comments about the book and ask book-related questions. The new discussion list will replace my Reporting Services in Action discussion list, which will retire at the end of this month after four years of service. I hope you liked my participation and will consider “upgrading” to the new discussion list and book.

Automating Report Deployment

Here is an awesome tip courtesy to the Craig Guyer’s I Command Thee blog. You probably know that Visual Studio supports project configurations. In BIDS 2008, you can automate report deployment with configurations. For example, the following command deploys the solution using the settings of the QA configuration.

C:\>devenv “C:\Books\RS2008\Code\ch03\Reports\Reports.sln” /deploy QA

This is especially useful when automating deployment to SharePoint because you need to change the report definitions to use absolute paths to external resources, such as shared data sources. BIDS deployment can handle this for you and save you writing custom code to automate deployment.

While we are on the SharePoint deployment topic with BIDS, one nasty bug was introduced late in the SQL Server 2008 cycle that prevents you deploying folders that contain a space, such as Data Sources, to SharePoint from BIDS. When you attempt to do this, you get:

Error rsInvalidItemName : The name of the item ‘Data%20Sources’ is not valid. The name must be less than 128 characters long. The name must not start with slash; other restrictions apply.

If the folder already exists, deployment is successful. Unfortunately, due to time constraints, this bug won’t get fixed in the RTM timeframe. As a workaround, don’t use spaces in the target folders, e.g.; DataSources instead of Data Sources.