We help IT Professionals succeed at work.

Crystal Returning Values Formula

sochionnaitj
sochionnaitj asked
on
hI, I am building a report off a view that has a value for each period (See attached for Layout) There are 12 lines 1 per period, I am putting a filter on the report for users to select a required period.  However, no mater what period they select they also want to see the Full Year budget which is held in DSYTD ( A running total - with period 12 holding the Full Year Budget).  Is there any way of showing the FY Budget with a period value? EE-TABLE.doc
Comment
Watch Question

Top Expert 2011
Commented:
You can use SQLExpression to get the value for the same GLCode and Period=12 (or 13)
SQLExpression will be executed as a separate query so this can be expensive call if you use it for multiple GLCodes, If you use multiple GLCodes it may worth to create a new view which will return the full year budget  as a separate column and to base report on this new view.

something like this:
SELECT ev1.GLCode,ev1.Period,ev1.DS, ev1DSYTD, ev.DSYTD as FY
from <existing view> ev1
       INNER JOIN ( select GLCode, DSYTD FROM <existing view> ev1 WHERE  Period=12) ev2 ON ev1.GLCode=ev2.GLCode

Author

Commented:
Thanks