Dynamic Year Headings 1

[TXP007]

I am using CRXI against SQL server tables.

I have a SQL data element called BUDGET_YEAR. It is a string value (e.g. 2010).

I want to derive 3 coulumn headings from this BUDGET_YEAR as efficient as practical. My goal is to limit the amount of formulas for each heading.

The column headings I want to derive from the BUDGET_YEAR are FY10, FY09, and FY08.

What is the best approach to accomplish this task?

Thanks for your time.

TXP007

 

[MCuthill]

@TXP007:

I assume you would create "FY10" for 2010, "FY09" for 2009... so on and so forth.

Curiousity... "FY" = "Fiscal Year"?

In this case, I would advise the following formula:
@FormulaField
"FY" & Right({Table.BUDGET_YEAR},2)

This will append "FY" infront of the right two characters from the BUDGET_YEAR field in your DB.

Hope this Helps!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[TXP007]

Thank you for your reply MCuthill.

I apologize for the lack of detail in my question.

You are correct. "FY10" would be for 2010, "FY09" for 2009... so on and so forth.

FY = Fiscal Year

I only have one BUDGET_YEAR to work with. This year it's 2010. We are preparing our budget for the upcoming fiscal year. I have to derive my titles (FY10, FY09, and FY08) from the string '2010'.

I created the following formulas to accomplish this task.

To create FY10 from string 2010...
'FY' + Right({WORKING_BUDGET.BUDGET_YEAR},2)

To create FY09 from string 2010...
'FY' + cstr(tonumber(Right({WORKING_BUDGET.BUDGET_YEAR},2))-1,"00")

To create FY08 from string 2010...
'FY' + cstr(tonumber(Right({WORKING_BUDGET.BUDGET_YEAR},2))-2,"00")

Thanks again for your time.

TXP007