how to pass a field as a multiple parameter to subreport?

[joksanen]

Hello,

Here's another CR (8.5) newbie getting really frustrated while trying to pass multiple discrete values from main to subreport for record filtering.

There's a couple Oracle fields in the mainreport and I need to pass one of these fields (having 100-500 rows depending on the parameter given to mainreport, 5-9 char strings) for record selection formula of the subreport to chart some data that resides on sql server 7.

No matter what I try, I keep getting only the one and first, single value instead of the array I need.

I've read hundreds of articles from seagate knowledge base, usenet, several forums etc. and played with parameters, formulas, subreport linking, sections. No help, I'm lost.

Any help or advice would be greatly appreciated!

 

[synapsevampire]

I gather that you can't just place the graph in the report footer and you're done.

Otherwise, perhaps you can just add the same params/record selection criteria into the subreport? Passing 500 rows of data to be used in a where clause (record selection criteria) doesn't sound very efficient.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[joksanen]

There's two different data sources in the report (oracle+mssql). Tried connecting tables of these two db's (ignoring cr's warnings) in same report, - no can do, it just broke all those oledb and odbc drivers. So I don't know how the same selection criteria could be used.

Idea of this simple report is that mainreport asks for a factoryID param which filters productIDs (this is Oracle).
I need to pass these productIDs of mainreport into the subreport where I could for example collect them in formula string (like:
("productID(0)", ...,"productID(499)&quot
to be used in the record selection of the subreport(querying mssql).

It may not be the most elegant solution, but works good enough. Tested that ugly query manually.

I obviously must have missed something. I still don't know how to get those productIDs from main report to sub reports record selection formula. So help me, please!

 

[synapsevampire]

I tried creating a shared array and using it in a subreport record selection without success (rather it wouldn't allow an "IN", and a conventional variable in CR won't support that length. Perhaps someone else here has a solution, I can't think of one within Crystal.

Anyway, I would address this on the database side. Perhaps build a parameterized SP which returns the main report data and also builds a temp table to house the graph data, or perhaps return everything you need at once.

You could do this from the Oracle database too, or link the tables in an MS Access database and report off of it, which is about the only thing I use Access for these days.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

I have had better luck linking Oracle to other DB types using the native Oracle driver. I can at least link to sybase.. The only time I HAVEN'T been able to directly link Oracle to another database is when my data from Oracle comes from a stored procedure. I don't have any directly linking data from SQL server (the sybase server sort of sits inbetween the two). I will be soooooooo happy when we get everything to at least one server type!

Lisa

 

[joksanen]

Thanks for support.

Perhaps I could just generate a few shared arrays, but there must be better way around this...

I'm afraid this is a stupid question, but why only one value comes from main to sub when I link to this productID field.

 

[chelseatech]

Another solution is to use the Join function in a formula to join all your product Id's into a string - pass the single value to the subreport, and manually adjust the record selection to
{subtable.field) = split({?linkedfield},",&quot

Check out Join and Split - they became available in CR8.0, so you can use it with your CR8.5 reports. Sadly not available in prior versions. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm