SSAS/SSRS : Building a graph for analyzing a subset related to the whole set

Introduction
My client has some wishes about a graph that shows a subset of facts of certain locations against the whole population of locations. They want to highlight the locations and show their position in a sorted diagram. Below you can see the end result of the graph.

Scenario
For this purpose i've quickly created a small cube. First i build a script for building the following tables:

• DimDate
• DimLocation
• DimDepartment
• BridgeLocationDepartment

 And they are related as follows:

This situation is already explained in more detail in my former posts:

• Modeling a N:M relation between dimensions (part I).
• Building a multivalue dimension construction in SSAS ( part II).
• Joining issue with MDXing of M2M dimension (part III).
• Cascading parameters and M2M dimensions  (part IV)

I've created the following example script:

USE [GraphProblem]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactTest]') AND type in (N'U'))
DROP TABLE [dbo].[FactTest]
GO

CREATE TABLE [dbo].[FactTest](
[FactTestID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Location_Key] [int] NOT NULL,
[FactTestCount] [int] NOT NULL
) ON [PRIMARY]
GO

----
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimLocation]') AND type in (N'U'))
DROP TABLE [dbo].[DimLocation]
GO

CREATE TABLE [dbo].[DimLocation](
[Location_Dim_key] [int] NOT NULL,
[Location_Code] [int] NOT NULL,
[Location_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

----
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[DimDepartment]
GO

CREATE TABLE [dbo].[DimDepartment](
[Department_Dim_key] [int] NOT NULL,
[Department_Code] [int] NOT NULL,
[Department_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

---
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BridgeLocationDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[BridgeLocationDepartment]
GO

CREATE TABLE [dbo].[BridgeLocationDepartment](
[BridgeLocationDepartment_ID] [int] IDENTITY(1,1) NOT NULL,
[FK_Location_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[Valid_From] [date] NOT NULL,
[Valid_Until] [date] NOT NULL
) ON [PRIMARY]
GO

---
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

---

INSERT INTO dbo.DimDepartment (Department_Dim_key, Department_Code, Department_Description)
VALUES
(1, 1, 'Department A'),
(2, 2, 'Department B'),
(3, 3, 'Department C'),
(4, 4, 'Department D'),
(5, 5, 'Department E'),
(6, 6, 'Department F'),
(7, 7, 'Department G'),
(8, 8, 'Department H'),
(9, 9, 'Department I'),
(10, 10, 'Department J')

INSERT INTO dbo.DimLocation (Location_Dim_key, Location_Code, Location_Description)
VALUES
(1, 1, 'Location 1'),
(2, 2, 'Location 2'),
(3, 3, 'Location 3'),
(4, 4, 'Location 4'),
(5, 5, 'Location 5'),
(6, 6, 'Location 6'),
(7, 7, 'Location 7'),
(8, 8, 'Location 8'),
(9, 9, 'Location 9'),
(10, 10, 'Location 10')

INSERT INTO dbo.BridgeLocationDepartment (FK_Location_Key, FK_Department_Key, Valid_From, Valid_Until)
VALUES
(1, 1, '2011-01-01', '9999-12-31'),
(2, 1, '2005-01-01', '9999-12-31'),
(2, 2, '2005-01-01', '9999-12-31'),
(3, 3, '2005-01-01', '9999-12-31'),
(4, 3, '2005-01-01', '9999-12-31'),
(4, 4, '2005-01-01', '9999-12-31'),
(5, 5, '2005-01-01', '9999-12-31'),
(5, 7, '2005-01-01', '9999-12-31'),
(6, 6, '2005-01-01', '9999-12-31'),
(6, 7, '2005-01-01', '9999-12-31'),
(7, 7, '2005-01-01', '9999-12-31'),
(8, 8, '2005-01-01', '9999-12-31'),
(9, 9, '2005-01-01', '9999-12-31'),
(9, 10, '2005-01-01', '9999-12-31'),
(10, 10, '2005-01-01', '9999-12-31' )

INSERT INTO dbo.DimDate
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)


INSERT INTO [dbo].[FactTest]([FK_Date_Key],[FK_Location_Key],[FactTestCount])
VALUES
(7, 1, 1),(7, 2, 1),(8, 3, 1),(9, 3, 1),(9, 2, 1),(10, 4, 1),(10, 5, 1),(10, 5, 1),(11, 5, 1),(7, 7, 1),
(9, 4, 1),(10, 3, 1),(11, 10, 1),(12, 6, 1),(10, 2, 1),(9, 9, 1),(8, 7, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),
(8, 1, 1),(7, 1, 1),(7, 2, 1),(8, 3, 1),(9, 2, 1),(10, 4, 1),(10, 5, 1),(10, 5, 1),(7, 7, 1),(9, 4, 1),
(10, 3, 1),(11, 10, 1),(10, 2, 1),(9, 9, 1),(8, 7, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),(8, 1, 1),(10, 5, 1),
(7, 7, 1),(9, 4, 1),(10, 3, 1),(11, 10, 1),(10, 2, 1),(9, 9, 1),(8, 7, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),
(8, 1, 1),(10, 5, 1),(10, 5, 1),(11, 5, 1),(7, 7, 1),(9, 4, 1),(10, 3, 1),(11, 10, 1),(12, 6, 1),(10, 2, 1),
(9, 9, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),(8, 1, 1),(10, 5, 1),(10, 5, 1),(11, 5, 1),(8, 1, 1),(7, 1, 1),
(7, 2, 1),(8, 3, 1),(9, 2, 1),(10, 4, 1),(10, 5, 1),(10, 5, 1),(10, 1, 1),(10, 2, 1),(10, 3, 1),(10, 7, 1),
(10, 5, 1),(10, 9, 1),(10, 2, 1),(10, 1, 1),(10, 4, 1),(10, 4, 1),(10, 5, 1),(10, 9, 1),(10, 2, 1)

Created a cube on the datastructure with following structure:

The dimension usage tab:

And now take a look at the dimension browser tab:

So the values for 2009 seems an interesting candidate for simulating the situation. There are certain departments and locations which can be used for testing my graphproblem. I would like to focus on Department C (location 3 and 4) and department G (Location 5 and 7).

The solution
Now, let's build the report with a graph and a parameter "department". So when the department is selected i want to show the locations in highlighted manner. You can see this in the diagram in the introduction section. Initially, it would look something like this (the customer don't want the names of the locations shown):

But the locations of department C is not red (the two 5's). It's not possible to do a lookup between two datasets in SQL Server 2008 (SQL Server 2008 R2 does have a lookup function). Therefore i decided to alter my MDX query and add an extra column indicating whether a location belongs to a selected department at the parameter. So i changed the standard MDX query to this:

WITH MEMBER [Measures].[test]
AS
IIF(ISEMPTY((EXISTS([DimLocation].[LocationDescription].CURRENTMEMBER, {STRTOSET(@DimDepartmentDepartmentDescription, CONSTRAINED)}, "FactTest").item(0), [Measures].[FactTestCount])), 0, 1)

SELECT {[Measures].[FactTestCount], [Measures].[test]} ON COLUMNS,
[DimLocation].[LocationDescription].[LocationDescription] ON ROWS
FROM (SELECT ( { [DimDate].[DateYear].&[2009] }) ON COLUMNS
               FROM [GraphProblem])

Resulting in (when testing with department C):

The most important section is the WITH section where IIF and EXISTS do some nifty work. The EXISTS function checks whether the set of locations are related to the set of departments (selected  with the parameter). The EXISTS returns a NULL when there is not a relation and a measure value  when there is a relation. So with the IIF and the ISEMPTY it's possible to return a 1 (for a relation) and 0 (for no relation)

So now take a look in the report and select the series properties in the diagram and select the Fill tab:

The next thing is checking whether the test measure is 0 or 1 with : "=IIF(Fields!test.Value = 1, "Red", "Blue")". This looks great when i run the report. The locations belonging to department C is highlighted:

When i choose department G the following diagram is shown:

Conclusion
Initially, i wanted to solve this problem in the report but when i found out that SQL Server 2008 doesn't have a lookup function i needed to try other things. There are some solutions available on the internet about  building this with subreports or custom coding but the solution above is a better solution (in my opinion).

Greetz,
Hennie