COGNOiSe.com - The IBM Cognos Community

Hi,

I have a complex crosstab Report based on relational data.
The Problem I have there can be reduced to the following:

In Query I have dataitems:
ProductionDate
Itemnumber
ProdQty
ProdAmount

crosstab ProductionDate as columns
Itennumber ProdQty & Itemnumber ProdAmount as rows.

So far so good. Now I Need an additional column with percentage of lastday / Maximum_ever_reached_in_a_day.
To do this, I've created a dataitem lastday=filter([Productiondate];[Productiondate]='2015-11-22').
As Long as I Keep enter here the date as string it works well.
But as soon as I try to replace the string'2015-11-22' by a calculated dataitem, things won't work any more.
             lastday=filter([Productiondate];[Productiondate]=[calcdate]).

I've tried different things to get a solution, but depending on what I tried I've got different error Messages from Invailid coercion ,.... , cast not supported in this context.

Any hint to get me in the right direction is appreciated.

try to ensure/cast [calcdate] to become a string

Tried that with no success.

lastday=filter([Productiondate];[Productiondate]=CAST('2015-11-22'; VARCHAR(10)))   :  The cast function is not supported in the context

[calcdate]='2015-11-22'
lastday=filter([Productiondate];[Productiondate]=[calcdate])   :    Empty set for lastday
lastday=filter([Productiondate];[Productiondate]=cast([calcdate];varchar(10)))   :  Empty set for lastday

[calcdate]=cast('2015-11-22';varchar(10))
lastday=filter([Productiondate];[Productiondate]=[calcdate]   :  Empty set for lastday
lastday=filter([Productiondate];[Productiondate]=cast([calcdate];varchar(10))    :  Empty set for lastday

Even tried out the following:
lastday=filter([Productiondate];[Productiondate]=[Productiondate])    :    OK - Full Set returned
lastday=filter([Productiondate];cast([Productiondate];varchar(10))=cast([Productiondate];varchar(10)))   :    Empty set for lastday     --- Why ?

i think something is off with dimensional filter function being used against relational data... but i'll leave that to the experts like MFGF

here's a couple of things i think worth to try.
1.
* make sure [calcdate] is a string
* try lastday=filter([Productiondate];caption([Productiondate])=[calcdate])

if #1 do not work:
  a. select the query in the query explorer
  b. In the Data Property, set Override Dimension Info to 'Yes'
  c. A new tab called Dimension Info will appear beside Projected Data Items
  d. Drag [Productiondate] in Dimensions... (you might need to experiment on this)

try this again:

lastday=filter([Productiondate];caption([Productiondate])=[calcdate])

Thanks khayman,

I've never tried "Override Dimension Info" before.
Allthough it did not help me directly, it pushed me in the right direction.
When playing around with the possibilities, I found somewhere a tip as  "filtering a dataitem seems to Change it to a memberset". And afterwards dimensional functions work on this set.

So I created an item [IntProdDate]=extract(Year;[Productiondate]) *10000 + extract(Month;[Productiondate])*100 + extract(Day;[Productiondate])
and a memberset [SetProdDate]=order(filter([Productiondate];1=1);[IntProdDate])

Now I was able to get the last member of the set as   [lastday]=tail([SetProdDate])

I've tried tail before directly on [Productiondate] and it never worked. But this way it worked for me in small test Scenario. I will now try to move this to my complex Report.