Good day , I have a field which list all deductions and earnings for a companies Payroll.
I designed a report for the company and I need to separate the data in the one field into 2 subreports or list all deductions on the right and earnings on the left. i have already tried a conditional formula to say if is like any earning do not show on deductions side. Here is an example of what i want to accomplish
here is the values from that field.
Regular
Holiday Pay
Bosl ( bank charges)
PAYE (Pay as you Earn)
National Ins.
Other Deductions
Service Charge
Tips
Overtime
Cash Advance
I would like these values which are all generated from one field to be placed into two subreports or fields.
Earnings:
Holiday Pay
Regular
Tips
Overtime
Service Charge
Deductions:
Cash Advance
Other Deductions
Bosl (bank charges)
Paye (pay as you earn)
National Ins.
i have been working on these for a month now , any help would be greatly appreciated.
There is one field in crystal which lists both imports and deductions.
However that field must be separated into two separate fields.
Earnings on the left and Deductions on the right .
Attached image is how the report looks currently because i used a condition formula to supress the records i do not one to see on the left but it doesnt balance . National Insurance Should be as far up the report as service charge . This is my problem .
Do you mean that there’s one record with all of this data in the same file or are there multiple records with a field that has the value and a field that has the “type” of the value?
The challenge with this is going to be in getting all of the data to line up. However, it can be done. I’m going to assume this is something like a payroll report where you’re showing earnings and deductions for individual people.
Main report: Use just the table(s) needed to get the information about the person. Don’t include the table with the earnings/deductions. Use the ID for the person to link to the subreports. Group by person.
Earnings sub report: Use just the table(s) needed to get the earnings data. Filter the table based on the earnings types you listed above so that you don’t have any deduction data. Put this subreport on the left side of a person group header or footer. Also, save this subreport out to a separate .rpt file so that you don’t have to create it again.
Deductions sub report: Import the subreport that you saved out to the file. Change the filter so that it is looking at just the deductions types you listed above.
To get the “net” value, do the following:
Place a formula in a person group header section that is before the section where the subreports are located. This will initialize some global variables like this:
{@InitVars}
Global Numbervar earnings := 0;
Global Numbervar deductions := 0;
“” <-- these double quotes will ensure that nothing will appear on the report when this formula is calculated.
In the earnings subreport, place a formula in the report footer that will total the value field for the earnings. It will look something like this:
{@CalcEarnings}
Global Numbervar earnings := Sum({earnings field});
“”
In the deductions subreport, use a similar formula to sum the deductions.
Assuming that all number values are positive, use the following formula to show the net pay data on the main report:
{@ShowEarnings}
Global Numbervar earnings := 0;
Global Numbervar deductions := 0;
earnings - deductions
“Earnings sub report: Use just the table(s) needed to get the earnings data. Filter the table based on the earnings types you listed above so that you don’t have any deduction data. Put this subreport on the left side of a person group header or footer. Also, save this subreport out to a separate .rpt file so that you don’t have to create it again.” this is exactly what i need to do , but i do not know how .
Go to the Select Expert in the subreport. Select the field that you need to filter on, select “Is one of” and add each of the values that you need. Because of the way databases are structured, this will probably be a numeric or text “key field” that doesn’t show the exact text that you want. To get that text, you’ll have to link to a “lookup” table that will translate the key field to a text value that’s the name of the amount type. You’ll add this filter to the one that should be there when you link the subreport to the main report on person id.
Without knowing more about what your tables look like, I can’t give you more than general information because I don’t know your data.
(BOB member since 2003-11-27)
(BOB member since 2007-04-16)