COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: jway on 14 Dec 2017 12:34:13 PM

Title: Understanding error message
Post by: jway on 14 Dec 2017 12:34:13 PM
In an 11.08 report I want to filter the members displayed in a parameter by comparing the caption() to a value in a relational data item that's in another query.  I have this expression: 

Item(filter(members([Channel Transaction].[Organization].[Organization]) ,  caption(members([Channel Transaction].[Organization].[Organization])) =[QUserIDBridge].[Level03_Security] ),0)

Getting this error:  XQE-PLN-0243 Relational querySubjects defined using dimensional terms can not be supported. Term: "[Channel Transaction].[Organization].[Organization]" is referred from a relational querySubject.

I haven't had any luck googling this error.  Not sure what to do to resolve it ....
Title: Re: Understanding error message
Post by: MFGF on 15 Dec 2017 05:07:42 AM
Quote from: jway on 14 Dec 2017 12:34:13 PM
In an 11.08 report I want to filter the members displayed in a parameter by comparing the caption() to a value in a relational data item that's in another query.  I have this expression: 

Item(filter(members([Channel Transaction].[Organization].[Organization]) ,  caption(members([Channel Transaction].[Organization].[Organization])) =[QUserIDBridge].[Level03_Security] ),0)

Getting this error:  XQE-PLN-0243 Relational querySubjects defined using dimensional terms can not be supported. Term: "[Channel Transaction].[Organization].[Organization]" is referred from a relational querySubject.

I haven't had any luck googling this error.  Not sure what to do to resolve it ....

Hi,

The issue you have is that one query is MDX based and the other is SQL based, so you can't join or cross-join them as joining is a relational-only concept. The best you can do is to master/detail based on the caption of the members. This probably doesn't fit your requirement here, though.

Unless a genius like CognosPaul can pull a rabbit out of his hat, I guess you're out of luck here, sorry.

Cheers!

MF.
Title: Re: Understanding error message
Post by: prikala on 18 Dec 2017 12:28:27 AM
How about a parameter map based on query items?

If [QUserIDBridge].[Level03_Security] is something filtered by userid,
you could define the paremeter map in the FM having userid as key
and Level03_Security as value.
Then in the report you could use something like this:
   # map_Level03_Security{$account.personalInfo.userName} #
to get the parameter to compare to.
Title: Re: Understanding error message
Post by: jway on 19 Dec 2017 09:32:55 AM
I've never tried a parameter map before, would that work with a dimensional cube package ?
Title: Re: Understanding error message
Post by: prikala on 20 Dec 2017 06:14:40 AM
yes!
In this example the parameter map would return as string the caption you are looking for.
Actually my example was wrong: As this is a string, you need to add single quotes in the macro:
   # sq($map_Level03_Security{$account.personalInfo.userName}) #

You could actually construct member unique names in the FM and return those in the parameter map if that suits your needs.
Title: Re: Understanding error message
Post by: jway on 04 Jan 2018 02:52:59 PM
I've created a parameter map in FM and published out a package with it.  When I try to create a new report from this package I'm immediately getting an error (attached)    "This request requires prompting which is not currently supported"

I'm not understanding what's causing this, any ideas ??
Title: Re: Understanding error message
Post by: jway on 16 Jan 2018 03:51:21 PM
I figured out the cause of the error message i was getting, have multiple connections on this server and it wasn't able to prompt to select one.

Now however I'm trying to use the value from the parameter map as the default value in a prompt and am getting a syntax error.

Tried this filter expression:

caption([Channel Transaction].[Organization].[Organization]) = (#prompt('POrg','string','# sq($PMOrg{$account.personalInfo.userName})#')#)

But it doesn't set the prompt value so the filter doesn't execute.  Any ideas what else I could try ?
Title: Re: Understanding error message
Post by: CognosPaul on 16 Jan 2018 10:35:57 PM
The default value you have in the prompt macro doesn't need to be surrounded by quotes, and it doesn't need the hashes.

caption([Channel Transaction].[Organization].[Organization]) = (#prompt('POrg','string', sq($PMOrg{$account.personalInfo.userName}))#)


Also, I strongly urge you to abandon using detail filters when using OLAP environments. You may end up with unexpected results and it greatly reduces what you can do with the query.

Instead, try constructing the mun using macros.
#'[Channel Transaction].[Organization].[Organization]->[PC].['+prompt('POrg','string', sq($PMOrg{$account.personalInfo.userName}))+']'#
(assuming your muns look like that).

Stick it in your slicer and you should be golden.
Title: Re: Understanding error message
Post by: jway on 17 Jan 2018 10:52:09 AM
Thank you very much that worked!!!