The XML is a report spec that was developed in 11.2.4FP5.  It should be compatible across all of 11.2.4.

I have found that Cognos Analytics report features really don't change that much from the perspective of what is in the report spec.  Report specs are compatible with many versions, but Cognos checks the version number and throws errors based on that.  Maybe create a new report and inspect the report spec.  Change the version in my XML from 16.2 to whatever version your system produces.  You'll also need the samples packages (specifically, GO Sales (query)) installed in your environment.

Quote from: dougp on 11 Mar 2025 05:48:57 PMYou didn't say which RDBMS you are using.  If you can have SQL do the work for you, here's an example for SQL Server 2016 and later:

Code Select Expand

with
searchTerms as (
  select *
  from (
    values
      ('Test1')
    , ('Fox')
    , ('Cat')
    , ('Dog')
    , ('Test2')
  ) q (Term)
),
input as (
  select *
  from (
    values
      ('The test1 module has been tested and shows good results on cats and dogs.')
    , ('The module has been tested and shows good results on fox.')
    , ('People in recovery groups can be dogmatic.')
    , ('Cattle can be scatty.')
  ) q ([Text description])
),
a as (
  select
    i.[Text description]
  , t.Term
  , (len(i.[Text description]) - len(replace(i.[Text description], t.Term, ''))) / len(t.Term) as TermCount
  , len(t.Term) as TermLen
  from searchTerms t
    inner join input i on i.[Text description] like '%' + t.Term + '%'
)
select
  a.[Text description]
, string_agg(left(replicate(a.Term + ', ', a.TermCount), (a.TermLen + 2) * a.TermCount - 2), ', ') as 'Keywords Found'
from a
group by
  a.[Text description]
order by 1
If it matters what order the search terms appear in the output, STRING_AGG has an ORDER BY clause in 2017 and later

If STRING_AGG is not available, or if you are using SQL Server 2016 and need the search terms in a specific order, you can use STUFF:

Code Select Expand

with
searchTerms as (
  select *
  from (
    values
      ('Test1')
    , ('Fox')
    , ('Cat')
    , ('Dog')
    , ('Test2')
  ) q (Term)
),
input as (
  select *
  from (
    values
      ('The test1 module has been tested and shows good results on cats and dogs.')
    , ('The module has been tested and shows good results on fox.')
    , ('People in recovery groups can be dogmatic.')
    , ('Cattle can be scatty.')
  ) q ([Text description])
),
a as (
  select
    i.[Text description]
  , t.Term
  , (len(i.[Text description]) - len(replace(i.[Text description], t.Term, ''))) / len(t.Term) as TermCount
  , len(t.Term) as TermLen
  from searchTerms t
    inner join input i on i.[Text description] like '%' + t.Term + '%'
),
b as (
  select
    a.[Text description]
  , left(replicate(a.Term + ', ', a.TermCount), (a.TermLen + 2) * a.TermCount - 2) as Keyword
  from a
)

select distinct t1.[Text description]
, STUFF(
    (
      SELECT ', ' + t2.Keyword
      from b t2
      where t1.[Text description] = t2.[Text description]
      order by t2.Keyword
      FOR XML PATH('')
    )
    , 1
    , 2
    , ''
  ) data
from b t1;
To do it natively in Cognos, STRING_AGG and STUFF are not available.  You'll need to have the visualization perform the work using a repeater:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/16.2/" useStyleVersion="11.6" expressionLocale="en-us">
<drillBehavior/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents>
<list horizontalPagination="true" name="List1" refQuery="Input">
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="padding:16px;"/>
</style>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemLabel refDataItem="Text description"/>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Text description"/>
</dataSource>
</textItem>
</contents>
</listColumnBody>
</listColumn>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents/>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<repeater name="Repeater1" refQuery="Query1">
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Terms"/>
</dataSource>
</textItem>
<textItem>
<dataSource>
<dataItemValue refDataItem="comma"/>
</dataSource>
</textItem>
</contents>
<masterDetailLinks>
<masterDetailLink>
<masterContext>
<dataItemContext refDataItem="Text description"/>
</masterContext>
<detailContext>
<dataItemContext refDataItem="Text description"/>
</detailContext>
</masterDetailLink>
</masterDetailLinks>
</repeater>
</contents>
</listColumnBody>
</listColumn>
</listColumns>
<sortList>
<sortItem refDataItem="Text description"/>
</sortList>
</list>
</contents>
</pageBody>
<XMLAttributes>
<XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/>
</XMLAttributes>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes>
<XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/>
<XMLAttribute output="no" name="listSeparator" value=","/>
<XMLAttribute output="no" name="decimalSeparator" value="."/>
<XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/>
</XMLAttributes>
<queries>
<query name="SearchTerms">
<source>
<sqlQuery name="SQL1" dataSource="great_outdoors_sales">
<sqlText>  select *
  from (
    values
      (&apos;Test1&apos;)
    , (&apos;Fox&apos;)
    , (&apos;Cat&apos;)
    , (&apos;Dog&apos;)
    , (&apos;Test2&apos;)
  ) q (Term)</sqlText>
<mdProjectedItems>
<mdProjectedItem name="Term"/>
</mdProjectedItems>
</sqlQuery>
</source>
<selection>
<dataItem name="Term" aggregate="none" rollupAggregate="none">
<expression>[SQL1].[Term]</expression>
</dataItem>
</selection>
</query>
<query name="Input">
<source>
<sqlQuery name="SQL2" dataSource="great_outdoors_sales">
<sqlText>select *
from (
  values
    (&apos;The test1 module has been tested and shows good results on cats and dogs.&apos;)
  , (&apos;The module has been tested and shows good results on fox.&apos;)
  , (&apos;People in recovery groups can be dogmatic.&apos;)
  , (&apos;Cattle can be scatty.&apos;)
) q ([Text description])</sqlText>
<mdProjectedItems>
<mdProjectedItem name="Text description"/>
</mdProjectedItems>
</sqlQuery>
</source>
<selection>
<dataItem name="Text description" aggregate="none" rollupAggregate="none">
<expression>[SQL2].[Text description]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
</selection>
</query>
<query name="a">
<source>
<joinOperation>
<joinOperands>
<joinOperand cardinality="0:1">
<queryRef refQuery="SearchTerms"/>
</joinOperand>
<joinOperand cardinality="1:1">
<queryRef refQuery="Input"/>
</joinOperand>
</joinOperands>
<joinFilter>
<filterExpression>[Input].[Text description] contains [SearchTerms].[Term]</filterExpression>
</joinFilter>
</joinOperation>
</source>
<selection>
<dataItem aggregate="none" rollupAggregate="none" name="Text description">
<expression>[Input].[Text description]</expression>
</dataItem>
<dataItem aggregate="none" rollupAggregate="none" name="Term">
<expression>[SearchTerms].[Term]</expression>
</dataItem>
<dataItem name="TermCount" aggregate="none" rollupAggregate="none">
<expression>(len([Input].[Text description]) - len(replace([Input].[Text description], [SearchTerms].[Term], &apos;&apos;))) / len([SearchTerms].[Term])</expression>
</dataItem>
<dataItem name="TermLen" aggregate="none" rollupAggregate="none">
<expression>len([SearchTerms].[Term])</expression>
</dataItem>
</selection>
</query>
<query name="Query1">
<source>
<queryRef refQuery="a"/>
</source>
<selection>
<dataItem aggregate="none" rollupAggregate="none" name="Text description" sort="ascending">
<expression>[a].[Text description]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
<dataItem name="Terms" aggregate="none" rollupAggregate="none" sort="ascending">
<expression>left(replicate([a].[Term] + &apos;, &apos;, [a].[TermCount]), ([a].[TermLen] + 2) * [a].[TermCount] - 2)</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
<dataItem name="comma">
<expression>case running-count([Terms] for [Text description])
when count([Terms] for [Text description])
    then &apos;&apos;
else &apos;, &apos;
end</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
</selection>
</query>
</queries>
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath>
<reportName>warm</reportName>
</report>

Thank you for the advice. We're using IBM Cognos Analytics 11.2.4.

Couldn't load the XML script into a report. Will look into possibilities to make it work.

I am trying to create a join/relationship between two packages...  What am I doing wrong?
Either:

• Using packages
• Not defining your data needs

In the data module, create tables from the contents of each package.  The tables should include only what you need for the reports the data module serves.  Then you can create relationships between the tables.  To reduce confusion for the report developer, be sure to not expose the items the reports won't need.  (like the original packages)

If you are trying to create a monster data module that serves up the entire content of two large or complex packages, you'll need to make the joins in the reports.

Hello, new to data modules and not sure how they work or what they are used for.  I have been watching youtube videos and they make it look so easy.

I am trying to create a simple data module just to see how they work and start playing around.  I am trying to create a join/relationship between two packages.  1.  Accounts Payable and 2. Accounts receivable.  I click create new relationship, but I can only select one table in the join.  What am I doing wrong?

If I try to create a join from 1 package, just different tables within.  I don't even have the option to create a relationship? 

You didn't say which RDBMS you are using.  If you can have SQL do the work for you, here's an example for SQL Server 2016 and later:

with
searchTerms as (
  select *
  from (
    values
      ('Test1')
    , ('Fox')
    , ('Cat')
    , ('Dog')
    , ('Test2')
  ) q (Term)
),
input as (
  select *
  from (
    values
      ('The test1 module has been tested and shows good results on cats and dogs.')
    , ('The module has been tested and shows good results on fox.')
    , ('People in recovery groups can be dogmatic.')
    , ('Cattle can be scatty.')
  ) q ([Text description])
),
a as (
  select
    i.[Text description]
  , t.Term
  , (len(i.[Text description]) - len(replace(i.[Text description], t.Term, ''))) / len(t.Term) as TermCount
  , len(t.Term) as TermLen
  from searchTerms t
    inner join input i on i.[Text description] like '%' + t.Term + '%'
)
select
  a.[Text description]
, string_agg(left(replicate(a.Term + ', ', a.TermCount), (a.TermLen + 2) * a.TermCount - 2), ', ') as 'Keywords Found'
from a
group by
  a.[Text description]
order by 1
If it matters what order the search terms appear in the output, STRING_AGG has an ORDER BY clause in 2017 and later

If STRING_AGG is not available, or if you are using SQL Server 2016 and need the search terms in a specific order, you can use STUFF:

with
searchTerms as (
  select *
  from (
    values
      ('Test1')
    , ('Fox')
    , ('Cat')
    , ('Dog')
    , ('Test2')
  ) q (Term)
),
input as (
  select *
  from (
    values
      ('The test1 module has been tested and shows good results on cats and dogs.')
    , ('The module has been tested and shows good results on fox.')
    , ('People in recovery groups can be dogmatic.')
    , ('Cattle can be scatty.')
  ) q ([Text description])
),
a as (
  select
    i.[Text description]
  , t.Term
  , (len(i.[Text description]) - len(replace(i.[Text description], t.Term, ''))) / len(t.Term) as TermCount
  , len(t.Term) as TermLen
  from searchTerms t
    inner join input i on i.[Text description] like '%' + t.Term + '%'
),
b as (
  select
    a.[Text description]
  , left(replicate(a.Term + ', ', a.TermCount), (a.TermLen + 2) * a.TermCount - 2) as Keyword
  from a
)

select distinct t1.[Text description]
, STUFF(
    (
      SELECT ', ' + t2.Keyword
      from b t2
      where t1.[Text description] = t2.[Text description]
      order by t2.Keyword
      FOR XML PATH('')
    )
    , 1
    , 2
    , ''
  ) data
from b t1;
To do it natively in Cognos, STRING_AGG and STUFF are not available.  You'll need to have the visualization perform the work using a repeater:

<report xmlns="http://developer.cognos.com/schemas/report/16.2/" useStyleVersion="11.6" expressionLocale="en-us">
<drillBehavior/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents>
<list horizontalPagination="true" name="List1" refQuery="Input">
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="padding:16px;"/>
</style>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemLabel refDataItem="Text description"/>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Text description"/>
</dataSource>
</textItem>
</contents>
</listColumnBody>
</listColumn>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents/>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<repeater name="Repeater1" refQuery="Query1">
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Terms"/>
</dataSource>
</textItem>
<textItem>
<dataSource>
<dataItemValue refDataItem="comma"/>
</dataSource>
</textItem>
</contents>
<masterDetailLinks>
<masterDetailLink>
<masterContext>
<dataItemContext refDataItem="Text description"/>
</masterContext>
<detailContext>
<dataItemContext refDataItem="Text description"/>
</detailContext>
</masterDetailLink>
</masterDetailLinks>
</repeater>
</contents>
</listColumnBody>
</listColumn>
</listColumns>
<sortList>
<sortItem refDataItem="Text description"/>
</sortList>
</list>
</contents>
</pageBody>
<XMLAttributes>
<XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/>
</XMLAttributes>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes>
<XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/>
<XMLAttribute output="no" name="listSeparator" value=","/>
<XMLAttribute output="no" name="decimalSeparator" value="."/>
<XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/>
</XMLAttributes>
<queries>
<query name="SearchTerms">
<source>
<sqlQuery name="SQL1" dataSource="great_outdoors_sales">
<sqlText>  select *
  from (
    values
      (&apos;Test1&apos;)
    , (&apos;Fox&apos;)
    , (&apos;Cat&apos;)
    , (&apos;Dog&apos;)
    , (&apos;Test2&apos;)
  ) q (Term)</sqlText>
<mdProjectedItems>
<mdProjectedItem name="Term"/>
</mdProjectedItems>
</sqlQuery>
</source>
<selection>
<dataItem name="Term" aggregate="none" rollupAggregate="none">
<expression>[SQL1].[Term]</expression>
</dataItem>
</selection>
</query>
<query name="Input">
<source>
<sqlQuery name="SQL2" dataSource="great_outdoors_sales">
<sqlText>select *
from (
  values
    (&apos;The test1 module has been tested and shows good results on cats and dogs.&apos;)
  , (&apos;The module has been tested and shows good results on fox.&apos;)
  , (&apos;People in recovery groups can be dogmatic.&apos;)
  , (&apos;Cattle can be scatty.&apos;)
) q ([Text description])</sqlText>
<mdProjectedItems>
<mdProjectedItem name="Text description"/>
</mdProjectedItems>
</sqlQuery>
</source>
<selection>
<dataItem name="Text description" aggregate="none" rollupAggregate="none">
<expression>[SQL2].[Text description]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
</selection>
</query>
<query name="a">
<source>
<joinOperation>
<joinOperands>
<joinOperand cardinality="0:1">
<queryRef refQuery="SearchTerms"/>
</joinOperand>
<joinOperand cardinality="1:1">
<queryRef refQuery="Input"/>
</joinOperand>
</joinOperands>
<joinFilter>
<filterExpression>[Input].[Text description] contains [SearchTerms].[Term]</filterExpression>
</joinFilter>
</joinOperation>
</source>
<selection>
<dataItem aggregate="none" rollupAggregate="none" name="Text description">
<expression>[Input].[Text description]</expression>
</dataItem>
<dataItem aggregate="none" rollupAggregate="none" name="Term">
<expression>[SearchTerms].[Term]</expression>
</dataItem>
<dataItem name="TermCount" aggregate="none" rollupAggregate="none">
<expression>(len([Input].[Text description]) - len(replace([Input].[Text description], [SearchTerms].[Term], &apos;&apos;))) / len([SearchTerms].[Term])</expression>
</dataItem>
<dataItem name="TermLen" aggregate="none" rollupAggregate="none">
<expression>len([SearchTerms].[Term])</expression>
</dataItem>
</selection>
</query>
<query name="Query1">
<source>
<queryRef refQuery="a"/>
</source>
<selection>
<dataItem aggregate="none" rollupAggregate="none" name="Text description" sort="ascending">
<expression>[a].[Text description]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
<dataItem name="Terms" aggregate="none" rollupAggregate="none" sort="ascending">
<expression>left(replicate([a].[Term] + &apos;, &apos;, [a].[TermCount]), ([a].[TermLen] + 2) * [a].[TermCount] - 2)</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
<dataItem name="comma">
<expression>case running-count([Terms] for [Text description])
when count([Terms] for [Text description])
    then &apos;&apos;
else &apos;, &apos;
end</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value=""/>
</XMLAttributes>
</dataItem>
</selection>
</query>
</queries>
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath>
<reportName>warm</reportName>
</report>

Specific to my last post, should the same keyword be listed twice?

Quote

description	keywords
People in recovery groups can be dogmatic.	dog
Cattle can be scatty.	cat, cat

Is "cat, cat" correct, or should it just be "cat"?

Have tried the IF ELSE THEN statement in the Data Item Text description, but not did not give a result.

IF (COLUMN TEXT DESCRIPTION CONTAINS 'Test1') THEN ('Test1')
ELSE (IF (COLUMN TEXT DESCRIPTION CONTAINS 'Fox') THEN ('Fox')
ELSE (IF (COLUMN TEXT DESCRIPTION CONTAINS 'Cat') THEN ('Cat')
ELSE (IF (COLUMN TEXT DESCRIPTION CONTAINS 'Dog') THEN ('Dog')
ELSE (IF (COLUMN TEXT DESCRIPTION CONTAINS 'Test2') THEN ('Test2')
ELSE (NULL)))))

Hi Friends,

Our users are complaining that when users are running and exporting drill through reports into Excel or PDF the
the blue numbers and hyperlinks are not turning off. Is there a solution for this without creating multiple columns and using conditional variables. Please advise.

Assuming that the Detail Aggregation is Total for Var 1, Var 2, and Var 3, my guess is the one that is working is basically being interpreted as

Var 3 = (total(If (year=2023) THEN (A) ELSE (NULL))/total(If (year=2023) THEN (B) ELSE (NULL))*100
So when you try
Var 3 = If (year=2023) THEN (A/B) ELSE (NULL)it's performing the calculation before aggregating A and B.  And since NULL/B and A/NULL and NULL/NULL all result in NULL, and this is before the values are being aggregated, you will get different results than if you aggregate before calculating.

You can check this by clicking the 3 dots in the top right (next to Properties) and using Show Generated SQL/MDX.  That will enable you to inspect the SQL to see what's really going on.

Quote from: dougp on 05 Mar 2025 11:59:39 AMThat's better, but I'd still need more clarification.

"cats" and "dogs" are not in your list of search terms, but you indicate you want them found.  How would that work?

Does the word need to be the first, last, succeeded by punctuation, or surrounded by spaces?  (...meaning, it's the whole word.)  What if it is found as part of a word?

description keywords People in recovery groups can be dogmatic. dog Cattle can be scatty. cat, cat And are the terms case-sensitive?

Would like to find part of the words. So if the parameter is cat and test, it also finds cats, cattail and test01, test02, testing etc...

It's also necessary in our filter because the input of the text description is not always grammatically correct and therefore we need to filter on multiple parameters in 1 text description field.

It does not have to be case sensitive.