How to pass NULL value to a Multi value parameter in SSRS?

This week I tried to build a SSRS report with a parameter which can have multiple values including the NULL value. This is not possible by default in SSRS. When you configure the parameter with option: Allow null value and Allow multiple values. You will receive next error message:

A multi-value parameter cannot include null values.

The reason for this is the TSql syntax of the underlying dataset.

SELECT Column1, Column3
FROM TableX
WHERE Column2 IN (@MyParameter)

This query will fail for the NULL value because IN syntax is in conflict with NULL value. TSql syntax for NULL values is

SELECT Column1, Column3
FROM TableX
WHERE Column2 IS NULL

Solution:
We are going to use the ISNULL function to change parameter values to a blank value (''). Assume you have a dataset to retrieve parameter values for @MyParameter.

SELECT DISTINCT(Col1) FROM TableB

Change this query to:

SELECT DISTINCT ISNULL(Col1, '') AS Col1, ISNULL(Col1, 'NULL') AS Col1Description
FROM TableB
Change the Report Parameter Properties to: Allow blank values ('') and Allow multiple values.
Change the available values: Value field: Col1   , Label field: Col1Description.


Now we need to change the query of the dataset.
-- Original query
SELECT Column1, Column3
FROM TableX
WHERE Column2 IN (@MyParameter)

-- Changed query
SELECT Column1, Column3
FROM TableX
WHERE  ISNULL(Column2,'')  IN (@MyParameter)

That's all, enjoy it to make your report more user friendly.

Register your hours spent really simple and fast in SaaS solution Exact Online.

In the past I blogged about 8 reasons why I uses a Saas accounting solution for my financial administration. Up till today, I still uses Exact Online with a lot of pleasure for my financial administration. With the introduction of Exact Online Time & Billing in november 2011, we started to use this module in my company for the hour registration of different development teams. As you can see in the title of my blog, I like solutions which are Simple And Fast. The Time and Billing iPhone app is one one of these solutions I like because it is Simple and Fast. Registering my hours is a matter of seconds. Start the app and press the hours symbol.

Now I got a list of all days. The days for which I need to register my hours can be recognized by the plus symbol. Press on the plus symbol.

For the selected day, I can specify the Project, Relation, hour classification and the hours I have spent.

Press on the green submit (Indienen) button and you are done.

Within 1 minute I have entered the hours I spent for the whole week on my iPhone. Hour registration is now fun instead of a boring activity. This app is also available for Android.

The iPhone app can be download via this link or scan this QR tag. Of course this app will also work on the iPad.

More information about the Time and Billing solution can be found here (unfortunatly Dutch only).
Have fun with the registration of the hours you have spent.

Argument data type nvarchar is invalid for argument 2 of dateadd function.

Situation:
I have build a query, using the DATEADD function, which executed without any error in SQL Server Management Studion (SSMS). I want to use this query as a dataset in my SQL Server Reporting Service Report (SSRS). I paste the query in the query designer window of my dataset. After I press the OK button of the Query designer, I got next error message:

Argument data type nvarchar is invalid for argument 2 of dateadd function.

This is the part of the query with the DATEADD function:
(SELECT DATEADD(HOUR, @MeasureHourStart, @MeasureDateStart) AS XXXX)

In my report I have report parameters @MeasureHourStart (INT) and @MeasureDateStart (DateTime)

Solution:
SSRS still sees @MeasureHourStart as a Varchar value.

As a workaround I forced to set the value of @MeasureHourStart as an Integer by using the CAST function.
(SELECT DATEADD(HOUR, CAST(@MeasureHourStart AS INT), @MeasureDateStart) AS XXXX)

Now the query will be accepted by SSRS as a valid dataset.

My first experience with Power View of SQL Server 2012 (febr 2012)

In the past I blogged about SQL Server Project "Crescent" Denali. This project will come general available in SQL 2012 named Power View. Last week I installed SQL 2012 RC0 to experience the Power View capabilities. First of all I will give some characteristics of Power View which will give you an idea what you can expect from Power View.

• Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.
• Web-based report designer. So, this will be the fifth report designer after BIDS, Report Builder 1.0 (not sure if RB 1.0 will survive SQL 11), Report Builder 3.0, Visual Studio Report Designer.
• Reporting experience will become more interactive and similar to Excel PivotTable. The end user can quickly create an interactive report by dragging metadata, and then with a few mouse clicks change the report layout without switching to design mode. This is completely different in comparison to a Reporting Services report which is always static.
• See next blog post for a perfect demo of Power View.

 To install Power View you need to have 2 installation sets:

• SQL 2012
• SharePoint 2012 SP1

Use next blog post to install Power View: Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3). It will explain the sequence to install which components. After completing the installation, I installed the Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012 Release Candidate 0 (RC0). The HelloWorldPicnic samples are very useful to explore the new capabilities of Power View. After this I used the Power Pivot sheet on a Globe database I created in the past. This sheet enables me to play with data I know.

Having done this, it is now time to talk about my experiences with Power View. I will start with a list of GREAT user experience examples:

• You can really 'play' with the data without changing and redeploying your report.
• Highlight of selected values, see next example
  
  
  
  
  
  
  
  
  
  
  
  Left chart is the revenue and margin per year of the company. Right chart is the revenu per employee over the years. Select one employee in the right chart and you will get:

Now you will see what the amount of Revenue and Margin this employee has made in comparison with the total amount of revenue made by the company.
• Pop out. Every visualization has an pop out option. This will show the visualization full screen so you can better look to the details. Press on the Pop In button to go back to the report.
• Tiles. Another way of selecting data. Selected data in the tile applies only to the visualization the tile belongs.
  
  
• Play Axis on a bubble chart. Press on the Play button so the bubbles move, grow, and shrink as their values change over the months. Note that the months display as watermarks in the upper-right corner, and the bubbles move to the right as the months pass and the year-to-date quantity increases.
  
  
  Click on the city Assen and you will see how the city 'Assen' moved over the years.
  
  
• Interact in Power Point. You can export the Power View report to Power Point 2010. Each Power View view has been added as a separate slide. In PowerPoint design mode, each view is static. After you open the Power Point presentation in presenter view, you will get a button in the right bottom on your presentation called: Click to interact
  
  
  As long you have connection to the sharepoint server on which report is deployed, you can interact with the view in the same way that you can in reading and full screen modes. So you can play with the data in the Power Point presentation itself.

This is the first release of Power View with a lot of interesting interactive reporting features. Next to this nice features I will give a list of possible future improvements:

• Installation is complex. It is not 'out of the box' like a one click installation.
• Additional knowledge about Sharepoint 2010 SP1 is needed. A big part of the SQL community do not use Sharepoint 2010. The installation of SQL Server itself is already complex enough for them.
• Silverlight is needed. This means Power View will not run on the IPad. Power View is, for instance, made for the people in the board room. However a lot of them are using IPad's as of today.
• Visualizations can not be customized like you can in SSRS. For instance, Fonts, Colors, Borders  etc. It is not possible to apply your style guide.
• Default colors are sometimes in conflict with dashboard design rules. One example: The use of the color Red. The first color in charts is blue, the second color is red. According to the dashboard design rules, red is used to get attention for something. In next example, it is not intended to give extra attention to the Amount of Margin. Using red it looks like something is going wrong with the Amount of Margin.
  
  
• Automatic alignment of chart horizontal or vertical is not supported.
• Report is based on a metadata layer. You do not have datasets. To use Power View on a database you first need to develop a metadata layer.
• Existing SQL 2008 R2 reports (RDL) can't be converted to the Power View format RDLX. You need to build them from scratch. Of course you can use your SQL 2008 R2 RDL's in the traditional reporting service SSRS of SQL 2012.
• Power View is not available in SQL Azure Reporting. When Power View is available in SQL Azure Reporting we can get rid of the Share Point knowledge need.

Enjoy your Power View journey.