Passing parameter in MDX query

Hi,
I am facing problem during passing the parameters in MDX query. I need tp
pass two parameter in my dataset, but not sure whether I am writing correct
query or not. as I am getting error during parsing the query.
here the query
SELECT {
[Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
} on columns,
NON EMPTY NonEmptyCrossJoin(
[Component Parts].[Part].Members,
NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
Code].Members)
)
on rows FROM [CUBE NAME]
WHERE (" + Parameters!ManufactureDate.Value + ", " +
Parameters!Suppliername.Value + ")
--
The two parameter I have declared in parameter box with multiselect value
option.
while executiing the query I am gettiign belwo message.
"The tuple function expects a tuplet set of expression for the argument.A
string or numeric was used"
Please help me to write the correct syntax for this mdx.
Appreciate your help
Regards
SomuMosha Pasumansky has written an article called "Writing multiselect friendly
MDX calculations":
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
Maybe you'll find your answer there. :)
Have you tried your query without parameters in an MDX parser? If not, try
it and you might figure out how the query should be before trying with the
parameters.
Kaisa M. Lindahl
"Somu" <Somu@.discussions.microsoft.com> wrote in message
news:65941D3D-8CBB-494C-9E95-3F4FD983E31F@.microsoft.com...
> Hi,
> I am facing problem during passing the parameters in MDX query. I need tp
> pass two parameter in my dataset, but not sure whether I am writing
> correct
> query or not. as I am getting error during parsing the query.
> here the query
> SELECT {
> [Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
> } on columns,
> NON EMPTY NonEmptyCrossJoin(
> [Component Parts].[Part].Members,
> NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
> Code].Members)
> )
> on rows FROM [CUBE NAME]
> WHERE (" + Parameters!ManufactureDate.Value + ", " +
> Parameters!Suppliername.Value + ")
> --
> The two parameter I have declared in parameter box with multiselect value
> option.
> while executiing the query I am gettiign belwo message.
> "The tuple function expects a tuplet set of expression for the argument.A
> string or numeric was used"
> Please help me to write the correct syntax for this mdx.
> Appreciate your help
> Regards
> Somu

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).
|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

sql

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

passing parameter from vb 6 to crystal report 8

I am trying to pass value to the parameter field which i have created in crystal report 8. the parameter field is of type date. but i get error. the code is as follows.

.Reset
.ReportFileName = App.Path & "\reports\vaccine_flow_ledger.rpt"
.Connect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=APACCESS"
.DiscardSavedData = True

.ParameterFields(0) = "SDT;" & #1/7/2005#
.ParameterFields(1) = "EDT;" & #1/7/2005#

.Action = 1
End With

Please help me to resolve this problem.Try this

.ParameterFields(0) = "SDT;"# & 1/7/2005 & #"
.ParameterFields(1) = "EDT;"# & #1/7/2005& #"|||I tried that one,but i didn't work.

but

.parameterfields(0) = "sdt;" & "date(2005,1,1);"

works finesql

passing parameter from stored proc to crystal

Im using crystal 11.5.

Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.

Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.

In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.

What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.

How do I get crystal to prompt me when I add the stored proc to the report?

passing parameter FROM REPORT TO WINDOWS APP

Hi all. Is it possible to pass parameter FROM Report to my windows application(C#) ? Report is made in Business Intelligence Project. I just want to retrieve the total row number to my windows appication. Is it possible? If yes, Can you provide codes for this? Thanks. Your help would be highly appreciated.

-Ron-

Yes you can pass parameter values from your report to your windows application.

Have a default value for your parameter which is the count of rows of the table and then use Values property of ReportParametersInfo class ro retrieve the value.

Shyam

|||Excluding creative approaches such as exporting the report to HTML and parsing the HTML payload, in general the user has to initiate an action explicitly in the report. Then, assuming you use the Windows Report Viewer, you can sink the event. See the hypelink example in this article.|||

Shyam - I have no idea of what you are saying. Can you provide me sample codes for this? thanks.

Teo Lachev - yes i am using windows report viewer. can't find the hyperlink example in link you gave.

Please Help me....

-Ron-

|||In the link I gave you, there is another link that will bring you to a DevX article. From there you can download a code sample that shows how to sink a hyperlink event.

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 parameter from Asp.Net

Dear Sir,
How can reporting servicing receive parameter from Asp.net program?You could call a report via URL access and pass the parameter to the URL.
Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> Dear Sir,
> How can reporting servicing receive parameter from Asp.net program?
>|||Yes, via the SOAP API Render() method. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
for an example.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"yoclee" <yoclee@.discussions.microsoft.com> wrote in message
news:886688CD-0C5F-41D4-88E8-8134AD61D579@.microsoft.com...
> Can we pass the parameter using others method instead of using URL?
> "Ravi Mumulla (Microsoft)" wrote:
> > You could call a report via URL access and pass the parameter to the
URL.
> > Refer to
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
> > for details.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> > > Dear Sir,
> > > How can reporting servicing receive parameter from Asp.net program?
> > >
> > >
> >
> >
> >sql

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 parameter

I have a stored procedure named "processInventory" like the following.
Depending on the passed in parameters, I would like to add a WHERE
clause for "select" action. For example, if any varchar type of
parameter is passed in, the where clause would use "LIKE" operator. For
example, "Select * from Main where [s/n] like @.Serial. All other types
will use "=" operator. For example, "Select * from Main where MAKE =
@.Make and Type = @.type".
How could this be achieved? Thanks.

CREATE PROCEDURE processInventory
@.Action varchar(7),
@.ControlNumber int = null,
@.AssetTag int = null,
@.Serial varchar(50) = null,
@.Description varchar(50) = null,
@.Make int = null,
@.Type int = null,
@.Model int = null,
@.Status int = null,
@.Networked bit = null,
@.LoginName varchar(50) = null,
@.Shared bit = null,
@.Org varchar(15) = null,
@.RecordDate datetime = null,
@.LastUpdate datetime = null,
@.ManufactureDate datetime = null,
@.Comment ntext = null

AS

declare @.processError int
set @.processError = 0

if @.Action = 'Select' goto selectInventory
else
If @.Action = 'Update'
begin
if @.ControlNumber = null return(1) --Required parameter value not
specified
else
goto updateInventory
end
else
if @.Action = 'Insert'
begin
if @.Serial = null return(1) --Required parameter value not
specified
else
goto InsertInventory
end
else
if @.Action = 'Delete'
begin
if @.ControlNumber = null return(1) --Required parameter value
not specified
else goto deleteInventory
end

selectInventory:
if @.Serial <> null
begin
select * from Main where [S/N] like @.Serial
if @.@.Error<>0
begin
set @.processError = @.@.Error
return @.processError
end
end
else
if @.ControlNumber <> null
begin
select * from Main where ControlNumber = @.ControlNumber
if @.@.Error <>0
begin
set @.processError = @.@.Error
return @.processError
end
end
else
select top 100* from Main

updateInventory:
update MAIN
set [Org Asset Tag] = @.AssetTag, [S/N] = @.Serial, [Description]
= @.Description, Make = @.Make, Type = @.Type,
Model = @.Model, Status = @.Status, Networked = @.Networked,
LoginName = @.LoginName, Shared = @.Shared,
Org = @.Org, [Date Of Record] = @.RecordDate, [Date Last
Updated] = @.LastUpdate, [Manuf Date] = @.ManufactureDate,
Comments = @.Comment
where ControlNumber = @.ControlNumber
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else
return(0) -- successful update

insertInventory:
insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,
Model, Status, Networked, LoginName, Shared,
Org, [Date Of Record], [Date Last Updated], [Manuf
Date],Comments)
values(@.AssetTag, @.Serial, @.Description, @.Make, @.Type, @.Model,
@.Status, @.Networked, @.LoginName, @.Shared,
@.Org, @.RecordDate, @.LastUpdate, @.ManufactureDate,
@.Comment)
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else return(0) -- successful insert

deleteInventory:
delete MAIN where ControlNumber = @.ControlNumber
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else return(0) -- successful delete
GOFirst, I would suggest that you not lump all of your actions together
in one stored procedure; you will suffer from a performance impact,
because SQL Server will be forced to recompile your procedure every
time it runs (for SELECT, UPDATE, or DELETE). This is never a good
idea.

That being said, you could set the default value of the parameter you
wish to use wildcards on as a wildcard ('%'); later, in the body of the
stored procedure, add a wildcard character to the value before you use
it in the query. A simple example is below:

CREATE PROC procTestWildcard @.Param varchar(10) = '%' AS

SET @.Param = @.Param + '%'

SELECT Column
FROM Table
WHERE Column Like @.Param

--

Running the following

exec procWildCardTest

will return all of the data in your table since you've essentially run
the statement

SELECT Column
FROM Table
WHERE Column Like '%%'

The statement

exec procWildCardTest 'S'

will return all of the data in your table that starts with the letter
'S', since the SQL statement is now interpreted as

SELECT Column
FROM Table
WHERE Column Like 'S%'

HTH,
Stu|||Thanks for your suggestion. As you can see, I have more than one
parameter that might be passed into the proc. How do I dermine which
one is passed in? If I use IF..ELSE, there would be many combination
of parameters. I don't think SQL2000 allow concation of partitial
statments, so each combination need to be dealt with like
IF @.Make <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @.Make
ELSE
IF @.Make <> NULL AND @.Model <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @.Make and MODEL LIKE
@.Model
ELSE
other paramter combination|||The suggestion I gave above will work for any number of paramater
combinations. Not the most effecient way, but it will work.

CREATE PROC procTestParams (@.Make varchar(10) = '%', @.Model varchar(10)
= '%') AS

SET @.Make = @.Make+'%'
SET @.Model = @.Model+'%'

SELECT COL1, COL2
FROM TABLE
WHERE MAKE LIKE @.Make
and MODEL LIKE @.Model

Another way to do this is to build your SQL string dynamically and use
sp_executeSQL

CREATE PROC procTestParams (@.Make varchar(10) =NULL, @.Model
varchar(10) = NULL) AS

DECLARE @.SQL nvarchar(4000)

/*Return all records by default; need a basic true WHERE condition so
that you can
append AND's to it as needed*/

SET @.SQL = 'SELECT COL1, COL2 FROM TABLE WHERE 1=1 '

IF @.Make IS NOT NULL
SET @.SQL =@.SQL + ' AND Make LIKE @.Make ' --make sure that you are
passing wildcards if needed

IF @.Model IS NOT NULL
SET @.SQL =@.SQL + ' AND Model LIKE @.Model '

exec sp_executeSQL @.SQL, N'@.Make varchar(10), @.Model varchar(10)',
@.Make, @.Model

You'll just have to play around with it to see which is more effecient;
the first version will basically run a search against all parameters,
looking for wildcards (any data) on the columns you don't specify a
value for, whereas the second version will dynamically build a SQL
statement to be executed against only those columns you supply a
parameter for. The effeciency of either approach is going to be
affected by the number and atype of indexes on your table, and the
amount of data to be returned.

Hope that clarifies.

Stu|||[posted and mailed, please reply in news]

js (androidsun@.yahoo.com) writes:
> I have a stored procedure named "processInventory" like the following.
> Depending on the passed in parameters, I would like to add a WHERE
> clause for "select" action. For example, if any varchar type of
> parameter is passed in, the where clause would use "LIKE" operator. For
> example, "Select * from Main where [s/n] like @.Serial. All other types
> will use "=" operator. For example, "Select * from Main where MAKE =
> @.Make and Type = @.type".
> How could this be achieved? Thanks.

I have a longer article on the topic on
http://www.sommarskog.se/dyn-search.html.

Since you are into UPDATE, I would careful with using dynamic SQL
because of the permissions issues.

--
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 Parameter

I've to create an instance reference to my custom assembly by constructor
method.
It works well with a Code into the report .rdl like this:
---
<Code>
Public Obj As MyClass
Protected Overrides Sub OnInit()
dim MyArg as String = 4
Obj = new MyClass(MyArg)
End Sub
</Code>
--
But i need to pass a real parameter so:
Obj = new MyClass(Parameters!MyPar.Value)
it don't work and break with the error
BC30469 "The reference to a member not shared needs a reference to an object"
(I translate this from the italian version.... sorry for my bad english!!!
How can i do'
Thanks a lot.Try using this reference.
Report.Parameters!MyPar.Value
that should work in the Code.section
--
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
"Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> I've to create an instance reference to my custom assembly by constructor
> method.
> It works well with a Code into the report .rdl like this:
> ---
> <Code>
> Public Obj As MyClass
> Protected Overrides Sub OnInit()
> dim MyArg as String = 4
> Obj = new MyClass(MyArg)
> End Sub
> </Code>
> --
> But i need to pass a real parameter so:
> Obj = new MyClass(Parameters!MyPar.Value)
> it don't work and break with the error
> BC30469 "The reference to a member not shared needs a reference to an
> object"
> (I translate this from the italian version.... sorry for my bad
> english!!!
> How can i do'
> Thanks a lot.|||This not works, but the error is changed with a message box:
" Unable to load the assembly expressions. The expression refers to a
nonexistent parameter in the parameters's collection.".
This is not true, becouse the parameter exists.
Can you help me again?
"Wayne Snyder" wrote:
> Try using this reference.
> Report.Parameters!MyPar.Value
> that should work in the Code.section
> --
> 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
> "Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
> news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> > I've to create an instance reference to my custom assembly by constructor
> > method.
> > It works well with a Code into the report .rdl like this:
> > ---
> > <Code>
> > Public Obj As MyClass
> > Protected Overrides Sub OnInit()
> > dim MyArg as String = 4
> > Obj = new MyClass(MyArg)
> > End Sub
> > </Code>
> > --
> > But i need to pass a real parameter so:
> > Obj = new MyClass(Parameters!MyPar.Value)
> > it don't work and break with the error
> > BC30469 "The reference to a member not shared needs a reference to an
> > object"
> > (I translate this from the italian version.... sorry for my bad
> > english!!!
> > How can i do'
> > Thanks a lot.
>
>

Passing paramers in URL to Sharepoint Deep Integrated SSRS Report

I am currently struggling with this one, can anyone help with this supposedly
simple problem.
I am trying to pass report parameters to a Report that exists on A
Sharepoint Site that has had SSRS Deep Intergration applied. Can someone give
me the typical URL to do this, and let me know the parameter settings I need
to set against the RDL.
Reading the msdn stuff suggests it is as simple as adding
¶mname=paramvalue
to the URL that points to the RDL file, but my parameters are being ignored
totally. I have tried playing with the Prompt and default settings of the RDL
file, but whatever I try my URL passed parameter fails. Is there even some
security settings somewhere along the chain that is preventing this query
string interpretation?
This must be something very simple and straight forward for any Sharepoint
SSRS Deep Integration experts out there (are there any of those out there
yet?)
Maybe Microsoft could even assist on this one under the 48 hours response
promise this time?
--
Steve GHello Steve,
I am sorry that currently our internal application got some error and we
did not get your post. So we did not response at the quick manner.
I would like to know what URL you are using to access the Report.
You could see that when we use the URL to access the erport, we use the
ReportServer.
So you may try to use like this:
http://servername/reportserver?/Sales/YearlySalesSummary&rs:Command=Render&r
s:ShowHideToggle=13¶metername=value
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei, I'm having the same problem as the person who started this
thread. The problem is stemming from the fact that in SP2 when you
configure a server for "integrated mode with SharePoint" and not
"native mode", reports (the rdl files) are stored directly in a
SharePoint document library and you can no longer load them using the
"/reportserver?" type syntax for the URL. You have to reference them
in the document library, not on the report server. Now, I've gotten a
parameterless report to render but if an RDL file needs parameters it
ignores any parameters I set in the URL string. The report loads with
this error:
"Report parameter values must be specified before the report can be
displayed. Choose parameter values in the parameters area and click
the Apply button."
which is not true because I did pass them. Here is a sample URL I use:
https://www.mycompany.com/mysite/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/mysite/myDocumentLibrary/test.rdl&KeyDate=10/5/2007
This report has only 1 parameter (KeyDate) and it ignores what I pass
in the URL string. PLEASE HELP. I've posted questions about this since
April 07 and nobody has given me a solution yet. I can of course send
parameters to a report using the "Jump To Report" method within the
report designer, but many people like myself prefer to use the "Jump
To URL" method because we can use javascript to launch the drilldown
report in a new window. That's the only reason I don't use "Jump To
Report" since "Jump To Report" always replaces the currently loaded
report with the drilldown report. Very annoying. If the RS development
team would add that feature to let us selectively open a drilldown in
a new window, then this issue would be solved. I've wanted that
feature since RS 2000 and still nothing yet.
Another related problem is that I used to be able to use the Globals
collection such as Globals.ReportName, Globals.ReportServerURL, etc
but they no longer work in SharePoint integrated mode. This is a big
problem too, because I now have to hard code my www.company.com URL in
every drilldown link of every report. That's a big maintainance issue
especially when going from a development to a production server URL.
So far, I'm disappointed with SharePoint integrated mode because of
these shortcomings. SSRS SP2 highly publicized the fantastic
integration with SharePoint, wo-ho! And I adopted and bought into it
and moved our reports to this mode. But these drawbacks are terribly
disappointing. Plus, the reports render much slower because of the 2
server model, but I'm disgressing here...
Does anyone out there using SharePoint mode have answers to these
issues'|||Hello Dave,
According to the product team, this issue maybe the product limitation and
they are still looking for the workaround yet.
Once I got any response, I will update ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Nice Wei Lu/Microsoft. It is a feature of SSRS that passing parameters
through a URL doesn't work. Talk about being LAZY... we've been looking for
answers on this for months and your answer is barely better than 'thank you
drive through'. Im having the same problem without explanation and without
Sharepoint integration. Back to Crystal for us I guess.
"Wei Lu [MSFT]" wrote:
> Hello Dave,
> According to the product team, this issue maybe the product limitation and
> they are still looking for the workaround yet.
> Once I got any response, I will update ASAP.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Passing Paramenter to SP for Column Name

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@.SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@.SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type
int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michaelhi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@.selectColumn varchar(255))
as
set nocount on
declare @.dynamicSql varchar(8000)

select @.dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @.selectColumn + ' = 0)
'
execute (@.dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:
> I need to select status values form 1 of 4 possible columns, and I
need to
> pass the column name to select on as a parameter to the stored
procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
> SET NOCOUNT ON;
> SELECT *
> FROM Documents
> WHERE (@.SelectColumn = 0)
> The columns to select on are BIT columns.
> The error message on the above SP is:
> 'Syntax error converting the nvarchar value 'P' to a column of data
type
> int.'
> At this point, the passed in parameter is a string "ProducerStatus"
> Thanks
> Michael|||Thanks for the help. It worked great.

"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.z14g2000cwz.googlegr oups.com...
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
> create procedure dbo.sp_Document_select_bystatus
> (@.selectColumn varchar(255))
> as
> set nocount on
> declare @.dynamicSql varchar(8000)
> select @.dynamicSql = '
> SELECT *
> FROM Documents
> WHERE ( ' + @.selectColumn + ' = 0)
> '
> execute (@.dynamicSql)
> set nocount off
> Go
> Thank you
> santhosh
> Michael Jackson wrote:
>> I need to select status values form 1 of 4 possible columns, and I
> need to
>> pass the column name to select on as a parameter to the stored
> procedure.
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
>> (
>> @.SelectColumn nVarChar
>> )
>> AS
>>
>> SET NOCOUNT ON;
>>
>> SELECT *
>> FROM Documents
>> WHERE (@.SelectColumn = 0)
>>
>> The columns to select on are BIT columns.
>>
>> The error message on the above SP is:
>>
>> 'Syntax error converting the nvarchar value 'P' to a column of data
> type
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus"
>>
>> Thanks
>> Michael|||Avoid dynamic SQL if you can. In this case you don't need it:

SELECT col1
FROM Documents
WHERE col1 = 0 AND @.selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @.selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @.selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @.selectcolumn = 'col4'

To understand why dynamic SQL isn't a good idea for this, see:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--|||Michael Jackson (stratojack@.cox.net) writes:
> I need to select status values form 1 of 4 possible columns, and I need to
> pass the column name to select on as a parameter to the stored procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS

To add to the other responses, permit me to point out two other flaws:

1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.

2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Passing output parameter from procedure to variable

I have a stored procedure that runs a query and the result of the query is a
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:

> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks

Passing Out Parameter to Sybase Stored Proc from RS

I am defining the Output parameter from Sybase Stored Proc.
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql

PASSING ORDER BY AS PARAMETERS

I have a stored procedure that excepts @.orderby as a parameter
and executes following sql statement:
select * from titles
order by
case when @.orderby = 'title' then
title
end
asc
Is there anyway I can add second parameter @.direction and control order
ASC/DESC based on parameter value , like "case when @.direction then asc"?
Thanks in advance
Programmerhttp://www.aspfaq.com/2501
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||You can use:
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
However, the plan for such a query would result in a table scan plus a sort
operation.
If you want to allow an efficient plan based on the input, you should either
use dynamic execution, which has it's obvious limitations (SQL injection
attacks and other security issues), or the following static approach:
if @.col = 'title' and @.dir = 'asc'
select * from titles order by title
else if @.col = 'title' and @.dir = 'desc'
select * from titles order by title desc
else if @.col = 'price' and @.dir = 'asc'
select * from titles order by price
else if @.col = 'price' and @.dir = 'desc'
select * from titles order by price desc
...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||Hi
You can try as
exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sergey Zuyev" wrote:

> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||It's not quite that simple.
Create Procedure MyProcedure
@.OrderBy nvarchar(12)
AS
Select * /*We really should list the field names, but this is an
example*/ ,
OrderMeBy = Case @.OrderBy
When 'title' Then title
When 'Price_Asc' Then cast(Price as nvarchar(20))
When 'Price_Desc' Then cast(Price *-1 as nvarchar(20)) /*negated
to go in descending order*/
When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
'12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
recent dates a lower value, thus descending*/
END
From Titles
Order By OrderMeBy
What I wrote actually has a few sorting problems due to the CAST's, but it
demonstrates the method. I don't have a nice little formula for turning
text around to make it sort backwards like I do for numeric fields and
dates.
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||And what if the user specifies "delete from titles" as the value of @.order ?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
> Hi
> You can try as
> exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sergey Zuyev" wrote:
>|||Daniel, using multiple CASE expressions instead of one, where each deals
with one column only, solves all the issues related to differences in
datatypes.
Check out the solution I proposed. You will also find a treatment for the
direction issue.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> It's not quite that simple.
> Create Procedure MyProcedure
> @.OrderBy nvarchar(12)
> AS
> Select * /*We really should list the field names, but this is an
> example*/ ,
> OrderMeBy = Case @.OrderBy
> When 'title' Then title
> When 'Price_Asc' Then cast(Price as nvarchar(20))
> When 'Price_Desc' Then cast(Price *-1 as nvarchar(20))
> /*negated
> to go in descending order*/
> When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
> When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
> '12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
> recent dates a lower value, thus descending*/
> END
> From Titles
> Order By OrderMeBy
>
> What I wrote actually has a few sorting problems due to the CAST's, but it
> demonstrates the method. I don't have a nice little formula for turning
> text around to make it sort backwards like I do for numeric fields and
> dates.
> hth,
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>|||well if u specify the lenght to be 4, then the string would not be accepted
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"JT" wrote:

> And what if the user specifies "delete from titles" as the value of @.order
?
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
>
>|||Thanks, Itzik.
I saw your solution after I'd posted -- I got the basics of my solution here
on this group about 4 years ago. Perhaps SQL Server 7 did not support that
syntax?
Regardless, your solution is much cleaner & I will be putting it to work in
the future.
dwlison
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%235n$t8OnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Daniel, using multiple CASE expressions instead of one, where each deals
> with one column only, solves all the issues related to differences in
> datatypes.
> Check out the solution I proposed. You will also find a treatment for the
> direction issue.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
more
it
asc"?
>|||> I don't have a nice little formula for turning
> text around to make it sort backwards
Well, the other solutions are better, and this is far from efficient, but it
can be done. This assumes a case insensitive collation.
CREATE FUNCTION dbo.SortReverse
( @.in VARCHAR(32) )
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.i TINYINT, @.out VARCHAR(32)
SELECT @.i = 1, @.out = ''
WHILE @.i <= len(@.in)
BEGIN
SELECT @.out = @.out + CHAR(90+65-ASCII(UPPER(SUBSTRING(@.in, @.i, 1))))
SET @.i = @.i + 1
END
RETURN @.out
END
GO
Now you can do this:
DECLARE @.sortOrder VARCHAR(4)
SET @.sortOrder = 'ASC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
SET @.sortOrder = 'DESC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END

Passing Order By as parameter

A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
solution:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/t
hread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/u
rl]
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
Now I have a situation with multiple fields on which to sort, one of them
being descending. How can I use this method to come out with ORDER BY Price
DESC, Title ?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
DVBrown Company
(864)292-5888You could try ORDER BY -price
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:uEOm$UzzFHA.908@.tk2msftngp13.phx.gbl...
>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
> solution:
> [url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm
/thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[
/url]
> select * from titles
> order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
> Now I have a situation with multiple fields on which to sort, one of them
> being descending. How can I use this method to come out with ORDER BY
> Price
> DESC, Title ?
> Thanks.
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> DVBrown Company
> (864)292-5888
>|||On Wed, 12 Oct 2005 09:59:33 -0400, Daniel Wilson wrote:

>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
>solution:
>[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/
thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/
url]
>select * from titles
>order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
>Now I have a situation with multiple fields on which to sort, one of them
>being descending. How can I use this method to come out with ORDER BY Pric
e
>DESC, Title ?
>Thanks.
Hi Daniel,
select * from titles
order by
case when @.col1 = 'title' and @.dir1 = 'asc' then title end,
case when @.col1 = 'title' and @.dir1 = 'desc' then title end desc,
case when @.col1 = 'price' and @.dir1 = 'asc' then price end,
case when @.col1 = 'price' and @.dir1 = 'desc' then price end desc,
case when @.col2 = 'title' and @.dir2 = 'asc' then title end,
case when @.col2 = 'title' and @.dir2 = 'desc' then title end desc,
case when @.col2 = 'price' and @.dir2 = 'asc' then price end,
case when @.col2 = 'price' and @.dir2 = 'desc' then price end desc,
..
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

passing or defaulting null

I wanted to know what are the advantages/divantages of passing null value
s
in the sp as oppose to setting them as default in the tables.
I am using SQL 2005, "set ANSI_NULLS ON".
ThanksMumbai_Chef wrote:
> I wanted to know what are the advantages/divantages of passing
> null values in the sp as oppose to setting them as default in the
> tables.
> I am using SQL 2005, "set ANSI_NULLS ON".
>
If you have, for example, a DateEntered column, which will always need to be
initialized to the current date and time when a row is inserted and never
subsequently updated, then by all means create a default constraint for the
column. This allows you to never even have to mention the column in any
UPDATE/INSERT DML queries, and as a result, you never have to declare a
parameter for this columns value in any of your procedures.
On the other hand, if you have a column for which you will sometimes be
providing a non-default value, then obviously you will need to include a
parmaeter for that value in your stored procedures. Whether or not you
declare the parameter with a default value (making it an optional parameter)
is totally up to you.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Passing OLAP-parameters between reports

I have to reports, ReportA and ReportB. Both uses datasets that are based on
OLAP cubes. Both reports has parameters and works fine.
My challenge:
When clicking in a field in ReportA, I want to navigate to ReportB, sending
the appropriate parameters from ReportA to ReportB. The first parameter I
send is the value of a fiield "Location", for instance "Seattle". However,
when I send this to reportB (which takes, amongst others, a location
parameter), the value "Seattle" is sent, and not the value which is
understood by ReportB's parameter, that is "[Dim Location].[Location].1".
To accomplsh my task I think I somehow need to print the value "[Dim
Location].[Location].1" in a hidden field in ReportA and send this fields
value to ReportB. But how do get both Seattle and [Dim
Location].[Location].1" as member values of the same OLAP dimension?
Hope I made my self clear enough here. It is a bit difficult to explain, and
don't hesitate to ask me if there is something you didn't understand.
Thanks in advance for your helpYou can return the OLAP dimension member's Unique name in your query, like
this:
with member [Measures].[MyName] as '[Gender].currentmember.name'
member [Measures].[MyLevelOrdinal] as '[Gender].currentmember.Level.Ordinal'
member [Measures].[MyUniqueName] as '[Gender].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
member [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
select {[Measures].[MyName], [Measures].[MyLevelOrdinal],
[Measures].[MyUniqueName], [Measures].[MyMeasure4], [Measures].[MyMeasure5],
[Measures].[MyMeasure6]} on columns,
{filter([Gender].members, [Measures].[Unit Sales] > 0) } on rows
from [Sales]
(try with Foodmart 2000)
In your case, this should return something like
MyName = Seattle
MyLevelOrdinal = Seattle's level ordinal
MyUniquName = [Dim Location].[Location].1
LIke: member [Measures].[MyUniqueName] as '[Dim
Location].currentmember.UniqueName'
Kaisa M. Lindahl
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:35966F0A-9112-487A-A8EC-BF6E3F2E39C3@.microsoft.com...
>I have to reports, ReportA and ReportB. Both uses datasets that are based
>on
> OLAP cubes. Both reports has parameters and works fine.
> My challenge:
> When clicking in a field in ReportA, I want to navigate to ReportB,
> sending
> the appropriate parameters from ReportA to ReportB. The first parameter I
> send is the value of a fiield "Location", for instance "Seattle". However,
> when I send this to reportB (which takes, amongst others, a location
> parameter), the value "Seattle" is sent, and not the value which is
> understood by ReportB's parameter, that is "[Dim Location].[Location].1".
>
> To accomplsh my task I think I somehow need to print the value "[Dim
> Location].[Location].1" in a hidden field in ReportA and send this fields
> value to ReportB. But how do get both Seattle and [Dim
> Location].[Location].1" as member values of the same OLAP dimension?
> Hope I made my self clear enough here. It is a bit difficult to explain,
> and
> don't hesitate to ask me if there is something you didn't understand.
> Thanks in advance for your help

Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

passing olap parameters in url

hi. could anyone pls tell me how to pass parameter values on url to an olap
RS 2005 report? i tried to do it as such and got an error.
parameter name is AbsenceTypesAbsenceTypes
parameter value is =[Absence Types].[Absence Types].[Absence Type].&[Absent
Days]
this is the link:
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCA_Reports%2fcAtt_01&rs:Command=Render&AbsenceTypesAbsenceTypes=[Absence
Types].[Absence Types].[Absence Type].&[Absent Days]
this is the error:
The path of the item "/CA_Reports/cAtt_01,[Absent Days]" is not valid. The
path must be less than 260 characters long and must not start with slash.
Other restrictions apply.
it's obvious that the & in the value of the parameter is causing this
problem.
also if somebody knows of a site where i can find help on this subject. i've
searched a lot and none talks about the current version of RS with olap.
ThanksURL parameter values always must be encoded. E.g. & needs to be encoded as
%26, [ would be %5B, ] would be %5D.
Lookup the HttpUtility class on MSDN - it provides a static method
HttpUtility.HtmlEncode to perform this encoding.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"TinaWina" <TinaWina@.discussions.microsoft.com> wrote in message
news:1B900061-7EF8-4FC5-AA0D-EE61C20E5E53@.microsoft.com...
> hi. could anyone pls tell me how to pass parameter values on url to an
> olap
> RS 2005 report? i tried to do it as such and got an error.
> parameter name is AbsenceTypesAbsenceTypes
> parameter value is =[Absence Types].[Absence Types].[Absence
> Type].&[Absent
> Days]
> this is the link:
> http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCA_Reports%2fcAtt_01&rs:Command=Render&AbsenceTypesAbsenceTypes=[Absence
> Types].[Absence Types].[Absence Type].&[Absent Days]
> this is the error:
> The path of the item "/CA_Reports/cAtt_01,[Absent Days]" is not valid. The
> path must be less than 260 characters long and must not start with slash.
> Other restrictions apply.
> it's obvious that the & in the value of the parameter is causing this
> problem.
> also if somebody knows of a site where i can find help on this subject.
> i've
> searched a lot and none talks about the current version of RS with olap.
> Thanks|||Thank you very much.
It worked:)
"Robert Bruckner [MSFT]" wrote:
> URL parameter values always must be encoded. E.g. & needs to be encoded as
> %26, [ would be %5B, ] would be %5D.
> Lookup the HttpUtility class on MSDN - it provides a static method
> HttpUtility.HtmlEncode to perform this encoding.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TinaWina" <TinaWina@.discussions.microsoft.com> wrote in message
> news:1B900061-7EF8-4FC5-AA0D-EE61C20E5E53@.microsoft.com...
> > hi. could anyone pls tell me how to pass parameter values on url to an
> > olap
> > RS 2005 report? i tried to do it as such and got an error.
> > parameter name is AbsenceTypesAbsenceTypes
> > parameter value is =[Absence Types].[Absence Types].[Absence
> > Type].&[Absent
> > Days]
> >
> > this is the link:
> > http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCA_Reports%2fcAtt_01&rs:Command=Render&AbsenceTypesAbsenceTypes=[Absence
> > Types].[Absence Types].[Absence Type].&[Absent Days]
> >
> > this is the error:
> > The path of the item "/CA_Reports/cAtt_01,[Absent Days]" is not valid. The
> > path must be less than 260 characters long and must not start with slash.
> > Other restrictions apply.
> >
> > it's obvious that the & in the value of the parameter is causing this
> > problem.
> >
> > also if somebody knows of a site where i can find help on this subject.
> > i've
> > searched a lot and none talks about the current version of RS with olap.
> >
> > Thanks
>
>

Passing of parameter

Need help for the passing of parameter in CR9.

How to pass the "record_id" from main report to the sql command of subreport. To clarify, i add a sql command in database expert in fmy subreport. This is my query....

For the main report..

Select record_id, distributor_name... from distributor

In the sql command of my subreport....

Select record_id, distributor_name, customer_id, sum(sales) as amount where record_id =2 group by record_id, distributor_name, customer_id
order by amount desc limit 20.

All i want to do is how to pass the record_id from the main report into the sql command of my subreport to look like this....

Select record_id, distributor_name, customer_id, sum(sales) as amount where record_id =("record_id from main_report") group by record_id, distributor_name, customer_id
order by amount desc limit 20.

My goal is how to view the top 20 accounts per distributor

Thanx a lot!!!1. Make main report
2. make sub report with same parameters.
3. In Design mode of main report, edit->subreport links.
Select main report parameter and link it to subreport parameter.

Passing Object Variable as input parameter to an Execute SQL Task Query

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

Please give me solutions for the above two..

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||

Darren,

Thanks for your solution,

Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.

When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.

Moreover I want the solution for the step 2 as well|||

DarrenSQLIS wrote:

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx

@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)

Just cast it to an integer and all will be well.

select cast(@.@.IDENTITY as int) as 'Ident'|||

Thanks Phil,

It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.

There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

|||

Subhash wrote:

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

That works for me, using OLE DB connection though. What type of connection are you using?

|||

Hi Rafael,

I am using OLEDB connection.

When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.

Passing object properties to variables

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

One way you could accomplish this is by using a Script task.

You can access the properties of all the connections in your Connection Manager through the Dts ScriptObjectModel .

For example, the following code grabs the ConnectionString of my "currentFolder" connection object, and stores it in the "myConnectionString" variable.

PublicSub Main()

Dim value AsString

value = Dts.Connections("currentFolder").ConnectionString

Dts.Variables("myConnectionString").Value = value

'MsgBox(Dts.Variables("myConnectionString").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

Be sure to include your variable (ie. User::myConnectionString) in the ReadWriteVariables property of your script task.

Is this what you meant?

|||Yes, this is exactly what I meant. Thanks!

Passing NULL-value into package variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.

This link shows one of the ways of doing that.|||

Dennis_v_E wrote:

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

Its not possible.

One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.

Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.

Something like that anyway. You get the idea.

-Jamie

|||

Thanx,

To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.

Dennis

sql

Passing null value parameter to a data-driven subscription

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

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

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

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

Thanks in advance for your assistance.

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

Thank you,

Dave

|||

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

Hope that helps.

Regards,

Greg

Passing null value parameter to a data-driven subscription

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

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

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

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

Thanks in advance for your assistance.

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

Thank you,

Dave

Passing NULL value not working

I currently have a stored procedure that looks something like this

SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@.Name+'%', Title)

I have a form that supplies this value. This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not. On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,

SqlDataSource1.SelectParameters.Add("@.Name", TextBox1.Text);

It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why. Thank you in advance

/jcarver

Try this:

WHERE ([Title] Like '%' + @.Name + '%') or ( @.Name is NULL)"

And add this to your SqlDatasource: CancelSelectOnNullParameter="False"

|||

The two where clauses are logically equivalent.

I'm a bit curious why you have a column called title and a parameter called @.name instead of @.title.

As for passing in a null, you could check the length of the textbox text, and if it's 0, send DBValue instead of the Text property.

|||

Try

IF @.Name IS NULL SET @.Name = ''

IF DATALENGTH(@.Name) > 0
SELECT * FROM tblQuestions WHERE Title LIKE '%'+@.Name+'%'
ELSE
SELECT * FROM tblQuestions

|||

SELECT * FROM tblQuestions WHERE Title LIKE '%' + @.Name + '%'

ConvertEmptyStringToNull must be set to False!

sql

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 NULL to DataSet parmameter

Hey All,

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

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

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

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

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

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

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

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

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

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

Thanks in advance.

Eric Wild

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

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

Hello Eric,

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

Jarret

|||

Jarret,

That worked!

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

Here is steps on how to fix this.

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

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

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

-Check Allow Nulls

- Check Hidden

-Verify Default Value NULL is bubbled in below.

Thanks again

Eic Wild

passing null reportparameter values

Hi,
Iâ'm using the report viewer object to view a report which works fine until I
need to pass null values. When I use the following code I get â'The
'reportId' parameter is missing a valueâ':
ReportViewer1.ShowParameterPrompts = false;
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("reportId");
parameters[1] = new ReportParameter("userid", "123");
ReportViewer1.ServerReport.SetParameters(parameters);
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.Refresh();
The reportId is setup for null values, however it still gives me an error:
<ReportParameter Name="reportId">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<Prompt>reportId</Prompt>
</ReportParameter>
I know other people have had this problem but using â'new
ReportParameter("reportId")â' doesnâ't work for me.
Thanks in advance!I'm guessing here, but do you need to explicitly set the value of the
null parameter. DBNull isn't the same as .Net C# null.
Regards, Rhys
On Sep 21, 11:50 pm, Nathan <nathan.et...@.online.nospam> wrote:
> Hi,
> I'm using the report viewer object to view a report which works fine until I
> need to pass null values. When I use the following code I get "The
> 'reportId' parameter is missing a value":
> ReportViewer1.ShowParameterPrompts = false;
> ReportParameter[] parameters = new ReportParameter[2];
> parameters[0] = new ReportParameter("reportId");
> parameters[1] = new ReportParameter("userid", "123");
> ReportViewer1.ServerReport.SetParameters(parameters);
> ReportViewer1.ProcessingMode => Microsoft.Reporting.WebForms.ProcessingMode.Remote;
> ReportViewer1.ServerReport.Refresh();
> The reportId is setup for null values, however it still gives me an error:
> <ReportParameter Name="reportId">
> <DataType>Integer</DataType>
> <Nullable>true</Nullable>
> <Prompt>reportId</Prompt>
> </ReportParameter>
> I know other people have had this problem but using "new
> ReportParameter("reportId")" doesn't work for me.
> Thanks in advance!

passing null report parameter via a url

Hi,
I have a SP in sql with parameters that can get null values such as:
@.paramguf tinyint=null,
AS
select 1 as kodKibuz,name
from learningstuds
where
(@.paramguf is null or hasamaguf=@.paramguf)
I am sending the report parameters via vb.net
Dim URL As String
URL = "http://localhost/reportserver/reports/tutorials?%2fTutorials%2fKibuzKlita1"
URL += "&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
URL += "¶mdate=" + Date32.saveDate().ToShortDateString
URL += "¶mguf=" + ddlGuf.SelectedValue
'Pass the URL as a Web request
'Dim request As HttpWebRequest = WebRequest.Create(URL)
Response.Redirect(URL)
How can I send a null value to the report for paramguf?
Thanksquoting KB article http://support.microsoft.com/kb/842853
You can also pass a null value for a parameter to a report by adding
'param:isnull=true' to the URL. For example, if the SalesOrderNumber is a
Null-valued report parameter that must be passed to the report, add the
'SalesOrderNumber:isnull=true' string to the URL."
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:72BB20CA-9B8F-48F6-A3C8-6CCA1049EBE6@.microsoft.com...
> Hi,
> I have a SP in sql with parameters that can get null values such as:
> @.paramguf tinyint=null,
> AS
> select 1 as kodKibuz,name
> from learningstuds
> where
> (@.paramguf is null or hasamaguf=@.paramguf)
> I am sending the report parameters via vb.net
> Dim URL As String
> URL => "http://localhost/reportserver/reports/tutorials?%2fTutorials%2fKibuzKlita1"
> URL += "&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
> URL += "¶mdate=" + Date32.saveDate().ToShortDateString
> URL += "¶mguf=" + ddlGuf.SelectedValue
> 'Pass the URL as a Web request
> 'Dim request As HttpWebRequest = WebRequest.Create(URL)
> Response.Redirect(URL)
>
> How can I send a null value to the report for paramguf?
> Thanks
>