Passing null value parameter to a data-driven subscription

I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.

When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.

I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.

Has anyone else experienced this issue and do you have a resolution?

Thanks in advance for your assistance.

I am having the same issue, please let me know if you have found a resolution.

Thank you,

Dave

|||

I had the same problem. In another thread someone mentioned that there is a problem with null parameters and data-driven subscriptions. It seems like a major bug in reporting services. My workaround was to use something other than null and have the report query recognize this setting as equivalent to null e.g. if it's a varchar field then set to 'NOTHING' and have the report definition query treat 'NOTHING' as NULL. For a uniqueidentifer field I used a guid with all zeroes.

Hope that helps.

Regards,

Greg

Passing null value parameter to a data-driven subscription

I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.

When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.

I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.

Has anyone else experienced this issue and do you have a resolution?

Thanks in advance for your assistance.

I am having the same issue, please let me know if you have found a resolution.

Thank you,

Dave

Passing Date as String

I have a report that returns date in the format 'YYYY-MM-DD'. This report
has no parameters, but I am drilling to another report that accepts date as a
parameter. So, I select the textbox I want to drill from and select "Jump to
Report". I select the report to be drilled to and define the parameters that
are to be passed.
Parameter Parameter Value
date =Fields!date.Value
Next, I go to the report that is to be drilled to and add date as a
parameter. Parameter name "date", parameter data type "string". Now, when
the drill report passes the date to the drilled report, the date parameter is
filled in as:
MM/DD/YYYY 00:00:00
Why is RS converting my strings and what is the best way to either make it
stop or work around this problem.
Thanks for any help!Sprinkle liberally with:
=Format(Convert.ToDateTime(Fields!date.Value), "yyyy-MM-dd")
Cheers,
"asnewbie+rs=argh" wrote:
> I have a report that returns date in the format 'YYYY-MM-DD'. This report
> has no parameters, but I am drilling to another report that accepts date as a
> parameter. So, I select the textbox I want to drill from and select "Jump to
> Report". I select the report to be drilled to and define the parameters that
> are to be passed.
> Parameter Parameter Value
> date =Fields!date.Value
> Next, I go to the report that is to be drilled to and add date as a
> parameter. Parameter name "date", parameter data type "string". Now, when
> the drill report passes the date to the drilled report, the date parameter is
> filled in as:
> MM/DD/YYYY 00:00:00
> Why is RS converting my strings and what is the best way to either make it
> stop or work around this problem.
> Thanks for any help!
>

Passing a value between datasets?

Hi
Does anyone know if it is possible to use a value from one dataset as a
parameter to the query in a second dataset?
Example: Datset1 returns 8 rows, my report produces a page for each
row, but each page requires additional data relevant to that page of
the report.
Is there a way I can take (for instance) the current value of the
ReportType field from dataset1 and use that in a select statement in
Dataset2?
TIA
StewartWhat you are describing is perfect for subreports. Subreports are regular
reports that you can design and test by themselves. You create the report
with a parameter. Make sure it all works and then drag and drop the report
onto the main one. Do a right mouse click on the report, properties,
parameter tab and then map the parameter to the field of you first dataset.
The last thing I do is I hide the subreport for list view via report manager
(go to the properties of the report and there is a check box to do this).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sdm" <macneisd-www@.dcs.gla.ac.uk> wrote in message
news:1108719942.123540.216560@.c13g2000cwb.googlegroups.com...
> Hi
> Does anyone know if it is possible to use a value from one dataset as a
> parameter to the query in a second dataset?
> Example: Datset1 returns 8 rows, my report produces a page for each
> row, but each page requires additional data relevant to that page of
> the report.
> Is there a way I can take (for instance) the current value of the
> ReportType field from dataset1 and use that in a select statement in
> Dataset2?
> TIA
> Stewart
>|||Bruce
That sounds just what I need! Apologies for not acknowledging it
sooner, I'm just looking into RS in 'spare' moments.
Thanks very much for your reply, I really appreciate it.
Stewart|||Bruce, worked great, thanks again.
Stewart
Bruce L-C [MVP] wrote:
> What you are describing is perfect for subreports. Subreports are
regular
> reports that you can design and test by themselves. You create the
report
> with a parameter. Make sure it all works and then drag and drop the
report
> onto the main one. Do a right mouse click on the report, properties,
> parameter tab and then map the parameter to the field of you first
dataset.
> The last thing I do is I hide the subreport for list view via report
manager
> (go to the properties of the report and there is a check box to do
this).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sdm" <macneisd-www@.dcs.gla.ac.uk> wrote in message
> news:1108719942.123540.216560@.c13g2000cwb.googlegroups.com...
> > Hi
> >
> > Does anyone know if it is possible to use a value from one dataset
as a
> > parameter to the query in a second dataset?
> >
> > Example: Datset1 returns 8 rows, my report produces a page for
each
> > row, but each page requires additional data relevant to that page
of
> > the report.
> >
> > Is there a way I can take (for instance) the current value of the
> > ReportType field from dataset1 and use that in a select statement
in
> > Dataset2?
> >
> > TIA
> >
> > Stewart
> >

Passing a subquery as a parameter to a user defined function

I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>

passing \ as a parameter to a function

I have a UDF for splitting delimiter strings:
CREATE FUNCTION Split
(@.Source varchar (5000)
,@.Delimiter varchar (10) = ','
)
RETURNS @.T table (F1 varchar (100))
AS
--Accepts a source string @.Source and parses it to break it up into single
units
--delineated by @.Delimiter.
--Returns a Single Column Table with each row containing one of the split
chunks
--e.g. @.Source = 'SP,AQ,YD'
-- Returns @.T with three rows:
-- SP
-- AQ
-- YD
-- or @.Source = 'P1=V1, P2=V2'
-- Returns @.T with two rows:
-- P1=V1
-- P2=V2
BEGIN
DECLARE @.w varchar (5000)
DECLARE @.inte int
SET @.W = @.Source + @.Delimiter
WHILE len(@.W) > 0
BEGIN
SET @.inte = patindex('%,%',@.w) - 1
INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
END
RETURN
END
A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
the @.Reps table would have three records with one of the names in each.
e.g. Fred
Joe
Andy
It can also be called "inline":
SELECT s.* FROM tblSales s
INNER JOIN (SELECT * FROM split(@.Reps,',') r
ON s.Rep = r.F1
This all works fine until I try to call it using '\' as the delimiter
parameter, then I just get an error that says "Invalid length parameter
passed to the substring function"
Here is sample code to run this:
DECLARE @.NewPath varchar (100)
--Use this pair and it works
-- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
-- SELECT * FROM split(@.NewPath, ',')
--Use this pair and it fails
SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
SELECT * FROM split(@.NewPath, '\')
Sorry to be so long winded, but does anyone have any ideas?
Regards,
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"Hi
I have modified a little bit the function written by Dejan Sarka.
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000),@.delim VARCHAR(2))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(@.delim,@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(@.d
elim),8000)
END
RETURN
END
GO
--A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
declare @.RepSelect varchar(50)
set @.RepSelect='Fred\Joe\Andy'
INSERT @.Reps (RepIn) SELECT * FROM TsqlSplit(@.RepSelect,'')
select * from @.Reps
"RMarmion" <RMarmion@.Discussions.Microsoft.com> wrote in message
news:76AF1578-99B3-45FE-A24E-4D82B5435CF8@.microsoft.com...
>I have a UDF for splitting delimiter strings:
> CREATE FUNCTION Split
> (@.Source varchar (5000)
> ,@.Delimiter varchar (10) = ','
> )
> RETURNS @.T table (F1 varchar (100))
> AS
> --Accepts a source string @.Source and parses it to break it up into
> single
> units
> --delineated by @.Delimiter.
> --Returns a Single Column Table with each row containing one of the split
> chunks
> --e.g. @.Source = 'SP,AQ,YD'
> -- Returns @.T with three rows:
> -- SP
> -- AQ
> -- YD
> -- or @.Source = 'P1=V1, P2=V2'
> -- Returns @.T with two rows:
> -- P1=V1
> -- P2=V2
> BEGIN
> DECLARE @.w varchar (5000)
> DECLARE @.inte int
> SET @.W = @.Source + @.Delimiter
> WHILE len(@.W) > 0
> BEGIN
> SET @.inte = patindex('%,%',@.w) - 1
> INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
> SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
> END
> RETURN
> END
> A typical use of this would be:
> DECLARE @.Reps table (RepIn varchar (20))
> INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
> Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
> the @.Reps table would have three records with one of the names in each.
> e.g. Fred
> Joe
> Andy
> It can also be called "inline":
> SELECT s.* FROM tblSales s
> INNER JOIN (SELECT * FROM split(@.Reps,',') r
> ON s.Rep = r.F1
> This all works fine until I try to call it using '' as the delimiter
> parameter, then I just get an error that says "Invalid length parameter
> passed to the substring function"
> Here is sample code to run this:
> DECLARE @.NewPath varchar (100)
> --Use this pair and it works
> -- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
> -- SELECT * FROM split(@.NewPath, ',')
> --Use this pair and it fails
> SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
> SELECT * FROM split(@.NewPath, '')
> Sorry to be so long winded, but does anyone have any ideas?
> Regards,
> -Rob
> --
> Robert Marmion
> ITBridges Inc
> 609 844 0949
> "Connecting your Business with your Software"|||Here lies the pain:
> SET @.inte = patindex('%,%',@.w) - 1
You're still looking for the comma. And, BTW, you could just as well use
CHARINDEX.
ML|||Duh!
Thank you both so much. What a stupid error!!
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"
"ML" wrote:

> Here lies the pain:
> You're still looking for the comma. And, BTW, you could just as well use
> CHARINDEX.
>
> ML