ReportNet: complex filter problem (for the gurus!)

[RNRM]

Hi,

Environment: Sybase data warehouse - Framework manager / Reportnet in front of that and Windows XP Pro on my desk PC.

I am rewriting a report that is based on an application which allows the user to do some pretty cool stuff - basically: define their own filter (as well as using all the standard prompts and things).
They have a screen where they can select certain columns from tables in the database then put different operators between them like >; <; >=; <= etc.

Ideally what I need to do is create a similar interface but its a little outside the scope of ReportNet (Query Studio would be perfect for this but not in our environment unfortunately) so boiling the problem down to the most basic level: I need to let the user enter a string into a text entry prompt (eg: 'colA >= ColB') then I need to pass that string as the final statement in the generated query's filter.

Any ideas will be greatly appreciated - I hope my explanation is clear enough.

TIA
M

 

[KingCrab]

Stored procedure to parse it all out. I dont think you will ever get it to work just using RS

 

[almeids]

OK, RNRM, you asked for it...I don't think this will prove practical but it's possible IF you have a small finite number of columns to deal with and IF you don't mind doing a lot of typing:
(1) Parse your prompt value into left column, operator, and right column string data items (LC, OP, RC) - this in itself is a challenge but can be done with subtr, instr (Oracle) or perhaps the RS equivalents. You will either need a disciplined user or parsing functions that can handle variable spacing in the entry. You may have to calculate separate positional and/or length data items to facilitate the parsing, I had to in my experiment just to get it to run without crashing.
(2) Create left and right filter column (F_LC, F_RC) data items using case statements which examine LC and RC values, respectively, and set the value to the appropriate database column from your model. In the ELSE clause you can set them to the value of LC or RC if you want to allow constants.
(3) Create a filter something like: (OP='=' AND F_LC=R_LC) OR (OP='>' AND F_LC>R_LC) OR (OP='<' AND F_LC<R_LC)... and so on for as many operators as you'll allow.

Simple, no? No. Also very temperamental and you're likely to run into inconsistent data type issues in the real world. Good luck!