Structure - suppress zero rows

[Former Member]

Hi BW people,

I found very similar topics on this forum concerning suppressing zero rows in structures but not exaclty the same case nor an answer.

<u><b>Query</b></u>

I have a structure in the rows with several selections (S1, S2, S3) and beneath that structure a characteristic. To keep it simple: there is only one key figure in the columns.

<u><b>Aim</b></u>

I would like to have the <i>non-zero</i> key figure for all materials (M1, M2,...) for each selection in the structure.

So it should look like this:

S1   M1   50
S2   M2   100
S3   M3   90

Unfortunately I get this:

S1   M1   50
S1   M2
S1   M3
S2   M1
S2   M2   100
S2   M3
S3   M1  
S3   M2   20
S3   M3   70

So, even if the key figure for M2 and M3 (for selection S1) is zero, it is shown because these materials have a non-zero key figure elsewhere in the selection.

<u><b>Condition</b></u>

I tried about every possibility of 'conditions', but again the same problem: they are evaluated on the whole structure AND material, and not on every separate line of the report.

Anybody a solution? I would be grateful as I have tried quite a lot of work arounds.

Thanks in advance!

[Former Member]

Hello Bart,

By creating a condition (By clicking the Button next to CELLS or from View menu you can open a CONDITIONS..  Create a New Condition by clicking context menu then double click on it.

1.  In Define Condition Parameters (Click New) on bottom you will find 3 entries.  Keyfigures, Operator and Values.

2.  Select the 0 value Keyfigure

3.  In Operator select Not Equal To and

4.  Under Values give 0 and click Transfer button then OK.

Now it will hide 0`s in the report.

Thanks,

Anjan

[Loed]

The suggested solutions by ROB and GSM were correct..

Thank you!

Loed

[Former Member]

You should try not to create Structure on Characterics selection but create Structure with Key figures "Quantity" with restrictions of the Object values (Cars and Colour) or Directly create restricted Key figures on which you can select conditions on Restricted Key figures..

Alain

[Former Member]

oK better explanation :

Try in this case to apply conditions on your report this should solve your problem..

Alain

[Former Member]

Dear Alain,

I am very sorry, but conditions do not work on the structure. Have you tried this in practice?

Isn't here anybody from SAP who just can confirm whether it this is possible or not?

This issue is taking a long time... strange it does not work out.

Thanks anyway people!

[Former Member]

Hi Bart,

Alain is wrong that you can't hide structure if it has zero values. I have done this in my last project. Solution is already posted by Rob and GSM. Actually you need to do both the settings to hide zero row and columns. First goto query properties select to hide zero row and column ( Check 'Suppress Zero Columns/Rows' in your case). Then goto structure, right click and check the allow suppress zero check box. It worked well for me.

Regards,

RB

[Former Member]

Another solution is do not make value selection in the structure . I suppose you have created a structure with 3 Selection :

Field 1 when value is S1

Field 2 when value is S2

Field 3 when value is S3

Try just selecting the Object and filter it with S1-S2-S3 value. Otherwise delete the structure and only add the Object in your query Restricted on S1-S2-S3 value

Alain

[Former Member]

Hi

When you use a Structure it always display all values even if there is no Key figures available for each value.

To avoid these lines with 0 use a filter in the query instead of Structure.

That's why this is called Structure !!

REgards

Alain

[Former Member]

Hi Alain,

Thank you for your reply.

What you are saying is that it is not possible with structures to hide every single row that has al key figures equal to zero?

What do you mean by 'filter'? If you mean the filter area in Bex Analyser, I am afraid this is not possible. I give you a simple example (not the real case) to describe the aim in more detail:

S1 has description 'cars - yellow' and is a selection of

- 0PRODUCT = "CARS"

- 0COLOR= "YELLOW"

S2 has description 'cars - others':

- 0PRODUCT= "CARS"

- 0COLOR= ]"YELLOW"[

S3 has description 'other vehicles":

- 0PRODUCT = ]"CARS"[

What I want to make some things clear:

- the selections are necessary because we want to define the description in the report of every selection ourself (not the medium text of the characteristics nor text variables)

- we can not use only filters because the filter restricts ALL data, while the restriction on color depends on the group(selection) in the structure.

I hope you know what I mean. This is really a brainbreaker.

I managed to filter the right rows in 1 selection of the structure, but the problem is that the hidden materials (M2 and M3 in the example at top of the post) are hidden in the other selections even if they are not zero in S2 and S3.

I am VERY curious if someone can solve this problem...

Thank you already for your help.

Best regards,

[EDIT]

Alain,

I was writing this post at the same moment you were sending your last reply.

Your second suggestion would be OK if the selection was only containing one characteristic to restrict on. But the selections have 3 characteristics that have to be restricted. And not all values of the characteristics are used... So just all characteristics in the rows en restricting on the desired values is not enough as he will combine them to n x m relationships for which the key figures are not zero.

[/EDIT]

[Former Member]

Hi,

Try this...

If you have only one keyfigure I mean if your problem exactly how you have described then

Goto Query Properties->Display Tab->Suppress Zeros section

Choose Suppression = Active and Effect On = Rows then it should give you

S1 M1 50

S2 M2 100

S3 M3 90

GSM.

[Former Member]

Hi Rob and GSM,

Thank you for your help.

Rob, I already tried your solutions but it didn't work.

GSM, also your solution did not bring a solution as far as I was able to test it: what do you mean with 'Suppression=Active' and 'Effect On = Rows'? Are you working with BW3.5? I tried the 'Number Format' section: 'Suppress Zeros' in the drop down and checked 'Suppress Zero Columns/Rows'. I tried both (checked/unchecked), but without a good result.

I hope somebody can help me. Very important as a lot of Materials with zero's are shown.

Tank you

[Former Member]

Hi,

On query properties screen, Below Number Format section there is a section called 'Suppress Zeros'. In this section it has two dropdowns 1, for suppression and 2, effect on.

Choose suppression value = active

Choose Effect on value = Rows.

First consider this,

Currently for 0 value records system is not showing 0 values rather it is showing blank values. User might be willing to keep all the records if the record shows zero values than blank values on keyfigures.

For this you can create a new formula keyfigure in the structure by giving a condition.

(KF1 > 0) * KF1 and use this keyfigure in your query output. It displays 0 in place of blanks.

After you create this formula keyfigure, then test your query against

Choose suppression value = active

Choose Effect on value = Rows.

Make sure you have only one keyfigure in the result view.

GSM.

[Former Member]

Hi Sri

I read this post however;using (KF1 > 0) * KF1 still gives me blank and doesnt give me 0 value.

can you please guide me through exact steps and properties for it.

thanks

Bill

[Former Member]

Probably you have tried this already, but just to make sure...

If you right-click on the structure in the query designer and select properties. There's a checkbox there that allows you to supress zero rows.

Does that not help you either?