Dynamically Changing OLAP Query Data Item Source???

[torre2011]

I am strugling with a problem of changing a data item source based on a prompt selection.  Now the source of my reports is a Dynamic Cube.  So my prompt has 2 values, and within a query I need to change the data item..so I thought I could do something like this within the data item:

CASE ?pTarget?
WHEN [QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[HEDIS] then [QR].[HDim].[HDim].[Measure]
ELSE [QR].[IHDim].[IHDim].[Measure]
END

I get the following error: RSV-VAL-0010 Failed to load the report specification. XQE-GEN-0010 Found an internal error: 'The number of the columns of set operation are not equal.'

I have attemtped to use a prompt macro as well..and received the same error???

I know that this is possible...just unsure if it is because of how I am constucting it or if it is related to the how the children of the inserted data is viewed??

Any ideas?

If I get more information as I work on it, I will update this thread

[torre2011]

I am currently working within Cognos 10.1.1, so I am wondering if there is a defeciency here.  The reason I say this is that I noticed a new feature within 10.2.1 called case macro function!

This would allow me to build my dynamic data item like this:

Code Select Expand

# case prompt('pTarget', 'token')
when 'H'
then [QR].[HDim].[HDim].[Measure]
else ([QR].[IH Dim].[IH Dim].[Measure])
end
#


So, not sure if that would solve my issue.  Well..I am still plugging along here  :-\

[MFGF]

I am currently working within Cognos 10.1.1, so I am wondering if there is a defeciency here.  The reason I say this is that I noticed a new feature within 10.2.1 called case macro function!

This would allow me to build my dynamic data item like this:

Code Select Expand

# case prompt('pTarget', 'token')
when 'H'
then [QR].[HDim].[HDim].[Measure]
else ([QR].[IH Dim].[IH Dim].[Measure])
end
#


So, not sure if that would solve my issue.  Well..I am still plugging along here  :-\

I would use an entirely different approach:

Define your Target prompt with Static Choices. Set the Use Values to be the MUNS of your measures (ie [QR].[HDim].[HDim].[Measure] and [QR].[IH Dim].[IH Dim].[Measure]) and set the Display values to be Measure1 and Measure2.

Code the query calc you use for the measure in your crosstab as as #prompt('pTarget','token','[QR].[IH Dim].[IH Dim].[Measure]')#

Cheers!

MF.

[torre2011]

MFGF,

I took your approach and it is working...the only thing now, is that I have run into an obstacle with the prompt.  Here is why, this prompt was supposed to be used to filter on 2 different queries.  One query was resolved through the approach of the static options, but the static values were specific to the query.

So for the 2nd query, I was thinking that I could use a data item to test the value of the selected prompt and then change the filter of this query accordingly.  For example lets say the prompt selection was 'Apples' and the 'Use Value' is all the members within the path we hardcoded into the prompt static choices (i.e Red, Yellow, Green. etc.).  As I stated that will work great within the 1st query which uses the prompt macro within the data item to dynamically change the data item values.

But lets say the 2nd query needs to know what was selected from the prompt to capture a specific member value.  If the selection was 'Apple' then we would have a filter in this query like [Fruit Dim].[Fruit Type] = ?pFruit?.  This would give me 1 value for the selected prompt.

Ok..so i can NOT use the static prompt value for this 2nd query...it just does not apply, but the DISPLAY VALUE is what I need!  So I tried to do the following within a data item in the 2nd query:

CASE ?pFruit?
WHEN (whatever the static prompt value) THEN 1
ELSE 2
END

But this throws an error: The argument at position 1 of the function 'WHEN' is invalid in data item 'Data Item1' of query 'qryFruit', expected one of the following types: 'value'.

Does my issue make sense???

[MFGF]

Hi,

Can you tell us the necessary MUN you need for the second query? It may be that we can adapt the approach to prompt for just part of the MUN and assemble the rest using macros in each query? It's not possible to be certain unless we know what you need, though. We already have [QR].[HDim].[HDim].[Measure] and [QR].[IH Dim].[IH Dim].[Measure] for the first query (or was this just a non-specific example?) - what do we need for the second?

Cheers!

MF.

[torre2011]

Sorry for any confusion!

So the prompt has the Display Values of 'IH' and 'H' with respective Static Values as [QR].[IHDim].[IHDim].[Measure] and [QR].[HDim].[HDim].[Measure].

The 2nd query that i was referring to in my previous reply can not use the static values,  instead I just need to know that they selected either 'IH' or 'H'.  Since this 2nd query is using a different dimension, the mun I require to get the specific value will be different..but for this discussion lets say it is [QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[H]

Does this help you to assist me?

[MFGF]

Hi,

Yes - perfect!

The approach I would use would be:

1. Change your prompt so that the static (use) values are H and IH
2. Change your current measure expression in the first query to be #'[QR].[' + prompt('pTarget','token','IH') + 'Dim].[' + prompt('pTarget','token','IH') + 'Dim].[Measure]'#
3. Add a query calculation to your second query with the expression #'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#

Hopefully this should give you what you need?

MF.

[torre2011]

This looks like it could work...only thing is that I am receivng the following error when i attempt to run the query outside of tabular view:

V5 syntax error found for data item 'Measure' of query 'qry2', invalid token "Dim" found after "([QR].[[QR].[HEDIS Dim].[H Dim].[Measure] ".

Whats strange is that it works perfectly fine when run in tabular view, but not in html!  So I made a change and used this same macro as a filter..which is really what I want to do with it.  Here is what I have:

[QR].[Target Type Dim].[Target Type Dim].[Target Type] = (#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#)

Again, this will filter exactly what I want when i run in tabular...but as soon as put a list on the report and bind it to this query, and run in html, i get an error..like this:

V5 syntax error found in expression "[QR].[Target Type Dim].[Target Type Dim].[Target Type] = ([QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[[QR].[H Dim].[H Dim].[Measure]]) ", invalid token "]" found after "[QR].[Target Type Dim].[Target Type Dim].[Target Type] = ([QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[[QR].[H Dim].[H Dim].[Measure]".

I am not sure why it is compiling like it is described in the error details...where you see the Dim Measure level being inserted with the macro from the 2nd query???

Any thoughts??

[MFGF]

This looks like it could work...only thing is that I am receivng the following error when i attempt to run the query outside of tabular view:

V5 syntax error found for data item 'Measure' of query 'qry2', invalid token "Dim" found after "([QR].[[QR].[HEDIS Dim].[H Dim].[Measure] ".

Whats strange is that it works perfectly fine when run in tabular view, but not in html!  So I made a change and used this same macro as a filter..which is really what I want to do with it.  Here is what I have:

[QR].[Target Type Dim].[Target Type Dim].[Target Type] = (#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#)

Again, this will filter exactly what I want when i run in tabular...but as soon as put a list on the report and bind it to this query, and run in html, i get an error..like this:

V5 syntax error found in expression "[QR].[Target Type Dim].[Target Type Dim].[Target Type] = ([QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[[QR].[H Dim].[H Dim].[Measure]]) ", invalid token "]" found after "[QR].[Target Type Dim].[Target Type Dim].[Target Type] = ([QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[[QR].[H Dim].[H Dim].[Measure]".

I am not sure why it is compiling like it is described in the error details...where you see the Dim Measure level being inserted with the macro from the 2nd query???

Any thoughts??

Hi,

I'd strongly recommend you steer clear of detail filters as you're using a dimensional source!

You are seeing the error

V5 syntax error found for data item 'Measure' of query 'qry2', invalid token "Dim" found after "([QR].[[QR].[HEDIS Dim].[H Dim].[Measure] ".

Where did the opening round bracket come from? Also, why are there two square brackets before the second QR? Why is there even a second QR? Even without these issues, why is the macro resolving to "[QR].[QR].[HEDIS Dim].[H Dim].[Measure] instead of [QR].[HDim].[HDim].[Measure]?

Can you check that the Use Values in your prompt are simply H and IH? Is there a Default Selection defined? If so, it needs to be either H or IH.

It sounds like the prompt macro is still resolving to a MUN rather than just H or IH. Once we fix that it should work.

Cheers!

MF.

[torre2011]

I forgot I had a default value...so that resolved the V5 syntax, but know I am getting this error:

Internal error. The expression has invalid combination of hierarchies.

The only thing I can think of is that there is a data item within this query that is a calculation based on a value from the 2nd query!  Here are the data items with any associated expressions in order for query 1:

• Group Type - dimension level
• Measure - (#'[QR].[' + prompt('pTarget','token','IH') + ' Dim].[' + prompt('pTarget','token','IH') + ' Dim].[Measure]'#)
• Quarter - closingPeriod([QR].[Time Dim].[Time Dim].[Quarter])
• Num - total([Numerator] for [Group Type])
• Den - total([Denominator] for [Group Type])
• Numerator - these are facts
• Denominator - these are facts
• Score - ([Num]/[Den])*100
• Performance - [Score] - caption([qry2].[Target Rating])

So, you can see I am using the data item value from query2 for the Performance calc...and query2 requires it to be filtered so as to give on Target Rating.  Below is how query 2 looks:

• Target Type - dimension level
• Target Rating - dimension level
• Category- (#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#)
  

And to answer your other question...yes this is an OLAP source...I am actually using a Dynamic Cube.  So, i understand that I want to refrain if possible from using detail filters...so i would just need to know how to use a slicer or some other approach to filter query 2.

Does this make sense?

[torre2011]

So I made a slight adjustment to the calculation I was referring to within query1...here is what I have:

[Score] - caption(children(#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#))

This works as long as I do not add the Performance data item to the right edge of the crosstab....when I do that i get this error:

The hierarchy already belongs to the projected hierarchy list.

Any ideas???

[MFGF]

I forgot I had a default value...so that resolved the V5 syntax, but know I am getting this error:

Internal error. The expression has invalid combination of hierarchies.

The only thing I can think of is that there is a data item within this query that is a calculation based on a value from the 2nd query!  Here are the data items with any associated expressions in order for query 1:

• Group Type - dimension level
• Measure - (#'[QR].[' + prompt('pTarget','token','IH') + ' Dim].[' + prompt('pTarget','token','IH') + ' Dim].[Measure]'#)
• Quarter - closingPeriod([QR].[Time Dim].[Time Dim].[Quarter])
• Num - total([Numerator] for [Group Type])
• Den - total([Denominator] for [Group Type])
• Numerator - these are facts
• Denominator - these are facts
• Score - ([Num]/[Den])*100
• Performance - [Score] - caption([qry2].[Target Rating])

So, you can see I am using the data item value from query2 for the Performance calc...and query2 requires it to be filtered so as to give on Target Rating.  Below is how query 2 looks:

• Target Type - dimension level
• Target Rating - dimension level
• Category- (#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#)
  

And to answer your other question...yes this is an OLAP source...I am actually using a Dynamic Cube.  So, i understand that I want to refrain if possible from using detail filters...so i would just need to know how to use a slicer or some other approach to filter query 2.

Does this make sense?

Ok - some observations:

1. Your calculation for your measure is in brackets (). Is there any reason for this? They seem unnecessary.
2. Num and Den (4 and 5) are using a relational total(xxx FOR yyy) summary rather than a dimensional total([measure] within set [set]) summary. I'd recommend against using relational constructs like this against a dimensional source. It might even be simpler to use a tuple() instead if it's for a single aggregate member?
3. You appear to be subtracting a caption from a measure in your Performance item(9)? Can you explain this?

I'm thinking point 9 is your problem here. Why do you have multiple queries? Are they separate cubes? If so, you can't mix and match like this - different cubes can't be "joined" within a query. You can, however, set up master/detail relationships between the reporting objects (ie crosstabs charts etc) from different cubes - as long as one is embedded within the other.

If the two queries are from the same cube, then you don't need two queries - you can do everything you need in a single query.

Can you explain in a little more detail?

Cheers!

MF.

[torre2011]

MFGF,

Since yesterday i made adjustments which coincide with some of your observations!  I am able to get this crosstab to work but it will not present like i need it to.  Here is what my query looks like now:

1.Group Type - dimension level
2.Measure - (#'[QR].[' + prompt('pTarget','token','IH') + ' Dim].[' + prompt('pTarget','token','IH') + ' Dim].[Measure]'#)
3.Quarter - set(lag(closingPeriod([QR].[Time Dim].[Time Dim].[Quarter]),1),closingPeriod([QR].[Time Dim].[Time Dim].[Quarter]))4.Num - total([Numerator] for [Group Type])
4.Num- [QR].[Measures].[Numerator]
5.Den - [QR].[Measures].[Denominator]
6.Score - ([Num]/[Den])*100
7.Performance - [Score]  - caption(children(#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#))

So i removed references to the 2nd query by imbedding the prompt macro into the calculation.  To answer your question about using the caption for the calc..its because the value I am using is not a fact but rather an attribute within a dimension, and for this project I have been asked to use it for this calc.

So, with the above data items, my crosstab is constructed using the Group Type as the rows, Q for the columns and then I nested the Score under the Q.  I then put the Performance calculation to the far right but not under the Q.  When I run this, the Score comes out correctly but the Performance is blank!  When I move the Performance under the Q, I then see the Performance for both quarters.  This would be fine, but that is not what I have been asked to show...instead the Performance that needs to be shown is ONLY for the last quarter (currentPeriod)!

I am not sure as to why the Performance data item will not show unless it is nested under the time dimension..but maybe it makes sense...i just need to figure out how to get it to show just for the one quarter????

THis is just one of 4 crosstabs in a dashboard I am building...so once I can figure this out, then I should be on my way!

I want to say thanks for all your assistance thus far, it has definitely helped with some of these more advanced OLAP techniques!

[MFGF]

MFGF,

Since yesterday i made adjustments which coincide with some of your observations!  I am able to get this crosstab to work but it will not present like i need it to.  Here is what my query looks like now:

1.Group Type - dimension level
2.Measure - (#'[QR].[' + prompt('pTarget','token','IH') + ' Dim].[' + prompt('pTarget','token','IH') + ' Dim].[Measure]'#)
3.Quarter - set(lag(closingPeriod([QR].[Time Dim].[Time Dim].[Quarter]),1),closingPeriod([QR].[Time Dim].[Time Dim].[Quarter]))4.Num - total([Numerator] for [Group Type])
4.Num- [QR].[Measures].[Numerator]
5.Den - [QR].[Measures].[Denominator]
6.Score - ([Num]/[Den])*100
7.Performance - [Score]  - caption(children(#'[QR].[Target Type Dim].[Target Type Dim].[Target Type]->:[RO].[Target Type Dim].[Target Type Dim].[All].[' + prompt('pTarget','token','IH') + ']'#))

So i removed references to the 2nd query by imbedding the prompt macro into the calculation.  To answer your question about using the caption for the calc..its because the value I am using is not a fact but rather an attribute within a dimension, and for this project I have been asked to use it for this calc.

So, with the above data items, my crosstab is constructed using the Group Type as the rows, Q for the columns and then I nested the Score under the Q.  I then put the Performance calculation to the far right but not under the Q.  When I run this, the Score comes out correctly but the Performance is blank!  When I move the Performance under the Q, I then see the Performance for both quarters.  This would be fine, but that is not what I have been asked to show...instead the Performance that needs to be shown is ONLY for the last quarter (currentPeriod)!

I am not sure as to why the Performance data item will not show unless it is nested under the time dimension..but maybe it makes sense...i just need to figure out how to get it to show just for the one quarter????

THis is just one of 4 crosstabs in a dashboard I am building...so once I can figure this out, then I should be on my way!

I want to say thanks for all your assistance thus far, it has definitely helped with some of these more advanced OLAP techniques!

Hi,

Hmmm. You still have brackets around your measure definition in 2. Is there a reason?

It's rather unorthodox to use a caption in a measure calc, but if it works I'm mightily impressed :)

What do you see if you create another calc - tuple([your current quarter member],[Performance])

Does it show a value or produce an error? If it shows a value, is it the value you need?

Cheers!

MF.

[torre2011]

Well...your help has allowed me to think outside the box!  I was able to get the results to appear as the business requested by changing to a List object rather than a crosstab...BUT...now I am not seeing the dynamic change to Numerator and Denominator which make up the components of my Score.

I am not sure if I explained that portion..but the help you provided with the prompt macro...was so that I could change the data item value within the query so as to change the calculation...so the database fact table which supplies the Numerator and Denominator facts has an attribute column called Measure ( very badly named)...so I need to sum up all the Numerators and Denominators for each Category row.  My challenge was when a user selects 'IH' from the prompt, this in turn affects the Measure columns associated with 'IH', thus changing the total Numerator and Denominator...( have i lost you).

So, the prompt macro that I imbedded into the data item called 'Measure' would work like described above...but it wont change the Num & Den data items unless the Measure data item is included within the list...but they do not want to see each row of the Measure displayed.  So I am trying to figure out how to dynamically changed the Num & Den calc based on the Measure macro????

What a crazy project this is!!!

[MFGF]

Well...your help has allowed me to think outside the box!  I was able to get the results to appear as the business requested by changing to a List object rather than a crosstab...BUT...now I am not seeing the dynamic change to Numerator and Denominator which make up the components of my Score.

I am not sure if I explained that portion..but the help you provided with the prompt macro...was so that I could change the data item value within the query so as to change the calculation...so the database fact table which supplies the Numerator and Denominator facts has an attribute column called Measure ( very badly named)...so I need to sum up all the Numerators and Denominators for each Category row.  My challenge was when a user selects 'IH' from the prompt, this in turn affects the Measure columns associated with 'IH', thus changing the total Numerator and Denominator...( have i lost you).

So, the prompt macro that I imbedded into the data item called 'Measure' would work like described above...but it wont change the Num & Den data items unless the Measure data item is included within the list...but they do not want to see each row of the Measure displayed.  So I am trying to figure out how to dynamically changed the Num & Den calc based on the Measure macro????

What a crazy project this is!!!

Instead of adding the measure value to the list, try adding it to the query, then selecting the List object, going to the Properties property of the list and selecting the Measure item. Might work?

MF.

[torre2011]

Unfortunately it still breaks down the list by the individual Measure, even though it was not directly included within the list object.

So, now I am wondering if there was a way to aggregate the Num & Den by the array of measures, which are dynamically changing based on the prompt???  Not sure if it is possible...but I am exploring all options..

Any other suggestions??  I am desperate ;)

[MFGF]

So, now I am wondering if there was a way to aggregate the Num & Den by the array of measures, which are dynamically changing based on the prompt???  Not sure if it is possible...but I am exploring all options..

Can you explain what you mean by this?

MF.

[torre2011]

Yea...sorry...i was just re-capping what i said previously but in a different way :)

So lets say the following measures with thier Numerator and Denominator  columns are associated with prompt value 'IH':

Apples - 5 - 10
Oranges - 2 - 20
Watermellon - 6 - 12

Total Num = 13
Total Den = 42
Score = 30%

Then for the prompt 'H':

Carrots -15 - 30
Tomatoes - 20 - 40

Total Num = 35
Total Den = 70
Score = 46%

So i am trying to dynamically change the base Num and Den totals depending on the prompt...

Does this help?

[karthik.kadambi]

The dynamic ?prompt? case when statement works in 10.2. When I tried that in 10.1 it gave me a syntax error. I switched to an if else statement and it worked properly.