Variables and Shared Variables on a Subreport 1

[CSADataCruncher]

I'm feeling a little dense right now because I just can't seem to figure out how to use variable fields properly and I'm fairly certain that variables are exactly what I need to accomplish my goal.

So, here is my goal. I'm creating a report that tracks an employee's weekly time and expenses. One of the reimbursements that we track is mileage. However, we need to subtract 30 commuting miles per day if the employee travelled more than 30. I'm putting the mileage into a subreport for this timesheet.

So, on my mileage subreport, I pretty much have that portion working correctly... I created a formula field that finds the reimbursed mileage as such:

If {TimeSheetExpenses.Reimbursable}= true and
{TimeSheetExpenses.ItemNumber}='Mileage' and Sum ({TimeSheetExpenses.Quantity}, {TimeSheetExpenses.ExpenseDate}, "daily")>=30
then Sum ({TimeSheetExpenses.Quantity}, {TimeSheetExpenses.ExpenseDate}, "daily")-30
else Sum ({TimeSheetExpenses.Quantity}, {TimeSheetExpenses.ExpenseDate}, "daily")-Sum ({TimeSheetExpenses.Quantity}, {TimeSheetExpenses.ExpenseDate}, "daily")

.... and I have this coding in the expense date group footer section of my subreport.

Now what I need to have help with is getting this group total for each day added into a weekly total group footer for the timesheet number.

As you may know, I cannot summarize this field. So, I've been reading up on NumberVar and Shared NumberVar but keep coming up with zeros so I'm hoping that someone may be able to give me some exact coding and tell me where on the report I should add the coding.

Thank you, in advance, for your help.

Peggy

 

[lbass]

First I think you should change your formula to:

//{@Mileage}
If {TimeSheetExpenses.Reimbursable} = true and
{TimeSheetExpenses.ItemNumber} = 'Mileage' then{TimeSheetExpenses.Quantity}

This is because your current formula says if the current item is mileage, tell me the sum of all quantities per day, when you really only want the sum of quantities that refer to mileage.

Then create a second formula to place in the subreport report footer (assuming the subreport is linked on employee and date (daily)):

whileprintingrecords;
shared numbervar mileage;
if sum({@mileage}) >= 30 then
mileage := Sum ({@mileage})-30

Then in the main report you need a reset formula in the date (daily) GH3a (assuming employee is Group 1, date (weekly) is group 2, and date (daily) is group 3:

whileprintingrecords;
shared numbervar mileage := 0;

The sub should be in GH3b.

In Group Footer #3 (date-daily), add a formula like this:

//{@accum}:
whileprintingrecords;
shared numbervar mileage;
numbervar addmil := addmil + mileage;

In GF#2, add this formula to display the results:

//{@display}:
whileprintingrecords;
numbervar addmil;

Add a reset for the weeks mileage in GH#2:

//{@resetwk}:
whileprintingrecords;
numbervar addmil := 0;

I made a lot of assumptions here. Probably wrong in some cases.

-LB

 

[CSADataCruncher]

Thank you LB,

I'll give this all a try shortly... So, the first one (@mileage) would still go in the date footer? or in the details? Because I currently have the details just showing me the mileage expense.

But, as mentioned, I trust you, and logically it seems correct what you have said, so I'll give it a try and let you know what happens.

Peggy

 

[lbass]

Yes, it should go in the details section.

-LB

 

[CSADataCruncher]

LB,

Yay, I'm/We're getting closer... at least it's no longer a zero. )

I modified the coding just slightly and it seems that I do need to clear up a few misconceptions because the code isn't working entirely the way we need. This is my current results:

Mon, 6/22/09 343 313 $125.20
Tue, 6/23/09 52 22 $8.80
Wed, 6/24/09 26 0 $0.00
Thu, 6/25/09 26 0 $0.00
Fri, 6/26/09 356 326 $130.40
803 747

The reason I modified the coding is because the main report shows the time and then a subreport for the expenses and another subreport for the mileage (even though it is technically an expense). So, In the example above I need to subtract a total of 142 miles (30 miles each for Mon, Tues, and Fri, and the 26 miles for both Wed and Thurs). The 313, 26, 0, 0, and 326 are coming from my earlier formula that I forgot to delete.

However, when I show the details lines (which aren't showing above) the "miles" field just shows the exact same thing as the quantity field.

This subreport is connected to my main Timesheet report by the Timesheet number. So, the headings/groupings on the subreport are timesheetexpenses.TimsheetNumber (group 1) and timesheetexpenses.ExpenseDate (group 2).

Here is how I modified the code:

In my details section I have a field labeled "Miles" (I couldn't use the word mileage since that is used in other locations) with the following coding:

//{@Mileage}
If {TimeSheetExpenses.Reimbursable} = true and
{TimeSheetExpenses.ItemNumber} = 'Mileage' then {TimeSheetExpenses.Quantity}

In GF#2 I have a field titled "DailyMiles" coded as:

whileprintingrecords;
shared numbervar miles;
if sum({@miles}) >= 30 then
miles := Sum ({@miles})-30

In the subreports GH#1 I have a "ResetMiles" field coded as:

whileprintingrecords;
shared numbervar miles := 0;

And then in the subreports report footer I have the "Accum" field with the coding:

//{@accum}:
whileprintingrecords;
shared numbervar miles;
numbervar addmil := addmil + miles;

So, did I totally mess up your coding or am I just missing a step somewhere?

Peggy

 

[lbass]

Please identify the groups on the main report and explain in what section the mileage subreport is located. Is the other subreport irrelevant to the mileage? Do the mileage calculations only occur within the mileage subreport? Is the date group in the sub on change of date? Is the timesheet number unique to the week? Where does the employee fit in? Is there no group for this?

-LB

 

[CSADataCruncher]

LB,

I'm sorry, you're right, that would be helpful. A little background... the database I'm using is a CRM database that is online. So, I cannot change the database structure too much.

That being said, Yes, the timesheet number is unique not just for the week but also for the employee. When the employee enters his time into the database, the timesheet is automatically created (or the time placed on the proper timesheet) based on the date of the work.

The tables used for the main report are:

Employee
Incident (this is a service call type table)
ProjectTask (which I'm not sure if I really need)
TimeSheet
TimeSheetDetail

The main report has the following structure:

Report Header: Suppressed

Page Header: A title box and then the {?FromDate} and {?ToDate} parameter fields. It also has the {?From Technician} and {?To Technician} parameter fields and {TimesheetNumber} - these last three fields are suppressed.

GH#1 = TimeSheet.EmployeeCode: {Employee.Name}, {Employee.City}, {Employee.State}, and {Employee.ManagerEmployeeCode} along with column headers for the detail fields.

Details: has a formula field for the "Date" which represents the date that work was done... the formula is Date({TimesheetDetail.StartDateTime}). It also includes formula fields for the Labor Hours, Travel Hours, Total Hours, and a Yes/No field to indicate whether or not the job was complete on that particular date. Along with these formula fields, it includes the following: {Incident.UserText1} (a service receipt# field), {Incident.UserText6} (an additional activity# field), {Incident.IncidentNumber}, {Incident.CustomerName}, {Incident.City}, {Incident.State}, {TimeSheetDetail.ServiceCode}, and {ProjectTask.ProjectNumber}.

GF#1a: Has summary totals for the Labor Hours, Travel Hours, and Total Hours fields.

GF#1b: The two subreports. Both the sr_expenses.rpt and sr_mileage.rpt are built from the TimesheetExpenses table. They are linked to the main report using the timesheet number which resides in the Timesheet, TimesheetDetail, and TimesheetExpenses tables. As mentioned, this is a unique field for a particular employee's timesheet for a particular week.

Yes, I do plan to have a Shared NumberVar field so that I can add the "other reimbursed Expenses" total to the "Mileage" reimbursement total to come up with a total reimbursed expenses field. But I've been concentrating more on the mileage subreport at this point. The expenses subreport is basically working as expected at this point.

Page Footer: Has Print Date and Print Time fields.

Report Footer: Currently empty - I'm basically just using this to serve as the bottom of my box that is around each of the subreports. However, this is where I plan to put the total expenses and I may also move the total for the hours fields down to this part of the report.

I just want you to know, I really appreciate all the time you've been giving to help me solve my problem.

Peggy

 

[lbass]

Okay, now I see. Use formulas like this in the subreport. You don't really need a Group #1 on timesheet # since you are linking on it, but you can leave it as is--doesn't hurt.

In the subreport, place the reset in Group Header #2_a (date-daily):

whileprintingrecords;
shared numbervar miles := 0;

Change {@DailyMiles} to the following and place it in GH2_b:

whileprintingrecords;
shared numbervar miles;
shared numbervar addmiles;
if sum({@miles},{timesheetexpenses.ExpenseDate}) >= 30 then
miles := Sum ({@miles},{timesheetexpenses.ExpenseDate})-30 else
miles := 0;
addmiles := addmiles + miles;
miles;

In Group Footer #1, use:

whileprintingrecords;
shared numbervar addmiles;

In the main report, you can reference addmiles in
calculations in GF1c (they must be in a section below the one containing the sub).

You also should place a reset formula in the main report in GF1a:

whileprintingrecords;
shared numbervar addmiles := 0;

-LB

 

[CSADataCruncher]

LB,

I messed up in all my other explanations on one fact... it's not the total miles that I will be bringing forward, it is the reimbursed amount which would be {timesheetexpense.cost}*{@miles} but I can't seem to get it to figure right.

I figured once the mileage was calculating correctly (which it is now thanks to your wonderful coding expertise) that all would be right but I've tried different ways of coming up with the calculation and it either wants to calculate based on the total miles (803) in the scenario we've been using or nothing at all.

O, hey... before I actually posted this, I came a little closer to the finish line... so, I added the following two codes to my subreport:

In the GH#1 area, I have:

{@ReimbCost} Coded as follows:

//{@ReimbCost}
If {TimeSheetExpenses.Reimbursable} = true and
{TimeSheetExpenses.ItemNumber} = 'Mileage' then {TimeSheetExpenses.Cost}

And {@DailyMileCost} Coded:

{@Miles}*{@ReimbCost}

So, now I just need to know how to sum this {@DailyMileCost} field and have this summary brought over to the main report and added to the cost of the other reimbursed expenses from the other subreport. I tried creating my own Shared NumberVar coding but I'm still not doing things right and Crystal doesn't like what I'm trying to do regarding a Shared NumberVar.

Thanks,
Peggy

 

[lbass]

Crystal doesn't have any feelings about this one way or the other.

Isn't there a standard cost per mile? What is it? If it varies, on what basis?

-LB

 

[CSADataCruncher]

LB,

For the most part it's standard @ $0.40/mile. But we do have one group within our company that is reimbursed at a higher rate - they are not currently on this database system and we add them, I think I can handle that coding to change the rate for a specified group.

The only other time it increases is when the company VPs decide that they are going to raise the reimbursement more in keeping with the current cost of living. I was just trying to build a report that would flow with the times.

But I have thought of just plugging in this standard cost and, in that case, I guess I would just do the calculation on the main report by bringing that "addmiles" shared value over. So, if you think that would be the easiest solution, I'll give that a try.

Thank you,
Peggy

 

[lbass]

That wasn't my point really. The mileage is being summed at the group level and accumulated in the miles variable. You can simply add in the amount like this:

whileprintingrecords;
shared numbervar miles;
shared numbervar addmiles;
if sum({@miles},{timesheetexpenses.ExpenseDate}) >= 30 then
miles := (Sum ({@miles},{timesheetexpenses.ExpenseDate})-30)*maximum({@cost},{timesheetexpenses.ExpenseDate}) else
miles := 0;
addmiles := addmiles + miles;
miles;

I just wasn't sure the cost was the same for all members of the group. Of course now the miles variable would actually be the mileagecost variable. If cost is a currency, you might have to change numbervar to currencyvar.

-LB

 

[CSADataCruncher]

That coding is giving me a few errors... first it said a field was unknown {@Cost} So, I tried changing it to {@ReimbCost} but then the error message said it was expecting a number and highlighted the entire line of the code. So, I changed it to take out the word "maximum" (as each timesheet will only have one cost amount) and then the error message came back saying that the rest of the text didn't seem to go with the formula.

I finally ended up with:

whileprintingrecords;
shared currencyvar micost;
shared currencyvar addcost;
if sum({@miles},{timesheetexpenses.ExpenseDate}) >= 30 then
micost := (Sum ({@miles},{timesheetexpenses.ExpenseDate})-30)*{@ReimbCost} else
micost := 0;
addcost := addcost + micost;
micost;

but this only adds the last line's reimbursement. So, I started over, copying your coding and trying to change as little as possible but ended up with almost the same results in coding and the exact same results in the outcome of the report:

whileprintingrecords;
shared currencyvar milescost;
shared currencyvar addmilescost;
if sum({@miles},{timesheetexpenses.ExpenseDate}) >= 30 then
milescost := (Sum ({@miles},{timesheetexpenses.ExpenseDate})-30)*{@ReimbCost}

The reason I had to keep changing it the 2nd time was because it gave me similar errors to those listed above and then said I could not declare the same value as a different variable (currency vs number) which made sense and I didn't want to lose the number part as that is important to the subreport... just not being carried over to the main report for totalling if that makes sense.

Peggy

 

[lbass]

whileprintingrecords;
shared currencyvar micost;
shared currencyvar addcost;
if sum({@miles},{timesheetexpenses.ExpenseDate}) >= 30 then
micost := (Sum ({@miles},{timesheetexpenses.ExpenseDate})-30) * maximum({@ReimbCost},{timesheetexpenses.ExpenseDate}) else
micost := 0;
addcost := addcost + micost;
micost;

This formula will only display the cost for that date, while accumulating addcost behind the scenes. If you want to see the results as they accumuluate, remove the last line. Otherwise, you would see the cumulative results in the following formula:

whileprintingrecords;
shared numbervar addmiles;

-LB

 

[CSADataCruncher]

Thank you so much for all your help!!! I'm sorry it took me so long to finalize the things we worked on together but I took a vacation in between. Today I noticed another post with a similar problem that you had responded to and, for some reason, everything finally clicked for me and I was able to get the totals that I needed and 0 out/reset the variables from timesheet to timesheet.

So, thank you, again... you were most helpful in getting me to the point I am on this report design.

Peggy