Record selection

[northw]

Hello all,
I have an issue with record selection, I need help figuring out a solution. My report's data source is SQL Command (MS SQL Server DW), I have current_fy_flag and Prior_FY_flag, I hava a parameter FY, where user can select the fiscal year. my requirement is if the user select's current fiscal year then it has to pull data for current financial year and prior fiscal year, else only for prior fiscal year.

Thanks in advance!

 

[hilfy]

What does the FY parameter return?

I would create the parameter in the Command (not in the main report!) as that will push the filter down to the database server. If you put it in the main report instead of in the command, Crystal will pull all of the data through and then filter it in memory.

So the question becomes how do you use the parameter? If possible, I would create a function in the database that will calculate the beginning date of the prior fiscal year based on whatever date you pass into it. For this example, I'll call it Get_Prior_FYStart. I would create another function that gets the start date of the current fiscal year based on the date you pass into it - I'll call this Get_Current_FYStart.

In the where clause of your command, you'll then use something like this:

table.start_date >= Get_Prior_FYStart(sysdate) and
(({?FY} = 'Current' and table.start_date <= sysdate) or
(table.start_date < Get_Current_FYStart(sysdate))

NOTE 1: This is Oracle syntax. If you're in another type of database, replace "sysdate" with whatever your db used to get the current date.

NOTE 2: This assumes that you just want current FY through the date the report is run. If you need it through the end of the current FY, you would create a function to get the end of the current fiscal year.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[northw]

Thank you Dell!
As I cannot use UDF's or stored procs in our environment, I have no option other than using SQL Command object or Crystal.
Any other work around?

Thanks again!

 

[hilfy]

That makes things a bit more difficult. What are your fiscal year start and end dates? I should be able to figure out the logic.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[northw]

I am just pulling the Year, there is no date associated.
But our fiscal year starts on 1st JULY to 30 JUNE.

Thank you!

 

[hilfy]

Oh, that makes it much easier!

In Oracle, the where clause in your would look something like this:

(({?FY} = 'Current' and table.start_date >= to_date('01-JUL-'||to_char(add_months(sysdate, -24), 'yyyy') and table.start_date < to_date('01-JUL-'||add_months(sysdate, 6), 'yyyy')) or
(table.start_date >= to_date('01-JUL-'||to_char(add_months(sysdate, -24), 'yyyy') and table.start_date < to_date('01-JUL-'||add_months(sysdate, -12), 'yyyy')) )

In SQL Server, you would use (I believe - it's been a while since I've worked with SQL Server) the FormatDate, DateAdd, and DatePart functions to get the various pieces of the date.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.