Subtracting on a Group 2

[lkerr]

Okay, I have given up ... I need some help, here. I'm using CR 9, and trying to create a report that will show the difference between 1st half 2006 and 1st half 2005. All the information is stored in a SQL database. I've probably gone about it all wrong, but I've tried about 50 different things, and I'm out of ideas.

Here's what I have so far:

Group 1 - Client Name
Group 2 - Date (sorted specifically by 1st Half 2005 and 1st Half 2006)
Details - suppressed, contains dates and amounts.
GF 2 - contains 2 formula, one for each date, looks like this:

if GroupName ({vMaster.Date}, "daily") = "1st Half 2005" then sum({vMaster.Amount},{vMaster.Date}) else 0

(The 1st Half 2006 formula looks exactly the same, with the exception of the selection criteria)

I'm getting correct amounts in my group footers, but when I go to process my differences via a separate formula in GF1, I'm getting it to only process the 1st Half of 2006 - won't recognize 2005 at all - here's that formula:

whileprintingrecords;
{@1st Half 2006 Sum} - {@1st Half 2005 Sum}

I thought it was pretty basic, but I'm doing something wrong, and I know it's something simple, but I can't figure out what! Anybody have any ideas? Thanks!

LK

 

[synapsevampire]

So if you want the difference by Client, you might create a record selection formula of the following to already determine which dates to pull into the report:

if month(currentdate) < 7 then
(
(
{table.date} in dateserial(year(currentdate)-2,7,1)
to
dateserial(year(currentdate)-2,12,31)
)
and
(
{table.date} in dateserial(year(currentdate)-1,7,1)
to
dateserial(year(currentdate)-1,12,31)
)
)
else
(
(
{table.date} in dateserial(year(currentdate)-1,1,1)
to
dateserial(year(currentdate)-1,6,30)
)
and
(
{table.date} in dateserial(year(currendate),1,1)
to
dateserial(year(currentdate),6,30)
)
)

Now you've limited the rows being returned to the previous full 6 months period, and the previous years same 6 months period.

Now you can just group on the date field and select for each year.

As for doing the difference, you can create formulas such as the following in the client group header:

whileprintingrecords;
numbervar curryear:=0;
numbervar prevyear:=0;

Then in the Details use:
whileprintingrecords;
numbervar curryear;
numbervar prevyear;
if month(currentdate) < 7 then
(
if {table.date} in dateserial(year(currentdate)-2,7,1)
to
dateserial(year(currentdate)-2,12,31) then
prevyear:=prevyear+{VMaster.Amount}
else
curryear:=curyear+{VMaster.Amount}
);
if month(currentdate) > 6 then
(
if {table.date} in dateserial(year(currentdate)-1,7,1)
to
dateserial(year(currentdate)-1,12,31) then
prevyear:=prevyear+{VMaster.Amount}
else
curryear:=curyear+{VMaster.Amount}
);

Now in the Client Group Footer you can use the following to display:

whileprintingrecords;
numbervar curryear;

whileprintingrecords;
numbervar prevyear;

Looks sane, or at least close, I can test right now.

-k

 

[lkerr]

k -

I tried the selection formula, I'm getting a blank on all records ... here's what I have, maybe I typoed (I've gone through it twice, though) ...

if month(currentdate) < 7 then (({vMaster.Date} in dateserial(year(currentdate)-2,7,1) to dateserial(year(currentdate)-2,12,31))
and ({vMaster.Date} in dateserial(year(currentdate)-1,7,1) to dateserial(year(currentdate)-1,12,31)))
else (({vMaster.Date} in dateserial(year(currentdate)-1,1,1) to dateserial(year(currentdate)-1,6,30))
and ({vMaster.Date} in dateserial(year(currentdate),1,1) to dateserial(year(currentdate),6,30))) and
{vMaster.ClientID} = 5

 

[lbass]

Not sure what you mean by first half, but you could use a record selection formula like:

month({vMaster.Date}) < 7 and
year({vMaster.Date}) in year(currentdate)-1 to year(currentdate) and
{vMaster.ClientID} = 5

Then create formulas like this:

//{@previousyr}:
if year({vMaster.Date}) = year(currentdate)-1 then {vMaster.Amount}

//{@thisyr}:
if year({vMaster.Date}) = year(currentdate) then {vMaster.Amount}

Place these in the detail section and then right click on each and insert a grand total. If you want to compare them, you can use a formula like this in the report footer:

sum({@thisyr}) - sum({@previousyr})

-LB

 

[synapsevampire]

I took a much broader approach than LB, allowing for future runs.

I'd suggest that you look at what's in the Database-Show SQL Query to make sure thwaqt the criteria is correctly passed.

I doubt that LBs solution will pass the criteria at all, forcing the report to do the processing.

-k

 

[lkerr]

Okay, I have to admit ... I kind of picked around and had to "do my own thing" on this one, although I used both of your *very valuable* suggestions as a jumping off point. K - Simply stated, I couldn't get my arrays to work correctly, I was getting a lot of kooky numbers and goofy stuff, however, with a little alteration into a running total, it worked out great!

LB - Great suggestion on the year/record selection formulas - I don't know why I didn't think of that before.

All I did after the record selection and the running totals was create a formula subtracting the running totals - bingo, exactly what I needed. Thanks! Couldn't have done it without you two!!

LK

 

[synapsevampire]

That record selection will work for the past 6 months, but will require rework for the next 6 month run period,

Look at my record selection more closely, it bases the record selection on the current date, however you could change thsi to selecting one, but hardcoding in a record selection that only looks at half of a year won't do much good 6 months from now.

-k

 

[synapsevampire]

Oh, and as I stated, the record selection proposed by LB probably isn't being passed to the database, which degrades performance.

-k

 

[lbass]

I agree with SV on the record selection formula, but I didn't really think the variables/running totals were necessary, although maybe we didn't have the full picture--maybe there was row inflation requiring that approach. If not, I think the conditional formulas I proposed would have been simpler and more efficient.

-LB