SSAS - Scoping on restricted members

Due to a miscommunication in the requirements gathering process I recently had to apply a temporary fix to overwrite values in my cube like so:

SCOPE([MEASURES].[Revenue KPI]);

  SCOPE([Product].[Product].&[11700]);

    THIS = [Measures].[P and L];

  END SCOPE;

END SCOPE;

In this particular cube we have security roles set up that restrict on the [Product].[Product] hierarchy. Users that are members of roles that restrict them from seeing [Product].[Product].&[11700] were complaining about error messages like the one displayed below.

The problem is that we're trying to scope on a member that that user is not allowed to query. So how do we get around that? I pinged a message to Chris Webb and he replied that he had already blogged about this.

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1563.entry

The solution is shown here and basically works around the issue by creating a named set that is empty if the user is not allowed to view the member:

CREATE HIDDEN SET [BFSelect] AS

  iif(iserror(strtomember("[Product].[Product].&[11700]")),

  {},

  {[Product].[Product].&[11700]}

  );

SCOPE([MEASURES].[Revenue KPI]);

  SCOPE([BFSelect]);

    THIS = [Measures].[P and L];

  END SCOPE;

END SCOPE;

Problem solved, thanks to Chris.

SSRS - passing multi value parameters via URL

Passing parameters from one report to another report via the URL can be a bit tricky, esepecially when it comes to multi value parameters. Below you will find an example of how to do this for single and multi value parameters.
For example, your report server is at http://myserver/reportserver and the report you want to pass the parameter(s) to is in \MyFolder\MyReport and the parameter name is myparam1.


If it is a single value parameter you will generate the URL as
=http://myserver/reportserver/Pages/ReportViewer.aspx?%2fMyFolder%2fMyReport&rs:Command=Render&myparam1=" & parameters!myparam.value


This is quite obvious and not too tricky but how would you pass multi value parameters? Some people have tried to do it like this:
=http://myserver/reportserver/Pages/ReportViewer.aspx?%2fMyFolder%2fMyReport&rs:Command=Render&myparam1=" & Join(parameters!myparam.value,",")


Unfortunately this won't work, the correct method is:
=http://myserver/reportserver/Pages/ReportViewer.aspx?%2fMyFolder%2fMyReport&rs:Command=Render&myparam1=" & Join(parameters!myparam.value,"&myparam=,")


In other words, for every value of the parameter you have to pass &myparam=value to the URL.


For more info on URL access click http://msdn.microsoft.com/en-us/library/ms152835.aspx

SQL Server - allowing remote connections

Whenever I install SQL Server on a development environment I forget to enable remote connections. See this blog for the steps.
http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/

SSRS - conditional formatting for lazy people

Let's say you create a report and you want to apply conditional formatting, in this case just a conditional colour of the font, there's a couple of ways to do this. Let's consider the below (mini) report.

We'll have to:

1. Select the textbox containing the value =Fields!Revenue_KPI.Value
2. Edit Color property to say =IIF(Fields!Revenue_KPI.Value<0,"Red","Black")
3. Select the textbox containing the value =Fields!Volume_KPI.Value
4. Edit Color property to say =IIF(=Fields!Volume_KPI.Value<0,"Red","Black")

If your table only contains two textboxes this is OK, but if your table has tens on textboxes this can be a lot of work, especially if you have footers containing totals.
The alternative is to use the value of the textbox instead of the value of the dataset field to determine if the color of the text needs to be red or black.

1. Select all the textboxes you want to format
2. Edit Color property to say =IIF(Me.Value<0,"Red","Black")

How easy is that?

SSRS - adding parameters to a dataset with an Oracle data source

When you are creating a SQL Server Reporting Services (SSRS) report and you want to add a dataset that connects to an Oracle dataset using OLE DB and you want to add parameters to the PL/SQL query, you might make the mistake of using the same syntax as in T-SQL which is @param. The PL/SQL version is :param.


For example, in T-SQL we would write:


SELECT   id, name
FROM
   myTable
WHERE
   cond   =   @param


while in  PL/SQL we would write:


SELECT   id, name

FROM

   myTable

WHERE

   cond   =   :param

Also, don't forget to pass the report parameter variable value to the dataset parameter on the dataset properties tab aptly called "Parameters". See below for the screen shot in 2008.