|
Hi,
I am hoping there is someone that can help me as this is something that I cannot seem to be able to get to work 100%
I have a Variance report that Calculates the differences between 2 amounts, but I seem to have come across a few snags:
1. The report is pulling info from the following Tables:
tblCoinCount
tblMachines
tblMeters
tblSites
2. I have the following Group Name Field:
tblSites.Allocated
tblSites.SiteName
tblMachines.AssetNo
3. I have the following Parameter Fields:
Start - Date - Discrete Value
End - Date - Discrete Value
4. I Have the Following Record Selection Formula:
{tblMeters.PDate} In {?Start} to {?End};
5. I have the following Formulas:
@Coin
WhilePrintingRecords;
NumberVar CTotal :=0;
NumberVar CCO;
NumberVar HPO;
NumberVar HFO;
NumberVar CSO;
if {?End} - {?Start} > 1 then
Ctotal := ((CCO - HPO - HFO - CSO)*100) else
CTotal := (({tblCoinCount.Closing} - {tblCoinCount.Handpay} - {tblCoinCount.HopperFill} - {tblCoinCount.StartCoins}) * 100)
@CoinCE
WhilePrintingRecords;
NumberVar CCE;
if {tblMeters.PDate} = {?End}
then CCE := {tblCoinCount.Closing}
@CoinCS
WhilePrintingRecords;
NumberVar CCS;
if {tblMeters.PDate} = {?Start}
then CCS := {tblCoinCount.Closing}
@CoinEDiff
WhilePrintingRecords;
NumberVar CCS;
NumberVar CCE;
NumberVar CCO:=0;
CCO := CCE + CCS
@CoinHFDiff
WhilePrintingRecords;
NumberVar HFS;
NumberVar HFE;
NumberVar HFO:=0;
HFO := HFE + HFS
@CoinHFE
WhilePrintingRecords;
NumberVar HFE;
if {tblMeters.PDate} = {?End}
then HFE := {tblCoinCount.HopperFill}
@CoinHFS
WhilePrintingRecords;
NumberVar HFS;
if {tblMeters.PDate} = {?Start}
then HFS := {tblCoinCount.HopperFill}
@CoinHPDiff
WhilePrintingRecords;
NumberVar HPS;
NumberVar HPE;
NumberVar HPO:=0;
HPO := HPE + HPS
@CoinHPE
WhilePrintingRecords;
NumberVar HPE;
if {tblMeters.PDate} = {?End}
then HPE := {tblCoinCount.Handpay}
@CoinSDiff
WhilePrintingRecords;
NumberVar CSS;
NumberVar CSE;
NumberVar CSO:=0;
CSO := CSE + CSS
@CoinSE
WhilePrintingRecords;
NumberVar CSE;
if {tblMeters.PDate} = {?End}
then CSE := {tblCoinCount.StartCoins}
@CoinSS
WhilePrintingRecords;
NumberVar CSS;
if {tblMeters.PDate} = {?Start}
then CSS := {tblCoinCount.StartCoins}
@CXDMetEnd
WhilePrintingRecords;
NumberVar CXDMetEnd;
if {tblMeters.PDate} = {?End}
then CXDMetEnd := {tblMeters.SoftCxdCredits}
@CXDMetStart
WhilePrintingRecords;
NumberVar CXDMetStart;
if {tblMeters.PDate} = {?Start}
then CXDMetStart := {tblMeters.SoftCxdCredits}
@WhilePrintingRecords;
NumberVar CXDMetStart;
NumberVar CXDMetEnd;
NumberVar CXDVar :=0;
CXDVar := CXDMetEnd - CXDMetStart;
@End Date
cdate (MAXIMUM ({tblMeters.PDate}))
@InMetEnd
WhilePrintingRecords;
NumberVar MeterEndI;
if {tblMeters.PDate} = {?End}
then MeterEndI := {tblMeters.SoftMeterIn}
@InMetStart
WhilePrintingRecords;
NumberVar MeterStartI;
if {tblMeters.PDate} = {?Start}
then MeterStartI := {tblMeters.SoftMeterIn}
@InVariance
WhilePrintingRecords;
NumberVar MeterStartI;
NumberVar MeterEndI;
NumberVar VarI :=0;
VarI := MeterEndI - MeterStartI
@JPMetEnd
WhilePrintingRecords;
NumberVar JPMetEnd;
if {tblMeters.PDate} = {?End}
then JPMetEnd := {tblMeters.SoftJackpot}
@JPMetStart
WhilePrintingRecords;
NumberVar JPMetStart;
if {tblMeters.PDate} = {?Start}
then JPMetStart := {tblMeters.SoftJackpot}
@JPVariance
WhilePrintingRecords;
NumberVar JPMetStart;
NumberVar JPMetEnd;
NumberVar JPVar :=0;
JPVar := JPMetEnd - JPMetStart
@OutMetEnd
WhilePrintingRecords;
NumberVar MeterEndO;
if {tblMeters.PDate} = {?End}
then MeterEndO := {tblMeters.SoftMeterOut}
@OutMetStart
WhilePrintingRecords;
NumberVar MeterStartO;
if {tblMeters.PDate} = {?Start}
then MeterStartO := {tblMeters.SoftMeterOut}
@OutVariance
WhilePrintingRecords;
NumberVar MeterStartO;
NumberVar MeterEndO;
NumberVar VarO :=0;
VarO := MeterEndO - MeterStartO
@rstMeterEnd
WhilePrintingRecords;
NumberVar MeterEndI;
NumberVar MeterEndO;
NumberVar JPMetEnd;
NumberVar CXDMetEnd;
MeterEndI := 0;
MeterEndO := 0;
JPMetEnd := 0;
CXDMetEnd := 0;
@rstMeterStart
NumberVar MeterStartI;
NumberVar MeterStartO;
NumberVar JPMetStart;
NumberVar CXDMetStart;
MeterStartI := 0;
MeterStartO := 0;
JPMetStart := 0;
CXDMetStart := 0;
@SoftVar
WhileprintingRecords;
NumberVar VarI;
NumberVar VarO;
NumberVar CXDVar;
NumberVar JPVar;
NumberVar SVar :=0;
If {tblMachines.ReportGroup} = "E" then
(SVar := VarI - VarO) * {tblMachines.Denomination} else
If {tblMachines.ReportGroup} = "F" then
(SVar := VarI - VarO - JPVar) * {tblMachines.Denomination} else
If {tblMachines.ReportGroup} = "G" then
(SVar := VarI - VarO - JPVar - CXDVar) * {tblMachines.Denomination} else
If {tblMachines.ReportGroup} = "H" then
(SVar := VarI - VarO - CXDVar) * {tblMachines.Denomination}
@VarTotal
WhilePrintingRecords;
NumberVar Ctotal;
NumberVar SVar;
NumberVar TotalVar :=0;
TotalVar := (Ctotal - (SVar * {tblMachines.Denomination}))
The Report Is formatted as Follows:
Group 3 Header is Supressed with @rstMeterStart and @rstMeterEnd in
Details is Supressed and has
@InMetStart
@InMetEnd
@OutMetEnd
@OutMetStart
@JPMetStart
@JPMetEnd
@CXDMetStart
@CXDMetEnd
@CoinCE
@CoinCS
@CoinEDiff
@CoinHFDiff
@CoinHFE
@CoinHFE
@CoinHFS
@CoinCoinHPDiff
@CoinHPE
@CoinHPS
@CoinSDiff
@CoinSE
@CoinSS
In It.
Group3 Footer Has:
tblMachines.AssetNo
@InVariance
@OutVariance
@JPVariance
@CXDVariance
@SoftVar
@Coin
@VarTotal
In It
Now the result I am getting is as follows:
AssetNo InDiff OutDiff JPDiff CXDDiff GGR Operator Variance
1 10 5 0 0 5 5 0
2 35 10 0 5 20 21 1
3 25 0 25 0 0 -1 -1
4 2 6 0 0 0 -4 0
Which is what I want except for a few things:
If I change the date and there is no input for the start or end date it shows really really bad amounts.
I need it to go to the previous date if there is no info for the date chosen.
eg. If my end date is 17.05.2011 and there is no info for the 17.05.2011 it should automatically got the
previous date in the tables that has data in it like for instance 16.05.2011 or 15.06.2011 if there is no data for
16.05.2011 and so on. This should apply for start and end dates.
I aslo need it to be able to sum the coins together if i chose a longer period as at the moment is is day to day.
The table for the coins is not like the meters, it is the sum of everydat=y between the two dates, start date and end date.
This report is relatively slow and I need to find a way to speed it up slightly.
If anyone could please help with this or just send me in right direction I would be forever gratefull.
Edited by Inieas - 18 Jul 2011 at 4:22am
|
IP Logged