Fiscal Years

building reports desktopintelligence
1

I’d like to create a variable called “Obligation FY” that produces a user-defined “year” based on a dimension called “Obligation Date” that falls within a user-defined range. I thought the best means to this end would be an IF, THEN, ELSE statement in the variable’s formula. For instance, IF “Obligation Date” between “10/1/2001” and “9/30/2002” THEN “2002” ELSE IF… Unfortunately, I can’t get the syntax down, which results in syntax errors or incorrect data. I’ve also attempted to change the data type for the “Obligation Date” dimension (e.g., character, numeric, date etc.) prior to creating the variable, which hasn’t helped.

What am I doing wrong? :confused:

John Phillips :us: (BOB member since 2002-10-01)

2

Here is the syntax for Obligation FY variable

= If Between (‘10/1/2001’,‘9/30/2002’) Then 2002 Else If—

Reema

reemagupta (BOB member since 2002-09-18)

3

Thanks for such a quick response to my post. Your suggestion worked. This is the syntax I used when the dimension was defined as a “date” data type:

= If Between (‘10/1/1994’ ,‘9/30/1995’) Then “1995” Else If …

Unfortunately, I’ve discovered that if I don’t get the syntax just right relative to the functions (and their associated data types), I can’t seem to get anything to work. For instance, I wanted to add the following ending ELSE to the above statement:

= If Between (‘10/1/1994’ ,‘9/30/1995’) Then “1995” Else

However, the result of the last ELSE statement was the following syntax error:

[b]“Incorrect data type (DMB0003)”

:crazy_face:

John Phillips :us: (BOB member since 2002-10-01)

4

try

avaksi :us: (BOB member since 2002-08-22)

5

How about creating it as an object in designer? For a column ob_date, you could define the fiscal year as …

to_char(Add_Months(ob_date,3),‘YYYY’)

… assuming that you are using Oracle – other DB’s would do something very similar. Maybe you could code the same in a BusObj function.

slimdave :uk: (BOB member since 2002-09-10)

6

Just a top tip if you are having problems with syntax. Especially with If…then…Else… statements make sure you enclose every discrete part of the statement in brackets. As you build your formula watch what appears in the ‘Operators’ box on the right hand side. If each discrete statement is correctly built you will see the next required operator appear in this box. If you’ve got something wrong you may only see a bracket or a single speech marks, for example. This is a good pointer to the fact that something is wrong with the statement you have written so far.

Nick Daniels :uk: (BOB member since 2002-08-15)

7

Thanks everyone for the great feedback :smiley:

avaksi wrote:

This suggestion works. The thing I didn’t realize until I say your solution is that I created a variable (Obligation FY), which is a character data type. Therefore, I think the results that are generated by the variable must also be a character data types :oops:

John Phillips :us: (BOB member since 2002-10-01)

8

Here’s a fabulous link to a topic in the Reporter forum that Nick Daniels created showing great examples of syntax that can be used to report by various periods of time.

How to report by week - Thursday to Wednesday

I can’t believe some of the things you’ll are able to come up with. Absolutely incredible. :smiley:

John Phillips :us: (BOB member since 2002-10-01)

9

Another topic I believe is closely related to this one is from the Designer Forum: Financial Periods

Andreas :de: (BOB member since 2002-06-20)

10

Thanks for mentioning this one. The period table is something we desperately need for most of our reports. I’ll be sure to send the link to our DBA.

John Phillips :us: (BOB member since 2002-10-01)