How to create new KPI for a Quotation in SAP Hana?

[former_member592380]

Dear All,

I would appreciate if anyone of you helps on this issue. I wanted to create a new KPI pervasive analysis in SAP Hana for an open quotation based on the query belowI already created, but I couldn't go further.

++++++

SELECT T0."DocNum" AS "Belegnummer", T0."DocDate" AS "Buchungsdatum", T0."DocDueDate" AS "Fälligkeitsdatum", T0."DocStatus", T0."CardCode" FROM OQUT T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID" WHERE T0."DocStatus" = 'O' AND T1."USERID" = [%USER] AND T0."DocDate" >= [%0] AND T0."DocDate" <= [%1] ORDER BY T0."DocDueDate", T0."DocNum"

++++

if anyone can help based on the above query or any other idea, would be really good.

Thanks in advance

Biniam

[msundararaja_perumal]

I believe is the issue with the parameter syntax, please try query without the parameters to verify if you are able to get the query result in the Dashboard Desginer.

Thanks.

[former_member592380]

Dear M.Sundararaja Perumal,

I thank you for your prompt reply and suggestions. Yes, Without the parameters it works but there is no graphical presentation option. Only quantity indication is displaed. The reason why I asked was to have the report based on the parameters. Specially with the $[USER] option. Is there any way that I can do in this matter? Is it possible to get a KPI analytics with the inclusion of the parameters?

Thank you for your support in advance

Best Regards

Biniam

[Abdul]

You can have parameter field in your dashboard and use graph with X axis as parameter field and Y axis as data field

[former_member592380]

Dear Abdul Mannan,

I thank you for the info. As you can see in the above Query, I put the [%USER] just to check if it works. The query runs but in KPI, I couldn't be able to see the user option. But originally I wanted to know if this ( JOIN OUSR T1 ON T0."UserSign" = T1."USERID" WHERE T1."USERID" = $[USER] part of the query is correct and can be applied in the KPI analytics. That was the main problem.

Therefore, can anyone suggest what is wrong with the following query?

SELECT T0."DocNum" AS "Belegnummer", T0."DocDate" AS "Buchungsdatum", T0."DocDueDate" AS "Fälligkeitsdatum", T0."DocStatus", T0."CardCode" FROM OQUT T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID" WHERE T0."DocStatus" = 'O' AND T1."USERID" = $[USER] AND T0."DocDate" >= [%0] AND T0."DocDate" <= [%1] ORDER BY T0."DocDueDate", T0."DocNum"

I wanted to have the KPI Analytic report displayed as per current user when they log on in SAP Hana.

Hope you understood my question.

Thank you once again

Best regards

Biniam

[Abdul]

In SAP B1 HANA current user code shows from below query so use this as a sub query like

AND T1."USER_CODE" = (SELECT CURRENT_USER FROM DUMMY)

 SELECT CURRENT_USER  FROM DUMMY;

[former_member592380]

Dear Abdul Mannan,

I thank you again for the info. I tried with what you suggested as below:

SELECT T0."DocNum", T0."DocDate", T0."CardCode", T0."DocTotal", T0."DocStatus", T0."UserSign", T1."USER_CODE", T1."U_NAME" FROM OQUT,"TESTKUNDE"."ORDR" T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID" WHERE T0."DocStatus" = 'O'; SELECT CURRENT_USER FROM DUMMY;

But I can not call up the query when I tried to create a new KPI. It says that the source is not correct. Do you have any idea?

Thanks in advance

Best regards

Biniam

[Abdul]

HI,

If your query is showing results in query generator and not working in KPI then i believe dynamic parameters are not available yet for dashboards.

How ever you can develop a dashboard for all users with figures on x axis and users on Y axis so user can know info about his relevant figure.

[former_member592380]

Dear Abdul Mannan,

I have tried as per your suggestions. I used the following query also:

SELECT T0."DocNum", T0."DocDate", T0."CardCode", T0."DocTotal", T0."DocStatus", T0."UserSign", T1."USER_CODE", T1."U_NAME" FROM OQUT,"TESTKUNDE"."ORDR" T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID" WHERE T0."DocStatus" = 'O'; SELECT T0.U_NAME FROM OUSR T0 WHERE T0.INTERNAL_K = $[USER];

This doesn't work if we try to display the query from Query manager, but it displays properly from Tools --> Query--> User--> Query name.

However i was unable to create a new KPI based on this query

Any ideas?

thanks as always

Best regards

Biniam