Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating a custom group based upon an array

Status
Not open for further replies.

Oscar115

Technical User
May 21, 2010
30
US
Using Crystal XI, I would like to group my data for each date in the date range. This is something I have never done nor have I worked much with crystal arrays, just trial and error.

I have defined a datetime date range parameter, Date Range, using any date range. This instance my date range is 6/1/10 to 6/15/10.

I do not know how the make my parameter a group.

I have also defined beginning date and ending date parameters.
I have tried creating an array for my date range from beginning date to ending date in an attempt to the Date Range Array formula a group but had not been able to figure it out.

Below is my Date Range Array formula where
@Number of Days is defined as datediff("d", ?BeginningDate, ?EndingDate) + 1

BeginningDate is 6/1/10
EndingDate is 6/15/10

Date Range Array is:

shared datetimevar array RangeDates;
redim DateTimeDates[{@Number of Days}];
numbervar end := {@Number of Days};
numbervar i;
for i := 1 to end do
(
RangeDates := ({?BeginningDate}+i-1);
);

Any direction would be appreciated.
Oscar115
 
Hi,
A Date range parameter is already an array and, when used in your seelction criteria formula, it will, of course,
limit the data to that date range,so you can use the Date field in that returned data to group on,with no need for a formula.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
The data that I am searching has start dates and end date long before and after my date range.

I need to get data (statuses) based upon each date in my date range.

Below is the type of date I am looking at.
I am writing reports for apartment complexes.
When a tenant moves out, inspections, maintenance work, etc take place in the units. Each of those work instances, have a starting date and ending date. I need to determine the status of each unit each day in my date range. My date range rarely coincides with the beginning or the end of the work, nor is the work completed in some instances.

I need my group to me my date range and the dates int he database.

Hopes that makes more sense.
 
You can group on the following formula to get all the dates in the selected range. However, in order to then add data to correspond with these dates, you need to insert a subreport in the group header or footer that is linked on this formula to a date in the subreport.

Note that in the main report, you must add any table that contains at least as many records as are in your daterange, and then add a field to the report header and suppress it. The table is necessary for the dynamic array to populate. Otherwise, nothing will be displayed.

whilereadingrecords;
numbervar i := i + 1;
numbervar j := datediff("d",{?BeginningDate},{?Enddate})+1;
datevar array RangeDates;
datevar k := date(0,0,0);
if i <= j then (
redim preserve RangeDates[j];
RangeDates := {?BeginningDate}+i-1;
k := RangeDates
);
k

I tested this and it worked appropriately.

-LB
 
Thanks for the info. I will try to see if I can get it to work.

 
LB,
I have tried what you have suggested.
I receive an error on the formula you have provided, specifically on {?BeginningDate}+i-1;, error given is:
A date array is required here.

I have changed the formula (Date Range Array)as follows but do not know if it is correct:
whilereadingrecords;
numbervar i := i + 1;
numbervar j := datediff("d",{?BeginningDate},{?EndDate})+1;
datetimevar array RangeDates;
datetimevar k := date(0,0,0);
if i <= j then (
redim preserve RangeDates[j];
RangeDates := {?BeginningDate}+i-1;
k := RangeDates
);
k

I have grouped the main report on the field {lrunitst.start_dt}, inserted a subreport in the header.
I have linked the formula Date Range Array to the field {lrunitst.start_dt} in the subreport.
I see dates in the subreport only when the {lrunitst.start_dt} equals a date in {lrunitst.start_dt}, so I believe the grouping is working in the subreport although I have not defined a group in the subreport based upon the RangeDates array (still do not understand how to do this).

I would like to return more data in the subreport where the date range group {?Pm-@Date Range Array} in {lrunitst.start_dt} to {lrunitst.end_dt}.
I have changed the selection criteria of the subreport to this but get no data.

I have also tried the selection criteria of the sub report to be
{lrunitst.start_dt} <= {?Pm-@Date Range Array} and
{lrunitst.end_dt} >= {?Pm-@Date Range Array} and get no data.

I am assuming (and perhaps I do not understand) that I would be looking for records where RangeDate[1] = 6/1/10, RangeDate[2] = 6/2/10, etc. through RangeDate[15] = 6/15/10.
 
You changed my formula. You should use it exactly as is.

The main report should NOT be grouped on anything but my formula. As I said, you should add one field to the report header from any table that has at least enough records as the difference in the beginning and end dates. ALL data for reporting should be in the subreport. The subreport should be linked like this:

{lrunitst.start_dt} = {?Pm-@Date Range Array}

...since the formula is not returning an array, but a single date value (k).

-LB
 
Hello LB,
I did change your formula because the line:
RangeDates := {?BeginningDate}+i-1;
is giving me an error, on {?BeginningDate}+i-1;,
'A date array is required here.'

 
That is not a line from my formula. Please copy my formula into your formula editor without any changes.

-LB
 
Oscar,

lbass' post had: RangeDates := {?BeginningDate}+i-1;


it appears that you left the off after RangeDates

 
LB,
Thanks for your direction on this. I found the mistake and corrected it but I am not getting what I am looking for.
My understanding of an array is an array (table) of values.
I do not understand why the formula array definition is returning only one value. I see that k is being returned, one value, but I would like an array of values returned.

Thanks for you help!
 
LB,
I went back to where I left off last night.
I defined the Date Range Array as you have indicated, removed all other groups from the main report, added and suppressed the date field as you suggested. I created the group based upont he formula. I inserted the subreport and linked the array to a date field in the subreport. Now I see my groups of my date range.

I am not sure I understand how it got there, as I do not understand Crystal arrays. But it got me what I needed.

Thanks!!
 
Just as a point of clarification, you could have added ANY table to the main report and added ANY recurring field to the report header from that table, as long as the table contained as least as many records as the maximum number of days between start and end date. The table in the main report only acts to force the array to display across group instances. Ideally, you would add a table that has just a few more records than required, so that it doesn't bog down the overall report speed.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top