Oracle Analytics

Get Involved. Join the Conversation.

Topic

    Mthompson
    Suppressing rows in BI Pub Excel template
    Topic posted June 4, 2019 by Mthompson Red Ribbon: 250+ Points, last edited September 11, 2019 
    71 Views, 6 Comments
    Title:
    Suppressing rows in BI Pub Excel template
    Summary:
    Need to figure out how to suppress rows in a BI Publisher Excel template
    Content:

    I am following the instructions found here:

    https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID-30710EE5-ED97-4489-8E48-642BCACD1837.htm#GUID-88EDBF14-41BC-48AE-A869-496BC7F39E8B

    in an attempt to suppress rows in an Excel-template for BI Publisher. However, I am unable to get the appropriate row to suppress. 

    I have attached the .xml data file that I am using, as well as the .xls file.  As far as I can tell, I have followed the instructions to the letter, but no rows are suppressed.  I still see all three employees.

    Can someone who has the BI Publisher add-in for MS Office open this xls, attach the xml, confirm that you see three rows, and possibly tell me what I am doing wrong?  (I am using the add-in for BIP 11.1.1.9, but I would probably be satisfied with an answer on any version.)

    Thanks!!

    Document:
    Document:

    Comment

     

    • Suresh

      Oracle BI Publisher Desktop Installtion will have sample templates. If you have BI Publisher Desktop installed on your machine go to below location and check this template.(AllEmployeesWithoutManagers.xls) to compare against yours.

      C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\samples\Excel templates\Skiprow Sample Report

      In past we used to 'Save As' sample excel template provided by oracle, and work on it based on your requirements. Instead of building the template in excel from scratch.

      Let me know if this helps.

       

      • Mthompson

        Suresh, this example was indeed useful, but (unfortunately) only in helping me to decide that what I really want to do cannot be done.  This example uses an attribute of a field in the XML data (specifically, the @manager attribute on the Employee_ID field).  What I really want is to be able to test the value of the field itself, and not one of its attributes.

        Example:  The template you mentioned uses this condition, which works just fine.

        <xsl:if test="string-length(./EMPLOYEE_ID/@MANAGER) != 0">
        <xsl:value-of select="./EMPLOYEE_ID/@MANAGER"/>
        </xsl:if>

        But what I really want to do is make this sort of comparison, where I am using the value of the field itself, not one of its attributes:

        <xsl:if test="string-length(EMAIL) != 7">
        <xsl:value-of select="EMAIL" />
        </xsl:if>

        If that syntax worked, I would expect to SKIP rows that do not have a 7-character email. Instead, I see all rows.  I have messed around with this for about an hour, and have not struck upon the correct set of magic words. 

        Is what I want to do possible?  If so, can you provide the correct syntax?

         

         

         

    • Noelle Bartlam

      You might be able to accomplish this using an XSLT file.

      https://docs.oracle.com/middleware/bi12214/bip/BIPRD/GUID-F926959D-B7A5-41DE-8388-2951666A38EE.htm#BIPRD2843

    • Mthompson

      Thank you, Noelle. That gives me another path to explore to try to figure this out.  The document mentions that XSLT can add style attributes to data elements, which sounds like what I might need.  But alas, it does not provide an example, so I will have to add that to my ever-growing list of things to figure out. 

      I really would like to know if the @attribute method is truly the only way to accomplish my goal.  I don't know very much about XSL, but it just seems odd that a comparison against a data element value would not be a valid method of determining a true/false condition.

      Thanks again!

    • Mthompson

      I would be quite happy to use an RTF.  My client really, really wants to use XLS.