This content has been marked as final. Show 4 replies
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.
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,
DECODE(TAX_ADJUSTMENT.CATEGORY_DESC,'Net Income/(Loss)',1,'Permanent Adjustment',2,'Temporary Adjustment',3,4) Category_Desc,
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 ,
DECODE(TAX_ADJUSTMENT.CATEGORY_DESC,'Net Income/(Loss)',1,'Permanent Adjustment',2,'Temporary Adjustment',3,4),
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.
Try v function and see if helps.
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.