Passing NULL to DataSet parmameter

Hey All,

I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:

@.ID = <Null> - ERROR - [BC30201] Expression expected

@.ID= - ERROR - Return statement in function must return a value

@.ID = Null --ERROR - Null constant not supported use System.DBNull instead

@.ID = System.DBNull -ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@.ID=System.DBNull.Value NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.

Does anyone know another magic value I can pass to get this to work?

I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.

Thanks in advance.

Eric Wild

PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!

ALTER PROC [dbo].[spGetLaborRole]
@.ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @.ID) OR (@.ID IS NULL) )

Hello Eric,

Can you verify that in your report parameter definition (Report menu --> Report Parameters), the 'Allow null values' checkbox is selected for your ID parameter?

Jarret

|||

Jarret,

That worked!

I guess I didn't see them as being related. I think of report parameters as things that communicate with the ouside world, and not related to my internl querrires. I wouldn't want a prompt to the end user showing ID: NULL to run the report. I can see though there is a hidden check so it not for end users. Cool thanks!

Here is steps on how to fix this.

1) go to the data tab and select the elipse. select the parameters tab and delete the @.ID=... stuff I put in and close Window.

2) Go to the Layout tab, and from the menu select Report/Report Parameters...

3) a new Parameter is in the list to the left callled ID.

-Check Allow Nulls

- Check Hidden

-Verify Default Value NULL is bubbled in below.

Thanks again

Eic Wild

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing an Entire XQuery String as Variable

Hi,
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
Bob
Hello Bob,
No, not really because the XQuery parser is one-pass, so something like this:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob,

> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is sad to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> sad to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co. uk...[vbcol=seagreen]
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:

Passing an Entire XQuery String as Variable

Hi,
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
BobHello Bob,
No, not really because the XQuery parser is one-pass, so something like this
:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob,

> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall
,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co.uk...
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote: