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.

passing a timestamp datatype column to a variable and back

After several hours of trying, I trow the towel in the ring and come here to ask a question.

Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).

What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable

I try to do this in 2 sql execute tasks:

sqltask 1: "select @.@.DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?

DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

INT64 and UINT64 don't work error message that types for column and parameter are different

STRING works

Then I want to store this variable back in a table of a different data source

sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...

if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ?

if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.

Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?

How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?

tx for any help

Dirk

After some more hours (It just kept anoying me till late in the evening) I finaly found a way to make this work.

Make the variable in SSIS of type string

To store a timestamp as a variable

select Cast(timestampcolumn as bigint) as outputcolumn from controltable

create result set to map outputcolumn to variable

(SSIS wil do the conversion to string itself) Know that I tried to include this in the sql a convert to bigint an then to string but didn't work.

To use the timestamp in your queries or write it to another table

select * from table where timestampcolumn >= CAST(? AS BIGINT)

parameter mapping variable input type VARCHAR

(SSIS wil to the conversion from bigint to binary(8) itself

Got this working.

Point stays that datatypes should be more alligned between all the different places we use them, scripts, expressions, variables, parameters, sql data types...

Anyone got a better way, let me know.

tx

Dirk

|||

SSIS can genertae timestamps without an external data source. Is that not an option for you?

-Jamie

|||

Hi Jamie,

Don't think so. Let me explain what the purpose of all this was. The source ERP system uses timestamp columns on the tables. So I would like to use these columns as ModificationTime for my incremental load of new data in the DWH.

My problem was that I needed to get the @.@.DBTS from the source ERP system (hence the first SQL task) so that I would know the timestamp from the moment the upload started (my datapumps have a where clause like WHERE ModificationTimestamp >= TimestampOfStartLastSuccesfullUpload, so it will get all new and changed records since the start of the last upload).

Then I needed to store this Timestamp in my control table of the DWH and that's the 2d SQLtask.

I would have been extremly simple if the controltable was in the source ERP system, I could just insert the @.@.DBTS, but that was not an option. So I needed to pass the timestamp between the 2 SQL tasks. And that was my problem, getting the timestamp from the first select SQL task store it in a variable and pass that variable to the second insert sql task.

Perhaps this would have been easier in a script task, but I'm not that good at cooding. I haven't passed the level of copy, paste and modify some code ;-).

What do you think ? Is there a simpler solution ?

Dirk

|||

Ah I understand now. You need to persist the max timestamp between executions. That's a common requirement of course.

I may be mistaken but it seems the format of the timestamp is somewhat proprietary, hence the solution that you have come up with should be the most suitable - it sounds as though it will work fine though.

Question. What does the timestamp value look like? i.e. Can you paste it up here?

Regards

Jamie

|||

Dirk Van der Straeten wrote:

(BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

Yes, there is. Drag the variable into a Watch window within the BIDS environment. When you execute and break you will be able to look at the value of the variable.

-Jamie

|||

Jamie,

Following select on the database and resultset.

select @.@.dbts as timestamp, CAST(@.@.dbts as bigint) as ConvertedInteger, CAST(@.@.dbts as varchar) as ConvertedVarchar

0x00000000000F94C7 1021127 _

The Timestamp is a binary(8) that is non nullable. But you can also interpreted is a an 8 byte integer which is the same as a bigint. Where __ is actualy the blanc of the string. That was the problem that converting directly from timestamp into the string variable did not work. Strangly I also didn't get it to work when I made the Variable a UI64 or I64 and then user LARGE_INTEGER as parameter type. He gave the error that the types where not compatible.

Got the watch thing figured out now.Tx.

/Dirk

Passing a SQL Query / MDX Query to RDL

Has anybody passed a SQL Query or an MDX Query as a source to an RDL file?
This is what I am trying to do:
I have a Report whose format is exactly the same but I need to use it
for creating different reports.
For Example) The Report prints Employee name and address and I want to use
the same RDL to print Customer Name and Address.
I am redefining the query so, that column names are same
Ex) EMP_NAME as NAME and EMP_ADDRESS as ADDRESS
ThanksOn Jul 13, 11:36 am, jvn <sha...@.online.nospam> wrote:
> Has anybody passed a SQL Query or an MDX Query as a source to an RDL file?
> This is what I am trying to do:
> I have a Report whose format is exactly the same but I need to use it
> for creating different reports.
> For Example) The Report prints Employee name and address and I want to use
> the same RDL to print Customer Name and Address.
> I am redefining the query so, that column names are same
> Ex) EMP_NAME as NAME and EMP_ADDRESS as ADDRESS
> Thanks
You should be able to do this if you are dynamically creating the RDL
file in a custom application. You will want to change the query found
in this location: <DataSets><DataSet><Query><CommandText>...</
CommandText></Query></DataSet></DataSets>
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'm relatively new to Reporting Services. I'm working with SSRS 2000,
perhaps you are working with SSRS 2005.
I'm reading the book 'Hitchikers Guide to SQL Server 2000 Reporting
Services'.
The suggested approach in this book is to add a report parameter (e.g.
ReportSubject) with the values 'employees' and 'customers'.
Create a new dataset, select command type 'Text'. Manually add the
field names referring tot database field names NAME and ADRESS.
Enter the following expression:
= IIF(Parameters!ReportSubject.Value = 'employees', "SELECT EMP_NAME
as NAME, EMP_ADDRESS as ADDRESS FROM ...", "SELECT CUST_NAME as
NAME, CUST_ADDRESS as ADDRESS FROM ...")
This may become rather complex when you also want to use the report
for other purposes (nested IFF statements). But in that case you could
create a custom code function in VB so you can you CASE-statements
etc. This is not as complex as you might think.
Regards,
Willy

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

Pass XML string as parameter for report data source?

Hello,
Is it possible to pass an XML document as a string parameter to a Reporting
Services 2005 report and use this report as a data source for the report?
I know you can source from a web service, the problem is that the effort
needed to convert what I have into a web service will be more than we would
like to take on. Instead, I am trying to pass an XML structure as a string
parameter to a report and then setting the Command Text like so:
<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
TIA!!!I figure out how to do what I am looking to do.
1. Create a new report and specify a sample XML file as your data source.
This way you get all the DataSet <Field> elements you need automatically.
2. Create a new blank report (without the wizard using Add > New Item >
Report).
3. Create a string report parameter to hold your XML data structure for the
new report. I give it a default value of my sample XML used to create the
report in step 1 for testing purposes.
4. Create a new DataSet (Data tab > <New DataSet...> from DataSet dropdown).
5. Name the DataSet (like "XmlSource").
6. Select XML from the Type dropdown.
7. Specify your connection string like so (use the appropriate parameter):
="<Query><XmlSource>" & Parameters!XmlSource.Value & "</XmlSource></Query>"
8. Go to your report you created in Step 1 in "Code View" and copy the
<Fields> element from your <DataSet> element. Now paste this <Fields>
element into the new report's <DataSet> element.
9. Using the fields, drag and drop to complete the layour of your report.
This is sort of a hack to specifying a web service as the datasource, but it
seems to work! I wouldn't recommend this if you have a really large data
structure, but might be useful if you don't have the resources (i.e. web
servers, developers, TIME) to create a web service out of some existing XML
structure.
Hope this helps!!
"brianpmccullough" wrote:
> Hello,
> Is it possible to pass an XML document as a string parameter to a Reporting
> Services 2005 report and use this report as a data source for the report?
> I know you can source from a web service, the problem is that the effort
> needed to convert what I have into a web service will be more than we would
> like to take on. Instead, I am trying to pass an XML structure as a string
> parameter to a report and then setting the Command Text like so:
> <Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
> TIA!!!
>|||Sorry...Correction...In step 7, do not specify a connection string...instead
leave it empty and instead this value should be used in the Query String!
"brianpmccullough" wrote:
> I figure out how to do what I am looking to do.
> 1. Create a new report and specify a sample XML file as your data source.
> This way you get all the DataSet <Field> elements you need automatically.
> 2. Create a new blank report (without the wizard using Add > New Item >
> Report).
> 3. Create a string report parameter to hold your XML data structure for the
> new report. I give it a default value of my sample XML used to create the
> report in step 1 for testing purposes.
> 4. Create a new DataSet (Data tab > <New DataSet...> from DataSet dropdown).
> 5. Name the DataSet (like "XmlSource").
> 6. Select XML from the Type dropdown.
> 7. Specify your connection string like so (use the appropriate parameter):
> ="<Query><XmlSource>" & Parameters!XmlSource.Value & "</XmlSource></Query>"
> 8. Go to your report you created in Step 1 in "Code View" and copy the
> <Fields> element from your <DataSet> element. Now paste this <Fields>
> element into the new report's <DataSet> element.
> 9. Using the fields, drag and drop to complete the layour of your report.
> This is sort of a hack to specifying a web service as the datasource, but it
> seems to work! I wouldn't recommend this if you have a really large data
> structure, but might be useful if you don't have the resources (i.e. web
> servers, developers, TIME) to create a web service out of some existing XML
> structure.
> Hope this helps!!
> "brianpmccullough" wrote:
> > Hello,
> >
> > Is it possible to pass an XML document as a string parameter to a Reporting
> > Services 2005 report and use this report as a data source for the report?
> >
> > I know you can source from a web service, the problem is that the effort
> > needed to convert what I have into a web service will be more than we would
> > like to take on. Instead, I am trying to pass an XML structure as a string
> > parameter to a report and then setting the Command Text like so:
> >
> > <Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
> >
> > TIA!!!
> >

Pass XML Data to use as Data Source?

Hello,

Is it possible to pass an XML document as a string parameter to a Reporting Services 2005 report and use this report as a data source for the report?

I know you can source from a web service, the problem is that the effort needed to convert what I have into a web service will be more than we would like to take on. Instead, I am trying to pass an XML structure as a string parameter to a report and then setting the Command Text like so:

<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>

TIA!!!

Hi Brian-

Yes, it is possible to do this. You will need to make the entire query string an expression, so that report processing will construct the query string first, then pass it to the data provider. For your example it would look like this:

="<Query><XmlData>" & Parameters!XmlData.Value & "</XmlData><ElementPath>MyRootElement</ElementPath></Query>"

Note that this will not execute in the designer data tab (It will show an error loading the query string). Ignore this error and manually add the data set values in the layout tab. This can be done by right clicking the your xml dataset in the DataSets pane and selecting add. The report will execute successfully through the preview pane and after publishing on the server.

Thanks, Jon

|||Why does this not run in the designer? Why do you have to publish it to get it to work?|||

It will run in the designer preview or a published report. However, the report designer query window/data tab does not have the ability to parse report parameters and will thus execute an invalid query.

-JonHP

Pass XML Data to use as Data Source?

Hello,

Is it possible to pass an XML document as a string parameter to a Reporting Services 2005 report and use this report as a data source for the report?

I know you can source from a web service, the problem is that the effort needed to convert what I have into a web service will be more than we would like to take on. Instead, I am trying to pass an XML structure as a string parameter to a report and then setting the Command Text like so:

<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>

TIA!!!

Hi Brian-

Yes, it is possible to do this. You will need to make the entire query string an expression, so that report processing will construct the query string first, then pass it to the data provider. For your example it would look like this:

="<Query><XmlData>" & Parameters!XmlData.Value & "</XmlData><ElementPath>MyRootElement</ElementPath></Query>"

Note that this will not execute in the designer data tab (It will show an error loading the query string). Ignore this error and manually add the data set values in the layout tab. This can be done by right clicking the your xml dataset in the DataSets pane and selecting add. The report will execute successfully through the preview pane and after publishing on the server.

Thanks, Jon

|||Why does this not run in the designer? Why do you have to publish it to get it to work?|||

It will run in the designer preview or a published report. However, the report designer query window/data tab does not have the ability to parse report parameters and will thus execute an invalid query.

-JonHP

Pass variable value to DataReader Source

Dear All,

I have created a DTS Package in Integration Services 2005.

Within the DTS Package declared a variable named xxx and passed a value 1234.

In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined

Property = [DataReader Source].[sqlCommand]

Expression = Variable name.

Now in the DataFlow Task Canvas dropped DataReaderSource.

How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.

regards

Sufian

This post explains how to do that...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2009522&SiteID=1

|||

sorry , i have a diffrent problem.

I need to know how can i pass the variable value to the query supplied in the SQLCommand custom properties in datareader source on execution.

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

Regards

Sufian

|||

mohd sufian wrote:

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

You can't concatenate a string with an integer. You'll have to cast the integer as a string and THEN concatenate.

The cast operator is:

(DT_STR, <length>, <code_page>) variable_value

Code page will probably be 1252.

-Jamie

Pass report parameters to data source (2000)?

In the data tab of my report (Reporting Services for SQL Server 2000), I'd like to run a query that requires parameters be passed to it. Is there a way to pass the parameters of the report to that query?

Thank you.

Hello,

Yes, of course you can. Let's say you have a parameter named Param1, in the data tab, your query would look something like this:

select * from table1 where field2 = @.Param1

Hope this helps.

Jarret

|||I knew it had to be simple... Thank you so much!