SSRS 2005 : How to create a dataset of member from an OLAP database

Requirements:
Having a knowledge of MDX

Having a knowledge of SSRS (being able to create a report, a datasource and a report parameter)

Having an OLAP database with at least one cube and one dimension

Creating the dataset

• Name = your dataset name
• Datasource = your OLAP datasource
• Command type = Text
• Query string =

WITH
MEMBER [MEASURES].[ParameterValue]
AS Axis(1).Item(0).Item(0).Dimension.CurrentMember.UNIQUE_NAME

MEMBER [MEASURES].[ParameterCaption]
AS String( Axis(1).Item(0).Item(0).Dimension.CurrentMember.Level.Ordinal * 1 , ' ' ) + Axis(1).Item(0).Item(0).Dimension.CurrentMember.Member_Caption

--the 5 last members
Set [theSet] as Order( Tail( [DimensionName].[HierarchyName].Members, 5), [DimensionName].[HierarchyName].CurrentMember.UniqueName, Asc)

SELECT
{ [ParameterCaption]
, [ParameterValue]
} ON COLUMNS
, Non Empty [theSet]
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on ROWS
from [CubeName]

• Click on the OK button to validate

To use this list of member, you have to create a new Report Parameters.

You can use the following picture as example

Good Luck!

SSRS 2005 : Using Stored Procedure with Report Parameters

Requirements:
Having a knowledge of T-SQL
Having a knowledge of SSRS (being able to create a report, a datasource and a dataset)

1. Creating the stored procedure.

The following example receives 2 input variables (a month and a 2-digit year) and returns a set of the previous month until the month of the previous year.

A picture is better than 1000 words!

So now the code.

CREATE PROCEDURE [dbo].[STP Name]
@ActMonth varchar(15),@ActYear varchar(4)
AS
BEGIN

DECLARE @id_Selected_Month tinyint

CREATE TABLE #Tmp_Table
(
[id_Month] [tinyint] NOT NULL,
[Month_Name] [varchar](3) NOT NULL
)

INSERT INTO #Tmp_Table VALUES (1,'Jan')
INSERT INTO #Tmp_Table VALUES (2,'Feb')
INSERT INTO #Tmp_Table VALUES (3,'Mar')
INSERT INTO #Tmp_Table VALUES (4,'Apr')
INSERT INTO #Tmp_Table VALUES (5,'May')
INSERT INTO #Tmp_Table VALUES (6,'Jun')
INSERT INTO #Tmp_Table VALUES (7,'Jul')
INSERT INTO #Tmp_Table VALUES (8,'Aug')
INSERT INTO #Tmp_Table VALUES (9,'Sep')
INSERT INTO #Tmp_Table VALUES (10,'Oct')
INSERT INTO #Tmp_Table VALUES (11,'Nov')
INSERT INTO #Tmp_Table VALUES (12,'Dec')

SET @id_Selected_Month = (SELECT [id_Month] FROM #Tmp_Table WHERE [Month_Name] = LEFT(@ActMonth,3))

CREATE TABLE #Tmp_OrderdedTable
(
[id_Act_Month] [tinyint] IDENTITY(1,1) NOT NULL,
[Month_Name] [varchar](10) NOT NULL
)

INSERT INTO #Tmp_OrderdedTable SELECT [Month_Name] FROM #Tmp_Table WHERE id_Month > @id_Selected_Month

INSERT INTO #Tmp_OrderdedTable SELECT [Month_Name] FROM #Tmp_Table WHERE id_Month <= @id_Selected_Month CREATE TABLE #Tmp_ActMonthTable ( ActMonth_1 [varchar](6) NULL, ActMonth_2 [varchar](6) NULL, ActMonth_3 [varchar](6) NULL, ActMonth_4 [varchar](6) NULL, ActMonth_5 [varchar](6) NULL, ActMonth_6 [varchar](6) NULL, ActMonth_7 [varchar](6) NULL, ActMonth_8 [varchar](6) NULL, ActMonth_9 [varchar](6) NULL, ActMonth_10 [varchar](6) NULL, ActMonth_11 [varchar](6) NULL, ActMonth_12 [varchar](6) NULL ) DECLARE @First_Month_ShortName varchar(6), @Month_ShortName varchar(6), @counter tinyint, @idMonth tinyint, @Output varchar(100), @ColumnName varchar(11) /* Act Month - 1 */ IF(@id_Selected_Month = 1)--if equal to January SET @First_Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = 11)+ ' 0' + CAST((@ActYear - 1)AS varchar(2)) ELSE SET @First_Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = 11)+ ' ' + @ActYear INSERT INTO #Tmp_ActMonthTable (ActMonth_1) VALUES(@First_Month_ShortName) /* Act Month - 2 to Act Month - 11 */ SET @counter = 1 SET @idMonth = 10 WHILE @counter <= 10 BEGIN SET @counter = @counter + 1 SET @ColumnName = 'ActMonth_' + CAST(@counter AS varchar(2)) IF((SELECT [id_Month] FROM #Tmp_Table WHERE [Month_Name] in(SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = @idMonth)) > @id_Selected_Month )
SET @Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = @idMonth) + ' 0' + CAST((@ActYear - 1)AS varchar(2))
ELSE
SET @Month_ShortName = (SELECT Month_Name FROM #Tmp_OrderdedTable WHERE [id_Act_Month] = @idMonth) + ' ' + @ActYear

SET @Output = 'UPDATE #Tmp_ActMonthTable SET ' + @ColumnName + ' = ' + '''' +@Month_ShortName + ''' WHERE ActMonth_1 = ''' + @First_Month_ShortName + ''''
EXEC(@OutPut)

SET @idMonth = @idMonth - 1
END


/* Act Month - 12 */
SET @Month_ShortName = LEFT(@ActMonth,3) + ' 0' + CAST((@ActYear - 1)AS varchar(2))
UPDATE #Tmp_ActMonthTable SET ActMonth_12= @Month_ShortName WHERE ActMonth_1 = @First_Month_ShortName

/* Result */
SELECT * FROM #Tmp_ActMonthTable


/* Free space */
DROP TABLE #Tmp_ActMonthTable
DROP TABLE #Tmp_OrderdedTable
DROP TABLE #Tmp_Table

END

2. Configuring the dataset

• In the Data tab of your report, create a new dataset
• In the Dataset window, change the dataset name, the datasource if needed, the command type to 'StoredProcedure' and write the name of your stored procedure in the 'Query String' pane
• Go to the Parameters tab of the same window and copy the following parameters name and value :
  Name Value
  @ActMonth =TRIM(Parameters!Period.Label)
  @ActYear =RIGHT(Parameters!Year.Label,2)

Period is a list of month and Year a list of year from 2005 until 2009 and are both parameters of the report. The Label function is used to return the parameter caption.

• Click on the OK button, pass to the 'Generic Query Designer' and click on the exclamation point to execute your stored procedure. In the new window enter March as @ActMonth value and 08 as @ActYear value and then click the OK button.

Voila, you have linked the Report parameters to your stored procedure.

Now you can use the fields of your dataset in the Layout tab and see the results in the Preview tab

Have fun!

SSRS 2005 : How to format a textbox using a conditional formatting?

• In the layout tab, select the textbox to modify
  
• click on the arrow of the FontSize property to deploy the combobox
  
• click on Expression to open the Edit Expression window
  
• In this window, you can write a iif clause for instance as

IIF(Fields!KPI.Value ="The world","10pt","12pt")

• You can do the same sort of formatting with the font, the color, the value, etc.