Monday, December 21, 2009

Working With NULLS

In SQL Server one of the interesting to deal with is NULLS while programming in T-SQL and also design of tables. Very recently i ran into an issue with NULLS, the scenario was in the context of T-SQL. The business rules were such that I had to compare values, so the SQL was constructed. Once the stored procedure was complete, it was time to unit test the stored procedure. On executing the stored procedure I was not getting the desired results, the expected the result was there were rows that should have got updated in the table. On debugging the sql, the problem area was comparing the two numeric values, the condition was like CurrentAmount <> InputAmount. Here there were 6 rows in the table which had NULLs in the currentamount and the other 10 rows which had values, when comparing NULL to a value the result is an Undetermined state this caused no rows to be updated. The expectation was 16 rows would be updated, in fact after a little bit of examining, the ISNULL function was used on the arguments on both sides of the condition. The check was modified to ISNULL(CurrentAmount,0) <> ISNULL(InputAmount), once this was done, I got the desired results. While working with NULLs it would be good to keep in mind the undetermined state while comparision.
The above situation arises when:
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:
When ANSI_NULLS IS OFF, the above behavior mentioned is not applicable.

Thursday, December 17, 2009

SSRS:Master-Detail Reports...



In SSRS 2008 there are different ways to create Master detail reports. One of the strategies adopted to Master Detail was to use the Javascript Windows.Open function. For this function the URL string has to be built, in case the detail report requires parameters, the values for the parameters need to be passed and built as well. For example if there is a Column called SalesPerson, the users wants to click on the sales person's name and view the details of his sales transactions. In SSRS , in the tablix control right click on the cell containing the Salesman name. Choose the Option TextBox Expression.





This opens up a window, on the left there is a option Action, click on this. You will be presented with a list of options, choose Go to URL, below that there will be a textbox with a button on to the right, click on it.





This will open the expression window where you will type:
="javascript:void(window.open(" & Code.GoToDetail("XYZ","Manager",Fields!SalesmanId.Value,Code.ShowParameterValues(Parameters!as_accttype),Code.ShowParameterValues(Parameters!as_city)) & ",'_Blank','Toolbar=no resizable=yes'))"

In the above example the windows.open calls a function called GoToDetail which is defined in the Code Section of Report Properties. In the GotoDetail function, one would build the URL String to access the detail report. The URL construct would like:


URL = "'" & ReportPath & ReportName & "&rs:Command=Render&rs:Format=" & RenderFormat & "&rc:Parameters=false" & as_Type & as_Section & as_accttype & as_city & "'"

The URL String returned from the function. When the user runs report, under the SalesmanID column the icon would change to pointing finger. When the user clicks on the SalesmanID value it would open the Detail Report. In case one has dual monitors the master and the detail report can be compared side by side.

Thursday, December 10, 2009

PowerShell

One of the new features to surface in SQL Server is PowerShell. It is a scripting lanaguage which is very powerful altrnative for VB script/SQL-DMO. PowerShell has lot of commands and functions which are very useful for database connectivity,querying and file movement and copying of files. The Powershell is also incorporated into SQL Server Jobs as a type which means that Powershell scripts can be executed with a sql server job. Once logged into SSMS, one can start the PowerShell by right clicking on the databases, this will take to the PowerShell Prompt, which looks likes a DOS-Command prompt window. In this window, Powershell commands can be executed, this can be used as a good testing ground for building PowerShell scripts. Here is a link to an excellent link on PowerShell tutorial:
http://www.powershellpro.com/, the lessons are very well laid out with lots of examples:

Here is a sample of how a Powershell script will look like: For example, if the need is to delete files which are more than 3 days old:


$Now = Get-Date
$Days = "3"
$TargetFolder = "Your Path Where the Files are Located"
set-location $TargetFolder
$ModifiedDate = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolderWhere{$_.LastWriteTime -le "$ModifiedDate"}
If($Files -eq $Null)
{exit}
Else
{
ForEach($File in $Files)
{Remove-Item $File}
}

Tuesday, December 8, 2009

PowerPivot

While i was reading Pinal Dave's summary on PowerPivot in his blog(blog.sqlauthority.com), It made me curious and wanted to check out what the product is and what would be the features. Microsoft has put a out a nice website with the download option along with videos and try out Powerpivot hands on in the virtual lab. Here is the web site:
http://www.powerpivot.com/
I decided to check out the virtual lab of powerpivot. It was a nice tutorial lab experience to begin with. The lab takes you through how to launch the powerpivot window and connect to a sql server database. Then there is a series of steps in the lab manual which appears on the side. This instructs the user as to how to go about creating a Pivot table report along with a chart. Once i went through these steps it became evident that it is going to give the Power Users/Analysts of an organisation a self service BI component. The users can use Powerpivot to build out very useful Pivot tables/charts, it also gives the ability the user to refresh the reports/charts real time. The one thing i noticed as i was going through Powerpivot was that the interface could take some time to get used to along with plethora of other options. I guess main deal with the user interface is the office 2010 layout along with the placement of options. The lab also takes the user through on how to create a powerpivot report using data from the web. I guess with this way of extracting data from the web, it leads the way to accessing unstructured data present on the web. In the final analysis based on the initial looks Powerpivot is a powerful tool for doing BI for power users. It will be interesting to see how Powerpivot coexists with sql server analysis services in terms of how the lines will be drawn between the two tools in terms of usage.

Monday, December 7, 2009

SSRS 2008 - Tablix Control

One of the neat features available in SSRS 2008 is the tablix control. This control combines the table control and matrix control which were in the earlier versions of SSRS. These controls (Table and Matrix are still present in the current version). Matrix controls in SSRS is bit tricky to work with, tablix control on the other hand helps us generate complex reports in a efficient manner. One of the neat features available in the tablix control is presence of row groups and column groups. The row groups work like the report groups we have for the rows of data. For example in a Data set let us say we have SalesPerson,Quarters and Salesamount has the fields, once could have a row grouping on sales person on the tablix control. This could generate a report with sales amounts for different sales persons. Let us say in the same report the user wants the amounts to be broken out by quarters, one has to create a column grouping based on quarters. This way the user can display sales person and quarters on the same report with salesamount broken out by different quarters. This kind of groupin features also allows the use of total columns where in the sales amount can be summed across different column groups. This kind of combination grouping of row and column groups can be used to develop very complex reports. Below are snapshots of the tablix control: