Monday, October 17, 2011

SSRS: Retrieving Member Properties in Reporting Services (with Analysis Services)

In Reporting Services, it is unclear how, or if it is even possible to retrieve member properties when using an analysis services data source.

Member properties refer to the extra information attributed to members apart from their names. For instance, in the AdventureWorksDW, the Customer dimension contains several member properties including Address, Gender and Phone.

Screenshot 1 - Member Properties

Dragging these properties into the query window in Reporting Services unfortunately does not do the job. From what I've seen so far, it also does not seem possible from the query designer in design mode. However you can make a small change to the MDX code in order to retrieve these properties.

Change the design mode to see the MDX . Within the code, one line reads "DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME" as in the screenshot below.

Screenshot 2 - MDX Code to look for

Following that, add a comma and insert the syntax for the member property that you wish to retrieve. For instance, if I want to retrieve the Customer's Address, I would add [Customer].[Customer].[Address].

Screenshot 3 - Addition to the MDX Code

If you are in doubt about the syntax, look for the property in the left panel and hover your mouse over it. A tooltip will show you the syntax, but you need to modify it slightly.

Screenshot 4 - Tooltip showing Member Property Syntax


Remove the "Properties" and parenthesis, and replace the double quotes with square brackets. So [Customer].[Customer].Properties( "Address" ) would become [Customer].[Customer].[Address]

After successfully doing this and executing the query to test the result, you can freely use the property as you use any other field in the report (Fields!Address.Value).

Screenshot 5 - Result with Customer Address

Few Notes:

  • After doing this, you will be unable to use the graphical designer to edit the query as that will undo any changes you made to the MDX code. This could compromise the maintainability of the report especially if you are unfamiliar with MDX.
  • An alternative is to have some design changes to the cubes, such that all member properties are separate members themselves. This allows you to easily drag the fields into the query and it's not a bad cube design approach in itself.
Tags: reporting services analysis services mdx member properties dimension properties

Monday, July 25, 2011

MDX: Replacing Null / Empty Values

In the result of an MDX query, you may get some empty or null values which you'd want to treat differently, such as considering them as 0s. For instance in Reporting Services, you might want to filter out any rows with 0 or empty values.

In such cases, you can use the MDX function CoalesceEmpty, which replaces an empty cell value with a given value. For instance:

WITH MEMBER [Measures].[NonEmpty Discount] AS
     COALESCEEMPTY( [Measures].[Discount Amount], 0 )

will replace any empty values of discount amount with zero.

Very useful in certain scenarious.

MSDN Documentation: http://msdn.microsoft.com/en-us/library/ms146080.aspx
Tags: mdx replace replacing get getting null empty values measure zeros

Friday, July 22, 2011

SSRS: Hierarchies issue in report parameters with Analysis Services

Reporting Services offers great support for Analysis Services (getting data from cubes). Admittedly however, you will sooner or later meet with several little annoyances that hinder report development, especially if you are not confident or have no knowledge in MDX.

One common issue occurs when setting a dimension hierarchy as a parameter, as in screenshot 1 (for this example, I will be using Customer Geography hierarchy in the AdventureWorks Cube).
Screenshot 1 - Parameter
Reporting Services automatically creates a new hidden dataset, that retrieves the dimension members and all the members of its levels to use as available values for the report parameter. For instance, if you want the countries as parameter, you also get cities and names along with it, which could make the dropdown list parameter huge and messy, as in screenshot 2. While sometimes this is desired, other times it is not.

Screenshot 2 - Large dropdown list
So what can you do if you want only the countries? One option, of course, is to have each level as a separate hierarchy defined in the cube, as seen in the AdventureWorksDW. This is not always possible however. The steps below show you how to do this in the report, and can be applied for any case.

Solution 1 (if you only need the top level in the hierarchy):
  • In the Report Data, right click the Datasets folder and select "Show Hidden Datasets".
Screenshot 3 - Show Hidden Datasets
  • Right click the new dataset that appeared, and select "Query...". You'll be shown some scary, unformatted code (called MDX). Don't fret!
Screenshot 4 - MDX with ALLMEMBERS
  • In the code, there is one and only one place with the text "ALLMEMBERS", as seen in screenshot 4. This can be safely replaced with "CHILDREN" as in screenshot 5. If you execute the query, you will see that you only get the members without all the lower levels.
Screenshot 5 - MDX with CHILDREN
  • Click OK to save the query and preview the report. The dropdown list only shows the countries now, and is much cleaner than before.
Screenshot 6 - Clean dropdown list
Solution 2 (to get a specific level in the hierarchy):
  • The first solution works only if you require the members at the highest level (in the example, countries). But what if we want cities only for example? The first thing is to repeat the steps as above until screenshot 4.
  • This time, we will leave ALLMEMBERS as it is, and instead replace the statement preceding it. In this case, it is [Customer].[Customer Geography]. In the left column under the Metadata tab, find the level that you require and hover the mouse over it to get the statement you need, as in screenshot 7.
Screenshot 7 - Getting level MDX statement
  • We replace the statement in the MDX code as shown in the screenshot above. Finally we get the desired level members.
Screenshot 8 - Dropdown list with cities
Tags: reporting services analysis services mdx report parameters hierarchies level issue problem messy large dropdown list

Thursday, June 23, 2011

Microsoft SQL Server Reporting Services vs PerformancePoint Services

These 2 products aren't often compared with each other, and they are in fact different. However there are times where their capabilities overlap and reasons for using one instead of the other may be unclear, especially when using these tools together with Analysis Services.

Since not much information on such aspects seem to be available on the internet, below is a basic list displaying some comparisons.


Product
Standardized reports
Dynamic dashboards
Audience
Few to many users
Power users
Usage
Operational and Management
Strategy and Management
Measures
Fixed
Changeable
Dimensions
Fixed
Changeable
Drilldown
Fixed drilldown or drill through path as defined in report
Flexible drilldown for any report
Styling
Customizable to the lowest detail
Fixed appearance
Distribution
Online, Email, File Share or Printed
Online
Export
CSV, Excel, Image, PDF, Word or XML
Excel, PowerPoint (Individual Dashboard Elements)
Security
At report level. Users see what the report displays
At data warehouse level. Users see all data they have access to

It is certainly possible to implement some tricks or workarounds in order to achieve some capabilities such as styling in PerformancePoint using CSS, but those were the general aspects of the two tools. It should give a good idea of the differences.

Saturday, May 7, 2011

Hello World!

Welcome to yet another tech blog. Since every IT geek has an IT blog, then why not have one too? Though part of the reason comes from motivation from a colleague.

Topics I’ll be posting about can vary depending on what I’m currently working on. For instance, as I’m currently working on reports and analytics, you can expect a few posts regarding Reporting Services coming up, including some tips and workarounds of common issues, especially concerning Analysis Services.

Otherwise I’m a fan of C# and game development, so things may be headed that way too.