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

[former_member211603]

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.

[former_member211603 · Accepted solution]

Hi All,

I am able to fix above issue. There was problem with 'Col2' calculation:

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

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

As you can see in my screenshot, Last Year Date was showing correct as: 20180630 in HANA CV data preview. But when I pulled it to BW, it was displaying as 20186330. It means col2 calculation was not happening correctly. When I was trying to display Input date($$ACT_DATE$$), format I was getting was: 2018-06-30 and hence in mid str I had used 6,2 but looking at Date pulled into BW, I have changed it to 5,2 and it has correctly passed 20180630 date and hence YTD was calculated correctly by comparing with actual date.

But it is truly with the way HANA store data format and BW store and pass to HANA.