Filter stored procedure data set results

What is the most efficient way (short of changing the stor proc itself) to filter the results of a data set that uses a stored procedure as its source. I need to filter the rows being returned by the proc based on a combination of a report parameter and values of specific columns in the result set.

For example, I only want to keep rows where....

(param1 = "Y" and row.col1 = "ABC") or (param1 = "N" and row.col1 = "notABC").

Thanks,
Steve

Find more posts tagged with

Comments

bhanley

The solution is there out-of-the-box. Simply cvreate your data set to load the results of your Stored Procedure. From that point, create a filter in the Data Set editor. When creating a filter, you can click on the expression editor. The expression editor will allow you to use report parameters and data set values in any combination you need.

Good Luck.

gslane

I actually tried that route, but can't quite figure out the syntax / usage of the expression builder to specify the logical relationship between the conditions.

If I put the conditions in one expression, such as....

(params["repoflag"].value!="Y" && params["repoflag"].value!="N") ||
(params["repoflag"].value=="Y"
&& row["nm_type"]=="REPO"
&& row["nm_key"].substr(0,2)=="T ") ||
(params["repoflag"].value=="N"
&& !(row["nm_type"]=="REPO"
&& row["nm_key"]=="T "))

then what do I specify as the operator and "value1", "value2"?

If I build the conditions 1 at a time, how do I link them logically, ie, the correct "and" and "or" relationships?

Thanks for your help and patience...... as you can tell, I'm a novice.

Steve

bhanley

You do not build out filters in one long string of staments as you have illustrated. Instead you build out a group of filters each one targeted at a specific field. I do not think think the filter approach is what you need for two reasons. First, the linking of the various filters would be difficult to achieve. Second, it would be a rather inflexible solution that would be difficult to maintain. 
 
Instead, you should consider modifying the query itself at run time. This will provide the best [possible performance as the filtering you are looking for will be done at the database level (no other filter would be faster) and you will only transmit the data you need back to the report itself. 
 
You can modify the beforeOpen script on the data set to evaluate parameters and determine your best WHERE clause, then set that WHERE clause on your query using: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(params["repoflag"].value != "Y" && params["repoflag"].value != "N"){
this.queryText = this.queryText + "Some WHERE Clause";
}
else if(params["repoflag"].value=="Y"){
WHERE = "nm_key = 'T' AND nm_type = 'REPO'";
this.queryText = this.queryText + WHERE;
}
...
</pre>
 
Check out this post for more details: 
 
<a class='bbc_url' href='http://www.birt-exchange.com/forum/designing-birt-reports/13580-birt-scripted-query-question.html#post42438'>http://www.birt-exchange.com/forum/designing-birt-reports/13580-birt-scripted-query-question.html#post42438</a>

bhanley

I just realized you are filtering a Stored procedure (Sorry, I was reading the post too fast...). As such you cannot modify the Where clause. 
 
My recommendation is to either craft a stored proc. that can accept arguments (if that is possible). Short of that, try and target your filters to each field you want to filter on. 
 
Bear in mind you can build out the field used to filter on dynamically. So the filter creation dialog gives you an expression editor. You could build an expression like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(params["repoflag"].value=="Y"){
row["nm_key"]
}
</pre>
 
Then in the "Value" for the filter you could add "Repo". The logic in the field selection can be as complex (and contain as many cases) as you need 
 
Sorry if I added to your confusion at all.

gslane

I agree..... changing the proc would be the way to go, but it's used in a number of different places and I wanted to minimize impact. I may end up writing a new one and just clone the logic from the old one.

But first, I'll try building multiple filters as you suggested.

With multiple filters, I assume they act as though they have an "or" between them?

Thanks.

bhanley

No, the filter conditions are joined together using an AND operator. 
 
I did come across a reference to implementing what you want like this: 
 
1) create a filter on the Data Set. 
 
2) Put all your conditional logic in the Expression editor: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
(params["repoflag"].value!="Y" && params["repoflag"].value!="N") ||

(params["repoflag"].value=="Y" && row["nm_type"]=="REPO" && row["nm_key"].substr(0,2)=="T ") ||

(params["repoflag"].value=="N" && !(row["nm_type"]=="REPO" && row["nm_key"]=="T "))
</pre>
 
3) For the Operator, select "Is True" 
 
This will give you values that meet the OR'd filter condition that you set in the expression. 
 
I think that will do it. Let me know.

gslane

I was pretty close with one of my first tries...... I did that but put just "true" as the value. I'll try "is true" and see what happens.

thanks.

bhanley

The operator I am referring to is a drop-down for selecting the value, so you should be safe in selecting the proper value. See the attached screen shot if you are not finding it.

Once you select "Is True" you will not need to enter values for Value 1 and Value 2.

gslane

The code above worked with operator "is true".

Thanks for your help.