Multiple formula fields

[fcullari]

I need to breakdown fields by task type and then list the start and end dates for that task. I'm using Crystal 9 with NGC's eSPS software. We have calendars setup with tasks and I need to breakdown each task with it's start and end dates.

Task Types (TACalendar.name) - Approved Cost, Bid Pkg Sent, Dev Spec, Final BOM, Grade Digitize, Main Matl, Market week, etc.

Report layout:

Calendar ID Style Season Calendar Name

Approved Bid Pkg Dev
Cost Sent Spec
str end str end str end

06/01/05 06/17/05 06/17/05 06/20/05 06/21/05 06/25/05

Also, if someone can tell me how I can get the report in landscape mode instead of letter?

 

[lbass]

You left out the key information--your date field(s). Is there one for startdate and one for enddate? Or is there only one datefield? Please provide a sample of several records so we can see how the fields relate to each other, i.e., if you laid the fields on the detail section, how would they look?

-LB

 

[fcullari]

There is a StartDate and EndDate fields.

[Calendar ID] [Style] [Season] [Calendar Name]

[Approved [Bid Pkg [Dev
Cost] Sent] Spec]
[Start [End [Start [End [Start [End
Date] Date} Date] Date] Date] Date]
06/01/05 06/17/05 06/17/05 06/20/05 06/21/05 06/25/05

In this example the field names are bracketed.

 

[lbass]

That wasn't really what I asked--I was looking for sample raw data.

To get landscape mode, go to file->printer setup->orientation->landscape.

You might want to try inserting a crosstab in a group footer based on Calendar ID. In the group header, you can lay out the fields for calendarID, style, season, etc.

Use {table.tasktypes} as your column field and add start and end dates as your summary fields. Since there should only be one set of start and end dates per task, you could use a maximum as the summary. I can't tell whether the calendar ID is unique to the style and season. If not, you might need to add groups on those other fields and place the crosstab in the innermost group.

-LB

 

[fcullari]

I created a formula field for all the Calendar Names -
Example:
IF ISNULL ({TACalendar_1.Name})THEN 0 ELSE
IF {TACalendar_1.Name} = 'FINAL COST APPVL TO FINANCE' THEN 1 ELSE 0

Because I need to breakdown the Calendar Names and then put the Start and End dates per that task.

The Calendar_ID is unique and just a field to group by. The problem I'm having is breaking down the TACalendar_1.name field by Name and then Start & End Dates?

I do not do much programming, so I appreciate any help you can offer.

 

[lbass]

Please try the inserted crosstab solution first. Otherwise you need to do a manual crosstab, which is more work. I'm glad to help you with that, but first try the simpler solution and let me know how that works.

-LB

 

[fcullari]

Cross tab within the report does not solve my problem. They want a tabular report with all the tasks broken down and the start & end times under the tasks. I've never done this kind of report with Crystal before.

 

[lbass]

Did you try the inserted crosstab? Because that is exactly what it would have given you--the tasks going across the top, with start and end times below each. The only thing I'm unsure of is whether you have the option of a horizontal display of summaries in CR 9.

If you want to do it the hard way, then try this:
First group on {table.calendarID}. Then for each task type create two formulas:

//{@ApprovedCostStart}:
if isnull({TACalendar_1.Name}) then date(0,0,0) else
if {TACalendar_1.Name} = "Approved Cost" then {table.startdate}

//{@ApprovedCostEnd}:
if isnull({TACalendar_1.Name}) then date(0,0,0) else
if {TACalendar_1.Name} = "Approved Cost" then {table.enddate}

Repeat for each type of cost. Place these formulas in the detail section and then right click on each and insert a maximum at the CalendarID group level. Then suppress the details field.

-LB

 

[fcullari]

I will try this longer version. Thanks for your help.

 

[fcullari]

I created all the formula's for the start and end dates, now how do I get the Calendar_name fields for break along the top with the dates. I just need Calendar_name fields for the Product Development tasks, and I need them to show from the top.

Style Season

Bid Pkg Approved Cost Dev Spec Pre-Dev
Sent Spec

Start/End Start/End Start/End Start/End


I have the report almost complete, I just need to know how I can separate the Calendar.name field in columns with the dates.

 

[lbass]

Add them as text fields. Insert a text box, and type the labels in and then position them above the corresponding formula.

-LB