1

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....

Steven Taub
  • 11
  • 1

2 Answers2

0

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>
Daniel Wagemann
  • 741
  • 4
  • 6
  • Thanks for this, but was unable to get it going. I found the "Default selections" property of the text box prompt (only thing marked 'default') and when I included this text and ran the report, it showed the page with exactly what I typed in. So then I switched it to this. #prompt([QUERY FIELDNAME],'string',[QUERYNAME])# – Steven Taub Jan 10 '20 at 22:55
  • (Sorry, wasn't aware that I only had 5 minutes to edit. This was what i wanted to respond with). Thanks for this, but was unable to get it going. I found the "Default selections" property of the text box prompt (only thing marked 'default') and when I included this text and ran the report, it showed the page with exactly what I typed in. So then I switched it to this. #prompt([QUERY FIELDNAME],'string',[QUERYNAME])# The prompt appeared blank #prompt([QUERYNAME],'string',[FIELDNAME])# Nothing. – Steven Taub Jan 10 '20 at 23:01
  • Using the default value on the prompt object won't be dynamic, it will just be the text. If you use the prompt macro and make the text prompt optional, then run the report without typing anything in, it should run the report with the dynamic query item value. – Daniel Wagemann Jan 13 '20 at 12:20
  • Thanks Daniel. I tried the prompt you listed above in the default selection by first copy/paste it in. #prompt('Text','string','[MyValue]')# Then in my version , I typed i the following #prompt('Text','string','[SEL]')# Where SEL is the single field value that I get from my query. It still shows the value as the text I typed in. Can you suggest a different way? – Steven Taub Jan 13 '20 at 17:06
  • I'm guessing my problem is in how I'm actually adding it to the Default Selections property – Steven Taub Jan 13 '20 at 17:12
  • I've added a sample spec to the reply above, just run that without filling in the text prompt, I am using the R5 release so if you get a schema version error just lower it in the header. – Daniel Wagemann Jan 13 '20 at 17:17
0

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.

dougp
  • 2,810
  • 1
  • 8
  • 31
  • Thanks. You might have discovered I'm kind of new at Cognos. It's kind of dissapointing that working with previous reporting software that this is such a challenge to do. – Steven Taub Jan 14 '20 at 22:07
  • What "previous reporting software"? I'm curious because I'm trying to figure out what I would replace Cognos with. There are many features that my users can't live without. – dougp Jan 14 '20 at 22:41
  • The thing about cognos so far is that it seems that many features that are simple to set up are a challenge. For example using my example here, i have a simple query that returns one value and I can't seem to just hook it into a text or value prompt and let my report run with whatever value appears because of the query. This is a no brainer in other software like SSRS Powebi, tableau, but not in Cognos! – Steven Taub Jan 21 '20 at 20:41
  • You must have different requirements. For my users' requirements, I find Cognos to be far simpler overall than SSRS and Power BI when I balance ease-of-use and feature set with administration effort. Plus, Cognos has features that I don't see ever being implemented in SSRS or Power BI. (I have no experience with Tableau, but I understand it behaves much like Power BI - but more grown up.) – dougp Jan 21 '20 at 23:05