Passing Null to Stored Procedure in Reporting Services

Hello,

I have a report. I need to make a drop-down parameter optional. I've setup the parameter to use a dataset as its source, and I need to allow the value to be null. I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value. But, when the entry is not selected (blank), no data ever comes back. If the data is blank or null, my procedure works (I've tested it). How do I send a null value to the database through reporting services report? I was hoping to avoid creating a special query just for that purpose.

Thanks.

Since you say that it is Blank, the report is passing a blank '', not a null. In your proc, you could have the statement:

if @.Parm = ''

Begin

Select @.Parm = null

End

In your parameter setup, uncheck the Allow Blank, and select the Allow Nulls...

Then, when running the report, when the NULL check box in the parameter panel is checked, a NULL will be passed to the proc.

hth

BobP

|||

Hello,

I have Allow Nulls checked. I do have that safeguard in my procedure. I don't have the option for the null checkbox in the parameter panel though. I am sure I have Allow Nulls checked in my application. How does that get added to the panel?

Thanks.

|||

You might want to consider modifying the stored procedure to default the parameter in question. You can set up the last (or last few) arguments of a stored procedure to be optional by providing a default value for the argument. For example, if you have a stored procedure defined as:

Code Snippet

create dbo.myProc

( @.anArgument varchar(20) = null

)

as

...

Since you have provided a default value to your parameter (as designated in red), you are no longer required to pass this argument to your stored procedure. Sometimes this can help.

Kent

|||

The null check box only gets added when you check the Allow Nulls box.

Also make sure you uncheck the Allow Blanks box.

BobP

|||

Hello,

I have a default value set to null for my stored procedure. I have allow nulls checked. I have allow blanks unchecked. I have verified my stored procedure and it works with nulls and blanks (SP converts blanks to null), and I have run the stored procedure in the RS designer and it works. I am sure of that... But, no matter what, I do not get a nulls checkbox in the parameter area...

Any ideas?

|||

BobP,

I do have Allow Nulls checked, and still it does not show.

Any ideas?

|||What version of SSRS are you using? 2000 or 2005, and which SP?|||

For some reason, SSRS doesn't seem to support "allow NULL" option for drop-down query-based parameters. You are always required to pick one of the parameter values to proceed with the report.

I created a small test report based on an SP that accepts one optional parameter of type INT. I was able to configure optional drop-down parameter for this report by creating a following dataset for parameter query:

Code Snippet

SELECT NULL party_id, '<all customers>' party_name

UNION ALL
SELECT party_id, party_name FROM party ORDER BY party_name

This will add "<all customers>" option to the list of parameters values and return NULL to the SP if this option is selected.

Hope this helps.

Denis

|||Reporting Services 2000.|||

Hey,

I was hoping not to have to do that, but your right I believe that is the only option.

Thanks.

Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldnâ't the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgnHi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:
> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn?t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?
> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>

Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldn’t the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgn
Hi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:

> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldn’t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>
|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnt the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>
|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?

> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn’t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>

Partitioning using Date - TimeKey (Ref to another table) column

Hello.
I'd like to implement table partitioning for one of my tables in my DB
(Sales History). I'd like to setup partitioning using a date. I'm planning
to use a partition per year that are going to be spread among multiples
Filegroups. This table contains an integer field that link to my TimeKey
table to get the Date of the transaction. I would know how to setup
partitioning if my date was directly in my table but since I have to join to
another table, how can I achieve this? I can't use a range from my integer
since they're not really sorted sequentially. Any help would be
appreciated. Thanks!"Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I'd like to implement table partitioning for one of my tables in my DB
> (Sales History). I'd like to setup partitioning using a date. I'm
> planning to use a partition per year that are going to be spread among
> multiples Filegroups. This table contains an integer field that link to
> my TimeKey table to get the Date of the transaction. I would know how to
> setup partitioning if my date was directly in my table but since I have to
> join to another table, how can I achieve this? I can't use a range from
> my integer since they're not really sorted sequentially. Any help would
> be appreciated. Thanks!
>
Not what you want to hear but I'd be inclined to allocate an intelligent
time key to start with. If it's keyed on date only then use the date as an
8-digit number in the form yyyymmdd.
Possibly you could create a computed column that derives a date from your
key and use that as your partitioning column.
David Portas
SQL Server MVP
--|||I DON'T WANT TO HEAR THAT!
:)
That's what I thought, it is not a big deal since I'm @. the beginning of
implementing our new DW using SQL Server 2005 so I will definitively
consider your "intelligent" key suggestion. Thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: OQMwB1TEGHA.376@.TK2MSFTNGP12.phx.gbl...
> "Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
> news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Not what you want to hear but I'd be inclined to allocate an intelligent
> time key to start with. If it's keyed on date only then use the date as an
> 8-digit number in the form yyyymmdd.
> Possibly you could create a computed column that derives a date from your
> key and use that as your partitioning column.
> --
> David Portas
> SQL Server MVP
> --
>