Kasper de Jonge BI Blog

Brian Knight has come up with a really nice, clean solution which leverages a helper assembly to do a drill through to the database, real easy to setup and use.

Download the assembly here:

http://blogs.pragmaticworks.com/brian_knight/2009/09/creating-a-ssas-rowset-action.html

Great work Brian!

Found at: performancepointblog.com

I think all business users want to see alternating colors in on  rows from your tablix. This is not a default function in reporting. After a evening of searching the internet and playing around i found the answer thanks to a post by Teo Lachev here: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/18b5a5f7-7304-4d61-821e-7faa344ce964/

How does it work:

 The solution takes advantage of the expression evaluation rules in RS 2008 that have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics.

1. The report has a EvenRow code-behind function that toggles each time it’s executed.
2. In the Category group (double-click it to access its properties), a EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.

The rest is easy. I set the BackgroundColor property for each textbox to use this variable.

 Practical:

• Go to report properties and then add the evenrow function to the Code box (just a simple state toggler):
  Public _evenRow As Boolean
  Public Function EvenRow() As Boolean
      _evenRow = Not _evenRow
      return _evenRow
  End Function
• Go to the row group properties you want to alternate, go to variables, add the variable “EvenRow” with the function “=Code.EvenRow()”, this will toggle each time the row is called upon
• Now go to the text box properties of the row in the group you want to alternate, go to fill, fill color and add the function “=IIF(Variables!EvenRow.Value=true,”Red”,”Green”)”

Thats it the background color will alternate each time a new group element is started and this is the result:

New in SQL Server 2008 is the RDLOM, in which you can use  Microsoft.ReportingServices.RdlObjectModel.dll to create your RDL’s programmatically within Visual Studio. Apears to be a very powerfull tool!

A few things to keep in mind:

• Not officially supported
• Use at your own risk
• Doesn’t validate RDL semantics

 Theo Lachev has a free sample from his book which you can use to get started:
http://prologika.com/Books/0976635313/Resources/amrs2008_code.zip

I needed to test my SSIS import package which reads files every minute, to test this i want a program that moves files from one directory to the SSIS import directory, the filename has to be sorted on filename (it consists of datetime) so i get a more real import.

I first tried to create a SSIS to package that can do what i want but after some searching that i decided to create a simple C# file.  I found this great post to help me sorting my filenames using IComparer:
http://webdevel.blogspot.com/2007/09/sorting-files-by-name-date-filesize-etc.html

Ok while not realy BI (not yet anyway till Gemini) its a topic i am interested in: “data in the cloud”. This blog identifies five problems when starting to use cloud services,a interesting read:
http://smartdatacollective.com/Home/17990

I have created a package with a for loop that runs for a few hours and process incoming files , but when a file is wrong and the processing gives an Error I want the for loop to continue with the next package but still log the error. To do this you have to play around with the Propagate variable. The Propagate variable controls whether the event is raised to the next container.

I have created 2 separate packages, one for the file watching and one for processing, the process package is executed from a execute package task, on this task I have created a OnError eventhandler and in this eventhandler set the propagate error to false.

This means when the package throws an error it will follow the Error constraint to enable you to for example send a mail or move the file but it won’t bubble the Error to the superseding task allowing the package to keep running.

Read more at: http://social.msdn.microsoft.com/Forums/pt-BR/sqlintegrationservices/thread/a8c841bf-8899-415f-a91f-1a4b17db6917 and http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx

When developing a ETL package a lot of work goes into creating test data, usually you load a file in SSIS and enrich it with data. It could be very useful when you could script this test data so you can recreate it on thy fly or share it with you colleague developers.

Using SQL Server management studio you can generate a script from the data in your tables. Go to your database, right mouse click, Tasks and click Generate scripts

Click next on the splash screen, select your database, at the script options screen set the select script data value to True, set all other properties to false.

Click next, On the next screens select all tables you want to select and click the desired output and click finish. It will now create your data script.

When rendering reports, oftentimes the application would like to minimize as much of the non-report area of the viewer control as possible.  This is easily accomplished when rendering the report through the built-in report viewer control hosted in the ReportServer web site (http://servername/reportserver). 

Read here how to call your reports to hide the parameters: http://blogs.msdn.com/jgalla/archive/2009/03/23/hiding-parameter-area-when-viewing-reports.aspx

Alexander kuznetsov of sqlblog.com has made a summary of previous posts about defensive database programming,defensive programming is an approach that is designed to ensure high quality of software by eliminating existing bugs and avoiding potential ones. The goal of defensive programming to make sure that software robustly and gracefully handles cases of unintended use. Programming defensively includes the following techniques:

• Explicitly list the assumptions that you made during development
• Whenever feasible, remove assumptions that are not essential
• Ensure that the essential assumptions always hold
• Use all your imagination to come up with cases of unintended use, trying to break your module. Incorporate these cases into your testing suite.
• Lay out your code in short, fully testable, and fully tested modules
• Reuse your code whenever feasible

Read the entire post here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/summarizing-previous-posts-about-defensive-database-programming.aspx

Brian Hartman has created a sample apllication with the ReportViewer built-in print functionality, people often wish to implement their own version to provide significant customizations or a deeper integration with their application. Great for all the developers who want more than the default reporting services. Attached to his post is a sample PrintDocument that will programmatically print a given ServerReport or LocalReport.

Read it here:
http://blogs.msdn.com/brianhartman/archive/2009/02/27/manually-printing-a-report.aspx