Passing Formula to Subreport to use in SQL -- bad idea?

[jollyreaper]

I'm calculating the current accounting period to use in several sub-reports. The slow and dirty way to do this is to just put that formula in every sub-report and things will work fine. I wanted to make this quick and elegant by calculating the formula once in the main report, then passing it to the sub-reports. However, this is generating errors.

Formula from main report (Formula called CurrentPeriodFN):

Shared PeriodMonth, PeriodYear, CurrentPeriod As Number

PeriodMonth = Month (CurrentDate)
PeriodYear = Year (CurrentDate)
CurrentPeriod = PeriodMonth + (12 * PeriodYear) - 1

formula = PeriodMonth + (12 * PeriodYear) - 1

(Note: I completely suck at Crystal. I'm still conflustered by the differences between basic and crystal syntax.)

As I understand it, I cannot make the formula CurrentPeriodFN but I can declare a variable as shared and do the same thing.

So, according to the books, the way to make that variable value available in the sub-report is to define it there.

So, in the sub-report, formula is called CurrentPeriodFN and contains:
Shared CurrentPeriod As Number
formula=CurrentPeriod

For some reason this isn't working. Either a value of zero is getting passed or nothing is passed and null defaults to zero in the whacky world of Crystal.

What's more, if I include that value in a SQL statement, I get more than just a SQL error.

Here's the formula from the editor.

{GLACCTS.AN} in "40010" to "40090" and
{GLACCTS.AN} <> "40030" and
{GLACCTSA.CAP} = {@CurrentPeriodFN}

"The formula cannot be used because it must be evaluated later"

There's a whole KB article about it.

http://64.233.187.104/search?q=cach...1639.asp+cannot+be+used+evaluated+later&hl=en

So, is what I'm describing even doable or should I default to the slow and dirty way instead?

 

[Madawc]

If you've correctly calculated a value in the main report, you ought to be able to pass it to the subreport. Edit > Subreport Links in Crystal 10. Find something you can link it to - this will generate a command in the subreport record selection, but this can be edited or removed.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jollyreaper]

Well, my main frustration is a lack of good documentation. According to the books I read, declaring variables as shared should make them available to the subreports, assuming you shared them there. I have passed database values to subreports before (i.e. you need to know the customer number so you put that in the link and pass it along.) So, you're saying I need to do that for the variables as well?

What kills me here is that there are two different syntaxes you can use and the errors are all so cryptic.

This is something I'm encountering on another subreport on this same project. Every house in the database has a construction stage associated with it. I need to see what all the statuses are and report on it.

I know that SQL can do a select count where it just sums up the returns. Not sure if I can make that work in Crystal so I tried doing this instead: pull all records, use a formula to evaluate the stages and incriment variables as appropriate.

SELECT "CCLIENT"."ADKEY", "CCLIENT"."IDCSTAGE", "CDVLP"."LONGNAME"
FROM "CCLIENT" "CCLIENT", "CDVLP" "CDVLP"
WHERE ("CCLIENT"."IDCDVLP"="CDVLP"."IDCDVLP") AND "CDVLP"."LONGNAME"='East Lake Village'

And here's Mr. Formula.

Dim permit, slab, framing, cabinets, otherstage As Number
if {CCLIENT.IDCSTAGE} = "1" then permit = permit + 1 elseif
{CCLIENT.IDCSTAGE} = "2" then slab = slab + 1 elseif
{CCLIENT.IDCSTAGE} = "7" then framing = framing + 1 elseif
{CCLIENT.IDCSTAGE} = "9" then cabinets = cabinets + 9 else
otherstage = otherstage + 1
end if


My thinking was once I got the evaluation working, I'd stick formulas in the report footer to print the results .... oh wait....[works on report] Ok, I was suffering from a bout of supreme dumbassery. USE THE SUMMARY FIELDS!!! Ugh. I suck. Problem fixed, at least for this one.

 

[Madawc]

Sorry, that's not Crystal, or not Crystal I recognise.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jollyreaper]

Well, it's of no matter, I got it working a different way. I'm just having a devil of a time getting basic/crystal syntax down. Thanks anyway!