SSRS PageName Part1/2

Prior to SSRS 2008 R2 when you export a multi page report to excel the sheet names are by default named as sheet1, sheet2 …sheet N. It is very difficult if not impossible to change these names to custom names.

A new feature is introduced in SSRS 2008 R2 that allows renaming sheet names. Let’s see how it works.

To begin with, I have simple report with two tables.

To render the tables in separate pages instead of rendering them one after the other, add a page break after the first table.

If you click preview and export the report to excel you will see the report rendered as sheet1 and sheet2 in excel.

But remember we wanted to give them custom names. So to do that first select the top table (or tablix whatever you want to call, I am used to call it table) and in the properties scroll down to ‘PageName’. You can enter a string here that will be the name of the sheet in excel. As you can see I named it as ReportEast. Now do the same for the bottom table in the report and name it ReportWest.

Preview the report and export to excel you will notice that the sheets are now named as ‘ReportEast’ and ‘ReportWest’.

How to generate RDL file from report manager?

Your boss asked you to change a report that’s currently in production. You realize that you don’t have a RDL or a solution file for that dirty report. What do you do?
The process of regenerating the rdl from a report on the report manager is slightly different in 2005 and 2008.
2005: Navigate to the report properties page. In the report definition section click edit, a file download page will appear, click save and specify the location where you want to save the rdl file.
2008: On the open menu of the report click download and specify the location where you want to save the rdl file.

Once you have the rdl file you can add this as an existing item to a report server project.

Keys to(in) Data Warehousing

I was helping a co-worker understand how surrogate keys work in a data warehouse and the messgae from him at the end of the conversation was
“Keys are key to a data warehousing solution”. Thought of blogging about this.
In a OLTP based environment its all about primary keys(PK) and foreign keys(FK). However when it comes to data warehousing, in addition to PK and FK there are a couple of other concepts that are very commonly used.
Natural Key:
This can also be called as business key and is only a synonym of primary key and i think this doesn’t need any further explanation. It is nothing but the primary key from the OLTP system. Period.
Surrogate Key:
Surrogate Key is a common practice in any dimensional model. In simple terms, it is an auto generated identity column and this will act as primary key for the dimension table. The basic script to create a dimension table will look like

CREATE TABLE [HR].[Employee]
   (
       EmployeeKey INT IDENTITY(1,1) PRIMARY KEY  --SurrogateKey
       , EmpLicenseNumber VARCHAR(25) NOT NULL    --NaturalKey
   )

I guess the next question is why do we want to maintain a surrogate key instead of using the same primary key from OLTP. Well, hopefully you will find the answers below.
1) If you notice, the natural key in the above example is a varchar. The joins among facts and dimensions is done on key columns and joining on varchar instead of integer columns severly hurts query performance.
2) For a SCD type 2 dimension we should be able to insert multiple rows for the same employee. This will not be possible if we have EmpLicenseNumber as the primary key since you cannot insert duplicate values into a primary key column.

Degenerate Dimensions

So what are Degenerate dimensions? Where and why do we use them? This post will answer these questions.
Degenerate Dimensions are dimension keys in the fact tables and unlike other dimensions they are not related to any dimension tables. Instead Degenerate Dimensions are used to link back to the source system for data validation. Simply speaking these are the natural keys in the source system like the transaction number, sales order number, shipping tracking number etc., Clearly the business users will not be interested in browsing the facts on Degenerate dimensions because for instance total sales by sales order number doesn’t mean anything.

SQL Date Cheat Sheets

This post will show the commonly used queries to play with date functions in SQL Server. And yes i hope to update this list frequently.
Currently the following script has queries to:
Select two digit month, Select the first and last day of the month…

 --query to select month in two digits
SELECT	[TwoDigitMonth]
		= RIGHT('0' + RTRIM(MONTH(GETDATE())), 2)
--select first day of month
 SELECT	[FirstDayofMonth]
		= CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(GETDATE())-1),GETDATE()),101)
--select last day of month
 SELECT	[LastDayofMonth]
		= CONVERT(VARCHAR(10),DATEADD(DD,-(DAY(DATEADD(MM,1,getdate()))),DATEADD(MM,1,GETDATE())),101)

You can replace the GETDATE() with a variable to use this for any month.

Recover Management Studio from crash

Many a times my Management Studio hang up on me. Sometimes the files were automatically recovered but many times i had to loose the sql files i have been working on.
I always thought that there is no way for me to recover the files manually, but actually there is (atleast sometimes) a way for you to restore the files in case of a crash.
Browse to C:/Users/<YourUserName>/Documents/SQL Server Management Studio\Backup Files\Solution1.
If you are lucky enough you will find the sql files that you were working on at the time of crash
Note that you can’t recover files if you close them without saving, obviously.

Different background color for alternate rows

It’s a very common request to alternate the background color of rows in SQL Server Reporting Services. There is a simple way to this and we will see how.

In the properties window of the detail row(of the data region), find background color and choose expression. In the edit expression window, paste this expression.

=IIF( ROWNUMBER (NOTHING) MOD 2, “Silver“,”Grey“)

Click Ok and you are all set.

Row Constructor

I just discovered a very cool feature in sql server 2008 which i always wanted. With this feature called the row value constructor or the table value constructor you no longer have to write multipe insert statements or use the
INSERT INTO <TableName> SELECT <ColumnList> UNION ALL block to insert values into a table.

Row constructors/Table value constructors let you insert multiple rows into a table with single insert statement.

IF OBJECT_ID('#Employee') IS NOT NULL
DROP TABLE #Employee --Drop the table if it exists
GO

CREATE TABLE #Employee (EmpID INT, EmpName VARCHAR(50)) -- Create the table 

INSERT	#Employee     -- This inserts 5 rows
VALUES	(1,'JBauer'),
	(2,'DPalmer'),
	(3,'NCaffrey'),
	(4,'PCollingwood'),
	(5,'PFernandeso')

The above statement inserted 5 rows into the #Employee table. The row values have to specified in the VALUES clause of the insert statement and they should be in braces separated by a comma.

You can see the result by executing the below select statement

SELECT	EmpID,
	EmpName
FROM    #Employee

The row constructors can also be used in a derived table:

SELECT  EmpID,EmpName
FROM	(VALUES(1,'JBauer'),
        (2,'DPalmer'),
        (3,'NCaffrey'),
        (4,'PCollingwood'),
        (5,'PFernandeso')) AS DerTable(EmpID,EmpName)

Any constraints on the tables like the IDENTITY,NOT NULL or the DEFAULT can’t be overwritten when inserting using the row constructors. An explict value
can’t be insterted into an identity column and a NULL value can’t be inserted into a column with a NOT NUL constraint defined on it.

Row Constructor with NOT NULL and DEFAULT”

IF OBJECT_ID('#Employee1') IS NOT NULL

DROP TABLE #Employee1
CREATE TABLE #Employee1 (EmpID INT, EmpName VARCHAR(50) NOT NULL DEFAULT 'N/A')

INSERT #Employee1
VALUES	(1,'JBauer'),
	(2,'DPalmer'),
	(3,'NCaffrey'),
	(4,'PFernandeso'),
	(5,DEFAULT)

SELECT	EmpID,EmpName
FROM	#Employee1

While i don’t understand the limit of 1000 rows that can be inserted using this approach, there are some more meaningful limitaions like
1) The number of columns for each row in a table value constructor must be the same
2) The data types of the corresponding columns should be of the same domain.

Load data into a temp table using SSIS

Very recently i had to load a flat file into a temp table. I know this is not a good approach but i had no other option (atleast at that time). It was then i found out that this is not very straight forward. Below listed are the steps i followed.

Again in short, this is what i want to accomplish. Using a data flow task (DFT) in the SSIS package, i want to load data from a file to a temp table. As you can see the source of the DFT is a flat file and destination is a temporary table.

1) Create a OLEDB connect manager pointing to the SQL Server database, Create a flat file connection manager pointing to the source flat file.

2) For the OLEDB connect manager, in the properties pane set the RetainSameConnection property to true. This is an important part when working with temp tables in SSIS.
3) Configure an execute sql task to create a global temp table. This is my sample create statement

CREATE TABLE ##LoadTempTable(
[ProductKey] INT NULL,
[ProductName] VARCHAR(50) NULL,
[ProductType] VARCHAR(50) NULL )

4) Execute the create statement you used in the execute sql task (step 2) in the management studio to create the same table there. This way we will have the table available for design and column mappings.
5) Drag a DFT and set the DelayValidation property to true. Configure the source connection. Drag and edit the OLEDB destination connection. Specify the connection manager name.
6) In the data access mode select table or view. In the Name of the table or view field click new. You will see a create statement like CREATE TABLE [OLE DB Destination]…
7) Change the name of the table to your global temp table name.
8) Edit the mappings and you will be able to run the package.

Custom message when there are no rows in the data source

Some times when there is no data in the data source the SSRS report created on top of this will show only the header information. There is an inbuilt option to show your own message when this is the case. In this quick post i will demonstrate how to do this.First, i will create a dataset and make sure that this dataset doesn’t return any rows.

SELECT    TOP 0 FirstName, LastName, [State]
FROM dbo.ResultTable

If you take a close look at the above query, i used TOP 0 to make sure that the dataset returns 0 rows. Now that we have the dataset lets go ahead with the report design. I created a table report and the fields are First Name, Last Name and State.

Since there are no rows returned when you run the report we will see only the report headers.

Now to display a custom message when there are no rows there is a property called “NOROWS”. Select the table and in the properties pane find NOROWS.

Click on the expression button and enter any custom text.

Now that you have the NOROWS property configured you will see this message when you run the report and there are no rows returned by the dataset.

Conclusion: NOROWS property in SSRS can be set to display a user friendly message when the data set doesn’t return any data.