No results are retrieved for a calculated column that uses an input parameter

[leandrong]

SAP HANA Studio.

My problem is the following: No results are retrieved in Data Preview for calculated column that uses an input parameter.

Hi, I have this calculated column "CALMONTH"

Its formula refers to CC_CREATION_DATE, and this latter refers to the Input Parameters IP_BUDAT_TO

The problem is in Data Preview when I want to show only the column "CALMONTH".

No data is shown but exists as you can see in the filters.

If I add another column or value it works fin and data is shown.

Does anyone know how to fix it?

Thanks in advance

Leandro.

[leandrong]

Making some test I can assure that the problem is not in the Input Parameter. I tried with a calculated column (that does not refer to a Parameter) and got the same problem.

[DotEiserman]

Hi Leandro,

I tried your example on my play system and I would say this is purely because it needs the help of a physical record to process and calculate a result for, i.e. something to sit next to, that's why it works when you drag in an attribute or measure.

For calculated columns that has a value derived from a value on a row, its able to calculate the value and stand alone.

I haven't written that very eloquently so hopefully you understand what I mean.

The only time that I think you would want to use that value as standalone is to get the value of the parameter? Is that your aim?

Kind regards,

Dot

[leandrong]

Thanks for the response. I agree with you, what you said it was I thought first.

This VDM is a source for a Tableau report and CALMONTH is used as a filter. The report now is only showing the months before the actual one because they come from another VDM where CALMONTH is a field.

[leandrong]

Any suggestions? Thanks

[DotEiserman]

Hi Leandro, I didn't quite understand your data flow. You want to execute this report 1 which has this parameter and derived calmonth, and then feed this calmonth value from report 1 to report 2 as a filter? Kind regards, Dot

[leandrong]

There is only one report, but user want to filter by calmonth (in Tableau) and current month is not appearing because of this problem.

[DotEiserman]

Hopefully we're not having a lost in translation moment. 🙂 This is what I would typically do (ignoring toolset for the moment):

1. Analyse base dataset to find a date which can be converted into Calendar Month

2. Once I have this date, then I can create a calculated column which will calculate Calendar Month per record

3. This new column can then be used for pivoting, filtering etc.

4. If my base dataset doesn't have any date that can be used to derive calendar month for e.g. it is a snapshot, then I would populate a snapshot date when loading, and again use this date to derive the calendar month.

Does your base data have some date field you can use to derive Calendar Month in the calculated formula instead of the parameter?

Kind regards,

Dot

[leandrong]

Dot, hi, the problem is not the parameter, I tried with a calculated column (that does not refer to a Parameter) and got the same problem.

The date is calculated based on open documents to actual day. For example, 2 open documents from April are completed with 201909. That's why it is a calculated column.

[shanthi_bhaskar]

what is the formula for the CC_CREATION_DATE?

[leandrong]

'$$IP_BUDAT_TO$$'

[former_member531065]

Hi Leandro,
I think you should try with "SQL Engine" option instead of "Column Engine" option. There is a specific difference in which both these engine handles the "Null" part of the If/Case statement where SQL Engine works better than Column Engine.

Please test with the same and keep us posted.

Here is a useful note on this topic:

https://launchpad.support.sap.com/#/notes/1857202

Regards,

Vaibhav

[leandrong]

Thanks, but I have the same problem.