One dropdownlist depend from other.

Hello, I need to know how can I pass two parameters, One select depends from what I selected on the first one. I did it once but I just dont remember

You are looking for a dynamic parameter ( or dynamic dropdown) walk through or " how to".

Ill use the example of store locations and employees located in each store.

Fist what you would do is create your data sources for your drop downs

==================================== Data source "stores"

Select StoreID,

StorelocationName

From storetable

===================================

The second table will have the employee info in it . In this statement will be a parameter in the where clause the depends on the out put of the first data source

======================================== data source "employee"

select EmployeeName,

EmployeeID

from sometable

where storeID = @.storeID

========================================

after creating this data sources you will notice that a new parameter has been created in the report labeled "StoreID".

you will need to use a use the "stores" data source. Make the value field the storeID field. Do the same for the employee data source.

On the Data Tab you will see a "..." button on for each data source press it and go to the parameters tab for the data source employee. Make sure the @.storeID parameter is listed.

this should help you figure out the rest

hope it helps

One column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql

On export to excel

I have list within an list and we have section 1 thru 7. We display the list based the parameters seleted from report for some result section might start from 3 thru 6 . In such case when try to export to excel i get huge gaps as i am missing section 1 thru 2 in the report.

How do we avoid those spaces when exporting to excel,

What I could understand from your description is that each section is a list, and all 7 lists are within another list. You have set the visibility of these lists based on some parameter value.

If that is the case, try putting each list inside a rectangle and set the visibility expression for the rectangles, instead of the lists. That should resolve your problem.

-Aayush

OleDbCommand with Parameters

Hi,

I have application connected to MS Access DB using OleDB. When creating commands (Insert/Update/Select) I use OleDbParamater class to insert data into command. Examples :

Select ::

OleDbCommand select_cmd = new OleDbCommand("SELECT * FROM " + ObjectTable.TableName + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Update ::

OleDbCommand update_cmd = new OleDbCommand("Update " + ObjectTable.TableName + " SET " +
ObjectTable.idParent + "=@." + ObjectTable.idParent + " , " +
ObjectTable.idParentUnder + "=@." + ObjectTable.idParentUnder + " , " +
ObjectTable.License + "=@." + ObjectTable.License + " , " +
ObjectTable.Type + "=@." + ObjectTable.Type + " ," +
ObjectTable.Language + "=@." + ObjectTable.Language + " , " +
ObjectTable.Name + "=@." + ObjectTable.Name + " , " +
ObjectTable.Checksum + "=@." + ObjectTable.Checksum + " , " +
ObjectTable.VText + "=@." + ObjectTable.VText + " , " +
ObjectTable.VInt + "=@." + ObjectTable.VInt + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Parametes:: (Adding in separate method -> AddParameters(OleDbCommand command); )

command.Parameters.Add("@." + ObjectTable.idObject, OleDbType.BigInt).Value = this.IDUpper;
command.Parameters.Add("@." + ObjectTable.idObjectUnder, OleDbType.BigInt).Value = this.IDUnder;
command.Parameters.Add("@." + ObjectTable.Name, OleDbType.VarChar).Value = this.Name;
command.Parameters.Add("@." + ObjectTable.idParent, OleDbType.BigInt).Value = GetUpper(this.IDParent);
command.Parameters.Add("@." + ObjectTable.idParentUnder, OleDbType.BigInt).Value = GetUnder(this.IDParent);
command.Parameters.Add("@." + ObjectTable.License, OleDbType.BigInt).Value = this.License;
command.Parameters.Add("@." + ObjectTable.Language, OleDbType.BigInt).Value = this.Language;
command.Parameters.Add("@." + ObjectTable.Type, OleDbType.BigInt).Value = (int)this.Type;

command.Parameters.Add("@." + ObjectTable.VText, OleDbType.VarChar).Value = String.IsNullOrEmpty(this.VText) ? null : this.VText;
command.Parameters.Add("@." + ObjectTable.VInt, OleDbType.BigInt).Value = this.VInt;
command.Parameters.Add("@." + ObjectTable.Checksum, OleDbType.BigInt).Value = this.Checksum;

Question: Does the order of adding parameters to command matter? Because allways when the order of parameters added is diffrent from order in command text, I get weird Exceptions . I thought that the name matters, not the order, but it seems that system doesn't care about the parameter's name, it just picks next parameter in command.Parameters when putting values. How is it?Do you mean that if it could matter during the addition of the parameters ? It does not, as the .add method only puts the parameter in the collection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You know what? It does. And it is the only thing that matters (talking about OleDb) -> ORDER. Despite of using names (I could questionmarks instead of parameter's names). I've done this using Oracle DB and (of cource) namespace and everything worked fine, but OleDb looks to handle thing in it's own weird way. Example :

OleDbCommand selectCmd = new OleDbCommand( "Select * from Customers where CustomerName=@.name AND CustomerAge=@.age);

selectCmd.Parameters.Add("@.age", OleDbType.Integer).Value = 20;

selectCmd.Parameters.Add("@.name", OleDbType.VarChar).Value = "Michael";

this is NOT going to work !!! If the order of parameters added to command's parameters collection is diffrent from order of parameters used in command itself, it won't work.

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.sql

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.

OLEDB Datasources and parameters

I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.

Dick Campbell

I've never had any problems using parameters in OLEDB Sources. How are you defining the placeholders for your parameters? For OLEDB they should be a single "?".
A sample SQL statement would look like the following:
Select Col1, Col2 from MyTable where MyDate between ? and ?
The first ? would map to Parameter0 and the second would map to Parameter1.
Larry Pope
|||I am using ? as you suggest but I am calling an inline table valued function. The format is "select * from function(?,?,?) as function".

OLEDB - parameters

Hi.
I'm new in OLEBD and I have some problems when I'm creating query for
report. The way to uses parameters in query is with '?' instead of '@.'.
So, for each '?' symbol in the query the Report Designer creates one
parameter. But, what's happening if I need to use the same parameter in
the Query? I don't need that the Report Designer creates another
parameter. How can I resolve my problem'
Thanks.What you do is go into the mapping (click on the ..., parameters tab) and
map them to the same report parameter.
What I do first though is go to layout, report parameter menu and I change
the parameter name to something that means something to me. Then I go back
to the data tab, ..., parameters and change the mapping.
Also, you can delete any extra parameters from the report parameters menu
(layout tab).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gave" <vasgabriel@.gmail.com> wrote in message
news:1135264018.222907.10650@.f14g2000cwb.googlegroups.com...
> Hi.
> I'm new in OLEBD and I have some problems when I'm creating query for
> report. The way to uses parameters in query is with '?' instead of '@.'.
> So, for each '?' symbol in the query the Report Designer creates one
> parameter. But, what's happening if I need to use the same parameter in
> the Query? I don't need that the Report Designer creates another
> parameter. How can I resolve my problem'
> Thanks.
>|||Bruce Thanks for the immediate reply. I'm using RS 2000 with Visual
Studio 2003. When I create the query, like this:
Select ...
>From ....
Where (fieldName between ? and ?)
The report designer creates two parameters named 'Parameter1' and
'Parameter2'. You said that I need to map the parameter on parameters
tab. But I don't find where I can do that. Where I can map the
parameters? The only thing that I can do is go to layout and change the
parameter name on Report parameter menu. But I get the error:
The value expression for the query parameter '?' refers to a
non-existing report parameter 'Parameter1'.
Could you please be more explicit?
Thanks again.|||On the data tab (I said this in the previous posting but perhaps was not
clear).
On the data tab you should see a ... next to the dataset name. Click on the
..., go to the Parameters tab.
This is an important thing to be able to do. You can have your query
parameter based on an expression as well as a report parameter. Or you can
have several parameters based off of the same report parameter (which is
what you want to do).
Also, after changing the name of the report parameter this is where you need
to go to remap the query parameters to the report parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gave" <vasgabriel@.gmail.com> wrote in message
news:1135268131.600774.291770@.g14g2000cwa.googlegroups.com...
> Bruce Thanks for the immediate reply. I'm using RS 2000 with Visual
> Studio 2003. When I create the query, like this:
> Select ...
>>From ....
> Where (fieldName between ? and ?)
> The report designer creates two parameters named 'Parameter1' and
> 'Parameter2'. You said that I need to map the parameter on parameters
> tab. But I don't find where I can do that. Where I can map the
> parameters? The only thing that I can do is go to layout and change the
> parameter name on Report parameter menu. But I get the error:
> The value expression for the query parameter '?' refers to a
> non-existing report parameter 'Parameter1'.
> Could you please be more explicit?
> Thanks again.
>|||Thank you Bruce!
I resolved my problem.

OLE DB Source parameters ignored when subquery?

I am using an OLE DB Source with SQL command for the data access mode. I defined parameters, then added a complex query with subqueries. It seems like the parameters for a query are not being filled correctly (because I am getting too many rows returned). At one point I saw an error message the said something to the effect that the parameters would be ignored when there was a subquery in the SQL.

Can anyone shed some light on this?

Thanks,

Laurence

I don't know if that is true or not but regardless of that you should stop using parameters as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Jamie,

I have read your blog many times and it is really helpful. Thanks.

In this instance, the query is long and complex, and I might as well go to a script task as a string variable.

I am disappointed that parameters do not seem to be working correctly, thereby making my life more difficult.

Laurence

|||

Yeah, script task is a good option in those circumstances.

N.B. SP1 will have an expression builder attached to variables which will make it easier to build your expressions.

-Jamie

|||Would you mind sharing the long, complex query that didnt work for you?

OLE Db Source and Variables

I created a OLE DB Source, and created a sql command text.

The issue is with parameters, I have to use the '?' to identify.

What if I have multiple variables of the same type scatter around, how can I define that?

Somthing like this would be good...

Declare @.month
set @.month = ?

select blah,@.month where month=@.month

or

How could I use the ADO Syntax and just put @.month?

Thanks,

Mardo

Hi Mardo,

OLE DB only supports '?' style parameters. You can use ADO style named parameters if you define an ADO.NET connection manager. Ensure you set the ConnectionType property of the Exec SQL Task to ADO.NET in order to reference the new connection manager.

Cheers,

Nick

http://nickbarclay.blogspot.com

|||another solution would be to use variables and expressions to dynamically create the sql statement.|||Execuse my ignorance but I only see a OLE DB Data Flow Source. I understand that ADO.NET can used name paramters but I can only seem to use that in Data Flow Destinations.

Marty|||

Guys, my apologies for being a bit misleading.

The .NET connection manager can be used in conjunction with the Execute SQL task (in the control flow) - when using this type of connection manager you are able to use named parameters. The OLE DB source adapter (in the data flow) is specifically OLE DB i.e. it must use an OLE DB connection manager to access the DB. As Duane posted earlier you can also "use variables and expressions to dynamically create the SQL statement", other than that you will have to use "?" as parameter placeholders.

Cheers,

Nick

http://nickbarclay.blogspot.com

Ole DB Multi value named parameters problem.

I'm trying to use cascading report parameters using the mult-value select option. The first parameter is used as input for second parameter.

The first dataset called Employee,
select employeeid from humanresources.employee

Using SQL Server you can use multi named parameters to create your second dataset. For example,
select employeeid, loginid from humanresources.employee where employeeid
in (@.Employee)

However, I'm sadly not using sql server. I'm trying to do the same thing using an OleDB (non SQL Server) interface which doesn't support named parameters. So I used an expression. For example,

="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"
This works in Reporting Services if you do not have multi-value select option checked. Once you check the multi-value select option I get an error creating the second data set.
"Cannot set the command text, Error during process of command text of expression"
How do I do multi-value expressions.

Data providers do not support multi value parameters natively. Consequently, we have chosen to implement query statement rewrite for the managed SQL and the managed Oracle provider. Multi value parameters are also supported for AS 2005 data sources.

The OleDB standard in particular only allows single value parameters. It is not possible to write a "generic" multi value parameter query rewrite algorithm that works with any database server behind an arbitrary OleDB provider.

If you need multi value parameter functionality for a specific OleDB provider, you could write a custom RS data extensions which implements in particular the Microsoft.ReportingServices.DataProcessing.IDataMultiValueParameter interface. Then you could implement the query rewrite yourself in a way that is understood by your target database server.

Hope this helps,
Robert

|||Thanks for the education on Ole DB Providers. The testing I did validates what you have told me.

I'm tyring to do a work around. Is it possible to create a data set expression when multi-value parameters is selected. So I could do an expression query similar to this where I concatanate everything into the in portion of the query?
="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"

I believe Reporting Services would construct the query and would therefor the OleDB provider would not have to support multiple parameters. Thus a work around.

Todd|||If you change a report parameter to be multi value, the .Value property will return an object[] rather than an object. Hence you cannot just use regular string concatenation.

In your particular case, you may want to try the following expression:
="select employeeid, loginid from humanresources.employee where employeeid in (" & Join(Parameters!Employee.value, ", ") & ")"

Below is more information about using multi value parameters in reports. To access individual values of a multi value parameter you can use expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used e.g. for drillthrough parameters, subreports, or query parameters)

See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp

-- Robert