Parameter date selection problem

[sewilj]

I have created a report in v8.5 which runs on a grouped month. I have to use parameters for Jan - Dec that allows me to input the number of hours worked in each month.

If I use a simple date range parameter linked with my table {table.DOFF} I can run the report as long as I only select full months, and I also input the hours worked into the relevant months parameters.

However, what I would like to do is replace the date parameter with a parameter to select the relevant financial year (running April to March, defined as 2000/01, 2001/02 etc...) and have the report select the dates where I have added hours worked into the month’s parameter. I have created a formula which I have added to the Select Expert but it does not return any data.

if {?January} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,1)+mid({@financial year},9)) + &quot;,01,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,1)+mid({@financial year},9)) + &quot;,01,31&quot] else
if {?February} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,1)+mid({@financial year},9)) + &quot;,02,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,1)+mid({@financial year},9)) + &quot;,02,28&quot] else
if {?March} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,1)+mid({@financial year},9)) + &quot;,03,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,1)+mid({@financial year},9)) + &quot;,03,31&quot] else
if {?April} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,04,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,04,30&quot] else
if {?May} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,05,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,05,31&quot] else
if {?June} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,06,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,06,30&quot] else
if {?July} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,07,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,07,31&quot] else
if {?August} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,08,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,08,31&quot] else
if {?September} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,09,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,09,30&quot] else
if {?October} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,10,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,10,31&quot] else
if {?November} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,11,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,11,30&quot] else
if {?December} <> 0 then date({CPCASE.DOFF}) in [date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,12,01&quot to date(totext(left({@financial year},1)+mid({@financial year},3,3)) + &quot;,12,31&quot]

I have had to use left and mid to create the year as, although the default selections in the financial year are as above it appears to add in commas and spaces.

I hope this makes sense and if not at least it might give some of you a laugh at how I tried to achieve the solution.



Lewis
United Kingdom

 

[Madawc]

For a start, there's a much simpler way to find which month a date belongs in. Try
Datepart(&quot;m&quot;, {CPCASE.DOFF})

This will give a number 1 to 12.

Beyond that, whenever a selection form isn't returning anything, use // to knock out the command and see what you do get. Often the data isn't what you expect, and watch out for nulls. Crystal has the nice habit of stopping without warning when it finds a null, which is very confusing if you've been used to languages with a different design philosophy. Nulls are useful once you get used to them, but getting used to them wasn't easy.

Madawc Williams
East Anglia, Great Britain

 

[herbsza]

I will give this a try.

create a parameter {?Calendar year}

select expert

date({CPCASE.DOFF}) in cdate({?Calendar year},04,01) to cdate({?Calendar year}+ 1 ,03,31)

This should then give you a years worth of data.

create formulas for each field you need to show.

if {?January} <> 0 then
if month({CPCASE.DOFF}) = 1 then
{Field name}
else
'' or 0
else
if {?February} <> 0 then
if month({CPCASE.DOFF}) = 2 then
{Field name}
else
'' or 0
and so on.

I have not tested this but it should work.
Looking at other possible solutions.

 

[herbsza]

create again a calendar year.
in the select expert try the following

if {?January} <> 0 then
year({CPCASE.DOFF}) = {?calendar year} + 1 and
month({CPCASE.DOFF}) = 1
if {?february} <> 0 then
year({CPCASE.DOFF}) = {?calendar year} + 1 and
month({CPCASE.DOFF}) = 2
if {?March} <> 0 then
year({CPCASE.DOFF}) = {?calendar year} + 1 and
month({CPCASE.DOFF}) = 3
if {?April} <> 0 then
year({CPCASE.DOFF}) = {?calendar year} and
month({CPCASE.DOFF}) = 4
if {?May} <> 0 then
year({CPCASE.DOFF}) = {?calendar year} and
month({CPCASE.DOFF}) = 5
if {?june} <> 0 then
year({CPCASE.DOFF}) = {?calendar year} and
month({CPCASE.DOFF}) = 6
and so on

 

[sewilj]

herbsza

Thanks for your help. I'll give it a go and let you know how i get on.

Looks a lot tidier than mine

Lewis
United Kingdom