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