X

An Oracle blog about BI Publisher

Lexical Reference Problems

Been in an interesting forum/email thread over the last few days with Naushad

I have a data template with a lexical parameter that is used in the SELECT caluse of the query.

DATA Template


<parameter name.............................../>

<parameter name="P_BREAK_COLUMN" dataType="varchar2"/>

<parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/>

</parameters>

<lexicals>

</lexicals>

<dataQuery>

<sqlStatement name="Q_DATA">

<![CDATA[

SELECT &P_BREAK C_BREAK,

gcc.segment4 ,

exp.je_category ,

exp.vendor_number ,

exp.vendor_name ,

exp.invoice_number ,

exp.invoice_date ,

SUM (NVL (exp.accounted_dr, 0) - NVL (exp.accounted_cr, 0)) accounted_amt,

ffv4.description account_desc

FROM xxcus.XXGL_XGLOGEXP exp,

gl_code_combinations gcc,

fnd_flex_values_vl ffv4,

fnd_flex_value_sets ffs4

WHERE exp.code_combination_id = gcc.code_combination_id

AND ffs4.flex_value_set_id(+) = ffv4.flex_value_set_id

AND ffv4.flex_value(+) = gcc.segment4

AND ffs4.flex_value_set_name = 'ACCT_AFF'

GROUP BY &P_BREAK ,

gcc.segment4 ,

exp.je_category ,

exp.vendor_number ,

exp.vendor_name ,

exp.invoice_number ,

exp.invoice_date ,

ffv4.description

]]>

</sqlStatement>

</dataQuery>

<dataTrigger name="beforeReportTrigger" source="XXGL_XGLOGEXP_PKG.BeforeReport"/>

<dataStructure>

<group name="G_DATA" dataType="varchar2" source="Q_DATA">

<element name="C_BREAK" dataType="varchar2" value="C_BREAK"/>

<element name="SEGMENT4" dataType="varchar2" value="SEGMENT4"/>

<element name="JE_CATEGORY" dataType="varchar2" value="JE_CATEGORY"/>

...

The beforeReportTrigger looks like this:

FUNCTION BeforeReport RETURN BOOLEAN IS

BEGIN

IF P_BREAK_COLUMN = 'Location' THEN

P_BREAK := 'gcc.segment2';

ELSIF p_BREAK_COLUMN = 'Department' THEN

P_BREAK := 'gcc.segment3';

END IF;

fnd_file.put_line(fnd_file.log,'P_BREAK --> '||P_BREAK);

END;



The parameter starts with a default of "gcc.segment3". On execution of this report in EBS, the beforeReportTrigger fires and changes the value of P_BREAK to "gcc.segment2" (I can see that change in the concurrent reqs LOG file).

But when the SQL is executed, it still holds the default value. The fact that beforeReport has changed the value to segment2 is not visible to the SQL in the data XML template.

I have to admit I was fishing around for answer from development but Naushad continued to tinker with it and found the answer. Another gap in our doc we need to fill!

I found the source of my problem.

I have worked in Reports 9i for many years (since 1996 when it was version 4.5) and when I transition to XML data template seems like I have to unlearn some of that.

In Reports 9i, we define parameters in the “User Parameters” section. Those parameters can be assigned different values in the “before report” trigger. Essentially, we change the value of the parameter and the new values are visible to the SQL in the Data Model.

In XML data template, the parameters defined in the section are in a read only format. Any changes by the beforeReport trigger, to the variables in the parameter section are not visible to the SQL in the section.

I was able to resolve it by commenting out the declaration of parameter P_BREAK.

P_BREAK is defined as variable in the package spec and the beforeReport trigger changes the value as required.

<parameters>

……………………..

<!-- <parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/> -->

</parameters>

<lexicals>

</lexicals>

<dataQuery>

<sqlStatement name="Q_DATA">

<![CDATA[

SELECT &P_BREAK C_BREAK,

gcc.segment4 ,……

FROM gl_code_combinations gcc, …..


The take away from the solution - the parameters in the data template are not updatable and you can declare public variables in your before report trigger and reference them inside the data template without the need to declare them.

Join the discussion

Comments ( 8 )
  • Ike Wiggins, Minneapolis, MN Tuesday, July 15, 2008
    I created a SR for this in April-2008, when reports get converted from reports6i to bip using the DataTemplateGenerator class.
    sr: 6841521.992
    bug: 6939880
    Ike Wiggins
    http://bipublisher.blogspot.com
  • Paul MacDonald Tuesday, July 22, 2008
    In SR 6901490.993
    On BIP Enterprise 10.1.3.2:
    1. A data template has a sql query that uses a sysdate value
    for the date parameter and subtracts 3 days from the sysdate.
    2. The purpose of this is to schedule a report to run on Wednesday
    but to put the previous Sunday's date on it.
    3. The report, when run as scheduled on Wednesday, is instead putting the previous Saturday's date (subtracting 4 days) as the report date.
    4. When the same report is run manually (not scheduled), on Wednesday, it correctly gives the correct previous Sunday date as the report date.
    5. Also, running the sql query itself returns the correct (subtracted 3 days) date.
    So, something is happening 'under the covers' in whatever programming handles the scheduler for BIP Enterprise.
    Is this piece of scheduler programming:
    1. ours to bug?
    2. even a bug at all?
    Thanks
    Hi Paul
    I asked for your SR number in another comment thread.
    Tim
  • sidharth Tuesday, October 21, 2008
    Hi Tim,
    I've created a data template with one parameter and it had LOV..
    in the query i have to select single,multiple values(one or more),all values for the parameter..the report is running fine for one value and all values..but when I select 2 values its giving an error some thing like this..
    ORA-06502:PL/SQL:numeric or value error: character to number conversion err
    as i am using lov i tried changing the datatype of the parameter as char in the parameter declaration part and this time i got a different error regarding buffer size..
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    i tried changing the datatype as varchar2,char,character and increased the size...but still i have the same error...
    my thread in oracle.forums for the error
    http://forums.oracle.com/forums/thread.jspa?threadID=720273&tstart=0
    Thanks..
  • Sarah Monday, February 23, 2009
    I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
    Sarah
    http://www.craigslistdecoded.info
  • Tammy Cosby Wednesday, April 29, 2009
    I am rrying to find a way to pass BI Publisher username information from when the user logs in to Bi Publisher to a report query, hence restricting access based on the username in the query. I don't want to use a VPD feature
  • Tammy Cosby Wednesday, April 29, 2009
    I am rrying to find a way to pass BI Publisher username information from when the user logs in to Bi Publisher to a report query, hence restricting access based on the username in the query. I don't want to use a VPD feature
  • Joe Leiba Wednesday, May 6, 2009
    Immutable report parameters can be a problem from another perspective. Often you want to include the values of parameters in the XML output, which BIP can do, but how then can you show the values of lexical references that were derived from report parameters? In other words, the values you're actually using in your query cannot be displayed. One would often prefer to show these parameters in the header of the output report.
  • May Monday, February 13, 2012

    Thank you so much for this artical. It saved me lots of digging around.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.