UltraSmooth
Programmer
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.
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.