Solved: Text box prompt for each selected value from prompt

[navissar]

Hi Gurus!
I'm pretty sure this is nearing impossible, but I thought I might ask. The requirement is this: The user picks some of several services from a multi select value prompt, and then needs to type in a corresponding comission percentage for each value.
for example, there are 5 services: A, B, C, D and E. Of these services, the user selects A, B and D. Now I need the user to be able to type in a different commision percentage for A, B and D.
Also, I don't know how many services there are (It varies) or how many did the user pick...
Any ideas?

[Lynn]

Hmm...do you know the maximum number of services possible? Perhaps create a commission parameter for each possible choice and default to 0? A rather clunky approach.

What about the external data feature of 10? Have them upload a spreadsheet containing the selected services and corresponding percentages and use that to join with the data query to essentially filter the result and perform the calculations?

Just rambling wildly now....I will be curious to know if you come up with a good way to solve it.

[navissar]

Thanks for your reply, Lynn,

I thought of these myself. The first is a definite no-go: They change the number of services almost daily.
The second is a great idea, but would require them to change an Excel sheet, while they need to be able to run it quickly many times (It's for a what-if scenario, so there aren't steady comission percentages).
I will probably end up looking what was the maximum amount of services to date, doubling that number and create as many parameters. It sucks, but it will work and I could probably file it under "Good enough".

[CognosPaul]

This sounds like a fun challenge.

Creating the input box won't be a problem. Drag in a list. The query of the list returns the services filtered by what they selected. Each row is a service. Now drag in an HTML item, set to report expression:

Code Select Expand

'<input name="p_'+[Service Name]+'">'

Using those parameters in the report also shouldn't be a problem, but it depends on how the report is set up. I'm going to assume that you're using a repeater with a master/detail.

The next trick will be to tell each detail datacontainer to point to the correct prompt. You are aware that you can point a master/detail relationship to a parameter? Are you also aware that every parameter in a prompt macro can be set with another macro function,  including the parameter name? (read that in a dramatic voice, please)

I know how much you enjoy figuring things out on your own, so I'll leave you with that until tomorrow. 

[navissar]

Mr. PaulM!
As always, to the rescue.
After we talked I just had to try it. And. It. Worked.
Needed to straighten a few kinks, but nothing to complex, and works like a charm.
Thanks!

[CognosPaul]

I had fun with this. prompts in the prompt name parameter and in the default parameter.

The following XML is based on Cognos 10.1.1 on the Sales and Marketing Cube.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</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="List2" refQuery="FilteredProducts (No Default)">



<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<propertyList><propertyItem refDataItem="Code"/></propertyList><listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Products"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Products"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>Combination Chart</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><combinationChart showTooltips="true" maxHotspots="10000" refQuery="Query1" name="Combination Chart1">
<legend>
<legendPosition>
<relativePosition/>
</legendPosition>
<legendTitle refQuery="Query1">
<style>
<defaultStyles>
<defaultStyle refStyle="lx"/>
</defaultStyles>
</style>
</legendTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lg"/>
</defaultStyles>
</style>
</legend>
<ordinalAxis>
<axisTitle refQuery="Query1">
<style>
<defaultStyles>
<defaultStyle refStyle="at"/>
</defaultStyles>
</style>
</axisTitle>
<axisLine color="black"/>
<style>
<defaultStyles>
<defaultStyle refStyle="al"/>
</defaultStyles>
</style>
</ordinalAxis>
<numericalAxisY1>
<axisTitle refQuery="Query1">
<style>
<defaultStyles>
<defaultStyle refStyle="at"/>
</defaultStyles>
</style>
</axisTitle>
<gridlines color="#cccccc"/>
<axisLine color="black"/>
<style>
<defaultStyles>
<defaultStyle refStyle="al"/>
</defaultStyles>
</style>
</numericalAxisY1>
<combinationChartTypes>
<bar><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Quantity"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></bar>
<bar useNumericalAxis="numericalAxisY2"><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Quantity * Value"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></bar></combinationChartTypes>
<style>
<defaultStyles>
<defaultStyle refStyle="ch"/>
</defaultStyles>
</style>
<commonClusters><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Month"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></commonClusters><masterDetailLinks><masterDetailLink><masterContext><dataItemContext refDataItem="Products"/></masterContext><detailContext><dataItemContext refDataItem="Products"/></detailContext></masterDetailLink><masterDetailLink><masterContext><dataItemContext refDataItem="Code"/></masterContext><detailContext><parameterContext parameter="CodeForPrompt"/></detailContext></masterDetailLink></masterDetailLinks><numericalAxisY2><gridlines lineStyle="dash" color="#99CCFF"/><axisTitle refQuery="Query1"><style><defaultStyles><defaultStyle refStyle="at"/></defaultStyles></style></axisTitle><axisLine color="#99CCFF"/><style><CSS value="color:#0066CC"/><defaultStyles><defaultStyle refStyle="al"/></defaultStyles></style></numericalAxisY2><chartTitle refQuery="Query1"><style><defaultStyles><defaultStyle refStyle="ct"/></defaultStyles></style><chartContents><chartTextItem><dataSource><dataItemValue refDataItem="Products"/></dataSource></chartTextItem></chartContents></chartTitle><chartSubTitle refQuery="Query1"><style><defaultStyles><defaultStyle refStyle="cs"/></defaultStyles></style><chartContents><chartTextItem><dataSource><dataItemValue refDataItem="Value"/></dataSource></chartTextItem></chartContents></chartSubTitle></combinationChart>
</contents></listColumnBody></listColumn></listColumns></list></contents>
</pageBody>
</page>
</reportPages>
<promptPages><page name="Prompt Page1">

<pageBody>
<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><selectWithTree parameter="Products" refQuery="Products" required="false" multiSelect="true"><selectWithTreeItem refDataItem="Products"/><style><CSS value="height:300px"/></style></selectWithTree></contents></tableCell><tableCell><contents><list horizontalPagination="true" name="List1" refQuery="FilteredProducts">



<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</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="Products"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="text-align:left"/></style><contents><textItem><dataSource><dataItemValue refDataItem="Products"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>Multi Params</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><HTMLItem description="MultiParam">
<dataSource>

<reportExpression>case when [FilteredProducts].[Code] is null then '&lt;input name="p_Default"&gt;'
else
'&lt;input name="p_Product'+[FilteredProducts].[Code]+'"&gt;'
end</reportExpression></dataSource>
</HTMLItem></contents></listColumnBody></listColumn></listColumns><propertyList><propertyItem refDataItem="Code"/></propertyList></list></contents><style><CSS value="vertical-align:top;text-align:left"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><promptButton type="reprompt">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton></contents><style><CSS value="text-align:center"/></style></tableCell><tableCell><contents><promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton></contents><style><CSS value="text-align:center"/></style></tableCell></tableCells></tableRow></tableRows></table></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>

<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"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><queries><query name="Products"><source><model/></source><selection><dataItem name="Products" aggregate="none"><expression>rootMembers([sales_and_marketing].[Products].[Products])</expression></dataItem></selection></query><query name="FilteredProducts"><source><model/></source><selection autoSummary="false"><dataItem name="Products"><expression>union(member(0,'Default','Default',[sales_and_marketing].[Products].[Products])
,
#promptmany('Products','mun','[sales_and_marketing].[Products].[Products].[Products]-&gt;:[PC].[@MEMBER].[Products]','set(','',')')#)</expression></dataItem><dataItem name="Code"><expression>roleValue('_businessKey',[Products])</expression></dataItem></selection></query><query name="FilteredProducts (No Default)"><source><model/></source><selection autoSummary="false"><dataItem name="Products"><expression>#promptmany('Products','mun','[sales_and_marketing].[Products].[Products].[Products]-&gt;:[PC].[@MEMBER].[Products]','set(','',')')#</expression></dataItem><dataItem name="Code"><expression>roleValue('_businessKey',[Products])</expression></dataItem></selection></query><query name="Query1"><source><model/></source><selection><dataItem name="Products" rollupAggregate="none" aggregate="none"><expression>[sales_and_marketing].[Products].[Products]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Month]</expression></dataItem><dataItem name="Value"><expression>#prompt(
prompt('CodeForPrompt','token','1','Product')
,'integer',prompt('Default','integer','1'))#</expression></dataItem><dataItem name="Quantity"><expression>[sales_and_marketing].[Measures].[Quantity]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Quantity * Value"><expression>[Quantity] *[Value]</expression></dataItem></selection></query></queries><reportName>Prompt in promptname</reportName></report>We really need a spoiler tag here