dynamic subquery as filter in Crystal 10

[ajleonhard]

Hi, my apologies for the long explanation. I am attempting to create a integration point between my application and our customers' Crystal reports, which would be published to Crystal Enterprise 10. The goal is to allow users to filter records in the application, then pass the filter criteria to Crystal so that when they run a report they see the same recordset as they had in the app.

This filter information is provided to me from the app in the form of a complete where clause. Normally, I would just split the where clause into expressions, create selection formulas using the expressions, and pass them to the report using the "SF" URL Command. Unfortunately, the where clause may also include a subquery, for example:
WHERE (EXISTS
(SELECT EMPLOYEEID FROM LABOR
WHERE (CLASS = 'EXEMPT') AND (EMPLOYEEID = PERSON.EMPLOYEEID))

I don't believe a subquery can be used in a selection formula, is this correct? I don't think views or stored procedures are an option either because of the dynamic nature of this subquery - I don't know in advance what the specific SQL will be.

I can use the RAS SDK instead of URL Commands, but even then I'm not sure if this is feasible. From what I can see, the only way to include a subquery in the SQL of a Crystal 10 report is by using SQL Commands. One thought I had was to create a full select statement out of my where clause, for example:
SELECT PERSONID FROM PERSON WHERE
(EXISTS (SELECT EMPLOYEEID FROM LABOR
WHERE (CLASS = 'EXEMPT') AND (EMPLOYEEID = PERSON.EMPLOYEEID))
Then I could create a Command Table with that SQL, add it to the report, and join it to the existing PERSON table in the report with an inner join, using the join to filter the records. But I have seen some posts expressing concern about using SQL Commands in conjunction with regular database tables. Are there other problems with this approach? Any other ideas as to how to accomplish this?

BTW, I won't have any information about the report SQL except that it will definitely contain the table required by the subquery (PERSON in my example). Also, this solution should work on both Oracle and SQL Server (although syntax can be different on each.)

Thanks for any ideas,
Angela

 

[synapsevampire]

Perhaps you can just build the entire SQL statement from within the app, generate the recordset, then attach the recordset to the report?

check:

http://support.businessobjects.com/library/kbase/articles/c2012982.asp

Note that if refers you to examples.

-k

 

[ajleonhard]

Thanks for the suggestion. This sounds like a promising option but I'm unsure about one element. I won't know the SQL of the report. Can I get a report's SQL using the API? I've looked though the docs but I can only find methods for getting individual report components such as filters, tables, etc. Is it at all feasible to manually generate a select statement from those elements?

Angela

 

[pelajhia]

But I have seen some posts expressing concern about using SQL Commands in conjunction with regular database tables. "
That is interesting; can you elaborate?

I think you are right about sql commands being the only way, though. Have you checked out:

http://www.tek-tips.com/viewthread.cfm?qid=914555