how to give simple If Then Else condition while creating a cognos report on cube

[SAM2013]

A simple If Then Else expression on a Cube data source (Dimentional Reporting in Report Studio) is throwing the below error

QE-DEF-0478 Invalid coercion from 'level' to 'string'

I am tryig to create a data item D1 as

D1 = If (Status = 'A') Then ([3-5 days] + [5-9 Days]) Else (Null). How to do it?
STATUS is a dimension and 'A' is a member.
[3-5 days] and [5-9 Days] are members of another Dimention 'AGE TYPE'

I cannot give a filter on the query, as i need to calculate totals for different combinations.

I can create multiple queries, each with a different filter, but it is not allowing me to join queries (Just like the way we do on relational data sources)

I am new to reporting on dimentional data, how to do this?

[CognosPaul]

It looks like you're trying to build queries on cubes the same way that you would build them on a relational system. First thing, abandon the relational way of thinking - it will only slow you down.

Think of a Rubix cube, 3 dimensions. You have rows, columns, and a third dimension that can only be shown one slice at a time. The cube itself describes a fourth dimension, a measure. In effect, each block can be viewed as an intersection between all four dimensions. You can perform all sorts of interesting tricks on the edge to find and display desired members. Once you fully understand this bit, the only hard part is trying to figure out how to describe what you want to appear in the edge.

Now I suspect that you have a crosstab, something in the rows, and this calculated field is supposed to be one of several columns. There are a few ways to go about doing this. Let's go for the easiest way. You want this column to show the intersection [A] and [3-5 days] plus the intersection of [A] and [5-9 days]. So let's convert that into an acceptable expression:

tuple([A],[3-5 days]) + tuple([A],[5-9 days])

That's good, but let's say that you had 10 members you wanted to combine. There are a few other ways.

You can explicitly state how you want measures to aggregate against a static list of members. Take a look at the member summaries section. In this example, we'll use total. You want to total the intersection between your measure and [A] for several members from the Age Type dimension. Converting that into an expression might give us:

total(tuple([A],currentMeasure) within set set([3-5 days],[5-9 days]))

You could also simply do
total(currentMeasure within set set([3-5 days],[5-9 days]))
and nest that under status in the crosstab.

Now let's try a different method that is a little more advanced. Tuples, or intersections, can only be done between members and measures. In your case, you have a set. You can create a calculated member to feed a tuple. The benefit of this is that if you create the calculated member inside another data item, you can use it in other places. If you were to put this in a set, your set would use the custom caption.

3-9 Days: member(total(currentMeasure within set set([3-5 days],[5-9 days])),'3-9 days code','3-9 days caption',[Cube].[AGE TYPE].[Hierarchy])
This creates a calculated member, with the code 3-9 days code inside the hierarchy.

D1: tuple([A],[3-9 Days])
Since [3-9 Days] is a calculated member, you can use it in a tuple without error.

[cognostechie]

Awesome post Paul ! You are so good at it !

For SAM2013 -

Another way to do it is to do it in the cube itself (presuming it's a Transformer Cube)

Create a query calculation in the query that is the data source for the Cube with the calculation:

If (Status = A) then ('3 - 5 Days')
Else If (Status = B) then ('6 - 9 Days')
Else If (.........)
End

By the way, something wrong with your logic here because after 3-5 Days, it should be 6 - 9 Days.  5 cannot belong to two Age Types !

Create either an alternate hierarchy or assign the A,B.C values as short names and 3 - 5 Days as the Long Names for the same level of the same dimension.

Then you can just use it in the report instead of having to do all this calculation. This will let you use both the Status and the Age Type. It can also be used in Business Insight Advanced.

[SAM2013]

Thanks for the detailed explanation Paul. I got my report.

I am trying to see the Dimentional concept, but still not clear on how to visualize the data dimentionally-rubics cube way..

I can't see the tabular data here, is there any document/material that can help in understanding the dimentional data concept.

In relatyional data, i can clearly imagine the source data in a tabular format, can create calculations, joins etc and I can clearly see what my report is doing. But this dimentional concept is confusing me.


I am trying to understand the concept. Any help/document would be greatly appreciated. Thanks

[calson33]

I asked pretty much the same question and got some good responses -
http://www.cognoise.com/index.php/topic,21437.0.html

[cognostechie]

One thing to add to all this nice links is to understand how the data is stored in the Cube as opposed to how it is stored in a relational database. However, that is something only those people try to understand who have access to the cube and not just to the reporting tool. Like in this case, I didn't realize that the OP has access only to the reporting tool so it wasn't neccecary for me to provide the optimum solution which would be built inside the cube itself . Anyway, we all learn from experiences  ;) 

[CognosPaul]

Getting to the point where you can visualize multidimensional cubes can take some time. First try to imagine a normal cube. Start with a solid block. That's a tuple between the top member of all dimensions. Split it in half, and that means you're drilling down on one of the dimensions. It's sort of like the riddle, how can you cut a cake into 8 equal pieces with only three slices?

Then try to picture a tesseract. It's a simple matter of adding another dimension to the cube. Now stop before you break your brain.

One of the things I try to emphasize is to, unless you can easily imagine tesseracts and hypercubes, avoid trying to visualize the entire query. If you try too hard you'll end up drinking absinth and writing bad poetry. Instead, realize that each data item can be considered a mini-query, and focus on figuring out how one set will interact with another set.

There is a learning curve. And that learning curve is fairly steep at the beginning, simply because of how similar it looks to SQL. Once you do fully understand MDX, you'll be ruined for life because SQL will suddenly become slow and cumbersome and impossible to work with.

A few tips, don't ever use detail filters. I wrote a nice article about that here and here. That last one also goes into why you should avoid using non-olap functions in olap reports. And since I wrote that last one, I've learned a bit and my opinion of drill downs have improved.

I've written other posts on Cognoise on writing olap queries, take a look here.