Hocus Pocus with Microsoft BI Tools

SSIS - Tinyint datatype issue

2010-05-12T07:26:00.000-07:00

The stage I prefer is the design of the database. Using the most appropriate datatype, creating triggers, indexes, etc. I like that!

Recently I encountered an issue with SSIS where a field having the TINYINT SQL datatype was converted by SSIS in unsigned unicode data type. Of course, the destination field and this field did not match anymore.

The solution is to use a Derived Column component to convert back the data type to the signed unicode data type.

Thanks to the post of Simons, I was able to find the workaround:
http://sqlblogcasts.com/blogs/simons/archive/2006/02/24/SSIS-data-flows-and-tinyint.aspx

Time dimension in SSAS

2010-01-27T22:54:00.000-08:00

In SSAS 2005 and 2008, it exists 4 types of built-in time dimensions. All are based on the server time.

The problem with these server time dimension is the flexibility. Indeed no way to customize the members name or change the name of these. You will always have something like 'Friday 1 January 2010' as day.

To have a more usable time dimension, you will have to create a physical time dimension and import it into your datasource view.

This time dimension will contain then the naming you want.

Here the code I built to fill in my Calendar table which is the source table to my time dimension.

--Creation of the table

CREATE TABLE [dbo].[Dim_Calendar](
[DateKey] [int] NOT NULL, --Surrogate key
[FullDate] [datetime] NULL, --DD/MM/YYYY 00:00:00.000
[WeekNumInYear] [tinyint] NULL, -- The order number of the week in the year (gregorian calendar)
[Month] [tinyint] NULL, -- Month number (1-->12)
[MonthAbbrev] [char](3) NULL, -- Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Spe,Oct,Nov,Dec
[MonthBeginDateKey] [int] NULL, -- The surrogate Key of the first day in the month
[Quarter] [tinyint] NULL, -- Quarter number (1,2,3,4)
[QuarterName] [char](2) NULL, -- Q1,Q2,Q3,Q4
[QuarterYear] [char](7) NULL, -- Q1 2009,Q2 2009,etc.
[Year] [smallint] NULL, -- Year
[WeekNumOverall] [smallint] NULL, -- key identifier of the week
[WeekName_W] [char](4) NULL,
[ISOWeek] [smallint] NULL,
CONSTRAINT [PK_Dim_Calendar] PRIMARY KEY NONCLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Create function to get the ISO Week
CREATE FUNCTION [dbo].[fn_Get_ISO_Week_Number](

@date DATETIME )

RETURNS INT

AS

BEGIN

/***

The code here is from the article http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/ by Christoffer Hedgate 20 Jan 2005.

Note, change 01-04 in Christoffer's article because dates need to be passed as YYYYMMDD in SQL Server to avoid any inconsistency.

****/

RETURN
( CASE

-- Exception where @date is part of week 52 (or 53) of the previous year

WHEN @date < CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

END

THEN

(DATEDIFF(d,

CASE (DATEPART(dw, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

END,

@date

) / 7) + 1

-- Exception where @date is part of week 1 of the following year

WHEN @date >= CASE (DATEPART(dw, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

END

THEN 1

ELSE

-- Calculate the ISO week number for all dates that are not part of the exceptions above

(DATEDIFF(d,

CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

END,

@date

) / 7) + 1

END

)

END

-- Create the stored procedure to fill the calendar table
CREATE PROCEDURE [dbo].[Dim_Calendar_Generation]

@StartDate Datetime,
@EndDate Datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SET DATEFIRST 1

DECLARE
@DateKey int,
@WeekNumInYear tinyint,
@Month tinyint,
@MonthAbbrev char(3),
@MonthBeginDateKey int,
@Quarter tinyint,
@QuarterName char(2),
@QuarterYear char(7),
@Year smallint,
@WeekNumOverall smallint ,
@WeekName_W char(4),
@ISOWeek smallint

WHILE @StartDate <= @EndDate
BEGIN
SET @DateKey = CASE
WHEN (SELECT COUNT(*) FROM Dim_Calendar) > 0) THEN (SELECT MAX(DateKey) FROM Dim_Calendar) + 1
ELSE 1 -- Dim_calendar is empty
END
SET @WeekNumInYear = DATEPART(week,@StartDate)
SET @Month = MONTH(@StartDate)
SET @MonthAbbrev = SUBSTRING(DATENAME(month,@StartDate),1,3)
SET @MonthBeginDateKey =
CASE
WHEN
(
SELECT COUNT(MonthBeginDateKey)
FROM dbo.Dim_Calendar
WHERE MONTH(@StartDate) = MONTH(FullDate)
AND YEAR(@StartDate) = YEAR(FullDate)
AND DAY(FullDate) = 1
) > 0
THEN
(
SELECT DISTINCT MonthBeginDateKey
FROM dbo.Dim_Calendar
WHERE MONTH(@StartDate) = MONTH(FullDate)
AND YEAR(@StartDate) = YEAR(FullDate)
AND DAY(FullDate) = 1
) -- @StartDate is not the first date in the month
ELSE @DateKey -- @StartDate is the first day of the month
END

SET @Quarter = DATEPART(quarter,@StartDate)
SET @QuarterName = 'Q' + DATENAME(quarter,@StartDate)
SET @QuarterYear = 'Q' + DATENAME(quarter,@StartDate) + ' ' + CAST(YEAR(@StartDate) AS VARCHAR(4))

SET @Year = YEAR(@StartDate)
SET @WeekNumOverall =
CASE
WHEN
(
SELECT COUNT(WeekNumOverall)
FROM Dim_Calendar
WHERE YEAR(@StartDate) = YEAR(FullDate)
AND MONTH(@StartDate) = MONTH(FullDate)
AND @WeekNumInYear = WeekNumInYear
) > 0
THEN
(
SELECT DISTINCT WeekNumOverall
FROM Dim_Calendar
WHERE YEAR(@StartDate) = YEAR(FullDate)
AND MONTH(@StartDate) = MONTH(FullDate)
AND @WeekNumInYear = WeekNumInYear
)
WHEN (SELECT COUNT(*)FROM Dim_Calendar) = 0 THEN 1 -- Dim_Calendar is empty
ELSE (SELECT MAX(WeekNumOverall) FROM Dim_Calendar) + 1 -- First week of the month
END
SET @WeekName =
CASE
WHEN DATEPART(week,@StartDate) < 10
THEN 'W 0'+ CAST(@WeekNumInYear AS VARCHAR(4))
ELSE 'W ' + CAST(@WeekNumInYear AS VARCHAR(4))
END

SET @ISOWeek = (SELECT dbo.fn_Get_ISO_Week_Number(@StartDate))

INSERT INTO Dim_Calendar
(
[DateKey]
,[FullDate]
,[WeekNumInYear]
,[Month]
,[MonthAbbrev]
,[MonthBeginDateKey]
,[Quarter]
,[QuarterName]
,[QuarterYear]
,[Year]
,[WeekNumOverall]
,[WeekName_W]
,[ISOWeek]
)
VALUES
(
@DateKey
,@StartDate
,@WeekNumInYear
,@Month
,@MonthAbbrev
,@MonthBeginDateKey
,@Quarter
,@QuarterName
,@QuarterYear
,@Year
,@WeekNumOverall
,@WeekName_W
,@ISOWeek
)

SET @StartDate = @StartDate + 1
END

END

Get the date without the time

2009-09-02T07:24:00.000-07:00

PL/SQL
SELECT TRUNC(SYSDATE) --That should get you today's date

T-SQL
SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0) --That should get you today's date

SSIS - Kimball SCD Component vs. SCD Component

2009-09-02T07:21:00.000-07:00

Some of you certainly know the performance issue of using the Slowly Changing Dimension (SCD) SSIS component.
With a small or medium amount of records, this component works fine but with a bigger amount of data, I’m speaking here about more than 10000 records (which is not huge J), this component “sucks”.

Here at Mobistar I encountered this kind of issue because I had to import more or less 80000 records per day.

By using the SCD component, after 40 minutes I decided to stop the loading and starting to find another solution.
By using the Kimball SCD component, the loading takes a bit more than 2 minutes.

The Kimball SCD component receives input from 2 sorted sources, compares the data and updates, inserts or deletes the records into the destination table. Furthermore, the Kimball SCD applies an insert or update against all rows at the same time. The normal SCD applies the update or the insert against a single row at the time. This is why the normal SCD is slower than the Kimball SCD.

I guess this component could interesting you a lot in your future developments.

Feel free to send me your experience with this component.

http://kimballscd.codeplex.com/

Kind regards,
Jérémie
PS: you can also have a look to http://www.sqlbi.eu/Projects/TableDifference/tabid/74/language/en-US/Default.aspx for information about another improved SCD component

SQL Server 2005 :Insert a flat file in a OLTP table

2008-06-19T00:48:00.000-07:00

This article will show you 4 different ways to import a flat file in a SQL Server table.

The first question that you must have is:" Have I to manipulate some of my data?"

If no, you can use the following methods:

BULK INSERT

BULK INSERT is a T-SQL command that allows to import data from a flat file into a table.

Here an example:

BULK INSERT .dbo.[TheDestinationTableName] FROM 'FullPathToTheFlatFile'
WITH
(
FIELDTERMINATOR = '',
ROWTERMINATOR = '\n'
)

Here the end character between each column is '' and the final character to pass to the next record is '\n'

bcp

bcp can copy Microsoft® SQL Server™ data to or from a data file. bcp is a command prompt utility and not a T-SQL command. bcp is used in a batch file or in your .NET application, for instance. The bcp utility is written using the ODBC bulk copy application programming interface (API).

Import/Export Tasks

As the Copy tool of SQL Server 2005, it exists also an import tool in it.

From SSMS, connect you on your destination database (which one that contains the destination table). Right click on this database and select Tasks --> Import Data... The Sql Server Import and Export Wizard window opens itself.

Select Flat File Source as Datasource. Specify the File path and the formatting. You can preview the result in the Columns tab for the columns name and the data in the Preview tab.

Click Next and follow the steps.

if you have a manipulation:

In this case, you will need to use a tool that will make the changes. This tool is SSIS. SSIS is provided with the Developer and Enterprise version of SQL Server 2005.With SSIS you can create a connection to your flat file, make some changes on the values in your DataFlow and importing them in a table thanks to an OLE DB connection.

I hope that this post will help you ...

SSAS 2005 : My Best Practices

2008-05-21T05:02:00.000-07:00

1. OLTP database

Use physical table instead of physical view to build your dimension
Set index on these tables the following articles is very useful to chose which column indexing (http://sqlblogcasts.com/blogs/ssqanet/archive/2008/02/19/sql-server-2005-index-optimization-best-practices.aspx)

2. OLAP database

for query performance, use user defined hierarchy as much as possible except if you use custom rollup. In this case, Parent-Child hierarchies are more performant.
Case When mdx has been more performant than IIF in some case. When I used the Case When in a calculation of my cubes the time query was faster. Personaly, I don't hesitate to compare the response time of my query with an IIF statement and with a case when in the cube calculations part.
Decrease the number of cube calculation. Cause these calculations are evaluated on the fly.

In plus of my best practices, you can find more on the microsoft website (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx)

SSAS 2005 : Aggregation...Important thing to know!

2008-05-19T04:18:00.001-07:00

Aggregations are pre-calculated summaries of data. Specifically, an aggregation contains the summarized values of all measures in a measure group by a combination of different attributes.

How aggregations are used ?

To understand aggregations, consider the cube shown in Figure.

This is a small cube consisting of two dimensions, Date and Product, and one measure, Sales Amount.

The cube has three aggregations contributed by different combinations of attribute hierarchies.

The all-level aggregation stores the grand total Sales Amount.
The intermediate aggregation stores the total Sales Amount for the year 2004 and Road Bikes product subcategories.
Finally, assuming that the fact table references Month and Product dimensions, there is also a fact-level aggregation for July 2004 and product Road-42.

Suppose a query asks for the overall Sales Amount value. The server discovers that this query can be satisfied by the all-level aggregation alone. Rather than having to scan the partition data, the server promptly returns the result from the pre-calculated Sales Amount value taken from this aggregation. Even if there isn't a direct aggregation hit, the server may be able to use intermediate aggregations to derive the result. For example, if the query asks for Sales Amount by product category, the server could use the intermediate aggregation, Year 2004 and Subcategory Road Bikes, to derive the total.

Why not create all possible aggregations?

The short answer is that this will be counter-productive because all those aggregations will require enormous storage space and long processing times. The reason is that aggregations are created when the cube is processed.

Last thing, to help the Aggregation Design Wizard make a correct cost/benefit analysis, be sure to keep the EstimatedCount (attribute property) and EstimatedRows (Measure group property) counts up to date.

To make the good estimation you can use a very useful tool named BIDSHelper .

SQL Server 2005 : How to copy a database using SQL Server 2005?

2008-05-13T03:08:00.000-07:00

To do this manipulation, you have a lot of way.

Backup-Restore? too long

Create an SSIS package using BIDS? too much manipulation

The solution is ...

the integrated copy tool of SQL Server 2005.

This tool can copy one or several OLTP database and "paste" them on a server. It creates a package and launch the SQL server Agent that executes this one.

You can find it by right-clicking on a database in your SSMS.

After click on Copy Database.

The Copy Database Wizard opens.

1st step: select the source server and the authentification mode

2nd step: select the destination server and the authentification mode

3rd step: choose the transfer method (Detach-Attach or the SQL Management object method to keep the source db online)

4th step: choose the database to move/copy

5th step: specify file names and wether to overwrite database(s) at the destination

... creation of the package that will copy/move the database

Easy and useful!

SSIS 2005 : How to write annotations in a package?

2008-05-09T05:41:00.001-07:00

In the SSIS Designer, right-click on the design surface of your Control Flow, Data Flow or Event Handler tab and then click Add annotation.

After you have created your annotation, you can either change the font formatting or group it to an object of your design surface. How to do it? --> right-click on it :-)

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

2008-04-30T02:43:00.000-07:00

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

2008-04-30T00:53:00.000-07:00

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?

2008-04-28T10:21:00.000-07:00

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.