Retain formatting in BEx

[p_m8]

Gurus,

We're on BI 7.

I have inserted a query in my BEx Analyzer with the following steps:

1) Open BEx Analyzer

2) Selected the Query and insert the Variable as the query has variables.

3) Now I have 2 Characteristic (Product Categroy and Material) and 3 Key Figure (Year 2004, 2005 and 2006) display on the BEx workbook.

4) On the BEx Workbook now, I manually selected the Year 2005 and 2006 column (in the result are only, not the entire workbook), and change the color to Yellow.

5) I also added a new text (Good Year) on top of the Year 2005 and 2006, and merge the column, so I have something like following:

..Good Year..

2005...2006..

So basically Year 2005 and 2006 are grouped under Good Year.

I then click BEx toolbar ->Save Workbook as, to save the workbook and close this workbook.

When I open this workbook again, the formart and color remain the same as what I have done above. However, when I click on Refresh on the BEx toolbox, the color has gone.

How can I keep the formatting that I have done manually in the workbook?

Please advice, thanks.

[Former Member · Accepted solution]

Hi Lynn.

You can customize the styles that control the look of design items in the BEx. You cannot add new styles yourself - you will have to modify the SAP standard ones and then save this as a theme. You can then apply the theme to one or all workbooks.

See this [link|http://help.sap.com/saphelp_nw70/helpdata/en/f1/0a551ce09411d2acb90000e829fbfe/content.htm] where it mentions default workbooks.

Also this [link|http://help.sap.com/saphelp_nw70/helpdata/en/54/a493f7256b43698191a2623e5a0532/content.htm] where it mentions themes.

Regards

Jacob

[p_m8 · Accepted solution]

Hi Jacob,

Thanks again for your input.

I did what you says but still, when I click on the Refresh icon, the colour gone.

This is what I did:

1) I open the Workbook and click on the Refresh icon. I can see the result screen.

2) I select the 2 columns on the result, then I go to Home tab (in Excel 2007).

3) In the Home tab, I have Style tab. In the Stlyle tab, I can see the default Style is "SAPBEXstdData".

4) I then selected Style "SAPBEXaggItm". I can see the color and the fonts have changed on the 2 columns that I selected.

5) I click Save Workbook As, and save the workbook.

6) I open this workbook again, the color is still there for that 2 column. However, when I click on Refresh icon, the color gone!!!

Can you please show me the steps on where and how you do the modify on the style and save it as Theme?

Please help, thanks.

[Former Member · Accepted solution]

Hi Lynn

I haven't played around with this for some time, but I remember it was a bit weird.

After you have modified the bex-styles, you have to save them as a theme. To do this, you need to go to workbook settings and the themes tab.

Here, you create a new theme (you need auth for this... see second link in previous post). Once created, you need the new theme to take your modified styles. To accomplish this, I think you need to use "assign" in the theme tab (so in this case "assign" means "assigns styles to the theme I have selected", rather than "apply selected theme to workbook" which is what you would expect...). Saving puts the theme on the server, so do that too.

Now save your workbook.

br

Jacob

[p_m8 · Accepted solution]

Hi Jacob,

In Workbook Setting ->Themes tab, I have nothing dispaly on the Current Themes section.

On the Styles section, I can see a list of SAPBEX* styles.

Please kindly explain on how I create and save the Themes?

Need your help urgently.

Thanks.

[Former Member · Accepted solution]

Hi Lynn

I think you need to fix your authorisations, to make all the buttons appear in the Themes tab:

New/Delete/Apply/Save
If the following special authorization is assigned to your user name, you can change and save themes on the server: AUTHORITY-CHECK OBJECT 'S_RS_TOOLS' ID 'COMMAND'  FIELD 'THEMES'.
If you have this authorization, these buttons appear:
●      Create a new theme by choosing New.
●      Select a theme from the Current Theme list, and choose Delete to delete it. This deletes your theme locally.
●      Select a theme from the Current Theme list, and choose Apply to apply currently configured styles to it. When you do this, the BEx Analyzer reads the Microsoft Excel style definitions of all the styles in the workbook that are prefixed with SAPBEX* and stores them in the selected theme. This allows you to edit the styles in Microsoft Excel and then create a theme with these customized styles.
●      To save all the themes to the server, choose Save. 

Let me know if you have problems.

br

jacob

[p_m8 · Accepted solution]

Hi Jacob,

I have access to the mentioned button.

In Theme, I can click on the New to create new Theme.

Question is in the Theme ->Current Themes section, I don't have anything there.

When I click on New, then what next?

Can you provide me the steps to create and apply the theme so that I can change the fonts colours and style in BEx?

Is this the correct steps to create and apply the Themes:

1) In workbook, I select the columns in the Result area and change the color and fonts via Home ->Style (in Excel 2007), and select one of the SAPBEx* style.

2) I can see the selected columns have been changed with new fonts and color.

3) Then go to Workbook Setting ->Themes

4) Click on New and Apply (this will apply my current formatting in Result are into this Theme???)

5) Click Save and Save the workbook.

6) When I open the Workbook, the color remain the same. But when I click on the Refresh icon, the color gone again!!!!

I think the problem is I dont know exactly how to create and save the Themes (step 4 above).

Please advice on the steps to create and save the Theme.

I running out of time on this, need you help urgently.

Thanks.

Edited by: lynn on Jan 18, 2010 11:49 PM

[Former Member · Accepted solution]

Hi Lynn.

I don't have access to a 7.0 front-end right now, but I think you need to save the Theme to the server, after you created a new Theme and applied the current formatting to it. Maybe then you can select it in "Current Themes", which you need to do, to apply the theme to your workbook.

So, in your steps, you need a step 4.1:

1) In workbook, I select the columns in the Result area and change the color and fonts via Home ->Style (in Excel 2007), and select one of the SAPBEx* style.

2) I can see the selected columns have been changed with new fonts and color.

3) Then go to Workbook Setting ->Themes

4) Click on New and Apply (this will apply my current formatting in Result are into this Theme???)

(Yes, hitting Apply should "save" the style-changes you made, to the theme)

4.1) Save the theme to the server (save button in Theme-tab) and select it in current theme to apply it to your workbook.

5) Click Save and Save the workbook.

6) When I open the Workbook, the color remain the same. But when I click on the Refresh icon, the color gone again!!!!

regards

Jacob

[p_m8 · Accepted solution]

Hi Jacob,

I have done the step 4.1 but still not working. When I click on Refresh button, the color gone!!!

Do you think this is bug? Are you able to simulate this process in your system now?

Please help, running out of time to complete this...

Thnaks.

[Former Member · Accepted solution]

Hi Lynn,

Unfortunately, I cannot check the details - at least not for a couple of days, until I get access to a 7.0 system again.

But I was 100% able to make it work some time ago....it was" tricky", I remember, but didnt exactly make a note of the details One question though: When you open the workbook again, you open it from the server, not from your local pc, right?

br

jacob

[p_m8 · Accepted solution]

Hi Jacob,

That's correct, I save and open the workbook in/from Server. None of my works are saved to my local drive.

Please help...

Millions thanks in advance.

[Former Member · Accepted solution]

Ok. Then for now by best advice is to "try doing all sorts of weird stuff" to the theme to understand how it works. What you want to do is possible, so don't give up. Try saving the theme, leave bex, come back, check if it still there, make change to the styles, apply to theme, save, get out, come back, select theme to be used for workbook, save workbook, etc.... sometimes, it's a question of sequence

I will revert as soon as I have a chance to check myself (...but LIveOffice just messed up my Excel2007 really bad, so now Bex is not working at all for me...sigh)

br

Jacob

[p_m8 · Accepted solution]

I have tried all weird sequence with the theme and style but JUST DONT GET IT WORK.

I will continue tyring this but PLEASE GET BACK as soon as you can.

Need your help urgently...:(

[Former Member · Accepted solution]

Hi Lynn.

Sorry to keep you waiting!

So, I just checked and I followed this sequence of creating a workbook with it's own theme:

1) Run query

2) Save workbook as...

3) Go to design mode

4) Change some style

5) Save workbook

6) Go to workbook settings, themes tab.

7) Create New Theme

😎 Apply formatting to New Theme

9) Save New Theme and set to "reapply theme before rendering..."

10) Exit workbook settings

11) Save workbook

12) Exit designmode and check style is applied (yes)

13) Save workbook

14) Refresh query and check style is still applied (yes)

15) Exit excel completely and open Bex Analyzer again

16) Open workbook

17) Refresh query and check that style is still applied (yes)

Hence, I cannot re-create the problem you are facing. On thing maybe, could be that you have to change to design mode, before you make the change to the style... just an idea.

Let me know how it goes!

jacob

[p_m8 · Accepted solution]

Hi Jacob,

Thanks for the input. I have tried your steps below but it doesn't work!!!

In step 3, when I swith to Desing Mode, I can't see how we can select the font style or color

on the result screen.

===

1) Run query

2) Save workbook as...

3) Go to design mode

4) Change some style

5) Save workbook

6) Go to workbook settings, themes tab.

7) Create New Theme

😎 Apply formatting to New Theme

9) Save New Theme and set to "reapply theme before rendering..."

10) Exit workbook settings

11) Save workbook

12) Exit designmode and check style is applied (yes)

13) Save workbook

14) Refresh query and check style is still applied (yes)

15) Exit excel completely and open Bex Analyzer again

16) Open workbook

17) Refresh query and check that style is still applied (yes)

===

Can you please tell me what style you have change in your example?

What I want to achieve is I would like to highlight certain cell and change the font in the

result area. Example:

1) I run workbook, the query result is display on the screen.

2) If I point to any Key Figure (result) in the result screen, then I can see that the Style used

is SAPBEXstdData (with color light blue).

3) In the Style, I can change the Style to SAPBEXresltemX.

4) The font/color of that cell will change.

5) Now I go to Workbook Setting ->Theme and follow what yor steps above to save the Theme.

6) When I save and refresh the workbook, the style gone!!!

Please advice.

[Former Member · Accepted solution]

Hi Lynn!

I think we finally got to the bottom of this:

You need to modify the style that is applied to the cell you are looking to format. You cannot apply a new/different style definition to it, fx in your case, modify the SAPBEXstdData style (right click on the style, modify, now change font, color, etc).

You cannot get BEx to "understand" that for the type of cell you have selected, you want it to use a different style, fx MySAPBEXstyle or any of the other ones. You can only change the definition of the existing ones, because all result rows are setup to look like what is defined in SAPBEXstdData - however, if you modify that and save it in a theme, it's only the workbook with this theme, which will use the modified style; so, ALL reports will use the style SAPBEXstdData to format the results, but your workbook is using a theme in which you changed the SAPBEXstdData to use a different color.

So, again, modify SAPBEXstdData and save that to a theme.

Keep me posted!

Jacob

Edited by: Jacob Jansen on Jan 27, 2010 10:25 AM

[p_m8 · Accepted solution]

Hi Jacob,

Thanks for the note, indeed new discovery!!!

I have tried that (change the fonts/color of SAPBEXstdData) and it does work!!!

However, the problem now is the fonts and color of the entire result area are changed!!!

What I want to achieve is only change one of the Key Figure column to yellow.

Example, I have 3 Key Figure:

Plan, Actual and Different

So I woud like to change the cell color of the Key Figure Different.

Can we achieve it?

Also, if we can't change the style for particular cell, then I wonder why there are so many SAP style in the Excel? What's and where it use?

Please advice, thanks.

[Former Member · Accepted solution]

Hi Lynn.

Well, that is a definite no can do - unfortunately.

The reason is, as you have discovered, that you cannot control individual cells with the styles. The styles belong to "types of cells" that will be present in the report by virtue of the design items you are using, Fx the analysis grid. So all parts of the analysis grid have a style definition. You can change that on the same level of detail as it is delivered, so if you have a style for result rows/columns, then you can change it, but you cannot define a style for the intersection of the results row with one of the columns.

To do that, the BEx whould have had to beem build with this in mind, but that will probably be quite difficult, as you are facing a slice and dice scneario, so the intersection "moves" and breaks up as you navigate. Hence, you would have to define on all possible levels of detail, how the formatting of the intersection should behave...

The reason you got so many styles is probably because you have a number of different design items availble and depending on the item, you can tweak different parts, so you get quite a few styles.

Maybe report designer is the way for you - I havent gone anywhere near it, so I dont know any details...

good luck!

Jacob

[Former Member · Accepted solution]

Hi again.

Maybe you can use an exception in the query to do the formatting for you? With an exception, you can target 1 column and then evaluate it only for the results... you still would have to set thresholds with different colors...

If you can't make it work only with an exception (fx the values you get for results can not be predicted to fall into a certain range), maybe you can tweak the styles for good, bad, etc and set the same colors for each, save to theme and apply to workbook.

Good luck!

Jacob

P.S: One exception should do the trick, I believe, as you should be able to define an interval for "infinitely negative" to " infinitely positive" for the key figure and apply it to results or single values.

If you need a different color than the standard ones used for the alert levels, find the right SAPBEX* style and modify it - they are called things like SAPBEXgood1 or something like that, so you need to change the one that corresponds to the alert level you used in the exception.

Now save this change to a theme, use that theme in the workbook and save the workbook. Thus, you changed the look of SAPBEXgood1 in this workbook only and if you want to change it down the line, you can just change the color of the style in the theme again

That should work!

Edited by: Jacob Jansen on Jan 27, 2010 6:02 PM

[former_member199448 · Accepted solution]

Hi Lynn,

did you finally solve the issue?

If yes please share.

Thanks

Gilo