Dynamic date ?

building semantic layer
1

Hi everyone, I am at my wits end with this problem have spent countless hours trying to find solutions on this and many other pages:
I am relatively new to the Symantec layer of BOBJ, but have spent many years building Webis based on BW Bex Queries.

I have created a connection to our SAP system, created a simple data foundation based on one of the info sets, And finally created a rudimentary Business layer.
My problem is dates! ( sorry I know there are heaps of posts even a “sticky” but I cant work this out)

If I create a filter on VTTK-ERDAT in my webi and set it to either Prompt or Fixed value, its everything works great!

But I want a dynamic filter so that I can schedule the report twice a day. All I want is “TODAY”. I have attached a screen shot if where I am at. Having tried what feels like every piece of code ever written, I am at a loss as to what I am, undoubtedly, doing wrong.
For the record
ERP is on DB2, BOBJ is running on MS SQL Server.

Any help appreciated.

John
TodayFilter.png

IknoNothing :new_zealand: (BOB member since 2017-11-30)

2

Not entirely sure if the Universe will be talking to the DB2 backend directly with SQL (you`d think it would!!).

The DB2 function for getting the current date is ‘current date’. You are using GetDate(), that`s T-SQL.

ABILtd :uk: (BOB member since 2006-02-08)

3

Thanks. I did wonder about which language to use.
I have tried ‘Current Date’, but it didn’t seem to work.
Could you suggest syntax? are there quotes? single quotes?

I understand that this might not parse, so I should create the variable as is and the the publish the universe, and use it it in an actual webi correct?
There is no way to test the variable in the designer?

Thanks

John

IknoNothing :new_zealand: (BOB member since 2017-11-30)

4

Single or multi-source connection? If the latter, then try the code in my post here: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

Debbie

Debbie :uk: (BOB member since 2005-03-01)

5

In DB2 this code worked for me

SCHEMA.TABLE.COLUMN >= CURRENT DATE

Derf :canada: (BOB member since 2011-05-16)

6

Thanks for the replies guys!
Debbie, Its a single source. Derf, thanks I tried your suggestion, no joy.
FYI: when i “show values” the VTTK-ERDAT column is in in this format
Aug 15, 2015

If i create a simple query like this:

SELECT
  Table__1."LIKP-WERKS",
  Table__1."LIKP-ANZPK",
  Table__1."LIKP-VOLUM"
FROM
  "VTTK"  Table__1
WHERE
  (
   Table__1."VTTK-TPLST"  =  '57DA'
   AND
   Table__1."VTTK-SHTYP"  =  'Z001'
   AND
   (  Table__1."VTTK-ERDAT" >= CURRENT DATE  )
  )

I get the following error:

The “expecting RIGHT_PAREN” is a recurring error I see with various attempts at try to get this to work.
Any more suggestions?
Thanks

John

IknoNothing :new_zealand: (BOB member since 2017-11-30)

7

As Derfs native DB2 code doesn`t work, it alludes to a middleware issue.

Probably worth finding out;

DB version
DB2 client version
Connectivity Method (ODBC, JDBC etc).

It`s likely to be an issue there…

ABILtd :uk: (BOB member since 2006-02-08)

8

Try using: curdate()

That’s what we use to get the current date using DB2 ODBC connections.

dtolley (BOB member since 2006-07-14)

9

HI, Thanks for your reply & help so far.
According to the System / status menu:
Database System : DB6
Release 09.05.0004

DB2 client version : Unsure - give me some guidance and i will check.

Connection Method:
Under General:
Network Layer: JCO
DataBase: SAP R/3 Release 4

Ah… Could that be my problem? The standard install of BOBJ I have a bunch of DB2 Drivers. 2 of them are DB2 v9 JDBC Driver, or BD2 Client?

Thanks

Thanks for your reply, No luck with that either I am afraid

Thanks for your replies

John

IknoNothing :new_zealand: (BOB member since 2017-11-30)

10

Hi John,

You may find the following article enlightening / useful:
https://blogs.sap.com/2007/02/08/ibm-db2-who-is-who-in-the-sap-environment/

Regards,
Jim

jwaterbury :us: (BOB member since 2007-09-21)