Extract number of days from YTD dimension

[leahmarie]

So I need the number of days from the YTD dimension in a report to calculate a per day cost.  Does anyone know of a way to do this?  It is a dimensional model and I am using $/value(tuple(quanity, member)) and then that needs to divide by however many days, be it 30 or 120.  I've tried case and if statements? Also extracting but I do not know much about how to make those work.  Any help would be appreciated!!!!:)

[CognosPaul]

count(1 within set [Cube].[Time].[YTD].[Day]) will return a numeric value you can use in calculations.

Also, you generally don't need to use the value function. Cognos will automatically wrap tuples with value(), just as it will wrap levels with members().

Your expression could look something like:
tuple([Quantity],[Member])/count(1 within set [Cube].[Time].[YTD].[Day])

[leahmarie]

Thank you so much for the reply.  There is one problem though, my date dimension does not include days in this particular cube, it only goes down to the month.  I'm sorry I should have mentioned that in the first post.  Is there anyway around that?

[CognosPaul]

This makes it somewhat difficult. Can your cube author add in a new measure which is simply number of days, that's only sliced by the time dimension?

Another possible solution, if this is a list/crosstab in Report Studio.
Change the source type of the measure cell to report expression. 
In one of my cubes the months look like: 2008/Feb, so I'll use that as my base.

Code Select Expand

[Query1].[Actual]
/
extract("day",_last_of_month(_make_timestamp(string2int32(substring([Query1].[Month],1,4))
,case substring([Query1].[Month],6,3)
when 'Jan' then 1
when 'Feb' then 2
when 'Mar' then 3
when 'Apr' then 4
when 'May' then 5
when 'Jun' then 6
when 'Jul' then 7
when 'Aug' then 8
when 'Sep' then 9
when 'Oct' then 10
when 'Nov' then 11
when 'Dec' then 12
end
,1)))

The expression converts the month into a date, then returns the last day of the month.

[leahmarie]

Thanks for the help again.  I ended up using a form of your sql but i couldn't get the maketimestamp to work for some reason.  For the month variable the case statement works great i just changed 1 2 or 3 to the number of days in the month.  It just takes a while to run.  for some reason it won't let me use the case statement with the MTD variable, it gives me a parent child hierarchy error.  But it only has months up to the prior month to date so it may just not be feasible.  I've talked to them about adding days already and I doubt that will happen so it looks like they are going to have one that works and the others will have to be done by hand.  It makes sense that your sql should work but that MTD variable just won't take it. 

[CognosPaul]

I think you might have tried putting the code into a data item in the report. Unfortunately that won't work as the maketimestamp function is not supported in MDX. My suggestion was to change the source type of the value in the crosstab intersection.

The fact that your report is running slowly is an indication that Cognos is doing local processing, which is strongly discouraged.

[leahmarie]

I tried it in a calculated member, a set expression, and a data item so you are correct there.  I don't guess I know how to change the source type of the crosstab intersection? and also I've read about local processing, but I have only been using cognos for about 2 months and it's all self taught, so I don't really know what you're talking about there either.  Any pointers?  And if you change the source type will all of the date functions work? bc I've tried using one once before and it gave me the same error.  Thank you so so so much for all your help!

[leahmarie]

I just found where you can change the source type of the crosstab member to a report expression is that the same thing?

[CognosPaul]

Exactly. Change the source type of the cell intersection from Data Item Value to Report Expression. In this way you're performing the calculation on the report page, instead of in the query.

[leahmarie]

Thank you for continuing to help me.  I did all that you said though and it still isn't working.  I wasn't able to just change one intersection value to report expression though I had to select fact cells and change all of them is that the problem?

[CognosPaul]

Click on the intersection. In the properties on the left there is a row called Define Contents. Set that to yes, and the contents of the intersection should disappear. Unlock the report and drag in a layout calculation. You should be able to simply paste in the expression I gave before.

Alternately unlock the report and click on the <#1234#>. You should be able to change the source value for that from Cell Value to Report Expression.