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
------------------------------
Original Message:
Sent: Mon July 11, 2022 10:17 AM
From: Paul Mendelson
Subject: Filter Function using Data Module
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
Original Message:
Sent: Fri July 08, 2022 03:40 PM
From: corrie briscoe
Subject: Filter Function using Data Module
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
Original Message:
Sent: Wed July 06, 2022 07:01 AM
From: Paul Mendelson
Subject: Filter Function using Data Module
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
Original Message:
Sent: Tue July 05, 2022 08:04 AM
From: corrie briscoe
Subject: Filter Function using Data Module
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
Original Message:
Sent: Tue July 05, 2022 06:31 AM
From: Paul Mendelson
Subject: Filter Function using Data Module
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
Original Message:
Sent: Mon July 04, 2022 10:28 AM
From: corrie briscoe
Subject: Filter Function using Data Module
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
Original Message:
Sent: Mon July 04, 2022 10:11 AM
From: IAN HENDERSON
Subject: Filter Function using Data Module
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
Original Message:
Sent: Thu June 30, 2022 04:25 PM
From: corrie briscoe
Subject: Filter Function using Data Module
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
------------------------------
#CognosAnalyticswithWatson