Topic
  • 5 replies
  • Latest Post - ‏2013-10-09T06:42:18Z by rperzul
Joe_316
Joe_316
3 Posts

Pinned topic How to check if Member Exists

‏2013-10-05T06:41:31Z |

In my query, I have Data Item with the following Expression

[ABC].[X1] ->: [PC].[@MEMBER].[X]

but sometimes that Member will not exist because of no data usage.

So when I run the report, I receive an error message that the Member does not exist.

How do I check if that Member exists?

Thanks.

  • rperzul
    rperzul
    25 Posts

    Re: How to check if Member Exists

    ‏2013-10-07T09:01:22Z  

    Hi,

    you actually cannot check this. If you are working with members directly the report will fail if it does not exist in the datasource. This is the standard Cognos behavior if reporting against dimensional sources.

    Nevertheless, you could try the following expression:

    item(filter([sales_and_marketing].[Products].[Products],caption(currentMember([sales_and_marketing].[Products].[Products]))='Camping Equipment');0)

    Just replace [sales_and_marketing].[Products].[Products] with the hierarchy of your member and 'Camping Equipment' with the business description of this member. If the string does not exist the member will not be part of your (visible) result, e.g. crosstab, etc.

    Regards,

    Ron

  • Joe_316
    Joe_316
    3 Posts

    Re: How to check if Member Exists

    ‏2013-10-08T04:13:07Z  
    • rperzul
    • ‏2013-10-07T09:01:22Z

    Hi,

    you actually cannot check this. If you are working with members directly the report will fail if it does not exist in the datasource. This is the standard Cognos behavior if reporting against dimensional sources.

    Nevertheless, you could try the following expression:

    item(filter([sales_and_marketing].[Products].[Products],caption(currentMember([sales_and_marketing].[Products].[Products]))='Camping Equipment');0)

    Just replace [sales_and_marketing].[Products].[Products] with the hierarchy of your member and 'Camping Equipment' with the business description of this member. If the string does not exist the member will not be part of your (visible) result, e.g. crosstab, etc.

    Regards,

    Ron

    Thank you Ron for your kind help.

     

    Here is the problem.  In a query, I have a data item with Set([X], [Y], [Z]) called Set_XYZ.  [X] is using an avg function with member [Mem_X]. 

    [Mem_X] = [ABC].[X1] ->: [PC].[@MEMBER].[X]

    Same for Y and Z.

    Sometimes [Mem_X] does not exist so I want to exclude [X] in the set data item called Set_XYZ.  For example, Set([Y], [Z])

    How do I this?

     

     

  • rperzul
    rperzul
    25 Posts

    Re: How to check if Member Exists

    ‏2013-10-08T08:18:34Z  
    • Joe_316
    • ‏2013-10-08T04:13:07Z

    Thank you Ron for your kind help.

     

    Here is the problem.  In a query, I have a data item with Set([X], [Y], [Z]) called Set_XYZ.  [X] is using an avg function with member [Mem_X]. 

    [Mem_X] = [ABC].[X1] ->: [PC].[@MEMBER].[X]

    Same for Y and Z.

    Sometimes [Mem_X] does not exist so I want to exclude [X] in the set data item called Set_XYZ.  For example, Set([Y], [Z])

    How do I this?

     

     

    Would it be possible to share your report spec with us? At least the part with the data items of concern.

  • Joe_316
    Joe_316
    3 Posts

    Re: How to check if Member Exists

    ‏2013-10-09T04:58:18Z  
    • rperzul
    • ‏2013-10-08T08:18:34Z

    Would it be possible to share your report spec with us? At least the part with the data items of concern.

    Sorry, I'm unable to provide the requested info.

  • rperzul
    rperzul
    25 Posts

    Re: How to check if Member Exists

    ‏2013-10-09T06:42:18Z  
    • Joe_316
    • ‏2013-10-09T04:58:18Z

    Sorry, I'm unable to provide the requested info.

    ... or try to replicate the behavior using Cognos samples like the sales and marketing cube? I have difficulties in figuring out how certain things are supposed to work in your environment like the avg function being part of a set ?!