COGNOiSe.com - The IBM Cognos Community

Hey folks,

I have what I think might be a bit of a unique request. The client wants a report that takes two lists of values (Names and IDs) and generates a report. The report will be data based on the IDs, but they also want to see the manually provided name entered into the list beside the data name element found in the database.

The order of the values will match up such that the first ID matches the first name and so forth.

In order to further complicate things the ID will not always be found in the data.

Is there a way to reference the nth element in a parameter? I can easily compute the position of the ID as it is fixed length. If I know that it's the 12th ID entered, is there an easy way to pull the 12th name from the list. The only way I can think to do it is to force the names to be fixed length.

I'm working in Report Studio 8.4, and currently using a multi select text box to grab both lists of names and IDs.

If you can think of an alternative method of doing this that would also be great.

Thank you!
Rick

This is an interesting problem. Unfortunately the Cognos Macro functions leave quite a bit to be desired, especially when it comes to arrays. Fortunately there is a way around the limitation.

If I understand correctly, you have two multi text boxes. One allows the user to enter a series of codes, and the other a series of labels. The data is filtered by the codes, with no guarantee that there is data for a specific code, while the label needs to be displayed with the corresponding code.

The easiest way, I figure, would be to build a case statement. Create another text box prompt, and feed that with a JavaScript function:

function getLabelAndCodes()
{

var labels = fW._textEditBox_labels.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.getElementsByTagName('select')[0].options;
var codes = fW._textEditBox_codes.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.getElementsByTagName('select')[0].options;
var forReport = '';

if(labels.length!=codes.length ){alert('Please ensure there are the same number of labels and codes selected.');return false}
if(labels.length==0){alert('Please select at least one label.');return false}

for(i=0;i<labels.length;i++)
{
  forReport+='when ' + codes[i].value + ' then \'' + labels[i].value + '\' '
}

fW._textEditBox_forReport.value = forReport;
promptAction('finish');
}


What that JavaScript does it to check that both label and code text prompts have a matching number of values. It will then generate a string like:
when 1 then 'ABC' when 2 then 'BCD'

In the report query, you could then use that parameter inside a data item:

case [ProductCode]
#prompt('forReport','token')#
end


The following example is based on the GO Sales package in 10.1:
<report xmlns="http://developer.cognos.com/schemas/report/7.0/" useStyleVersion="10" expressionLocale="he-il">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<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="Query1">



<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="Base product number"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles>
<defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Base product number"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style>
<contents><textItem><dataSource><dataItemLabel refDataItem="Product"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product"/></dataSource></textItem>
</contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Case"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles>
<defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Case"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
</pageBody>
</page>
</reportPages>
<promptPages><page name="Prompt Page1">
<pageHeader>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue/>
</dataSource>
<style>
<defaultStyles>
<defaultStyle refStyle="tt"/>
</defaultStyles>
</style>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ta"/>
</defaultStyles>
</style>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="hp"/>
</defaultStyles>
</style>
</pageHeader>
<pageBody>
<contents><HTMLItem>
<dataSource>
<staticValue>&lt;script&gt;
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) 
{

    fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );

}

function getLabelAndCodes()
{

var labels = fW._textEditBox_labels.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.getElementsByTagName('select')[0].options;
var codes = fW._textEditBox_codes.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.getElementsByTagName('select')[0].options;
var forReport = '';

if(labels.length!=codes.length ){alert('Please ensure there are the same number of labels and codes selected.');return false}
if(labels.length==0){alert('Please select at least one label.');return false}

for(i=0;i&lt;labels.length;i++)
{
  forReport+='when ' + codes[i].value + ' then \'' + labels[i].value + '\' '
}

fW._textEditBox_forReport.value = forReport;
promptAction('finish');
}

&lt;/script&gt;</staticValue>
</dataSource>
</HTMLItem><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows>
<tableRow><tableCells><tableCell><contents><textBox parameter="Labels" multiSelect="true" name="_labels"/></contents></tableCell><tableCell><contents>
<textBox multiSelect="true" name="_codes" parameter="Codes"/></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell colSpan="2">
<contents><textBox parameter="ForReport" required="false" name="_forReport"/><HTMLItem>
<dataSource>
<staticValue>&lt;input type="button" onclick="getLabelAndCodes()"&gt;</staticValue>
</dataSource>
</HTMLItem></contents></tableCell></tableCells></tableRow></tableRows></table></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>
<pageFooter>
<contents>
<promptButton type="cancel">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="back">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="fp"/>
</defaultStyles>
</style>
</pageFooter>
<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages></layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes>
<reportName>testing labels and codes problem</reportName><queries><query name="Query1"><source><model/></source><selection><dataItem name="Product" aggregate="none" rollupAggregate="none">
<expression>[Sales (query)].[Products].[Product]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/>
</XMLAttributes></dataItem><dataItem name="Base product number" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Products].[Base product number]</expression><XMLAttributes>
<XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Case"><expression>case [Base product number]

#prompt('ForReport','token')#

end</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Base product number] in ?Codes?</filterExpression></detailFilter></detailFilters></query></queries></report>

Paul,

That worked wonderfully! Thank you for the quick response!

I made a few small modifications to escape out ' characters in the text portion. Also I had to set a default value on the final text box so it would allow the "Next" prompt to activate. Not complaining just notes to anyone else who runs into these issues. :)

Now I just need to figure out how to show a list of values that didn't match to anything in the database.

Rick