Execution of report on BI Platform with date prompts fails

[Former Member]

Hi experts,

I built a report in Design Studio 1.3. The data source is an universe connecting to an Oracle DB. When I execute the report locally an on the BI Platform, both works fine.

Then I added a date filter (like, date between date1 and date2). The variables date1 and date2 are defined as optional prompts that are initialized in the startup script with the command: APPLICATION.setVariableValueExt(...).

With this filter the local execution still works and returns the expected results. However, when I run this report on the BI platform I don't get any data back. Unfortunately it's not even throwing an error message. It just doesn't return any data.

My assumption is that the resolution of the date filter format could be different when execution locally than on the BI platform??

Btw: when I replace the variables date1 and date2 by constant values, everything works fine on the BI Platform. It's just not working when I am using prompts...

Any idea what the solution could be? Or might it be a bug?

best regards,

Georg

[former_member204452]

Hello George,

  I think, This blog may help you.

http://scn.sap.com/community/businessobjects-design-studio/blog/2014/12/17/passing-date-range-to-a-w...

[TammyPowlas]

Georg - what version of the BI platform are you using?  Are you on BI4.1 and if so which SP?

My experience with these tools is it works best with the latest SP

[Former Member]

Hi Tammy,

we are running BI4.1 with SP4 and for Design Studio we are on Verson 1.3 (Build ID: 13.0.3.201405141058).

what do you think? could that version be buggy?

also, is there a way to fetch the sql statement which is generated by the server? My suspicion is still, that the WHERE clause for the prompt filter is not properly generated.

regards,

Georg

[TammyPowlas]

Georg - I am not saying it is buggy; it is just experience shows that being on the latest SP. 

I am not sure how to fetch the SQL statement.  I am going to need time to think about it; if your situation is urgent I recommend creating an SAP Support incident.

I am on different versions, however (1.4 and BI4.1 SP05)

[Former Member]

Hi Tammy,

thanks for responding so quickly.

As usual it is urgent since our customer wants to see results... therefore I will open a ticket as you suggested.

And if you could find out in the meantime how to fetch sql statements, that would also be a great help.

thanks a lot.

[Former Member]

I am still waiting for a response from SAP Support after submitting an incident at 9th of Feburary...

In the meantime I found the root cause of the problem.

Local execution:

Application.SetVariableValueExt("psStartDate","01.02.2015 00:00:00");

gets translated into following SQL clause:

TO_TIMESTAMP('2015-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')

BI platform execution:

The same prompt as above will throw an error. However..

Application.SetVariableValueExt("psStartDate","1425772800000");

gets translated into following SQL clause:

TO_TIMESTAMP('2015-03-08 00:00:00','YYYY-MM-DD HH24:MI:SS.FF')

"1425772800000" is the unix timestamp format, which on the BI platform gets translated into a readable oracle datetime value "2015-03-08 00:00:00".

So my question is: Since I have no means to convert an oracle date/time into a unix timestamp in Design Studio, is there a possibility on the Server to configure how the input variable shall be interpreted?

regards,

Georg