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
Substring function is not working in advance sql filter
I have a prompt called fiscal_period which will have values like 2016R 1, 2016R 2,.......
I need to apply a filter on the report on fiscal_year = 2016. So i'm trying to get the first four letter from fiscal_period and pass it to fiscal_year.
Please find the screenshot below, please help me with any alternate solutions.
First screenshot is the advanced sql filter in the criteria tab, second is the result view in results.
Thanks, Helan
Answers
-
Pass the fiscal_period to a report that you don't put on the dashboard, accept the prompted value and put Year as the only criteria column. Then in your report filter Year by the results of another analysis ... skip the messy functions all together.
0 -
I first thought it was because of a wrong syntax, OBIEE substring is SUBSTRING(value FROM beginning FRO length) but it looks like the syntax with with (value, from, length) works as well ...
Is your Fiscal year a string? Do you have no data or an error?
How does the physical generated query looks like? (does it makes sense? is the condition correct? did you try to change the = for a LIKE ?)
0 -
Yes both the syntax works.
Its not a string but that can be handled using cast if required. There is no error, its just no data
SQL looks sense [and D6.c3 = substr('2016R 3' , 1 , 4)].
Have tried LIKE too, same results - no data
0 -
Will give a try. Thanks.
0 -
If you run the physical SQL (which looks good so it's already a good point), you also don't have any data (I hope), but do you see what makes the query to not return any data?
Maybe you just don't have data for 2016? (or not for that combination of filters and columns?)
0 -
Sorry for jumping in middle
Did you cast to int after substring
0 -
"Time"."T05 Per Name Year" = SUBSTRING('@{pvPeriod}{2012 R1}' FROM 1 FOR 4)
^--- Works perfectly fine as proven here:
Prodney / Admin123 to log on.
0 -
It has data, the same formula works when i write it in edit formula section
0 -
Working now!
Thank you
0
