Passing Parameters to MDX Shared Datasets in SSRS

So part of my ongoing punishment work involved making some changes to a SSRS report that was running off some MDX, querying a Tabular  Model. It needed Parameters to feed Shared Datasets…  and the two had to talk nicely. This was not as easy at is seems…

First of all  big warning to anyone wanting to mix DAX and MDX on the same report: the two won’t talk well to each other. So in the below examples, whilst the DAX queries to get the parameters would have been relatively easy, they ultimately had to feed an MDX query (as that was what was pre-existing). This is a problem as Member Names are not in the same syntax for DAX and MDX so you can’t easily feed a selected Member from a DAX resultset into an MDX operator like STRTOMEMBER.

So, what we will do in this post is set up a simple report that has a user selectable parameter in the report that affects a shared dataset. Note the below examples run off AdventureWorks 2012.

Setting up a Parameter from MDX

Setting up an MDX Parameter to be usable is a little adventure in itself.  In order to be a Parameter that feeds into an MDX query, you need to pick up some additional Member properties along the way. So while this may get the members you want on the rows:

WITH
MEMBER    Measures.[Dummy] AS NULL

SELECT
{
[Measures].[Dummy]
}
ON COLUMNS,
{
[Date].[Calendar].[Calendar Year].Members
}
ON ROWS

FROM [Adventure Works]

It won’t play nicely when you try to use the member name in a function such as STRTOMEMBER it as it doesn’t return the Unique Member Name, which that function needs to work properly. So you need to write something like this:

WITH
MEMBER    Measures.ParameterCaption AS [Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION
MEMBER    Measures.ParameterUniqueName AS [Date].[Calendar].CURRENTMEMBER.UNIQUENAME
MEMBER    Measures.ParameterLevel AS [Date].[Calendar].CURRENTMEMBER.LEVEL.ORDINAL

SELECT
{
[Measures].[ParameterCaption]
,    [Measures].[ParameterUniqueName]
,    [Measures].[ParameterLevel]
}
ON COLUMNS,
{
[Date].[Calendar].[Calendar Year].Members
}
ON ROWS 

FROM [Adventure Works]

What this adds by returning these member properties is some values that can be used more effectively in SSRS parameters. The MEMBER_CAPTION gives the Member name for display,  UNIQUENAME gives the unique member name in the hierarchy so it can be used in downstream MDX calculations reliably and finally the level ORDINAL number can be used if depth in the hierarchy is important.

So now, to set it up in SSRS. I won’t go into too much detail – you can get most of this from the sample, but essentially create a shared dataset with the MDX for the parameter, add a report, then within the report create a dataset that uses the shared dataset. Then, we add a parameter and set it up as below (click on the images to see the full screen – the screen grabs were too big).

First, give it a name, a user friendly label and allow multiple values (if you want)

SSRS MDX  parameter setup properties
SSRS MDX parameter setup properties

Then, we point the parameter to get values from the shared dataset we created for the parameter. Here we use the additional properties we added in the MDX query.  We use the member UNIQUENAME as the Value field and the CAPTION as the Label field. The Value field is the actual data that will be passed by the parameter to other datasets.

SSRS MDX  parameter setup available values
SSRS MDX parameter setup available values

Finally, to keep things clean, we use the same dataset to provide the default values for the parameter.

SSRS MDX  parameter setup default values
SSRS MDX parameter setup default values

.. and we’re done. Now to connect it up with the Shared Dataset properly.

Parameterise a Shared Dataset

First, we need to create a parameterised shared data set. So we will add a Shared Data Set with a parameter in the text of the query, like below:

SELECT NON EMPTY
{ [Measures].[Order Count] }
ON COLUMNS,
NON EMPTY
{ ([Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) }
ON ROWS
FROM [Adventure Works]

WHERE STRTOSET(@CalendarYears)

Note the parameter for the query is called out by preceding it with the “@” symbol.

Now it is important to add the query in the right place to get the parameterisation to work. Don’t use the function button and add the query directly. Go to the “Query Designer” option

Define a Shared Data Set Query
Define a Shared Data Set Query

 

Within the Query Designer, click the “Design Mode” option (the triangle on the far right):

Shared Data Set Query Designer
Shared Data Set Query Designer

And then paste the query in there:

ssrs_mdx_parameter_setup_3c

Then you need to click on the  “Query Parameters” option –  5th from the right (the toolbar icons are minuscule at 1920×1080), and set up the parameter – this time, specify the name without the @ symbol:

Shared Dataset Parameter setup
Shared Dataset Parameter setup

This involves naming the parameter and tying it to a Dimension, Hierarchy and optionally default value(s). After that  click ok, run the query to test, and if all is OK, click on OK to close the designer.

Adding the parameter to the query in this way automatically adds content to the Parameters section of the Shared Dataset – note  the parameter name here is not preceded by an “@” symbol:

Shared Dataset Parameter section
Shared Dataset Parameter section

And you’re done.

Side warning about trying to set up parameters directly in the Shared Dataset properties:

One thing to be careful of is that doing parameter setup directly in the Shared Dataset properties, instead of via the Query Designer Parameter set up is that doing so adds a default value to the parameter which may throw this error when you run the report.:

An error occurred during local report processing.
The definition of the report ‘[Report Name]’ is invalid.

An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC40000] ‘RequestMinimum’ is obsolete: ‘Assembly level declarative security is obsolete and is no longer enforced by the CLR by default. See http://go.microsoft.com/fwlink/?LinklD=155570 for more information.’.’.

I haven’t tested any other context but this was using SSDT as part of VS2012. The reason is the default parameter format looks like this:

=new Object() {[Date].[Calendar Year].&[2008], [Date].[Calendar Year].&[2009], [Date].[Calendar Year].&[2007]}

Which causes errors at runtime. It seems to be impossible to correct as well – you can edit it but it always reverts back to the created value. So make sure you follow the steps above – as a reminder:

  1. Create a shared dataset
  2. Create the query in Query Designer mode
  3. Set up the parameter via the Query Designer “Query Parameters” window

Parameterise the Report

Next we need to set all this up in a report. Add a new dataset to the report that refers to the shared dataset we just created, and put a Tablix on the report that uses the Shared Dataset as a source.

Because the source Shared Dataset is parameterised, the report dataset will carry that parameter across:

Report Dataset Parameters
Report Dataset Parameters

It will map the Parameter Name from the query (without an “@”) to the  Parameter Value available from the report (with the “@”). Now – because planning – I named the parameter created in the first section the same as that for the Shared Data set, it has auto-magically mapped correctly.

Now, if you execute the report and set some values for the parameter, the parameter values will successfully pass via the report to the Shared Data Set and affect the results it passes back to the report.

There you go. Not the most obvious thing in the world, and there’s lots of ways to get it wrong – especially by putting @ symbols in the wrong place – but if you follow the pattern laid out in this post, you should avoid too much trouble.

The sample project can be downloaded here: Shared Dataset Parameters Example Project

 

 

Read More

Adventures in DAX, a mild grumble

Being back in the field is proving to be interesting as I get back into the daily frustrations and challenges of development (as opposed to those of PowerPoint). Anyway, one of the new things I’m hands on with is and Tabular Models and DAX table queries, and I’m feeling a little underwhelmed.

Syntax

I’m now torn with whether to hate DAX more than MDX. It seems that in the design of DAX table queries, any opportunity to make it accessible was largely ignored. What do I mean? Well…  here’s how to get all the rows from a given table in a Tabular Model:

EVALUATE
(
‘A Fact Table’
)

Ugh…   “EVALUATE”? I’m sure it makes sense to someone, but even MDX managed to have “SELECT”.
But then hiding in there are some weird syntactic quirks at the simplest level. Say I want to SUM a given column, I do this:

EVALUATE
(
SUMMARIZE
(
‘A Fact Table’,
‘A Fact Table’[My Measure]
)
)

So setting aside the Americanisation of SUMMARIZE, the above only actually works if the Measure is in the base table. If its a calculated column, you have to give it an alias to be able to access it, i.e:

EVALUATE
(
SUMMARIZE
(
‘A Fact Table’,
“My Alias”, ‘A Fact Table’[My Measure]
)
)

So that’s just annoying, as the error message if you don’t use an alias simply implies the measure isn’t there.

Usability

That’s of course just the small stuff. There’s the bigger challenges.

SSMS has no DAX query editor, so you need to use an MDX query template. And then in that there’s no Intellisense and the Metadata panel is useless as it is for querying your tabular model in MDX mode.

SSRS can only cope with DAX queries if you pretend you are writing a DMX query.

I’ve yet to interact with it via SSIS but I doubt the experience is much better.

The Upside

Anyway, it’s not all bad. To get started, take a look at these posts by Chris Webb (don’t fall over yourself looking at the official documentation though, it’s a bit sparse).

The language looks pretty powerful and getting sets of data out of it is simpler than MDX. However it’s not as friendly as it could be and it’s lacking some basic support as part of day to day usability. Hopefully this will improve by SQL2016…

Read More

The Geometry Data Type and SSRS Floorplan Reports

I was looking for a solution on how to build dynamic floorplans in SSRS, expecting to have to battle Visio to do such a thing. It turned out that a new data type, the Geometry spatial data type was introduced way back in SQL2008. This allows the creation and storage of polygon shapes in SQL Server. So now I have a database based mechanism to store objects on the floorplan, the floor itself and all the objects positions on the floor. Reporting Services can then surface all this using standard reporting capabilities.

How the Geometry Data Type works

The Geometry data type works on a simple x,y coordinate system to describe a polygon (official MSDN docco here). It can get more complicated than that, but this is the basic idea:

SQL Server Polygon Geometry Data Type description
SQL Server Polygon Geometry Data Type description

 

 

 

 

 

 

 

 

 

 

 

 

On a simple zero based set of x,y coordinates, you describe the path you take around the polygon – importantly ending back where you started – using the coordinates of each point of the polygon. In the case above, I’ve used a square, so that needs 5 points:

  1. 1,1 (start)
  2. 3,1
  3. 3,3
  4. 1,3
  5. 1,1 (finish back at start)

We can describe the Polygon construct as follows in T-SQL:

‘POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))’

Creating and loading a simple Geometry table

So, to use this data first we need to construct a table:

CREATE TABLE [dbo].[GeometryTest](
[ID] [int] NOT NULL,
[Shape] [geometry] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Value] [int] NULL,
CONSTRAINT [PK_GeometryTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Note there’s no configuration of the Geometry data type, or anything that needs to be enabled on the server to support this. I’ve included a Value column for the report, and an ID & Name column for metadata purposes.

Next I’ll load the table with a floorspace, and a few shapes – a couple of triangles and squares:

INSERT INTO [GeometryTest]
VALUES (1,’POLYGON((0 0, 7 0, 7 7, 0 7, 0 0))’,’Container’, NULL);

INSERT INTO [GeometryTest]
VALUES (2,’POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))’,’Box One’,1);

INSERT INTO [GeometryTest]
VALUES (3,’POLYGON((4 1, 6 1, 6 3, 4 3, 4 1))’,’Box Two’,5);

INSERT INTO [GeometryTest]
VALUES (4,’POLYGON((1 4, 3 4, 3 6, 1 4))’,’Triangle One’,2);

INSERT INTO [GeometryTest]
VALUES (5,’POLYGON((4 4, 6 4, 4 6, 4 4))’,’Triangle Two’,3);

We can check this works with the handy spatial previewer in SSMS, running a select all against the table:

SSMS Spatial Previewer
SSMS Spatial Previewer

 

 

 

 

 

 

 

 

 

 

 

Using SSRS to generate a Spatial report

Next, in SSDT (or BIDS, if not running SQL2012), create a new report with this table we just created and loaded as a data source. Drag a Map report item from the toolbox onto the report canvas, and choose to use a SQL Server Spatial Query as your data source. You should have a data set available – if not, just create one as a select * from the our test table. Click next to get to the previewer – it will autodetect your geometry column, so just click next again.

When choosing a report type, I’ve opted to take a Color Analytical Map, then clicked next. For the Analytical dataset, I’m reusing the same table as I stored the values with the shapes. At the Data Visualisation screen, we need to change the “Field to Visualise” to the [Sum(Value)] option as there’s no autodetect here. Then click finish.

To make it display in pretty colours, select the MapPolygonLayer object, and change the Polygon Rules > Colour Rules > Distribution Type property to “Equal Distribution”. Then run the report:

SSRS Spatial Report
SSRS Spatial Report

 

 

 

 

 

 

 

 

 

Simple, but proves a point and shows how you can create floorplan style reports from a dynamic data source.

 

 

 

 

 

Read More

SSRS & Stored Procedures

As an ETL Monkey, my experience with reports has been a bit incidental. One thing that puzzled me though is why report developers always wrote stored procedures to generate data for their reports, instead of using the SQL capabilities within the report. They said “Best Practice” and I was happy to leave them to it!

In this detailed post Adam Haines explains why. It’s very detailed so here are the key points if you have a short attention span:

  • The query can be maintained independently of SSRS, allowing tuning the query without accessing or modifying the reports
  • The execution plans can be cached if you use an Stored Procedure, but not if you use SSRS
  • Stored procedures allow the use of certain objects that cannot be used in embedded T-SQL in the report such as temp tables and indexes specific to those temp tables as well as table variables
  • Stored Procedures provide a layer of abstraction between the report and the business logic
  • Stored Procedures allow re-use of similar logic

Credit for the above list to my colleague John Simon who authored most of the above points in an internal discussion.

Read More

AUSSUG Upcoming Sessions

In case you aren’t in the Australian SQL Server User Group, AUSSUG, there are a few upcoming sessions in Sydney which will be pretty useful – check out the official site to register. Sessions are free and always useful.

Lunchtime Wed 3rd March, 2010 – Ensuring Optimal Performance in SQL Server 2008 Based Applications with Viktor Isakov

Evening * Thu * 4th Mar 2010 – What’s new in Reporting Services 2008 R2 and PerformancePoint Services 2010 with Peter Myers, presenting what’s new in the upcoming release of Reporting Services 2008 R2 and PerformancePoint Services 2010

And TBA date in April 2010 – Knights of the SSIS Round Table – Kevin Wong, Glyn Llewelyn and myself will be presenting a series of mini demos followed by Q&A, so a chance to pick some expert brains

Hope to catch you at one of the sessions!

Read More