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

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

Parameter Optimization

Hi guys,
I have a rather complex query which accepts 2 date parameters. When the
dates are hard-coded the query runs in 2 seconds; however when a parameter
is passed with the same values the query runs in excess of 2 minutes.
I got around this by building the entire query into a varchar variable, and
then using the exec(strexpression).
Is there a problem in the way the SQL server optimizer handles parameterized
queries? And what are the alternate ways to get around this?
Thanks,
JustinMaybe post some ddl on this specifying what the datatypes are.
"Justin" wrote:

> Hi guys,
>
> I have a rather complex query which accepts 2 date parameters. When the
> dates are hard-coded the query runs in 2 seconds; however when a parameter
> is passed with the same values the query runs in excess of 2 minutes.
>
> I got around this by building the entire query into a varchar variable, an
d
> then using the exec(strexpression).
>
> Is there a problem in the way the SQL server optimizer handles parameteriz
ed
> queries? And what are the alternate ways to get around this?
>
> Thanks,
> Justin
>
>|||I noticed that if you have many records in tables, conversion from varchar
to nvarchar or back can cause behavior you talk about. maybe when you
hardcode dates u use different type than when you pass them as params?
peter

Parameter Number is Invalid

I am using Crystal Reports 8.0, with SQL Server.
I am trying to create a Report on a Stored Procedure.
My Stored Procedure is having 3 parameters (2 datetime parameters and 1 integer parameter).
When i select the stored procedure in the initial stage, it asks for the parameters. I tried entering 2050-01-01 00:00:00.000, etc etc ...

But I am getting an error "Parameter Number 1 is invalid" which is the same datetime field.

When I run the stored procedure in the query analyser. Its runs fine:
exec spTravelRpt_Person_chargeno '01-01-1990 00:00:00.000','01-01-2050 00:00:00.000',-1.

Can anyone plzzzz plzzz help me.. bcoz i am stuck.. i cannot move forward..

Ur help will be appreicated.Give only 2050-01-01 to datetime field and when calling the SP from your front end, pass the parameter in the format you want|||Hi Madhi,

I tried giving that too ... just
2050-01-01 ...
but it didnt help me...
Its giving the same error:
Parameter number 1 is invalid.
What do i do ????|||change the parameters datatype to varchar(20) instead of datetime and check.sql

Parameter name getting garbled

I have a function that calls a sproc, and passes in a bunch of parameters. One of the parameters is named @.ProcessorEmail. Occasionally, the parameter name apparently gets garbled at runtime, as the following error occurs:

System.Data.SqlClient.SqlException: @.ProcessorEm??. is not a parameter for procedure gUser

The "@.ProcessorEm" part is always fine, then there will be various characters in place of the "ail" part.

Any ideas?Does your code hardcode the params or are they been fetched/constructed?|||They are hardcoded. The rest of the params are fine, it is always this same parameter that gets messed up. Again, it does not happen every time, just occasionally.|||Does the data your sending in the param contain any control codes or slighly odd unicode (i.e. taking data from a non .net source)?|||Actually, the parameter in question is being passed as an output parameter, so it does not have a value when I am passing it.|||What data is returned from the proc? Is it only outputs or a combination of outputs and record-sets?|||A combination of outputs. The sproc accepts a single input sproc, then the rest of the params are output params, with only a single record being returned. The param being passed in is an email address, which is the primary key constraint in the table, so there should be no dupes.|||Do you ensure that you've processed *every* row of the record set before you attempt to access the output params?|||Sorry, I misspoke. It is only output parameters, no recordsets.|||Odd one, are you sharing the command object or creating a fresh one each time?|||It's new on each instantiation. The command resides in a class I created called User. There are two constructors for User, one accepts the userid, the other accepts the email address. The only way to run the sproc is to create a new User object in my code.

Dim mUser as New User("me@.domain.com")

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".sql

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".

Parameter location

With 2005, is it possible to control the position of the parameter boxes? I have 3 parameters, the first 2 lay out horizontally, the third stacks up under the first. There is plenty of room across the report for all the parameters to display in one row.

You can not change this behavior if you are using Report Manager.

If you wanted to you could write your own web app and use the Viewer Controls and display the parameters anyway you want. This does however require you write a fair amount of code for rendering parameter areas.

parameter limiting

I have a few required parameters listed at the top of a report. I want to
make it where when one parameter is chosen in the drop down menu, the other
parameter(s) limit their choices accordingly. I can't seem to find a way to
do this though.
Any help would be great. Thanks.Use the first parameter in the SQL for the second
"scraejtp" wrote:
> I have a few required parameters listed at the top of a report. I want to
> make it where when one parameter is chosen in the drop down menu, the other
> parameter(s) limit their choices accordingly. I can't seem to find a way to
> do this though.
> Any help would be great. Thanks.
>|||Don't think I follow. This is at the top of a report, there are 4 parameters
they need to specify. I want it to somehow update the list of the other drop
down parameters when they choose one.
For example at the top of a report they will have a parameter "Automaker",
and it will have Ford, GM. Then another parameter at the top called "cars"
and it will contain neon, mustang, corvette, f150. When they pick Ford under
automatker, I want the other parameter drop down to only show the mustang and
f150 without going to a different report.
"Antoon" wrote:
> Use the first parameter in the SQL for the second
> "scraejtp" wrote:
> > I have a few required parameters listed at the top of a report. I want to
> > make it where when one parameter is chosen in the drop down menu, the other
> > parameter(s) limit their choices accordingly. I can't seem to find a way to
> > do this though.
> > Any help would be great. Thanks.
> >
> >|||Base the dependent parameters on queries. Then have the SQL for the query
use the report parameter value to define the list.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
news:C59CD7D4-7966-402F-B770-1EA9817713A3@.microsoft.com...
> Don't think I follow. This is at the top of a report, there are 4
> parameters
> they need to specify. I want it to somehow update the list of the other
> drop
> down parameters when they choose one.
> For example at the top of a report they will have a parameter "Automaker",
> and it will have Ford, GM. Then another parameter at the top called "cars"
> and it will contain neon, mustang, corvette, f150. When they pick Ford
> under
> automatker, I want the other parameter drop down to only show the mustang
> and
> f150 without going to a different report.
> "Antoon" wrote:
>> Use the first parameter in the SQL for the second
>> "scraejtp" wrote:
>> > I have a few required parameters listed at the top of a report. I want
>> > to
>> > make it where when one parameter is chosen in the drop down menu, the
>> > other
>> > parameter(s) limit their choices accordingly. I can't seem to find a
>> > way to
>> > do this though.
>> > Any help would be great. Thanks.
>> >
>> >|||Got it. Thanks.
"Bruce L-C [MVP]" wrote:
> Base the dependent parameters on queries. Then have the SQL for the query
> use the report parameter value to define the list.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
> news:C59CD7D4-7966-402F-B770-1EA9817713A3@.microsoft.com...
> > Don't think I follow. This is at the top of a report, there are 4
> > parameters
> > they need to specify. I want it to somehow update the list of the other
> > drop
> > down parameters when they choose one.
> >
> > For example at the top of a report they will have a parameter "Automaker",
> > and it will have Ford, GM. Then another parameter at the top called "cars"
> > and it will contain neon, mustang, corvette, f150. When they pick Ford
> > under
> > automatker, I want the other parameter drop down to only show the mustang
> > and
> > f150 without going to a different report.
> >
> > "Antoon" wrote:
> >
> >> Use the first parameter in the SQL for the second
> >>
> >> "scraejtp" wrote:
> >>
> >> > I have a few required parameters listed at the top of a report. I want
> >> > to
> >> > make it where when one parameter is chosen in the drop down menu, the
> >> > other
> >> > parameter(s) limit their choices accordingly. I can't seem to find a
> >> > way to
> >> > do this though.
> >> > Any help would be great. Thanks.
> >> >
> >> >
>
>|||Also, I forgot to mention, it works completely in the preview without any
issues.
"scraejtp" wrote:
> Got it. Thanks.
> "Bruce L-C [MVP]" wrote:
> > Base the dependent parameters on queries. Then have the SQL for the query
> > use the report parameter value to define the list.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
> > news:C59CD7D4-7966-402F-B770-1EA9817713A3@.microsoft.com...
> > > Don't think I follow. This is at the top of a report, there are 4
> > > parameters
> > > they need to specify. I want it to somehow update the list of the other
> > > drop
> > > down parameters when they choose one.
> > >
> > > For example at the top of a report they will have a parameter "Automaker",
> > > and it will have Ford, GM. Then another parameter at the top called "cars"
> > > and it will contain neon, mustang, corvette, f150. When they pick Ford
> > > under
> > > automatker, I want the other parameter drop down to only show the mustang
> > > and
> > > f150 without going to a different report.
> > >
> > > "Antoon" wrote:
> > >
> > >> Use the first parameter in the SQL for the second
> > >>
> > >> "scraejtp" wrote:
> > >>
> > >> > I have a few required parameters listed at the top of a report. I want
> > >> > to
> > >> > make it where when one parameter is chosen in the drop down menu, the
> > >> > other
> > >> > parameter(s) limit their choices accordingly. I can't seem to find a
> > >> > way to
> > >> > do this though.
> > >> > Any help would be great. Thanks.
> > >> >
> > >> >
> >
> >
> >|||Ok, I got it to work, except for one small thing. Now when the screen opens
you have your choices like you should. If you chose a different parameter
than the one it opens with the list does not change accordingly. BUT if you
click anywhere on the screen first, and then change the parameter it will
change the list accordingly. And it only takes the one click and then you can
change the first parameter as many times as you like and it will change
accordingly. It is like it isn't updating until the screen is reselected. Any
insights?
"Bruce L-C [MVP]" wrote:
> Base the dependent parameters on queries. Then have the SQL for the query
> use the report parameter value to define the list.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "scraejtp" <scraejtp@.discussions.microsoft.com> wrote in message
> news:C59CD7D4-7966-402F-B770-1EA9817713A3@.microsoft.com...
> > Don't think I follow. This is at the top of a report, there are 4
> > parameters
> > they need to specify. I want it to somehow update the list of the other
> > drop
> > down parameters when they choose one.
> >
> > For example at the top of a report they will have a parameter "Automaker",
> > and it will have Ford, GM. Then another parameter at the top called "cars"
> > and it will contain neon, mustang, corvette, f150. When they pick Ford
> > under
> > automatker, I want the other parameter drop down to only show the mustang
> > and
> > f150 without going to a different report.
> >
> > "Antoon" wrote:
> >
> >> Use the first parameter in the SQL for the second
> >>
> >> "scraejtp" wrote:
> >>
> >> > I have a few required parameters listed at the top of a report. I want
> >> > to
> >> > make it where when one parameter is chosen in the drop down menu, the
> >> > other
> >> > parameter(s) limit their choices accordingly. I can't seem to find a
> >> > way to
> >> > do this though.
> >> > Any help would be great. Thanks.
> >> >
> >> >
>
>

Parameter Layout: Any way to specify this in report designer?

Hi,
I am designing a report with 12 parameters in 4 groups that I would
like to layout in groups of 3 parameters. I cannot find any way to
specify a layout (other than the order, which makes it confusing for
the user.
Does anyone know if there is a way to do this or if it is something
that is planned for a future version?
Many thanks
PatrickHi, Patrick
Unfortunately, the Report Server doesn't offer much control of the
parameter toolbar, except for the order in which you display your
paremeters, and whether or not to display the parameter toolbar.
If you really need to create such layout, I would recommend creating an
ASP.NET web application with an aspx page that lays out your parameters
using web controls, then you can gather the input from the user in the
postback, and create your URL to the report server, or use the SOAP API
and call the Render() method passing those params in and return the
byte[] of the rendered report in the specified format.
I know this is probably not what you wanted to hear, but people do this
all the time and you can find tons of samples onn the web.
Regards,
Thiago Silva, MCAD.NET
On Nov 22, 6:06 pm, pfisc...@.hotmail.co.uk wrote:
> Hi,
> I am designing a report with 12 parameters in 4 groups that I would
> like to layout in groups of 3 parameters. I cannot find any way to
> specify a layout (other than the order, which makes it confusing for
> the user.
> Does anyone know if there is a way to do this or if it is something
> that is planned for a future version?
> Many thanks
> Patrick|||Hi Thiago,
You got it :( - BUT thanks very much for confirming this as I know I
have not missed something that I would kick myself for!
...The thing is, it is so easy to use as it stands and that is the
beauty of it I think. It's just that much more effort to code it -> but
again thanks for the tip on samples for this - clearly others have had
a similar experience. Fingers crossed it will be improved in a future
version.
cheers Patrick
tafs7 wrote:
> Hi, Patrick
> Unfortunately, the Report Server doesn't offer much control of the
> parameter toolbar, except for the order in which you display your
> paremeters, and whether or not to display the parameter toolbar.
> If you really need to create such layout, I would recommend creating an
> ASP.NET web application with an aspx page that lays out your parameters
> using web controls, then you can gather the input from the user in the
> postback, and create your URL to the report server, or use the SOAP API
> and call the Render() method passing those params in and return the
> byte[] of the rendered report in the specified format.
> I know this is probably not what you wanted to hear, but people do this
> all the time and you can find tons of samples onn the web.
> Regards,
> Thiago Silva, MCAD.NET
> On Nov 22, 6:06 pm, pfisc...@.hotmail.co.uk wrote:
> > Hi,
> >
> > I am designing a report with 12 parameters in 4 groups that I would
> > like to layout in groups of 3 parameters. I cannot find any way to
> > specify a layout (other than the order, which makes it confusing for
> > the user.
> >
> > Does anyone know if there is a way to do this or if it is something
> > that is planned for a future version?
> >
> > Many thanks
> >
> > Patrick

Parameter Layout on SRS report

Hi, I have a list of parameters that I am showing for an srs report
and want them to visually look like this:
Company: <combo box>
Date Range Type: <combo box> From: <combo box> To: <combo
box>
but it's showing them like this:
Company: <combo box> Date Range Type: <combo box>
From: <combo box> To: <combo box>
I don't seem to have a lot of control over this. Is there a way to
set this up so I can format the look of the parameters the way I want
them?You don't have any control over this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doogie" <dnlwhite@.dtgnet.com> wrote in message
news:77bf060f-5f64-4bc2-861a-765da97425c5@.60g2000hsy.googlegroups.com...
> Hi, I have a list of parameters that I am showing for an srs report
> and want them to visually look like this:
> Company: <combo box>
> Date Range Type: <combo box> From: <combo box> To: <combo
> box>
>
> but it's showing them like this:
> Company: <combo box> Date Range Type: <combo box>
> From: <combo box> To: <combo box>
> I don't seem to have a lot of control over this. Is there a way to
> set this up so I can format the look of the parameters the way I want
> them?|||On Mar 6, 11:47=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You don't have any control over this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Doogie" <dnlwh...@.dtgnet.com> wrote in message
> news:77bf060f-5f64-4bc2-861a-765da97425c5@.60g2000hsy.googlegroups.com...
>
> > Hi, I have a list of parameters that I am showing for an srs report
> > and want them to visually look like this:
> > Company: =A0<combo box>
> > Date Range Type: =A0<combo box> =A0 =A0 =A0 From: <combo box> =A0 To: <c=ombo
> > box>
> > but it's showing them like this:
> > Company: =A0<combo box> =A0 =A0 =A0 =A0 =A0Date Range Type: =A0<combo bo=x>
> > From: <combo box> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0To: <combo box>
> > I don't seem to have a lot of control over this. =A0Is there a way to
> > set this up so I can format the look of the parameters the way I want
> > them... Hide quoted text -
> - Show quoted text -
Is there some option for maybe creating hidden combo boxes or
something that may allow me to space things up? I tried to add a
hidden text box and because it was hidden, it didn't actually space
anything out.|||The only way to have control is to have your own website and your own web
page and integrate with RS. A lot of effort though. Truly, there is no
workaround that I am aware of. If there was a workaround I would use it
myself because I do have some reports where this would come in handy. I not
aware of any changes in this area for 2008 either.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doogie" <dnlwhite@.dtgnet.com> wrote in message
news:16b3a5c4-1031-478c-bd47-af607ac3b91b@.q78g2000hsh.googlegroups.com...
On Mar 6, 11:47 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You don't have any control over this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Doogie" <dnlwh...@.dtgnet.com> wrote in message
> news:77bf060f-5f64-4bc2-861a-765da97425c5@.60g2000hsy.googlegroups.com...
>
> > Hi, I have a list of parameters that I am showing for an srs report
> > and want them to visually look like this:
> > Company: <combo box>
> > Date Range Type: <combo box> From: <combo box> To: <combo
> > box>
> > but it's showing them like this:
> > Company: <combo box> Date Range Type: <combo box>
> > From: <combo box> To: <combo box>
> > I don't seem to have a lot of control over this. Is there a way to
> > set this up so I can format the look of the parameters the way I want
> > them... Hide quoted text -
> - Show quoted text -
Is there some option for maybe creating hidden combo boxes or
something that may allow me to space things up? I tried to add a
hidden text box and because it was hidden, it didn't actually space
anything out.

Parameter key/value list in the actual report?

We are in need of a visualization of all parameters (about 16 of them)
for our reports. (When a cutstomer prints a report, we need to have the
parameters (mostly filterering input) values available)
Is there a way if "looping" the Parameters Collection, creating a String
or populate a table with the parameter/value pairs?
We tried to create a string by looping the Report.Parameters object in
the "Code" section, but it is not a collection so we failed there.
Also tried to access the parameters "by index". Looping until we fails,
but it seems you cant access the paremeters by index :(
The solution we have is to create a table with a field for each
parameter. But this is a non dynamic and non generic solution. A bit of
worl work too for our 25+ reports with about 10-15 parameters each.
Anyone out there have a solution for this problem?
Regards
Jonas MontonenJFYI, parameters can be accessed by name, i.e.
=Parameters("Param1").Value (same as =Parameters!Param1.Value)
Or even =Parameters(Field!A.Value).Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Montonen" <jonas.montonen@.ascade.se> wrote in message
news:OMW23aBwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> We are in need of a visualization of all parameters (about 16 of them) for
> our reports. (When a cutstomer prints a report, we need to have the
> parameters (mostly filterering input) values available)
> Is there a way if "looping" the Parameters Collection, creating a String
> or populate a table with the parameter/value pairs?
> We tried to create a string by looping the Report.Parameters object in the
> "Code" section, but it is not a collection so we failed there.
> Also tried to access the parameters "by index". Looping until we fails,
> but it seems you cant access the paremeters by index :(
> The solution we have is to create a table with a field for each parameter.
> But this is a non dynamic and non generic solution. A bit of worl work too
> for our 25+ reports with about 10-15 parameters each.
> Anyone out there have a solution for this problem?
> Regards
> Jonas Montonen|||Lev Semenets [MSFT] wrote:
> JFYI, parameters can be accessed by name, i.e.
> =Parameters("Param1").Value (same as =Parameters!Param1.Value)
> Or even =Parameters(Field!A.Value).Value
>
Yes, that we know.
What I want is to be able to (programmatically) loop all parameter and
there values, appending them to a tring and display that string in the
report.
So if I add a parameters, I will not have to update the logic for
displaying the parameter values.
Regards Jonas Montonen|||Jonas Montonen wrote:
> Lev Semenets [MSFT] wrote:
>> JFYI, parameters can be accessed by name, i.e.
>> =Parameters("Param1").Value (same as =Parameters!Param1.Value)
>> Or even =Parameters(Field!A.Value).Value
> Yes, that we know.
> What I want is to be able to (programmatically) loop all parameter and
> there values, appending them to a tring and display that string in the
> report.
> So if I add a parameters, I will not have to update the logic for
> displaying the parameter values.
> Regards Jonas Montonen
As a workaround I'm trying another funtion in the Code section.
But I would like to have a identical function in each report... so some
parameter could be missing.
Is there a way of refering/checking if a parameter exists and not get
the error '
Eg:
IF(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
.
.
End if
This line generates this error today :(
"The value expression for the textbox ?textbox2? contains an error: The
expression referenced a non-existing parameter in the report parameters
collection."
FYI:
- Textbox2.Value: "=Code.getParametersHeader()"
- PARAM_THAT_ARE_MISSING is a parmeter that is not present in the actual
report (Dummy to trigger this error)
// Regards Jonas Montonen
Function getParametersHeader()
Dim retString
Dim parCount
Dim newLine
Dim ParamsPerLine
ParamsPerLine = 3
parCount = 0
If(Report.Parameters("Pop").Label <> "") then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "Pop: " +
Report.Parameters("Pop").Label
end if
if(Report.Parameters("Measure").Label <> "") then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "Pop: " +
Report.Parameters("Pop").Label
end if
if(Report.Parameters("TimeType").Label <> "") then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "Time Type: " +
Report.Parameters("TimeType").Label
end if
if(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "PARAM_THAT_ARE_MISSING: " +
Report.Parameters("PARAM_THAT_ARE_MISSING").Label
end if
Return retString
End Function|||Jonas Montonen wrote:
> We are in need of a visualization of all parameters (about 16 of them)
> for our reports. (When a cutstomer prints a report, we need to have the
> parameters (mostly filterering input) values available)
> Is there a way if "looping" the Parameters Collection, creating a String
> or populate a table with the parameter/value pairs?
> We tried to create a string by looping the Report.Parameters object in
> the "Code" section, but it is not a collection so we failed there.
> Also tried to access the parameters "by index". Looping until we fails,
> but it seems you cant access the paremeters by index :(
> The solution we have is to create a table with a field for each
> parameter. But this is a non dynamic and non generic solution. A bit of
> worl work too for our 25+ reports with about 10-15 parameters each.
> Anyone out there have a solution for this problem?
> Regards
> Jonas Montonen
Not very nice... there must be a better way.
Usage:
TextFieldXX.Value "=Code.getParametersHeader(5, "Pop,AgreementType,
ServiceLevel, CRAZY_PARAM_THAT_DOES_NOT_EXIST")
Function getParametersHeader(paramsPerRow, daWantedParams)
Dim myArray() As String = Split(daWantedParams, ",")
Dim RetVal As String
Dim newLine As String
Dim parCount As Integer
Dim paramsPerLine
ON ERROR RESUME NEXT
For Each curParam As String In myArray ' Iterate through elements.
If(Report.Parameters(curParam.ToString()).Label <> "") then
newLine = getParamNewLine(parCount, paramsPerRow)
RetVal += newLine + curParam.ToString() + ": " +
Report.Parameters(curParam.ToString()).Label
parCount += 1
end if
Next
Return RetVal
End Function
Function getParamNewLine(countValue, valuesPerRow)
if((countValue mod valuesPerRow) = 0) then
return vbcrlf
else
if((countValue mod valuesPerRow) = 1) then
return " "
else
return " | "
end if
end if
End Function|||I'm afraid that is not supported. But I think it is possible to do
indirectly, using custom assemly.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Montonen" <jonas.montonen@.ascade.se> wrote in message
news:%236H$b9LwEHA.1396@.tk2msftngp13.phx.gbl...
> Jonas Montonen wrote:
>> Lev Semenets [MSFT] wrote:
>> JFYI, parameters can be accessed by name, i.e.
>> =Parameters("Param1").Value (same as =Parameters!Param1.Value)
>> Or even =Parameters(Field!A.Value).Value
>> Yes, that we know.
>> What I want is to be able to (programmatically) loop all parameter and
>> there values, appending them to a tring and display that string in the
>> report.
>> So if I add a parameters, I will not have to update the logic for
>> displaying the parameter values.
>> Regards Jonas Montonen
> As a workaround I'm trying another funtion in the Code section.
> But I would like to have a identical function in each report... so some
> parameter could be missing.
> Is there a way of refering/checking if a parameter exists and not get the
> error '
> Eg:
> IF(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
> .
> .
> End if
> This line generates this error today :(
> "The value expression for the textbox ?textbox2? contains an error: The
> expression referenced a non-existing parameter in the report parameters
> collection."
> FYI:
> - Textbox2.Value: "=Code.getParametersHeader()"
> - PARAM_THAT_ARE_MISSING is a parmeter that is not present in the actual
> report (Dummy to trigger this error)
> // Regards Jonas Montonen
> Function getParametersHeader()
> Dim retString
> Dim parCount
> Dim newLine
> Dim ParamsPerLine
> ParamsPerLine = 3
> parCount = 0
> If(Report.Parameters("Pop").Label <> "") then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "Pop: " +
> Report.Parameters("Pop").Label
> end if
> if(Report.Parameters("Measure").Label <> "") then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "Pop: " +
> Report.Parameters("Pop").Label
> end if
> if(Report.Parameters("TimeType").Label <> "") then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "Time Type: " +
> Report.Parameters("TimeType").Label
> end if
> if(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "PARAM_THAT_ARE_MISSING: " +
> Report.Parameters("PARAM_THAT_ARE_MISSING").Label
> end if
> Return retString
> End Functionsql

Parameter is missing a value

Hi Folks,

I'm receiving the "Parameter is missing a value" error message while testing my report.

I have five parameters, two of which are hidden that gets prefilled from the query below. The hidden parameters are DatatechClient and DatatechProduct.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Three parameters are shown and the report works fine as long as the CMRNum and AcctNum is found in the V_TranslationTable however, the error generates when they are not found. I looked through the other threads in this forum that deals with "Parameter is missing a value" but it didn't appear to be a solution shown.

Thanks in advance for any assistance you give.

I take it that when either CMRNum or AcctNum don't exist, you don't want the report to error out. Use this in your query - the report should run and return nothing, but won't error out:

Where IsNull(CMRNum,'') = @.Cmr AND IsNull(AcctNum,'') = @.ClientNum

|||

Hi Jamvir,

Thanks for responding.

I need to give more clearity. My report consists of six datasets and I'm working with RS2005. @.Cmr and @.ClientNum gets populated by a query in my first two datasets. My two hidden parameters @.DatatechClient and @.DatatechProduct are a part of my third dataset and they are the columns that are coming back blank from the query below.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my fifth dataset query below.

SELECT EntityID, PrimaryDirInd, IsOnContract, [5DigitCode], EntityName, Addr1, City, State, Zip, Attn, Phone, ClientID, ProductID
FROM V_EntityDirectories
WHERE ((DirID = @.DirNum) AND (ClientID = @.DatatechClient) AND (ProductID = @.DatatechProduct))
ORDER BY EntityID

Also blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my sixth dataset query below.

SELECT PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID, PkgHdngs.HdngSeqNum, PkgHdngs.HdngText, PkgHdngs.HdngCode, PkgHdngs.DirVer, HdngAds.AdUnitID, HdngAds.AdRate
FROM PkgHdngs INNER JOIN
HdngAds ON PkgHdngs.ClientID = HdngAds.ClientID AND PkgHdngs.ProductID = HdngAds.ProductID AND PkgHdngs.DirID = HdngAds.DirID AND PkgHdngs.DirVer = HdngAds.DirVer AND PkgHdngs.HdngSeqNum = HdngAds.HdngSeqNum
WHERE ((PkgHdngs.DirID = @.DirID) AND (PkgHdgns.ClientID = @.DatatechClient) AND (PkgHdgns.ProductID = @.DatatechProduct))
ORDER BY PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID

Best regards

|||

Have Non queried default values for DatatechClient and DatatechProduct parameters and use the follwing expression for it:

=IIf(Count(Fields!DatatechClient.Value, "DataSet3") > 0, First(Fields!DatatechClient.Value, "DataSet3"), "SomeDefaultValueOrBlank")

and similarly for DatatechProduct

Parameter drop down boxes alignment

I am doing a report currently and have got 3 parameters in the header. The
problem is that I cannot fit the parameters in 1 line.
Is there a way to correct this.
I wanted the 3 dropdown boxes (assignment, start date, end date) in 1 line.
Thanks,
Kiran.Unfortunately you cannot customize the default layout of the parameter
controls. They will always show 1 or 2 per line. Best way to get the look
and layout that you want is to use the ReportViewer control and then in your
page or windows control you create your own textboxes/list boxes for your
parameters and lay them out however you like. Then you will need to pass the
values into the reportViewer control thru the api.
Let me know if you need samples of this.
Bret Updegraff
http://bretup.spaces.live.com/
"Kiran Vukkadala" wrote:
> I am doing a report currently and have got 3 parameters in the header. The
> problem is that I cannot fit the parameters in 1 line.
> Is there a way to correct this.
> I wanted the 3 dropdown boxes (assignment, start date, end date) in 1 line.
> Thanks,
> Kiran.
>|||Thank you bret. I understand the workaround. It would be appreciated if I
could get some samples. Thanks. mail address is kiranv83@.hotmail..
"Bret" wrote:
> Unfortunately you cannot customize the default layout of the parameter
> controls. They will always show 1 or 2 per line. Best way to get the look
> and layout that you want is to use the ReportViewer control and then in your
> page or windows control you create your own textboxes/list boxes for your
> parameters and lay them out however you like. Then you will need to pass the
> values into the reportViewer control thru the api.
> Let me know if you need samples of this.
> Bret Updegraff
> http://bretup.spaces.live.com/
>
> "Kiran Vukkadala" wrote:
> > I am doing a report currently and have got 3 parameters in the header. The
> > problem is that I cannot fit the parameters in 1 line.
> >
> > Is there a way to correct this.
> > I wanted the 3 dropdown boxes (assignment, start date, end date) in 1 line.
> >
> > Thanks,
> > Kiran.
> >|||OK, but if I use navigation inside a report, the linked report will show up
"in standard way" and I have no control over the parameters, right?
The only way I can imagine is to use url-navigation with relatively complex
concatenated strings like" url/owntargetreport.aspx?parameters".
I would really appreciate examples for this.
"Bret" wrote:
> Unfortunately you cannot customize the default layout of the parameter
> controls. They will always show 1 or 2 per line. Best way to get the look
> and layout that you want is to use the ReportViewer control and then in your
> page or windows control you create your own textboxes/list boxes for your
> parameters and lay them out however you like. Then you will need to pass the
> values into the reportViewer control thru the api.
> Let me know if you need samples of this.
> Bret Updegraff
> http://bretup.spaces.live.com/
>
> "Kiran Vukkadala" wrote:
> > I am doing a report currently and have got 3 parameters in the header. The
> > problem is that I cannot fit the parameters in 1 line.
> >
> > Is there a way to correct this.
> > I wanted the 3 dropdown boxes (assignment, start date, end date) in 1 line.
> >
> > Thanks,
> > Kiran.
> >

Parameter direction of a stored procedure

I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcRetDbl]

@.Threshold real, @.Result real OUTPUT
AS
BEGIN

SET NOCOUNT ON;

SELECT @.Result = Channel1 FROM DataTable WHERE Channel2 < @.Threshold
END

But then when I look at the properties of the @.Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.

I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.

KamenYou are probably thinking too hard :)

You need to supply a parameter in to give you something to read when the value comes out. I can't remember what I use for sqlParameters - probably inputoutput. Try both - what have you got to lose?|||The only pure output from a stored procedure is the return code value and any result sets. Procedure parameters must be input, and can optionally be output too.

-PatP

Parameter dependency on other params

Hi,
I have a param in my report which depends on the other two parameters. So
every time I change either Param1 or Param2 the list for Param3 is
automatically refreshed. While this works fine in one of the reports, I am
having following problems in the another report -
1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
2. Even a mouse-click in the Param2 box, half of the time, takes the cursor
to Param1 box. A very "cautious" click prevent it from going back to Param1
box :-)
3. The report "hangs" at time in the "Preview" mode and it adds 3-4 blank
pages at the end of the report although I know that the report should not
exceed 1 page.
4. I get "Object reference not set to an instance of an object" and "A
generic error occured in GDI+" randomly.
Param1 and Param2 are of type datetime and are not related. Param3 is of
type string and comes from a query based on the values for Param1 and Param2.
Has anybody else faced this ? Any solutions.
RegardsYou might be seeing a problem in preview in the report designer. Try
deploying it and see if the same problem happens. I have seen other issues
in the designer with cascading parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dude" <Dude@.discussions.microsoft.com> wrote in message
news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> Hi,
> I have a param in my report which depends on the other two parameters. So
> every time I change either Param1 or Param2 the list for Param3 is
> automatically refreshed. While this works fine in one of the reports, I am
> having following problems in the another report -
> 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> 2. Even a mouse-click in the Param2 box, half of the time, takes the
cursor
> to Param1 box. A very "cautious" click prevent it from going back to
Param1
> box :-)
> 3. The report "hangs" at time in the "Preview" mode and it adds 3-4 blank
> pages at the end of the report although I know that the report should not
> exceed 1 page.
> 4. I get "Object reference not set to an instance of an object" and "A
> generic error occured in GDI+" randomly.
> Param1 and Param2 are of type datetime and are not related. Param3 is of
> type string and comes from a query based on the values for Param1 and
Param2.
> Has anybody else faced this ? Any solutions.
> Regards|||I don't see the report hang but the "Available List" is not refreshed when I
modify the Param1 or Param2. Only after I click on "View Report" is the list
modified.
I found the sam thing happening in the other report which I thought was
working. The only difference is that it has another string Param (Param0)
which is used as an Input param for the Param3. Param0 is populated by a
query. So what I am seeing is -
1. On change of Param0 (a drop down list), Param3 is refreshed automatically
2. On change of Param1 and Param2, Param3 is not refreshed
3. Param3 is refreshed, once I click "View Report".
Is this the expected behaviour ? anything to do with the type (datetime) ?
"Bruce L-C [MVP]" wrote:
> You might be seeing a problem in preview in the report designer. Try
> deploying it and see if the same problem happens. I have seen other issues
> in the designer with cascading parameters.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dude" <Dude@.discussions.microsoft.com> wrote in message
> news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> > Hi,
> > I have a param in my report which depends on the other two parameters. So
> > every time I change either Param1 or Param2 the list for Param3 is
> > automatically refreshed. While this works fine in one of the reports, I am
> > having following problems in the another report -
> > 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> > 2. Even a mouse-click in the Param2 box, half of the time, takes the
> cursor
> > to Param1 box. A very "cautious" click prevent it from going back to
> Param1
> > box :-)
> > 3. The report "hangs" at time in the "Preview" mode and it adds 3-4 blank
> > pages at the end of the report although I know that the report should not
> > exceed 1 page.
> > 4. I get "Object reference not set to an instance of an object" and "A
> > generic error occured in GDI+" randomly.
> >
> > Param1 and Param2 are of type datetime and are not related. Param3 is of
> > type string and comes from a query based on the values for Param1 and
> Param2.
> >
> > Has anybody else faced this ? Any solutions.
> >
> > Regards
>
>|||I think you are seeing a bug in Report Designer. If you deploy it and test
it I bet you will see it is OK. I tracked down something similar for someone
else awhile back.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dude" <Dude@.discussions.microsoft.com> wrote in message
news:2FF737A5-D781-4FA1-A205-7BFE7533EEA5@.microsoft.com...
> I don't see the report hang but the "Available List" is not refreshed when
I
> modify the Param1 or Param2. Only after I click on "View Report" is the
list
> modified.
> I found the sam thing happening in the other report which I thought was
> working. The only difference is that it has another string Param (Param0)
> which is used as an Input param for the Param3. Param0 is populated by a
> query. So what I am seeing is -
> 1. On change of Param0 (a drop down list), Param3 is refreshed
automatically
> 2. On change of Param1 and Param2, Param3 is not refreshed
> 3. Param3 is refreshed, once I click "View Report".
> Is this the expected behaviour ? anything to do with the type (datetime) ?
> "Bruce L-C [MVP]" wrote:
> > You might be seeing a problem in preview in the report designer. Try
> > deploying it and see if the same problem happens. I have seen other
issues
> > in the designer with cascading parameters.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dude" <Dude@.discussions.microsoft.com> wrote in message
> > news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> > > Hi,
> > > I have a param in my report which depends on the other two parameters.
So
> > > every time I change either Param1 or Param2 the list for Param3 is
> > > automatically refreshed. While this works fine in one of the reports,
I am
> > > having following problems in the another report -
> > > 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> > > 2. Even a mouse-click in the Param2 box, half of the time, takes the
> > cursor
> > > to Param1 box. A very "cautious" click prevent it from going back to
> > Param1
> > > box :-)
> > > 3. The report "hangs" at time in the "Preview" mode and it adds 3-4
blank
> > > pages at the end of the report although I know that the report should
not
> > > exceed 1 page.
> > > 4. I get "Object reference not set to an instance of an object" and "A
> > > generic error occured in GDI+" randomly.
> > >
> > > Param1 and Param2 are of type datetime and are not related. Param3 is
of
> > > type string and comes from a query based on the values for Param1 and
> > Param2.
> > >
> > > Has anybody else faced this ? Any solutions.
> > >
> > > Regards
> >
> >
> >|||I think it was just a bit slow and I was a bit impatient :-)) Though I did
test it in the deployed version, I was seeing the problem as I didn't wait
enough for it to refresh the params. Thanks.
"Bruce L-C [MVP]" wrote:
> I think you are seeing a bug in Report Designer. If you deploy it and test
> it I bet you will see it is OK. I tracked down something similar for someone
> else awhile back.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dude" <Dude@.discussions.microsoft.com> wrote in message
> news:2FF737A5-D781-4FA1-A205-7BFE7533EEA5@.microsoft.com...
> > I don't see the report hang but the "Available List" is not refreshed when
> I
> > modify the Param1 or Param2. Only after I click on "View Report" is the
> list
> > modified.
> >
> > I found the sam thing happening in the other report which I thought was
> > working. The only difference is that it has another string Param (Param0)
> > which is used as an Input param for the Param3. Param0 is populated by a
> > query. So what I am seeing is -
> > 1. On change of Param0 (a drop down list), Param3 is refreshed
> automatically
> > 2. On change of Param1 and Param2, Param3 is not refreshed
> > 3. Param3 is refreshed, once I click "View Report".
> >
> > Is this the expected behaviour ? anything to do with the type (datetime) ?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You might be seeing a problem in preview in the report designer. Try
> > > deploying it and see if the same problem happens. I have seen other
> issues
> > > in the designer with cascading parameters.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Dude" <Dude@.discussions.microsoft.com> wrote in message
> > > news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> > > > Hi,
> > > > I have a param in my report which depends on the other two parameters.
> So
> > > > every time I change either Param1 or Param2 the list for Param3 is
> > > > automatically refreshed. While this works fine in one of the reports,
> I am
> > > > having following problems in the another report -
> > > > 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> > > > 2. Even a mouse-click in the Param2 box, half of the time, takes the
> > > cursor
> > > > to Param1 box. A very "cautious" click prevent it from going back to
> > > Param1
> > > > box :-)
> > > > 3. The report "hangs" at time in the "Preview" mode and it adds 3-4
> blank
> > > > pages at the end of the report although I know that the report should
> not
> > > > exceed 1 page.
> > > > 4. I get "Object reference not set to an instance of an object" and "A
> > > > generic error occured in GDI+" randomly.
> > > >
> > > > Param1 and Param2 are of type datetime and are not related. Param3 is
> of
> > > > type string and comes from a query based on the values for Param1 and
> > > Param2.
> > > >
> > > > Has anybody else faced this ? Any solutions.
> > > >
> > > > Regards
> > >
> > >
> > >
>
>

parameter dependency

hi folks
I have a Report Parameters issue:
Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
Parameter# 2 is a (organization) drop-down with 'All','US','Canada',etc.
There is NO dependencies between #1 & #2 and both are SQL Queries
Parameter# 3 is a (location) drop-down based on #1+#2:
like 'All-All', 'All-US','2001-Canada',etc.
I have a (functioning) stored proc. for parameter#3, but I'm having issues
with constructing the value for the sproc call. It doesn't let me do things
like:
exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
-or-
exec sp_location @.YR,@.CNTRY
also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
'2000-US' should bring back all locations for drop-down#3, like
(2000-US-)DETROIT, etc.)
I keep getting forward dependency errors on the build.
thanks
RobTried another option:
For parameter#3 I swapped the sproc for a query like:
select 'All' as YR, CNTRY, Location from myview with (nolock)
union
select YR, 'All' as CNTRY, Location from myview with (nolock)
union
select YR, CNTRY, Location from myview with (nolock)
ORDER BY Location
AND then applied filters for YR & CNTRY as the Parameters!?.value, but this
return nothing in Parameter#3 drop-down.
Rob
(done lots of drop-down direct dependencies, but never where a drop-down
depends on more than one parameter value... :(
"tutor" wrote:
> hi folks
> I have a Report Parameters issue:
> Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> Parameter# 2 is a (organization) drop-down with 'All','US','Canada',etc.
> There is NO dependencies between #1 & #2 and both are SQL Queries
> Parameter# 3 is a (location) drop-down based on #1+#2:
> like 'All-All', 'All-US','2001-Canada',etc.
> I have a (functioning) stored proc. for parameter#3, but I'm having issues
> with constructing the value for the sproc call. It doesn't let me do things
> like:
> exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> -or-
> exec sp_location @.YR,@.CNTRY
> also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> '2000-US' should bring back all locations for drop-down#3, like
> (2000-US-)DETROIT, etc.)
> I keep getting forward dependency errors on the build.
> thanks
> Rob|||"=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
For Parm #3, have you tried using code to build the query string instear
of a stored proc?
as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
> hi folks
> I have a Report Parameters issue:
> Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> Parameter# 2 is a (organization) drop-down with
> 'All','US','Canada',etc.
> There is NO dependencies between #1 & #2 and both are SQL Queries
> Parameter# 3 is a (location) drop-down based on #1+#2:
> like 'All-All', 'All-US','2001-Canada',etc.
> I have a (functioning) stored proc. for parameter#3, but I'm having
> issues with constructing the value for the sproc call. It doesn't let
> me do things like:
> exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> -or-
> exec sp_location @.YR,@.CNTRY
> also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> '2000-US' should bring back all locations for drop-down#3, like
> (2000-US-)DETROIT, etc.)
> I keep getting forward dependency errors on the build.
> thanks
> Rob|||Hi Asher
Just tried it and although it runs just fine from the Dataset, it has an
issue with converting the YR value of 'All' to an int...
This parameter stuff is 'sucking out my life force...' (what I think is a 5
minute job is getting close to 5 hours now...)
Let me trying...
Rob
"Asher_N" wrote:
> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
> For Parm #3, have you tried using code to build the query string instear
> of a stored proc?
> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>
> > hi folks
> >
> > I have a Report Parameters issue:
> >
> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> > Parameter# 2 is a (organization) drop-down with
> > 'All','US','Canada',etc.
> >
> > There is NO dependencies between #1 & #2 and both are SQL Queries
> >
> > Parameter# 3 is a (location) drop-down based on #1+#2:
> >
> > like 'All-All', 'All-US','2001-Canada',etc.
> >
> > I have a (functioning) stored proc. for parameter#3, but I'm having
> > issues with constructing the value for the sproc call. It doesn't let
> > me do things like:
> >
> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> >
> > -or-
> >
> > exec sp_location @.YR,@.CNTRY
> >
> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> > '2000-US' should bring back all locations for drop-down#3, like
> > (2000-US-)DETROIT, etc.)
> >
> > I keep getting forward dependency errors on the build.
> >
> > thanks
> > Rob
>|||got it!
adding an 'All' - 'All' portion to the query and putting it in a view was
the ticket (ie.
select TOP 100 'All' as YR, CNTRY, Location from myview with (nolock)
union
select TOP 100 YR, 'All' as CNTRY, Location from myview with (nolock)
union
select TOP 100 YR, CNTRY, Location from myview with (nolock)
union
select TOP 100 'All' as YR, 'All' as CNTRY, Location from myview with (nolock)
ORDER BY Location)
now I get drop-down values...
this is really 'hokey' and I wish somebody could do this with multiple sp
paramters.
rob
"Asher_N" wrote:
> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
> For Parm #3, have you tried using code to build the query string instear
> of a stored proc?
> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>
> > hi folks
> >
> > I have a Report Parameters issue:
> >
> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> > Parameter# 2 is a (organization) drop-down with
> > 'All','US','Canada',etc.
> >
> > There is NO dependencies between #1 & #2 and both are SQL Queries
> >
> > Parameter# 3 is a (location) drop-down based on #1+#2:
> >
> > like 'All-All', 'All-US','2001-Canada',etc.
> >
> > I have a (functioning) stored proc. for parameter#3, but I'm having
> > issues with constructing the value for the sproc call. It doesn't let
> > me do things like:
> >
> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> >
> > -or-
> >
> > exec sp_location @.YR,@.CNTRY
> >
> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> > '2000-US' should bring back all locations for drop-down#3, like
> > (2000-US-)DETROIT, etc.)
> >
> > I keep getting forward dependency errors on the build.
> >
> > thanks
> > Rob
>|||"=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
news:0EF21B79-A883-40ED-95DA-A3848E74AAB1@.microsoft.com:
> Hi Asher
> Just tried it and although it runs just fine from the Dataset, it has
> an issue with converting the YR value of 'All' to an int...
>
Pass it as a string to the code. The code is a VB function that would
build a SQL stmt with string concat, so it deals with strings better. You
could also tets for the values and isert the right stmt better.
> This parameter stuff is 'sucking out my life force...' (what I think
> is a 5 minute job is getting close to 5 hours now...)
> Let me trying...
> Rob
> "Asher_N" wrote:
>> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
>> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
>> For Parm #3, have you tried using code to build the query string
>> instear of a stored proc?
>> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>>
>> > hi folks
>> >
>> > I have a Report Parameters issue:
>> >
>> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
>> > Parameter# 2 is a (organization) drop-down with
>> > 'All','US','Canada',etc.
>> >
>> > There is NO dependencies between #1 & #2 and both are SQL Queries
>> >
>> > Parameter# 3 is a (location) drop-down based on #1+#2:
>> >
>> > like 'All-All', 'All-US','2001-Canada',etc.
>> >
>> > I have a (functioning) stored proc. for parameter#3, but I'm having
>> > issues with constructing the value for the sproc call. It doesn't
>> > let me do things like:
>> >
>> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
>> >
>> > -or-
>> >
>> > exec sp_location @.YR,@.CNTRY
>> >
>> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
>> > '2000-US' should bring back all locations for drop-down#3, like
>> > (2000-US-)DETROIT, etc.)
>> >
>> > I keep getting forward dependency errors on the build.
>> >
>> > thanks
>> > Rob
>>

Parameter defaults not working

Hi,

i have some parameter dropdowns in a report, and i have default values for those parameters, but when viewing the report through IE (just as a customer would), the defaults for the dropdowns are not working - in multivalue dropdowns nothing is selected, and in single select dropdowns the value "<Select Value>" is showing.

The ParameterValue entries of the parameters consist of MDX style values, so they would look like this:

ParameterCaption ParameterValue

-- --

1 January 2006 [Sales Date].[Date Description].&[2123]

and the rdl looks like this (the first one is single select, the second one multi):

<ReportParameter Name="FromSalesDateDescription">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Sales Date].[Date Description].&amp;[2332]</Value>
</Values>
</DefaultValue>
<Prompt>From Date</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>FromSalesDateDescription</DataSetName>
<ValueField>ParameterValue</ValueField>
<LabelField>ParameterCaption</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>

<ReportParameter Name="Saleserson">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Saleserson].[Saleserson].[All]</Value>
</Values>
</DefaultValue>
<Prompt>Saleserson</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>SalesersonDataset</DataSetName>
<ValueField>ParameterValue</ValueField>
<LabelField>ParameterCaption</LabelField>
</DataSetReference>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>

I will mention now that the MDX values used in the defaults are correct. Can anyone give me an idea about this?

Thanks,

sluggy

I have a similar problem. Did anyone ever get a hint on this?

TIA!

|||

Don't know if I understand you correctly but I have something like this:

MyDateDataSet:

select GetDate() ID, 'Today' Description
union
select GetDate()-1, 'Yesterday'
union
select GetDate()-30, '30 days ago'
order by ID desc

I added parameter to my report: MyDateParam

Available values:

*from query

*Data set: MyDateDataSet

*Value field: ID

*Label field: Description

Default value:

*from query

*value field: ID

In RDL it looks like this:

<ReportParameter Name="MyDateParam">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>MyDateDataSet</DataSetName>

<ValueField>ID</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>MyDateParam</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>MyDateDataSet</DataSetName>

<ValueField>ID</ValueField>

<LabelField>Description</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

Either in designer and www it works fine. Default value is the first item in the query (“Today”).

Maciej

sql

Parameter defaults not working

Hi,

i have some parameter dropdowns in a report, and i have default values for those parameters, but when viewing the report through IE (just as a customer would), the defaults for the dropdowns are not working - in multivalue dropdowns nothing is selected, and in single select dropdowns the value "<Select Value>" is showing.

The ParameterValue entries of the parameters consist of MDX style values, so they would look like this:

ParameterCaption ParameterValue

-- --

1 January 2006 [Sales Date].[Date Description].&[2123]

and the rdl looks like this (the first one is single select, the second one multi):

<ReportParameter Name="FromSalesDateDescription">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Sales Date].[Date Description].&amp;[2332]</Value>
</Values>
</DefaultValue>
<Prompt>From Date</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>FromSalesDateDescription</DataSetName>
<ValueField>ParameterValue</ValueField>
<LabelField>ParameterCaption</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>

<ReportParameter Name="Saleserson">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Saleserson].[Saleserson].[All]</Value>
</Values>
</DefaultValue>
<Prompt>Saleserson</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>SalesersonDataset</DataSetName>
<ValueField>ParameterValue</ValueField>
<LabelField>ParameterCaption</LabelField>
</DataSetReference>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>

I will mention now that the MDX values used in the defaults are correct. Can anyone give me an idea about this?

Thanks,

sluggy

I have a similar problem. Did anyone ever get a hint on this?

TIA!

|||

Don't know if I understand you correctly but I have something like this:

MyDateDataSet:

select GetDate() ID, 'Today' Description
union
select GetDate()-1, 'Yesterday'
union
select GetDate()-30, '30 days ago'
order by ID desc

I added parameter to my report: MyDateParam

Available values:

*from query

*Data set: MyDateDataSet

*Value field: ID

*Label field: Description

Default value:

*from query

*value field: ID

In RDL it looks like this:

<ReportParameter Name="MyDateParam">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>MyDateDataSet</DataSetName>

<ValueField>ID</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>MyDateParam</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>MyDateDataSet</DataSetName>

<ValueField>ID</ValueField>

<LabelField>Description</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

Either in designer and www it works fine. Default value is the first item in the query (“Today”).

Maciej