Month over month Summary report

[jtwilber]

Can anybody start me in the direction of setting up my database so I can get a report like this.

Each account will have multiple (job tickets) at multiple locations at multiple (branches). This is a broad total summary, month over month with Year to date.

]

Account jan feb mar april may

Hess

Dunkin

Holdiday in

I couldnt figure out how to post the jpeg graphic of the excel spreadsheet sample.

[No_access]

I think what you are looking for is somthing like this

Create a summary field ie.. total_of_months with 12 repititions point to months, total of months

create a calulation field called months

Case( Get( CalculationRepetitionNumber ) = Day(Extend( ShipDate )) ;

Extend( pricefield) )

see screenshots below

[comment]

setting up my database so I can get a report like this.

IMHO, you should set up your database in order to best track the data you need - not to satisfy any specific presentation needs.

Each account will have multiple (job tickets) at multiple locations at multiple (branches).

That's not really a sufficient description. Assuming each job ticket belongs to one account, at one location and one branch ... well, we don't know the relationship between accounts, locations and branches, but you should likely have a table for each, with job tickets serving as a star join table.

Once you have that figured out, then it's time to see how to produce a report in the form you need.

[jtwilber]

Table 1: BroadGroup - This is the broadest marketing name used by these customers. Dunkin Donuts have many forms of ownership, but they are all part of the broadgroup name "Dunkin Donuts"

Table 2 : Parent_Company - This is the legal owner. Example Stratford Donuts, LLC they own 12 Dunkin Donuts locations. Key field = Parent_ID . Also contains the broadgroup name. 1 record per owner

Table 3: Accounts: 1 Record per location address - Key field = Account_number Example Dunkin Donuts, 12 Main Street, Stamford, CT ( one of the locations owned by Stratford Donuts, LLC ) Also contains the Parent_company name.

Table 4: Jobs: 1 Record per each job done. Key field = Ticket number.... contains account_Number as well. This is a single invoice for a job at the account "Dunkin Donuts on 12 Main ST " -- Owned by the Parent Company "Stratford Donuts, LLC" --- Considered part of the "Dunkin Donuts" Group.

What are our sales month over month for each account " Jan Feb Mar April..... Year to Date

[comment]

Assuming the Jobs table has a JobDate field and an Amount field, define the following fields:

cMonth - Calculation, result is Date = JobDate - Day ( JobDate ) + 1

sTotalAmount - Summary, Total of Amount

Find the Jobs you want to include in the report (IIUC, that would be the jobs in the current year) and sort them by AccountNumber and by cMonth. Use a layout with two sub-summary parts for the two breakfields and no body part.

Note:

This is the "native" (and fastest) method of summarizing records. Each sub-summary value will appear on its own row. Producing a cross-tab report is more complicated.

[jtwilber]

Great. I'll be working on it Friday. Thanks for the information.