Friday, December 21, 2007

SSIS Derived Column IIF/IF Expression

This is for my memory, but I figure if I have a hard time remembering, maybe someone else will...

For those of you who are trying to remember how to do IF/IIF functionality in the SSIS Derived Column control:
({comparison} ? {truepart} : {falsepart})
So, if I want to evaluate the similarity from a fuzzy lookup, it would look like this:
(_Similarity > .78 ? ID : "")

Maybe someday Microsoft will standardize all of the expression languages in their BI tools!


Tuesday, December 18, 2007

Disappearing Parameter Defaults

I ran into an interesting problem today with default parameter values.
I had a report with 6 parameters. One of them, it happened to be Language, had a default value of 1 in the parameter window. The rest had values from datasets. Once deployed, if you chose a data driven parameter, the language default would disappear and ask you to select a value. This was very inconvenient, as it was a hidden parameter in this report.

After googling and looking in the forums, I realized this is an ongoing issue in SSRS. So I had to come up with a workaround.

I played around with it for a while, and figured out a way to make it work. I created a dataset called DefaultLanguage, with the SQL:
Select 1 as ID
I then set the default for the language parameter to that dataset.

Now when any other parameter is changed, the default language stays selected!


Tuesday, December 11, 2007

Parameter Prompts Go Prime Time

I just was reading Teo Lachev's blog and saw this entry: Parameterized Parameter Prompts.

SQL 2008 will allow expressions on the parameter prompts!!! This means that in my article Multi Language Reports, you will be able to update the prompts to different languages. :-D

Check out Teo's other blogs too... He has been working with the new features in SQL2008, something that I hope to be able to get around to soon.


Wednesday, December 5, 2007

Javascript in Jump To URL

You know that if you right click on a textbox, select Properties, and then click on the Navigation tab, you can put an URL into the Jump to URL textbox, and when the user clicks on the data in that textbox it will jump to that URL in the same window as the report. But did you know that you can use javascript in there as well?

This allows you to open the link in a new browser window or display a pop up a message box.

I will sometimes use pop up message boxes on the column headers to define what the data definition is. This is the syntax:
="javascript:void alert('This is a test column header alert')"

To open the link in a new window, use syntax similar to this:
="javascript:void'http://servername/reportserver/pages/reportviewer.aspx?%2freportfolder%2fReport+Name&Parameter1=" & Fields!Field1.Value & "&Field2=" & Fields!Field2.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"