Hello Joel, very sorry for the time in getting to you on this - my example was very simple the complexities on the real thing not so much. Your "CASE WHEN RANK(Amount BY Supplier, Invoice Num) = 1 THEN Code END " - proved to be the logic I needed - I am still struggling to understand how when displaying at header level it…

RANK code by invoice_num, supplier name will get me the largest code, I want the line where the code has the largest total value in the Amount Column, and I want to see the total amount - unfiltered. Does that make sense?

Hello Joel, I am looking at rank syntax and just not understanding how it helps me get the CODE for an Invoice Num / Supplier that has the Max(amount) by Invoice Num / Supplier To be clear I want the TOTAL (Amount) but I only want the CODE for the line where Max(Amount by Invoice_Num, Supplier) = Amount Is there a way? If…

Think yours is the best answer I will get - but will log an SR as it looks like the date parameters do not work as they should in BIP through Fusion FInancials.

Your suggestion worked darling - but like you said it does make it take longer. Thank you Don't know why when elb666's suggestion works for him and mine is identical it won't work for me. Teresa X

Thanks for your reply sweetie, really very very kind of you. Completely puzzled as following your set up perfectly but it does not work this way for me. I can use sysdate to compare - good I can use text to match to text - good But the minute I try to convert text to a date - even though it matches the DD-Mon-YYYY using…

The manual tells me this { Working with Date Parameters Oracle BI Publisher always binds date column or date parameter as a timestamp object. To avoid timestamp conversion, define the parameter as a string and pass the value with formatting as 'DD-MON-YYYY' to match the RDBMS date format. } This being true then if my text…

Still hoping there is an answer on text to date in BIPS?

Anyone at all with even half a clue on this or at least confirmation that it cannot be done - please?

And what Joel is advising is best practise in all circumstances - you should always remove what you do not need for display or calculation purposes.

Anyone please - anything? My latest; - and nvl(prd.calendar_period_close_date,sysdate+1) >= to_date('01-'||nvl(:P_PERIOD_FROM,'Jan-1951'), 'DD-Mon-YYYY') I have reviewed the links that talk about the canonical date format, but as this is entirely within SQL and I am not starting with a date parameter and I am using to_date…

I use the below and it does not recognise the variable as a variable and nvl(prd.calendar_period_close_date,sysdate+1) >= cast('01-'||'&MyPeriodSelector' as date) I remove the speech marks around the variable name and it sees the month from the full variable as a name and errors. and…

Anyone provide any help at all on this please? In brief; Text financial period passed via parameter with format "Apr-2018" I need to change that value into a date in a data model. Can anyone tell me what syntax I need to achieve this; - to_date(&MyPeriodSelector,'Mon-YYYY')

Yes, cornerstone I would say! And on possible, duplicate your existing measure, rename it, drag it onto the month level of the time hierarchy, easy!

Or he could create Month based measures "Current Revenue Month" which he pulls onto the month level of his time hierarchy to make it not change at that level of detail.

Yes - I did have second thoughts and actually came back to append that you might need to apply a different security model for row level security or similar - though in those cases I have seen the requirement be so extreme that the only option is to duplicate the business model / presentation layer.

Connection pools are more data source than logical areas. Other than writeback I would not see the need to duplicate a connection pool once it is set up.

Sounds like you need master / detail -> https://gerardnico.com/dat/obiee/obips/master_detail

So compare 1st February with 1st January ... Compare 28th February - when not a leap year - with 28 January? or 31 January? So compare May 31 with April 30? or ????? As I suggested before to break it down for you; - Use substring to scrape out the day number and the month text and then use these in case logic to derive the…

Completely agree. I ask 'why' and 'what are you really trying to achieve here' more than is healthy for my career. Project managers too are often guilty of the same mind set of not challenging the poor requirement. I must change my display name back to the original, I keep getting called Larry and the apostrophe does not…