Front end filter for users to manipulate the result set

[jtpw1]

Infomaker has a filter function where, after the report is generated, the user can apply sql like conditions on values contained in any (or all) of the columns that were retrieved by the report. For example in a report that lists the daily sales for all staff from 100 stores in all States for a given date range, the user can further refine the result set by applying a filter like: (staff_lname like 'T%') and (sales_value > 500.00) and (state_id in (21,56)).
Is there a way to do this type of dynamic/user driven filtering on all available report columns in BIRT?

[speckyspooky]

This isn't a standard question on BIRT.
This is a question how your application work at the end and you can use mechanism of BIRT.
The main point is that BIRT doesn't have a user-accesss handling and you need a way to get the information which user start the report.

The importanst step is to have your user on the context then I see 2 options to solve it:

1. BIRT data set manipulation
2. SQL-mechanis

On both implementations is the question how many different conditions do you have(?)
Because this is important of the implementation.

Option 1) - Idea, change the filter condition based on the user name

The SQL-query can be manipulated in the "beforeOpen()" of the data set. You will get the query with the "this.queryText".
And here you can use a kind of replacement. But for that you have to prepare your statement accordingly, e.g. your query:

SELECT sales FROM table_sales
WHERE
store_id in (5000, 6001, 7880)
/*[-replacement_user_based_beforeOpen-]*/

The patern /[-replacement_user_based_beforeOpen-]/ should be replaced in the "beforeOpen()"-method with the uset dependend condition.

beforeOpen() with the replacement mechanism (demo):

var sqlString = this.queryText;
var addWhereClause = "";
if (paramUser == "Max") {
   addWhereClause = " and (sales_value > 500.00) ";
}

sqlString = sqlString.replace(/\/*[-replacement_user_based_beforeOpen-]*\//g, addWhereClause) = this.queryText;
this.queryText = sqlString;

Option 2) - Idea, give the user name like a parameter to the SQL statement and create there the filter condition

This way is competely based on the SQL statement and the only BIRT step is to set the user name (for example) with a parameter to the data set. The you can handle the conditions completely on SQL level.
I use this way by standard combined with the grant rules of the most applications.

I don't know if you will use MS SQL Server or Oracle. But I have done it in both versions.
The main question is here how many condition will be needed to define the way to work with fixed conditions or work with own created condition tables (may temporary tables).

MS SQL Server example "fixed conditions" (I would use if the condition count isn't so much):

DECLARE @param_user_name NVARCHAR(100) = ?;

SELECT sales FROM table_sales
WHERE
store_id in (5000, 6001, 7880)
AND (
  @param_user_name like 'T%' and (sales_value > 500.00) and (state_id in (21,56))
OR
  @param_user_name like 'B%' and (sales_value <= 100.00) and (state_id in (20,30))
OR
...
OR
  @param_user_name like 'S%' and (sales_value > 1000.00) and (state_id in (25,60))
);

So I hope you got some ideas of the options. I have further ideas but it would be to much and
I don't know exactly your data model and your way of the user-name-handling.

[hvbtup]

Is there a way to do this type of dynamic/user driven filtering on all available report columns in BIRT?

I think the short answer is "No, unless you provide it".