cognos 8 BI

Assuming that you're reporting off of a relational database, you could define the measure that you're displaying with something like:

IF ([Report Date] <= ?PromptDate?)
THEN ([Actual Value])
ELSE([Budget Value])

Actually, that'll work for OLAP too (just noticed the zone), but you have to be careful with it, because IF-THEN-ELSE triggers local processing against OLAP.

In OLAP, a better solution is to define two dataitems for the edge members.  The first contains all the dates up to the prompted date (these samples are based on the Great OUtdoors cube):
Periodstodate([great_outdoor_sales_en].[Years].[Years].[Year],[great_outdoor_sales_en].[Years].[Years].[Month]->?PMonth?)

The second contains everything else:
EXCEPT([great_outdoor_sales_en].[Years].[Years].[Month],Periodstodate([great_outdoor_sales_en].[Years].[Years].[Year],[great_outdoor_sales_en].[Years].[Years].[Month]->?PMonth?))

Then you just nest the appropriate measure beside the sets in your crosstab.

Hi RWrigley....
     Thanks for your reply. And we are geting close. for the second expression its displaying all the periods. But I want to display only upto the end of the period.(i.e., If we select Aug2006 then Apr2006 to Aug2006(Actual) and Aug2006 to Mar2006(forecast)).

Note that if you wanted to constrain the first set in the same fashion (i.e, only showing months from the same quarter or year), you'd modify the first expression by changing the level specified as the first parameter.  My example uses the "year" level, which in the Great Outdoors sample is the "All" level, so it includes everthing.  If I change it to the expression below, it'll restrict to the current quarter:

Periodstodate(
 [great_outdoor_sales_en].[Years].[Years].[Quarter],
 [great_outdoor_sales_en].[Years].[Years].[Month]->?PMonth?
)

Select allOpen in new window

Hi RWrigley..Thank you once again to get back to me. For getting the actuals I am using the following function. I am same pashion, I want forecast from the selected period in the second function.

periodsToDate(cubename.[All Dates].[All Dates].[Fiscal Year],
item(filter(members(cubename.[All Dates].[All Dates].[Fiscal Period]),
caption(members([FIN_PL_CUBE_V2].[All Dates].[All Dates].[Fiscal Period])) = ?MonthParam?),0))

I'll caution you right off the bat that doing a filter on a caption is a relatively slow processes; cubes don't do string comparisons very well, and you'll find this method won't be a fast as using the member pointer (as in my examples).

Having said that, the end results should be the same.

EXCEPT(
 Periodstodate(
   cubename.[All Dates].[All Dates].[Fiscal Year],
   lastSibling(
    item(
     filter(cubename.[All Dates].[All Dates].[Fiscal Period],
       caption([FIN_PL_CUBE_V2].[All Dates].[All Dates].[Fiscal Period]
       ) = ?MonthParam?
      ),0
     )
    )
   ),
 Periodstodate(
      cubename.[All Dates].[All Dates].[Fiscal Year],
      item(
     filter(cubename.[All Dates].[All Dates].[Fiscal Period],
       caption([FIN_PL_CUBE_V2].[All Dates].[All Dates].[Fiscal Period]
       ) = ?MonthParam?
      ),0
     )
 )

Select allOpen in new window