SQL Server Reporting Services [SSRS] – Error – Null is not declared. ‘Null’ constants is no longer supported; use ‘System.DBNull’ instead


It is a little bit of work to use null-able arguments in Sql Server Reporting Services (SSRS).


In DataSet Properties, Tried using “=(Null)”

For the dataset, when we set the parameter value to =Null




Error - Null is not declared.  'Null' constants is no longer supported; use 'System.DBNull' instead



In Dataset Properties, tried using “System.DBNull”

In Dataset properties, When we set the parameter value to =System.DBNull


Error: [BC30691] 'DBNull' is a type in 'System' and cannot be used as an expression.


In Dataset Properties, tried using “=Nothing”

Tried setting the parameter value to:

  • =Nothing
  • =System.Convert.DBNull




We get an error.


In Designer, when trying to preview the report.


One or more parameters required to run the report have not been specified





Report Parameter – Default Value – Setting Parameter Value

  • Access the parameter
  • Access the “Default Values” panel
  • In the “Default Values” panel
    • In “Select from one of the following options” radio group, choose the “Specify values” option
    • In “Value” set “(Null)



Does not help

Dataset Properties – Report Parameter

Let us remove the parameter


Here are the dual parameters.



We have removed the second parameter.


Error Message:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified (2nd Parameter removed)

Report Parameter – Set Value to Empty

Let us go set the parameter’s value to “” or System.String.Empty.


Things are good.

SQL Server Profiler

Here is what we see in SQL Server Profiler:


Transact SQL

In Transact SQL, make sure of the following:

    • The arguments is nullable — (variable [datatype] = null )
    • That you have same code line for when the variable is null and empty

 Sample Code:

if object_id('[dbo].[usp_city]') is null

    exec('create procedure [dbo].[usp_city] as select 1/0 as [shell] ')


alter procedure [dbo].[usp_city]
      -- make sure @countryCode is nullable
      @countryCode varchar(255) = null

     --make sure that @country is nullable
    , @country     varchar(255) = null

            , vc.countryCode
            , vc.city

    from   [dbo].[vw_city] vc

    where  (

                                = case
                                        when (@countryCode is null) then vc.countryCode
                                        when (@countryCode = '') then vc.countryCode
                                        else @countryCode

                                = case
                                        when (@country is null) then vc.country
                                        when (@country = '') then vc.country
                                        else @country


    order by
                  vc.country asc
                , vc.city




It appears that SSRS does not really support the setting of parameters to Null, but it supports empty string.




SQL Server Reporting Services–Error Message–“Invalid value ‘Outside’ for the custom attribute ‘Outside’”


Here I am working on some reports and I ended up with the error message:

      Invalid value ‘Outside’ for the custom attribute ‘Outside’

How did I get that far off line

While waiting for my next train, I just ran into my play sister.  As we spoke the question of whether her parents are strict came up.

And, she replied “Ain’t all Caribbean parents like that”.

So here is my take, my graph used to look like this.


It is obviously a Column Chart; on the other hand, it might not be so obvious that in some cases the labels will be placed inside the bars; while in

other cases they will be placed outside.

In our captured graph, our data points are shown inside for the first two numbers; 246 and 244.

SSRS Version

To keep this honest, we are developing using SQL Server Data Tools – v2012 ( SP1-CU4).


Setting Chart Label’s Position Outside

For the sake of consistency, let us go set the label’s position to always be on the outside.


Error Message

The error message below is shown where the graph should have appeared



To remediate, please run back and revert to Automatic.

The proper trail to take is to set via “Custom Attributes”:


Available Styles

Within the label style drop-down, here are the available choices:


We can see that the disallowed choice of “Outside” is not included.

Pie Line Style

One of the cases where the Outside Position is valid, is when the graph’s type is Pie.

Custom Attributes:

For Pie Charts, the Label’s position is called “PieLabelStyle”.




Source Code

Here is the Source Code:


Quick Explanation:

  • Label Position is set through the
    • Chart Series \ Label \ Position property, it is stored in ChartDataLabel \Style\ Position element
    • Chart Series \ CustomAttributes \ LabelStyle property, it is stored in ChartDataLabel \Style\ Position element



All the years, I have been listening to this song….  And, it is only now that I heard this line (at the very end of the song)!


Somethings that happen, you never really know,

Just like it says in the bible, it happens for a reason

You got to have faith


Where will you be Today


I think back to my sister’s wise words:

Yes, I agree with my Dad.  After seeing all the problems other kids have, I like the fact that my parents are more structured

Enough about her.

I really would not have had my problems if I had gone about setting the Label Position \ Style via the more concise trail of “Custom Attributes”.

Notwithstanding what all the kids gang up to say, “more is not always better“.

Visual Studio (v2010/v2012) – Designing SQL Server Report Server Reports


As always this is one born out of frustrations. I need to make changes to quite a few of Microsoft SQL Server Reporting Services Reports (v2008).

Our current Microsoft Studio development environment is 2010 and so I launch that GUI, but I am not able to create a new Report Server Project.

I thus went back and started Visual Studio 2008 and I am able to create new Report Server project from VS 2008.

But, one big caveat for me!

No matter what I try, once I start on a new Project the Report Server files (RDL) are copied unto a folder underneath the new Project.

This is going to cause problems for me as we have a strict folder hierarchy mapping that is already in place and under Team Foundation Services (RDL) armor-ship.

And, so if use VS 2008, I have to keep copying files from my working folder to the actual Project\TFS folders.

Too much hapless work; especially the way I am feeling these days!

Problem Discussion

Here is what I see when I try to link to an existing file in VS 2008 and VS 2010:



Here is what I see when I try to link to an existing file in VS 2012:


Please keep in mind that there is an arrow right by the “Add” button.  If you click on that Arrow, you will be able to see and activate the Link option.

With the Link option, I am to keep\preserve my strict folder placements.

Download & Install SQL Server Data Tools (SSDT)

Goggled for help and told to try SQL Server Data Tools.

Get Download Link for SQL Server Data Tools (VS 2010) Integration

SQL Server Data Tools is available @ http://msdn.microsoft.com/en-us/jj650014.  Please choose to download the full blown ISO Image.

BTW, SSDT for VS 2012 is available @ http://msdn.microsoft.com/en-us/jj650015


Installation is quite straightforward.


Let us validate inside Visual Studio 2012.

In Visual Studio 2012, a new Template Option for SQL Server Data Project is present, but nothing per SQL Server Reports.

More Goggling

More goggling led me to Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012.

Download SSDT – Business Intelligence for VS 2012

Downloaded and SSDT for VS 2012 from http://www.microsoft.com/en-us/download/details.aspx?id=36843.

Install – Business Intelligence for VS 2012


Interestingly enough VS 2012 is bundled as an executable, I was looking forward to having to mount another ISO or burning the ISO unto a DVD.


Here are some of the install screen shots:

Product Updates – SQL Server 2012 – SP1

Yes, chose that it is OK to apply SP1 to my local SQL Server 2012.


Installation Type – Add Features

I am really not gunning for a new install, just update my existing install.


Features Selection

Yes, I want “SQL Server Data Tools – Business Intelligence for Visual Studio”


Installation Configuration Rules (Blow-up)


I got the error message pasted above:

Rule “Same architecture installation” failed.

The CPU architecture of installation feature(s) is different than the instance specified. To continue, add features to this instance with the same architecture.
And, I am thinking how to get over this hump.  Yes, my machine is 64-bit.  But, there is only one installation file and there was nothing stating that bitness specific.

Thanks Goodness for Google as it proved useful.

The right search tags led me to Graham Lannigan’s blog:
Installing SQL Server Data Tools – Business Intelligence for Visual Studio 2013
And, his suggestion is that I should navigate back to the Installation Type page and choose to “Perform a new Installation of SQL Server 2012“.



Did so and everything installed properly the 2nd time around.  Makes me go back and remember Shalamar – Second Time Around.

Install Completed

Restart Computer?

Prompted to restart computer, but I happily said NO.

Validation (2nd)

Let us validate inside Visual Studio 2012.

In Visual Studio 2012, we now have Template Options for “Business Intelligence”.  We have Templates for Report Server, Integration Services, and Analysis Services.


Confirm Target Choices

As I said earlier, I really can not afford to break the build.  Whatever I do on my machine is OK, in as much as I do not break the reports; they need to be backward compatible.

Project Settings

In Visual Studio 2012, once I have chosen to create a new project let us ensure that we can “target” SQL Server 2008 R2.

Thankfully the Report (RDL) files themselves are XML, but I need to get a bit of sureness that not much changed between 2008 R2 and 2012.


The return TargetServerVersion options are:

  • SQL Server 2008
  • SQL Server 2008R2 and later
  • Detect version

My read is that indeed there were changes between 2008 and 2008R2.  But, 2008R2 and 2012 stayed same.


There are a couple of items still outstanding.

  • What path shall I take to be able to work on Reports in VS 2010

I know I could have avoided most of this problem by going with SQL Server Standard Edition, but license terms for Developers are a bit mucky for me at this time.

Thanks Microsoft for SSDT.  But, honestly the Setup and QA team could and should have done a better job cleaning up the verbiage for installing SSDT.

What does perform a “New install of SQL Server 2012” mean when we are simply trying to add a feature to Visual Studio?


One good thing about writing is that you can not but give credits.  In real life it is so easy not to say Thank you.

Anyone can copy someone else’s web page and write it down as theirs.  But, if you take the time to read Graham Lannigan’s blog post @ http://picnicerror.net/development/sql-server/installing-sql-server-data-tools-business-intelligence-for-visual-studio-2013-03-13/ you will quickly see that he cares about his work and takes time to jot down each step.

He says I took that fake and stumbled a bit for it.  Almost, like a Jordan fake that ends up on Sportscenter or these days YouTube.  And, this is why I did so and why you shouldn’t…

And, that is what learning and sharing is all about!


References – Installation

References – Visual Studio Templates

References – Presentations