Subreport, how to pass a parameter from parsed data 1

[teamssc]

Crystal 11 and SQL Server 2005.

I've got 2 SQL databases. I need to pull data from both and drop into a Crystal Report.

In SQL.DB1 there is a text field formatted like this "The Quote for Jefferson county is #ABCQ12345 for 3 speakers". I need the quote number "#ABCQ12345" to match and pull data from SQL.DB2. I can successfuly parse "#ABCQ12345" with a formula called "quotenum". Now I need to select multiple records in SQL.DB2 field "docid" based on "quotenum" (#ABCQ12345).

I've written this with a main report body that displays data from SQL.DB1 and a SubReport that should pull the matching data from SQL.DB2.

It seems I've read here that I can't use a formula result to pass as a parameter to a SubReport. What are my options?

Thanks

 

[lbass]

Yes, you can link on a formula. Are you not seeing it in the field list in the main report on the subreport linking screen?

-LB

 

[teamssc]

The forumla field does show in the linking screen, and yes I can link to it, however the subreport doesn't return any data. I've read somewhere that the formula is the last thing calculated in a report so the subreport runs before the calc is done, thus no data is passed to the subreport, because it's too late. ????

 

[Turkbear]

Hi,
Where is the sub inserted? It will need to be after the main report, and its formula, have had a chance to run.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

donedds, That is not true. Turkbear is probably correct that this has something to do with the placement of the sub, so please identify in what section it is located. It should probably be in a group section based on the formula.

You should also check how the formula and the corresponding field are displaying to make sure they match.

-LB

 

[teamssc]

Thanks for your continued help. I wish I understood this better.

I guess I thought the subreport would go in the details section... Yes? No?

 

[lbass]

You need to check how the fields are displaying to see if they match. You can run the sub separately to do this.

Also check the length of each field by using a formula like this:

len({@linkingfield})

-LB

 

[teamssc]

lbass, thank you for your persistance and patience...

I had such a mental block that I couldn't pass a formula to the subreport I was haveing trouble seeing the obvious.

When you asked about how the fields are displaying that is is where I caught the error. In starting to write my explanation I realized I was parsing the quotenum to #JAVQ12345 and the DocumentHeadersID was just JAVQ12345 (no # sign). Once I rewrote the formula I got the correct results.

Wrong
if instr({CAL.REF},"#") > 0 then
(ltrim(mid({CAL.REF},instr({CAL.REF},"#")+0,10)))

Corrected: I had to change is to drop the leading # sign
if instr({CAL.REF},"#") > 0 then
(ltrim(mid({CAL.REF},instr({CAL.REF},"#")+1,9)))

Final answer, yes you can pass a formula to a sub-report, if you have half a brain... ;