Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula displays zero when placed in report header 3

Status
Not open for further replies.

sdzlkds

Technical User
Oct 20, 2005
41
GB
I have a report where I want to show a chart that displays the KPI target met yesterday, and the average KPI target met over the last 3 months

No problems with the 3 month average measure, but the formula to get yesterday's KPI works in the report footer, but not in the report header, where the chart is placed.

The formula I an using is

if {dsplit.row_date} = currentdate -1 then
100*(Sum({dsplit.acceptable},{dsplit.row_date}))/sum({dsplit.callsoffered},{dsplit.row_date})
else 0

is there any way round this?

Thanks
 
The problem is that the sums are calculated as the report processes, so the data is not available yet in the report header. If you must have the chart in the header, then you'll have to put the chart into a subreport and place the subreport in the header. It will take your report longer to process as it will have to query your data twice, but it's then only way to get the format you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.
 
its just luck that formula works in footer.

Formula is based upon a record value

if {dsplit.row_date} = currentdate -1 then 100*(Sum({dsplit.acceptable},{dsplit.row_date}))/sum({dsplit.callsoffered},{dsplit.row_date})else 0

In your footer the last record meets the condition

{dsplit.row_date} = currentdate -1

where your first record does not.

If you want it to work in Header you can restrict data in select expert using

{dsplit.row_date} = currentdate -1

Ian

 
If you can't use the selection formula as Ian suggests, you could create conditional formulas like this:

//{@yesterdayacceptable}:
if {dsplit.row_date} = currentdate -1 then
{dsplit.acceptable}

//{@yesterdaycallsoffered}:
if {dsplit.row_date} = currentdate -1 then
{dsplit.callsoffered}

Then the formula becomes:

sum({@yesterdayacceptable},{dsplit.row_date})%
sum({@yesterdaycallsoffered},{dsplit.row_date})

Your original formula said show me the percent for all records if the current record = yesterday. I think you meant, show me the percent for yesterday.

-LB
 
This is now sorted - thanks all for you assitance
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top