Skip to Content
0
P J
Feb 07, 2019 at 03:43 PM

HANA calcualtion view Date input parameter does not work with Bex query

1639 Views

Hi All,

This is long question but please go through it and help with your inputs. I have tried few date format changes, std function date, to_date usage but still not getting require data.

In HANA studio I have calculation view(CV) and it has Date as input parameter of Data type "Date" in CV. When I do data preview on CV, it ask to enter date and format it accept is YYYY-MM-DD (2018-06-30).

Composite Provider (CP) is created on this CV and then in BW 7.4 Bex Query is created on CP. In Bex query, on CV input parameter I have created user input variable.

When Bex query runs it populate Calendar for user (this is user requirement to populate calendar), to select the date, once user selected the date, it display as: (DD.MM.YYYY (30.06.2018) then we execute the report with entered date.

I have created 4 calculated columns in CV, all 4 display correct data on data preview in HANA studio but when same are pulled into BW query, only 3 display correct data (they do not have any calculation related to date) but 1 column do not display any data (they do have calculation related to input date/parameter).

It looks like user input value gets correctly pass to HANA CV and return the data because 3 columns display correct data but for 1 calculated column(Get LAST YEAR YTD) seems issue with date formatting or the way Date format gets passed into HANA and the format HANA read it from BW query is creating problem.

CV has existing date column ACT_DATE_INT and is of data type VARCHAR:8 (20180630) used for data comparison. I am only explaining data that has issue.

Col1 (VARCHAR,4): Take Year from input parameter and do -1 using INT: leftstr('$$ACT_DATE$$',4)

Col2 (VARCHAR 8): Calculate 'Last year date' from input date:

"Col1"+midstr('$$ACT_DATE$$',6,2) + rightstr('$$ACT_DATE$$',2)

Col3 (Decimal 15,2): Get Last Year YTD value by comparing last year date with ACT_DAT_INT column.

IF("ACT_DATE_INT"="LAST_YEAR_DATE","ACTUALS_YTD",0)

HANA data preview and BW query output for input date: 2018-06-30

HANA display correct data but Bex query does not.

Attachments

khszz.jpeg (3.5 kB)
ldunb.png (4.2 kB)
kksfg.jpeg (7.9 kB)
wglkm.jpeg (9.1 kB)
p98sy.jpeg (2.9 kB)