Displaying Multi Value Parameters in Reports

So you've got a multi-valued parameter in your report and as per best practice you want to display the selected values on the report, usually in the report header. But what if the list of parameter values is long, e.g. 10 or more values? If the user has ticked "Select All" in the parameter, you often don't want to show every individual value e.g.

MyParameter: Value1, Value2, Value3, Value4, ..... Value15

Usually if the user ticks "Select All" it's preferable to just display something like:

MyParameter: All

I've seen a few ways of accomplishing this, often involving clunky SQL in the dataset that provides the report values to create an artificial "ALL" option in the data. However, my preferred option is simpler, and I think a little more elegant (I don't claim to have invented this by the way, it's just the best way I have seen it done).

The textbox you are using to display the parameter values will probably already contain an expression like:

=Join(Parameters!MyParam.Value, ", ")

to concatenate the selected values in the multi-value parameter. Try replacing it with this:

=Iif(IsArray(Parameters!MyParam.Value),

Iif( CountRows("ParamDataset")=Parameters!MyParam.Count,

"All",

Join(Parameters!MyParam.Label, ", ") ),

Parameters!MyParam.Label)

where MyParam is the report parameter and ParamDataset is a dataset that provides the available values for the report parameter.

What's happening here is the code first checks to see if the value of the parameter is an array (if it is, then the parameter is a multi-value parameter). It then counts the number of rows in the dataset supplying the available values for the parameter (i.e. the total available values) and compares this to the number of values in the array. If these numbers are equal, this means that all available values have been selected (i.e. "Select All" in the parameter options) and so the textbox will just display the string "All". If they are not equal, then it just concatenates the selected parameter values (or labels) together into a string.

It's not perfect, because if the user selects all but one parameter values from a large list then they still get a long concatenated list, but I in my experience this works well for many clients.

VS2005 crashes when editing a data flow

Had an unusual issue with BIDS/VS2005 today where the environment (specifically the devenv.exe process) would crash when particular package was opened and someone tried to edit the Data Flow. This occurred on multiple workstations with the same solution/project.

The Windows event log contained the following information:

Faulting application name: devenv.exe, version: 8.0.50727.867, time stamp: 0x45d2c842

Faulting module name: msdds.dll, version: 9.0.30729.4462, time stamp: 0x4b2fa701

Exception code: 0xc0000005

Fault offset: 0x0002006b

Faulting process id: 0x1c30

Faulting application start time: 0x01cb489014680845

Faulting application path: C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\devenv.exe

Faulting module path: C:\Program Files (x86)\Common Files\Microsoft Shared\MSDesigners8\msdds.dll

Report Id: 6e0187f5-b483-11df-8963-6cf049256ac3

Very confusing initially but after a bit of Googling we found a simple solution: in the Build menu in BIDS select the "Rebuild Solution". This corrected whatever corruption had occurred and the package could be edited normally.

Mosha Pasumansky

Belated Happy New Year to all!

The big news in BI for the end of 2009 was the announcement by Mosha Pasumansky that he would no longer be working in the BI space, having moved over to Microsoft's search engine team (Bing). Mosha was one of the original creators of Analysis Services and the MDX language at Microsoft, which went on the become one of the most successfuly OLAP engines and the industry standard query language respectively, so it's sad to hear he will no longer be contributing his extensive experience to the BI community. However I've no doubt the Bing team will be benefitting immensley from having such a creative guy onboard.

MDX SCOPE error

Had an interesting error today when adding a new Calculated Measure to a cube. When I tried to process the cube BIDS came up with these errors:

Error: A set has been encountered that cannot contain calculated members.

and

The END SCOPE statement does not match the opening SCOPE statement.

After some digging around on the internet, I found that the issue was caused by me adding the new calculated measure to the SCOPE statement for the existing MDX statements for created by the "business intelligence wizard" in BIDS. This wizard adds some time based calculated measures to the time dimension in your cube (e.g. Year To Date, Month on Month Growth) and I thought I would be able to add my new calculated measure to the existing SCOPE statement to get the same time intelligence functionality. Wrong!

It appears that you cannot mix regular and calculated measures in a SCOPE statement, and doing so will generate the above errors. The reasons for this are not clear, and it is certainly a bit of a pain since it means you have to repeat any logic or calculations within the SCOPE statement twice, once for regular measures and again for calculated measures. This, as you can imagine, does not help with the maintainability of MDX scripts.

So, for example;

SCOPE(
              {
                [Measures].[My Regular Measure],
                [Measures].[My Calculated Measure]
              }
)
.. (some MDX assignment here)
END SCOPE;

will cause the above errors. To get around this you have to rewrite it like this;

SCOPE(
              [Measures].[My Regular Measure]
)
.. (some MDX assignment here)
END SCOPE;

SCOPE(

[Measures].[My Calculated Measure]
)
.. (some MDX assignment here)
END SCOPE;

Basically duplicating your MDX assignment code! There is an alternative way around this sometimes, where you can rewrite to query to avoid using the SCOPE statement at all, as discussed here, but with complex scripts this can start to make the code harder to understand.

SQL Syntax to SSIS Expression Syntax

Expressions are a very powerful way to build flexibilty into packages, however people (like me) who have come from a SQL database background into developing SSIS packages often struggle to get to grips with the syntax used in SSIS expressions.

Here I have noted some SQL syntax and the SSIS expression equivalent:

SQL	SSIS Expression
AND	&&
OR	||
=	==
<>	!=
ISNULL()	ISNULL()
IS NOT NULL	!ISNULL()
CHARINDEX()	FINDSTRING()*
CONVERT(DateTime, '2007-12-04')	(DT_DATE)”2007-12-04”

*Both functions return the location of the occurence of the character, however CharIndex starts from a specified location, whereas FindString looks for the specified occurrence of the string/character. 

Helpfully, many functions are the same between the two languages, for example GETDATE() and LTRIM() work in exactly the same way in expressions as in SQL.

Microsoft Business Intelligence Project Booster Kit @ SQLServerCentral

Siddharth Metha has written an article, rather grandly entitled "Microsoft Business Intelligence Project Booster Kit", that summarises the best of "best practice" notes and blogs for Microsoft BI and also links to some of the most popular tools and add-ons available.

Particularly useful for anyone starting out on a BI project, but also a good checklist for an established team to benchmark themselves against, the article includes links to SQL and SSIS naming conventions from the likes of Jamie Thompson, best practice documents from SQLCAT and tips on project documentation.

The full article can be found here.

Capturing The MDX Generated By Reports

Debugging Reporting Services reports that include dynamically generated MDX statements can be a tedious business - however help is at hand. MVP Darren Gosbell has created a simple utility that allows you to capture the MDX generated by a report (or anything else for that matter) including all the substituted parameter values. The MDX is captured to a text file which can then be opened up for debugging in SQL Server Management Studio, or the often more useful (and free) MDX Studio.

Darren's original post about this is here, and the QueryCapture utility files can be downloaded here.