Record Selection based on Input Parameters Problem

[Nansat]

Hi,

I am using Crystal reports XI.

I am having a problem of determining the record selection based on the user input parameters

Input Parameters

wk1
wk2
wk3
...
wk52.

I have defined two formulas @stdt @enddt which determines the start and end dates based on the week selected by user.

Now new requirement is user should be able to pick multiple, random weeks i.e wk1, wk7 ..etc

Now my record selection should be

(date from (wk1-st-dt) to (wk1-end-dt) )OR
(date from (wk7-st-dt) to (wk7-end-dt) )

How can I achieve this??

 

[lbass]

How did you set up your start and end formulas?

If instead you had a set up a number parameter {?week} allowing multiple values, you could have set up your record selection formula like:

datepart("ww",{table.date}) = {?week}

...and that would have worked for multiple selections.

-LB

 

[Nansat]

Actually I have divided our company fiscal year into 52 weeks. and I have week start date and end date.

Suppose, when user selects wk5 then I will use this input parameter {?week} that has value 5, to determine the {@stdt} and {@enddt}

{@stdt}
select({?week})
case 1: mm/dd/yyyy
case 2: mm/dd/yyyy
........
case 52: mm/dd/yyyy
endselect;

{@enddt}
select({?week})
case 1: mm/dd/yyyy
case 2: mm/dd/yyyy
........
case 52: mm/dd/yyyy
endselect;


This was working fine all through. Now the latest requirement is user should be able to select multiple weeks. I am not getting how to handle this.

Ultimately the record selection should be like..

(salesdate in (wk1-st-dt) to (wk1-end-dt) )OR
(salesdate in (wk7-st-dt) to (wk7-end-dt) )

 

[mocgp]

You can use the DatePart function for this but it would entail 2 or 3 formulas for it to work without any hardcoded dates for the fiscal year. Using the formula DatePart("ww",{currentdate}) would return the week number 28, which can be used by your input parameter to determine the date range. The other formulas would be needed to adjust the current date to your fiscal year. Perhaps this is enough information for you to go on, but if not, tell me when your fiscal year starts, and I will try to build the other formuals for you.

 

[lbass]

Mocgp, That's what I suggested earlier.

Nansat,

If you want to continue with your current approach, then change your record selection formula to:

numbervar i := 0;
numbervar j := ubound({?week});
datevar array stdate;
datevar array enddate;
booleanvar y := false;

for i := 1 to j do(
redim stdate [1000];
redim enddate[1000];
stdate :=
select {?week}
case 1 : date(2004,01,01) //add your hard dates here
case 2 : date(2004,01,08)
case 3 : date(2004,01,15)//add cases up to 52
;
enddate :=
select {?week}
case 1 : date(2004,01,07)//add your hard dates here
case 2 : date(2004,01,14)
case 3 : date(2004,01,21)//etc.
;
if {table.date} in
stdate to enddate then
y := true);
y

-LB

 

[Nansat]

Thanks lbass and mocgp for your valuable suggestions.

DatePart("ww",{currentdate}) may return the week number that may not be same as that of my fiscal year week number.

Actually my fiscal year starts on 09/25/2005 and ends on 09/26/2006

Also By using Datepart, can we determine fiscal year period start and enddates?

Right now my report has all the weeks, period information being captured in

{@stdt} and {@enddt}

{@stdt}
select({?week})
case 1: mm/dd/yyyy
case 2: mm/dd/yyyy
........
case 52: mm/dd/yyyy
endselect;

{@enddt}
select({?week})
case 1: mm/dd/yyyy
case 2: mm/dd/yyyy
........
case 52: mm/dd/yyyy
endselect;

 

[mocgp]

Sorry lbass, I read right past that. I tested this with a couple of formulas that adjust the week # for the fiscal year and it works, but the criteria would have to be hard-coded for each fiscal year you needed to report on. I would post these but I'm not sure if that setup will work for you. Also, the formulas are "inefficient" in that they operate on the client instead of the server and this might be slow if you are querying a large number of records.

 

[Nansat]

yes Mocgp. Can you please post the formulas here?

thanks

 

[synapsevampire]

I'd suggest that you take the data warehouse approach and resolve this long term by setting up a period table.

I have a FAQ here which demonstrates setting one up in SQL Server (although for a different purpose, but it solves so many problems). Odd how you can discuss so much with total disregard for the database being used, I never understand these posts/responses.

faq767-4532

Now you can select the work weeks, and join the date fields to your data.

-k