Crosstab union query issue (source:TM1 / Cognos 10.2.1) - SOLVED

[ersin.gulbahar]

Hi experts,
I want to make a crosstab from one query and it is so simple.(I use TM1 as a source) It seems like 1.png which I added it as attachment.
When I change the query to union query it changes its behaviour. It looks different which I do not want it.
How can I solve this issue?

thanks in advance

[MFGF]

Hi experts,
I want to make a crosstab from one query and it is so simple.(I use TM1 as a source) It seems like 1.png which I added it as attachment.
When I change the query to union query it changes its behaviour. It looks different which I do not want it.
How can I solve this issue?

thanks in advance

Hi,

Why are you using unions between queries? If your data is in the TM1 cube, you can bring it into your crosstab all in a single query?

The image you posted seems to show entirely different data in rows in your second query vs your initial one. If this is the case, it is always going to look different - if you have multiple members to display, this is what you will see. It's not clear what you think the problem is here, and what you're trying to achieve - can you explain in more detail?

MF.

[ersin.gulbahar]

Thanks for answer,
I use one of the query(which has union query) And second image is from union query.(so data is not important) But I really need to use union query because I should get the last year data but not the top level.I need to edit some data to it look it as a [Surum] , actually I need to use union query, :(

[MFGF]

Thanks for answer,
I use one of the query(which has union query) And second image is from union query.(so data is not important) But I really need to use union query because I should get the last year data but not the top level.I need to edit some data to it look it as a [Surum] , actually I need to use union query, :(

No - you shouldn't need multiple queries for that. TM1 (or any dimensional package, in fact) supports you bringing in different year members or defining calculations based on different years without using multiple queries. You shouldn't need to do any usions or use more than one query in your report. If you can tell us what you are trying to do in a little more detail perhaps we can point you in the right direction?

MF.

[ersin.gulbahar]

Thanks for answer,
I want to show you details on paint. I hope you can understand it.

thanks in advance

[MFGF]

Thanks for answer,
I want to show you details on paint. I hope you can understand it.

thanks in advance

Your question seems to be how you can nest a value for 2013 underneath 2014 without using a union? Is that the crux of your problem?

If so, you can drag in a Query Calculation where you want to see the 2013 values, and code an expression that uses a Tuple() function. A tuple gives you the value at a specified intersection of members/measures. To get your measure value for 2013, your expression would be

tuple([2013 year member],[your version member if required],[your measure])

Here's an example I created using the sample great_outdoors_sales_en package:



Using multiple queries and unions is not a good strategy with dimensional sources - use dimensional functions instead.

Cheers!

MF.

[ersin.gulbahar]

Thanks for answer again,
I understand your way thanks about it. But the thing is last year is not static member,it depends on a parameter which is from prompt page.
So I use union query because in query(data from last year) I put filter like this : [TimeDimension]= ?last_year_parameter?

How can I do with tuple function.

Thanks in advance

[MFGF]

Thanks for answer again,
I understand your way thanks about it. But the thing is last year is not static member,it depends on a parameter which is from prompt page.
So I use union query because in query(data from last year) I put filter like this : [TimeDimension]= ?last_year_parameter?

How can I do with tuple function.

Thanks in advance

Yikes! You are using a detail filter???? Not a good idea!!

Code your tuple as

tuple([your year level from the time dimension] -> ?last_year_parameter?, [your measure])

My earnest advice is that you should not use detail filters with dimensional packages.

Cheers!

MF.

[ersin.gulbahar]

thanks for answer,,
I will try your suggestion now , But I want to know what is the meaning of -> symbol.

thanks in advance

[MFGF]

thanks for answer,,
I will try your suggestion now , But I want to know what is the meaning of -> symbol.

thanks in advance

It is a dimensional operator, used as part of the MUN (Member Unique Name) for each member in your cube. In this case we are using it to locate a member in the year level of your time dimension.

Cheers!

MF.

[ersin.gulbahar]

thanks again and again :),
I hope this is my last question :D
Can I put filter for just this tuple I mean I calculate last year dimension but I want to need filter about versions on it. I need just one version about last year data.

When I Use this tuple it works but It brings all data about last year. I need put filter for different dimension on this tuple.

I mean

tuple(

• .[y].[z]->?last_year?) this is fine but I need like this :
  
  case when caption([a].[bb].[c]) ='test' then tuple( [ x].[y].[z]->?last_year?) end
  
  how can I handle this?
  
  thanks in advance
  

[MFGF]

thanks again and again :),
I hope this is my last question :D
Can I put filter for just this tuple I mean I calculate last year dimension but I want to need filter about versions on it. I need just one version about last year data.

When I Use this tuple it works but It brings all data about last year. I need put filter for different dimension on this tuple.

I mean

tuple(

• .[y].[z]->?last_year?) this is fine but I need like this :
  
  case when caption([a].[bb].[c]) ='test' then tuple( [ x].[y].[z]->?last_year?) end
  
  how can I handle this?
  
  thanks in advance
  

Hi,

What is caption([a].[bb].[c]) ='test'  - are you referring to a specific member? If so, just include that member in the tuple function.

eg tuple([test member], [your year level]->?last_year?)

MF.

[ersin.gulbahar]

thanks for you help!!

[ersin.gulbahar]

Hi again,
last question :)

tuple([your year level from the time dimension] -> ?last_year_parameter?, [your measure])
this is work but How can I use this for multiple choices. Because it works only one choice .

thanks in advance

[MFGF]

Hi again,
last question :)

tuple([your year level from the time dimension] -> ?last_year_parameter?, [your measure])
this is work but How can I use this for multiple choices. Because it works only one choice .

thanks in advance

So you want to be able to select multiple Year members in the prompt and display a single aggregated value of your measure for those years?

How about

aggregate( [your measure] within set set([your year level from the time dimension] -> ?your Years parameter?) )

Is this what you require?

MF.

[ersin.gulbahar]

It is not measure. It is time dimension and I want to select months on the prompt then I want to see them with this
tuple([your year level from the time dimension] -> ?last_year_parameter?, [your measure]) so this is just select 1 value. How can I select multiple selection with this?

thanks in advance

[MFGF]

It is not measure. It is time dimension and I want to select months on the prompt then I want to see them with this
tuple([your year level from the time dimension] -> ?last_year_parameter?, [your measure]) so this is just select 1 value. How can I select multiple selection with this?

thanks in advance

Did you try the expression I suggested? Did it give you the desired result? If not, can you explain what result you require?

MF.

[ersin.gulbahar]

Did you try the expression I suggested? Did it give you the desired result? If not, can you explain what result you require?

MF.

I want to show selected year's months. So I need to use this to say selected years months : tuple([your year level from the time dimension] -> ?last_year_parameter?) But If I drag and drop levels(TIME,4) (these are months) It cant get what I want. I need to show selected year months and previous year last month in the same query.

thanks

[navissar]

Right. Here we go:
1. Tuple can only get one member from each hierarchy.
2. Now, if you want a set of all months selected, it should be something along the lines of set([your year level from the time dimension] -> ?last_year_parameter?)
Your tuple function could then use currentMember to tuple your measure for each month.
These are general lines, though. It'll be easier to help if we fully understood your requirement. You started off by asking a very particular question which was answered, but then it turns out there's a lot of the picture we've missed. It's better if you give us the whole picture, then we can give you a solution and save you some time.

[ersin.gulbahar]

thanks it works,

[navissar]

Well, colour me baffled.