One of the problems often faced when working with true dimensional sources (as opposed to DMR which will not be discussed here, ever) is working with dates. You can very easily make Current Year or Previous Year members, but those will not allow you to use the family functions. How then can you make a query default to a specific year?
To begin you must first understand the structure of your cube. Different cubes have different ways of building their members. The Member Unique Name (MUN) for a specific year might look like:
[Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231]
You can use
children([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].#sb(timestampMask($current_timestamp,’yyyy’)+’0101-‘+timestampMask($current_timestamp,’yyyy’)+’1231′)#)
Cognos will resolve that statement to children([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231])
You can also use timestampMask with prompt macros:
#timestampMask(toUTC(prompt(‘pDate’,’date’) +’ 00:00:00.000000000-00:00′),’yyyy’)#
The toUTC command will convert a string 2010-09-26 00:00:00.00000000-00:00 to a timestamp, allowing it to be used with the timestampMask function.
Hi Paul,
Just been browsing through your posts… lost of useful tips and tricks here… Thanks for putting them up.
One question: You mention here that you won’t be discussing DMR models ever. Is there a particular reason for this?
Are there difficulties when using DMR that people should be aware of before embarking on a modelling project?
Thanks,
Paul
Hi Paul,
I have a slight aversion to DMR. Generally when I build a Cognos solution I have a proper data warehouse (the kind that Kimball would approve of), and at least one multi-dimensional data source. Most reports are based on the multi-dimensional source, with drill throughs, when necessary, to the relational warehouse. My thought process is generally along the lines of “If it’s an aggregated report, use cubes. If the report needs to show distinct rows, use relational”.
Any Framework Models that are built are tested to ensure every possible query generates the most efficient and correct SQL. The SQL needs to obey indexes and be reasonably easy to understand.
DMR, on the other hand, tends to generate SQL that is several pages long. If a report shows an incorrect value, good luck trying to pick the SQL apart to find the problem. While DMR will get the job done, why not spend the energy building a real multi-dimensional data source? DMR will always be slower than a cube.
To be fair, there are a few benefits with DMR. You can use database functions that would otherwise be unavailable. Date functions, a host of math functions, and custom functions. I’m sure there are other benefits to DMR, I’m just not familiar enough to talk about them.
Hi Paul.. Could you please guide me in the right direction in creating optional multiselect prompts for the fileds that are not part of crosstab.
For Ex: Crosstab with products in rows and months in columns and I want to have optional multiselect prompt on order method.
PS:using Cube as datasource
Thanks in advance..
You should ask your questions on a forum like Cognoise or the developerworks. Between my various clients and my commuting I have relatively little time to answer.
Try something like:
#promptmany('Param','mun','[cube].[dim].[hier].[level]->allmember','set(','',')')#Hi Paul,
I tried this syntax #timestampMask(prompt(‘From Date’, ‘timestamp’), ‘yyyymmdd’)# to process my parameter value as required before calling SP, prompt works fine but when I test my SP, it doesn’t return the result set (i.e) it returns column with no records. When I checked my macro function, by default it returns 00000000 for ‘YYYYMMDD’. Can u help me out what I’m missing?
Hi Aprasad,
prompts return strings as yyyy-mm-dd, timestampMask expects timestamp with timezone.
Try
#timestampMask(prompt('test','date') +' 00:00:00.00+00:00','yyyy')#