Hello!  I am new to this website.  I am getting an error and I googled it and found a post with the same error, but I do not see how to view the answer(s) that were given....?????

QuoteCan you create joins off of calculations?

Yes and in one of the sample FM models there's an example of one.  It will be awfully inefficient but needs must I imagine.

QuoteI keep getting an error with really no error message.

Have you tried clicking on view details?  In some places, such as the relationship editor preview UI and the data grid, when something goes wrong there is a message to that affect with a link which says 'view details'.  If you click it you get the error message details.

here is the calculations:

SUBSTRING(i_ORGS,9,4) join to substring(i_ORGANIZATION_ID,9,4)

when on the grid tab, each return the correct data.  Just don't know why I can't use this as a join

Hello all, new to all this.  When creating relationships between tables.  Can you create joins off of calculations?  I have an org number and I need to substring the field to be substring(org,9,4) on each table that needs to be joined.  I keep getting an error with really no error message.

So is it even possible to do this?  Or do I need to add fields in the database to do this? Or even in FM to do this.

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.