SQL Scrapbook

Short and simple SQL Server posts

SSRS PageName Part1/2

leave a comment »

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.

clip_image002

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

clip_image004

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.

clip_image006

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

clip_image008

Written by sqlscrapbook

March 26, 2011 at 11:16 am

Posted in Uncategorized

How to generate RDL file from report manager?

leave a comment »

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.

Written by sqlscrapbook

December 28, 2010 at 8:27 pm

Posted in SSRS

Tagged with ,

Keys to(in) Data Warehousing

leave a comment »

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.

Written by sqlscrapbook

November 23, 2010 at 4:01 pm

Posted in Data Warehousing

Degenerate Dimensions

leave a comment »

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.

Written by sqlscrapbook

November 23, 2010 at 1:50 pm

Posted in Data Warehousing

SQL Date Cheat Sheets

leave a comment »

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.

Written by sqlscrapbook

October 20, 2010 at 7:46 pm

Posted in TSQL

Recover Management Studio from crash

leave a comment »

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.

Written by sqlscrapbook

July 28, 2010 at 7:22 pm

Posted in General

Different background color for alternate rows

leave a comment »

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.

Written by sqlscrapbook

July 24, 2010 at 1:32 pm

Posted in SSRS

Tagged with