• Overwriting Parameter Defaults

    April 14, 2009 / No Comments »

    Issue: An interesting question popped today on the discussion list about overwriting the default of a cascading parameter. In this particular case, the report has two parameters: Frequency and HireDate. The HireDate parameter is dependent on the Frequency parameter. Specifically, when the user changes the Frequency parameter, custom code changes the default of the HireDate parameter. Sounds easy, right? Well, not so fast. This requirement calls for smashing the HireDate default. However, smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing. There exist uncommon cases such as this one where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, Reporting Services doesn't not currently have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result,...

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

    April 13, 2009 / 1 Comment »

    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

    April 2, 2009 / No Comments »

    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. In BIDS, click File->New->Project. In the New Project dialog, select Import Analysis...

  • Excel PivotTable Tabular Reports

    March 31, 2009 / No Comments »

    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: 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. Before you drop the first field on PivotTable, click the Design main menu on the ribbon (the one next to Options menu). Expand the Report Layout drop-down menu and select Show in Tabular Form. Again, make sure that that PivotTable is empty before...

  • Report Builder – Past, Now and Future

    March 31, 2009 / No Comments »

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

  • Troubleshooting the SharePoint Add-in for Reporting Services Part 2

    March 30, 2009 / No Comments »

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

  • Measures on Rows

    March 14, 2009 / No Comments »

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

  • Invisible Cubes

    March 7, 2009 / 2 Comments »

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

  • Speaking at TechEd 2009 USA

    March 2, 2009 / No Comments »

    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

    February 26, 2009 / 2 Comments »

    [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). 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...

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication