Repeating a record for each day within its date range 1

[zmlteo]

(I'm not a programmer even though you might see that next to my user name. It's an error and I've not been able to correct it. Thanks.)

I am using Crystal Reports 9, MSSQL.

I would like for my record(s) to be displayed (I assumed grouped) on each day within its date range. The report should also show each date on a new page. The reason is that the report should be used as a work order, and the person can see what is needed for each date and not for the order itself. For example,

Order Table:
Order A
Order Start Date = May 1, 2004
Order End Date = May 4, 2004
Order B
Order Start Date = May 2, 2004
Order End Date = May 3, 2004

(A new page for each date)
1 May, 2004
Order A
OrderA details
2 May, 2004
Order A
Order A details
Order B
Order B details
3 May, 2004
Order A
Order A details
Order B
Order B details
4 May, 2004
Order A
Order A details

I wasn't too sure what other information I should include as I'm new to Crystal reports.

 

[kchaudhry]

You can try doing this:

Add 2 groups. The first group on {Order.Date} and the second Group on {Order.Type}. You can add the related information in the details section.

To get a new page for every date:
Right click on the Group Header (for the first group) section in design view > Format Section > Check "new Page Before" option.

Apply these changes and run the report.

Was this your question or you wanted to know something else?

Kchaudhry

 

[zmlteo]

Maybe I was not clear in my initial posting. My 2 records retrieved from the order table are:

(select * from table.orders where type = 'I')

ORDER START_DATE END_DATE TYPE
A 05/01/04 05/04/04 I
B 05/02/04 05/03/04 I

Given that, how will I be able to group on the order date? Will I be grouping on the start or the end dates? By grouping on the start or the end date, I will only get the order to still show up only one time because the database retrieved the record just once based on the order type. What i need is for Order A to appear four times under each date on and between the order start and end dates. Essentially, I would like the report to show:

1 May, 2004
Order A
OrderA details
2 May, 2004
Order A
Order A details
Order B
Order B details
3 May, 2004
Order A
Order A details
Order B
Order B details
4 May, 2004
Order A
Order A details

I might not be expressing my needs very well so please ask me the specific information you will need in order to help me solve this problem. Thanks.

 

[tektipdjango]

i think you need to create a dayTable with every dayDate
as record
1/1/04
1/2/04
...
1/31/04
2/1/04
...
12/31/04

Then make a query (here SQLServer)

select ORDER, START_DATE, END_DATE, TYPE, dayDate
from
table.orders
inner join
dayTable
on (daydate>=START_DATE and daydate<=END_DATE)
where type = 'I'

Then in your report you can group on dayDate to realize what you want

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[lbass]

The following worked when I tested it. You need to create a table that contains all dates and use this as the datasource for the main report. I used an Excel spreadsheet which which contained all the dates in a year as the datasource. You can enter the date in cell A1 and then use the fill function to quickly populate the entire year.

In the main report, insert a group on {xl.date} and then create a formula:

shared datetimevar dates := {xl.date};

Place this in the group header instead of the group name. Go to the section expert (format->section) and highlight the group footer and in the x+2 formula area for "New page after" enter:

Not OnLastRecord

This will give you a new page after each date except the last one.

In the record selection area you might want to limit the date period, e.g.:

month({xl.date}) in 2 to 3 and
year({xl.date}) = 2004

Next insert a subreport. Add the table containing the fields you want to report on.

In the record selection formula area, add your criterion:

{table.type} = "I"

Insert a group on {table.OrderID} and go to report->sort records and add {@start date} and {@end date} to the sort list.

Next create a formula {@shared date} within the subreport:

shared datetimevar dates;

Place this in the group header of the subreport and go to format field->common->suppress.

Place the detail fields you want to display in the detail section. Then go to the section expert (format->section)within the subreport and highlight the Group Header->suppress and enter:

shared datetimevar dates;

not (dates in {@start date} to {@end date})

Enter this same formula in the suppression formula area for the details section.

Suppress all other sections.

Place the subreport in the details section of the main report. Do not add any links.

This should give you the display you're looking for.

-LB

 

[zmlteo]

Thank you tektipdjango. I've tried your method and it worked like a charm.