Can the following layout be achieve 1

[HarpreetSachdev]

Can the following layout be achieved in crystal?

prompting on from date: 03/01
To date: 09/01
Person name: Joe Doe

Layout:
03/01 04/01 05/01 06/01 07/01 08/01 09/01
Subcontractor
Performance Ind.1
Perf. Ind 2
Perf Ind 3

SO for the date range, and the person need to calculate the counts of PM1, PM2 and PM3 for the date range. THe date range can be different everytime based on the promts.

Can crystal do this? Do I need SQR?

I looked at using datediff to calculate the difference between the two dates and then use a for loop, in the
for loop use the dateadd function. But I cant get the columns to show the different dates on each month?

Any tips?
Thanks

 

[kenhamady]

Looks like a straight forward Cross-tab.
By Month, By whatever that other field is. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HarpreetSachdev]

But the problem is that the date fields that are columns can be anything. They keep changing.
The date fields are run time prompts and are stored in a different table. The subcontractor is stored in a different table. PM1, PM2, PM3 calculations are done
for each subcontractor for the time period selected.
Can this be done in a cross tab?
If so how?
Thanks

 

[kenhamady]

It depends on how the prompts are actually implemented.

If you can create 2 CR formulas that can tell from your prompts how to categorize each record; (ie which row and which column it should be included in) then you can use those fields as your ROW and COLUMN fields. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HarpreetSachdev]

Hi kenhamady ,

Can you show me an example of what you mean?

That would help

Thanks

 

[kenhamady]

Describe your prompts, and how they are used to determine what the columns should be. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HarpreetSachdev]

My prompts are:

From Date:
To date:
Subcontractor:

The from - to date becomes my column
I filter on the subcontractor to find how many
service orders he has done in each month. so if the
user enters
from: 04/2001
to: 06/2001
i will have three columns

04/2001 05/2001 06/2001

and under the columns i got to do some sum, counts
to show that for each month.

MY issuse is how to get the changing columns in there
it only knows the first date and the last date.
then in between it also needs to show the months

 

[kenhamady]

That is easy. You tell the report to include all records for all months, and then in the cross-tab you tell it to group the data (that it finds) into monthly columns. Put your date field in as the "column" field, highlight it and then select "Group Options" to change it to monthly. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HarpreetSachdev]

Only shows me the first month. It doesnt show me the other
months. How does it know that it should stop the dates
at the TO Date prompt.
My prompts are from date: xx/yy/zz to to date: xx/yy/zz

 

[kenhamady]

You put that in your selection formula, something like:

{Date} in {?FromDate} to {?ToDate}

This tells it to only include records between those dates. I am assuming that all three items are date values. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HarpreetSachdev]

I dont get what you are saying. It only shows me the
first date I entered in the prompt. I tried the grouping thing and it doesn't show all the other months between
the from and to dates
Thanks

 

[kenhamady]

what is your selection formula? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HarpreetSachdev]

dont know what this var.shouldbe?? in {?FromDate} to {?ToDate}

 

[kenhamady]

Go to the Report menu,
select "edit selection formula"
select "Record"

Then cut the formula and paste it here. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/