Sub Summary layout by Date

[rlinsurf]

Hi--

I'm replicating a layout where I need to be able to find a range of dates, then have subtotals for all transactions within that range, hence:

Transactions Processed and Closed between and including March 01, 2006 and September 11, 2006

Sale Transactions:77 

Return Transactions:3

Total Transactions:80 

Totals

Visa Sales:$2192.43 

MC Sales:$628.05 

AMEX Sales:$191.70 

Discover Sales:$34.95 

Total Sales:$3047.13 

Returns:$(104.85)

Total:$2942.28 

Averages (194 days)

Sale Transactions Per Day:0.39 

Amount Per Sale Transaction:$38.21 

Amount Per Day:$15.09

I've tried setting this up, but can't get the date range to be right at the top as shown -- or most of the totals either for that matter.

I have calc and summary fields doing the totals. I first check for the type of card and put the amount of the individual transaction into a calculation:

If(Card Type = "Visa"; amount; "")

I then do a summary, "Total of" the Visa Calc field. I repeat this for all four card types. Unfortunately, this is great at giving me the total for *all* transactions, but not just for a particular date range. As well, I can't seem to get the Grand Total for all transactions.

Lastly, I can't figure out a calc which will give me the total number of days in the date range...

I'm in FMP7.

Thanks to anyone who give me some help with this :

All My Best,

Jeffrey

[Kent Searight]

What TO is your layout based on? Do you have the correct fields in the Subsummary part of the layout? Are you doing a Find and then a Sort before trying to view your report in Preview mode?

These are things I'd check for if my Subsummary report weren't behaving properly.

Edited September 12, 2006 by Guest

[rlinsurf]

Ah -- ok that helped, thanks. :

Ok, what's happening now is that the totals are coming out fine, however, what I get in Preview is a list of dates, with the totals underneath each date, not one line stating the range, then all the grands totals as in the layout I'm trying to replicate.

All My Best,

Jeffrey

[rlinsurf]

I based the TO on Date.

[Kent Searight]

I based the TO on Date.

That doesn't sound right. I believe your layout should be based on Invoices or Sales or Receivables or something to that effect. Your Find should create the found set with the date range you wish to view. Unless your solution has a scheduler or a calendar I can't see why you'd have any TOs based on Date.

[rlinsurf]

Hi, Kent--

Sorry, maybe I misunderstood what a TO was...?

Okay, I basically put all the relevant fields into a trailing grand summary part. Everything is looking okay except for the line at the top which is supposed to state the date range chosen:

Transactions Processed and Closed between and including March 01, 2006 and September 11, 2006

Do you know how to do this?

All My Best,

Jeffrey

[Kent Searight]

How are you creating your found set of records based on a date range? If you're running a script that uses the GTRR step to define your found set, you should already have the values of the date range in your key fields.

Is "Transactions Processed and Closed between and including March 01, 2006 and September 11, 2006" one calc field? If so, what's the formula?

I guess I'm just not sure how you're doing some things, so it's a bit hard to recommend a solution.

BTW TO's are Table Occurrences. Every layout is based on one. If you don't understand this part, you'd be doing yourself a favor by reading this brief description.

[rlinsurf]

Ah--

I don't actually have any tables in this database (well, the one table, of course :.

GTRR -- Go To Record/Request? I'm just doing the finds and sorts manually, as every search is apt to be for a different date range.

As far as the calc field for the date, I'm using this:

"Transactions Processed and Closed between and including "& GetAsDate ( Min ( date ))&" and "&GetAsDate ( Max ( date ) )

It only gives me the date in the first record however, and I can't figure out how to format it properly either.

All My Best,

Jeffrey

[rlinsurf]

Looks like I got it.

I use two summary fields, one for the minimum of date, the other the maximum. I then use two calc fields.

Date Calc 1:

MonthName ( Date Max ) & " " & Day ( Date Max ) & ", " & Year ( Date Max )

And finally Date Calc:

"Transactions Processed and Closed between and including "& MonthName ( Date Min )&" "&Day ( Date Min )&", "&Year ( Date Min )&" and "& Date Calc 1

This seems to give the desired ranges properly formatted.

Thanks you again for all your help with this :

All My Best,

Jeffrey

Edited September 12, 2006 by Guest

[Kent Searight]

I'm just doing the finds and sorts manually, as every search is apt to be for a different date range.

All the more reason to script the creation of the found set and sort order.

GTRR = Go To Related Record, a script step that can greatly simplify the creation of found sets.