Convert Char to Int

[CynthiaBlue]

I am very new to cognos... we have cognos 8, Report Studio. I have just started working on it a couple of weeks ago. I apologize in advance if my questions are not clear or don't make sense.

Anyway, I am building a report. I know how to select certain records based on my framework. My question is this.

The user inputs the Fiscal Year. I want to take this Fiscal Year (that is a char) and find the prior Fiscal Year. Then I want to first determine if the prior Fiscal Year is closed or not based on the Fiscal_Year table.

If it is closed, I sum some records based on various criteria, if it is not closed, I do some other things. I know how to do the summing.

I don't know how to get the prior Fiscal Year. Do I need to run a macro or a query right after the prompt page, convert the char FY to a number, subtract by 1 to find the prior Fiscal Year, and then use that, as a variable or another component type to do my if statement?

Or should I do something in the framework to gather that information?

I appreciate the help. Thanks!

 

[blom0344]

I would in any case make sure that you create a fiscal year query opbject that is an integer in the framework.
Furthermore, the expression:

if the prior Fiscal Year is closed or not based on the Fiscal_Year table

needs some clarification. Where is the data stored that indicates which fiscal years are closed?

The best strategy is to bring in one dataset the data including FY, FY-1 and the closed/open indication on FY-1.



Ties Blom

 

[CynthiaBlue]

Thanks for your reply.

How would I go about making a query object in the framework? I'm going to Framework training next week so I will take all my questions with me.

The framework has a number of tables. The two main ones are the Accounting Journal and the Fiscal Year. The Fiscal Year Table has two records. The year (2007), and closedflag (Y or N). So the user will input the FY they want the report run for. After the input, I'll need to take that input, subtract a year from it, then query the FY table for that prior year to determine if the closed flag is Y or N.

Based on the Y or N, I select records for summarization.

Does that make more sense?
Thanks,
Cynthia

 

[blom0344]

Not really. The fiscal year table should have AT LEAST the number of fiscal years in it as are present in the accounting journal. If not, then a relationship between the two tables will never net you any other data than data of 2007. (assuming you do not specify an outer join)

So I assume the FY table looks:


Year Closed(Y/N)

2000 Y
2001 Y
2002 Y
2003 Y
2004 Y
2005 Y
2006 Y (OR N)
2007 N

So , in your case the prompt should relate to a report parameter that subtracts 1 from the prompt entered.
As 2007 is entered , 2006 is sent to the database and you retrieve 2006 data (including the value of Y/N from the FY table. In the report then combine filter and aggregate to calculate values..

Ties Blom