COGNOiSe.com - The IBM Cognos Community

Hi All,

Please someone helps me with this issue (as u always did...hehe...many thanks to all of u!!!).

I obtain a report like this one below:

Organization    Type    Total     %   
-----------------------------------
       UK           PM         25     25   
       UK           LS         25      25
       Uk           MEC       50     50

Now I wanna show in the report just one type, for example I wanna show only Type 'PM'.
But I dont wanna change my SQL select because I need all the types in order to calculate the percentage.

Is it possible to display only type 'PM' just working on the layout???

If not any other suggestion?

Thanks in advance.

Matt

How about using the filter to achieve what you want?

Regards,
~Harish

Good suggestion Harish!

The point is that I usually use the filters (or prompts) just to ask the user what he wants to obtain.

In this case, I dont want to ask the user anything, but I just want to obtain 1 row, the one with 'PM' type.

How can i perform this???

Thanks Matt

Ya Morphy, filter won't help u out in ur case.
This might help - http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos-l/hide-rows-in-list-based-upon-a-value-in-one-column-1901688

Seems to be an useful information for the group member.

Morphy, pls. try the same as suggested in the thread and revert with the result.

Regards,
~Harish

Hi Morphy,

Found the solution to ur problem ( Took some time but huh...finally :-)
1) Make a "BOOLEAN variable" with Expression "Type = PM" (call it BOOLEAN1)
2) Now set the "Style Variable" of all the columns to BOOLEAN1
3) Then Select "NO" from the "Variable Explorer" (BAR will turn GREEN) and set the "Box Type" propoerty to "NONE".

DONE.

Regards,
Tarun

Hi Tarun,

U r very kind and great...hehe...

U solved me 2 problems with an answer.

I fixed the layout problem and I fixed also the problem I messaged u in private about the wrong result when deleting the Date or the WorkOrderID column from the report.
I left the Date column in the report and with the same boolean variable created before, I just did hide the Date.

And it works perfectly!

I have the last problem now...

I am not able to create a cascading prompt.
I mean, I know how to create it, but it does not work in my report.

I put 'Autosubmit' = Yes in the primary prompt and I put the primary prompt as Cascading source in the secondary prompt.

The result is that the primary prompt works but the second is 'ghost'.

I just did not use a Table in the prompt page.
Could this fact be the reason???

Or maybe the problem is connected with the Queries of the 2 prompts?

Please help me.

Maybe u can find out the problem from the report I sent u in private.

Otherwise I can send u the 2 queries connected with the prompts.

Ciao

Matt

Hi Morphy,

Thanks for the kind words :)
-Using a table doesnt have anything to do with casacding prompt so deftntly its not a problem.
-Send me the report having these cascading prompts for better understanding.

Regards,
Tarun

Hi Tarun,

thank u very much for all what u r doing to me.
I appreciate.

Here below u find the report with the problem in cascading prompts.

At the very bottom I put the 2 queries I use for the 2 prompts.

Many THANKS

Ciao

Matt


******************************
REPORT
******************************




<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="it"><!--RS:8.2-->
   <modelPath>/content/package[@name='InforPackage_83']/model[@name='model']</modelPath>
   <layouts>
      <layout>
         <reportPages>
            <page class="pg" name="Pagina1">
               <pageBody class="pb">
                  <contents><list class="ls" refQuery="Query1">
                        <style>
                           <CSS value="border-collapse:collapse"/>
                        </style>
                        <listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="EOB_OBJECT"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="EOB_OBJECT"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="EOB_OBJECT"/><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="EOB_OBJECT_ORG"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="EOB_OBJECT_ORG"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="EOB_OBJECT_ORG"/><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="EVT_JOBTYPE"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="EVT_JOBTYPE"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="EVT_JOBTYPE"/><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="subtotale"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="subtotale"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="subtotale"/><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="totale"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="totale"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="totale"/><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="percentuale"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="percentuale"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="percentuale"/><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="BOO_EVENT"/></dataSource></textItem></contents><style><CSS value="display:none"/></style></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="BOO_EVENT"/></dataSource></textItem></contents><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault/></conditionalStyles><style><CSS value="display:none"/></style></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="EOB_OBJECT"/><listGroup refDataItem="EOB_OBJECT_ORG"/><listGroup refDataItem="EVT_JOBTYPE"/><listGroup refDataItem="subtotale"/><listGroup refDataItem="totale"/><listGroup refDataItem="percentuale"><sortList><sortItem refDataItem="percentuale" sortOrder="descending"/></sortList></listGroup></listGroups><listColumnStyle><conditionalStyles><conditionalStyleCases refVariable="Booleano1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault/></conditionalStyles></listColumnStyle></list>
                  </contents>
               </pageBody>
            </page>
         </reportPages>
      <promptPages><page class="pp" name="Pagina di richiesta1">
               <pageHeader class="hp">
                  <contents>
                     <block class="ta">
                        <contents>
                           <textItem class="tt">
                              <dataSource>
                                 <staticValue/>
                              </dataSource>
                           </textItem>
                        </contents>
                     </block>
                  </contents>
               </pageHeader>
               <pageBody class="py">
                  <contents><selectValue parameter="org" refQuery="Query_Org" multiSelect="false" range="false" required="true" autoSubmit="true"><useItem refDataItem="organizzazione"><displayItem refDataItem="descrizione"/></useItem></selectValue>
                  <selectDate parameter="begin_date" multiSelect="false" range="false" required="true"/>
                     <selectDate parameter="end_date" multiSelect="false" range="false" required="true"/>
                  <selectValue parameter="stato" refQuery="Query_Stato" multiSelect="false" range="false" required="true"><useItem refDataItem="stato"><displayItem refDataItem="descrizione"/></useItem></selectValue>
                  <selectValue parameter="ogg" refQuery="Query_ogg" multiSelect="false" range="false" required="true" cascadeOn="org"><useItem refDataItem="oggetto"><displayItem refDataItem="descrizione"/></useItem></selectValue>
                  </contents>
               </pageBody>
               <pageFooter class="fp">
                  <contents>
                     <promptButton type="cancel" class="bp">
                        <contents/>
                     </promptButton>
                     <promptButton type="back" class="bp">
                        <contents/>
                     </promptButton>
                     <promptButton type="next" class="bp">
                        <contents/>
                     </promptButton>
                     <promptButton type="finish" class="bp">
                        <contents/>
                     </promptButton>
                  </contents>
               </pageFooter>
            </page>
         </promptPages></layout>
   </layouts>
<queries><query name="Query1">
         <source>
            <sqlQuery name="SQL1" dataSource="AdvRepData">
               <sqlText>SELECT DISTINCT
       R5BOOKEDHOURS.BOO_EVENT,
       R5BOOKEDHOURS.BOO_HOURS,
       R5BOOKEDHOURS.BOO_DATE,
       R5BOOKEDHOURS.BOO_PERSON,
       R5BOOKEDHOURS.BOO_ENTERED,
       R5EVENTOBJECTS.EOB_OBJECT,
       R5EVENTOBJECTS.EOB_LEVEL,
       R5EVENTS.EVT_OBJECT,
       R5EVENTS.EVT_STATUS,
       R5EVENTS.EVT_TYPE,
       R5EVENTS.EVT_JOBTYPE,
       R5EVENTOBJECTS.EOB_OBJECT_ORG
  FROM R5EVENTOBJECTS INNER JOIN
                      R5BOOKEDHOURS ON R5EVENTOBJECTS.EOB_EVENT = R5BOOKEDHOURS.BOO_EVENT INNER JOIN
                      R5EVENTS ON R5EVENTOBJECTS.EOB_EVENT = R5EVENTS.EVT_CODE
WHERE     (R5EVENTS.EVT_JOBTYPE NOT IN ('CAP', 'PA'))
ORDER BY R5BOOKEDHOURS.BOO_EVENT</sqlText>
               <mdProjectedItems><mdProjectedItem name="BOO_EVENT"/><mdProjectedItem name="BOO_HOURS"/><mdProjectedItem name="BOO_DATE"/><mdProjectedItem name="BOO_PERSON"/><mdProjectedItem name="BOO_ENTERED"/><mdProjectedItem name="EOB_OBJECT"/><mdProjectedItem name="EOB_LEVEL"/><mdProjectedItem name="EVT_OBJECT"/><mdProjectedItem name="EVT_STATUS"/><mdProjectedItem name="EVT_TYPE"/><mdProjectedItem name="EVT_JOBTYPE"/><mdProjectedItem name="EOB_OBJECT_ORG"/></mdProjectedItems></sqlQuery>
         </source>
         <selection><dataItem name="BOO_HOURS"><expression>[SQL1].[BOO_HOURS]</expression></dataItem><dataItem name="BOO_DATE"><expression>[SQL1].[BOO_DATE]</expression></dataItem><dataItem name="EOB_OBJECT" label="Oggetto"><expression>[SQL1].[EOB_OBJECT]</expression></dataItem><dataItem name="EVT_STATUS"><expression>[SQL1].[EVT_STATUS]</expression></dataItem><dataItem name="EVT_JOBTYPE" label="Tipo OdL"><expression>[SQL1].[EVT_JOBTYPE]</expression></dataItem><dataItem name="EOB_OBJECT_ORG" label="Organizzazione"><expression>[SQL1].[EOB_OBJECT_ORG]</expression></dataItem><dataItem name="subtotale" label="Totale Parziale"><expression>total([BOO_HOURS] for [EVT_JOBTYPE])</expression></dataItem><dataItem name="totale" label="Totale"><expression>total([subtotale]for report)</expression></dataItem><dataItem name="percentuale" label="Percentuale"><expression>case
when ([EVT_JOBTYPE]='PM')
then ([subtotale]*100)/[totale]
else
([subtotale]*100)/[totale]
end</expression></dataItem><dataItem name="BOO_EVENT"><expression>[SQL1].[BOO_EVENT]</expression></dataItem></selection>
      <detailFilters><detailFilter use="required" postAutoAggregation="true"><filterExpression>[EOB_OBJECT_ORG]=?org?</filterExpression></detailFilter><detailFilter use="required" postAutoAggregation="true"><filterExpression>[BOO_DATE]&gt;=?begin_date?</filterExpression></detailFilter><detailFilter use="required" postAutoAggregation="true"><filterExpression>[BOO_DATE]&lt;=?end_date?</filterExpression></detailFilter><detailFilter use="optional" postAutoAggregation="true"><filterExpression>[EVT_STATUS]=?stato?</filterExpression></detailFilter><detailFilter use="required" postAutoAggregation="true"><filterExpression>[EOB_OBJECT]=?ogg?</filterExpression></detailFilter></detailFilters></query>
   <query name="Query_Org"><source><sqlQuery name="SQL2" dataSource="AdvRepData">
               <sqlText>select org_code as organizzazione, org_desc as descrizione
from R5organization
order by org_desc asc</sqlText>
               <mdProjectedItems><mdProjectedItem name="organizzazione"/><mdProjectedItem name="descrizione"/></mdProjectedItems></sqlQuery>
         </source><selection><dataItem name="organizzazione"><expression>[SQL2].[organizzazione]</expression></dataItem><dataItem name="descrizione"><expression>[SQL2].[descrizione]</expression></dataItem></selection></query><query name="Query_Stato"><source><sqlQuery name="SQL3" dataSource="AdvRepData">
               <sqlText>SELECT UCO_CODE as stato, UCO_DESC AS descrizione
FROM R5UCODES
WHERE UCO_RENTITY = 'EVST'
AND UCO_RCODE NOT IN ('A', 'B', 'Q')
</sqlText>
               <mdProjectedItems><mdProjectedItem name="stato"/><mdProjectedItem name="descrizione"/></mdProjectedItems></sqlQuery>
         </source><selection><dataItem name="stato"><expression>[SQL3].[stato]</expression></dataItem><dataItem name="descrizione"><expression>[SQL3].[descrizione]</expression></dataItem></selection></query><query name="Query_ogg"><source><sqlQuery name="SQL4" dataSource="AdvRepData">
               <sqlText>select obj_code as oggetto, obj_desc as descrizione
from r5objects
order by obj_desc asc</sqlText>
               <mdProjectedItems><mdProjectedItem name="oggetto"/><mdProjectedItem name="descrizione"/></mdProjectedItems></sqlQuery>
         </source><selection><dataItem name="oggetto"><expression>[SQL4].[oggetto]</expression></dataItem><dataItem name="descrizione"><expression>[SQL4].[descrizione]</expression></dataItem></selection></query></queries><reportVariables><reportVariable type="boolean" name="Booleano1">
         <reportExpression>[Query1].[EVT_JOBTYPE]='BRKD'</reportExpression>
         <variableValues>
            <variableValue value="1"/>
         </variableValues>
      </reportVariable>
   </reportVariables></report>



********************************
QUERY FOR THE FIRST PROMPT
********************************

select org_code as organizzazione, org_desc as descrizione
from R5organization
order by org_desc asc

*********************************
QUERY FOR THE SECOND PROMPT
*********************************

select obj_code as oggetto, obj_desc as descrizione
from r5objects
order by obj_desc asc

Ur prompts seem fine ... Cascading prompts properties are also set correctly.
As i dont have the package here , so i cant test them although.
I will suggest u to run the queries directly on DB and check if corresponding "child entries" are coming for the "parent prompt".
PFA attached sample report for cas-prompt ... Made on the similar format like ur report.
Hope it helps.

Regards,
Tarun

Hi Tarun,

Thanks very much for yr help!

I checked the attached PDF and it seems ok.

I just dont understand what u mean when u suggest me to run the queries directly on DB and check if corresponding
"child entries" are coming for the "parent prompt".

As u can see below the 2 queries are very simple.

The first one (primary) returns the codes of the Organization:

SELECT ORG_CODE FROM R5ORGANIZATION;

The second one (secondary) returns the codes of the Objects:

SELECT OBJ_CODE FROM R5OBJECTS;

I am thinking right now...do i have to put the First Parameter in the WHERE clause of the Secondary Prompt?

I mean:

SELECT OBJ_CODE FROM R5OBJECTS WHERE OBJ_ORG=?org?

where ?org? is the name of the first prompt. ???

Thanks in advance

Matt