This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Friday, September 18, 2009

Money vs Decimal in Sql Server

I normally use the Money data type when designing databases , as I've always read that it was specifically designed for currencies. One of the advantages , for example , is that you can use a currency symbol with it. Recently I came across something very interesting while browsing the net. Apparently there are accuracy issues with the data type. I have below a simple example to demonstrate. Note that this isn't my example but something that I found on the web which I thought I'd share :

declare @m money
declare @d decimal(9,2)

set @m = 19.34
set @d = 19.34

select (@m/1000)*1000
select (@d/1000)*1000

So what would the results be ? Well if you're expecting 19.34 for the money variable , you'd be wrong !! You actually get 19.30. Yup, I was surprised as you are right now. I've even tested this in the Katmai CTP and it does the same thing. So from now on I'll try to use decimal and specify the precision I need , when creating tables.

Thursday, September 17, 2009

How to configure drill through reports open in a new window in SSRS 2005

When using the standard Reporting Services drill-through navigation the new report is rendered in the current browser window, therefore overlaying the original report. There is no built in way to indicate you want the drill-through report to open a new window. In a standard web browser you can use the (shift-click) functionality to open the link in a new window, but in Reporting Services this functionality is disabled. So how can you launch a new window when you open a drill-through report?

Solution
Use a little javascript with a customized URL in the "Jump to URL" option of the Navigation tab.

Non-parameterized Solution
To get started, let's pop up a simple non parameterized report. Follow these instructions:

1. Instead of using the "Jump to Report" option on the Navigation tab, use the "Jump to URL" option.
2. Open the expression screen (Fx button).
3. Enter the following:
--------------------------------------------------------------------------------
="javascript:void(window.open('http://servername?%2freportserver%2fpathto%2freport&rs:Command=Render'))"
--------------------------------------------------------------------------------
4. Click OK twice, then save and deploy the report.

Note: The link will not work from the designer environment. You must deploy the report (hopefully to a test area) to try the link.

See screenshots below.





















Parameterized Solution
Now, let's say you want to pass a variable to the drill through report. Let's also assume you have a field called ProductCode. Normally, you might hard code that like this:

http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode=123

In this case, you want to pass variables dynamically, using an available value from the source dataset.

You can think of it like this:

http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode=Fields!ProductCode.Value

The exact syntax in the "Jump to URL" (Fx) expression window will be:

--------------------------------------------------------------------------------
="javascript:void(window.open('http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode="+Fields!ProductCode.Value+"'))"
--------------------------------------------------------------------------------

Note - To avoid confusion between double and single quotes, double quotes are in red above.

Notice, that the static portion of the URL is delimited within double quotes.