Need Clarification for displaying count of a keyfigure in required format in BEx query designer

[Former Member]

Hi,

     For the following format for the key figure count of site, based on document number(dimension) I should get the following:

HSSP ID     Document Number     Count     Count of site

a0001               101                         1               1

                                                       1               1

                                                       1               1

a0002               102                         1                1

                                                       1               1

a0003               103                         1                1

a0004                104                         0               0

----------------------------------------------------------------------------

overallresult                                     3               6

But I'm getting the report in the following format:

HSSP ID     Document Number     Count     Count of site

a0001               101                         1               1

                                                       1               1

                                                       1               1

a0002               102                         1                1

                                                       1               1

a0003               103                         1                1

a0004                104                         0               0

----------------------------------------------------------------------------

overallresult                                     6                6

Here, HSSP ID, Doc no. are dimensions(rows) and count, count of site are key figures.  Count is the calculated key figure having count of site, exception aggregation as summation and reference object as document number.

Can you please suggest to get the report in the given format?

with Regards,

Antony Jerald.

[rhen · Accepted solution]

Set Count as a keyfigure with exception aggregation "Counter for all values that are not null, zero or error", with reference object doc number.

kr,

Rutger

[Former Member · Accepted solution]

Hi,

      Thanks for your reply.  I'm getting desired result.  I'm again stuck with following step.  Can you please help me out?

HSSP ID     Document Number     Count     Count of site     Value

a0001               101                         1               1               100

                                                      1               1               100    

                                                      1               1               100

a0002               102                         1               1               200

                                                      1               1               200

a0003               103                         1                1               50

a0004                104                        0               0               50

----------------------------------------------------------------------------------------------

overallresult                                     3               6               400

for a key figure value, I'll have to get it's total as 400.  Means for each document number, it should avoid duplicate values and consider one and the total.  But I'm getting the result as 800.  Can you please help out in this?

with Regards,

Antony Jerald.

[anshu_lilhori · Accepted solution]

Hi,

On what basis you are getting the duplicate values in keyfigure...You need to identify that characteristic and then you have to apply exception aggregation as----First value-----Reference characteristic---choose the one on which you are getting unique set of values.

Hope you understand that.

Regards,

AL

[former_member1308742]

HI Antony,

Create 1 formula FR1:

Use Exception Aggregation :

Refrence Char:  Site

Type:  Average

hide FR1.

create FR2 with use of FR1.

Exp Aggr:

Reference Char:  Document No

Type:  Sum

Display FR2.

Please try this.

This will satisfy your requirement.

Please update me if you face any issue.

Thanks

[former_member1308742]

Hi Antony,

What is granularity of your data?

For count create one formula FR1 : put exeption aggre. Average.

refrence Object : Document No.

now Use this FR1 into onother formula (FR2) and use Exeption Aggr :- Sum

Refrence Object : HSSP ID.

Hide FR1 and Use FR2 to shoe as final result value.

This can apply for value part also.

Please check and let me know if any issue comes.

Thanks

Sourabh

[Former Member]

Hi,

Please look into the following as required with granularity:

HSSP ID     Document Number     Site        Value

a0001                101                     A001       100

                                                   A002       100    

                                                   A003       100

a0002                102                     B001        200

                                                   B002        200

a0003                 103                    C001          50

                                                   C002          50

                                                   C007          50

a0004                 104                    D001          50

------------------------------------------------------------------------------------

overallresult                                                  400

But I'm getting the result as 800.  Can you please help out in this?

with Regards,

Antony Jerald.

[anshu_lilhori]

Hi,

Make a new formula----Put your value keyfigure inside it---Hit the aggregation tab---Exception Aggregation as First value---Reference characteristic as Site.

This will give you the desired result.

Hope that helps.

Regards,

AL

[anshu_lilhori]

Hi,

Refer this:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/009819ab-c96e-2910-bbb2-c85f7bdec...

Hope it helps.

Regards,

AL

[Former Member]

😞

This is not much useful...

[anshu_lilhori]

Refer my post which i posted lately.