variable formula for dates?

[catchingup]

In CR11, I am using a subreport to create a cumulative totals section.
In the Main report, the user selects a week within the project period and then it populates the totals for that specific week.

MAIN RPT

Week Ending 1/14/07
WORK SICK TOTAL
BSmith 32 2 34
RKelly 44 0 44
WTomas 10 8 18


I need the subreport to then calculate the totals, for all of the weeks within the project period, prior to the given week the user selects. I presume the easiest wasy to do this is with a variable? I do not need to show this detailed info, I just need it to get the totals together.

Project to Date
WORK SICK TOTAL
BSmith 220 16 236
RKelly 200 40 240
WTomas 178 9 187

Any idea the best way to go about this?

 

[GJParker]

You don't need a subreport to create this report. One way would be to bring back all the data you need to calculate the grand totals and sum these accordingly in the report footer.

Create formulas to calculate the work and sick values for the week totals i.e.

If {Record.date} = {?Selected_date} Then
{Record.Value}
Else
0

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

If you want to do this in a subreport, link the subreport to the main report on the date parameter of the main report. Then go into the subreport record selection formula and change it to:

{table.date} in <project startdate here> to {?pm-?parmdate}-7

-LB

 

[catchingup]

I got this to work in the main report -- however the issue i have come across is that the Project To Date totals are not always accurate because for some weeks an individual may not be present. Example:

Week Ending 1/14/07 | Project to Date
WORK SICK TOTAL | WORK SICK TOTAL
BSmith 32 2 34 | 125 16 141
RKelly 44 0 44 | 200 40 240
WTomas 10 8 18 | 162 9 171

SubTotal 82 10 96 | 724 245 1526

**The Actual Project to Date Subtotals should be: 487/146/683 but there are other workers who did not work this week and their numbers for Project to Date are getting included in the Project To Date totals, when they should not be.

The workers are grouped into separate categories (senior Members, Junior Members and this seems to be where the issue is).

The grouping in the report is as follows:

1 Project
2 Company
3 Assignment
4 Worker
-- Detail level

The formula I'm using is: Sum({TotalHours},{@workername})

Any ideas????

 

[lbass]

Where is the subreport located and what are the current subreport links?

-LB

 

[Almie]

Date variable for case in subreport not connected to case in main report.

I am trying pull the receipt date from my subreport and place it on my mail report. I've tried creating a shared variable for the receipt date, but when I try to pull it into the main report, the receipt date that displays is for the Case/Party above.

I am using CR10 reading from a Informix database. The main report displays Case/Party from Case table. The subreport displays most recently issued receipt data for Case/Party from receipt table.

Main Report: Case/party date)
CR-200700101 Name: Gene Blue Sent_dt: 1-08-2007

Subreport: (receipt data by case/party)
receipt date: 1-8-2007 receipt: S-11222 amt: $9.00

I have connected the subreport to Main report by Case/Party that are grouped in Main menu and in subreport.

 

[Almie]

Date variable for case in subreport not connected to case in main report.

I am trying to pull the receipt date from my subreport and place it on my mail report. I've tried creating a shared variable for the receipt date, but when I place the formula field that holds the shared variable on the group footer, the receipt date that displays is not for that Case/Party.

I am using CR10 reading from a Informix database. The main report displays Case/Party from Case table. The subreport displays most recently issued receipt data for Case/Party from receipt table.

Main Report: Case/party date)
Case; CR-200700101 Party: Gene Blue Sent_dt: 1-08-2007

Subreport: (receipt data by case/party)
Case; CR-200700101 Party: Gene Blue
receipt date: 1-8-2007 receipt: S-11222 amt: $9.00

I have connected the subreport to Main report by Case/Party that are grouped in Main menu and in subreport.

 

[lbass]

The formula in the main report that references the shared variable from the subreport must be placed in a section below the subreport, so if the sub is in GF#1a, the formula should be GF#1b.

-LB

 

[Almie]

That worked. Thanks!