Create an array and display the values in it

[Budjette]

Hi,

I need help for the arrays. I never worked with arrays before and I have a hard time doing what I want. I have a report with 5 groups. The last group by is with dates. The maximum values that I can have in that group is 7. The thing I want to do is fill up an array with thoses values (can somebody tell me how to create that array and fill it up and where do I need to insert it???(wich group)). Then, I have a date parameter that I need to compare with my array to see if that parameter is in the array. If it is, I want to be able to display all the values of the array in the details section or in the group footer section (if it’s the right place). Is this possible or I can only display the value that is equal to my parameter? Can somebody give me a hand on this one???

Thanks in advance,

Anne-Marie

 

[Naith]

You display all the values of an array using the 'join' function.

Having said that, it isn't really clear what you're using the array for. If you have a date parameter, and base your Selection Criteria on it, then you will only have the parameter dates in the report. (You could make the parameter drive off multiple dates, thus making your parameter your array.)

What is the criteria for the other 6 dates to be included in the report, as opposed to any of the other dates? Are they fixed dates, or are they dynamic?

For what it sounds like you want to do, I'm not 100% convinced that using an array in some formulas is really what you need to be doing.

If you disagree, school me in with some more details.

Naith

 

[synapsevampire]

More details are required, present example data, and expected results.

So that you might better understand arrays in Crystal, as Naith pointed out, a parameter can be an array by selecting that users can enter multiple values when you create it, and you can even preload the parameter with values. If you intend to limit your data based on those values, use something akin to this in your record selection criteria:

{MyTable.MyDateField} IN {MyDateParameter}

This will only bring in data which has date fields which match what is entered in the parameter.

If you want to limit rows to a hardcoded list of values, you can create an array of dates as follows, you don't need to place this field in the report anywhere, just reference it by name in another formula or within the record selection criteria:

example Formula name: MyDateFields (will show as @MyDateFields in the formula field list)

global datevar array mydatefields;
redim mydatefields[7];
mydatefields[1] := cdate(2000,1,15);
mydatefields[2] := currentdate+1;
mydatefields[1]

You're probably better served to just hardcode the dates into the record selection criteria though:

{MyTable.MyDateField} = cdate(2000,1,15)
or
{MyTable.MyDateField} = cdate(2000,1,31)
or
etc...

This will likely provide pass through SQL to the database (better performance), whereas an array created from a variable might not (a parameter array will provide pass through, pass through is a dicey proposition with Crystal).

Hope this helps to clarify the use of date arrays.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Budjette]

Hi guys!

Thanks for the quick answers! I think my explanation is not very clear, my english is not that good, I’m french canadian. I will try to give you more details. I created a view in my Oracle database in PL/SQL and my report is linked to that view. Every week, some work orders are created and scheduled in the database for workers to pick so they could work on them. The goal of the report is to have a daily follow-up of the job planned versus the job done. In the report, I have informations on each work order (ex. : description, status, schedule date, hours done, etc.). I built up an ASP page with 2 values to choose : the work-order and a date (we want to know if there was any work done on that selected date). So if I choose the WO number 01-045834-000 with the date 07/03/2002 (as my parameters, I can only choose 1 date, it’s not a multiple values) and that date is a part of the list, I want to see all the other dates that were also planned for that same WO in that whole week. Here is an example (only a part of the report is displayed) of the result in the report that I should see (there is a group by work order number and then by scheduled date) :

work order number Scheduled date Date job done
01-045834-000 07/02/2002 07/02/2002
07/03/2002
07/04/2002 07/04/2002
07/05/2002

I hope I gave more details and that you can understand better what my report looks like. By the way, can we insert a pdf file in the forum so you can see exactly a sample of the report? If yes, I can add it in my thread!

Thanks,

Anne-Marie

 

[synapsevampire]

Ahhh, I see. I don't think that you need an array.

Just modify the record selection formula to include the currentweek surrounding the {?Scheduled Date parm}, it's something like:

(
{Scheduled Date} >= Date(Year({?Scheduled Date parm}),Month({?Scheduled Date parm}),Day({?Scheduled Date parm}))-DayOfWeek({?Scheduled Date parm})+1

and

{Scheduled Date} <= Date(Year({?Scheduled Date parm}),Month({?Scheduled Date parm}),Day({?Scheduled Date parm}))-DayOfWeek({?Scheduled Date parm})+7
)

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Hi Anne Marie

I was wondering about the date chosen in the parameter.

Are you looking for all records for a particular Work Order greater or equal to a user entered date...or all records for that Work Order period.

As mentioned by the others...I don't think you need arrays at all....unless, the parameter date is in the middle of the date range you want displayed for a given work order.

By that I mean, if the Scheduled dates for a Work Order were

01-045834-000 07/02/2002
07/03/2002
07/04/2002
07/05/2002

and the user specified 07/03/2002 in the parameter...do you want the information for 07/02/2002 returned.

If not then the record selection formula is easy and the report is straightforward...with no arrays required.

The record selection formula would be something like

{table.WorkOrder} = {?ParamWorkOrder} and
{Table.ScheduleDate} >= {?ParamDate}

then you would simply keep your ScheduleDate Group and print the information in the Detail section.

But if you want ALL date data...then you cannot do this since to specify a date in the record select will eliminate previous date data...or current data depending on how the {?ParamDate} is used.

Then your record selection formula would be just

{table.WorkOrder} = {?ParamWorkOrder}

you would still have the Group on ScheduleDate but in the detail section you would store the dates as well as other information in an array(s) for printing in the ScheduledDate footer if the {?parmScheduleDate} was in the array.

So...tell us exactly how the ScheduleDate is used and if it uses all the date data I'll show you how to set up and print the array data.

Comprendez vous? Je suis Canadien, mais je ne parle que un peu de francais.




Jim Broadbent

 

[Budjette]

Hi again!

You guys are fast! I really appreciate it! To answer to synasevampire, you solution won’t do the trick because it brings me all the dates in that week. Maybe I didn’t give you enough details. Here is another example with more infos in my report :

01-045834-000 07/02/2002
07/03/2002
07/04/2002
07/05/2002

02-012856-000 07/06/2002

02-027465-000 07/01/2002
07/03/2002

02-026777-000 07/04/2002

etc…

So those are the possible results. But with the parameter that I pass (07/04/2002 for example), I only want to see this in the report :

01-045834-000 07/02/2002
07/03/2002
07/04/2002
07/05/2002

02-026777-000 07/04/2002

The 2 WO that contains the parameter date. So if I take your solution by putting the selection in the record selection formula, I get ALL the WO with all the dates. Some WO I want and some I don’t. Sorry if I wasn’t clear enough, I don’t want to waste you time because of my misexplanations. And to answer to Jim, as you can see in my example above, I want all the dates before and after the parameter in that week (I have another parameter that brings me only the records in the whole week, so I don’t have to bother for values 2 weeks ago). But, I can’t just take your example in the record selection formula ({table.WorkOrder}={?ParamWorkOrder}) because if the date is not in that work order, I don’t want that work order at all (and all the infos that goes with it). I would say that I have a complex report with lots of grouping and conditions. If you guys want, I can send you a print screen of the report, maybe that would help!

So there you go, hope I gave enough infos so I won’t take to much of your time!

Anne-Marie

P.S. : Jim, your french is good! If you want, I could translate my thread in french…

 

[Ngolem]

Bonjour Anne-Marie

Merci du compliment sur ma capacité de parler français. Mais je préfère l'anglais

Ok...so I was correct...you want all instances of a workorder ... even instances prior to the date specified.

This makes the situation more complicated and probably will need a subreport to solve.

The problem is that when you specify a date...then you leave out records without that date...make sense?

So really what you want to do must take place in 2 steps

1. You need to collect all the workorders that have a
specific scheduled date (that would be the main report)
2. Then report on all records for that main report
workOrder(that would be the subreport)

And if you wish to specify a single WorkOrder...that would be ok too (in the main report)

So in the main report have a record selection formula like this (I am going to do it bringing in ALL workorders for a given scheduledate...you can add a parameter to specify one or several or all workorders later if you wish)

Main Report Record Selection formula

{Table.ScheduleDate} = {?ParamDate}

This will bring back the WorkOrderID's that you wish. Now, Group your main report on WordOrderID

Group Header1 WorkOrderID
Details (this is where you will display a subreport of
information on that WOrkorderID)
Group Footer1

Now create a subreport...and link it to the main report by WorkOrderID...place it in the detail section and make it the proper width of the main report.

The record selection formula in the subreport will have no Date restriction so it will pull in all the WorkOrder information.

Et voila!.... C'est tout fini! Jim Broadbent

 

[Budjette]

Hi Jim!

Thanks a lot! Why didn't I think of that! It's easier that way. I thought that it was very complicated but it was not the case. I'll modify my report and keep you posted!

Merci beaucoup et passe une belle journée!!!

Anne-Marie