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

Background

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

DatasetProperties-Parameters-(Null)

 

 Textual:

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

NullConstantIsNoLongerSupported

 

In Dataset Properties, tried using “System.DBNull”

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

DatasetProperties-Parameters-System.DBNull

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

Image:
DBNullIsATypeInSystemAndCannotBeUsedInAnExpression

In Dataset Properties, tried using “=Nothing”

Tried setting the parameter value to:

  • =Nothing
  • =System.Convert.DBNull

 

DatasetProperties-Parameters-Nothing

 

We get an error.

Error

In Designer, when trying to preview the report.

Textual:

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

 

Image:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified


 

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)

ReportParameter-DefaultValues-SpecifyValues-Value-(Null)

 

Does not help

Dataset Properties – Report Parameter

Let us remove the parameter

Original:

Here are the dual parameters.

DatasetProperties-Parameter-Null-Included

Revised:

We have removed the second parameter.

DatasetProperties-Parameter-Null-Excluded

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.

DatasetProperties-Parameter-SetToEmptyString

Things are good.

SQL Server Profiler

Here is what we see in SQL Server Profiler:

SQLServerProfiler-ParameterValueToEmpty

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
begin

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

end
go

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

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

    select
              vc.country
            , vc.countryCode
            , vc.city

    from   [dbo].[vw_city] vc

    where  (

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

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

           ) 

    order by
                  vc.country asc
                , vc.city

go

 

Conclusion

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

 

References

SSRS

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

Background

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.

ActivitiesByRoom

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).

MicrosoftSQLServerReportingServicesDesigners

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.

ChartData.Legend.Label.Position

Error Message

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

InvalidValueOutsideForTheCustomAttributeOutside

Remediate

To remediate, please run back and revert to Automatic.

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

CustomAttributes

Available Styles

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

CustomAttributeStyles

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”.

Properties-PieChart-Series-Attributes

Choices:

PieLineStyle

Source Code

Here is the Source Code:

SourceCode-XML-ChartDataLabel-Position-Outside

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

References

Listening

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)!

Lyrics

Somethings that happen, you never really know,

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

You got to have faith

Video

Where will you be Today
http://www.vevo.com/watch/kenny-chesney/Who-Youd-Be-Today/USBVA0500022

Summary

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

Introduction

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:

AddExistingItem-VS2010

 

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

AddExistingItem-VS2012

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

Installation is quite straightforward.

Validation

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

Media

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.

Install

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.

ProductUpdates

Installation Type – Add Features

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

InstallationTypeExisting


Features Selection

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

FeaturesSelection

Installation Configuration Rules (Blow-up)

InstallationConfigurationRules

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
http://picnicerror.net/development/sql-server/installing-sql-server-data-tools-business-intelligence-for-visual-studio-2013-03-13/
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“.

 

InstallationTypeNewInsallation

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

Install Completed

Complete
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.

BusinessIntelligence

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.

ProjectProperty

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.

Summary

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?

Commendation

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

References – Installation

References – Visual Studio Templates

References – Presentations