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!