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.

Comments:

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

Posted by Ike Wiggins, Minneapolis, MN on July 15, 2008 at 03:23 AM MDT #

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

Posted by Paul MacDonald on July 22, 2008 at 01:41 AM MDT #

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..

Posted by sidharth on October 21, 2008 at 02:48 AM MDT #

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

Posted by Sarah on February 22, 2009 at 07:41 PM MST #

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

Posted by Tammy Cosby on April 29, 2009 at 03:35 AM MDT #

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

Posted by Tammy Cosby on April 29, 2009 at 05:34 AM MDT #

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.

Posted by Joe Leiba on May 06, 2009 at 04:06 AM MDT #

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

Posted by May on February 12, 2012 at 05:57 PM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today