Formula to total a date range 2

[Debug44]

I feel like I am loosing something here as what I am trying to do should be easy, yet I can not get the formula to report correctly. I have only 1 table with approx 29 fields.

I am trying to sum the orders.shipqnty field by a date range by orders.partid.

The report selection criterion is for Jan-Dec 2003.
I want the formula to total shipments for Jan only.
A second would total for Feb...etc.
I want them to report in the details section by month by part.

Part # Jan03 Feb03 Mar03
064423 5,600 5,200 4,800

This is what I would like it to look like.
I have been able to perform this in a cross-tab with no problem, yet I need it on a non-cross tab report.

The current iteration of the formula i have is as follows:

if {invoice.inv_date} in Date (2003, 01, 01) to Date (2003, 01, 31) then sum ({orders.ship_qty})

I have tried various select and got myself confused as none worked.
Any suggestions before I try again in my currently, albeit somewhat confused state would be greatly appreciated.

 

[synapsevampire]

It has to be in the details section? That doesn't make any sense based on your output, it's at the month level...

Unfortunately Crystal doesn't show totals in the details readily, and though it can be done, I'll assume that you meant that you want it to display on the same line, not the details section.

Group by part number.

Create a Parameter field to select the ending date of the report, or use currentdate as a default, this uses a parameter:

Create 12 Running Total formulas which contain something like the following in the Evaluate Use a Formula (example using CR 9):

whileprintingrecords;
datevar startmmyyyy := cdate(dateadd("yyyy",-11,
{?enddateparm}));
datevar endmmyyyy := cdate(dateadd("yyyy",-10,
{?enddateparm}));
datevar startdate := cdate(year(startmmyyyy),
month(startmmyyyy),1);
datevar enddate := cdate(year(endmmyyyy),month(endmmyyyy),
1)-1;
//now we know the date range
{invoice.inv_date} >= startdate
and
{invoice.inv_date} <= enddate

Now for each subsequent RT decrement the dateadd month portion values by 1.

-k

 

[lbass]

You can insert a crosstab within a report that is not labelled a &quot;crosstab report&quot;...

But if for some reason you cannot insert a crosstab, another approach is to use a formula for each month almost like the one you showed, only omit the &quot;sum&quot;:

//{@Jan} for the details section:
if {invoice.inv_date} in Date (2003, 01, 01) to Date (2003, 01, 31) then {orders.ship_qty}

Then insert a sum on this to get the group level results, and suppress the details. Repeat for each month.

-LB

 

[Debug44]

-k
Thanks for the assist. It worked and gave me options for expanding the use of data.

-LB
This is the formula I had used previously and it worked GREAT. I guess I went brain dead, as I knew I had used a similar formula. Thanks for the awakening

I also created a third variation with the embedded cross-tab and removed the top labels and totals so I didn't replicate the titles

I am a new user to Tek-Tips and if this is the norm as to the assistance, I have truly found a GEM.

Thanks to you both for the variety of suggestions and the rapid response. Both were very helpful.