Datasets

Problem: I need to add filters on a Dataset. The current dataset is based on
a stored procedure that I would prefere NOT to touch.
Question: Is it possible to build a new dataset based on the first dataset?
(This would inable me to filter on the data output). Or are there other
suggestions for a solution to this problem.
Thanks.
Regards
JonasAs I am going on holidays can you please respond to
terry.bilsborough@.Alcan.com.
Thanks.
Regards
Jonas Larsen
Alcan Engineering
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Problem: I need to add filters on a Dataset. The current dataset is based
on
> a stored procedure that I would prefere NOT to touch.
> Question: Is it possible to build a new dataset based on the first
dataset?
> (This would inable me to filter on the data output). Or are there other
> suggestions for a solution to this problem.
> Thanks.
> Regards
> Jonas
>|||Yes it is possible to add a filter to a data set. The data set filter
functionality is located on the dataset dialog : Filter tab. Additionally
all data regions (lists, tables, matrix, and chart) support this
functionality.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23VWlAv%23YEHA.384@.TK2MSFTNGP10.phx.gbl...
> As I am going on holidays can you please respond to
> terry.bilsborough@.Alcan.com.
> Thanks.
> Regards
> Jonas Larsen
> Alcan Engineering
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Problem: I need to add filters on a Dataset. The current dataset is
based
> on
> > a stored procedure that I would prefere NOT to touch.
> >
> > Question: Is it possible to build a new dataset based on the first
> dataset?
> > (This would inable me to filter on the data output). Or are there other
> > suggestions for a solution to this problem.
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>sql

Dataset Filtering Not Working

I am trying to filter data at the dataset level after it is returned.

However, the filters tab on in the Dataset window does not appear to work correctly.

I have tried the following expressions:

Expression Operator Value

=(Fields!IdLog.Value = Parameters!Id_Log.Value) = =True

=Fields!IdLog.Value = =Parameters!Id_Log.Value

When I rerun my dataset query, the entire dataset is returned, instead of the filtered dataset. This same behavior occurs if I try to run the report itself.

I do not understand why the filtering does not occur.

Any help would be greatly appreciated.

To get maximum performance with filtering its the best to build the parameter into the SQL-Query:

="select your_colums from your_table where ID='" & Parameters!Id_Log.Value & "'"

I never tried something different, because its waste of resources to retrieve 1000records from a database and filter out 999 of them..

|||

BenniG,

Thank you for your comment. However, I am try to filter the results that are returned from a Web Service, and therefore, I cannot specify the SQL Statement used to query the database.

|||

I tried the filter-tab:

=Fields!ID.Value = =Parameters!ID.Value

works if both fields have the same datatype, if one is a string and one an integer I get an error.. Maybe you have to change the datatype for the Parameter?
I think the filter has no effect on the !-Icon in datasets, but in my case it worked in preview mode..

Dataset and Filters

Hi !

I have a quick question. If i have a report that show invoice by suppliers. Sometime i would like the report to diplayed all suppliers and sometimes for only one suppliers. Can i do this using the filters property of the dateset? Or did i have to create 2 reports. One for the case All suppliers and the other for the case Only one suppliers?

Thank and sorry about my bad English ^_^
Just play with the parameters
2 ways I can think of
1. Add a "ALL" parameter above all suppliers
(need to play with your DataSet query with UNION)

2. Use the multi-value option provided in report parameter
(it'll end up passing into dynamic sql as .... WHERE supplier IN (@.parameter)|||Thanks for your answer. I"ve tried what you told me and it works !

Thanks again !

DataFlow Task & Filters

Hi,

I am getting data from an external source. External data has a column called "Type". I have a variable in my package which contains the list of types as shown below:

Filtered_type_List = 2,4,8,10,11

If this variable(Filtered_type_List) is blank, then I need all the data from the external source and if it is not blank then I only need the records matching to his list. How can I implement this in DataFlow Task?

Thanks

You could do this in an expression. Something like:

"SELECT * FROM MyTable " + (LEN(MySSISVariable) != 0 ? "WHERE MyColumn IN (" + MySSISVariable + ")" : "" )

That expression will (I think) add a WHERE clause if the length of the string inside the variable (which I have called MySSISVariable) is not zero.

HTH

-Jamie

|||

Hi Jamie,

Where should I put this "Select" statement,

1. Source using SQL Command as variable using OLE DB Source or

2. Lookup transformation

Thanks

|||

OLE DB Source. Set it to 'SQL Command from variable' and paste the expression that I provided above into the variable expression. The variable will require EvaluateAsExpression=TRUE.

-Jamie