record selection based on year/period

[UltraSmooth]

My report selects records based on a fiscal_year and fiscal_period fields. I have 4 parameters setup, ?startYear, ?endYear, ?startPeriod, ?endPeriod.

How would I edit the record selection formula to give me the proper results? The formula would have to be different for times when the startDate and endDate are the same and when the endDate is greater.

If I use
((table.fiscal_year=startYear and table.fiscal_period>=startPeriod) OR
(table.fiscal_year=endYear and table.fiscal_period<=endPeriod))

This works when the endYear is greater than the start year. But if they the start and end years are the same it brings back fiscal_periods that I don't want (those lower than startPeriod).

If I change the formula to
((table.fiscal_year=startYear and table.fiscal_period>=startPeriod) AND
(table.fiscal_year=endYear and table.fiscal_period<=endPeriod))

This will work only if the startYear and endYear are the same, if the endYear is higher it will not return any records. In this situation the formula would have to be something like

table.fiscal_year=startYear and (table.fiscal_period>=startPeriod and
table.fiscal_period<=endPeriod)

I need a formula that will work in all situations and I just can't figure it out. I've tried putting a If Then statement in the record selection query to execute a different formula based on a startYear=endYear check, this works but the query takes too long, it seems to retrieve all records from that database and then filter them at the report level which is too slow. I idealy would like a query that works in both situations and is used at the database level.

Thanks for any help.

 

[synapsevampire]

Rather than posting that doesn't work, try posting what you have and what you need:

Crystal version
Database/connectivity used
Example data
Expected output

Stating fiscal period isn't descriptive, is this a date, a quarter, a month, or?

There are many ways to address fiscal periods, data warehouses do so by creating a period table, such as in my FAQ:

faq767-4532

-k