Displaying the Selections for SSRS Multi Select Parameters in a Report

When multi select is enabled on a parameter in a SSRS report, this obviously allows the user running the report to make 1 or more selections from the parameter list.  Whilst the selections appear in the report parameter drop down box after the selections have been made, it can be intuitive to add a label in the report showing the selections that were made for this parameter.

Create a Text Box in the report, create a Placeholder within the Text Box, highlight the Placeholder, right click and select the fx button next to the Value field.   Enter the following expression to make your multi select parameter selections appear as a comma seperated list within the Text Box Placeholder on your report.


=Join(Parameters!ParameterName.Label, ",")

As an alternative you could optionally just display the word “Multiple” if there is more than one selection made.


=IIF(Parameters!ParameterName.Count = 1, JOIN(Parameters!ParameterName.Label, ", "), "Multiple")

You will need to substitute ParameterName with name of your Multi Select Parameter.

Analysis Services DependsonDimension Property

I have never used the Analysis Services DependsonDimension property so I was intrigued when trialling a few configurations dealing with custom dimensional security – what it actually does.   Looks like Chris Webb is all over this so I won’t cover in detail here but suffice to say the property only really gives the Aggregation Design and Usage Based Optimisation Wizards something to ponder when two dimensions are closely related.

Chris Webb Covers DependsonDimension Property

Is also covered in brief here with more explanation – “If a dimension A has a strong correlation to dimension B, then you should set A “depends on” B. This way, the aggregation design algorithm won’t consider both as mutually independent dimensions. For example, Fiscal Time is dependent on Calendar Time.”

http://technet.microsoft.com/en-us/library/microsoft.analysisservices.dimension.dependsondimension(v=sql.90).aspx.

Analysis Services Server Version DMV

Recently I discussed with colleagues how to get the Analysis Services Server version in an environment when you are not an OLAP Administrator.

You can run the following code on the OLAP server as an MDX query on any database you have access to …

select [Value] from $system.discover_properties where PropertyName = 'DBMSVersion'

Thanks to DG !

Indexed Views are Good for Reporting…Inserts please turn away now…

There is a lot of red tape involved in creating indexed views – but the bottom line is they are worth it, particularly for reporting.

If you have ever created Indexed Views before you most definately would have come across an error or two throughout the process.  Before you put this into the too hard basket consider the following scenarios where the benefits of fast query performance will be realised…

Applications that benefit from the implementation of indexed views include:

  • Joins and aggregations of large tables
  • Repeated patterns of queries
  • Repeated aggregations on the same or overlapping sets of columns
  • Repeated joins of the same tables on the same keys
  • Combinations of the above

Indexed views are therefore likely to benefit in the following scenarios :

  • Decision support workloads
  • Data marts
  • Data warehouses
  • Online analytical processing (OLAP) stores and sources
  • Data mining workloads

Ok lets look at some basics.   The first thing we need to do is create our view.  We can then create one or more indexes on our view starting with a clustered index.

To create the view we will use a similar syntax to creating a regular view except we will use the WITH SCHEMA BINDING Option see Schema Binding …

e.g.


CREATE VIEW dbo.vw_SomeView WITH SCHEMABINDING

AS

SELECT colKey, col2, col3

FROM dbo.Table1

Note that any tables mentioned in the SELECT statement of your view must be in the two part owner.object name format.  There are a heap of caveits that could bring you down at this point which would prevent you from creating the view.   One of which is you should not use deterministic functions.  E.g. DateAdd returns the same value for a set of parameters each time, whereas getdate() does not i.e don’t use getdate() in your view.    CONVERT(datetime, somedate) is non deterministic as it can return different results when different values are provided for the style parameter….consider CONVERT(datetime, somedate,103) as it is deterministic – see Using Convert Within an Indexed View.   Note in the example Create View statement above there is explicit specification of columns in the select clause (i.e. no select * …), the select is on a base table not other views and if joins were used, OUTER JOINS would be avoided. The full design considerations for indexed views are outlined here http://msdn.microsoft.com/en-us/library/ms191432.aspx.

Once we have successfully navigated the design guidelines and have successfully created our view, now it is time to create the indexes.  We create the index as we would on a table.  The first index we need to create on a view is a unique clustered index.  We can then create additional none clustered indexes as required.


CREATE UNIQUE CLUSTERED INDEX ci_vw_Table1 ON dbo.Table1 (colKey)

Finally Aaron Bertrand runs through a practical example of using Indexed Views and discusses the good and dark sides of indexed views in this nice example Redundancy is No Evil.

MDX Samples – Helping our Business Users see Custom Groupings of Products

One of the more common citations from the desk of the business user, is something that starts with .. “I want to see …”…

Creating a data model that fits the business requirements like a glove might do for now, but the business are great at thinking up new ways of categorising their data.   Top items, these items, those items, those items excluding top items, the list goes on. Whilst some I.T. folk might view business requests as “annoying”, we in the B.I. space love seeing out business users take hold of our collaborations and running with them.

The following snippet demonstrates (using Adventureworks), how to group product lines into 3 custom groupings : Top 2 items, All Other Items, and All Items.  These techniques show you how to include specific line items of interest in your report, whilst grouping the remaining items you are not so interested in, into one total e.g. Other Products … From the Martin Mason Blog …


WITH SET [Top Items] AS

Generate(

{ [Product].[Category].[Category].Members },

TopCount(

EXISTING [Product].[Product].[Product].members,

2,

( [Measures].[Reseller Sales Amount] )

)

)

MEMBER [Product].[Product].[All].[All Other Products] AS

Aggregate( { EXISTING { [Product].[Product].[Product].Members } - [Top Items] } )

SELECT        {

[Measures].[Reseller Sales Amount]

} ON COLUMNS,

{

{ [Product].[Category].[Category].Members }

* {

[Top Items],

[Product].[Product].[All Other Products],

[Product].[Product].[All]

}

} ON ROWS

FROM        [Adventure Works]

Alternatively lets say the business wants to see all of the products that are listed in the current 20% catalogue sale (20% off all Accessories and Products), we can display the line items for all products in the Accessories and Products categories, then show all other items grouped into one total…


WITH SET [Catalog Accessory Products] AS

'Filter([Product].[Product].[Product].Members,

(InStr(1,[Product].[Category].CurrentMember.Name, "Accessories") <> 0)

)'

SET [Catalog Clothing Products] AS

'Filter([Product].[Product].[Product].Members,

(InStr(1,[Product].[Category].CurrentMember.Name, "Clothing") <> 0)

)'

MEMBER [Product].[Product].[All].[All Other Products] AS

Aggregate(

{ EXISTING

{ [Product].[Product].[Product].Members }

- {[Catalog Accessory Products] + [Catalog Clothing Products]} } )

SELECT        {

[Measures].[Reseller Sales Amount]

} ON COLUMNS,

NONEMPTY({

{ [Product].[Category].[Category].Members }

* {

[Catalog Accessory Products],

[Catalog Clothing Products],

[Product].[Product].[All Other Products]

}

}) ON ROWS

FROM        [Adventure Works];

Creating Time Intelligence Filters in Performance Point 2010 – Quick Ref

Those of you new to Performance Point 2010 or who are starting to want more from your dashboards using Time Intelligence, the following link provides a succinct reference for applying Time Intelligence formulas see Performance Point TI Shortcuts.

Those turning their hand at MDX filter queries and doing custom member rollups filters …..(exhale) be aware the Performance Point 2010 MDX editor has a strict requirement on formatting which excludes the use of WITH statements before your SELECT as identified here MSDN Reference.

Ron Davis provides a good You Tube clip demonstrating the use of the MDX Filter feature Ron Davis MDX Filter Clip.