Dynamic characteristic based on keyfigure-aggregate (KF-classification)

[marco_simon2]

Hi experts,

I'm facing the following request:

A report is needed, which shows (e.g.) invoices.

The invoices shall be groupable based on the quantity stored in the position-items (refering to that invoice).

The selection shall allow position-characteristics to be filtered.

An example:

The following records exist on the database:

Invoice     PosNr     Material     QTY

900               10          4711          30

900               20          4712          50

901               10          4711          60

901               20          4712          20

902               10          4711          10

The report-drill-down should look like this (without setting any filters):

QtyGroup          #Invoices    

00 - 20               1                                   (reading: There's 1 invoice with a total-qty between 0 and 20)                   

21 - 40               0              

41 - 60               0

61 - 100             2                                   (reading: There're 2 invices with a total-qty between 61 and 100

The report-drill-down should look like this if the user selects matnr 4711 only:

QtyGroup          #Invoices    

00 - 20               1                                  (reading: There's 1 invocice which has a total-qty bt. 0 and 20, regarding the filtered positions)          

21 - 40               1              

41 - 60               1

61 - 100             0                                  

As you can see the groups are dependant from the user's filter, hence they cannot be
precalulated on the database at the moment of data-loading.

Furthermore virtial characteristics imho cannot be used, since they work only on a - per-record - basis,

but cannot loop over the whole result-information to build the needed aggregates for the group-assignment.

So, does anybody have an idea how to solve such "classification"/"grouping"-problems?

[former_member182470 · Accepted solution]

Can you think of creating Qty Group with Cell structure in rows pane in query?

You need to basically create Invoices count.

Keep material in filter pane with user entry varaiable.

[marco_simon2 · Accepted solution]

A cell-structure is an option and the the invoice-count won't be a problem (as well as the filter).

What's missing (maybe only in my mind) is the tranformation from the invoice-level qty-_keyfigure_ to a _characteristic_ which can be used for grouping.

[former_member182470 · Accepted solution]

HI Marco,

We use Cell structure when we want to show something on the fly eg: Brackets and we create new formulas or new selections for the cells. You can just start developing as per my previous reply. You need not to worry about transformation etc..

Regards,

Suman

[marco_simon2 · Accepted solution]

Hi Suman,

let's become a little bit more concrete:

I'd like to have the above groups in my drill-down.

Hence I create some selections labled:

1) 00 - 20             

2) 21 - 40

3) 41 - 60

4) 61 - 100

But which characteristics can I use in this selections to restrict them to only the data that shall be displayed per group? In the above example there are exactly 3 characteristics:

- Invoice

- PosNr

- Material

None of them can be used for assigning the record to the right group.

The only information that might help me (to group) is the _aggregated_ quantity keyfigure.

But keyfigures cannot be used in the selections, can they?

Where did I miss your idea?

[former_member182470 · Accepted solution]

When you talk about Selections, do you mean "New Selection" in KF pane?

You want to show invoice count, right? Then you can use invoice field.

Can you show me your sample layout? Drill down can be a char but not KFs.

[marco_simon2 · Accepted solution]

Suman Chakravarthy K wrote:
When you talk about Selections, do you mean "New Selection" in KF pane?

In this context: Yes, correct.

You want to show invoice count, right? Then you can use invoice field.

As mentioned: Yes, the invoice-count won't be the problem.

Can you show me your sample layout? Drill down can be a char but not KFs. 

Sorry for the inaccurate wording. My layout is the the one described in my inital post:

QtyGroup                  #Invoices    

Group1(00 - 20)               1

Group2(21 - 40)               0              

Group3(41 - 60)               0

Group4(61 - 100)             2  

Drill down can be a char but not KFs. 

And that is the point: I do not have any characteristic which I can use for the definition of the Groups. What I could do is the following: At the moment of data-loading I could use a tranformation to fill a dummy-characteristic "ZQTY" with the total qty of an invoice. That would mean, that I could Define Selection "Group 1(00-20)" based on ZQTY - selecting all records where ZQTY is between # and 20.

But this approach works only if the user does not filter based on position-characteristics (eg. Material=4711). Because that would change the group-assignment (see my inital post), but it would not be reflected in the report since ZQTY will not change anymore.

Only way out I can see - and which is not a beautiful one: Using a virtual provider, which implements the needed behaviour.

[former_member182470 · Accepted solution]

OK. Give a try with your approach and let me know.

[Former Member · Accepted solution]

Hi Marco,

Can you think on this approach . I dont have a system right now otherwise i would have tried it with a sample scenario

1) Insert three dummy characteristics in your cube . And in the transformation add constant values for them .

ZCHAR1 : W

ZCHAR2 :X

ZCHAR 3 : Y

ZCHAR4 : Z

2) And go by this bucket scenario to create your 3 buckets of keyfigues . Dont follow the complete document.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c09f49c2-7448-2c10-ac92-d75f263a0...

3) And restrict those individual bucket key figures with W, X, Y, Z values of the chars ZCHAR1,ZCHAR2,ZCHAR3,ZCHAR4. Hide these 4 bucket keyfigures.

4) And make another key figure where you add these 4 buckets key figures to display count in column.

Now try taking a drill down on ZCHAR1 ,ZCHAR2, ZCHAR3,ZCHAR4.

If this approach gets successful , then you can add text for infoobjects as :

W: 00 - 20            

X: 21 - 40

Y: 41 - 60

Z: 61 - 100

This is just a thought process. I am not even sure whether this will work. I don't have system to give you a concrete reply.

Hope the above reply was helpful for you.

Regards,

Ashutosh

[anshu_lilhori · Accepted solution]

Hi Marco,

I think by simply making bucket of different ranges should serve your purpose.

Drag and drop quantity in rows--Make a new formula--Give the ranges for quantity as desired--Hit the aggregation tab--exception aggregation as--Count of all values that are not equal to zero--Reference characteristic-invoice no.

Refer the document shared by Ashutosh for the same.

The output shall look like this:

Hope this helps.

Regards,

AL

[marco_simon2 · Accepted solution]

Hi Ashutosh,

I followed your hints and especially checked and appreciated the mentioned link.

Unfortunately I did not really understand the purpose of the characteristics you mentioned.

Could you please explain that a little bit closer?

[marco_simon2 · Accepted solution]

Hi Anshu,

the bucked-approach seems to be a good direction, but it

brings me to a new bigger problem.

Let me extend the initially described layout (based on the same databasis as initially mentioned)
a little bit to explain my pain:

QtyGroup                  #Invoices     Quantity

Group1(00 - 20)               1               10

Group2(21 - 40)               0               0              

Group3(41 - 60)               0               0

Group4(61 - 100)             2               160

The problem is: I will need keyfigures in rows AND columns.

In rows for the bucket-definiton, and in rows for displaying different keyfigures concluding to each bucket (~There's 1 invoice belonging to group1, and this invoice contains a qty of 10 pcs).

Unfortunately the BEx-Query-Desinger does not allow Keyfigures in both dimensions.

Is there any way out of this problem?

Best regards,

  Marco

[anshu_lilhori · Accepted solution]

Hi Marco,

As per your initial layout where you only need to know the total invoices based on grouping i suggested the way out.

But now as you need another Kf to be displayed so as you are well aware that its a limitation of BEx you cannot put KF both in Rows and column.

So according to the layout Qty should be of type char with data type number.

I do not know how flexible you are with this modeling changes.

Regards,

AL

[former_member182470 · Accepted solution]

Hi Marco,

you cannot have two KF structures in BEx. Think of changing Qty as Char.

Regards,

Suman

[marco_simon2 · Accepted solution]

Hi Anshu,

first of all: I am aware that I changed the conditions since the inital post.
This was because I had to realize that I simplified the problem too much.

Which qty do you suggest to become a char?
I'd prefer to transfer a qty-kf to a char (via data-staging or virtual characteristics),

but as initially described:

- The quantity [is] stored in the position-items.

- The selection shall allow position-characteristics to be filtered.

That means that the value for the qty-char is dependant on the users selection,

which I do not not during datastaging. And virtual-chars only allow per-record-based
char-value definitions since I do not have access to the rest of the result record.

In the end I am not even sure if a virtual provider might help me out,

becuase (if I understood it correctly) the vp-function-module is only triggered if new data
needs to be collected from the database. But the fm is not involved if the user only sets
a filter for a char which was already part of the drilldown (and was already passed to OLAP in detail).

End of track? Request not realizeable?

[marco_simon2 · Accepted solution]

Hi Suman,

please check my previous reply to Anshu's post,

it fits to your reply as well.

[Former Member · Accepted solution]

Hi Marco,

Those four characteristics are just additional characteristics in your cube with some constant value . They will help you to take a drill down instead of having a fixed layout.

In your buckets as per the link i have given you have to restrict those KFs with constant values of these characteristics.

Example : restrict bucket 1 with constant value for zchar1.

                  restrict bucket 2 with constant value for zchar2.

                  restrict bucket 3 with constant value for zchar3.

                   restrict bucket 4 with constant value for zchar4.

And add these 4 bucket keyfigures in 1 key figure #Invoice(for example) .

now if you take a drill down on zchar1 you will see output like this :

QtyGroup          #Invoices   

00 - 20               1                                                     

Note : in the master data of zchar1 maintain its text as : 00 - 20 and similarly for zchar2,zchar3,zchar4.

I am so sorry i am not getting time to replicate this scenario otherwise i would have given you a concrete reply.

Regards,

Ashutosh Singh

[marco_simon2 · Accepted solution]

Hi Ashutosh,

that seems to be the missing puzzle-part.

It seems as if my target would be reachable if I use

- Several characateristic-selections based on a constant characteristic (there seems to be no need for 3 chars, one will be enough)

- Formulars using boolean logic

- Excecption-aggregation

- Dummy formulars

- Cell-structure with several repositionings....

- Formular-variables (for making ranges dynamic)

- Text-variables (for displaying conclusive describtions)

Techically all the Keyfigures exist all in the same row (or column),

and the cell-structure is used to reposition them in a cross-table.

A lot of tricky puzzleing - but it seems to be effective!

Thanks all 3 of you for your input!