How to tie ? to parameter name?

In the Visual Studio (2005) designer, I created a report, data source,
and dataset. The query is essentially Select * from Table1 where
ClientID = ?
Under Report Parameters I changed the name Parameter1 to CIDParam, so
that it is somewhat meaningful when I call it from code. But when I
try to preview the report, Studio complains that the query can't find
Parameter1. So how do I let Studio know that ? should look at
CIDParam?I go against Sybase and have to do this all the time (unfortunately). Go
back to the dataset tab, click on ..., parameters tab. Remap the query
paramters. Keep in mind that although RS creates the report parameters
automatically for you they are not the same thing. This is where they are
mapped.
Another time you will want to do this is if you want to use the same report
parameter multiple times. For instance you have a from and end date that is
used multiple times in your query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:l2jni255jalsgohek00ih5ac2h18vbh33a@.4ax.com...
> In the Visual Studio (2005) designer, I created a report, data source,
> and dataset. The query is essentially Select * from Table1 where
> ClientID = ?
> Under Report Parameters I changed the name Parameter1 to CIDParam, so
> that it is somewhat meaningful when I call it from code. But when I
> try to preview the report, Studio complains that the query can't find
> Parameter1. So how do I let Studio know that ? should look at
> CIDParam?|||On Tue, 10 Oct 2006 12:24:48 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>I go against Sybase and have to do this all the time (unfortunately). Go
>back to the dataset tab, click on ..., parameters tab. Remap the query
>paramters. Keep in mind that although RS creates the report parameters
>automatically for you they are not the same thing. This is where they are
>mapped.
>Another time you will want to do this is if you want to use the same report
>parameter multiple times. For instance you have a from and end date that is
>used multiple times in your query.
Yes, that seems to have done it. Thanks.

How to this with SQL

Table and sample data is
id date name value1
1 15-11-05 a 5
2 15-11-05 b 2
3 16-11-05 a 3
4 17-11-05 a 7
5 18-11-05 a 5
6 18-11-05 b 1
If date parameter is 17-11-05
result is
17-11-05 a 7
15-11-05 b 2
If date parameter is 18-11-05
result is
18-11-05 a 5
18-11-05 b 1
thanksIf you are looking for rows where the entry is the most-recent on or before
the given date for each name, then try:
select
m.*
from
(
select
name
, max ([date]) [date]
from
MyTable
where
[date] <= @.The Date
group by
name
) x
join MyTable m on m.name = x.name
and m.[date] = x.[date]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Serhat AKALIN" <serhatakalin@.yahoo.com> wrote in message
news:umDMcbt7FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Table and sample data is
> id date name value1
> 1 15-11-05 a 5
> 2 15-11-05 b 2
> 3 16-11-05 a 3
> 4 17-11-05 a 7
> 5 18-11-05 a 5
> 6 18-11-05 b 1
> If date parameter is 17-11-05
> result is
> 17-11-05 a 7
> 15-11-05 b 2
> If date parameter is 18-11-05
> result is
> 18-11-05 a 5
> 18-11-05 b 1
>
> thanks
>|||select thetable.date, thetable.name, thetable.value1
from thetable
join (
select name, max(date) as date
from thetable
where date<=@.DateParameter
group by name
) maxdates
on thetable.name=maxdates.name
and thetable.date=maxdates.date
order by thetable.name
Serhat AKALIN wrote:
> Table and sample data is
> id date name value1
> 1 15-11-05 a 5
> 2 15-11-05 b 2
> 3 16-11-05 a 3
> 4 17-11-05 a 7
> 5 18-11-05 a 5
> 6 18-11-05 b 1
> If date parameter is 17-11-05
> result is
> 17-11-05 a 7
> 15-11-05 b 2
> If date parameter is 18-11-05
> result is
> 18-11-05 a 5
> 18-11-05 b 1
>
> thanks
>

How to take off the time part of the parameter

Hello,

How can I display data that only compare the date part (ignoring the time part) between the value of the date parameter and the database?. It displays data only if I pass the whole date (date and time)

I tried with the following query in the report services 2000, but it even didn't pass the query parse

SELECT * FROM table

WHERE (CONVERT(CHAR(8), table.PRODUCTION_DATE, 112)
= CONVERT(CHAR(8), @.parameterDate, 112))

Any ideas will be appreciated.

Thanks,

Marco

What are the data types of the column and the parameter, and what error do you get when you say it doesn't get past the parse step?|||

The message is ADO error: Line 1: Incorrect syntax near ')'

If I replace @.parameterDate with a string e.g. '' The parse is ok

I tested the same query in RS 2005 and it passes the parse

Both the column and the parameter are datetime data types

Thanks

|||

Just in case someone has the same problem:

I realized that the workaround to this is to change the sql expression entering two dates as parameters, fromDate and toDate and using the where clause as follows:

where databaseDate => @.fromDate and databaseDate <= @.toDate

regards

Marco

How to supply NT Credential on RS Report Parameter

I have a Filter Dropdown parameter which calls Storeprocedure DataSet
accepts NT Domain and Username as parameter. How will i supply this
parameter to make it work. Right now i hardcode values and it works fine.
In Dot.net Program we can set Identity Impersonate to true and then use
User.Identity Security Principals . How do i use on Reporting Services.
Any idea would be great!I figured it out.. User.UserID ( User running the report)
"RAGHAVAN JAYARAMAN" wrote:
> I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> accepts NT Domain and Username as parameter. How will i supply this
> parameter to make it work. Right now i hardcode values and it works fine.
> In Dot.net Program we can set Identity Impersonate to true and then use
> User.Identity Security Principals . How do i use on Reporting Services.
> Any idea would be great!
>|||I am having the same problem.
Where did you place this code? In the Parameter field itself? Inside of
the report code tab?
Thanks,
msflinx
"RAGHAVAN JAYARAMAN" wrote:
> I figured it out.. User.UserID ( User running the report)
> "RAGHAVAN JAYARAMAN" wrote:
> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> > accepts NT Domain and Username as parameter. How will i supply this
> > parameter to make it work. Right now i hardcode values and it works fine.
> >
> > In Dot.net Program we can set Identity Impersonate to true and then use
> > User.Identity Security Principals . How do i use on Reporting Services.
> >
> > Any idea would be great!
> >|||Query parameters do not have to map to a report parameter. RS automatically
creates matching report parameters but you can instead map the query
parameter to an expression. In the dataset tab click on the ..., parameters
tab and map to an expression. When the expression builder comes up, expand
the global variable and set it to the User!UserID global variable. Note that
this has domain\username so if you need just one or the other you need to
strip off the part you don't want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:133E55D5-265D-4AAB-A703-B59EC82C3ABB@.microsoft.com...
>I am having the same problem.
> Where did you place this code? In the Parameter field itself? Inside of
> the report code tab?
> Thanks,
> msflinx
> "RAGHAVAN JAYARAMAN" wrote:
>> I figured it out.. User.UserID ( User running the report)
>> "RAGHAVAN JAYARAMAN" wrote:
>> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
>> > accepts NT Domain and Username as parameter. How will i supply this
>> > parameter to make it work. Right now i hardcode values and it works
>> > fine.
>> >
>> > In Dot.net Program we can set Identity Impersonate to true and then use
>> > User.Identity Security Principals . How do i use on Reporting Services.
>> >
>> > Any idea would be great!
>> >

How to submit a report parameter?

Microsoft® SQL Server� Reporting Services provides a single entry point to the full functionality of the report server: the Reporting Services Web service. The Web service uses Simple Object Access Protocol (SOAP) over HTTP and acts as a communications interface between client programs and the report server. The Web service and its methods expose the functionality of the report server and allow you to create custom tools for any part of the report life cycle, from management to execution.
The application that we have build is using the Web Service and the .NET Framework. We have successfully created the proxy class for the Web service.
We are trying to:
Create a ReportServices control
Create a ReportViewer control
Render a specific Report
To render the Report, we need to pass it a string paramter.
Our status is the following:
We have successfully created the ReportServices control with the following:
ReportingService rs = new ReportingService();
If we don't pass a parameter, we have everthing working fine.
If we pass a parameter, the report does not render. A summary of the error message follows:
"The report parameter 'sDealerId' is read-only and cannot be modified."
The full error message follows:
<detail>
<ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsReadOnlyReportParameter</ErrorCode>
<HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus>
<Message xmlns="http://www.microsoft.com/sql/reportingservices">The report parameter 'sDealerId' is read-only and cannot be modified.</Message>
<HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsReadOnlyReportParameter&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00</HelpLink>
<ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName>
<ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">8.00.878.00</ProductVersion>
<ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId>
<OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem>
<CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId>
<MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices">
<Source>Microsoft.ReportingServices.Processing</Source>
<Message msrs:ErrorCode="rsReadOnlyReportParameter" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsReadOnlyReportParameter&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">The report parameter 'sDealerId' is read-only and cannot be modified.</Message>
</MoreInformation>
<Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />
</detail>
Has anyone ever successful passed parameters to a ReportServices and/or ReportViewer control? If so, have you ever seen this type of error message and do you know which specific security issue is causing this?
Thanks,
Robert Baker
ZxytekNot sure, Robert, but this sounds like the error that version 1 gives when
you try to update a parameter that has been rendered "read only". SP1
removed this limitation allowing you to keep a parameter hidden and yet
change it's value via the URL.
"zxytek" <zxytek@.hotmail.com> wrote in message
news:252C34F9-652F-494C-BBE7-807B07073216@.microsoft.com...
> Microsoft® SQL ServerT Reporting Services provides a single entry point to
the full functionality of the report server: the Reporting Services Web
service. The Web service uses Simple Object Access Protocol (SOAP) over HTTP
and acts as a communications interface between client programs and the
report server. The Web service and its methods expose the functionality of
the report server and allow you to create custom tools for any part of the
report life cycle, from management to execution.
> The application that we have build is using the Web Service and the .NET
Framework. We have successfully created the proxy class for the Web
service.
> We are trying to:
> Create a ReportServices control
> Create a ReportViewer control
> Render a specific Report
> To render the Report, we need to pass it a string paramter.
> Our status is the following:
> We have successfully created the ReportServices control with the
following:
> ReportingService rs = new ReportingService();
> If we don't pass a parameter, we have everthing working fine.
> If we pass a parameter, the report does not render. A summary of the
error message follows:
> "The report parameter 'sDealerId' is read-only and cannot be
modified."
> The full error message follows:
> <detail>
> <ErrorCode
xmlns="rsReadOnlyReportParam">http://www.microsoft.com/sql/reportingservices">rsReadOnlyReportParam
eter</ErrorCode>
> <HttpStatus
xmlns="400</HttpStatus>">http://www.microsoft.com/sql/reportingservices">400</HttpStatus>
> <Message
xmlns="The">http://www.microsoft.com/sql/reportingservices">The report parameter
'sDealerId' is read-only and cannot be modified.</Message>
> <HelpLink
xmlns="http://go.microsoft.c">http://www.microsoft.com/sql/reportingservices">http://go.microsoft.c
om/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utili
ties.ErrorStrings.resources.Strings&EvtID=rsReadOnlyReportParameter&ProdName
=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00</HelpLink>
> <ProductName
xmlns="Microsoft">http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server
Reporting Services</ProductName>
> <ProductVersion
xmlns="8.00.878.00</ProductV">http://www.microsoft.com/sql/reportingservices">8.00.878.00</ProductV
ersion>
> <ProductLocaleId
xmlns="127</ProductLocaleId>">http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId>
> <OperatingSystem
xmlns="OsIndependent</Operat">http://www.microsoft.com/sql/reportingservices">OsIndependent</Operat
ingSystem>
> <CountryLocaleId
xmlns="1033</CountryLocaleId">http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId
> <MoreInformation
xmlns="">http://www.microsoft.com/sql/reportingservices">
> <Source>Microsoft.ReportingServices.Processing</Source>
> <Message msrs:ErrorCode="rsReadOnlyReportParameter"
msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft
.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtI
D=rsReadOnlyReportParameter&ProdName=Microsoft%20SQL%20Server%20Reporting%20
Services&ProdVer=8.00"
xmlns:msrs="The">http://www.microsoft.com/sql/reportingservices">The report
parameter 'sDealerId' is read-only and cannot be modified.</Message>
> </MoreInformation>
> <Warnings xmlns="http://www.microsoft.com/sql/reportingservices"
/>
> </detail>
> Has anyone ever successful passed parameters to a ReportServices and/or
ReportViewer control? If so, have you ever seen this type of error message
and do you know which specific security issue is causing this?
> Thanks,
> Robert Baker
> Zxytek

How to stop report parameter taking default value

I have a SSRS2000 report with three parameters:
1) Company - values from a query including NULL for ALL companies - no
default value
2) From date - has default value
3) To date - has default value
My problem is that the report runs automatically when launched. Even though
there is no default value specified for the Company parameter it takes the
NULL value.
Is there any way to change this to force the Company prompt to display a
<select a value> and allow selection of one of the options?
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, ScotlandHello magendo_man,
Instead of letting it take the value or NULL... change to: (CompanyID =@.Company OR @.CompanyID = "ALL"). Then in your query that supplies the
company names add a UNION SELECT "ALL". Don't specify a default for that
parameter.
Hope that helps!
Cheers,
Kathy
"magendo_man" wrote:
> I have a SSRS2000 report with three parameters:
> 1) Company - values from a query including NULL for ALL companies - no
> default value
> 2) From date - has default value
> 3) To date - has default value
> My problem is that the report runs automatically when launched. Even though
> there is no default value specified for the Company parameter it takes the
> NULL value.
> Is there any way to change this to force the Company prompt to display a
> <select a value> and allow selection of one of the options?
>
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland

How to Stop or Abort Report Processing

Is there a way to stop report processing based on evaluating a report
parameter? If the paramater fails to meet a certain requirement I would like
to stop report. I've come up with a work around to return no data in these
scenarios. But I would like to stop report processing all together and avoid
making a trip to the database. Is there a method or something that can be
called from the code window?
Thanks in advance for the help!Rob,
You can validate the format of the parameters if you go to the report.
Then click on the report in the toolbar => parameters => and then there
is a drop down for every parameter on what kind of parameter it is.
You can also control this in the ReportManager. I mean by going to a
certain report in report manager => properties => Parameters.
You can also do your own validation in the report Code.
regards,
Stas K.|||I understand this and I am doing my own validation. However, I would like to
know if there is a way to cancel or stop report from executing when a certain
condition is not met. What I want to avoid in an unnecesary trip to execute
query and return no results. Or is simply hiding the results of the report
using visible property the only solution?
"Sorcerdon" wrote:
> Rob,
> You can validate the format of the parameters if you go to the report.
> Then click on the report in the toolbar => parameters => and then there
> is a drop down for every parameter on what kind of parameter it is.
> You can also control this in the ReportManager. I mean by going to a
> certain report in report manager => properties => Parameters.
> You can also do your own validation in the report Code.
> regards,
> Stas K.
>|||Do you have a front end to run these reports? some kind of web
application? or are you using report manager?
For example, all of my reports are running off a C# application that
has all the validation in it - and if something is inproper in the
parameters being passed I have an user friendly exception thrown.
Basically I have a Frame tag inside a panel tag that has visible=false
and where the source of the frame is set to some "user friendly error
HTML page". Once the user clicks the Run Report button of my C#
application, I set the Source of that frame to the ReportString and I
set the panel in which that frame is located to Visible=true.
This way, the report is never touched until everything is set and
validated.
I don't know if this is possible in ReportManger.
regards,
Stas K.|||Yes. I'm running my reports using the same technique and accessing those
reports using the URL method. I'm using the Visual Studio report designer to
create the reports. If the user tampers with the query string I would like to
abort report processing all together. I'm hoping someone knowledgeable with
using the code window and expressions can tell me if this is possible.
"Sorcerdon" wrote:
> Do you have a front end to run these reports? some kind of web
> application? or are you using report manager?
> For example, all of my reports are running off a C# application that
> has all the validation in it - and if something is inproper in the
> parameters being passed I have an user friendly exception thrown.
> Basically I have a Frame tag inside a panel tag that has visible=false
> and where the source of the frame is set to some "user friendly error
> HTML page". Once the user clicks the Run Report button of my C#
> application, I set the Source of that frame to the ReportString and I
> set the panel in which that frame is located to Visible=true.
> This way, the report is never touched until everything is set and
> validated.
> I don't know if this is possible in ReportManger.
> regards,
> Stas K.
>

How to stop automatic excution of a report?

Hi,

I have a report containing one multi-value parameter with default values given. When open I report in Report Manager, report executes automatically. I need to stop this automatic execution of report. I need to give user an option to check if default values are ok for him or not. Is there any way other than removing default value from parameter to do this?

Thanks in anticipation.

Saeed

Sorry, there is no way to achieve this behavior in Report Manager. You need to remove the default value, otherwise the report will render.|||

It is unfortunate that this was not considered as a requirement during development. However, you can work around this by adding an additional report parameter. Add a Start Report parameter as a string with 1 non-queried availabIe value like 'Yes'. The report will not render until the user selects the only available option for that parameter. Its not the best solution but is a viable workaround to the default rendering problem.

|||Hi
Rather than use default parameters in a report why dont you make the report show a list of valid values e.g. from a query and the user will sellect appropriate value.
e.g.

A report uses a paramater param1; create a dataset with a sql query that generates valid values and make your report parameter populate from this dataset. You can set the default value to null so that the report prompts for the parameters from the valid list.

How to stop automatic excution of a report?

Hi,

I have a report containing one multi-value parameter with default values given. When open I report in Report Manager, report executes automatically. I need to stop this automatic execution of report. I need to give user an option to check if default values are ok for him or not. Is there any way other than removing default value from parameter to do this?

Thanks in anticipation.

Saeed

Sorry, there is no way to achieve this behavior in Report Manager. You need to remove the default value, otherwise the report will render.|||

It is unfortunate that this was not considered as a requirement during development. However, you can work around this by adding an additional report parameter. Add a Start Report parameter as a string with 1 non-queried availabIe value like 'Yes'. The report will not render until the user selects the only available option for that parameter. Its not the best solution but is a viable workaround to the default rendering problem.

|||Hi
Rather than use default parameters in a report why dont you make the report show a list of valid values e.g. from a query and the user will sellect appropriate value.
e.g.

A report uses a paramater param1; create a dataset with a sql query that generates valid values and make your report parameter populate from this dataset. You can set the default value to null so that the report prompts for the parameters from the valid list.