Cognos Analytics with Watson

 View Only
Expand all | Collapse all

Filter Function using Data Module

  • 1.  Filter Function using Data Module

    Posted Fri July 01, 2022 04:58 PM
    I'm trying to use the FILTER function in creating a Query data item using Cognos Analytics v11.1 and it does not seem to work. I'm able to get it to work on other relational datasets, but not on a data module that I created. I'm using a very basic "filter([Store_CD],[Store_CD] = 'AB')" and it renders no results. Tried doing some work around using case statements but I've been unsuccessful - read somewhere on IBM that it is a bug that was fixed with 11.1.7 - is this true?

    ------------------------------
    corrie briscoe
    ------------------------------


  • 2.  RE: Filter Function using Data Module

    Posted Mon July 04, 2022 10:11 AM

    Hi Corrie

    There is no filter function.

    You say you are trying to create a query item with that expression.  Can you go into a bit more detail about what you are trying to accomplish so that I can understand that and recommend an approach to the problem.  It strikes me that a clearer understanding could enable you to be successful.

    I'm guessing that you are trying to filter a column called store_cd so that only those records which have the value AB are returned.  What you are trying is not a supported method.  There are methods to perform filtering. I've included a link to a blog topic I wrote which might help.   Modules have a fairly extensive set of filtering methods and they provide flexibility to the modeller.  You can create an embedded filter on a query item or using the expression editor.   You can also create a stand alone filter, either in the query subject or in the module, which can be added to your report to apply the filter.  

    You are using square brackets in your expression.  Are you using a FM package as your data source?

    Here is a little bit about the various filter types available.

    https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2020/12/22/module-filter-types 



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 3.  RE: Filter Function using Data Module

    Posted Mon July 04, 2022 10:28 AM
    Thanks for the response. I'm not using a FM model, but that's what I've typically always used, and what I was trained on. I'm starting to read/realize that using a Data Module requires a different set of coding for reporting authoring.

    I use the FILTER function a lot in the FM models so was hoping to be able to use it in Data Modules source type as well. We use FILTER() and EXCEPT() and SET() expressions frequently with our FM models but since moving to Cog11 we are starting to use Data Modules more. 

    Is there some good documentation on how to author reports specifically when using a Data Module and/or SQL data source?

    ------------------------------
    corrie briscoe
    ------------------------------



  • 4.  RE: Filter Function using Data Module

    IBM Champion
    Posted Tue July 05, 2022 06:32 AM
    The functions you're referencing are dimensional functions. Technically they are supported in Data Modules, but the implementation is sketchy at best. I'm guessing your FM models are all DMR? You'll need to change your mindset for Data Modules - consider them as purely relational and use the Detail and Summary filter panes. 



    I personally find dimensional queries significantly easier than relational, and it is truly infuriating to me that Data Modules don't support DMR modeling.

    ------------------------------
    Paul Mendelson
    ------------------------------



  • 5.  RE: Filter Function using Data Module

    Posted Tue July 05, 2022 08:05 AM
    The issue with Summary/Detail filter pane is that I don't want to filter the whole report; I just want to create a Filtered Data Item and use it in the report. And yes, all DMR knowledge so far, so this new Data Models concept is tough. We were sold on the functionality of an upgrade so that we can use Data Modules and they are more responsive now however, we were not informed that the limitations would be significant. I've searched endlessly for some good documentation on how to author reports for relational data modules and have yet to find anything. I know I can create separate queries and join them as to be able to use the Summary/Detail filters but its a lot of extra work. 

    Hoping at some point I can be directed towards some materials that will help code for relational models.

    ------------------------------
    corrie briscoe
    ------------------------------



  • 6.  RE: Filter Function using Data Module

    Posted Tue July 05, 2022 10:30 AM

    Hi Corrie,
    Sorry to hear about the difficulties that you're having.
    Have you tried an expression like:
        case when [country] = 'AB' then [quantity] end
    The regular aggregate can be set to control the summary operation.
    You can also code the summary operation into the expression and set the regular aggregate to 'calculated'. Something like:
        total ( case when [country] = 'AB' then [quantity] end) 
    With those kinds of expressions you can also define the scope of aggregation as in:
       total( case when [country] = 'AB' then [quantity] end    for [product line]  )
    The expression reference guide on operators, such as case expressions can be found at: Operators - IBM Documentation

    I hope this helps.



    ------------------------------
    Kind regards,
    HENK CAZEMIER
    ------------------------------



  • 7.  RE: Filter Function using Data Module

    Posted Tue July 05, 2022 10:34 AM
    Hi Corrie,

    I think you might want to create a singleton.

    You can insert a single data item anywhere in your report using the singleton object. The singleton object retrieves only the first row value for that query. Inserting a single data item is useful when you want to show a value that is independent from the rest of the values in the report or when you want to insert some boilerplate text, such as a company name and address.

    For example, you can add the total revenue value in the header of each page in a report.

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------



  • 8.  RE: Filter Function using Data Module

    IBM Champion
    Posted Wed July 06, 2022 07:02 AM
    When building on relational sources, like DM, you have a few tricks that you can use.

    In your case it sounds like you only want one data container to show data for AB while everything else shows the total? Why not make a reference query and filter that? 


    Can you describe in more detail what you're trying to do?

    ------------------------------
    Paul Mendelson
    ------------------------------



  • 9.  RE: Filter Function using Data Module

    Posted Fri July 08, 2022 03:40 PM
    Thanks so much for the reply Paul. The query idea works but due to the size of the report, and the number of filters I require I'd prefer to not have multiple queries to maintain. I've decided to abandon the Data Module method and just create a FM Model instead - however, what I was trying to do is create several Query Data Items to filter data to create seperate list reports on one page which is why Detail & Summary filters won't work. 

    ie; 
    filter([GRP],[GRP] = 'MRKPP')
    in another example
    filter ([STORE_CD],[STORE_CD] = 'AB')

    I'm still not understanding why the same functions can't be used in all data source types - seems like a bit of a miss

    ------------------------------
    corrie briscoe
    ------------------------------



  • 10.  RE: Filter Function using Data Module

    IBM Champion
    Posted Mon July 11, 2022 10:17 AM
    The difference is that DMR/OLAP use a different underlying querying language. Dimensional reporting is based on MDX whereas Relational is based on SQL. Personally MDX is significantly easier to work with than SQL for reporting, especially when dealing with hierarchies and comparing members. 

    Can you describe what you're trying to do? It sounds like you could use a master/detail relationship to get the same output. Drag in a repeater and pull in GRP. Then pull in list, populate that the way you want. Then use master/detail to link GRP to GRP.

    ------------------------------
    Paul Mendelson
    ------------------------------



  • 11.  RE: Filter Function using Data Module

    Posted 30 days ago
    Thanks so much for the reply Paul. I tried your suggestion, however, its showing UNAVAILABLE for Master Detail Relationship option. 

    I'm trying to create several list reports on the same page that have varying filtered [GRP] data members. But I'm not using ALL members in GRP. I'm trying to create a filter on GRP so that each list is based on that filtered data. I'm also trying to avoid using multiple Queries, which is the only way I've been able to make this work (outside of FM modeling).

    I agree that MDX is definitely easier then SQL - was using the data module approach for easy, simple, 'flat' reports instead of creating a FM Model, but with no documentation to support the differences and what isn't available I am finding that Data Modules really isn't a functional option.

    Thanks again.

    ------------------------------
    corrie briscoe
    ------------------------------



  • 12.  RE: Filter Function using Data Module

    IBM Champion
    Posted 30 days ago
      |   view attached

    Instead of using filters, DM actually exposes the individual members as MUNs. You can't really see it from inside reporting, but you can from DM:


    In the properties you'll see: 


    That identifier is basically the MUN, and you can use it as such.

    Americas looks like: [C].[C_Great_outdoors_sales_data_module].[page_9].[Region]->[Americas] 
    Asia Pacific would be: [C].[C_Great_outdoors_sales_data_module].[page_9].[Region]->[Asia Pacific]

    Stick them into data items, and drag them in exactly the way you would with a dimensional source:


    The interesting thing is you can use dimensional functions, but getting the hierarchy is a bit difficult. This works: topCount(set([Americas],[Asia Pacific]),1,[Revenue])

    If you need to reference currentMember, use the hierarchy function: tuple(prevMember(currentMember(hierarchy([Americas]))),[Revenue])

    Extending it further we can call the hierarchy and see the all member, and even get the MUN of the all member with roleValue('_memberUniqueName',currentMember(hierarchy([Americas])))


    I've attached the report xml. It's based on the Great outdoors data module in the 11.2 samples. 



    ------------------------------
    Paul Mendelson
    ------------------------------

    Attachment(s)

    txt
    report.txt   15 KB 1 version