This discussion is archived
4 Replies Latest reply: Jan 11, 2013 7:38 AM by steverHFMadmin RSS

Using bind variables in Shared Component Reports not working

steverHFMadmin Newbie
Currently Being Moderated
I've created a report query as a shared component in my APEX application. I'm calling the report on a page via a button. The query behind the report has bind variables for period and entity fields which are both defined on Page 0. I can run the query and return values just fine, but when you run the report from the page via the button, I get no values back. Does anyone know a trick to getting this to work? I've been working in APEX for about a month and I don't have much developer experience so any help is appreciated.

Regards,

-Steve
  • 1. Re: Using bind variables in Shared Component Reports not working
    714270 Pro
    Currently Being Moderated
    What version of Apex are you using? It is always better to state your Apex version when you post queries in the forum.

    Now, I do not have experience in the older versions of Apex. In Apex 4.x, in the "Edit Report Query" page (under shared components -> report queries), there is a region called "Session State". You can add your page items that you are referencing in your report query in the list there.
    Let me know if that resolves your issue.
  • 2. Re: Using bind variables in Shared Component Reports not working
    steverHFMadmin Newbie
    Currently Being Moderated
    Rohit,

    Thanks for your response. We are using APEX version 4.2.1.00.08. I do have Session State enabled and have put the required page items in the list. Here is my report query (highlighted lines contain the bind variables):

    SELECT TAX_DIVISION.DIVISION_CODE Division,
    TAX_ENTITY.ENTITY_CODE Entity_Code,
    DECODE(TAX_ADJUSTMENT.CATEGORY_DESC,'Net Income/(Loss)',1,'Permanent Adjustment',2,'Temporary Adjustment',3,4) Category_Desc,
    TAX_ADJUSTMENT.CLASSIFICATION Classification,
    TAX_ADJUSTMENT.ADJUSTMENT_CODE Adjustment_Code,
    SUM(NVL(NVL(TAX_ENTITY_ADJUSTMENT.OVERRIDE_AMT,TAX_ENTITY_ADJUSTMENT.ADJUSTMENT_AMT),0)) Tax_Amount
    FROM TAX_DIVISION,
    TAX_ENTITY,
    TAX_ENTITY_ADJUSTMENT,
    TAX_ADJUSTMENT,
    TAX_PERIOD
    WHERE TAX_DIVISION.DIVISION_ID = TAX_ENTITY.DIVISION_ID
    AND TAX_ENTITY.ENTITY_ID = TAX_ENTITY_ADJUSTMENT.ENTITY_ID
    AND TAX_ENTITY_ADJUSTMENT.ADJUSTMENT_ID = TAX_ADJUSTMENT.ADJUSTMENT_ID
    AND  TAX_ENTITY_ADJUSTMENT.PERIOD_ID = :P0_PERIOD_ID
    AND  TAX_ENTITY_ADJUSTMENT.YEAR_ID = :P0_YEAR_ID
    AND  TAX_ENTITY.DIVISION_ID = :P0_DIVISION_ID
    AND TAX_ADJUSTMENT.ADJUSTMENT_CODE = 'IBIT'
    GROUP BY TAX_DIVISION.DIVISION_CODE ,
    TAX_ENTITY.ENTITY_CODE ,
    DECODE(TAX_ADJUSTMENT.CATEGORY_DESC,'Net Income/(Loss)',1,'Permanent Adjustment',2,'Temporary Adjustment',3,4),
    TAX_ADJUSTMENT.CLASSIFICATION ,
    TAX_ADJUSTMENT.ADJUSTMENT_CODE
    HAVING SUM(NVL(NVL(TAX_ENTITY_ADJUSTMENT.OVERRIDE_AMT,TAX_ENTITY_ADJUSTMENT.ADJUSTMENT_AMT),0)) !=0
    ORDER BY DIVISION_CODE, DECODE(TAX_ADJUSTMENT.CATEGORY_DESC,'Net Income/(Loss)',1,'Permanent Adjustment',2,'Temporary Adjustment',3,4), CLASSIFICATION, ENTITY_CODE, ADJUSTMENT_CODE


    The query returns data when I set the bind variables and test in the report queries screen. When I assign the report query URL to the button in the application and run from there, I get no results.
  • 3. Re: Using bind variables in Shared Component Reports not working
    VivekAnanda Newbie
    Currently Being Moderated
    Steve,
    Try v function and see if helps.
    v('P0_DIVISION_ID')
    v('P0_YEAR_ID')
    v('P0_PERIOD_ID)

    Vivek
  • 4. Re: Using bind variables in Shared Component Reports not working
    steverHFMadmin Newbie
    Currently Being Moderated
    Vivek (and Rohit),

    Turns out this was user error on my part. My bind variables were referring to the wrong page items. I had P0_DIVISION_ID, P0_YEAR_ID, and P0_PERIOD_ID. I should have had P0_DIVISION, P0_YEAR, and P0_PERIOD.

    I did confirm that Viveks syntax and the syntax I was using before both work with the correct bind variable settings. Thanks to both of you for your assistance.

    -Steve

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points