Translate NAV Filter expression into Transact-SQL

rsaritzky

Hi all,

I have a client who has a series of reports built in Native SSRS. They would like to enhance their reporting which requires a series of filter expressions. Here's a very brief example. I'm filtering on the Item Value Entry table:

Line 1 represents a group of transactions with the following filters:
Gen Prod Posting Group = RK or RET-RK and
Gen Bus Posting Group = DOMESTIC and
Customer Posting Group = DEALER-MUS or DEALER-MR

Line 2 represents a different group:
Gen. Prod Posting Group = RKNROLR or Returns-RR and
Gen Bus Posting Group = DOMESTIC and
Customer<>C000628

I realize that it may not look logical, but I'd like to focus on the "how" and not too much the "why"...

I had an idea of allowing the user to store a series of filter expressions in a NAV table, which would be passed/used by the SSRS reports to filter the query. I’d define a table that allows me to specify:

• Group Code
• Gen Prod Posting Group Filter
• Gen Bus Posting Group Filter
• Customer Posting Group Filter
• Customer Filter

They would enter normal NAV filter expressions, e.g. Line 1 above would be:
Gen Prod Posting Group Filter = RK|RET-RK
Gen Bus Posting Group = DOMESTIC
Customer Posting Group = DEALER-MUS|DEALER-MR

Then, my SQL Query or stored proc that builds the SSRS report would read the record stored in NAV above and "translate" it to a properly-formatted where clause, e.g.

WHERE
[Gen Prod Posting Group] IN ('RK','RET-RK') AND
[Gen. Bus Posting Group] = 'DOMESTIC' AND
[Customer Posting Group] IN ('DEALER-MUS','DEALER-MR')

Has anyone built - or seen - any Transact-SQL or C/AL code to parse out NAV filter expressions into Transact-SQL?

Thanks