Skip to Content
author's profile photo Former Member
Former Member

BPS Layout formatting looks simple but tricky - Help

Hi ,

Created a layout with the below charaterstics and Month1 give the qty for that month. It works great but

1) When there is no data i.e no qty for that month , it suppose to be blank but it gives the below out put

Currenlty :

Material mplant DType month(qty)

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

Desired :

Material mplant DType month(qty)

2) When there is data i.e qty for that month , it suppose to give two records but it gives the below out put

i.e values and some more lines with material but no qty . Actually there is data for only 2 rows.

Currently :

Material mplant DType month(qty)

552477 552477 A 10

552477 552477 B 20

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

552477 552477

Desired :

Material mplant DType month(qty)

552477 552477 A 10

552477 552477 B 20

I created a macro to delete the duplicate when the number repeats but that doesnot solve because when ever I want to read the total number of rows in another macro it shows wrong total number of line because of these repeating material numbers .

How can I get the desired output ? is there any simple setting in the layout or XL with out using a complicated macros etc.........

Using sem bw 320

Please help . Thank you inadvance.

Raju

Edited by: venkat bhuaptiraju on Jan 8, 2008 6:01 PM

Edited by: venkat bhuaptiraju on Jan 8, 2008 6:04 PM

Edited by: venkat bhuaptiraju on Jan 8, 2008 6:07 PM

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 05:08 AM

    Hi,

    Can you try writing a macro with the following check.

    IsEmpty Cell(row,Dtype).value = True,

    Range(cells(row,1),cells(row,lastcolumn)).clearcontents

    This code will clear the contents of the cells which have Dtype as blank. so that you wont see the records which dont have quantity. You can put the check on Dtype or month as per your wish.

    Regards,

    Bindu

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 05:30 AM

    hi,

    i think it should work fine and should give you the desired output.

    check the data in the cube and check whether the data is consistent in the cube.

    how many records do you have in the cube ???

    and if you have restricted # also somewhere in the filter remove the # restriction and vicecersa and try what it shows.

    because cube cannot hold duplicate records.

    try this and let us know here. else giv emore details about number of records in the cube and your filter details.

    good luck

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 07:07 AM

    Hi Venkat,

    The data available for the 2 rows would be coming from the cube and may be the ones existing for the combination.

    Are you using variable to fill the lead columns? Even otherwise, the occurrence of repeating rows looks strange.

    If you are unable to delete via macros, as Bindu suggests, you could also try copying cells from some empty cells in your layout to the ones which have no qty but duplicate records for your material. this may also help clearing it.

    Regards,

    Aby

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 07:39 AM

    Hi,

    From what I understand in you example :

    1- You have 3 chars (Material ; Material Plant ; DType)

    2- You have 1 Key Figure (month (qty))

    3- Your layout is setup in order to show "All Possible Characteristic Combinations"

    4- There are restrictions (variables?) on Material and Material Plant but not on DType

    5- The number of lines ready for input is X ==> check Note 428092 - Additional function in Manual Planning.pdf in case you want to reduce it...but pay attention that it is a global setting.

    ==> The duplicate rows appear because of points 3, 4 & 5. In other words, BPS generates automatically X rows with the restrictions it knows and is waiting for a restriction on the last char (DType).

    In order to have the result you expect, maybe try to change the setting of the layout (first tab) in "From Transaction data".

    Regards,

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello All,

      The problen is finally solved , thanks for all your inputs .

      How : played with lead columns sequences.... I do not belive what is that to do with may be a bug but finally no issues. all set.

      Regards,

      Raju

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 01:55 PM

    Hi Venkat,

    It's a bug, it happens if you have a single material and plant ( Characterstic ), you wont get this error if you have another material to show in the layout.

    even i had the same issue, i tried with macros also, it doesn't work.

    all the best !!

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you all for your reply very very useful tips. This is getting some how very interesting and frustating at the same time.

      Bindu : I used the similar code that you gave me that works perfect it removes the rows data that I do not need but retains the format for exmple the STYLES that I applied for the data and header data

      Arya : The cube has the 2 records and my below msg will clarify that the issue is not with this

      Aby : Yes even, I do not understand why the those 2 columns data with no key figure values

      Jeoffrey :

      - I checked 3- : Layout set to From Transaction data

      - I checked 4- restrictions (variables?) may not be because ...see... my below observations........

      - I checked 5- ...may not be because ...see... my below observations........ BUT I am going through they

      are very interesting.

      Sappie :

      ...may not be because even for one material ...see... my below observations........

      More Facts : Sorry I would have given this in the beginning.

      - I am running an adhoc package that contains an existing layout which works perfect

      - When I copy layout and remove all the styles and macros still it works great

      - BUT when I create a new layout with exactly same setting NO CHANGE and run the SAME adhoc package I get those material numbers repeating .........

      I have both the layouts in my PC and compared but did not see any difference , Can you please look at it if you think there could be something there ? Please send me you email I can email you.

      Again thank you for looking in to it, your replies are very logical.

      Regards,

      Raju

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.