Reporting Services 2005 and jump-to report navigation

Hi, I have used Jump-To report navigation in RS 2000 successfully to
navigate from one report to another, passing a report parameter to the
second report from a click event from a table in the parent report.
However, when I try to do the same thing in RS 2005, when I set up the
navigation jump-to report, the form picks up the report I want to
jump-to, but not any of the report parameters.
Has anyone found this problem and has anyone solved it?
I am using the April CTP install of Business Intelligence Studio
(Client Tools) connecting to an April CTP of Yukon on another server.
Thanks for any helpI believe we saw this issue and fixed it in the upcoming CTP15(June). I
tried a simple JumpTo report with CTP15 using a parameter and it works fine.
--
| From: "MDXQuery" <imgroup1@.hotmail.com>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Reporting Services 2005 and jump-to report navigation
| Date: 31 May 2005 07:31:18 -0700
| Organization: http://groups.google.com
| Lines: 13
| Message-ID: <1117549878.146860.257860@.o13g2000cwo.googlegroups.com>
| NNTP-Posting-Host: 212.135.29.68
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1117549883 20456 127.0.0.1 (31 May 2005
14:31:23 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 31 May 2005 14:31:23 +0000 (UTC)
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: o13g2000cwo.googlegroups.com; posting-host=212.135.29.68;
| posting-account=BkKmtgwAAAA2HVQbpzZ1Z_YN45g-L9kZ
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!o1
3g2000cwo.googlegroups.com!not-for-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45004
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi, I have used Jump-To report navigation in RS 2000 successfully to
| navigate from one report to another, passing a report parameter to the
| second report from a click event from a table in the parent report.
| However, when I try to do the same thing in RS 2005, when I set up the
| navigation jump-to report, the form picks up the report I want to
| jump-to, but not any of the report parameters.
| Has anyone found this problem and has anyone solved it?
|
| I am using the April CTP install of Business Intelligence Studio
| (Client Tools) connecting to an April CTP of Yukon on another server.
|
| Thanks for any help
|
|sql

Reporting Services 2000 Filtering based on a text parameter

I've looked through the reporting services help and it's very vague on how to do this.

What I want to do is set up a prompt with three values; Open, Closed, and Due for Completion.

Based on the user selection then a filter will be inserted, something like:If the user selects "Open" then Datatable.Status not in ('Closed', 'Cancelled', 'On Hold'), if the user selects "Closed" then Datatable.Status in ('Closed', 'Cancelled', 'On Hold'), if the user selects "Due for Completion" then Datatable.Completion date > getDate()-14.

In the Report Parameters screen there is a Value column for each label, but I haven't been able to work out how that value can be inserted into the where statement.

Thanks,

Bruce

Just use the parameter name you setup in the parameter dialog and insert it into SQL statement as @.ParameterName.

You can try the 'Multi-value ' option if want to allow user choose more then one values.

|||

OK here goes...

I've created a parameter in layout called Report_Type, and I've setup the following available values...

Label Value

Open requests.[Work Request Status] NOT IN ('Completed', 'Cancelled', 'Business Hold')

Closed requests.[Work Request Status] IN ('Completed', 'Cancelled', 'Business Hold')

Due For Completion requests.[Required By] > (getDate()-14)

Now I add a where statement to the SQL that reads "WHERE @.Report_Type"

The error I get is "syntax error or access violation".

Where am I going wrong?

Thanks.

Reporting services 2000 / 2005 parameters

Hi all,

Is there any way to pass a dataset parameter in RS 2000? Basicly, I have some data in my web page and want to print it. But I don't want to create another page to pass parameters to RS. I want to print the page as it is with the data already in the page, so if there is a way I can send the data through a dataset to the report directly, would be great. Is there?

Thank you very much,

Marco

You can give this a try.

http://blogs.msdn.com/bryanke/archive/2004/09/13/229129.aspx

cheers,

Andrew

|||The link from the blog is not valid.|||

More ideas, please

Thanks,

Marco

|||

I found it after googling custom dataset reporting services.

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468707-56ef-4864-ac51-d83fc3273fe5

cheers,

Andrew

|||

Good article, Andrew.

Thank you very much

Regards,

Marco

Reporting Services - String parameter problems

We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
all report parameters of type 'string'. If the actual datatype of the column
is 'varchar' this causes (serious) performance problems as SQL Server must
convert the 'varchar' column to match the 'nvarchar' parameter.
Is there any way to control the data type that is chosen for 'string'
parameters? And why does SQL Reporting Services choose 'nvarchar' when the
column is of type 'varchar'?
For example, if I have a table defined as:
CREATE TABLE [TestTable] (
[TheKey] [int] NOT NULL ,
[Name] [varchar] (50) NULL ,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TheKey]
)
)
... and I write a simple report to display this data with a query of :
SELECT Name
FROM TestTable
WHERE (Name = @.NameParam)
.. I notice that the query sent to SQL Server (from Profiler) is:
exec sp_executesql N'SELECT Name
FROM TestTable
WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam = N'Name0'
... notice that the parameter type is 'nvarchar(4000)' but the actual column
data type is 'varchar'. If you create an execution plan for this query you
can see that SQL Server must perform alot more work than if the parameter was
defined as 'varchar(4000)'.
Thanks,
Scott.Scott
Have you tried CONVERT or CAST on both sides of the expression, i.e
WHERE CONVERT(Varchar(4000), Name) = CONVERT(VarChar(4000), @.NameParam)
I know it's clunky but it is a way of explicitly data typing the fields!
Chris
"Scott Simms" wrote:
> We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
> all report parameters of type 'string'. If the actual datatype of the column
> is 'varchar' this causes (serious) performance problems as SQL Server must
> convert the 'varchar' column to match the 'nvarchar' parameter.
> Is there any way to control the data type that is chosen for 'string'
> parameters? And why does SQL Reporting Services choose 'nvarchar' when the
> column is of type 'varchar'?
> For example, if I have a table defined as:
> CREATE TABLE [TestTable] (
> [TheKey] [int] NOT NULL ,
> [Name] [varchar] (50) NULL ,
> CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> (
> [TheKey]
> )
> )
> ... and I write a simple report to display this data with a query of :
> SELECT Name
> FROM TestTable
> WHERE (Name = @.NameParam)
> .. I notice that the query sent to SQL Server (from Profiler) is:
> exec sp_executesql N'SELECT Name
> FROM TestTable
> WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam => N'Name0'
> ... notice that the parameter type is 'nvarchar(4000)' but the actual column
> data type is 'varchar'. If you create an execution plan for this query you
> can see that SQL Server must perform alot more work than if the parameter was
> defined as 'varchar(4000)'.
> Thanks,
> Scott.
>
>|||Hi Chris,
Yep. If we cast the parameter to varchar the query executes properly. I was
hoping not to update all of my reports to fix this problem!
If you can think of anyway to change the behaviour within Reporting Services
I would appreciate it. If not, can you check if this is being addressed in a
service pack or next version?
Thanks,
Scott.
"Chris McGuigan" wrote:
> Scott
> Have you tried CONVERT or CAST on both sides of the expression, i.e
> WHERE CONVERT(Varchar(4000), Name) = CONVERT(VarChar(4000), @.NameParam)
> I know it's clunky but it is a way of explicitly data typing the fields!
> Chris
> "Scott Simms" wrote:
> > We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
> > all report parameters of type 'string'. If the actual datatype of the column
> > is 'varchar' this causes (serious) performance problems as SQL Server must
> > convert the 'varchar' column to match the 'nvarchar' parameter.
> >
> > Is there any way to control the data type that is chosen for 'string'
> > parameters? And why does SQL Reporting Services choose 'nvarchar' when the
> > column is of type 'varchar'?
> >
> > For example, if I have a table defined as:
> >
> > CREATE TABLE [TestTable] (
> > [TheKey] [int] NOT NULL ,
> > [Name] [varchar] (50) NULL ,
> > CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> > (
> > [TheKey]
> > )
> > )
> >
> > ... and I write a simple report to display this data with a query of :
> >
> > SELECT Name
> > FROM TestTable
> > WHERE (Name = @.NameParam)
> >
> > .. I notice that the query sent to SQL Server (from Profiler) is:
> >
> > exec sp_executesql N'SELECT Name
> > FROM TestTable
> > WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam => > N'Name0'
> >
> > ... notice that the parameter type is 'nvarchar(4000)' but the actual column
> > data type is 'varchar'. If you create an execution plan for this query you
> > can see that SQL Server must perform alot more work than if the parameter was
> > defined as 'varchar(4000)'.
> >
> > Thanks,
> >
> > Scott.
> >
> >
> >|||Hi Scott,
I appreciate what your saying.
I reckon it's "Data Type Precedence" that's causing your problem, see;
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp
for an explanation.
It's typing to the highest common denominator which must be the parameter in
your case.
Is this parameters values derived from a query?
If not, then it's Reporting Services itself setting it to nvarchar!
If it is a query, maybe you could look at the query to force the type - I
don't really know.
But going back to my original suggestion, casting the parameter alone should
cure your problem, so it's just half as clunky! :)
Regards
Chris
"Scott Simms" wrote:
> Hi Chris,
> Yep. If we cast the parameter to varchar the query executes properly. I was
> hoping not to update all of my reports to fix this problem!
> If you can think of anyway to change the behaviour within Reporting Services
> I would appreciate it. If not, can you check if this is being addressed in a
> service pack or next version?
> Thanks,
> Scott.
>
> "Chris McGuigan" wrote:
> > Scott
> >
> > Have you tried CONVERT or CAST on both sides of the expression, i.e
> > WHERE CONVERT(Varchar(4000), Name) = CONVERT(VarChar(4000), @.NameParam)
> > I know it's clunky but it is a way of explicitly data typing the fields!
> >
> > Chris
> >
> > "Scott Simms" wrote:
> >
> > > We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
> > > all report parameters of type 'string'. If the actual datatype of the column
> > > is 'varchar' this causes (serious) performance problems as SQL Server must
> > > convert the 'varchar' column to match the 'nvarchar' parameter.
> > >
> > > Is there any way to control the data type that is chosen for 'string'
> > > parameters? And why does SQL Reporting Services choose 'nvarchar' when the
> > > column is of type 'varchar'?
> > >
> > > For example, if I have a table defined as:
> > >
> > > CREATE TABLE [TestTable] (
> > > [TheKey] [int] NOT NULL ,
> > > [Name] [varchar] (50) NULL ,
> > > CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> > > (
> > > [TheKey]
> > > )
> > > )
> > >
> > > ... and I write a simple report to display this data with a query of :
> > >
> > > SELECT Name
> > > FROM TestTable
> > > WHERE (Name = @.NameParam)
> > >
> > > .. I notice that the query sent to SQL Server (from Profiler) is:
> > >
> > > exec sp_executesql N'SELECT Name
> > > FROM TestTable
> > > WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam => > > N'Name0'
> > >
> > > ... notice that the parameter type is 'nvarchar(4000)' but the actual column
> > > data type is 'varchar'. If you create an execution plan for this query you
> > > can see that SQL Server must perform alot more work than if the parameter was
> > > defined as 'varchar(4000)'.
> > >
> > > Thanks,
> > >
> > > Scott.
> > >
> > >
> > >

Reporting service does not support In clause in select statment?

Hi,
I am trying to pass a parameters to the reporting services using "IN" clause in select statement.
If my parameter is 'C','D','F' and i wish to pass it to the IN clause, however it seem like it is taking the parameter as a whole string, and doesn't return me the result where the status IN ('C','D','F')
Anyone got solution for this??

Hi,
I managed to solve it. Found the solution from one of the site.
http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx
Hope it helps those that have this problem :o)