If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

User Entered Prompt values

Started by beav81psu, 30 Jul 2009 08:56:29 AM

Previous topic - Next topic

beav81psu

Hello,

I have a report where the user wants to get a list of student ID's based on his changing list of email addresses.  Is there a way I can set up a prompt to allow the user to paste into the prompt that list of email addresses?  Basically I want a prompt that is really not related to the email field.  Right now when I try to create the prompt it goes to the db and gets a distinct list of email addresses and he would then have to scroll though that list to pick the 100 or so that he wants.  (not a good choice when we have 300,000 students).

Thanks

uttam.mistry

can not you use text box prompt or Select & Search Prompt?
also have a look at http://www.cognoise.com/community/index.php/topic,7350.0.html

beav81psu

I followed the post you suggested with a list prompt, and it leads me to the same place i started with.... when i put this in as a filter in my report query....

[Student Email Address] in #  '('+ prompt('EXAMPLE_PARAMETER','token','-99') + ')'#


I guess a prompt that returns the dissintct list of email addresses from my database table.  I want to be able to free form that list in.


CognosPaul


uttam.mistry

i wanted to say don't use list prompt ---if possible--- (i.e. remove list prompt from your report), instead use 'search & select prompt' --> so that users are not required to select a value(s) from big list, they can limit the data in prompt list by entering keywords...

or you can go for 'text box' prompt (with use of macros if required)--> where user can copy paste the email id(s) they want to filter..

I think suggestion form PaulM is very good!!
(but, we try not to use java scripts because it is not supported by Cognos & when you upgrade java script may not work!!)

--
thanks PaulM for sharing useful information!!

uttam.mistry

#5
for a 'text prompt' (using macro) below is the report working on sales(query) package...

on prompt page enter:
'Mai.Nonaka@1fo208.com','Paul.Gasper@4Go611.com','Janice.Manning@New208.com','Adela.Panella@Aard700.com'

<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-gb">
<modelPath>/content/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Retailer name" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Retailer site].[Retailer name]</expression></dataItem><dataItem name="Email" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Retailer site].[Email]</expression></dataItem></selection>
<detailFilters><detailFilter use="optional"><filterExpression>[Sales (query)].[Retailer site].[Email] in
#'(' + prompt('Email','token','''a@a.a''')+ ')'#</filterExpression></detailFilter></detailFilters></query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<list refQuery="Query1" horizontalPagination="true" name="List1">



<style>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Email"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Email"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</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><block><contents><textItem><dataSource><staticValue>Email</staticValue></dataSource></textItem></contents></block><block><contents><textBox parameter="Email" numbersOnly="false" multiSelect="false" range="false" required="false"/></contents><style><CSS value="margin-bottom:12px"/></style></block></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>
<classStyles>
<classStyle name="pd_1" label="Excellent">
<CSS value="background-color:#009933; color:#FFFFFF;"/>
</classStyle>
<classStyle name="pd_2" label="Very good">
<CSS value="background-color:#FFFFFF; color:#009933;"/>
</classStyle>
<classStyle name="pd_3" label="Average">
<CSS value="background-color:#FFFFFF; color:#CC9900;"/>
</classStyle>
<classStyle name="pd_4" label="Below average">
<CSS value="background-color:#FFFFFF; color:#990000;"/>
</classStyle>
<classStyle name="pd_5" label="Poor">
<CSS value="background-color:#990000; color:#FFFFFF;"/>
</classStyle>
</classStyles>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>



and for select and search prompt below is the example...
on prompt page type anything say pi

<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-gb">
<modelPath>/content/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Retailer name" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Retailer site].[Retailer name]</expression></dataItem><dataItem name="Email" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Retailer site].[Email]</expression></dataItem></selection>
<detailFilters><detailFilter use="optional"><filterExpression>[Sales (query)].[Retailer site].[Email] in ?Email?</filterExpression></detailFilter></detailFilters></query>
<query name="Query2"><source><model/></source><selection><dataItem name="Email" aggregate="none"><expression>[Sales (query)].[Retailer site].[Email]</expression></dataItem></selection></query></queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<list refQuery="Query1" horizontalPagination="true" name="List1">



<style>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Email"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Email"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</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><block><contents><textItem><dataSource><staticValue>Email</staticValue></dataSource></textItem></contents></block><block><contents><selectWithSearch parameter="Email" refQuery="Query2" multiSelect="true" required="false"><useItem refDataItem="Email"/></selectWithSearch></contents><style><CSS value="margin-bottom:12px"/></style></block></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>
<classStyles>
<classStyle name="pd_1" label="Excellent">
<CSS value="background-color:#009933; color:#FFFFFF;"/>
</classStyle>
<classStyle name="pd_2" label="Very good">
<CSS value="background-color:#FFFFFF; color:#009933;"/>
</classStyle>
<classStyle name="pd_3" label="Average">
<CSS value="background-color:#FFFFFF; color:#CC9900;"/>
</classStyle>
<classStyle name="pd_4" label="Below average">
<CSS value="background-color:#FFFFFF; color:#990000;"/>
</classStyle>
<classStyle name="pd_5" label="Poor">
<CSS value="background-color:#990000; color:#FFFFFF;"/>
</classStyle>
</classStyles>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>