Cognos report studio - Text Prompt

Question

I'm running a report where I'd like to populate the text box prompt with data run from a local query. How do I populate that prompt with a query that gets, let's say, the latest Value that changes daily in the local query?

I'm spoiled by this being so easy to do with other software....

Answer 1

As silly as it sounds setting the default value and running the report with default values are actually two different things. To set the default value of the text box dynamically, you would have to use JavaScript which grabs the hidden query value and sets it on load.

To run the report with a dynamic default value you could use the following prompt macro:

#prompt('Text','string','[MyValue]')#

where the [MyValue] is the defined default value.

<report xmlns="http://developer.cognos.com/schemas/report/15.3/"           expressionLocale="en" useStyleVersion="11.5">

            <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="Query1">



        <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="Data Item1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Data Item1"/></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><textBox parameter="TextPrompt"/></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>
        <queries><query name="Query1"><source><model/></source><selection><dataItemMember name="10000"><dmMember><MUN>[plan_BudgetPlan].[plan_business_unit].[plan_business_unit]-&gt;:[TMR].[plan_business_unit].[plan_business_unit].[10000]</MUN><itemCaption>10000</itemCaption></dmMember><dmDimension><DUN>[plan_BudgetPlan].[plan_business_unit]</DUN><itemCaption>plan_business_unit</itemCaption></dmDimension><dmHierarchy><HUN>[plan_BudgetPlan].[plan_business_unit].[plan_business_unit]</HUN><itemCaption>plan_business_unit</itemCaption></dmHierarchy></dataItemMember><dataItem name="DI"><expression>&apos;booogie boogie&apos;</expression></dataItem><dataItem name="Data Item1"><expression>#prompt(&apos;TextPrompt&apos;,&apos;string&apos;,&apos;[DI]&apos;)#</expression></dataItem></selection></query></queries><XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="decimalSeparator" value="."/></XMLAttributes><classStyles><classStyle name="GuidedLayoutLeftPadding"><CSS value="padding-left:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutTopPadding"><CSS value="padding-top:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutRightPadding"><CSS value="padding-right:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutBottomPadding"><CSS value="padding-bottom:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutMargin"><CSS value="margin-bottom:10px"/></classStyle></classStyles><modelPath>/content/package[@name=&apos;PA Sample&apos;]/model[@name=&apos;2019-12-30T19:05:29.982Z&apos;]</modelPath></report>

Answer 2

Prompt Page 1:
Create a value prompt that is populated by a query that returns a single value.
Use JavaScript to automatically run the Next command.

Prompt Page 2:
Create a text box prompt that has uses the same parameter as the value prompt on Prompt Page 1.

There is an example similar to this approach in a report named Parameter Value Automation at https://github.com/dougpulse/Cognos.