Group (Summary) Label


#1

I just created a db of sales and rentals for our company that dates back to 2007 which includes, among other data, the following:

Job Type (sale, rental, repair, etc…)
Job Date
Amount
Salesperson
Customer

I’m creating reports I can run from this db and am starting with a simple “Account Executive Sales by Year” GroupUp procedure triggered via a menu item. (In another procedure I created a custom menu of Account Execs.)

Is there a way I can create custom summary labels in the data sheet or do I need to create a form to display customized summary labels? (I scanned “Help” and the older pdf’s for the solution but haven’t found it yet.)

I use “Total” to capture totals by year and grand total in the summary. All good there.

But the Date field is formatted “mm/dd/yy”, so the “Groupup by Year” procedure carries the year end date (12/31/14, 12/31/15, etc…) into the summary level. Is there a way I can format the “year” summary level as “YYYY”? (Currently my data runs through August 2016, hence 08/31/16 in the image below.)

I’d also like to change the summary label for the Salesperson field, which uses the reps initials (ex, JD for John Doe). I’m using “Propagate” to carry the Account Exec’s initials into the summary level. In the summary level, I’d like to change their initials to their full name. Can I run a procedure (Replace?) to customize the content of that summary field? Or maybe there are other ways to customize summary fields. Ideas?

For reference, here’s my procedure. The “AE” stands for “account executive”, .

if info("trigger") beginswith "Menu.AEs."
local zSelectedAE

zSelectedAE = ARRAY(info("trigger"),3,".")

removeallsummaries

select «Salesperson» = zSelectedAE
field Date
groupup by year
field «Commissionable Amt»
Total
field «Salesperson»
Propagate

endif

outlinelevel 1

Thank you for giving this a look.


#2

As for the formatting questions, I think you would need to create a form to make the layout you want. If you just want the standard data sheet layout but with different field names, you can of course change the field names at any time, but there is no way to have that happen automatically when you do a summary.

For changing reps initials to their full name, you can do that with a program using the change statement. However, it would require one change statement for each rep. That’s probably fine if you have just a handful of sales reps. If there are a lot, you would probably want to set up a separate database with initials and names, and do w formulafill with a lookup( to change the initials to names. Or, if you have set up a form with a custom report, you could put the lookup( in the text display object and then the lookup would happen automatically as the data is printed, with no code needed.


#3

Thank you Jim.

As I got further into it I figured a Form was the way to go. Thank you for the confirmation. And thank you for the “text display object” tip. It’s been awhile since I’ve built forms and I’d forgotten about that option.