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
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)
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):
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):
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:
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
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
