Trailing Grand Summary

[rellis32]

I'm having an issue with displaying data in in a trailing grand summary part. I'm attempting to run a report to display a contractors gross and net pay out. The first table is commissions, it stores all the money paid to the company. Through a relationship and lookup fields, it grabs the installers name and how much the installer was paid for each job. The second table is an items table which stores lines items for parts and supplies the installer has ordered. Keep in mind, there are several installers in this table. The relationship is based on a key that is created by taking the WeekOfyear(payWeek) & Year(PayWeek) => WeekOfyear(invoiceDate) & Year(invoiceDate) and also the installerID. I want to run this report for one installer at a time, to email them to track their YTD Gross and YTD Net pay. The subsummary parts are working great, showing gross pay, supplies ordered amount and net pay.

These fields are calculated as follows:

installerPaidSum = Total of installerPaid

sumInstallerSupplies = Sum (ITEMS_key::instExtPrice)

netPayPayDate = GetSummary(installerPaidSum ; PayDate) - sumInstallerSupplies

These display properly in the subsummary parts when sorted by PayDate. The issue I'm haveing is getting the grand total for supplies purchased, and net pay.

The installerPaidSum accurately shows the gross pay, but the other two only show the amounts from the last subsummary part.

Is it possible to get these sort of calculations to display in the grand summary? Any help would be greatly appreciated.

Bob

[Søren Dyhr]

I trying to digest your scriptling here:

Through a relationship and lookup fields, it grabs the installers name and how much the installer was paid for each job

Why is lookups used here??

--sd

[comment]

I don't think I am following this, but two things stand out nevertheless:

1. You don't seem to have a summary field for sumInstallerSupplies.

2. GetSummary(installerPaidSum ; PayDate) will not work in a grand summary. You need to use GetSummary(installerPaidSum ; installerPaidSum) for the grand total.

[rellis32]

There probably isn't a real good reason to use the lookup fields, other than it was very late, and I was very frustrated trying to get this to work.

[rellis32]

I don't think I am following this, but two things stand out nevertheless:

1. You don't seem to have a summary field for sumInstallerSupplies.

2. GetSummary(installerPaidSum ; PayDate) will not work in a grand summary. You need to use GetSummary(installerPaidSum ; installerPaidSum) for the grand total.

Comment, I do have the sumInstallerSupplies, are you referring to a calc field or a summary field?

2. I tried this suggestion, GetSummary(installerPaidSum; installerPaidSum), it putting out a huge number, which I would assume would be the sum of all the installers.

I think if I understood how exactly the trailing grand summary worked, I might be able to figure out what calculations will work, and which ones won't. Time to hit the books.

Bob

[comment]

I think you need to go back and explain exactly what tables you have, what are their relationships, and what do you want to summarize. I am under the vague impression that your report would be easier to produce if all transactions were in a single table - but as I said, I am not really following your situation.

[rellis32]

The database has many tables, but the tables involved are as follows.

COMMISSIONS: this table used to import all the jobs that have paid into. Every Friday I download a spread sheet that has all the jobs that the company is being paid for.

SCHEDULING: Scheduling holds all of the jobs that have been assigned to us to date. It is used to schedule the installers. Scheduling is related to the COMMISSIONS db by way of a unique ID assigned to each order.

In the COMMISSION db, this relationship is used to get the Installer that completed the job and how much that installer was paid for doing the job. Company profit, monthly margins, etc are tracked with the COMMISSION db.

ITEMS: this db holds the line items that are created with the INVOICES table. On a weekly basis I order the installers equipment and/or supplies. The installers are responsible for paying for their own supplies, the company buys the equipment. So in the ITEMS table there is a field for installer extended cost, which is how much the installer owes for that particular line item. There is a summary calculation that calculates the installers total supplies, with self joins to get sum installers, and sum installers by week.

The ITEMS table is related to the COMMISSION table via the _key field ( calculation to get the week of year number and year ) and the installerID field. When looking from the COMMISSION table I am able to view the total supplies ordered by any given installer for any given week. This works great. The issue I have is with adding them all up. I have managed to get the supplies amounts to show up correctly in the subsummary parts, and am showing gross - supplies = net in the subsummary parts, all working properly. What I can't figure out is how to get the TotalGross - TotalSupplies = TotalNet in the grand summary. I will attach a pdf so you can see what I am referring to.

payReport.pdf

Edited August 17, 2007 by Guest

[comment]

I am sorry, this is still too confusing for me (it could be just me being on medication right now).