Skip to Content
0

Calculation View - Filter on a group

Oct 04, 2017 at 10:08 AM

97

avatar image

Hi Friends,

I am stuck up with an interesting requirement in creating a calculation view. Attached is the screenshot which shows the data from a table.

As we can see there are two columns AUGBL and BLART which are important here. Scenario is if the line items of AUGBL contains "DR" as the entry in field BLART then only that AUGBL should go ahead in the calculation view. If the AUGBL does not contain "DR" as the entry then it should be filtered out.

For Ex: 1522000036 does not contain any "DR" as the entry in BLART column, hence both items of 1522000036 should be filtered out. whereas, AUGBL-1770000008 contains one entry of DR in BLART columns, so all the line items of AUGBL 1770000008 should go ahead in calculation view. So, even a single line item contains DR, the entire AUGBL will go ahead.

Basically, here we need to do some kind of grouping of AUGBL line items with BLART and apply filter.

Any thoughts?

Thanks,

Gaurav

cv.png (15.9 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Florian Pfeffer
Oct 04, 2017 at 11:51 AM
0

Why not just creating a second projection on the original data source with a filter where BLART is 'DR'. Then you can join that filtered data source against the unfiltered data source using AUGBL (and maybe some additional fields). With that only only that entries will be available in the further nodes where at least one entry exists where BLART is DR.

Regards,
Florian

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hi Florian,

Thanks for the reply. Yes, I have already done that as a workaround. However, I did not want to again introduce additional data set in 2nd projection node and doing join. Moreover,this adds two more additional steps. Is there any way to do it without introducing 2nd Projection and join node?

Don't we have any grouping concept in Calculation views?

Thanks,

Gaurav

0

If you would read the documentation, then you would see that there is not a 100% perfect out-of-the-box fitting solution for your requirement.

And sometimes you have to two two more additional steps (which are done by the way in 20 seconds).

0

Yes, I understand Florian. Thanks.

I was wondering if this requirement could be fulfilled through a table function, So I though of writing a table function. However, I am not able to put the logic in the select query. Can you please help me with this.

FUNCTION "BIUSER"."GK1.Func_Proc::t1acdoca" ( ) 
	RETURNS "BIUSER"."ACDCA"                --table type 
	LANGUAGE SQLSCRIPT SQL
	SECURITY INVOKER 
	DEFAULT SCHEMA BIUSER
	AS
BEGIN

RETURN

SELECT "RLDNR", "RBUKRS", "GJAHR", "BELNR", "DOCLN", "RYEAR", "AUGBL", "AUGGJ", "BLART", sum("WSL") AS "WSL", sum("HSL") AS "HSL" 
FROM "ACDOCA"
where "BLART" = 'DR'                                     --condition needs to be grouped
GROUP BY "RLDNR", "RBUKRS", "GJAHR", "BELNR", "DOCLN", "RYEAR", "AUGBL", "AUGGJ", "BLART";

END;

Thanks,

Gaurav

0

I think you need to improve your knowledge about modeling respectively SQL instead of let others do your job. As I do not wanna support a bad/not required table function solution I will stop here.

2

Florian,

I am not doing table function as it is. The approach which you mentioned earlier, I did that but I am facing some serious performance issues. Table is having 5 million records and having two projections with one join is affecting the performance. The report is taking 15 mins to give output. Hence, I am thinking to do so through table function.

And Yes, I am learning SQL everyday as I am not a SQL guy, my core competency is BI. Thanks.

Regards,

Gaurav

0
Lucia Subatin
Oct 05, 2017 at 03:24 PM
0

Hi,

Generally when you are retrieving clearing documents and their originating posts you need to access twice. If you wanted to do this by aggregations, you could have a counter set to 1 if one of the line items is DR and aggregate the counter and the clearing document only, then filter by anything that has a counter of 1 or higher. You will eventually need to access again to retrieve the payments., credit memoes, etc. In other words, a solution using aggregations would have less performance and you would also need a double access.

Your requirement is not as clear to me but if I am getting it right and you are facing performance issues you could start by fetching invoices from BKPF and then fetching all the lineitems from ACDOCA. I agree with Florian that the solution is not to script it, you are only retrieving invoices from that.

Best,

Lucia.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 08, 2017 at 10:52 AM
0

Hi Gaurav,

Can you check below link ,

https://blogs.sap.com/2014/06/24/the-usage-of-grouping-sets-rollup-cube-in-sap-hana/

Thanks,

sathya

Share
10 |10000 characters needed characters left characters exceeded