Help with formula 2

[flmngldn]

I am trying to create a report in 8.5
I have database table for labor tracking. I want to create two columns where 1st is week(prompt) and then the 2nd is rest of the data. This is going to be a summary report (with drill down)based on project# and task#. Here is an example:

Database:
Project# Task# Employee Weekend RegHrs OThrs
70023 101 Jane 02/13/2005 40 5
70025 202 Mary 02/20/2005 35 0
70025 202 Jack 02/20/2005 40 5
70025 202 Joe 02/13/2005 40 0
70025 444 Jack 03/06/2005 40 15
and so on

Report prompt week 02/20/2005)

Project# Task# 02/20/2005 Restoftime
70023 101 0 45
70025 202 80 95
and so on


I tried doing a formula against the prompt week and keep getting the total repeated for both the week and Restoftime
how would I accomplish this?
Thank you

 

[synapsevampire]

Doesn't make sense to me, you show 80 for 2/20/2005, yet there are 115 hours on or before that date, if it's just for that date, the hours would be 70.

Are you stating that you want just one days data, or for one week, or for all weeks prior to a date, or?

If it's for one week, group by the project,m and then by the task (Insert Group), and then add the following to your Report->Edit Selection Formula->Record

{table.date} >= {?MyDatePrompt}-6
and
{table.date} <= {?MyDatePrompt}

This will limit the rows to only that week, and then you can create totals for all of the data returned by the database, and just use the parameter (the term is not prompt) as the heading for that category.

Just add in the field to sum in the details, right click it and select Insert->Summary->Sum and select summary for all groups.

Now you can remove the field from the details, suppress the details section, and display all of the the fields in the group footer(s) as required.

-k

 

[bbgbp]

Can you post the formula you tried?

Without know what you've done so far, here's an idea:

Create the formula @Prompt:
if weekend = ?Prompt Week
then reghrs + othrs
else 0

Create the formula @RestOfTime:
if weekend <> ?Prompt Week
then reghrs + othrs
else 0

Then, create a group by Project# (Group 1) and Task# (Group 2).
Insert summaries (sum) for @Prompt and @RestOfTime at the group level for Task# (Group 2).

Hide the details section and the group footer for Group 1.

This should give you the output you described above.

Note: You can also accomplish this using running totals instead of the formulae mentioned.

This should

 

[flmngldn]

Thank you. Worked like a charm