passing parameter from asp.net application to reporting services report

Hi,

There is a .net application which has a screen with four options(text boxes)

Each of these should take to reports generated by SQL REp Services

This is the requirment

The School selected in the App should be passed on to the report

How should I pass the parameter in my report

so that when user selects a link or text box report for that particular school number is displayed

Thanks

sowree

Are you displaying the report in a report viewer control, or opening up a new browser window with the report URL?

BobP

|||

hi

the report is being viewed using report manager, so the report I have deployed to report server must be displayed when the user selects the menu selecting a school number in the asp app.

I need to know how i pass the parameter in my report rdl 's stored procedure to select the school number selected in the ASP application

Thanks

|||

You would create a parameter in the report rdl to accept the school number, and then use that parameter in the call to the stored proc.

For example: You create a parameter called @.SchoolNumber

exec spgSchoolInfo @.SchooNumber

To pass the parameter to the report in report manager, you would put the school number in the URL: (http://msdn2.microsoft.com/en-US/library/ms153586.aspx)

Example: http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&SchoolNumber=1234

HTH

BobP

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 more parameters

i am calling a report from a form and want to pass 90 parameters to a report.
but is not supported by reporting services. please help me to solve the
problemI suggest putting those 90 parameters into a table identified by a guid and
then pass the quid instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ter" <ter@.discussions.microsoft.com> wrote in message
news:CE88F1A4-4CD0-43BB-8DAA-75B6AC237EC0@.microsoft.com...
> i am calling a report from a form and want to pass 90 parameters to a
> report.
> but is not supported by reporting services. please help me to solve the
> problem|||You can set all the parameters in a XML document.
Good Luck.
"ter" <ter@.discussions.microsoft.com> wrote in message
news:CE88F1A4-4CD0-43BB-8DAA-75B6AC237EC0@.microsoft.com...
> i am calling a report from a form and want to pass 90 parameters to a
report.
> but is not supported by reporting services. please help me to solve the
> problem|||Can you give an example of how to access the XML document parameters in RS
THanks,
Rahul
"Liz Matyas" wrote:
> You can set all the parameters in a XML document.
> Good Luck.
> "ter" <ter@.discussions.microsoft.com> wrote in message
> news:CE88F1A4-4CD0-43BB-8DAA-75B6AC237EC0@.microsoft.com...
> >
> > i am calling a report from a form and want to pass 90 parameters to a
> report.
> > but is not supported by reporting services. please help me to solve the
> > problem
>
>

passing in long parametes to reporting services

hello,
how can i pass in long parameter into a report?
if i were to use url its just too short.
i want my users to select tons of numbers and pass it to RS. url parameter
access just does not seems right. does anyone have any advice for me?You could store the multiple values as rows in a db table, and assign a key
value which groups these together for the session. Then pass the key value
in as a parameter, the query could then use the key , and join the params
table to the orig table...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asha" <Asha@.discussions.microsoft.com> wrote in message
news:F2C123E8-3FA7-458A-863A-BC95FD4BF1B5@.microsoft.com...
> hello,
> how can i pass in long parameter into a report?
> if i were to use url its just too short.
> i want my users to select tons of numbers and pass it to RS. url parameter
> access just does not seems right. does anyone have any advice for me?

Passing datetime parameters to Reporting Services

Hi all...
How should I pass datetime parameters to a report in order for it to work?
This is the current scenario:
Dataset has, for example, this query:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN ? AND ?
where FECHA is of DATETIME type in an Oracle Database.
Both unnamed parameters are DateTime report parameters, which retrieve the
correct date values (using code embedded in the report)
When I run the report, I get the typical error message when date format is
incorrect:
"a non-numeric character was found where a numeric was expected"
It seems that report tries to pass the "-" of the date value to the unnamed
parameter.
Formerly I had this query that made the report to work:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
format). But using this way I can have (and I already had) problems with
incompatibilities in Regional Settings where the Reporting Server is
installed. For example, using this latter way, I forced the date to have the
format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
is installed, regional settings are different, so Report crashed because it
sent 08/31/2005 where database (in different server) expected 31/08/2005.
That's why I need to make it independent of regional settings of the target
server.
Any way to manage this?
Thanks a lot in advance
JaimeJaime
I believe this is an issue with the data being returned from the Query.
Not the rendering of the report.
If you run the report in the data tab and scroll to the bottom do you
get the same error?
You might want to take a look at the function Isdate(). Be advised this
will return valid dates so it will filter out any bad dates.
Sounds like someone put a character string in a date field. Does your
database allow this? Like an ASAP or somthing?
Leo
Jaime Stuardo wrote:
> Hi all...
> How should I pass datetime parameters to a report in order for it to work?
> This is the current scenario:
> Dataset has, for example, this query:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN ? AND ?
> where FECHA is of DATETIME type in an Oracle Database.
> Both unnamed parameters are DateTime report parameters, which retrieve the
> correct date values (using code embedded in the report)
> When I run the report, I get the typical error message when date format is
> incorrect:
> "a non-numeric character was found where a numeric was expected"
> It seems that report tries to pass the "-" of the date value to the unnamed
> parameter.
> Formerly I had this query that made the report to work:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> format). But using this way I can have (and I already had) problems with
> incompatibilities in Regional Settings where the Reporting Server is
> installed. For example, using this latter way, I forced the date to have the
> format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> is installed, regional settings are different, so Report crashed because it
> sent 08/31/2005 where database (in different server) expected 31/08/2005.
> That's why I need to make it independent of regional settings of the target
> server.
> Any way to manage this?
> Thanks a lot in advance
> Jaime
>|||Thanks Leo for answering...
When using the Data tab and run the query, I have to enter the date this
way:
30-apr-05 and query returns results.
The date field in database is correct, since as I told, when I use
TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
not DateTime as it should be. I cannot use the parameter as String as I
explained in my first post. In conclusion, the problem is only related to
passing parameters between Reporting Services and Oracle database.
Those DateTime parameters are calculated, for example, using this custom code:
Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
DateTime
Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
End Function
Finally, I'm going to tell you that I could display parameters just before
rendering the report (I'm using API to do it) and these are :
CUATRIMESTRE=1
AÃ?O=2005
NUMERO_POLIZA=5506666
FECHA_INICIO=5/1/2005 12:00:00 AM
FECHA_TERMINO=8/31/2005 12:00:00 AM
CUA_MESES=May - August
CUATRIMESTRE, AÃ?O and NUMERO_POLIZA are parameters the user enters.
FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
FECHA_INICIO and FECHA_TERMINO.
It's clear that Reporting Service is passing the datetime parameter formated
in a manner that isn't accepted by Oracle. I thought date time would be
passed as is, as a DATE datatype.
Any further help would be greately appreciated,
Thanks
Jaime
"cte25117@.centurytel.net" wrote:
> Jaime
> I believe this is an issue with the data being returned from the Query.
> Not the rendering of the report.
> If you run the report in the data tab and scroll to the bottom do you
> get the same error?
> You might want to take a look at the function Isdate(). Be advised this
> will return valid dates so it will filter out any bad dates.
> Sounds like someone put a character string in a date field. Does your
> database allow this? Like an ASAP or somthing?
> Leo
>
>
> Jaime Stuardo wrote:
> > Hi all...
> >
> > How should I pass datetime parameters to a report in order for it to work?
> > This is the current scenario:
> >
> > Dataset has, for example, this query:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN ? AND ?
> >
> > where FECHA is of DATETIME type in an Oracle Database.
> >
> > Both unnamed parameters are DateTime report parameters, which retrieve the
> > correct date values (using code embedded in the report)
> >
> > When I run the report, I get the typical error message when date format is
> > incorrect:
> > "a non-numeric character was found where a numeric was expected"
> >
> > It seems that report tries to pass the "-" of the date value to the unnamed
> > parameter.
> >
> > Formerly I had this query that made the report to work:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> >
> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> > format). But using this way I can have (and I already had) problems with
> > incompatibilities in Regional Settings where the Reporting Server is
> > installed. For example, using this latter way, I forced the date to have the
> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> > is installed, regional settings are different, so Report crashed because it
> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
> >
> > That's why I need to make it independent of regional settings of the target
> > server.
> >
> > Any way to manage this?
> > Thanks a lot in advance
> >
> > Jaime
> >
> >
>|||Have you tried CASTing the dates as Integers in the Oracel SQL query?
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:B960FCF6-7317-4197-8B76-55F79733BC71@.microsoft.com...
> Thanks Leo for answering...
> When using the Data tab and run the query, I have to enter the date this
> way:
> 30-apr-05 and query returns results.
> The date field in database is correct, since as I told, when I use
> TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
> not DateTime as it should be. I cannot use the parameter as String as I
> explained in my first post. In conclusion, the problem is only related to
> passing parameters between Reporting Services and Oracle database.
> Those DateTime parameters are calculated, for example, using this custom code:
> Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
> DateTime
> Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
> End Function
> Finally, I'm going to tell you that I could display parameters just before
> rendering the report (I'm using API to do it) and these are :
> CUATRIMESTRE=1
> AÑO=2005
> NUMERO_POLIZA=5506666
> FECHA_INICIO=5/1/2005 12:00:00 AM
> FECHA_TERMINO=8/31/2005 12:00:00 AM
> CUA_MESES=May - August
> CUATRIMESTRE, AÑO and NUMERO_POLIZA are parameters the user enters.
> FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
> CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
> FECHA_INICIO and FECHA_TERMINO.
> It's clear that Reporting Service is passing the datetime parameter formated
> in a manner that isn't accepted by Oracle. I thought date time would be
> passed as is, as a DATE datatype.
> Any further help would be greately appreciated,
> Thanks
> Jaime
> "cte25117@.centurytel.net" wrote:
>> Jaime
>> I believe this is an issue with the data being returned from the Query.
>> Not the rendering of the report.
>> If you run the report in the data tab and scroll to the bottom do you
>> get the same error?
>> You might want to take a look at the function Isdate(). Be advised this
>> will return valid dates so it will filter out any bad dates.
>> Sounds like someone put a character string in a date field. Does your
>> database allow this? Like an ASAP or somthing?
>> Leo
>>
>>
>> Jaime Stuardo wrote:
>> > Hi all...
>> >
>> > How should I pass datetime parameters to a report in order for it to work?
>> > This is the current scenario:
>> >
>> > Dataset has, for example, this query:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN ? AND ?
>> >
>> > where FECHA is of DATETIME type in an Oracle Database.
>> >
>> > Both unnamed parameters are DateTime report parameters, which retrieve the
>> > correct date values (using code embedded in the report)
>> >
>> > When I run the report, I get the typical error message when date format is
>> > incorrect:
>> > "a non-numeric character was found where a numeric was expected"
>> >
>> > It seems that report tries to pass the "-" of the date value to the unnamed
>> > parameter.
>> >
>> > Formerly I had this query that made the report to work:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
>> >
>> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
>> > format). But using this way I can have (and I already had) problems with
>> > incompatibilities in Regional Settings where the Reporting Server is
>> > installed. For example, using this latter way, I forced the date to have
>> > the
>> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
>> > is installed, regional settings are different, so Report crashed because it
>> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
>> >
>> > That's why I need to make it independent of regional settings of the target
>> > server.
>> >
>> > Any way to manage this?
>> > Thanks a lot in advance
>> >
>> > Jaime
>> >
>> >

Passing DataTable to Reporting Services 2000

It used to work with ReportDocument Object of CR.This works with the new Visual Studio Report Controls. Take a look at http://gotreportviewer.com.

Passing authentication from PHP to reporting services

Hello,

My boss wants me to create a front end webpage for our Reporting
Services reports that customers can log onto through a webform. I'm a
PHP programmer, so I'd rather do this site and the authentication in
PHP than learn .NET. The main problem I see is finding a way to pass
the authentication to Reporting Services so that when a user runs a
report, they are not asked to enter their password a second time.

I don't even know where to start researching this problem... I've asked
in PHP forums and they passed me to you guys. Any ideas?

AndrewIf no one knows the answer to this, does anyone know a better place
that I could ask this question?|||andrewdmason@.gmail.com (andrewdmason@.gmail.com) writes:
> If no one knows the answer to this, does anyone know a better place
> that I could ask this question?

That would be microsoft.public.sqlserver.repotingsvcs.

If your local newserver does not carry it, it's available at
msnews.microsoft.com.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Setup anonymous authentication (instead of NT Challenge Response, the
default) for MS Reporting services and diallow all connections besides
the PHP server and then you don't have to worry about authentication at
all.

Erland Sommarskog wrote:
> andrewdmason@.gmail.com (andrewdmason@.gmail.com) writes:
> > If no one knows the answer to this, does anyone know a better place
> > that I could ask this question?
> That would be microsoft.public.sqlserver.repotingsvcs.
> If your local newserver does not carry it, it's available at
> msnews.microsoft.com.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Passing ADO.Net dataset to RS 2005

Hi,
I would like to know how to pass ado.net dataset to reporting services 9.
I´ve found this article
http://msdn.microsoft.com/data/archive/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp
but, acording to the sumary, it does apply to "SQL 2000 - Reporting
Services".
Could anyone help me'
Any help will be appreciated.
thanx a lotHi Ricardo,
the same article code works for RS 2005 also.
There will be only few changes like pointing to 2005 RS assemblie references..
just point the Microsoft.ReportingServices.Interfaces.dll to the 2005RS dll
and it will work..
-Bava
"Ricardo" wrote:
> Hi,
> I would like to know how to pass ado.net dataset to reporting services 9.
> I´ve found this article
> http://msdn.microsoft.com/data/archive/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp
> but, acording to the sumary, it does apply to "SQL 2000 - Reporting
> Services".
> Could anyone help me'
> Any help will be appreciated.
> thanx a lot
>
>

Passing a report parameter to Reporting Services from VB

I have a Report that I want to access that has a parameter input for the date, Production_Date. I want to allow the user to use the DateTimePicker to select the date to use for Production_Date. I can't seem to find the correct way to format what I'm tring to do. My production date field is in a format without punctuation so that it becomes:

Dim ProductionDate = ((DateTimePicker1.Value.Month * 1000000) + (DateTimePicker1.Value.Day * 10000) + DateTimePicker1.Value.Year)

which gives me a value that I want to send as a parameter for the Reporting Services report that I have located on a tab in my project. The report is:

Me.ReportViewer1

I want to send the ProductionDate to the report where the report looks for Production_Date. Obviously, I 'm very new to this. I'd appreciate any suggestions. As it runs now, the default date is loaded (today's date) from the expression I calculated in Reporting services:

=(Now.Month*1000000)+(Now.Day*10000)+Now.Year

This is a big roadblock right now and I can't seem to put together a legal means to pass this value. Thanks for any help.

Have you tried passing DateTimePicker1.Value.ToString() to either Me.ReportViewer1.LocalReport.SetParameters() or Me.ReportViewer1.ServerReport.SetParameters() in a ReportParameter object?|||

Brian,

Thanks for the response. I'm not structuring the object correctly. I'm trying to understand how to set it up. I am enclosing where I'm at from copying some book examples, but I still have a way to go. In this case, I'm not really worrying about the calendar component just yet. I just want to understand how to pass this parameter to my report. In my case, the parameter is a date without delimiters for day or year. The parameter name is Production_Date. I'd like to allow my users to pass simple parameters such as the Production date or shift using the calendar component or a radio button for shift. In this report, there is only one parameter, the Production_Date. I would think this would be a common way to interact with Reporting Services because they use a third of the screen for their header. I'm still trying to get this figured out. Thanks again for the response.

Dim rp = New Microsoft.Reporting.WinForms.ReportParameter()

Me.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote

Me.ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://plant10plc/reportserver/")

Me.ReportViewer1.ServerReport.ReportPath = "/PLC Data Browser 2006/Report1"

Dim reportParameters = ReportViewer1.ServerReport.GetParameters()

rp.Name = "@.Production_Date"

rp.Value = 3292006

Me.ReportViewer1.ServerReport.SetParameters(reportParameters.rp)

Me.ReportViewer1.ShowParameterPrompts = False

Me.ReportViewer1.RefreshReport()

|||

The report viewer has a built in calendar prompt. If you set your parameter data type of date, it will show up in the built in parameter prompting. By using a datatype of date instead of string, you will need to use a formatted data (3/29/2006 instead of 3292006).

As far as your code, there are a few things to change:

1. You don't need to call GetParameters. The return value also doesn't have an rp property (that you are passing in to SetParameters)

2. Your parameter name shouldn't have the @.

3. You should pass in an array or ReportParameter objects to SetParameters. That array should have one element: rp.

|||

I spent time trying to modify my Date type from the calculated integer to the real datetime data type. In my system, I use a data collection service to collect information from the machines in a factory. I can't pass a string I create into a datetime field, but I can pass the timestamp() of the transaction. I kept my original value for keeping my records inserts and updates correct (my ProductionDate starts at 6:30am). What I found is that if I create a date, it enters a valus as 3/30/2006 12:00:00 am. If my software does it, it creates a date as 3/30/2006 3:45:00 pm, and updates to 3/30/2006 3:46:00 pm and so on. When I use the automatic date picker in Reporting Services, it only shows the fields that used M/D/YYYY 12:00:00 am. I could make it work, but there's no actual "date" datatype in SQL Server. This is really frustrating, but life continues....

In returning back to my original issue, I looked up the ReportParameter Members from the help files. It shows that there are Public Properties of ReportParameter which are Value and Name among others. Here's my code now:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim rp = New Microsoft.Reporting.WinForms.ReportParameter

Me.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote

Me.ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://plant10plc/reportserver/")

Me.ReportViewer1.ServerReport.ReportPath = "/PLC Data Browser 2006/Report1"

rp.Name = "ProductionDate"

rp.Value = 3292006

Me.ReportViewer1.ServerReport.SetParameters(rp)

Me.ReportViewer1.ShowParameterPrompts = False

Me.ReportViewer1.RefreshReport()

End Sub

End Class

When I run the code, it says "Public member 'Value' on type 'ReportParameter' not found." As I stated before, I'm kind of new to this. I appreciate the advice and would appreciate tips from Brian or anyone else that can explain what's probably a rookie mistake.

Thanks,

Jack

|||

The property name is called Values, not Value. And it is a collection, so you will need to write something like this:

rp.Values.Add(3292006)

Also, SetParameters takes an array of ReportParameters, not a single instance, so you will need to create an array of one element and assign rp to that first element.

Passing a report parameter from a Visual C# form to a report parameter

Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.

I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?

Thanks!

Terry B

I hope this this article will help.

Passing a parameter to a URL in a browser SQL SERVER REPORTING SERVICES 2000

Hi all..
I can't seem to get parameters to pass via an URL according to the
documentation..
http://certol/Reports/Pages/Report.aspx?ItemPath=%2fFSDBMR%2fManufacturing%2fPlanning+Data&Active_Obsolete_None_Filter=2
(btw Active_Obsolete_None_Filter is the parameter )
Is there something wrong with this syntax? It does bring up the report, but
it doesn't pass along the parameter... Any clues? I've tried several
different variations...
thanks in advance..Reports is the Report Manager portal. The actual server is reportserver. You
need to use reportserver, not reports. I.e.
http://yourserver/ReportServer?/YourFolder/YourReport&Param1=Param1Value
Note that parameters are case sensitive.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:u3zatoTFGHA.984@.tk2msftngp13.phx.gbl...
> Hi all..
> I can't seem to get parameters to pass via an URL according to the
> documentation..
> http://certol/Reports/Pages/Report.aspx?ItemPath=%2fFSDBMR%2fManufacturing%2fPlanning+Data&Active_Obsolete_None_Filter=2
> (btw Active_Obsolete_None_Filter is the parameter )
> Is there something wrong with this syntax? It does bring up the report,
> but it doesn't pass along the parameter... Any clues? I've tried several
> different variations...
> thanks in advance..
>|||Thanks, Worked like a charm...
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ubPOWuTFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> Reports is the Report Manager portal. The actual server is reportserver.
> You need to use reportserver, not reports. I.e.
> http://yourserver/ReportServer?/YourFolder/YourReport&Param1=Param1Value
> Note that parameters are case sensitive.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "certolnut" <whitney_neal@.hotmail.com> wrote in message
> news:u3zatoTFGHA.984@.tk2msftngp13.phx.gbl...
>> Hi all..
>> I can't seem to get parameters to pass via an URL according to the
>> documentation..
>> http://certol/Reports/Pages/Report.aspx?ItemPath=%2fFSDBMR%2fManufacturing%2fPlanning+Data&Active_Obsolete_None_Filter=2
>> (btw Active_Obsolete_None_Filter is the parameter )
>> Is there something wrong with this syntax? It does bring up the report,
>> but it doesn't pass along the parameter... Any clues? I've tried several
>> different variations...
>> thanks in advance..
>

Passing a parameter to a stored procedure in Reporting Services 2000

I am new to SQL Server 2000 Reporting Services

I have a stored procedure that has a string parameter passed to it.I would like the user to select the value of the parameter from a list box which is populated by a table.

Any help would be appreciated.

Google may be a good start, or a book
http://www.15seconds.com/issue/041027.htm

SSRS 2005 is a bit different, but the idea is the same
You select the source "From Query" for "Available Values"
Select the query you like, with the Name, and Value column

Passing a date report parameter on the URL

I'm trying to call a report I created (and deployed to our dev server -
running reporting services 2005), while passing the parameters on the URL.
I think I'm very close, but it doesn't seem to like the format that the date
is in. However, I've tried everything I can think of (surrounding the date
with quotes, double quotes, pound symbols, and every combination of those I
could think of), and yet I'm still getting the error "The value provided for
the report parameter 'StartDate' is not valid for its type.
(rsReportParameterTypeMismatch) "
The URL I'm using is:
http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
The two "date" parameters are obviously set up as date types (especially as
when I browse to the report without passing parameters, it allows me to pick
the dates for each parameter, rather than just allowing text entry).
Any ideas/suggestions?Scott,
Try to not use the ' " or # and just the value like
Start_Date=06/17/2007&End_Date=06/20/2007
Reeves
"Scott Lyon" wrote:
> I'm trying to call a report I created (and deployed to our dev server -
> running reporting services 2005), while passing the parameters on the URL.
> I think I'm very close, but it doesn't seem to like the format that the date
> is in. However, I've tried everything I can think of (surrounding the date
> with quotes, double quotes, pound symbols, and every combination of those I
> could think of), and yet I'm still getting the error "The value provided for
> the report parameter 'StartDate' is not valid for its type.
> (rsReportParameterTypeMismatch) "
>
> The URL I'm using is:
> http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
>
> The two "date" parameters are obviously set up as date types (especially as
> when I browse to the report without passing parameters, it allows me to pick
> the dates for each parameter, rather than just allowing text entry).
>
> Any ideas/suggestions?

Passing a dataset to reporting services

I've seen questions similar to this posted, but I'm unable to find a clear answer to what I need to do.

We are currently using Crystal Reports, generating a dataset in our application and passing it to Crystal. Due to the many problems we have with Crystal we are now looking to move to Reporting Services 2005, but I can't find out how to pass it a dataset.

Although we do use SQL Server for data storage, we have many reports with calculations that are way too complicated to use calculated fields, and which change too often to want to store them in the database. One solution might be to store the data temporarily and regenerate it each time it's required, but this doesn't seem very elegant. So please could someone tell me if there is a way to do what I'm after and point me at a page that gives a nice clear explanation.

Thank you

Hi Williams,

Here's a sample that shows you how to pass a Dataset to SRS.

http://msdn2.microsoft.com/en-us/library/aa902651(SQL.80).aspx

Regards,

Amol.

|||Thanks for that. It's surprisingly hard to find this information by searching.

Passing a dataset as a parameter?

Can you build a dataset in a web application and send it as a parameter
through the report viewer control and have reporting services use this
dataset as the binding dataset for your table in Reporting Services? IF so,
can you help me or direct me to some article that will show me how to do
this.RS (both 2000 and 2005) don't support binding to applcation datasets
natively. If you need to pass a dataset to a server-generated report, you
may find my custom extension useful
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
If you RS 2005 and the Report Viewers, you can bind the dataset to a local
report.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> Can you build a dataset in a web application and send it as a parameter
> through the report viewer control and have reporting services use this
> dataset as the binding dataset for your table in Reporting Services? IF
> so,
> can you help me or direct me to some article that will show me how to do
> this.|||The posts that I am seeing in your article looks like people are having
problems with doing this in Reporting Services 2005. At least the referencing
portion. Could you update with how this is done in 2005 or post where the
AWC.RS.Extensions.dll
is copied in 2005.
Thanks in advance
"Teo Lachev [MVP]" wrote:
> RS (both 2000 and 2005) don't support binding to applcation datasets
> natively. If you need to pass a dataset to a server-generated report, you
> may find my custom extension useful
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> If you RS 2005 and the Report Viewers, you can bind the dataset to a local
> report.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> > Can you build a dataset in a web application and send it as a parameter
> > through the report viewer control and have reporting services use this
> > dataset as the binding dataset for your table in Reporting Services? IF
> > so,
> > can you help me or direct me to some article that will show me how to do
> > this.
>
>|||I was also reading that someone was having trouble with using a dataset that
used SQL Query joins with multiple tables or is this not a problem?
"Teo Lachev [MVP]" wrote:
> RS (both 2000 and 2005) don't support binding to applcation datasets
> natively. If you need to pass a dataset to a server-generated report, you
> may find my custom extension useful
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> If you RS 2005 and the Report Viewers, you can bind the dataset to a local
> report.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> > Can you build a dataset in a web application and send it as a parameter
> > through the report viewer control and have reporting services use this
> > dataset as the binding dataset for your table in Reporting Services? IF
> > so,
> > can you help me or direct me to some article that will show me how to do
> > this.
>
>|||One day I will probably get to it :-) It shouldn't be that terribly
difficult to upgrade the changed interfaces.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:74683164-56A9-44BD-806D-7C9DBBCAA339@.microsoft.com...
> The posts that I am seeing in your article looks like people are having
> problems with doing this in Reporting Services 2005. At least the
> referencing
> portion. Could you update with how this is done in 2005 or post where the
> AWC.RS.Extensions.dll
> is copied in 2005.
> Thanks in advance
> "Teo Lachev [MVP]" wrote:
>> RS (both 2000 and 2005) don't support binding to applcation datasets
>> natively. If you need to pass a dataset to a server-generated report, you
>> may find my custom extension useful
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> If you RS 2005 and the Report Viewers, you can bind the dataset to a
>> local
>> report.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
>> > Can you build a dataset in a web application and send it as a
>> > parameter
>> > through the report viewer control and have reporting services use this
>> > dataset as the binding dataset for your table in Reporting Services? IF
>> > so,
>> > can you help me or direct me to some article that will show me how to
>> > do
>> > this.
>>|||While the sky is the limit what your CDE can do, RS expects a
two-dimensional resultset.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:3CC6145C-449B-4C11-B5DF-A7A4F1275375@.microsoft.com...
>I was also reading that someone was having trouble with using a dataset
>that
> used SQL Query joins with multiple tables or is this not a problem?
> "Teo Lachev [MVP]" wrote:
>> RS (both 2000 and 2005) don't support binding to applcation datasets
>> natively. If you need to pass a dataset to a server-generated report, you
>> may find my custom extension useful
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> If you RS 2005 and the Report Viewers, you can bind the dataset to a
>> local
>> report.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
>> > Can you build a dataset in a web application and send it as a
>> > parameter
>> > through the report viewer control and have reporting services use this
>> > dataset as the binding dataset for your table in Reporting Services? IF
>> > so,
>> > can you help me or direct me to some article that will show me how to
>> > do
>> > this.
>>

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 where clause to Report dataset

Hi there,
Does anyone know how to or if it is possible to pass a where clause as a parameter to a dataset for a Reporting Services report? I have a case where depending on what is selected the where clause needs to be based on a different fields. Or otherwise if an if statement can be used in the dataset to overcome this problem.
ThanksI used to do this at design time using conditional expression like this:
="SELECT * FROM myTable" & IIF(Parameters!myValue.value="All", ""," where myColumn =" & Parameters!Linea.value)
or using stored procedures. Not sure you can set dataset definition at run time.
Antonio
"PLSH" wrote:
> Hi there,
> Does anyone know how to or if it is possible to pass a where clause as a parameter to a dataset for a Reporting Services report? I have a case where depending on what is selected the where clause needs to be based on a different fields. Or otherwise if an if statement can be used in the dataset to overcome this problem.
> Thanks|||In the data pane in report designer.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Jolliffe" <MikeJolliffe@.discussions.microsoft.com> wrote in message
news:34FC573B-36E7-44CC-BEEE-A725AD30BBDE@.microsoft.com...
> Antonio
> hi - i'm interested where you put this conditional statement to make it
work
> - in the source of a control or in the data area somewhere? I'd tried
> something similar and could not make it work.
> thanks
> Mike
>
> "Antonio Rome" wrote:
> > I used to do this at design time using conditional expression like this:
> >
> > ="SELECT * FROM myTable" & IIF(Parameters!myValue.value="All", "","
where myColumn =" & Parameters!Linea.value)
> >
> > or using stored procedures. Not sure you can set dataset definition at
run time.
> >
> > Antonio
> >
> > "PLSH" wrote:
> >
> > > Hi there,
> > >
> > > Does anyone know how to or if it is possible to pass a where clause as
a parameter to a dataset for a Reporting Services report? I have a case
where depending on what is selected the where clause needs to be based on a
different fields. Or otherwise if an if statement can be used in the dataset
to overcome this problem.
> > >
> > > Thanks
>|||Do you mean the SQL Pane? Tried this and received syntax errors.
"Ravi Mumulla (Microsoft)" wrote:
> In the data pane in report designer.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Mike Jolliffe" <MikeJolliffe@.discussions.microsoft.com> wrote in message
> news:34FC573B-36E7-44CC-BEEE-A725AD30BBDE@.microsoft.com...
> > Antonio
> > hi - i'm interested where you put this conditional statement to make it
> work
> > - in the source of a control or in the data area somewhere? I'd tried
> > something similar and could not make it work.
> >
> > thanks
> > Mike
> >
> >
> > "Antonio Rome" wrote:
> >
> > > I used to do this at design time using conditional expression like this:
> > >
> > > ="SELECT * FROM myTable" & IIF(Parameters!myValue.value="All", "","
> where myColumn =" & Parameters!Linea.value)
> > >
> > > or using stored procedures. Not sure you can set dataset definition at
> run time.
> > >
> > > Antonio
> > >
> > > "PLSH" wrote:
> > >
> > > > Hi there,
> > > >
> > > > Does anyone know how to or if it is possible to pass a where clause as
> a parameter to a dataset for a Reporting Services report? I have a case
> where depending on what is selected the where clause needs to be based on a
> different fields. Or otherwise if an if statement can be used in the dataset
> to overcome this problem.
> > > >
> > > > Thanks
> >
>
>

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