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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

record selection based on year/period

Status
Not open for further replies.

UltraSmooth

Programmer
Oct 28, 2002
97
CA
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top