Oracle Communities

Oracle Analytics and AI

Child Item

Presentation variable with multiple values - Near <)>: Syntax error

I have started a discussion about passing multiple values in the presentation variable here: https://community.oracle.com/thread/4135887

I have almost found working solution, but I'm facing error which was described in the forum already few times.

Description of current situation:

Dashboard is created that way, that we have main page with dashboard prompt, and other tabs which has reports filtered based on prompt.Mandatory fields are Month and OpCo.Month is default to previous month, and OpCo need to be selected by user.So user, is selecting filter values, pressing Apply and click Tab which he wants.All reports work fine.On one of the pages there are Performance Tiles, to which I'm trying to add Action links.In the first run when you will choose report from Action link it returns syntax error:Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65PLocation: saw.views.evc.activate, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads Odbc driver returned an error (SQLExecDirectW).State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)State: HY000. Code: 27002. [nQSError: 27002] Near <)>: Syntax error (HY000)State: HY000. Code: 26012. [nQSError: 26012] . (HY000)doesn't matter what OpCo you will select.Filter (set as SQL) used in target report, which is causing problem:"CC - Business Unit"."Business Unit - OpCo" IN (@{PV_SELECTEDOPCO}['@']{"CC - Business Unit"."Business Unit - OpCo"})What we have noticed, that if you will get back to page with prompt, doesn't matter if you will change filter values or not, but will press again Apply button, after going to page with Performance Tiles and opening Action link, report will be executed without any error and filters will be passed correctly.

Any suggestions welcome

Find more posts tagged with

Accepted answers

All comments

Robert Angel

What format is your month in, is it just a text field with no date conversion logic imposed on it?

Also, if you examine the physical sql that generates the error what do you see?

Finally, are you certain that there are no clashes with your prompt name, I once spent ages on a similar error to find out that there was something else setting the same name to an inappropriate value for my usage. Hence errored straight about, but on refresh worked. Try renaming your prompt to something very short and very weird to rule this out (Bjork?)

aPsikus

Date.Month is in format: YYYY / MM

There is nothing to examine, as in Administration -> Sessions, there is no this report if error persists.

Not sure what do you mean in last point, as all reports on the dashboard are working fine with Prompt selection.

Only problem is with passing OpCo as variable to report from action link.

If I will remove this filter from the report, it opens without any error, but contains too wide data (all OpCo), which means that from Performance Tile to target report OpCo is not passed (even if it is set "is prompted").

Robert Angel

And your YYYY / MM is treat entirely as text, it is not explicitly or implicitly converted to a date to filter a date / datetime column?

The fact that you have no error when you remove it ties the error down to this one factor, so that is progress of a kind...

Can you add a filter section to your presentation so you can see exactly what is being passed in?

aPsikus

I guess Date.Month (YYYY / MM) is a text.

To have a date for it we are using to_datetime hidden function.

Filter section is not displayed when there is error in the target report.

Unless I should add it to the report from which we are opening Action Link. But I guess this one will not tell too much.

Robert Angel

Hi,

I have experienced this kind of pain with datetime before.

My advice will sound ridiculous, but it cured my issue so bare with me.

You need to explicitly convert the result to a datetime, ensuring that you concatenate hard coded elements for all of the datetime that are not present in the original.

If this is further converted to a date then again make sure the conversion is done explicitly not implicitly.

I have done this with cast, rather than to_datetime, but yours is good too!

if you can bypass the problem and filter text = text rather than via a datetime / date field in your time dimension then that is better still - you will completely sidestep the problem!!

I try in the data warehouse piece to make the TIME hierarchy rich enough such that when building reports in OBIEE you never have to resort to conversions - this improves performance speed of reports and decreases report building time and complexity.

aPsikus

You need to explicitly convert the result to a datetime, ensuring that you concatenate hard coded elements for all of the datetime that are not present in the original.If this is further converted to a date then again make sure the conversion is done explicitly not implicitly.I have done this with cast, rather than to_datetime, but yours is good too!

What exactly do you mean?

I have tried conversion using CAST, and it didn't work. Date.Date was not recognized in analysis.

Robert Angel

HI,

say you have 'YY MMM' as your value, what I am suggesting is that you concatenate the other elements that make up a valid date time format '01' (for DD') and 00:00:00 (for time) and then use explicit conversion to make it a wholly valid datetime. The problem usually comes where midway through the code there are two types (date / datetime or datetime / text) compared / added etc and an implicit conversion happens, never really on the implicits, always explicitly convert datatypes ensuring that you have a wholly valid conversion (in all cases) at all points through the formula.

aPsikus

Date from CAST variable and concatenating was not recognized when used in Filter section.

BTW, problem is not in the date.

Update:

I have created additional analysis, where I have removed all date filters using to_datetime, changed KPI from Standard to YTD, and still same error.

Which confirms that something is wrong with this filter repated to passing OpCo from one analysis to another.

Robert Angel

Can you kindly post the full error and the full current syntax of the filter.

aPsikus

Finally I have solved the problem.

Causing a problem was protected filter for OpCo is not null which was passed from Performance Tile.

When I have removed protection, everything is working fine.

Looks like in such case protected filter is passed as only one to target report, and is overwriting is prompted and variable in target report.