Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies, April 2009

/1 Comment/in /by

As I mentioned in a previous blog, one very important SSRS enhancement in the recently released SQL Server 2008 SP1 is Report Builder 2.0 ClickOnce. This lets you reconfigure the native and SharePoint integration modes to let the user use Report Builder 2.0 instead of Report Builder 1.0.

Note that in SharePoint integration mode, installing SP1 only is not enough. That’s because there are actually two instances of RB2.0 ClickOnce. If you install SQL Server 2008 SP1, this will provide an update for native mode only. For SharePoint integration mode, you must refresh the Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies. This is necessary because the ClickOnce bits must reside on the SharePoint server. The April 2009 version of the add-in can be downloaded from here.

SSAS Import Wizard to the Rescue

/0 Comments/in /by

Sometimes, it’s better just to reset. I came across an issue concerning processing a partition whose containing measure group joins a dimension via another reference dimension. Specifically, when I attempt to process the partition, the server would error out with:

The binding for the ‘dimension key’ column is not a ColumnBinding type

What made this issue interesting is that I was able to repro the issue each time I would process the partition but the Microsoft support engineer, who was assigned to this issue, hasn’t been able to. After some digging we realized that the issue only happens in project mode, that is, when working with the project source in BIDS and deploying to the server.

How do we fix this horrible issue? We reverse-engineer the deployed cube using the SSAS Import Wizard to regenerate the source code.

  1. In BIDS, click File->New->Project.
  2. In the New Project dialog, select Import Analysis Services 2008 (or 2005 depending on the version you have) Database.
  3. Specify the location of the deployed database. BIDS will reverse-engineer the database to its source code.

Magically, after this procedure the error went away although we are still working on finding out what caused the error with the original code.

Excel PivotTable Tabular Reports

/0 Comments/in /by

Here is a great tip thanks to Greg Galloway who pointed me to the Steve Novoselac’s excellent blog. By default, Excel 2007 PivotTable stacks (hierachizes) the attributes on the report even if they are from the same dimension. For example, let’s say the user wants to slice by the product hierarchy but want also some additional product attributes, such as the product color, size, etc. By default, Excel will produce the following layout:

033109_2111_ExcelPivotT1

Needless to say, this is hardly what the users want to see as they would prefer the product attributes to be displayed in a tabular format. Luckily, this is easy albeit not very intuitive.

  1. Before you drop the first field on PivotTable, click the Design main menu on the ribbon (the one next to Options menu).

033109_2111_ExcelPivotT2

  1. Expand the Report Layout drop-down menu and select Show in Tabular Form. Again, make sure that that PivotTable is empty before you do this.
  2. If you want to remove subtotals, expand the Subtotals drop-down menu and select Do Not Show Subtotals.
  3. Finally, to remove the +/- indicators, right-click on PviotTable and select PivotTable Options. On the Display tab, uncheck the Show Expand/Collapse Buttons.

Then, you can proceed to laying out the report as usual. Now you have a cool-looking tabular report.

033109_2111_ExcelPivotT3

Report Builder – Past, Now and Future

/0 Comments/in /by

The Report Builder technology made its debut in SQL Server 2005. It targeted business users who wanted to author their own (ad-hoc) reports but they were not necessarily technically savvy, meaning that no technical knowledge about database schemas or query syntax was assumed. The Report Builder technology includes a Report Builder Model and Report Builder Client. Similar to other ad-hoc reporting tools, such as Impromptu, the solution developer was required to implement a Report Builder model that is layered on top of the database and deploy it to the server. This model would receive the reports authored by the end users, which were described in an abstract language (SMDL), and automatically translate them to native queries for the supported data sources (SQL Server, Oracle, and Analysis Services).The initial vision, which didn’t materialize, was to make the query generator open, so developers can plug in custom query generators for other data sources. The Report Builder Client is a ClickOnce desktop report designer which was capable of producing very simple template-based reports.

As Brian Welcker, a former Group Program Manager on the SSRS team, explained in his blog, the Report Builder technology itself enjoyed good acceptance but it wasn’t perfect. To start with, many business users found the Report Builder Client too limiting. Targeting Analysis Services as a data source was problematic to say the least as essentially you had to “wrap” the cube with the Report Builder model layer while a direct access to the cube was preferred. As a result, SQL Server 2008 brought in Report Builder 2.0 which was targeted to supersede the Report Builder Client. As I explained, besides its name Report Builder 2.0 has very little to do with its predecessor as it’s much closer to the BIDS Report Designer than Report Builder 1.0. It provides a full RDL support and shares the same design layout with the BIDS Report Designer but runs outside Visual Studio as a desktop application (ClickOnce-enabled in SQL Server 2008 Service Pack 1).

So, does this mean that the Report Builder technology itself is outdated moving to SQL Server 2008? Absolutely not. If you are looking for an ad-hoc solution that targets small to medium-size SQL Server and Oracle databases, you should definitely evaluate Report Builder. In fact, future releases are expected to bring exciting changes to the Report Builder model which I cannot discuss as they are under NDA. On the report authoring side, business users should evaluate both Report Builder 1.0 (still available in SQL Server 2008) and Report Builder 2.0. Based on my experience, even non-technical users, such as these who struggle with copy and paste, would prefer Report Builder 2.0, so I’d definitely encourage my users to switch to it.

That said, remember that Report Builder is not the only ad-hoc technology that Microsoft provides. As many of you know, I am a big fun of Analysis Services for OLAP and ad-hoc reporting. If you are willing to invest time to learn Analysis Services and MDX (and you should), another approach is to implement a cube instead of a proprietary Report Builder model. If the user needs traditional (banded) reports, they can use the Report Builder 2.0 MDX Query Designer to author such reports from the cube. Of course, one of the advantages of this approach is that Analysis Services enjoys broader client support so you are not limited to Report Builder 2.0 only as a reporting tool. For example, business users can use Excel, ProClarity, and third-party tools. So, choosing a Report Builder model or SSAS is an important decision that requires carefully evaluating your reporting requirements, implementation effort, and skill set.

What may not be so obvious is that if you decide to use a Report Builder model, you can have the best of both worlds: Report Builder model on the server that abstract the database and Report Builder 2.0 client that supports all report authoring features. Unlike the BIDS Report Designer, with Report Builder 2.0 the user doesn’t have to (in fact the user can’t) create a data source that points to the model. Instead, the user wold point to a server Report Builder model when defining the report data source:

  1. In the Report Data pane, click New –> Data Source. Notice that the Data Source Properties dialog says “Use a shared connection or report model”.
  2. Browse to the Report Builder model on the server and click OK.

Once in the query designer, notice that it shows the same Report Builder metadata as in Report Builder 1.0. You have the same entity navigation that guides the user to the entities that are related to the selected entity. From here, authoring a report is a matter of dragging and dropping entities and fields. One thing that is not working in Report Builder 2.0 though, which is likely to be fixed in a future release, is filter prompts. Report Builder Client 1.0 would automatically populate the parameter available values if you select a filter as a prompt. Report Builder 2.0 would generate a report parameter but you have manually set up a dataset for the available values and bind it to the parameter.

Troubleshooting the SharePoint Add-in for Reporting Services Part 2

/0 Comments/in /by

Today, a co-worker declared a victory after struggling a few days to get Reporting Services 2008 SharePoint integration mode going on Windows Server 2008 (aka Longhorn). I helped of course J

  1. It all started with access denied error when attempting to deploy reports to SharePoint. Navigating to the WSS Central Admin Operations page would reveal however that the Reporting Services section is missing. After n-installs of the RS add-in we realized that something is not right.
    Take-home note: If the Reporting Services section is missing, the Reporting Services add-in failed to install irrespective of the fact that it didn’t report any errors. Bummer No 1.
    Wish No 1: The RS add-in should report errors to users ideally with recommendations.
  2. I asked him to troubleshoot the RS add-in installation following the steps in this blog.

    The most important line from the add-in log file was:

**********  User does not have permissions to add feature to site collection ********

So, the add-in installed the Report Server Integration Feature but it couldn’t activate it although my co-worker used the same account (his Windows account) to install the add-in and he had WSS Farms Administrators rights? Bummer No 2. Welcome to the SharePoint candy land where things are not what they appear to be.
Wish No 2: The farm administrator should have the right permissions to activate features or if he doesn’t have them for whatever reasons, the add-in should report this beforehand.

  1. Activate the feature manually:
    1. Go to SharePoint 3.0 Central Administration.
    2. Select Site Actions -> Site Settings
    3. Under the Site Collection Administration section click Site Collection Features
    4. Activate the Report Server Integration Feature in the list
  2. Run the RS add-in again. This time, no error messages in the add-in log file.

    Success!

    Disclaimer: The issues described above might be Windows Server 2008-specific (we have a tighter security model now, right) or at least I don’t recall having them on Windows Server 2003.

 

Measures on Rows

/0 Comments/in /by

Issue: You want an MDX query that returns measures on rows and calculated members on columns. You get The Measures hierarchy already appears in the Axis0 axis error. That’s because calculated members added to measures are treated as measures and you cannot have the same dimension (Measures in this case) on different axes.

Solution: Let’s point out that the above-mentioned error doesn’t occur if you request dimensions on columns. For example, the following is a perfectly legit query:

select {[Date].[Calendar].[Calendar Year].&[2002],

[Date].[Calendar].[Calendar Year].&[2003]} on 0,

{[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount],

[Measures].[Sales Amount]

} on 1

from [Adventure Works]

031409_0126_MeasuresonR1

In addition, you may be able to use the capabilities of the tool to rotate dimensions from columns to rows, such as using a crosstab report layout in Reporting Services. Interestingly, PivotTable in Excel 2007 lets you add measures on rows although behind the scenes Excel requests measures on columns but does an internal rotation to move the measure columns on rows.

However, sometimes you may need asymmetric columns that filter the results. This may force you to use calculated members on columns and measures on rows. Since you cannot put the calculated members on Measures, consider adding them to a dimension. The following query adds Column1 and Column2 calculated members to the Product.Category attribute. Notice that the first column filters the bikes sales for USA only.

with member [Product].[Category].Column1 as ([Product].[Category].[Bikes], [Date].[Calendar].[Calendar Year].&[2002], [Geography].[Country].&[United States])

member [Product].[Category].Column2 as ([Product].[Category].[Bikes], [Date].[Calendar].[Calendar Year].&[2003])

select {[Product].[Category].Column1, [Product].[Category].Column2 } on 0,

{[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount],

[Measures].[Sales Amount]

} on 1

from [Adventure Works]

Of course, you can use any tuple or aggregation function for the member definition. BTW, you can cheat SSRS to bypass the rule that insists that measures must be on columns and the entire validation and preparation goodness by manually entering the query in the report definition and defining the dataset fields and mappings.

031409_0126_MeasuresonR2

Invisible Cubes

/2 Comments/in /by

I had a head-scratcher the other day. All of a sudden, SQL Server Management Studio stopped showing the cubes in the dropdown of the MDX Query tab although MDX queries and reports would execute just fine. The Reporting Services MDX Query Designer would complain with “No cubes found” error when I attempted to launch it from the dataset properties. This strange behavior coincided with installing SQL Server 2008 SP1 CTP, so I was quick to find a culprit. My suspicion was that the SSAS Windows authentication was failing was some reason although the SQL Server Profiler would show that the right Windows identity was connecting to the database.

To troubleshoot this further, I issued the following schema rowset query when connected to the SSAS database in SSMS:

select * from $system.mdschema_cubes

Interestingly, the query would return a list of the database dimensions (SSAS treats dimensions as cubes and prefix the dimension name with $) but it wouldn’t return the cube name itself. Then, I had a Eureka moment. I went to the Cube Structure tab in the BIDS Cube Designer, and I realized that that somehow I managed to set the cube Visible property to False. Not sure why you would ever want to make the cube invisible and why this property is there to start with but there it was. Flipping it back to True and redeploying the cube fixed the mysterious No Cubes Found error.

Speaking at TechEd 2009 USA

/0 Comments/in /by

One of my TechEd 2009 session proposals got approved. The session is tentatively named Reporting Services 2008 Tips and Tricks, How-to, and Beyond. I am planning to cover solutions to common questions and challenges that span the three phases of the reporting lifecycle: authoring, management and delivery. I hope you can attend my session If you planning to attend TechEd 2009 USA. Shoot me a note if you want me to cover something in particular. See you in LA!

Hacking MDX Query Designer Parameters

/2 Comments/in /by

[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/simpleparameters.zip]Continuing our intrepid journey in the land of SSRS-SSAS integration, after we’ve figured out how to retrieve the cube metadata, we are ready to tackle another unchartered territory: query parameters.

Issue: You need to parameterize the MDX query by a measure or a simple parameter. Take for example the following report (attached).

022609_0224_HackingMDXQ1

This report shows the Internet Sales Amount measure broken down by the Employees parent-child hierarchy. What’s more interesting is the Show Descendants parameter. It lets you filter the report for a single employee (Show Descendants = False) or the descendants of the selected employee including the selected employee (Show Descendants = True). So, what’s the issue? You can whip out such a report in no time if you source data from a relational database. However, the MDX Query Designer insists that the parameter is resolved to a single dimension member or a set of dimension members. Apparently, simple parameters or parameterizing by measures were considered uncommon scenarios. So, we need a sleek hack.

Solution: Luckily, there is a way to gain more control over the MDX query parameters but the design mode (the one that lets you drag and drop stuff) won’t cut it. So, it may make sense to author the report query in design mode as much as you can before you jump into the cold waters of MDX query mode because there is no going back to drag and drop, sorry.

  1. To start with, you need to define a query parameter for the ShowDescendants parameter. Switch to MDX mode and click the Query Parameters toolbar button (enabled only in MDX mode).
  2. Add a new ShowDescendants “hanging” parameter that doesn’t reference any dimension. Make sure that the query parameter name matches the report parameter name. Otherwise, the MDX Query Designer will treat the parameter as a new parameter and it won’t associate it to the existing ShowDescendants report parameter. Note that the parameter name is case-sensitive.

022609_0224_HackingMDXQ2

  1. Leave the Dimension and Hierarchy columns empty. Luckily, they are not required in MDX mode. Either type in a measure name, such as [Measures].[Sales Summary], or enter DEFAULT in the Default column. In the latter case, the default cube measure will be used when you test the query in the MDX Query Designer. See this blog by Chris Webb for ideas about how to speed up the default measure if needed. Click OK.
  2. Back to the query, change it as follows:

SELECT NON EMPTY { [Measures].[Reseller Tax Amount] } ON COLUMNS, NON EMPTY

IIF(@ShowDescendants=False, StrToMember(@EmployeeEmployees), DESCENDANTS(StrToMember(@EmployeeEmployees)))

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS 

These changes are specific to the report requirements. In our case, the IIF expression checks the value of the ShowDescendants parameter. If the user has selected the False option, the query brings only the selected employee on rows. Otherwise, we use the Descendants function to bring the descendants of the selected employee.

Looks hacky? Join me and make a wish on connect.microsoft.com to improve the SSRS-SSAS integration. Don’t be surprised if you are not active that the MDX Query Designer will continue to sneak from one version to another unchanged.

Report Builder 2.0 ClickOnce

/2 Comments/in /by

One important change that Service Pack 1 for SQL Server 2008 (public CTP available here) will bring is ClickOnce deployment of Report Builder 2.0. By default, however, Report Manager or SharePoint (if you have SSRS installed in SharePoint integration mode) still point to Report Builder 1.0. Here is how to reconfigure SSRS to launch Report Builder 2.0 instead. The following procedure assumes SharePoint integration mode but the steps are similar if SSRS is running in native mode.

CAUTION   Steps 1 and 2 below to copy folders are for the CTP bits of SQL Server 2008 SP1 only because the CTP build doesn’t support RB 2.0 ClickOnce in SharePoint integration mode. SQL Server 2008 SP1 RTM will include an installer to make the appropriate folder changes automatically. Therefore, DO NOT manually copy the folder content with the final bits or undo your changes before you install SP1 RTM so the installer doesn’t fail.

  1. SharePoint Integration Mode Only. Back up the content of the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
  2. SharePoint Integration Mode Only. Copy all files from the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder folder to the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
  3. SharePoint Integration Mode Only. If you install SQL Server 2008 SP1 this will provide an update for native mode only.  In order to get Report Builder ClickOnce for SharePoint integration mode working, you need to download the RB 2.0 ClickOnce update for the RS SharePoint add-in from the feature pack page.  This is necessary because the ClickOnce bits must reside on the SharePoint server. As of the time of this writing, the latest add-in is April 2009 which you can download from here. Install rsSharePoint.msi (or rsSharePoint_x64.msi for x64) to update the SharePoint add-in. Then install RB2ClickOnce.msi which copies the Report Builder 2.0 ClickOnce files to the SharePoint site.Note (7/15/09): There is a known issue that RB2ClickOnce.msi fails to install on x64 environment. Microsoft should fix this in Cummulative Update 3 for SQL Server 2008 Service Pack 1. Meanwhile, if you have a report server running in SharePoint integration mode on x64, manually copy the Report Builder 2.0 files by following steps 1 and 2.
  4. SharePoint Integration Mode Only. Open WSS Central Administration. Click the Application Management tab. Click the Set Server Defaults link found under the Reporting Services section.

022509_0300_rbclickonce20

5. Enter the URL address to the ReportBuilder_2._0_0_0.application file. In SharePoint mode, assuming you followed steps 1-2, the URL address will be:
/_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application

For a report server configured in native mode, go to Report Manager, click the Site Settings menu link and enter the following to the Custom Report Builder launch URL setting:

/ReportBuilder/ReportBuilder_2_0_0_0.application

At this point, SSRS is reconfigured to launch Report Builder 2.0 instead of Report Builder 1.0. From the end user perspective, the user would launch RB 2.0 just like they used to launch RB1.0, that is, click New->Report Builder Report in SharePoint or click the Report Builder button in Report Manager. The first time the user does this, the Report Builder 2.0 bits (almost 50 MB) will get downloaded and installed in the ClickOnce local application cache, which is a hidden directory under Local Settings for the current user. The ClickOnce application cache will not be cleared when the user clears the browser cache. Subsequent requests will use the local copy.

UPDATE Now that SQL Server 2008 SP1 is released, it includes a readme file with steps how to configure and troubleshoot Report Builder 2.0 ClickOnce in SharePoint integration mode.