parameter properties in report manager - "Has Default"

ok I've designed a report template that uses cascading paramters based
on several sp's. The user selects a value for the first parameter and
that value is passed to the sp that populates the second parameter
list. The value selected for the second param is used to populate the
third param list etc...
I want the first parameter to be required and the other 3 to be
optional - the report should still run if they are not selected. RS
BOL told me to go into the Report manager, select "Properties"
then"Parameters" and then select the "Has Default" check box for the
three optional paramters. This in turn enables the "Null" checkbox, so
I checked that box so null will be the default value passed if these
parameters are not selected.
By all accounts this should allow the report to run with only the first
parameter selected, but when run from Report Manager it still keeps
prompting the user to enter values for the 3 other optional parameters.
Any ideas? help?Never mind! found a workaround...

Parameter Prompts

I don't see a way to attach an expression to a parameter prompt. I have a
custom report extension I have written that works great. I make a call to it
and it retrieves label values to report items. I would like to use that to
retrieve the prompt value for my parameters. Basically everything on the
reports are dynamic including labels because its a hosted application that
has mutliple clients and each can customise their view of the world. Is this
possible? If not is my only alternative to create by own pages to get the
parameters and pass them into the report thru URL or web service?Expressions are not supported for parameter prompt yet.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alex Telford" <atelford6@.nospam.hotmail.com> wrote in message
news:OKqtoLcfEHA.708@.TK2MSFTNGP09.phx.gbl...
>I don't see a way to attach an expression to a parameter prompt. I have a
> custom report extension I have written that works great. I make a call to
> it
> and it retrieves label values to report items. I would like to use that to
> retrieve the prompt value for my parameters. Basically everything on the
> reports are dynamic including labels because its a hosted application that
> has mutliple clients and each can customise their view of the world. Is
> this
> possible? If not is my only alternative to create by own pages to get the
> parameters and pass them into the report thru URL or web service?
>|||Alex,
I have a urgent project requirement to code for rdl reports to create
the content of the report dynamically. I just noticed at the forum
that you did the same. Can you please help with ideas/code.
basically I wish to send commandtext (of a dataset) to the rdl report
at runtime.
thanks,
anand sagar
"Alex Telford" <atelford6@.nospam.hotmail.com> wrote in message news:<OKqtoLcfEHA.708@.TK2MSFTNGP09.phx.gbl>...
> I don't see a way to attach an expression to a parameter prompt. I have a
> custom report extension I have written that works great. I make a call to it
********

Parameter Prompt with Wildcard

I'd like to create a report parameter to allow the user to enter only a
partial field using wildcard(s) in SRS. The list is lengthy and the user
does not always know the full name to enter.
Is this possible?
Thanks,
KarenVery much it can be done. Just you need to keep in mind about the single
quotes you are using for string paramters. eg '%userrequest' which you will
be passing the parameters to the "where" clause.
Amarnath
"Moving rpts from Access to Rptg Services" wrote:
> I'd like to create a report parameter to allow the user to enter only a
> partial field using wildcard(s) in SRS. The list is lengthy and the user
> does not always know the full name to enter.
> Is this possible?
> Thanks,
> Karen|||Thank you for our reply! in the where clause I currently have this:
WHERE (dbo.UP_Agents.AgentName = @.AgentName)
is this where I would add the wildcard? Or would I add in a separate
string? (sorry, i'm learning this as I go)
Thank you,
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen|||AHA, this worked:
WHERE (dbo.UP_Agents.AgentName LIKE '%' + @.AgentName + '%')
Thanks for getting me started.
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen

Parameter prompt value

is there a way to get parameter prompt value inside the report?
If i have parameter 1 with a prompt value "Customer ID", how can i get
"Customer ID" string value?Not really documented anywhere that I have seen but this is how to do it.
Parameters!ParamName.Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1141676084.764341.275170@.e56g2000cwe.googlegroups.com...
> is there a way to get parameter prompt value inside the report?
> If i have parameter 1 with a prompt value "Customer ID", how can i get
> "Customer ID" string value?
>

Parameter Prompt text (filter labels)

Is there a way to manipulate the label that appears next to a filter list
(the report parameter prompt) on the fly when generating a report?
For example, I have 2 people viewing a report -- one is in France and one is
in the US. In the report, the filter labels are currently all in English. I
have the French and English text stored in a database table -- so I was
thinking that I could use an expression to get the appropriate text from the
database, but I do not see an opportunity to use an expression for the prompt.
Any other ideas on how I could modify the text in a filter label on the fly?
Thank you,
--
LaurieTOn Mar 21, 11:05 am, LaurieT <Laur...@.discussions.microsoft.com>
wrote:
> Is there a way to manipulate the label that appears next to a filter list
> (the report parameter prompt) on the fly when generating a report?
> For example, I have 2 people viewing a report -- one is in France and one is
> in the US. In the report, the filter labels are currently all in English. I
> have the French and English text stored in a database table -- so I was
> thinking that I could use an expression to get the appropriate text from the
> database, but I do not see an opportunity to use an expression for the prompt.
> Any other ideas on how I could modify the text in a filter label on the fly?
> Thank you,
> --
> LaurieT
The only thing I can think of is to try to conditionally modify the
underlying XML in the RDL file itself (though this is a long shot).
Ofcourse, there's always the custom application route that access the
rdl files internally (or maybe custom assembly: though I'm not
experienced in this area). Sorry to not be of greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I guess that I have the same issue with Report Column and Row labels. Here I
see that I can enter en expression, but when I try to put a query into the
expression, it seems to fail. Any ideas on this?
--
LaurieT
"EMartinez" wrote:
> On Mar 21, 11:05 am, LaurieT <Laur...@.discussions.microsoft.com>
> wrote:
> > Is there a way to manipulate the label that appears next to a filter list
> > (the report parameter prompt) on the fly when generating a report?
> >
> > For example, I have 2 people viewing a report -- one is in France and one is
> > in the US. In the report, the filter labels are currently all in English. I
> > have the French and English text stored in a database table -- so I was
> > thinking that I could use an expression to get the appropriate text from the
> > database, but I do not see an opportunity to use an expression for the prompt.
> >
> > Any other ideas on how I could modify the text in a filter label on the fly?
> >
> > Thank you,
> > --
> > LaurieT
> The only thing I can think of is to try to conditionally modify the
> underlying XML in the RDL file itself (though this is a long shot).
> Ofcourse, there's always the custom application route that access the
> rdl files internally (or maybe custom assembly: though I'm not
> experienced in this area). Sorry to not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql

Parameter Prompt List & Filter selection

I have created a parameter for a report that displays a drop-down list of
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper

Parameter prompt based on expression

Hi,

Is it possible to construct the prompt (i.e. the text that is displayed next to the parameter text box when the report runs) for a parameter from an expression

Actually i have written some custom code and i want my parameter prompts to be populated based on the returned values from that custom code. I don't want these do be fixed texts.

Thanks,

Simranjeev

Can you use SWITCH option?

Igor.

|||

But the Switch option is also used inside an expression, and i don't know how to create the parameter labels from expressions.

On my report, I have a parameter called "StartDate". I've given it the label "Start Date:". Now i want that when the locale of the user is English, this label should be displayed as "Start Date:" and when the locale of the user changes to some other language, lets say German, then the label should be displayed as the translated value for "Start Date:" in German.

I've already done this for the textboxes on my reports, by calling custom code which reads from resource files depending on user locale. I wanted to know if this is possible for parameter labels as well, as i couldn't find any option to do so.

parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

Parameter problem with OLAP and spaces

Hello all,

I have an issue with parameters. Let me explain.....

I have report1 which uses analysis services as it's data source. The report displays a list of our companies sites along with some costs etc. Site names can include spaces and numbers but they are stored in the relational database as varchar[50]. On the relevant text box of report1 I have defined a data detsination using report2 and passing it the site name field.value.

On report2 I have added the site name field as a filter dimension and set it as a parameter. On the report parameters I have set the parameter as being non queried.

When I run report1 and click a value to "drill down" it only works for values that have no spaces and no numbers. If If convert spaces to underscores I get a little further, but sites that have numbers, for example 15_OxfordStreet still do not work. The error message I get is..

"Parser: The syntax for '_Oxford' is incorrect.

Can anyone suggest anything to help?

Many thanks

Matt

You may need to escape the member names. To avoid similar issues, the SSAS team introduced undocumented function UrlEscapeFragment. If you open the sample AW cube, flip to the Actions tab in the Cube Designer, select the Sales Reason Comparision action, and expand the Parameters section, you will see how this function is used to escape the member name.|||

Teo,

Thanks for your reply. I'm still learning SQL 2005 and although I tried your suggestion I could not get it to work. However I did fix the problem. The issue was that I was passing through the "friendly name" of the value, instead of [data1].[field name].&[0] etc. as the jump to parameter!

Arrrrgggg...so simple when you think about it!

M

Parameter Problem in Scheduled Report

Hi,
I have a Monthly Report set to be generated in everyday 7am. The default
value of the MONTH parameter is previous month (i.e. Nov). Yet if need to
extract a OCT report, it could be changed the parameter in Report Manager
(i.e. override default of MONTH parameter); however, once it is changed to
10, the scheduler would based on 10 to generate the Montly report afterward.
Is there any method to restore the default value without redeploy the report?
Thanks.
Yan.Why don't you or your users manually change the value of the parameter in
Report Viewer instead?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"YeungTakYan" <YeungTakYan@.discussions.microsoft.com> wrote in message
news:DF08AB98-747C-4C4C-91EE-F8AC7AEDD284@.microsoft.com...
> Hi,
> I have a Monthly Report set to be generated in everyday 7am. The default
> value of the MONTH parameter is previous month (i.e. Nov). Yet if need to
> extract a OCT report, it could be changed the parameter in Report Manager
> (i.e. override default of MONTH parameter); however, once it is changed to
> 10, the scheduler would based on 10 to generate the Montly report
> afterward.
> Is there any method to restore the default value without redeploy the
> report?
> Thanks.
> Yan.|||Once schedule is set, the parameter (textbox, dropdown list, ... ) becomes
disabled. So the parameter could not be changed in the View mode.
"Alain Quesnel" wrote:
> Why don't you or your users manually change the value of the parameter in
> Report Viewer instead?
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "YeungTakYan" <YeungTakYan@.discussions.microsoft.com> wrote in message
> news:DF08AB98-747C-4C4C-91EE-F8AC7AEDD284@.microsoft.com...
> > Hi,
> >
> > I have a Monthly Report set to be generated in everyday 7am. The default
> > value of the MONTH parameter is previous month (i.e. Nov). Yet if need to
> > extract a OCT report, it could be changed the parameter in Report Manager
> > (i.e. override default of MONTH parameter); however, once it is changed to
> > 10, the scheduler would based on 10 to generate the Montly report
> > afterward.
> > Is there any method to restore the default value without redeploy the
> > report?
> >
> > Thanks.
> >
> > Yan.
>

Parameter Problem

Hi All!

My report has two parameters; department and jobs within that department.

dataset2
SELECT dbo.Jobs.JobName, dbo.CostCentres.CostCentreId, dbo.Jobs.JobCode,
dbo.CostCentres.CostCentre
FROM dbo.Jobs INNER JOIN
dbo.CostCentres ON dbo.Jobs.CostCentreId = dbo.
CostCentres.CostCentreId
WHERE (SUBSTRING(dbo.CostCentres.CostCentre, 1, 4) = 'CMBS')
ORDER BY dbo.CostCentres.CostCentreId, dbo.Jobs.JobName

dataset1
has all the info about activity inside the jobs and the @.prompts.

The problem is I am getting duplicate departments in the department prompt
and all the jobs for all the departments in the job prompt. Is there a way,
after I choose what department I want, to just get the job within that
department I want?

Any help would be great!
Thanks in advanced, KerrieI figured out this problem, if anyone would like to no, email me at ksorrell@.cincom.comsql

Parameter problem

HI, I have a report that users select a parameter value from a dropdownbox.

I want to be able to limit the number of parameter options depending on who is logged in.

user A can only see values 'ABC', 'DSC', 'BHT''

whereas user B can see values 'MKP','NHJ','BLP'

is this possible? if so how?

TIA

Reporting services provides the "User!UserID" global parameter in all its reports. This populates with the windows user name if I'm not mistaken. You could pass this value as a parameter to the dataset that you're using to populate the dropdown box.|||If the datasource is driven by a view or a stored procedure or a query you can implement row-level security using a mapping table which locks ( locks up down the appropiate values which should not be seen by some people. If you have a static list, this would be too hard to implement / impossible (Nothing is impossible, impossible just takes longer :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I was hoping to be able to use custom code to populate the parameter dropdownlist/ set the parameter values.

Is this possible? can I reference datasets in my code?

|||

I created a view then set a filter on the dataset to return values based on the User!UserID=UserName field from dataset

then set the parameter to 'From Query' chose the dataset and appropriate field from dataset and everything aok.

|||Welcome to rowlevel security :-)|||

I am trying to do the something similar. In my report i created a stored procedure that stored procedure is passed a parameter CaseNumber. (I am using the Report Veiwer Control in Remote Mode). In Report Designer, I created to datasets the first dataset populates the dropdown list box and it is displayed in the toolbar. The Second dataset takes this parameter (CaseNumber) and brings back the report. Everything works fine in the Report Manager; however, when I run it from frmReport.aspx page the report does not show.

If I run the report without any parameters from the frmReport.aspx page (still using the stored procedure w/o using parameters) the report shows. Can any body help me with this.

Zachary

|||

Fixed the problem - thanks

Zachary

Parameter problem

Hi all i have serached crystal report forum but i m still in problem although i have seen some solutions but nothing is workig with me.

my problem is i am not getting Param1.ParameterFieldName in intellisence.
I am using c# and visual studio 2003 crystal reports 9.0

I am using a stored procedure to built report and i have to pass start date and end date so pls help for this.

I would be obligedIn your coding you need to supply those values like this

sp_name 'param1','param2'

Parameter Problem

Hello:
RS Newbie here.
I have created a report with a parameter that has an ALL value that should
display all records when that particular parameter is selected.
Here is the code for my dataset:
SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
dbo.BuilderWO.Address,
dbo.BuilderWO.City, dbo.BuilderWO.State,
dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
dbo.BuilderWO.WalkDate,
dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
dbo.Employees.LName, dbo.Status.Status_Status
FROM dbo.Status RIGHT OUTER JOIN
dbo.BuilderWO ON dbo.Status.Status_StatusID = dbo.BuilderWO.Status LEFT OUTER JOIN
dbo.Locations ON dbo.BuilderWO.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Employees ON dbo.BuilderWO.TSHRep1 = dbo.Employees.EmployeeID
WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID = @.location)
Here is the code for the parameter:
SELECT LocationID, Location AS Community FROM Locations WHERE Community = 1
UNION SELECT
-1, 'ALL'
My problem is that when I select ALL, no records are returned. The report
runs but just returns the Page Header items. If I select any other
location, the report runs fine.
It seems like I'm missing something. Any advice would be appreciated.
ThanksWHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID =@.location or @.location = -1)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> Hello:
> RS Newbie here.
> I have created a report with a parameter that has an ALL value that should
> display all records when that particular parameter is selected.
> Here is the code for my dataset:
> SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> dbo.BuilderWO.Address,
> dbo.BuilderWO.City, dbo.BuilderWO.State,
> dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> dbo.BuilderWO.WalkDate,
> dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> dbo.Employees.LName, dbo.Status.Status_Status
> FROM dbo.Status RIGHT OUTER JOIN
> dbo.BuilderWO ON dbo.Status.Status_StatusID => dbo.BuilderWO.Status LEFT OUTER JOIN
> dbo.Locations ON dbo.BuilderWO.LocationID => dbo.Locations.LocationID LEFT OUTER JOIN
> dbo.Employees ON dbo.BuilderWO.TSHRep1 => dbo.Employees.EmployeeID
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location)
>
> Here is the code for the parameter:
> SELECT LocationID, Location AS Community FROM Locations WHERE Community => 1
> UNION SELECT
> -1, 'ALL'
> My problem is that when I select ALL, no records are returned. The report
> runs but just returns the Page Header items. If I select any other
> location, the report runs fine.
> It seems like I'm missing something. Any advice would be appreciated.
> Thanks
>
>|||Great!
Thanks that worked.
"Bruce L-C [MVP]" wrote:
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location or @.location = -1)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brennan" <Brennan@.discussions.microsoft.com> wrote in message
> news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> > Hello:
> >
> > RS Newbie here.
> >
> > I have created a report with a parameter that has an ALL value that should
> > display all records when that particular parameter is selected.
> >
> > Here is the code for my dataset:
> >
> > SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> > dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> > dbo.BuilderWO.Address,
> > dbo.BuilderWO.City, dbo.BuilderWO.State,
> > dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> > dbo.BuilderWO.WalkDate,
> > dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> > dbo.Employees.LName, dbo.Status.Status_Status
> > FROM dbo.Status RIGHT OUTER JOIN
> > dbo.BuilderWO ON dbo.Status.Status_StatusID => > dbo.BuilderWO.Status LEFT OUTER JOIN
> > dbo.Locations ON dbo.BuilderWO.LocationID => > dbo.Locations.LocationID LEFT OUTER JOIN
> > dbo.Employees ON dbo.BuilderWO.TSHRep1 => > dbo.Employees.EmployeeID
> > WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => > @.location)
> >
> >
> > Here is the code for the parameter:
> >
> > SELECT LocationID, Location AS Community FROM Locations WHERE Community => > 1
> > UNION SELECT
> > -1, 'ALL'
> >
> > My problem is that when I select ALL, no records are returned. The report
> > runs but just returns the Page Header items. If I select any other
> > location, the report runs fine.
> >
> > It seems like I'm missing something. Any advice would be appreciated.
> >
> > Thanks
> >
> >
> >
> >
>
>

parameter passing with blanks - help required

Hi,

I am having a curious problem with parameter passing between

reports. I have 2 reports the first report gives employee name, Department and

in the second report I have employee details. If the user clicks on the

Employee name in the First report it should go to the second report and display

the details. My problem comes if suppose the employee name is “Don King”when the parameter gets passed it does not recognize

the space between thename and throws

an error saying King not recognized.

Would appreciate if anybody could suggest as to how I can

pass the name with spaces in the report parameters.

Thanks and regards

PMNJPassing parameter with space to Jump to report and a subreport work fine. How are you passing this parameter around?|||am passing the parameter thru the report parameter

regards
PMNJ|||I'm having the same problem. I've also got a parameter which can be one word, of more than one word, sometimes with a comma in it. I'm passing the parameter, like PMNJ, thru the report parameter.

Has someone solved this problem?|||I reiterate Brad's question. How are you passing the parameter? Are you using Jump to Report? Are you parsing together a URL link? More details.|||Sorry, here is the info you requested.

I'm passing the parameter (along with another one) with the report parameter, orinating from a jump to report link.
The parameter is correctly displayed when using a textbox and a expression with the parameter in it.
But, when using this parameter in the MDX query for the dataset it doesn't work when the parameter consists of more than one word, i.e. there are spaces (blanks) in it.

If you need more details, please say so, I'll be happy to supply them.

parameter passing with blanks - help required

Hi,

I am having a curious problem with parameter passing between

reports. I have 2 reports the first report gives employee name, Department and

in the second report I have employee details. If the user clicks on the

Employee name in the First report it should go to the second report and display

the details. My problem comes if suppose the employee name is “Don King”when the parameter gets passed it does not recognize

the space between thename and throws

an error saying King not recognized.

Would appreciate if anybody could suggest as to how I can

pass the name with spaces in the report parameters.

Thanks and regards

PMNJPassing parameter with space to Jump to report and a subreport work fine. How are you passing this parameter around?|||am passing the parameter thru the report parameter

regards
PMNJ|||I'm having the same problem. I've also got a parameter which can be one word, of more than one word, sometimes with a comma in it. I'm passing the parameter, like PMNJ, thru the report parameter.

Has someone solved this problem?|||I reiterate Brad's question. How are you passing the parameter? Are you using Jump to Report? Are you parsing together a URL link? More details.|||Sorry, here is the info you requested.

I'm passing the parameter (along with another one) with the report parameter, orinating from a jump to report link.
The parameter is correctly displayed when using a textbox and a expression with the parameter in it.
But, when using this parameter in the MDX query for the dataset it doesn't work when the parameter consists of more than one word, i.e. there are spaces (blanks) in it.

If you need more details, please say so, I'll be happy to supply them.

Parameter passing from report to SQL

Hi, I have the following SQL code in my DataSet:
(SQL Server 2005)
DECLARE @.sel1 AS varchar(20)
DECLARE @.sel2 AS varchar(20)
DECLARE @.sel3 AS varchar(20)
DECLARE @.sel4 AS varchar(20)
DECLARE @.test AS integer
set @.test = @.TimeDiff; <-- this I want to insert via URL in report
set @.sel1=convert
(char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
set @.sel2=Replace(@.sel1,'-','');
set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
set @.sel4=Replace(@.sel3,'-','');
select @.sel2 AS StartTime, @.sel4 AS EndTime;
I have declared a report parameter (TimeDiff, without @.) in Report
Parameters. It is integer and hidden and has a default value 2 (non queried).
In dataset parameters I do not have any configurations (should I have).
I tried to opass the parameter to my sql code but it is not succeeded.
I got the followng error message:
"Must declare the scalar cariable "@.TimeDiff"
The final meaning is to insert this as parameter via URL and use it in the
dataset sql code, but I can not get it into my SQL .
When running the dataset itself it asks the parameter @.TimeDiff and works OK.
What is wrong in my parameter handling ?Push the ... button (the point button) right beside your dataset
(within the data display), a couple of tabs will popup and goto the
parameters tab.
Within this tab you're able to bind the sql declared parameter to your
reporting parameter.
You'll notice the way it works via your other parameters which are
bound to the sql parameters|||I'd suggest having a stored procedure for this code but as long as you don't
use a temp table (if you use a temp table you must put this in a stored
procedure) then this will work.
I would have expected RS to automatically create a TimeDiff report parameter
to match the @.TimeDiff since @.TimeDiff is not declared. Perhaps it is the
use of the set statement. Try
select @.test = @.TimeDiff
If you click on the ..., parameters tab and there is no @.TimeDiff in the
name column then try adding this by hand and then select your report
parameter that it is mapping to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jarmopy" <jarmopy@.discussions.microsoft.com> wrote in message
news:9123A5B9-0DA9-4639-9884-D9D7A30CAA2C@.microsoft.com...
> Hi, I have the following SQL code in my DataSet:
> (SQL Server 2005)
> DECLARE @.sel1 AS varchar(20)
> DECLARE @.sel2 AS varchar(20)
> DECLARE @.sel3 AS varchar(20)
> DECLARE @.sel4 AS varchar(20)
> DECLARE @.test AS integer
> set @.test = @.TimeDiff; <-- this I want to insert via URL in report
>
> set @.sel1=convert
> (char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
> set @.sel2=Replace(@.sel1,'-','');
> set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
> set @.sel4=Replace(@.sel3,'-','');
> select @.sel2 AS StartTime, @.sel4 AS EndTime;
> I have declared a report parameter (TimeDiff, without @.) in Report
> Parameters. It is integer and hidden and has a default value 2 (non
> queried).
> In dataset parameters I do not have any configurations (should I have).
> I tried to opass the parameter to my sql code but it is not succeeded.
> I got the followng error message:
> "Must declare the scalar cariable "@.TimeDiff"
> The final meaning is to insert this as parameter via URL and use it in the
> dataset sql code, but I can not get it into my SQL .
> When running the dataset itself it asks the parameter @.TimeDiff and works
> OK.
> What is wrong in my parameter handling ?
>
>
>|||Hi, thanks for the answers.
I tried to put the following parameter setting in the dataset (... button)
@.TimeDiff = Parameter!TimeDiff.Value
but now I get the following error message:
The report parameter 'LastDate' has a DefaultValue or a ValidValue that
depends on the report parameter "TimeDiff". Forward dependencies are not
allowed"
The LastDate report parameter is coming from the dataset from field
StartTime and that is calculated from TimeDiff.
LastTime parameter is Hidden and available value is from query and the
default value is from query.
Still something wrong.|||Hi,
I solved the last problem just by changing the order of the parameters in
report side.
Thanks for nicismyname and Bruce L-C|||Hi, you should be aware of the order of your parameters. If lastdate
parameter it's dataset refers to the timediff parameter you should
first prompt the timediff parameter. The prompting order is determined
by the 'report>parameters' menu. You'll notice two arrows at the
dialog popping up after selecting 'report>parameters', use the up and
down arrow to determine the prompting order.

Parameter passing from master to subreport

Is it possible to pass a multi-value parameter from a master report to one or more subreports? If so, how?

Thanks for any help.

Wayne E. Pfeffer

Yes you can do this. I suggest the following: In your subreport make the report parameter a multi-value parameter. Test the subreport stand alone and make sure it works. Then drop the subreport into your main report, and map the subreport parameters to the main report parameters.

Scenario 1 (multi value parameter pass through):
The Main report has report parameter A which is a multi value parameter. You want to pass it to SubReport1 which has a report parameter B which is also defined as multi value parameter. In this case the mapping is just a simple parameter expression: =Parameters!A.Value
The subreport RDL element would look like this:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 2:
The Main report has no report parameters. You have a SubReport1 with a report parameter B defined as multi value parameter. You want to pass e.g. three selected values "A", "B", "C" as parameter values to the subreport. You need to create a multidimensional object array on-the-fly, e.g. with the Split function, e.g.: =Split("A,B,C", ",")
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Split("A,B,C", ",")</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 3:
The Main report has a multi-value parameter A. You want to pass only the first selected value from the main report's parameter to the subreport and the subreport's report parameter B is a single-value parameter. You can do this by using e.g. =Parameters!A.Value(0)
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value(0)</Value>
</Parameter>
</Parameters>
</Subreport>

-- Robert

|||Thank you for the help, it worked out.

Wayne|||Is there a limit on how many parameters you can pass if you are using Scenario1. I have 5 parameters and 1 subreport. I can pass in the first 4 (A,B, C,D) and the report works fine but when I try to pass in the last one (E), which is no different than the previous 4 parameters other than it might have a few more values than the others, I get an error saying that one or more parameters need has not been specified.|||

There is no limit on the number of parameters.

Instead, it seems like the parameter values passed in violate constraints defined for the subreport parameters. For example, the subreport parameter's data type is integer and you pass string values. Or you pass a NULL value ('Nothing' in VB) as one of the parameter values - which is not allowed for multi value parameters, etc.

-- Robert

|||

Okay, I am sort of new to reporting services...where would I go to check the data type?..I don't believe anything is a null but where would I go to check that? The subreport works fine on its own...The master report will be/is populated by stored procedures so at first I thought something may be off with the stored procedures but all the other parameters work just fine when hooked up to the subreport's parameters. Currently, the subreport is running off of data from the cube however, it has been suggested that I change the subreport to pull from the same stored procedures as the master report and it will work fine...which I don't understand because again the first four parameters work fine with out using the stored procedures. Any further assistance would be greatly appreciated.

Thanks.

Parameter passing from master to subreport

Is it possible to pass a multi-value parameter from a master report to one or more subreports? If so, how?

Thanks for any help.

Wayne E. Pfeffer

Yes you can do this. I suggest the following: In your subreport make the report parameter a multi-value parameter. Test the subreport stand alone and make sure it works. Then drop the subreport into your main report, and map the subreport parameters to the main report parameters.

Scenario 1 (multi value parameter pass through):
The Main report has report parameter A which is a multi value parameter. You want to pass it to SubReport1 which has a report parameter B which is also defined as multi value parameter. In this case the mapping is just a simple parameter expression: =Parameters!A.Value
The subreport RDL element would look like this:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 2:
The Main report has no report parameters. You have a SubReport1 with a report parameter B defined as multi value parameter. You want to pass e.g. three selected values "A", "B", "C" as parameter values to the subreport. You need to create a multidimensional object array on-the-fly, e.g. with the Split function, e.g.: =Split("A,B,C", ",")
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Split("A,B,C", ",")</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 3:
The Main report has a multi-value parameter A. You want to pass only the first selected value from the main report's parameter to the subreport and the subreport's report parameter B is a single-value parameter. You can do this by using e.g. =Parameters!A.Value(0)
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value(0)</Value>
</Parameter>
</Parameters>
</Subreport>

-- Robert

|||Thank you for the help, it worked out.

Wayne|||Is there a limit on how many parameters you can pass if you are using Scenario1. I have 5 parameters and 1 subreport. I can pass in the first 4 (A,B, C,D) and the report works fine but when I try to pass in the last one (E), which is no different than the previous 4 parameters other than it might have a few more values than the others, I get an error saying that one or more parameters need has not been specified.|||

There is no limit on the number of parameters.

Instead, it seems like the parameter values passed in violate constraints defined for the subreport parameters. For example, the subreport parameter's data type is integer and you pass string values. Or you pass a NULL value ('Nothing' in VB) as one of the parameter values - which is not allowed for multi value parameters, etc.

-- Robert

|||

Okay, I am sort of new to reporting services...where would I go to check the data type?..I don't believe anything is a null but where would I go to check that? The subreport works fine on its own...The master report will be/is populated by stored procedures so at first I thought something may be off with the stored procedures but all the other parameters work just fine when hooked up to the subreport's parameters. Currently, the subreport is running off of data from the cube however, it has been suggested that I change the subreport to pull from the same stored procedures as the master report and it will work fine...which I don't understand because again the first four parameters work fine with out using the stored procedures. Any further assistance would be greatly appreciated.

Thanks.

Parameter passing CR 10 from VB6

Hi,

I am having problems passing multiple parameters to a CR10 report through VB6 and SQL 2000 sp's.

I think that my vb syntax for passing the parameters is incorrect because:

1. I can call the reports fine within crystal reports
2. The stored procedures return the correct values using parameter passing in Query Analyzer
3. When I set EnableParameterPrompting = True in my VB code the report shows up fine when the parameters are entered through the Crystal parameter dialog box. However, this gives the user free text options to enter what ever they like.

The report accepts four parameters and I get a "Failed to open rowset 22007 - Syntax error converting datetime from character string".

All of my parameter values are set to be strings and this error is not making any sense to me.

Any help would be appreciated.

Thanks,

b1. The ERR in question is related to the Conversion from Date to String variable,
which I presume you have to correct it and store to a variable (strkey1)

Set crystal = New CRAXDRT.Application 'MANAGES REPORTS
Set Report = crystal.OpenReport(a) 'OPEN OUR REPORT - 'a' is the name
of your report with FULL PATH.
Report.DiscardSavedData
Report.Database.SetDataSource rsBldo ' rsBldo is the recordset
containing the Data.
Report.EnableParameterPrompting = False
Report.EnableSelectDistinctRecords = True
Report.EnableGeneratingDataForHiddenObject = False
Report.ParameterFields(j).ClearCurrentValueAndRange

// strkey1 is the variable which holds the above string variable
// Mandatorily you have to create a Parameter Field in CR.

Report.ParameterFields(1).AddCurrentValue (strkey1)

THIS I USES IN CR 9.2 and NOT CR 10, pls try out, hope it should work.