Passing parameter from a webpage to a report

I am developing a website which need to allow user to pass through several webpages for criteria selection and generate a report base on the selected criteria finally.

Anyone know how to pass the selected value from a webpage to the SQL statement or stored procedure which used to generate the report? Or there are other methods to do so?look at the rendering methods of RS - there's one by URL where you can pass the parameters via URL and request the report.|||I believe you simply build a querystring and append the parameters and values to the end of the querystring.|||Thanks for your kindly reply.

I have another related question see whether anyone can help. Base on my current knowledge, I know that we can pass one value for each parameter. E.g. countryID=20. If I want to pass multiple values for this parameter, e.g. countryID=20, 21, 25, ... How can I do so? Also, how should I set it in report designer for this purpose?|||Can you explain little more in detail (perhaps with some sampoe) ? Is your data in the format 20,21,25 for each record ?|||You could pass in a string like you describe. You would need to have logic in your report or stored procedure to handle such a request, though...|||In my report, there is a chart to compare sales performance between several products which are selected by user. The number of selected products is not fixed. I need to pass multiple productID into the report.

Also, I face a problem that ...seem I can't pass parameters into SQL functions to build dataset. Is it a rule? The error msg is "Syntax error or access violation.". My statement is as follows:

select * from getTable(@.productID)

Notes: getTable is a user-defined function.|||so what u can do

Select * from table where productid in (@.productid)|||In the following statement, getTable is a function with many calculation.

select * from getTable(@.productID)

If I use the one below, I can't get what I want. Also, seem can't be a string e.g. "12, 13, 14"
Select * from table where productid in (@.productid)|||so just pass the value in paenthesis

like '12,13,14'. it will definitely work.

If it is a function. then it should return the value same as u mentioned like "12,13,14"
if u can make it " '12,13,14' " . Then you can pass on values.

Passing Multivalue Parameters

Hello Friends,

I have a report (Say Report 1)which is asking for a multivalue parameter Period. The user selects three period from the dropdown list available for the Parameter. Lets say that the user select 0407,0507,0607. Now is this report 1 there is a particular field which is linked to another sub report. So if the top level Report 1 supplies the revenue for 3 regions say for the periods selected then clicking on any of the regions would open a second detailed report, say Report 2 for the three periods as selected.

Till this everything is fine.

Now the problem is that in this detailed Report 2 there would be a particular text box as "Click here to naviagate back to parent report". So that when the user clicks on this cell he is taken back to the parent report, Report 1 which was originally generated for the 3 periods.

Can anyone please advise as to how the Multivalue Parameter which was passed from Report 1 to Report 2 can again be looped back to Report 1 from Report 2.

Thanx Friends,

Raktim

Hello Friends,

Please let me know if I made my post too much confusing.

I am keenly awaiting a solution for this.

Thanx,

Raktim

|||

Dear ,

Please help me also to pass parameter value thru report builder to get drill thru from report1 to report2.

I done the following ways

1. Type LocalHost/Reportserver

2. Take report builder and created two reports ie, report1, report2

3. Create drill through Sql server management studio for datasource in the model in report2

4. After that when I run the report , the error is coming ' ReportQuryParameter' missing

Please help me to solve this problem

regards

|||SSRS can pass Multi-value parameters from a report(Report 1 in your case) to its linked report (Report 2 in your case).
If Report1Period and Report2Period are Multi-value parameters (and 0407, 0507, 0607, and so on are possible values), all you need to do is to check the Multi-value property in the Report Parameters dialog box for both parameters (in Report1 and Report2).
From Report1, you can then set the Navigation (? Jump to Report: (press) Parameters…) property of the textbox property that you want your user navigate to Report2. The details you need to pay attention to is that you should pick <Expression… > from the Parameter Value drop down list for Report2Period.
In the SSRS Expression editor, if you double click the Report1Period (Parameters, <All>, Report1Period), the Editor will fill in a value like “=Parameters!Report1Period.Value(0)” which picks up only the first parameter in the multiple choice list.
You can erase the index (“(0)”) part of the expression to have liked report receive all the selected value. For example, you may want to set the value passed to Report2Period like “=Parameters!Report1Period.Value”.
As navigate back, the browser Back button will work. An alternative approach is to add additional Textboxes and set their Navigation ? Jump to Report property to the way you like.

Passing multiple-values as a parameter

I am trying to drill through to a secondary report passing multiple values as a parameter.

For eg. I would like the user to select multiple years (2005,2006) from the drop-down parameter and pass that on to a secondary report.

When I select multiple values and pass that as a parameter, the parameter in the second report defaults to 'All' or in some case the first of the multiple parameters (i.e only 2005 is passed).

I don't have any problems passing single valued parameters. Any help would be appreciated.

FYI - I am using an Analysis Cube as the data source for the report.

Thanks

You can do this by using the Split function, search this forum there are plenty of examples of how to use it.

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

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=164056&SiteID=1

-- Robert

|||Thanks, I got this to work.

passing multiple values to a paramter

Hi,

I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

Nevermind, I figured it out, had to pass

Parameters! <ParameterName> .Label

as the parameter where I was linking the report. This passes the whole array with all the chosen values in it.sql

Passing Multi Value Delimited string to a parameter

Hi Everyone,
I am using, or want to use a parameter as a filter in a sql statement, the
user will pick from a list that will then send a delimited list of numbers
e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
statement with a " field IN (@.parameter) " type thing.
Im sure this should work and its probably just the phrasing or something
that ive got wrong, can anyone help?
Steve DMulti-select parameters are a feature of RS 2005. This will not work in RS
2000. You can do dynamic sql to do this (note that you do open up to
injection attacks). Switch to generic query designer (to the right of the
...). Put in something like this.
="select somefield from sometable where anotherfield in (" &
Parameters.ParamName.Value & ")"
Note that RS will not detect your field list, so first have a regular query
to populate the field list.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:OFZt89YWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I am using, or want to use a parameter as a filter in a sql statement, the
> user will pick from a list that will then send a delimited list of numbers
> e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
> statement with a " field IN (@.parameter) " type thing.
> Im sure this should work and its probably just the phrasing or something
> that ive got wrong, can anyone help?
> Steve D
>

Passing LoginName into SQL query

How do I pass the user's LoginName into the WHERE clause of a SQL query?

I created the following query, but I don't know how to get the user's LoginName and pass its value into Param1. Nothing I try works.

SELECT property.propertyid, property.shortdesc, property.shortdesclink, property.text, property.UserID, [User].UserID AS Expr1, [User].Name FROM property INNER JOIN [User] ON property.UserID = [User].UserID WHERE ([User].Name = @.Param1)

hi, when ever u connecting with sqlserver then

following code will help u in passing parameter

Dim sqlcmd as new sqlcommand

sqlcmd.commandtext =" your query"

Sqlcmd.commandtype= commandtype.text

sqlcmd.parameters.add("@.param1", "loginName")

just add the above lines i hope it will help u.

Passing Impersonation through the ReportViewer IFrame?

I am trying to impersonate a user through the web.config of the webapp that
I'm using to view reports. Reports are pulled back through the ReportViewer
object, and so, pulled back through an IFrame.
I impersonate the user which we set up in the report server, so that we can
bypass windows authentication dialog in the webapp and have impersonation do
this for us, so we can go straight into the report. When a user tries to go
to the service directly they get the prompt, and so are restricted. This is
what I'm after.
But, when I hit the report gen button on the Report Viewer it still prompts
for user/pass in a windows dialog. Is impersonation lost through the
IFrame? Does anyone have a better solution?
Appreciated,
Matt SIt sounds like you're creating a custom report manager. The call to the
report URL is separate from the call to the report manager; they are
separate web apps on the same server. In the Microsoft forms authentication
sample code, they deal with this issue by sharing session information
between the report manager and report server via an auth cookie.
I'm not sure exactly how this will apply to your situation, but hopefully
that helps you at least start tracking down the issues. See this article
for more information on the security interaction between the report manager
and report server:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Matt Swift" <matthewswift@.deletethisbitplshotmail.com> wrote in message
news:OuzKQpd4EHA.1524@.TK2MSFTNGP09.phx.gbl...
>I am trying to impersonate a user through the web.config of the webapp that
> I'm using to view reports. Reports are pulled back through the
> ReportViewer
> object, and so, pulled back through an IFrame.
> I impersonate the user which we set up in the report server, so that we
> can
> bypass windows authentication dialog in the webapp and have impersonation
> do
> this for us, so we can go straight into the report. When a user tries to
> go
> to the service directly they get the prompt, and so are restricted. This
> is
> what I'm after.
> But, when I hit the report gen button on the Report Viewer it still
> prompts
> for user/pass in a windows dialog. Is impersonation lost through the
> IFrame? Does anyone have a better solution?
> Appreciated,
> Matt S
>sql

Passing GUID into dtexec, invalid cast to a string variable. Solution?

I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.

Thanks for any help! Aaron B.

What does your SET look like, this works fine for me. I just set it, and check the value in a Script Task, just to ensure the value is being passed in OK.

/SET "\Package.Variables[StringVariable].Value";"{2B7045E0-F3D2-478a-BCC3-0E73858C59A8}"

|||

Thanks for your help i am using another field for the subquery which is not GUID and its working fine. Thanks for your help.

Passing a value from parent report to subreport

This is what I want to do:
I have the following report layout
Day Sales Expenses
1 10 2.5
2 12.5 3.2
3 10 4
4 14 7
5 12 5
When a user clicks on a day number a subreport will launch with layout
below:
Day Product Sales Expenses
* Core 2 .45
* Cobrand 2 1
* Franchise 5 1
* Other 3 3
Has anyone ever developed something like this that can give me some
help. I can pass parameters from the parent report to the subreport, I
just don't know how to pass a click action value from the parent report
to the subreport as a parameter.
Thanks in advanceHi John,
Yes have worked on it.
1. You need to create two reports. (one with day, sales, expenses 2nd with
day prod, sales,exp)
2. On the main report. go to the layout (expect u will place it on a table)
and on your "day field" just right click for properties. On the navigation
click hyperlick.
Jump to report.
3. On jump to report option select your detail file and give the parameters
if exists.
There you go you will get you reports. you will have a automatic previous
button to return it to the parent report. If you want to place a back button.
you can place a "back" textbox and give the hyperlink to the parent report.
So th users can click the back button to come to the parent. Ofcourse this is
optional.
Any doubts let me know
Amarnath
"john.r.carter@.bankofamerica.com" wrote:
> This is what I want to do:
> I have the following report layout
> Day Sales Expenses
> 1 10 2.5
> 2 12.5 3.2
> 3 10 4
> 4 14 7
> 5 12 5
> When a user clicks on a day number a subreport will launch with layout
> below:
> Day Product Sales Expenses
> * Core 2 .45
> * Cobrand 2 1
> * Franchise 5 1
> * Other 3 3
> Has anyone ever developed something like this that can give me some
> help. I can pass parameters from the parent report to the subreport, I
> just don't know how to pass a click action value from the parent report
> to the subreport as a parameter.
> Thanks in advance
>|||Amarnath,
Thank you for you reply. I have one more question. Is there a way to
filter the subreport table based on the day the user cloicked on the
parent report. Meaning, if a user clicks the 5th, the subreport will
launch with only the subreport table loaded w/ data for the 5th, no the
entire month.
Thanks in Advance,
John C|||Hi John,
Yes it works like this. when you click it takes the parameter of the clicked
field. In this case 1,2,3,5 etc.. so the sub rport takes 5 as the parameter.
Hope you have given the parameter on the "jump to report" report.
Amarnath.
"john.r.carter@.bankofamerica.com" wrote:
> Amarnath,
> Thank you for you reply. I have one more question. Is there a way to
> filter the subreport table based on the day the user cloicked on the
> parent report. Meaning, if a user clicks the 5th, the subreport will
> launch with only the subreport table loaded w/ data for the 5th, no the
> entire month.
> Thanks in Advance,
> John C
>|||Hello Armanath,
I have a report with a subreport. Both reports use the same parameters.
The user is prompted for the parameters, and they can be null. I'm
saving the parameter values in text fields on the main report and pass
them via the jump to report link. (I tried to use the original
parameters and pass them directly to the subreport, but that caused
errors and I therefore now save them on the report) I have created the
parameters for both reports,just as you described. But the sub report
does not render when one of the parameters is null. I have set the
parameters on the subreport to accept null values. I tried to set the
default value to null for the subreport parameters, but then it said
that "null" is no longer valid and I should use System.DBNull instead.
When I did that, it says that DBNull is a type in System and cannot be
used (or something similar).
Do you know what I need to do to be able to run the subreport with some
or all of the parameters being null? The error I get says" The value
for the report parameter [whatever parameter is null] is not valid for
it's type".
I would be very grateful if you have any idea of what needs to be done.
Thank you,
Mimi|||Hello Armanath,
I have a report with a subreport. Both reports use the same parameters.
The user is prompted for the parameters, and they can be null. I'm
saving the parameter values in text fields on the main report and pass
them via the jump to report link. (I tried to use the original
parameters and pass them directly to the subreport, but that caused
errors and I therefore now save them on the report) I have created the
parameters for both reports,just as you described. But the sub report
does not render when one of the parameters is null. I have set the
parameters on the subreport to accept null values. I tried to set the
default value to null for the subreport parameters, but then it said
that "null" is no longer valid and I should use System.DBNull instead.
When I did that, it says that DBNull is a type in System and cannot be
used (or something similar).
Do you know what I need to do to be able to run the subreport with some
or all of the parameters being null? The error I get says" The value
for the report parameter [whatever parameter is null] is not valid for
it's type".
I would be very grateful if you have any idea of what needs to be done.
Thank you,
Mimi|||Hello Armanath,
I have a report with a subreport. Both reports use the same parameters.
The user is prompted for the parameters, and they can be null. I'm
saving the parameter values in text fields on the main report and pass
them via the jump to report link. (I tried to use the original
parameters and pass them directly to the subreport, but that caused
errors and I therefore now save them on the report) I have created the
parameters for both reports,just as you described. But the sub report
does not render when one of the parameters is null. I have set the
parameters on the subreport to accept null values. I tried to set the
default value to null for the subreport parameters, but then it said
that "null" is no longer valid and I should use System.DBNull instead.
When I did that, it says that DBNull is a type in System and cannot be
used (or something similar).
Do you know what I need to do to be able to run the subreport with some
or all of the parameters being null? The error I get says" The value
for the report parameter [whatever parameter is null] is not valid for
it's type".
I would be very grateful if you have any idea of what needs to be done.
Thank you,
Mimi|||Nevermind. The way to solve it is to insert
<Omit>=Iif(Parameters!EndDate.Value is Nothing, True,False)</Omit>
under each parameter in the main report.
see
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate

Passing a table to a SP

I am making an SP that uses a table as an array. I want the user to pass th
e
table to the SP, where I will then iterate through it and pull out all of th
e
IDs and place them into a string that I can use with the IN keyword. Once I
get the table into the function, I will have no trouble. I just want to mak
e
sure that the table has at least a column of consecutive integers to use for
IDs while looping and another column of WorkIDs to be updated. I am making
this SP because it should be alot faster (I think) to use an IN statement
than having the SP update one record each time it is called and having to
call it many times. I figure that the client can just pass me a recordset
that they wish to be updated and I can pull the WorkIDs out of it.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi,
I'm not sure what the question is bu:
Assuming the table containing the request list is persistent (same name all
of the time):

code:

Update T1
Set T1.Col1 = @.SomeValue
From tbl_Target T1
Where Exists (Select * From tbl_List T2
Where T1.Id = T2.Id)

As long as you already have a table of keys, there is no need to manipulate
it into a string. If the table of keys also has a corresponding value to
assign, you can reference it instead of @.SomeValue and change the "Exists" t
o
a join. (Which you could do anyway here).
Good luck, and I hope I could help.
"Chris Lieb" wrote:

>
I am making an SP that uses a table as an array. I want the user to pass
the
>
table to the SP, where I will then iterate through it and pull out all of
the
>
IDs and place them into a string that I can use with the IN keyword. Once
I
>
get the table into the function, I will have no trouble. I just want to m
ake
>
sure that the table has at least a column of consecutive integers to use f
or
>
IDs while looping and another column of WorkIDs to be updated. I am makin
g
>
this SP because it should be alot faster (I think) to use an IN statement
>
than having the SP update one record each time it is called and having to
>
call it many times. I figure that the client can just pass me a recordset
>
that they wish to be updated and I can pull the WorkIDs out of it.
>
>
Thanks in advance
>
>
--
>
Chris Lieb
>
UPS CACH, Hodgekins, IL
>
Tech Support Group - Systems/Apps|||There are several approaches in t-SQL for such requirements. some of which
can be found at: http://www.sommarskog.se/arrays-in-sql.html
Anith

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 a subquery as a parameter to a user defined function

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 tabl
e.
> 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 ','.|||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 tabl
e.
> 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:
>

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 queryString to RDLC Help

I have this problem. Can someone enlightened me on how to pass a querystring to a rdlc report?

I have a aspx page "Quotation". When my user wants to print out a quotation hardcopy, he will click on an icon (it will go to Quotationhardcopy.aspx which contains the rdlc report) to print out the report. But, I am struck in how to pass the field "QuoteID" to the rdlc report so that it will only print out this quotation belonging to the quoteID..

Can someone advice me on that?

Smile
chankl78

Finally, i solved it. It is so simple & it take me almost 2 days to solve.

Just put the code inside the page Load & it works!!! :cheers:

[CODE]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim txtQuoteID As Label
Dim paraQuoteID As ReportParameter
Dim QuoteID As Integer

txtQuoteID = fvQuotation.FindControl("QuoteIDLabel")
QuoteID = txtQuoteID.Text
paraQuoteID = New ReportParameter("QuoteID", QuoteID)

Me.rvQuotationHardcopy.LocalReport.SetParameters(New ReportParameter() {paraQuoteID})
End Sub
[/CODE]

passing a parameter to TOP when getting random data

> Dear all,
> I am trying to write a stored procedure for a recipe database so that the
> user can choose how many recipes from what category.
> I have written this one to get 7 recipes from dessert category:
>
> create proc sp_ran(
>
> @.cat varchar(30)
> )
> as
> begin
>
> SELECT TOP 7 r.name,r.method FROM recipe r join Reccat rc on
> r.ID=rc.recipeID
> join category ca on ca.ID=rc.categoryID where ca.name=@.cat
> ORDER BY NEWID()
>
> end
>
> just want to know if I want the user to decide the number of recipe, how
> can
> I pass this parameter to the TOP? so a certain number of recipe will be
> randomly selected from the table?
>
> Thanks!!

Quote:

Originally Posted by crazyfisher

> Dear all,
> I am trying to write a stored procedure for a recipe database so that the
> user can choose how many recipes from what category.
> I have written this one to get 7 recipes from dessert category:
>
> create proc sp_ran(
>
> @.cat varchar(30)
> )
> as
> begin
>
> SELECT TOP 7 r.name,r.method FROM recipe r join Reccat rc on
> r.ID=rc.recipeID
> join category ca on ca.ID=rc.categoryID where ca.name=@.cat
> ORDER BY NEWID()
>
> end
>
> just want to know if I want the user to decide the number of recipe, how
> can
> I pass this parameter to the TOP? so a certain number of recipe will be
> randomly selected from the table?
>
> Thanks!!

Have one more input variable @.num in the procedure that accepts no of rows required and make use of this @.num in your query.

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 parameter into a stored procedure in a report ..........

Hi,

I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something like

Unique_Login_IPs

returns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in.

Note: the following does not work in this context : EXEC Unique_Login_IPsParameterValue nor does EXEC Some_proc_without_params

Visual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPsParameterValue" does not exist...

Thanks in advance,

Dustin L

Hi,

I am not very clear what you really need to do. What I understand is that you want to pass parameters to stored procedure right?

correct me if I am wrong, else you can do something like :

{

SqlConnection conn = new SqlConnection(DB.Config.Dsn);
conn.Open();
SqlCommand cmd = new SqlCommand("spCRMPublisherSummaryClear", conn);
cmd.CommandTimeout = 3600;
cmd.Parameters.AddWithValue("@.UserName", accountInfo.userName);
cmd.Parameters.AddWithValue("@.FirstName", accountInfo.firstName);
......so on for all your params
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

}

Hope this helps.

Please dont forget to "Mark as Answer" if this post answers your question.

Passing a datePart

How can I pass a datepart to my personal function or stored procedure.
In other words I want to allow a user to pass a datepart such as to compute
by day year, etc... The problem is that when I use the variable that has the
datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
sample of what I am trying to do:
DECLARE myDatePart varChar(1)
SET myDatePart = "d"
SELECT DATEADD(myDatePart, 1, GETDATE())
I Am trying to create my own datepart function. I do not want to create an
entire if statement to determine what type of datepart is passed into my
procedure.
Thanks in advanceInstead of creating many UDFs or single function with magic numbers as
enums, (if you have 2005) you may want to create a datetime type instead,
that way you can code methods that operate over that type and call like so
(note how intuitive it then becomes):
declare @.sd SqlDate
set @.sd = '1/1/2005'
select @.sd.Day as Day, @.sd.Month as Month, @.sd.Year as Year, @.sd.Hour as
Hour, @.sd.Minute as Minute, @.sd.Second as Second
I have example of this at:
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Angel" <Angel@.discussions.microsoft.com> wrote in message
news:64F1E3AC-6540-4BDD-99F3-3809DBCE9936@.microsoft.com...
> How can I pass a datepart to my personal function or stored procedure.
> In other words I want to allow a user to pass a datepart such as to
> compute
> by day year, etc... The problem is that when I use the variable that has
> the
> datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to create an
> entire if statement to determine what type of datepart is passed into my
> procedure.
> Thanks in advance|||"Angel" <Angel@.discussions.microsoft.com> wrote in message
news:64F1E3AC-6540-4BDD-99F3-3809DBCE9936@.microsoft.com...
> How can I pass a datepart to my personal function or stored
procedure.
> In other words I want to allow a user to pass a datepart such as
to compute
> by day year, etc... The problem is that when I use the variable
that has the
> datepart value ie: "d" or "m" or "y" it fails to recognize it.
This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to
create an
> entire if statement to determine what type of datepart is passed
into my
> procedure.
> Thanks in advance
Angel,
I am not sure how your code is running at all.
The above T-SQL returns:
SELECT DATEA.Net SqlClient Data Provider: Msg 155, Level 15, State
2, Line 1
'varChar' is not a recognized CURSOR option.
.Net SqlClient Data Provider: Msg 155, Level 15, State 1, Line 5
'myDatePart' is not a recognized dateadd option.DD(myDatePart, 1,
GETDATE())
For me.
One thing that comes to eye immediately is that there are no "@."
symbols in front of the variable names.
Try:
DECLARE @.myDatePart varChar(1)
SET @.myDatePart = "d"
As for DateAdd, the format is not:
dateadd('d', 1, GetDate())
It is:
dateadd(d, 1, GetDate())
And preferably:
dateadd(d, 1, CURRENT_TIMESTAMP)
Given that DateAdd is looking for an undelimited string literal, I
don't think you can use a variable in this parameter location (but I
don't know that for sure).
Sincerely,
Chris O.|||Security considerations aside, this does the trick:
CREATE PROCEDURE test4 @.dp char(1)
AS
BEGIN
DECLARE @.querystring nvarchar(512)
SET @.querystring = 'SELECT DATEADD(' + @.dp + ', 1, GETDATE())'
EXEC sp_executesql @.querystring
END
EXEC dbo.test4 @.dp='d'
What's strange is using sp_executesql another way does not work. For example
execute sp_executesql
N'SELECT DATEADD(dd, @.daystoadd, GETDATE())',
N'@.daystoadd int',
@.daystoadd = 4
This will work; passing the second parameter @.daystoadd as an integer. But
execute sp_executesql
N'SELECT DATEADD(@.dp, 4, GETDATE())',
N'@.dp varchar(4)',
@.dp = 'dd'
gets the error "Invalid parameter 1 specified for dateadd." Any ideas anyone
?
"Angel" wrote:

> How can I pass a datepart to my personal function or stored procedure.
> In other words I want to allow a user to pass a datepart such as to comput
e
> by day year, etc... The problem is that when I use the variable that has t
he
> datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to create an
> entire if statement to determine what type of datepart is passed into my
> procedure.
> Thanks in advance|||You might not have wanted to use IF statements, but CASE works well enough
for a finite set of ten possibilities
CREATE PROCEDURE test6 @.dp char(2), @.count int, @.dateout datetime OUTPUT
AS
BEGIN
IF (@.dp NOT IN ('yy','qq','mm','dy','wk','dd','hh','mi'
,'ss','ms'))
BEGIN
RAISERROR('wrong date format',16,1)
RETURN 1
END
ELSE
BEGIN
SELECT @.dateout =
CASE
WHEN @.dp = 'yy' THEN DATEADD(yy,@.count,GETDATE())
WHEN @.dp = 'qq' THEN DATEADD(qq,@.count,GETDATE())
WHEN @.dp = 'mm' THEN DATEADD(mm,@.count,GETDATE())
WHEN @.dp = 'dy' THEN DATEADD(dy,@.count,GETDATE())
WHEN @.dp = 'wk' THEN DATEADD(wk,@.count,GETDATE())
WHEN @.dp = 'dd' THEN DATEADD(dd,@.count,GETDATE())
WHEN @.dp = 'hh' THEN DATEADD(hh,@.count,GETDATE())
WHEN @.dp = 'mi' THEN DATEADD(mi,@.count,GETDATE())
WHEN @.dp = 'ss' THEN DATEADD(ss,@.count,GETDATE())
WHEN @.dp = 'ms' THEN DATEADD(ms,@.count,GETDATE())
END
END
END
DECLARE @.dateout datetime
EXEC dbo.test6 'yy', 9, @.dateout OUTPUT
SELECT @.dateout
"Angel" wrote:
> That is a good idea but I need to get the result of the DateAdd into a
> variable within the same stored procedure.
> thanks in advance
> "Mark Williams" wrote:
>|||That is a good idea but I need to get the result of the DateAdd into a
variable within the same stored procedure.
thanks in advance
"Mark Williams" wrote:
> Security considerations aside, this does the trick:
> CREATE PROCEDURE test4 @.dp char(1)
> AS
> BEGIN
> DECLARE @.querystring nvarchar(512)
> SET @.querystring = 'SELECT DATEADD(' + @.dp + ', 1, GETDATE())'
> EXEC sp_executesql @.querystring
> END
> EXEC dbo.test4 @.dp='d'
> What's strange is using sp_executesql another way does not work. For examp
le
> execute sp_executesql
> N'SELECT DATEADD(dd, @.daystoadd, GETDATE())',
> N'@.daystoadd int',
> @.daystoadd = 4
> This will work; passing the second parameter @.daystoadd as an integer. But
> execute sp_executesql
> N'SELECT DATEADD(@.dp, 4, GETDATE())',
> N'@.dp varchar(4)',
> @.dp = 'dd'
> gets the error "Invalid parameter 1 specified for dateadd." Any ideas anyo
ne?
> "Angel" wrote:
>

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

Passing "hidden parameter" to Report via URL

Hi,

We are trying to pass a parameter ("hidden from user") to our report which we
access via an URL. Has anyone done this successfully? If so please outline the steps
and we'll be greatful forever!

Note: We have the latest Reporting Services software SP2 installed.
We have MS SQL Server Standard Edition - and thus do not have Report Manager on our server.

Any parameters passed using the GET method (parameters over the URL) will be visible to the user. One option you have is encrypting the value before sending it.
HTH|||Thanks for your reply - however we are not concerned with the URL line itself. The problem is that the
value never gets to the report and we get an error message about the parameter being 'read-only'.
If anyone has successfully passed an URL parameter to a report please document and share the steps! Many Thanks!
We have latest version of Reporting Services SP2, and MS SQL Server 2000 standard version.|||OK. Found the answer:
1) Make sure Reporting Services SP2 is installed.
2) Set up the report as if the user will be selecting /entering the parameter value.
3) Make sure the Report runs correctly.
4) Go back into the Report Designer and change the prompt string to one blank.
5) Test in designer (the parameter name will be used as a default prompt).
6) Deploy
7) Run report - passing parameter in the URL. This works, and you will not see a prompt.|||

Hi, I am facing the same problem. Can any one please provide me 'real' solution(code example) how to do it.

jrokita has tried to ans the Q but not enough support. The main Q was how to do it with URL. Not steps the way it will be done.

Thanks in advance.