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
Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts
Friday, March 30, 2012
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?
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
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.
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 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.
>
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 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
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 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
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:
%5B%5D%26+ or%20Or 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
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
>
>
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 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 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
>
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
>
Passing null parameters via Query-String
I'm trying to call a report, passing my parameters via query-string...
I have one parameter that can receive null values...
Following Books Online, the syntax for null values is :isnull, giving the following sample of URL:
http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
In my report, I shadowed this syntax, it looks like the following:
http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
If I pass a common value to func_cd_matricula, like 1 or 2, it works fine, but with this isnull syntax, it gives me the following error:
"The path of the item '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The full path must be less than 260 characters long, must start with slash; other restrictions apply. Check the documentation for complete set of restrictions."
Does anybody know why this?
Thanks,
Rafa®BOL is incorrect.
Right syntax is
sales:isNull=true
Lev
http://blogs.msdn.com/levs
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rafa®" <Rafa®@.discussions.microsoft.com> wrote in message
news:4308595B-5A3C-4F4D-ADAD-D9288B71B926@.microsoft.com...
> I'm trying to call a report, passing my parameters via query-string...
> I have one parameter that can receive null values...
> Following Books Online, the syntax for null values is :isnull, giving the
> following sample of URL:
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
> In my report, I shadowed this syntax, it looks like the following:
> http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
> If I pass a common value to func_cd_matricula, like 1 or 2, it works fine,
> but with this isnull syntax, it gives me the following error:
> "The path of the item
> '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The
> full path must be less than 260 characters long, must start with slash;
> other restrictions apply. Check the documentation for complete set of
> restrictions."
> Does anybody know why this?
> Thanks,
> Rafa®sql
I have one parameter that can receive null values...
Following Books Online, the syntax for null values is :isnull, giving the following sample of URL:
http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
In my report, I shadowed this syntax, it looks like the following:
http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
If I pass a common value to func_cd_matricula, like 1 or 2, it works fine, but with this isnull syntax, it gives me the following error:
"The path of the item '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The full path must be less than 260 characters long, must start with slash; other restrictions apply. Check the documentation for complete set of restrictions."
Does anybody know why this?
Thanks,
Rafa®BOL is incorrect.
Right syntax is
sales:isNull=true
Lev
http://blogs.msdn.com/levs
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rafa®" <Rafa®@.discussions.microsoft.com> wrote in message
news:4308595B-5A3C-4F4D-ADAD-D9288B71B926@.microsoft.com...
> I'm trying to call a report, passing my parameters via query-string...
> I have one parameter that can receive null values...
> Following Books Online, the syntax for null values is :isnull, giving the
> following sample of URL:
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
> In my report, I shadowed this syntax, it looks like the following:
> http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
> If I pass a common value to func_cd_matricula, like 1 or 2, it works fine,
> but with this isnull syntax, it gives me the following error:
> "The path of the item
> '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The
> full path must be less than 260 characters long, must start with slash;
> other restrictions apply. Check the documentation for complete set of
> restrictions."
> Does anybody know why this?
> Thanks,
> Rafa®sql
Passing null parameters to subreports
This is similar to Carlos' question on 7/7/05 that nobody responded to.
Please respond.
(http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
a Parent report, I setup a hyperlink to jump to another report that accepts a
number of parameters (some of the parameters are defined as nullable in the
parent and the calling report). However when I try to preview the report and
click on the hyperlink cell passing parameters that are null in addition to
parameters that are populated, the subreport seems to hang and wait for
input. I tried so many variations of this and it just does't work. It only
works if all the parameters are populated, but that is not realistic.
Can someone please suggest a solution ASAP? This looks like a bug to me.
This is not rocket science here.
Thanks.
--
-RB
:)I had this problem too....
The solution is to manually change the .rdl file with the <Omit> element
under the
<Parameter> element...
Please check
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
for more details...
"capricorn" wrote:
> This is similar to Carlos' question on 7/7/05 that nobody responded to.
> Please respond.
> (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> a Parent report, I setup a hyperlink to jump to another report that accepts a
> number of parameters (some of the parameters are defined as nullable in the
> parent and the calling report). However when I try to preview the report and
> click on the hyperlink cell passing parameters that are null in addition to
> parameters that are populated, the subreport seems to hang and wait for
> input. I tried so many variations of this and it just does't work. It only
> works if all the parameters are populated, but that is not realistic.
> Can someone please suggest a solution ASAP? This looks like a bug to me.
> This is not rocket science here.
> Thanks.
> --
> -RB
> :)|||Hi Trisha.
Thank you for responding. It worked like a charm. What a relief.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||I also noticed that after I changed the .RDL file and go back into report
designer and save the report again for some other change, all my omit
statements are wiped out. Is there any way to stop this from happening?
Thanks.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||No, there is not. RS designer rewrites the RDL.
Something to try which I would like to hear if you are successfull is to map
the subreport parameter to an expression and put this in for the expression:
= Nothing
I think that might work for you but I am not sure since I have never needed
to do this myself. Let me know if it works. It would be easier than messing
with the RDL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"capricorn" <capricorn@.discussions.microsoft.com> wrote in message
news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable in
>> > the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)|||=Nothing will work for subreport and drillthrough report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O9bbI%23HtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to
> map the subreport parameter to an expression and put this in for the
> expression:
> = Nothing
> I think that might work for you but I am not sure since I have never
> needed to do this myself. Let me know if it works. It would be easier than
> messing with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>>I also noticed that after I changed the .RDL file and go back into report
>> designer and save the report again for some other change, all my omit
>> statements are wiped out. Is there any way to stop this from happening?
>> Thanks.
>> --
>> -RB
>> :)
>>
>> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded
>> > to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable
>> > in the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait
>> > for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)
>|||Save the rdl file and then go to the designer...I've had no problems after
doing it this way....
"capricorn" wrote:
> I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
> > I had this problem too....
> > The solution is to manually change the .rdl file with the <Omit> element
> > under the
> > <Parameter> element...
> >
> > Please check
> > http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> > for more details...
> >
> >
> >
> > "capricorn" wrote:
> >
> > > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > > Please respond.
> > > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> > >
> > > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > > number of parameters (some of the parameters are defined as nullable in the
> > > parent and the calling report). However when I try to preview the report and
> > > click on the hyperlink cell passing parameters that are null in addition to
> > > parameters that are populated, the subreport seems to hang and wait for
> > > input. I tried so many variations of this and it just does't work. It only
> > > works if all the parameters are populated, but that is not realistic.
> > >
> > > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > > This is not rocket science here.
> > >
> > > Thanks.
> > > --
> > > -RB
> > > :)|||I tried mapping the drillthrough parameters to an expression that contains:
=Nothing
It did not work. Since one of the parameters is an integer datatype, I got
the following error message:
"The value provided for the report parameter ... is not valid for its type."
--
-RB
:)
"Bruce L-C [MVP]" wrote:
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to map
> the subreport parameter to an expression and put this in for the expression:
> = Nothing
> I think that might work for you but I am not sure since I have never needed
> to do this myself. Let me know if it works. It would be easier than messing
> with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
> >I also noticed that after I changed the .RDL file and go back into report
> > designer and save the report again for some other change, all my omit
> > statements are wiped out. Is there any way to stop this from happening?
> > Thanks.
> > --
> > -RB
> > :)
> >
> >
> > "Trisha" wrote:
> >
> >> I had this problem too....
> >> The solution is to manually change the .rdl file with the <Omit> element
> >> under the
> >> <Parameter> element...
> >>
> >> Please check
> >> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> >> for more details...
> >>
> >>
> >>
> >> "capricorn" wrote:
> >>
> >> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> >> > Please respond.
> >> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >> >
> >> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
> >> > cell of
> >> > a Parent report, I setup a hyperlink to jump to another report that
> >> > accepts a
> >> > number of parameters (some of the parameters are defined as nullable in
> >> > the
> >> > parent and the calling report). However when I try to preview the
> >> > report and
> >> > click on the hyperlink cell passing parameters that are null in
> >> > addition to
> >> > parameters that are populated, the subreport seems to hang and wait for
> >> > input. I tried so many variations of this and it just does't work. It
> >> > only
> >> > works if all the parameters are populated, but that is not realistic.
> >> >
> >> > Can someone please suggest a solution ASAP? This looks like a bug to
> >> > me.
> >> > This is not rocket science here.
> >> >
> >> > Thanks.
> >> > --
> >> > -RB
> >> > :)
>
>
Please respond.
(http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
a Parent report, I setup a hyperlink to jump to another report that accepts a
number of parameters (some of the parameters are defined as nullable in the
parent and the calling report). However when I try to preview the report and
click on the hyperlink cell passing parameters that are null in addition to
parameters that are populated, the subreport seems to hang and wait for
input. I tried so many variations of this and it just does't work. It only
works if all the parameters are populated, but that is not realistic.
Can someone please suggest a solution ASAP? This looks like a bug to me.
This is not rocket science here.
Thanks.
--
-RB
:)I had this problem too....
The solution is to manually change the .rdl file with the <Omit> element
under the
<Parameter> element...
Please check
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
for more details...
"capricorn" wrote:
> This is similar to Carlos' question on 7/7/05 that nobody responded to.
> Please respond.
> (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> a Parent report, I setup a hyperlink to jump to another report that accepts a
> number of parameters (some of the parameters are defined as nullable in the
> parent and the calling report). However when I try to preview the report and
> click on the hyperlink cell passing parameters that are null in addition to
> parameters that are populated, the subreport seems to hang and wait for
> input. I tried so many variations of this and it just does't work. It only
> works if all the parameters are populated, but that is not realistic.
> Can someone please suggest a solution ASAP? This looks like a bug to me.
> This is not rocket science here.
> Thanks.
> --
> -RB
> :)|||Hi Trisha.
Thank you for responding. It worked like a charm. What a relief.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||I also noticed that after I changed the .RDL file and go back into report
designer and save the report again for some other change, all my omit
statements are wiped out. Is there any way to stop this from happening?
Thanks.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||No, there is not. RS designer rewrites the RDL.
Something to try which I would like to hear if you are successfull is to map
the subreport parameter to an expression and put this in for the expression:
= Nothing
I think that might work for you but I am not sure since I have never needed
to do this myself. Let me know if it works. It would be easier than messing
with the RDL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"capricorn" <capricorn@.discussions.microsoft.com> wrote in message
news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable in
>> > the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)|||=Nothing will work for subreport and drillthrough report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O9bbI%23HtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to
> map the subreport parameter to an expression and put this in for the
> expression:
> = Nothing
> I think that might work for you but I am not sure since I have never
> needed to do this myself. Let me know if it works. It would be easier than
> messing with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>>I also noticed that after I changed the .RDL file and go back into report
>> designer and save the report again for some other change, all my omit
>> statements are wiped out. Is there any way to stop this from happening?
>> Thanks.
>> --
>> -RB
>> :)
>>
>> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded
>> > to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable
>> > in the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait
>> > for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)
>|||Save the rdl file and then go to the designer...I've had no problems after
doing it this way....
"capricorn" wrote:
> I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
> > I had this problem too....
> > The solution is to manually change the .rdl file with the <Omit> element
> > under the
> > <Parameter> element...
> >
> > Please check
> > http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> > for more details...
> >
> >
> >
> > "capricorn" wrote:
> >
> > > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > > Please respond.
> > > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> > >
> > > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > > number of parameters (some of the parameters are defined as nullable in the
> > > parent and the calling report). However when I try to preview the report and
> > > click on the hyperlink cell passing parameters that are null in addition to
> > > parameters that are populated, the subreport seems to hang and wait for
> > > input. I tried so many variations of this and it just does't work. It only
> > > works if all the parameters are populated, but that is not realistic.
> > >
> > > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > > This is not rocket science here.
> > >
> > > Thanks.
> > > --
> > > -RB
> > > :)|||I tried mapping the drillthrough parameters to an expression that contains:
=Nothing
It did not work. Since one of the parameters is an integer datatype, I got
the following error message:
"The value provided for the report parameter ... is not valid for its type."
--
-RB
:)
"Bruce L-C [MVP]" wrote:
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to map
> the subreport parameter to an expression and put this in for the expression:
> = Nothing
> I think that might work for you but I am not sure since I have never needed
> to do this myself. Let me know if it works. It would be easier than messing
> with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
> >I also noticed that after I changed the .RDL file and go back into report
> > designer and save the report again for some other change, all my omit
> > statements are wiped out. Is there any way to stop this from happening?
> > Thanks.
> > --
> > -RB
> > :)
> >
> >
> > "Trisha" wrote:
> >
> >> I had this problem too....
> >> The solution is to manually change the .rdl file with the <Omit> element
> >> under the
> >> <Parameter> element...
> >>
> >> Please check
> >> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> >> for more details...
> >>
> >>
> >>
> >> "capricorn" wrote:
> >>
> >> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> >> > Please respond.
> >> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >> >
> >> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
> >> > cell of
> >> > a Parent report, I setup a hyperlink to jump to another report that
> >> > accepts a
> >> > number of parameters (some of the parameters are defined as nullable in
> >> > the
> >> > parent and the calling report). However when I try to preview the
> >> > report and
> >> > click on the hyperlink cell passing parameters that are null in
> >> > addition to
> >> > parameters that are populated, the subreport seems to hang and wait for
> >> > input. I tried so many variations of this and it just does't work. It
> >> > only
> >> > works if all the parameters are populated, but that is not realistic.
> >> >
> >> > Can someone please suggest a solution ASAP? This looks like a bug to
> >> > me.
> >> > This is not rocket science here.
> >> >
> >> > Thanks.
> >> > --
> >> > -RB
> >> > :)
>
>
Wednesday, March 28, 2012
Passing non-URL Friendly Parameters?
I have a parameter in my SQL report which is a system account number. This
account number often contains non-URL friendly characters (i.e. '<', '>',
';', ' ' , etc...). How can I create some type of substitition function to
work around this issue?
I reall need to pass the parameter through the URL. Thank you in advance.
- MaxWhat instead of using the GET method, you use the POST method to send your
parameters
Med Bouchenafa
"Max Tyack" <MaxTyack@.discussions.microsoft.com> a écrit dans le message de
news: D3AC72E4-77AE-4410-91E3-DC2DAB367842@.microsoft.com...
>I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max|||Do you need the link on a static page or from an other report?
Reporting Services should recognize escaped uri parameters. So instead of
'http://Hello World' you can use 'http://Hello%20World'. The same applies to
<, > and so on. The % values are the hex-ascii values, so you might need to
look up an ascii table and do a string replace ( like strUri.Replace(" ",
"%20") )
In .NET 2.0 you should look up for the Uri class and use EscapeUriString or
EscapeDataString.
I hope it's understandable, else you can try it at
http://www.greymana.net/examples/escape.html . For Non-URI friendly
parameters the Dest (param) might be more intersting. It's all in client-side
javascript.
"Max Tyack" wrote:
> I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max
account number often contains non-URL friendly characters (i.e. '<', '>',
';', ' ' , etc...). How can I create some type of substitition function to
work around this issue?
I reall need to pass the parameter through the URL. Thank you in advance.
- MaxWhat instead of using the GET method, you use the POST method to send your
parameters
Med Bouchenafa
"Max Tyack" <MaxTyack@.discussions.microsoft.com> a écrit dans le message de
news: D3AC72E4-77AE-4410-91E3-DC2DAB367842@.microsoft.com...
>I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max|||Do you need the link on a static page or from an other report?
Reporting Services should recognize escaped uri parameters. So instead of
'http://Hello World' you can use 'http://Hello%20World'. The same applies to
<, > and so on. The % values are the hex-ascii values, so you might need to
look up an ascii table and do a string replace ( like strUri.Replace(" ",
"%20") )
In .NET 2.0 you should look up for the Uri class and use EscapeUriString or
EscapeDataString.
I hope it's understandable, else you can try it at
http://www.greymana.net/examples/escape.html . For Non-URI friendly
parameters the Dest (param) might be more intersting. It's all in client-side
javascript.
"Max Tyack" wrote:
> I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max
Passing Multivalued Parameters through URL interface
Hi,
We are trying to generate report by passing parameter values through url
In our report, we have a multivalued parameter named "ABC", having values
say, val1, val2 and val3.
When try to generate the report with parameter ABC having values as val1 and
val2 using the url mentioned below, we find that the values are not selected
and report is not rendered.
http://<machinename>/ReportServer/Pages/ReportViewer.aspx?<reportpath>0&rs:Command=Render¶meter=val1,val2
This is working fine for singlevalued parameters.
Thanks in advanced,
Sumit PilankarA comma separated list of values in the QueryString doesn't do it.
I know one way to do it, but it isn't real elegant.
rather than this:
¶m=val1,val2,val3
you do this:
¶m=val1¶m=val2¶m=val3
Andy Potter|||how about UrlEncode(val1,val2,val3)?|||Thank you very much for the suggestion, it worked for me.:)
"Potter" <drewpotter@.gmail.com> wrote in message
news:1137178379.083160.191810@.o13g2000cwo.googlegroups.com...
>A comma separated list of values in the QueryString doesn't do it.
> I know one way to do it, but it isn't real elegant.
> rather than this:
> ¶m=val1,val2,val3
> you do this:
> ¶m=val1¶m=val2¶m=val3
> Andy Potter
>|||Thanks for the help.
UrlEncode is required, but we need to form the URL as Andy Potter said.
"Q. John Chen" <qjchen@.email.com> wrote in message
news:1137183444.756449.131090@.g43g2000cwa.googlegroups.com...
> how about UrlEncode(val1,val2,val3)?
>
We are trying to generate report by passing parameter values through url
In our report, we have a multivalued parameter named "ABC", having values
say, val1, val2 and val3.
When try to generate the report with parameter ABC having values as val1 and
val2 using the url mentioned below, we find that the values are not selected
and report is not rendered.
http://<machinename>/ReportServer/Pages/ReportViewer.aspx?<reportpath>0&rs:Command=Render¶meter=val1,val2
This is working fine for singlevalued parameters.
Thanks in advanced,
Sumit PilankarA comma separated list of values in the QueryString doesn't do it.
I know one way to do it, but it isn't real elegant.
rather than this:
¶m=val1,val2,val3
you do this:
¶m=val1¶m=val2¶m=val3
Andy Potter|||how about UrlEncode(val1,val2,val3)?|||Thank you very much for the suggestion, it worked for me.:)
"Potter" <drewpotter@.gmail.com> wrote in message
news:1137178379.083160.191810@.o13g2000cwo.googlegroups.com...
>A comma separated list of values in the QueryString doesn't do it.
> I know one way to do it, but it isn't real elegant.
> rather than this:
> ¶m=val1,val2,val3
> you do this:
> ¶m=val1¶m=val2¶m=val3
> Andy Potter
>|||Thanks for the help.
UrlEncode is required, but we need to form the URL as Andy Potter said.
"Q. John Chen" <qjchen@.email.com> wrote in message
news:1137183444.756449.131090@.g43g2000cwa.googlegroups.com...
> how about UrlEncode(val1,val2,val3)?
>
Passing Multi-Value parameters to a Data Driven Subscription
Hi,
I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.
As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.
Has anyone tried this before?
Thanks,
Dan
please search on "Multi-Value parameters to a Data Driven Subscription" in this forum.Passing Multivalue Parameters Through a Report Link
I've created a line chart in my report which lists number of transactions by month. I've also created a report to list transactions by day. My goal is for the user to select a month and link the report to the graph with the selected month divided up into days (in other words, the user wants to see the number of transactions for each day in the selected month).
Anyway, everything is working perfectly except for one thing. In the list of parameters that I am passing to the report for each day, I want to pass a multivalue parameter which contains all the transaction IDs the user selected in creating the report (these are supplied by a multivalue parameter). However, in the parameter dialog box where it asks for a value to send to the awaiting parameter, I do not know how to supply more than one value. If I need to pass one value, it will work fine. However, I would like to do something like:
JOIN(Parameters!TransactionID.value, ",")
for the value of TransactionID, but when I generate the report it is not accepting the values. I'm pretty sure its just a format issue, and I just need to know how exactly I should pass these values.
I already understand that when passing multivalue parameters in a URL you need something like:
TransactionID=1&TransactionID=2
...in order to select multiple values. However, this is a slightly different situation. I'm really running out of ideas, so any help would be much appreciated.
Thank you guys so much
can you please paste your select statement, multivalue parameters are automatically rendered if you have something like this in your select
select * from employees where managerid IN(select managerid from managers)
|||Have you tried:
SELECT *
FROM Managers
WHERE ManagerID in (@.ManagerParm)
This works in Oracle (except for : instead of @.) and I would be surprised if it did not work in SQL server.
NOTE you will not be able to test it using the !, but it it will work for the report.
|||I'm sorry I wasn't clear. The project I have is in RS 2005 and uses an analysis services data source and the select statement is entirely in MDX. However, the problem really isn't in the select statement. My problem is that I am creating a report link and I want to pass all the values selected in a multivalue parameter to the other report.
If you want to see reproduce the problem, try these steps:
Create a report with a multivalue parameter. Add a textbox. Right-click and select properties. Click the Navigation tab and select "Jump to Report". Select a valid report that accepts a multivalue parameter. Click the parameters button. In the parameter name column, select the multivalue parameter to receive the values. In the parameter value column, I need to pass the values for the current multivalue parameter. This is the problem I am having.
|||Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1
It describes the various scenarios of passing multi value parameters to a drillthrough or subreport. In your case, it should be sufficient to just specify =Parameters!ParameterName.Value to pass all values along.
However, note that if the target report contains a parameterized MDX query and you want to use the passed-in parameter values directly in that query, the parameter values in the main report must represent the UniqueNames (not the caption which is usually the parameter label) - otherwise the target MDX query will most likely not work.
-- Robert
|||Thank you, it works perfectly!!sqlPassing 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 in 1 varchar variable in a stored proc IN
This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w
of any SQL language class. SQL is not your original
procedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w
of any SQL language class. SQL is not your originalprocedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w
of any SQL language class. SQL is not your original> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w
of any SQL language class. SQL is not your original> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
passing multiple parameters to subreport
Hello Group,
Is it possible to pass multiple parameters down to a subreport? If so how?
I am using this code in the report to show the contents of the parameters on
the top of the report.
Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""
For Each sParamItem In Parameter
If sParamItem Is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next
'-- Remove last comma & space:
Return sParamVal.SubString(0, sParamVal.Length - 2)
End Function
Therefore I was expecting to use this to pass it through like this:
Code.Parameterlist(Parameters!Division.Value)
But that doesnt work unfortunately. Does anybody know an approach that
works....
Thanx
PerryDoes anybody have a clue?
thanx again
Perry
"Perry" <sjaak@.sjaak.net> wrote in message
news:OjSF%23CdzGHA.576@.TK2MSFTNGP03.phx.gbl...
> Hello Group,
> Is it possible to pass multiple parameters down to a subreport? If so how?
> I am using this code in the report to show the contents of the parameters
> on the top of the report.
> Function ParameterList(ByVal Parameter As Object) As String
> Dim sParamItem As Object
> Dim sParamVal As String = ""
> For Each sParamItem In Parameter
> If sParamItem Is Nothing Then Exit For
> sParamVal &= sParamItem & ", "
> Next
> '-- Remove last comma & space:
> Return sParamVal.SubString(0, sParamVal.Length - 2)
> End Function
>
> Therefore I was expecting to use this to pass it through like this:
> Code.Parameterlist(Parameters!Division.Value)
> But that doesnt work unfortunately. Does anybody know an approach that
> works....
> Thanx
> Perry
>
>
>
Is it possible to pass multiple parameters down to a subreport? If so how?
I am using this code in the report to show the contents of the parameters on
the top of the report.
Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""
For Each sParamItem In Parameter
If sParamItem Is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next
'-- Remove last comma & space:
Return sParamVal.SubString(0, sParamVal.Length - 2)
End Function
Therefore I was expecting to use this to pass it through like this:
Code.Parameterlist(Parameters!Division.Value)
But that doesnt work unfortunately. Does anybody know an approach that
works....
Thanx
PerryDoes anybody have a clue?
thanx again
Perry
"Perry" <sjaak@.sjaak.net> wrote in message
news:OjSF%23CdzGHA.576@.TK2MSFTNGP03.phx.gbl...
> Hello Group,
> Is it possible to pass multiple parameters down to a subreport? If so how?
> I am using this code in the report to show the contents of the parameters
> on the top of the report.
> Function ParameterList(ByVal Parameter As Object) As String
> Dim sParamItem As Object
> Dim sParamVal As String = ""
> For Each sParamItem In Parameter
> If sParamItem Is Nothing Then Exit For
> sParamVal &= sParamItem & ", "
> Next
> '-- Remove last comma & space:
> Return sParamVal.SubString(0, sParamVal.Length - 2)
> End Function
>
> Therefore I was expecting to use this to pass it through like this:
> Code.Parameterlist(Parameters!Division.Value)
> But that doesnt work unfortunately. Does anybody know an approach that
> works....
> Thanx
> Perry
>
>
>
Subscribe to:
Posts (Atom)