Cascaded Prompts - Dimensional Source

[sag4cognos]

Hello All,

First, I would like to convey my gratitude to all the members of this forum, for their time and effort. Thank you every one, for all your help.  :)

I am using Cognos 8.4 BI with TM1 9.5 cube as the data source.
My date dimension is arranged like this

Sept YTD
  |- Beg Bal
  |- Oct
  |- Nov

The list goes on till Sep. This repeats for every month YTD.

I have two prompts ( cascaded ).

The first prompt runs on a data item, whose definition is rootmembers(Date) - Which returns Sept YTD, Nov YTD etc etc.

When the user selects the YTD in the first prompt, the second prompt should get loaded with the Children of the level selected.

I have tried 4 - 5 different methods to achieve the result.  some of them are

1. Children(rootmembers(Date)) - Date is the level

2. Descendants(rootmembers(Date),1,self) -Date is the level - With the query having a slicer filter for the Month selected

3. Data item for If Condition --> IF(Caption(Rootmembers(Date) = 'Sept YTD') THEN ([Sept YTD])
Data item feeding the Child prompt Descendants(Data Item for IF condition,1,self)

4. Data item for If Condition  ---> IF (?Current Year To Date? = 'Sept YTD') THEN ([Sept YTD])
Data item feeding the Child prompt Descendants(Data Item for IF condition,1,self)

5. Data Item feeding the Child prompt --->
    [Reporting Cube].[Month] -> [all].#SQ(Prompt('Current Year To Date','Token'))#

Problem is in handling the value of the prompt.It is not able to get it into string format. Its of a MUN format.

When i finally managed to get it to work, the "string" version of the prompts played havoc with my data. I was browsing through the forum and I came across a post, where a contributor had mentioned using Java Scripts to create an MUN.

I am new to scripting and can anyone advice or direct me where to start and what to read? ???

I am looking for a script which can create MUN's, which could help me dynamically populate the child prompt. ???

Any help or suggestion on this ?

Thanks a ton for all the help,
Sagar

[MFGF]

Hi,

I just tried this and got it working.  Here's what I did:

1. In my crosstab report, I defined a query calculation called YTD with the expression rootmembers([Date Hierarchy]) and a second nested query calculation called YTD Month with the expression descendants(rootmembers([Date Hierarchy]),1).  I also defined two detail filters - the first was [YTD] = ?ytd? and the second was [YTD Month] = ?ytdm?

2. I defined a prompt page, and added in a Value prompt, based on the existing ytd parameter, then immediately clicked finish.  Next I added a new query to the query explorer (called YTDPrompt), dragged in a single data item from the toolbox, and set the expression to be rootmembers([Date Hierarchy]).  I then went back to the properties of my value prompt and specified the YTDPrompt query in the Query property, the data item in the Use Value property, and set Auto Submit to true.

3. I added a second value prompt to the prompt page, based on the existing ytdm parameter, then immediately clicked finish.  As before, I added a new query to the query explorer (called YTDMPrompt), dragged in a data item from the toolbox, and set the expression to be descendants(rootmembers([Date Hierarchy]),1)  I then went back to the properties of my new value prompt and specified the YTDMPrompt query in the Query property, the data item in the Use Value property and the ytd parameter in the Cascade Source property.

This worked for me with no problems.

MF.

[sag4cognos]

Hello MF,

Thank you for your time. The report did not work for me  :-[.

Could you share with me the report XML,  if you do not mind.

I guess, I am missing a point some where..  :-\

Thanks,
Sagar ???

[MFGF]

Hi,

I can't share the report based on the TM1 cube, but I have created another version based on the Great Outdoors Sales Powercube.  The difference here is that the Year level is not root of the hierarchy, but one level down, and Months are 3 levels down, but otherwise it's exactly the same.

The XML for my report (Cognos 8.4.1) is below.

Regards,

MF.


<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-gb">
            <modelPath>/content/package[@name='Great Outdoor Sales (cube)']/model[@name='2008-02-29T21:20:56.726Z']</modelPath>
            <drillBehavior modelBasedDrillThru="true"/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItemLevelSet name="Product line"><dmDimension><DUN>[great_outdoor_sales_en].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoor_sales_en].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy><dmLevel><LUN>[great_outdoor_sales_en].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel></dataItemLevelSet><dataItem name="YTD"><expression>descendants(rootmembers([great_outdoor_sales_en].[Years].[Years]),1)</expression></dataItem><dataItemMeasure name="Revenue"><dmMember><MUN>[great_outdoor_sales_en].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great_outdoor_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension></dataItemMeasure><dataItem name="YTD Month"><expression>descendants(rootmembers([great_outdoor_sales_en].[Years].[Years]),3)</expression></dataItem></selection>
               <detailFilters><detailFilter use="required" postAutoAggregation="false"><filterExpression>[YTD] = ?ytd?</filterExpression></detailFilter><detailFilter use="required" postAutoAggregation="false"><filterExpression>[YTD Month] = ?ytdm?</filterExpression></detailFilter></detailFilters></query>
            <query name="YTDPrompt">
         <source>
            <model/>
         </source>
         <selection><dataItem name="YTD"><expression>descendants(rootmembers([great_outdoor_sales_en].[Years].[Years]),1)</expression></dataItem></selection>
      </query><query name="YTDMPrompt">
         <source>
            <model/>
         </source>
         <selection><dataItem name="YTDM"><expression>descendants(rootmembers([great_outdoor_sales_en].[Years].[Years]),3)</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>
                              <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
                                 <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></crosstabCorner>
                                 
                                 
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="xt"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              <crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="YTD Month" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="YTD" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Revenue"/></crosstab>
                           </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><selectValue parameter="ytd" multiSelect="false" range="false" required="true" refQuery="YTDPrompt" autoSubmit="true"><useItem refDataItem="YTD"/></selectValue><selectValue parameter="ytdm" multiSelect="false" range="false" required="true" refQuery="YTDMPrompt" cascadeOn="ytd"><useItem refDataItem="YTDM"/></selectValue></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="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>