Date filter issue to extract appropriate year

[Cognoscat]

I am a computer-literate accountant vs a programmer, therefore, bear with me.  Here is my problem:

We use the SCT Banner system.  In the A/R module, transactions are linked to a date; however, there is no direct link from the transaction date to to any fiscal year.  Our fiscal year is July 1 to June 30.  I would like to extract data by detail code from the A/R module and be able to summarize the amount of transactions per specific detail code by fiscal year the transaction date relates to and showing me how much was recorded against each term in A/R.

Ideally I woud like to use a crosstab query in report studio with term down the side and fiscal year across the top with one amount per term and fiscal year cell for the detail code (vs. each transaction showing)

Ideally I would like to be able to write a filter that extracts the appropriate fiscal year by selecting the year on the transaction date if the month is January through June or the year plus 1 if the month is July through December.

Since I am a novice when it comes to writing SQL, I have not been able to write the above expression to get what I want.  As a result, I tried approaching it in pieces by extracting the year from the transaction date, extracting the month from the transaction date, and then writing an if then else statement as shown below to get the fiscal year.  The expression below works fine if the months are January through June.  For the months July through December, I am not getting the correct result.  For example, if my transaction date is between 7/1/10 and 6/30/11, I will get a fiscal year of 2011 if the month is March.  I will get a fiscal year of 20101 if the month is August, not 2011.

IF ([Month] in ('07', '08', '09', '10', '11', '12') ) THEN
    ([Year] + 1)
ELSE
    ( [Year] )

Can someone tell me how I would need to write the expression to get the reslts of 2011 for a month of July through December?

Even better, could someone tell me how to write the expression to get it directly from the transaction date without having to break it into two other fields first like I currently have done?  Assume field name is transaction_date.

Any help would be appreciated.

[Lynn]

It looks like your interim query items for month and year are strings rather than numbers so you are concatenating a 1 instead of performing a numerical addition.

The sample below might be close to what you need. The result of an extract function is an integer so no single quotes! The query item result will be a number. This means it may show up as 2,011 instead of 2011 in your report, but applying a data format (or casting to a string in the query item expression) could solve that.

The extract function is expecting that your transaction date is actually a DATE datatype (and not a string or numerical representation of the date). If it isn't a date datatype, post back what it is since the expression will certainly need to change to deal with it.

Code Select Expand


if ( extract(month, [TransactionDate]) in (7, 8, 9, 10, 11, 12) )
then ( extract(year, [TransactionDate]) )
else ( extract(year, [TransactionDate]) + 1 )


[Cognoscat]

Thanks a lot.  That worked.  I appreciate the quick response.

[Lynn]

 8)

glad to hear it