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

Expression Parsing Error

Started by srinu1253, 13 Mar 2010 03:28:40 AM

Previous topic - Next topic

srinu1253

I have made Query using Joins for two Query Items so that it will be Flexible for Cascading.Then I have prepared two prompts with parameters
ass_item_1
Alt_BOM_1
and associated these parameters to the Query.
When I am tring to filter using these prompt in the Main Query I am getting Expression Parsing Error.

Can Any one help me plaese........

Below is the Report XML

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
   <modelPath>/content/package[@name='RMA']/model[@name='model']</modelPath>
   <layouts>
      <layout>
         <reportPages>
            <page class="pg" name="Page1">
               <pageBody class="pb">
                  <contents><list class="ls" refQuery="Main Query BOM1">
                        
                        
                        
                        <style>
                           <CSS value="border-collapse:collapse"/>
                        </style>
                     <listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="INVENTORY_ORGANIZATION_ID"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="INVENTORY_ORGANIZATION_ID"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="ASSEMBLY_ITEM_ID"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="ASSEMBLY_ITEM_ID"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="ORGANIZATION_CODE"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="ORGANIZATION_CODE"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="SEGMENT1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="SEGMENT1"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="ALT_BOM_DESIGNATOR"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="ALT_BOM_DESIGNATOR"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="ASSY_DESCRIPTION"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="ASSY_DESCRIPTION"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="COMPONENT_ITEM"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="COMPONENT_ITEM"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="COMP_DESCRIPTION"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="COMP_DESCRIPTION"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="COMPONENT_MFG_ITEM"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="COMPONENT_MFG_ITEM"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="PREFERENCE_CODE"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="PREFERENCE_CODE"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="ROHS"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="ROHS"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="MANUFACTURER_NAME"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="MANUFACTURER_NAME"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="ITM"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="ITM"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="COMPONENT_QUANTITY"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="COMPONENT_QUANTITY"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="COMPONENT_YIELD_FACTOR"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="COMPONENT_YIELD_FACTOR"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="INVENTORY_ITEM_ID"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="INVENTORY_ITEM_ID"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
                  </contents>
               </pageBody>
            <pageHeader><contents/></pageHeader><pageFooter><contents/></pageFooter></page>
         </reportPages>
      <promptPages><page class="pp" name="Prompt Page1">
               <pageHeader class="hp">
                  <contents>
                     <block class="ta">
                        <contents>
                           <textItem class="tt">
                              <dataSource>
                                 <staticValue/>
                              </dataSource>
                           </textItem>
                        </contents>
                     </block>
                  </contents>
               </pageHeader>
               <pageBody class="py">
                  <contents><table class="tb"><tableRows><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents><selectValue parameter="ASS_ITEM_1" refQuery="P_Ass_Item_BOM_Sheet1"><useItem refDataItem="SEGMENT1"><displayItem refDataItem="SEGMENT1"/></useItem></selectValue>
                                    </contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents><selectValue parameter="ALT_BOM_1" multiSelect="true" selectValueUI="checkboxGroup" required="false" refQuery="P_Ass_Item_BOM_Sheet1"><useItem refDataItem="ALT_BOM"><displayItem refDataItem="ALT_BOM"/></useItem></selectValue>
                                    </contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse;width:100%"/></style></table></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="Main Query BOM1">
         <source>
            <sqlQuery name="SQL1" dataSource="Oracle - DEV2">
               <sqlText>SELECT
     bbm.organization_id inventory_organization_id
    ,bbm.assembly_item_id
    ,mp.organization_code
    ,asi.segment1                     
    ,bbm.alternate_bom_designator     alt_bom_designator
    ,asi.description                  assy_description
    ,csi.segment1                     component_item
    ,csi.description                  comp_description
    ,mpn.mfg_part_num                 component_mfg_item
    ,mpn.attribute1                   preference_code
    ,mpn.attribute2                   RoHS
    ,mm.manufacturer_name
    ,bic.item_num                     itm
    ,bic.component_quantity
    ,bic.component_yield_factor
    ,mpn.inventory_item_id
FROM   
     apps.bom_bill_of_materials       bbm
    ,apps.mtl_parameters mp
    ,apps.bom_inventory_components    bic
    ,apps.mtl_mfg_part_numbers        mpn
    ,apps.mtl_manufacturers           mm
    ,apps.mtl_system_items_b          asi
    ,apps.mtl_system_items_b          csi
WHERE
     bbm.common_bill_sequence_id = bic.bill_sequence_id
AND  nvl(bic.disable_date, sysdate+1) &gt; sysdate
AND  bic.implementation_date is not null
AND  bbm.organization_id = mp.organization_id
--AND  mpn.organization_id = 94
AND  bic.component_item_id = mpn.inventory_item_id
AND  nvl(mpn.end_date, sysdate+1) &gt; sysdate
AND  mpn.attribute1 &lt;&gt; 'O'
AND  mpn.manufacturer_id = mm.manufacturer_id
AND  bbm.assembly_item_id = asi.inventory_item_id
AND  bbm.organization_id = asi.organization_id
AND  bic.component_item_id = csi.inventory_item_id
AND  bbm.organization_id = csi.organization_id
AND  bbm.organization_id = 94
UNION ALL
SELECT
     bbm.organization_id
    ,bbm.assembly_item_id
    ,mp.organization_code
    ,asi.segment1                     assembly_item
    ,bbm.alternate_bom_designator     alt_bom_designator
    ,asi.description                  assm_description
    ,csi.segment1                     component_item
    ,csi.description                  comp_description
    ,' '                              component_mfg_item
    ,' '                              preference_code
    ,' '                              RoHS
    ,' '                              manufacturer_name
    ,bic.item_num                     itm
    ,bic.component_quantity
    ,bic.component_yield_factor
    ,bic.component_item_id
FROM
     apps.bom_bill_of_materials       bbm
    ,apps.mtl_parameters              mp
    ,apps.bom_inventory_components    bic
    ,apps.mtl_system_items_b          asi
    ,apps.mtl_system_items_b          csi
WHERE
     bbm.common_bill_sequence_id = bic.bill_sequence_id
AND NOT EXISTS
    (SELECT null
     FROM   apps.mtl_mfg_part_numbers
     WHERE inventory_item_id = bic.component_item_id
     --AND   organization_id = 94
     )
AND  bbm.organization_id = mp.organization_id
AND  nvl(bic.disable_date, sysdate+1) &gt; sysdate
AND  bic.implementation_date is not null
AND  bbm.assembly_item_id = asi.inventory_item_id
AND  bbm.organization_id = asi.organization_id
AND  bic.component_item_id = csi.inventory_item_id
AND  bbm.organization_id = csi.organization_id
AND  bbm.organization_id = 94
</sqlText>
               <mdProjectedItems><mdProjectedItem name="INVENTORY_ORGANIZATION_ID"/><mdProjectedItem name="ASSEMBLY_ITEM_ID"/><mdProjectedItem name="ORGANIZATION_CODE"/><mdProjectedItem name="SEGMENT1"/><mdProjectedItem name="ALT_BOM_DESIGNATOR"/><mdProjectedItem name="ASSY_DESCRIPTION"/><mdProjectedItem name="COMPONENT_ITEM"/><mdProjectedItem name="COMP_DESCRIPTION"/><mdProjectedItem name="COMPONENT_MFG_ITEM"/><mdProjectedItem name="PREFERENCE_CODE"/><mdProjectedItem name="ROHS"/><mdProjectedItem name="MANUFACTURER_NAME"/><mdProjectedItem name="ITM"/><mdProjectedItem name="COMPONENT_QUANTITY"/><mdProjectedItem name="COMPONENT_YIELD_FACTOR"/><mdProjectedItem name="INVENTORY_ITEM_ID"/></mdProjectedItems></sqlQuery>
         </source>
         <selection><dataItem name="INVENTORY_ORGANIZATION_ID"><expression>[SQL1].[INVENTORY_ORGANIZATION_ID]</expression></dataItem><dataItem name="ASSEMBLY_ITEM_ID"><expression>[SQL1].[ASSEMBLY_ITEM_ID]</expression></dataItem><dataItem name="ORGANIZATION_CODE"><expression>[SQL1].[ORGANIZATION_CODE]</expression></dataItem><dataItem name="SEGMENT1"><expression>[SQL1].[SEGMENT1]</expression></dataItem><dataItem name="ALT_BOM_DESIGNATOR"><expression>[SQL1].[ALT_BOM_DESIGNATOR]</expression></dataItem><dataItem name="ASSY_DESCRIPTION"><expression>[SQL1].[ASSY_DESCRIPTION]</expression></dataItem><dataItem name="COMPONENT_ITEM"><expression>[SQL1].[COMPONENT_ITEM]</expression></dataItem><dataItem name="COMP_DESCRIPTION"><expression>[SQL1].[COMP_DESCRIPTION]</expression></dataItem><dataItem name="COMPONENT_MFG_ITEM"><expression>[SQL1].[COMPONENT_MFG_ITEM]</expression></dataItem><dataItem name="PREFERENCE_CODE"><expression>[SQL1].[PREFERENCE_CODE]</expression></dataItem><dataItem name="ROHS"><expression>[SQL1].[ROHS]</expression></dataItem><dataItem name="MANUFACTURER_NAME"><expression>[SQL1].[MANUFACTURER_NAME]</expression></dataItem><dataItem name="ITM"><expression>[SQL1].[ITM]</expression></dataItem><dataItem name="COMPONENT_QUANTITY"><expression>[SQL1].[COMPONENT_QUANTITY]</expression></dataItem><dataItem name="COMPONENT_YIELD_FACTOR"><expression>[SQL1].[COMPONENT_YIELD_FACTOR]</expression></dataItem><dataItem name="INVENTORY_ITEM_ID"><expression>[SQL1].[INVENTORY_ITEM_ID]</expression></dataItem></selection>
      <detailFilters><detailFilter><filterExpression>[SEGMENT1] LIKE ?ASS_ITEM_1?</filterExpression></detailFilter><detailFilter><filterExpression>[ALT_BOM_DESIGNATOR] IN ?ALT_BOM_1?</filterExpression></detailFilter></detailFilters></query>
      <query name="P_Ass_Item_BOM_Sheet1">
         <source>
            
         <sqlQuery name="SQL2" dataSource="Oracle - DEV2">
               <sqlText>select m.segment1,b.alternate_bom_designator from mtl_system_items_b m,bom_bill_of_materials b,org_organization_definitions o where m.inventory_item_id=b.assembly_item_id
and b.organization_id=m.organization_id
and o.organization_id=m.organization_id</sqlText>
               <mdProjectedItems><mdProjectedItem name="SEGMENT1"/><mdProjectedItem name="ALTERNATE_BOM_DESIGNATOR"/></mdProjectedItems></sqlQuery>
         </source>
         <selection><dataItem name="SEGMENT1"><expression>[SQL2].[SEGMENT1]</expression></dataItem><dataItem name="ALTERNATE_BOM_DESIGNATOR"><expression>[SQL2].[ALTERNATE_BOM_DESIGNATOR]</expression></dataItem><dataItem name="ALT_BOM"><expression>if([ALTERNATE_BOM_DESIGNATOR] is null)
then ('NULL')
else
([ALTERNATE_BOM_DESIGNATOR])</expression></dataItem></selection>
      </query>
   </queries></report>

MFGF

Meep!

srinu1253

The Error is Expression Parsing Error as I have filtered using the date column I have to use after Auto aggregate option.

I have used this and I was able to Resolve