Extending Report Builder 2.0

/1 Comment/in /by

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

/0 Comments/in /by

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 THE Book?

/0 Comments/in /by

A few readers are asking about the whereabouts of my book Microsoft SQL Server 2008 Reporting Services, so I felt I need to write a short status report. The book is printed and it’s on stock with the distributor. It’s making its way slowly-y-y to the retail network. It turns out there are lots of things that need to happen before the book is finally listed in-stock with retailers, such as Amazon. I am frustrated by this fact but there is nothing I can do to speed up the process. Hopefully, the book will pop within a few days so you can get it at a discounted price.

Anyhow, if you need the paper copy as soon as possible, you can order it from the distributor at the full price and you will get it within 2-3 days. Alternatively, you can order the e-book version, which is already available retail, such as on ebooks.com, diesel-ebooks.com, etc.

Finally, don’t forget that two chapters are freely available on the book page, plus video demos, to get you started with RS 2008.

Nasty SSAS 2008 Bug

/0 Comments/in /by

There is a nasty known bug with SSAS 2008, where if a query that requests two measures with Sum and LastNonEmpty aggregate functions side by side and the query uses NON EMPTY (which all OLAP clients use by default), the server forces the measure with the SUM function to display empty.

Steps to repro:

Use the Analysis Services Tutorial cube (Lesson 10) which is included in the SSAS samples. Don’t use the Adventure Works cube because it has measure expressions for certain measures, so changing a measure aggregation function to LastNonEmpty and deploying the cube results gives the error “Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of ‘Internet Sales’, cannot be verified against the source object.” That’s a different issue that has been around since SQL Server 2005 but apparently didn’t get fixed.

1.    Open the Analysis Services Tutorial project from Lesson 10

2.    Open the Analysis Services Tutorial.cube in the Cube Designer.

3.    In the Cube Structure tab, expand the Internet Sales measure group and select the Internet Sales-Order Quantity measure.

4.    In the Properties pane, change the AggregateFunction to LastNonEmpty.

5.    Deploy the cube.

6.    Browse the cube with the cube browser or Excel. Create a report that requests Internet Sales Amount and Internet Sales-Order Quantity measures sliced by the Subcategory attribute of the Product dimension.

Notice that the query forces Internet Sales Amount to empty. While waiting for an official hotfix from Microsoft, you can use one of the following workarounds meanwhile:

1. Don’t use the NON EMPTY clause in the query

or

2. Change the following property in the \Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini file.

<DisableCalcExpressNonEmpty>1</DisableCalcExpressNonEmpty>

Not sure what DisableCalcExpressNonEmpty does as it is not documented but appears to work.

UPDATE 8/18/2008

DisableCalcExpressNonEmpty is a new property in AS 2008. Severe performance degradation is likely to result when set to 1, but it should be comparable to AS 2005 performance if not a little better. A hotfix better come out quickly [:(]

Where is Report Builder 2.0?

/2 Comments/in /by

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.

Static Credentials

/0 Comments/in /by

A new week comes with new issues… I ran into an interesting issue today with Windows integrated security and SharePoint. I was troubleshooting an issue on a behalf of a Windows user that connects to an SSAS server. To repro the issue, I used the SharePoint “Sign in as a different user” feature. To facilitate testing, I decided to save the password in the standard Windows authentication dialog that follows (“Remember my password” checkbox).

After this “convenient” setup, to my surprise all calls to that server went the credentials of that user, including connections to the cube from SQL Server Management Studio and Excel! For example, when I connected to the SSAS database with SSMS and attempted to manage the server, I was greeted with the following message although I have admin rights to the server:

The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties. (Microsoft.AnalysisServices.ManagementDialogs)

The SQL Server Profiler revealed that indeed the server impersonates any call under that user. Not sure what exactly happens when you save the password but be careful of this issue with “static” credentials. To correct the issue, use the Sign in as a different user feature again but don’t check the Remember my password option.

Microsoft SQL Server 2008 Feature Pack, August 2008

/0 Comments/in /by

Microsoft released a Microsoft SQL Server 2008 Feature Pack, August 2008 page which includes a collection of stand-alone install packages that provide additional value for SQL Server 2008. Strangely, Report Builder 2.0 is excluded and nowhere to be found since the RC0 page has been taken offline.

Update 8/7/2008:

A RC for Report Builder 2.0 feature pack will be available for download toward the end of August

Microsoft Has Released SQL Server 2008

/0 Comments/in /by

Today, Microsoft announced that official release of SQL Server 2008. Build #10.0.1600.22 has been declared as a SQL Server RTM.

SQL Server 2008 includes major enhancements to the Microsoft Business Intelligence Stack, some of which I have mentioned in my previous posts.

Note that if you have Visual Studio 2008 on your machine, you must wait for a few more days to get the official release of Visual Studio 2008 SP1, which is a required prerequisite to integrate the SQL Server 2008 client tools with Visual Studio 2008.

Enjoy!

Rules Have Changed

/0 Comments/in /by

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

/0 Comments/in /by

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.