date groupings ifnot in the data

[RPOC]

I am using crystal reports 11. I have the following situation that i never encountered before so if this is posted in the incorrect area I apologize.
The report I am creating will allow a user to enter a start date and end date. for each date in that range i want to have a line item regardless of if data exists or not. I plan on doing calculations to get summary counts etc for those line items. I am not sure if this is possible of if it is how to do this. example I enter a start date of 12/1/2011 to 12/5/2011.
I want the ouput to look as follows regardless of if data exists for that day .
12/1/2011
total records x
12/2/2011
total records x
12/3/2011
total records x
12/4/2011
total records x

12/5/2011
total records x

based off the date i want to do counts if the date exists in the table. each will be its own detail line. Since the number of days will be dynamic this list will need to grow and shrink accordingly.

any help would be apprciated.

 

[RTag]

PROC, there were similar questions in the past. Can you use stored procedure and what is your database type ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[RPOC]

I am an end user that only has the ability to read from the database. Stored procedures cannot be added to the db. The db is an intersystems cache database.

do you know how far back that old post was? i can review it to see if there is something i can use.

thanks

 

[RTag]

RPOC,this is the link , but it is for SQLServer:

http://www.tek-tips.com/viewthread.cfm?qid=1669776

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[lbass]

Create a formula like the following and insert a group on it:

whilereadingrecords;
datevar x := {?startdate};
numbervar diff := {?enddate} - {?startdate};
numbervar i := i + 1;
if i <= diff then
x := Date(DateAdd("d", i,{?startdate}));
x;

Then add a subreport that links this formula to the date field in your table. In the sub, add the values you want to summarize and insert a summary. Suppress all sections except the report footer. Place the sub in the group footer section of the main report and suppress the detail section.

-LB

 

[RPOC]

thanks i will try that. i was working on somthing simliar using arrays but i think your method is cleaner.

i will give it a try.

 

[RPOC]

i set the formula up and the group goes for 7 days then stops for some reason. any idea on what might cause this.
I broke the report down to just the grouping and it does this. I entered a start date of 12/1/2011 and an end date of 12/20. 19 days.
I confirmed the variables in teh formula know this

thanks

 

[lbass]

I tested this and it works, so please show the exact formula you used to group on. Note that this has to be the ONLY group in the main report.

-LB

 

[RPOC]

i am using the exact same formula you posted. i created a parameter for start date and one for end date on the report.

the formula is the only grouping.

i entered 12/1/2011 to 12/20/2011. the diff value is 19.
the grouping is by day of the week.

i did notice my data set contained under 5 records. When whilereading records is running if it is looking at this data taht could be the cause. ? under normal data conditions there will be hundreds of records. does this seem plausable?
thanks

 

[lbass]

Yes, that is the issue--I forgot about this as a factor. You could add ANY table to the main report that has a limited amount of records but at least as large as the days in the date range, e.g., at least 365 records if the date range could span a year--just to force the formula to work.

Good catch--I was working with an old report that already had a table containing over 2000 records, so forgot about this.

-LB