If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

macro inside fuction parallelperiod is not working

Started by HalfBloodPrince, 16 Nov 2018 04:49:29 AM

Previous topic - Next topic

HalfBloodPrince

Hi Experts,

we have different cube for each country. So we are using macro pull query item based upon the  country parameter. But in parallelperiod function is  working when we select county on prompt page for the first time. but after that if try to change country it gives below error
The model item '.[TIME (ALL)].[TIME (ALL)].[MONTH]' is invalid.

we are using the below code
parallelPeriod (#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].TIME (ALL)].[TIME (ALL)].[MONTH]')#,0,#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?)

CognosPaul

That snippet really confuses me. Why use it if you have a 0 in there? Are you using it elsewhere? Also, mixing macros and aliases like that is an interesting choice. What value are you passing to p_Month?

Can you paste in a full example of a mun?

HalfBloodPrince

#2
we are trying to compare current year and month  data with previous available data.
For example if we are passing 2017 and 06 , report should show data data for the 2017/05. if 2017/05 data is not available then it should show data for 2017/04 or any previous available data.

CognosPaul

I suspect that the combination of prompt macros and alias is what's causing the error you're seeing. Try changing the alias to a macro and see what happens. Can you paste in an example of a mun?

parallelPeriod will give you a specific month, but it sounds like you really want only the last month with data. How about something like:

item(tail(filter(except(monthLevel,selectedMonth),measure is not null)),0)

That returns the last month with data as a single member.

HalfBloodPrince

Thanks a lot for the reply ,CognosPaul. I tried ur code . its giving me the previous data  for example we have 2018-6,2018-3 and 2017-12 data. when i select month 2018-6 it gives me correct out put of 2018-3 but when i select 2018-3 it is not giving me 2017-12 data.

CognosPaul

can you paste in the expressions for both items?

HalfBloodPrince

current data query :- parallelPeriod (#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].TIME (ALL)].[TIME (ALL)].[MONTH]')#,0,#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?)

previous data query :parallelPeriod (#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].TIME (ALL)].[TIME (ALL)].[MONTH]')#,1#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?)

CognosPaul

Try this:

current data: #prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[MONTH]')# ->?p_Month?
previous data:

item(tail(filter(except(periodsToDate(#prompt('p_Country', 'token', '', '[COUNTRY_', '', '].[(TIME ALL)].[TIME (ALL)].[ALL LEVEL]')#),[current data]),measure is not null)),0)

periodsToDate to will take the top level of the time dimension, generating all of the members from the beginning of the cube to the selected member. Filter will limit that to only the members with data, tail returns the last one as a set, and item will extract the member from the set.

If that doesn't work then you need to check if there are any other members that the measure need to be tupled on. It may be possible that the filter isn't including items from the slicer.