precompute parameters/variables whit sub query values

[unarayan]

I'm using report net and would like to filter a simple report based on the time dimention to display all year to date sales.

My filter in SQL language will read:
where fiscal_year = (select fiscal_year from date_info
where calendar_date = trunc(sysdate))

a) I'm not sure how to translate the sub query into a cognos filter.
b) I thought it might be best to create a parameter that calculates current fiscal year then substitute it in the filter. eg. fiscal_year = ?CURRENT_FISCAL_YEAR?
But I'm unable to figure out a method of creating such a parameter that calculates a value.

Can you please advice me on what the best way to achive this result would be.

Thank you, I'll appreciate any help on this topic

 

[DoubleD]

If your DateInfo table has all dates, as well as their fiscal year, could you join to the DateInfo table, then filter on Fiscal Year?

I am what I am based on the decisions I have made.

DoubleD

 

[unarayan]

I did try that, but what I'm trying to do is avoid the user intervention to select the current fiscal year, when it can be automatically calculated based on system date.

I think my next step is going to be to add a function in oracle called get_current_fiscal_year with the following SQL :

(select fiscal_year from date_info
where calendar_date = trunc(sysdate))

and use it in this situation.

I'll appreciate any comments and thoughts on this.

 

[DoubleD]

If you have that join defined, your filter would only need to be this:

where fiscal_year = Year(sysdate)

No user intervention required.

I am what I am based on the decisions I have made.

DoubleD

 

[unarayan]

That will not work our fiscal calander is different from the regular calander.

eg. of date_info table's data
calander_date Fiscal_year
------------- -----------
30-aug-04 2004
31-aug-04 2005
28-sep-04 2005
04-may-05 2005
29-Jul-05 2005
20-Jul-05 2006

Thus if todays date is 04-may-05 I want sales for 31-aug-05 thru 29-July-05.

ofcourse every date will have an entry in date_info table.
what I showed you above is just a sample.

Thank you.

 

[DoubleD]

I'm not sure exactly how it's done in CRN, but it should be even simpler than this.

In Impromptu, you would write a report against just the Date_Info Table:
Column1: Fiscal_Year
Filter: Calendar_Date = SysDate

Thus this report would only return one row, which would be the Fiscal Year of the current date.

Now put a dataset filter in your second report where Fiscal_Year = (Report1:Column1)

Does this make sense?

I am what I am based on the decisions I have made.

DoubleD