For in house political reasons we don't / can't go to our existing IBM reseller for costs on moving from a named user model to a PVU based mode

We have calculated our PVU requirements but wanted to know a ball park figure for Cognos Analytics cost per PVU, can't seem to find it anywhere, just after a range really as costing up staying with Cognos or moving to a different product

Any ideas? (TIA)

I need to calculate the cumulative balance for two columns for a month. But I have subtractions only row by row. It does not count for a month.

running-total([debet] - [kredit] for [dat])

Thank you,  i was trying to join in two different packages.

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)))))