Hello,
I have fiscal month names like 1,2,3,4,5 etc..
I need to change them to January, March etc..
Please anybody can help how to get it and what function I have to use. I tried with to_char but it is giving incompatible data types error..
thanks in advance.
You will want to look at the to_date function. You will want to convert your fiscal period to a date and then extract the month with the to_char function. So similar to this
to_char(to_date([Purchasing Payable].[Invoice Accounting].[FISCAL_PERIOD] + '01-2009', 'MM-DD-YYYY'), 'MONTH')
In our reporting database we can have multiple charts of accounts so we actually pull the date a given fiscal period represents from the database itself. So the above code assumes your fiscal year begins in January.
Hi Chnu,
it is not working for me it is giving below error:
An error occurred while performing operation 'sqlOpenResult' status='-28'.
Thanks,
i guess it will depend on the datatype of your fiscal month column.
cschnu's SQL will work once you convert your month column to char to_char().
OR
you could use the _make_timestamp () function
to_char(_make_timestamp (2009,fiscal month,1), 'MONTH')
Yes, my data type is character '01', '02' etc. I would suggest breaking apart your function to see which part is failing. see if you can get Cognos to convert the fiscal period into a date first. Then try to cast it to a character representing the month.
Why don't you just create a Query Item in Framework Manager that's a case statement on the item? This seems like a lot of hassle for a relatively simple problem.
case
when [Month = 1 then "January"
when [Month] = 2 then "February"
...
end
You can name the Query Item something appropriate too.
Quote from: kaevne on 24 Sep 2009 03:01:02 PM
Why don't you just create a Query Item in Framework Manager that's a case statement on the item? This seems like a lot of hassle for a relatively simple problem.
case
when [Month = 1 then "January"
when [Month] = 2 then "February"
...
end
You can name the Query Item something appropriate too.
Yes, this is also an option. Although I think that a single line two function expression is quite simple, you just have to give the function the correct parameters to get it to work correctly. As is usually the case there is more then one way to solve a problem.
Like I stated earlier, I think it would be best to pull your fiscal period information(the beginning and end date of a give fiscal period) from your data that way you will be flexible in the future and will be able to reuse this report. For example period '01' is not going to January in everyones environment and in my case we have two different fiscal years depending on the chart of accounts.