WHERE Clause in formula from field defintion file and database table

[smartstream]

I am passing a recordset from Visual Basic to Crystal Reports developer edition 10. In the Crystal Report itself I want to not only use the recordset (i.e. field definitions only) but pull a value from another table (connected to say SQL Server or Oracle database) based on some conditions, such as MAX date and where the currencies are the same (of the recordset and the other table). Is it possible to do this? I.e. to use the recordset from Visual Basic as the WHERE clause against the other table? Can I have another table in a database and a field defintion source specified in the same report? I basically want to convert a value on the report (from field definition file) based on a value in this other table which is where it has the MAX date for the entry and the same currency?
Hope this makes sense. Thanks

 

[synapsevampire]

It sounds like you're attempting to build dynamic SQL based on table values.

I would think that you'd be better served to just build the dynamic SQL from within VB, pass it to the database, and when you've created the final record source, pass it to CR.

Of course the type and version of CR is important.

This whitepaper references whitepapers for passing an ADO recordset using CR 8 (search the BO site for your version if required):

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

-k

 

[smartstream]

It is supposed to be dynamic but this cannot be added to the VB code as this is suppose to be dynamic for the user. The only thing the user can pass across is the first record-set. This information would be an extra lookup required that would be added onto the report by the user and not a set report generated from VB. Is this possible?

 

[smartstream]

Oh its Crystal Reports Developer Edition 10

 

[lbass]

Not sure, but you might be able to add the second table to a subreport which is linked to the main report by currency. You could then limit records to the maximum date within the subreport. You didn't really specify what the user is choosing, but potentially a parameter could be set up to respond to this need, either in the main report and linked to some value in the subreport, or in the subreport itself. You would then need to use shared variables to use values from the subreport in formulas with values from the main report.

-LB