Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
i am getting below error while running Adhoc XML query in obiee10g
Hi All,
its a AD-Hoc XML query.This XML returns data when report is ran by admin but returns no data when user run this.
The specified criteria didn't result in any data. This is often caused by applying filters that are too restrictive or that contain incorrect values. Please check your Request Filters and try again. The filters currently being applied are shown below
what could be the reason.any one have idea.
thanks in advance.
Regards,
sudheer
Answers
-
3231180 wrote: The specified criteria didn't result in any data. This is often caused by applying filters that are too restrictive or that contain incorrect values. Please check your Request Filters and try again. The filters currently being applied are shown below what could be the reason.any one have idea.
Yes that just means that for the acutal user the data level filters kick in. Have you looked at the actual queries being fired against the source databases? You'll most likely see new WHERE clauses added, driven by either filters in the analysis, filters in the RPD or VPD connections to the database or simply user hand-over to the database which causes new restrictions in the database itself.
0 -
Hi Christian,
Thankyou so much for quick reply..yeah,i looked at actual Sql query generated by this Ad-Hoc XML query.
query:
WITH
SAWITH0 AS (select D1.c1 as c1, D1.c2 as c2,D1.c3 as c3,D1.c4 as c4, D1.c5 as c5, D1.c6 as c6
from
(select sum(T158659.HIRE_EVENT_IND) as c1,
T68497.EMPLOYEE_NUM as c2,
T68497.FULL_NAME as c3,
T68497.X_PREV_EMPLOYER as c4,
T154347.INTEGRATION_ID as c5,
T68497.INTEGRATION_ID as c6,
ROW_NUMBER() OVER (PARTITION BY T68497.INTEGRATION_ID, T154347.INTEGRATION_ID ORDER BY T68497.INTEGRATION_ID
ASC, T154347.INTEGRATION_ID ASC) as c7
from
W_JOB_RQSTN_D T154347 /* Dim_W_JOB_RQSTN_D */ ,
W_EMPLOYEE_D T68497 /* Dim_W_EMPLOYEE_D */ ,
W_EMPLOYMENT_D T95816 /* Dim_W_EMPLOYMENT_D */ ,
W_MONTH_D T100027 /* Dim_W_MONTH_D */ ,
W_WRKFC_EVT_MONTH_F T158659 /* Fact_W_WRKFC_EVT_MONTH_F_Event */
where ( T68497.ROW_WID = T158659.EMPLOYEE_WID and T95816.ROW_WID = T158659.EMPLOYMENT_WID and
T95816.W_EMPLOYEE_CAT_DESC = 'Employee' and T100027.ROW_WID = T158659.EVENT_MONTH_WID and T154347.ROW_WID =
T158659.X_JOB_RQSTN_WID and T158659.DELETE_FLG <> 'Y' and T100027.CAL_MONTH_START_DT <= TO_DATE('2016-06-21' , 'YYYY-MM-DD')
and T158659.EFFECTIVE_START_DATE <= TO_DATE('2016-06-21 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and T100027.PER_NAME_MONTH
between '2015 / 05' and '2016 / 04' )
group by T68497.INTEGRATION_ID, T68497.FULL_NAME, T68497.EMPLOYEE_NUM, T68497.X_PREV_EMPLOYER,
T154347.INTEGRATION_ID
having nvl(sum(T158659.HIRE_EVENT_IND) , 0) <> 0
) D1
where ( D1.c7 = 1 ) ),
SAWITH1 AS (select sum(T158659.HIRE_EVENT_IND) as c1
from
W_EMPLOYMENT_D T95816 /* Dim_W_EMPLOYMENT_D */ ,
W_MONTH_D T100027 /* Dim_W_MONTH_D */ ,
W_WRKFC_EVT_MONTH_F T158659 /* Fact_W_WRKFC_EVT_MONTH_F_Event */
where ( T95816.ROW_WID = T158659.EMPLOYMENT_WID and T95816.W_EMPLOYEE_CAT_DESC = 'Employee' and T100027.ROW_WID =
T158659.EVENT_MONTH_WID and T158659.DELETE_FLG <> 'Y' and T100027.CAL_MONTH_START_DT <= TO_DATE('2016-06-21' , 'YYYY-MM-DD')
and T158659.EFFECTIVE_START_DATE <= TO_DATE('2016-06-21 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and T100027.PER_NAME_MONTH
between '2015 / 05' and '2016 / 04' ) )
select SAWITH0.c2 as c1,
SAWITH0.c3 as c2,
SAWITH0.c4 as c3,
SAWITH0.c5 as c4,
nvl(SAWITH0.c1 , 0) as c5,
SAWITH0.c6 as c7,
SAWITH1.c1 as c8
please give me your inputs on this query..where can i get exact solution in above query.
your inputs/suggestions would be appreciated.
thanks in advance
regards,
sudheer
0 -
The query is one part of the solution. Have you looked at the RPD? Are there any business model fitlers enforced? Have you looked at the connection pool? How is the conneciton established? Have you compared the TWO queries (Admin and user) against each other in order to see the difference?
0 -
Christian already told you what can possibly cause this to happen. Putting your query here won't really help us because we don't know your data, therefore we can't really help you.
What you have to do is compare both queries and see what extra filters (WHERE clauses) exist in the query fired when running the report as that user and what filters don't exist when running the query as admin. Basically, get the physically query of the report when running it as that specific user and get the query of the report when running it as admin. Compare them, you'll find difference and you'll be able to find out what is causing this behaviour. Once you identify the cause, you'll need to find out what is making that happen (filters in the report, data-level filters in the RPD, etc).
You need to take it step by step and start putting the pieces together.
Edit: Christian was faster
0 -
What Christian and Pedro F have asked you to check is whether or not the SQL generated from your "XML" is the same for your Admin user and when user runs the same "XML".
If it is the same, have you run the SQL directly on database as Admin and user? Have you checked if the connection pool runs as a particular DB user?
Is there row level security on the database? Is there some user level security defined within your Rpd?
As you can see, there is a lot for you to check in order to determine what exactly is causing this behaviour.
Sent from my iPad
0 -
Joel said it already, but just to make things clear: Instead of simply repeating your problem why not do the things we tell you in order to find the issue? It's called root cause analysis.
If you just want someone to do your work, then I'll do it and send you my invoice. No problem with that either.
0 -
That was a joke. This here is a forum where we help people, not make business.
Also: Why would you want to spend (waste) money for a solution which has already been explained to you multiple times above and where you just need to invest a couple of minutes of work and thinking to find the solution?
0 -
Once more: Run the EXACT SAME analysis once as the administrator and once as the user - important: EXACT SAME. Get the ACTUAL SQL emitted by those two requests and COMPARE THE TWO SIDE BY SIDE. This will show you the DIFFERENCE in the actual query.
Do that and then come back and be PRECISE about your findings.
0
