Insert blank rows in the report

[Former Member]

Hi,

Is there anyway to insert blank rows in the report between a set of rows just useful in differentiating records?

Also any wat to colour a set of rows?

Regards,

Manesh

[Former Member · Accepted solution]

Give the selection the name as apostroph. I guess in Excel it will be shown as a blank.

Best regards,

Eugene

[Former Member · Accepted solution]

I tried apostrophe,single quotes,double quotes and white space.

Nothing displays blank row. White space displayes "#/#"

Regards,

Manesh

[former_member188325 · Accepted solution]

Hi,

instead of 'blank' u can have '.' know? which is almost invisible and u can hide Kf value using cell definition.

and you can replce '.' with 'blank' using some macro code.

[Former Member · Accepted solution]

Yes you are right, "." is almost invisible.But not completely. Macros will have to written at the client location(I mean the client PC) finally, right? Since macros are more local.

Regards,

Manesh

[Former Member · Accepted solution]

Then, it's better to use '_', since '.' may be used as a delimiter of decimals or triads. And macro may replace them too.

Best regards,

Eugene

[former_member188325 · Accepted solution]

no i dont think so..once you insert macro ,it applies whenever you open that workbbok..let me check..

Thnaks

[former_member188325 · Accepted solution]

ya..as eugene said, '_' would be better..

here is the code to replace '#'(from one of the posts)...u can replace '#' with '_'

1) In excel menu Tools->macro

2) Enter the macro name say SAPBEXonRefresh

3) click 'create', will go to visual basic editor

4) To display '#' as '', paste the following code

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim c As Range

For Each c In resultArea.Cells

If c.Value = "#" Then c.Value = ""

Next c

End Sub

5) Close the editor and click on refresh again.

Thanks

[Former Member · Accepted solution]

Thank you all.

Let me try this and will get back to you with due points.-;)

Thanks

Manesh

[former_member188325 · Accepted solution]

You have to paste the code exactly as it is...and just replace '#' with '_' in code if u r using '_' in Query.

[Former Member · Accepted solution]

That worked...Thanks.........

[Former Member · Accepted solution]

Hi,

It worked it my machine. But my collegue, when he opened the same query and executed in his machine, it still showed #.

This is what i was referring to a local for macros.

Any clue?

Manesh

[Former Member · Accepted solution]

HI Manesh,

Try this out.

Create a new selection and restrct any character with #/# value.

Now the selection name will be #/#, delete it and write Alt-0160 over there. (Press Alt and then type in 0160)

This is a Blank Character.

Press ok and that shud look fine.

hope it helps.

AB

[former_member188325 · Accepted solution]

you have to save it as workbook to make it global i believe.try it.

[Former Member · Accepted solution]

Hi Murali,

So you mean to say that I sent this saved workbook to the client which they will open in their BEx. Please correct me.

But if this is the case, it will not work, because we send only query names and they will execute them in their machine.

Regards,

Manesh

[Former Member]

I am not sure if have been able to meet your requirement to insert blank rows in your query. if not , this will help. You can use the 'define cell' functionality, which is activated after you have defined 2 structures in your query. After that, for each cell in the row that you want to be blank, define it as 'always hide'. Thus the characteristic/keyfigure will show up as blank, tho it may actually have a value.

[Former Member]

Hi All,

When you Suppress Zeros, the problem is that when we want to display the zeros in another columns/rows, then it wont be possible.

I tried the Hiding mchanism and it works fine.

Please let me know the ramifications(if there is any) in this approach.

Regards,

Manesh

[Former Member]

Hi,

If you are refering to the same query in which you have only one column, tell me what hiding mechanism you have tried. Is it at cell definition level? Even that will work fine I believe.

[Former Member]

Yes,

It is the same hiding mechanism in the cell definition.

Thanks,

Manesh

[Former Member]

Hi,

Then did you ge tthe desired output? Well if not let me know what and how you have done. I wish to close your thread by providing solution.

Thanks

[Former Member]

Hi,

This is the way I did. I was expecting for an answer whether this approach is fine or has got any consequence, since I am trying it for the first time.

1. Create a New Selection with the Description as " " (white space without quotes)

2. Select the Cell Definition and go to the right column of this row and right click and select New Formula.

3. In the formula window just add 0

4. Close the formula window

5. On the Cell Definition Screen, right click on the column where we just created a formula and go to Properties and under Hide drop down, select 'Always Hide'

6. Close the Cell Definition and save the query and execute.

7. It works for me.

I will close the thread if this is okay.

Regards,

Manesh

[Former Member]

Hi Manesh,

There is no problem with you approach as there is only one column in your report. You can proceed.

[Former Member]

Thank you AVR,

I will close the thread.

Regards,

Manesh

[Former Member]

Hi Manesh,

Well I am trying to ask you to look into the query design from cell definition you created yesterday.

As Sudhakar said you can use New formula and supress zeros from the properties which would work better than using a new selection. In the description just use a dot. This will help you I believe.

[Former Member]

Hi Manesh,

Just create a formula with out name put zero in the formula bar.

save it.

Go to Query properties select suppress zeros and check suppress zeros.

Place this formula after the row you want.

Thanks,

[Former Member]

Hi,

Check the below link regarding inserting blank rows in the report. I gave the solution in that. You can insert blank rows while defining the query itself but if you want to give colors you can do that in owrkbook with simple VB coding.

I hope this will help you.

In continuationwith other posts of yours...

In the new selection give space. and then go for restrictions as I said in the link. It will definitely work.

Message was edited by: AVR - IGA

[Former Member]

Hi AVR,

I tried that. I will list down the steps followed.

1. I created a <i>New Selection</i> under the current structure

2. Gave the name <i>New Selection</i>

3. Moved <i>Profit Center</i> to the <i>Description</i>

4. Right Clicked <i>Profit Center</i> and selected <i>Restrict</i>.

5. Moved <i>"#/#"</i> from <i>Fixed Values</i> to the right <i>Selection</i> box.

6. Right clicked "#/#" and clicked <i>Exclude from Selection</i>

7.Closed all and saved the query and executed.

And I get this row.

The value show towards the right of this is coming from a top row.

<b>New Selection 9,490,278.40 *</b>

Kindly help.

Manesh

[Former Member]

Hi Manesh,

Is profit center a heirarchy? If so put thhe top node and exclude it from selection. It will display blank row. Also check the cell definition. After changing the structure the cell definition also needs to be changed to the right cell. Check out and let me know.

[Former Member]

Hi AVR,

Yes ProfitCenter is a Hierarchy.

I did put the top node and excluded from the Selection resulting in a blank column to the right. The left column still remains "New Selection"

How do I change the Cell Definition (to the right cell?)

I went to the <i>Cell Definition</i> screen and right clicked on the left column,but it gives the same set of properties of the <i>Selection</i>.

Regards,

Manesh

[Former Member]

hi,

"New selection" means description? If so just give space in the description.

Regarding cell definition, define how you defined earlier exactly in the cell where you want to display. It should not be defined in the blank rows. check it out.

[Former Member]

Yes, "New Selection" is the description.

When I give single(or any number) space, and when I closed the Selection window, the description automatically gets changed to the "Profit By Activity" which is the node(the one which I selected from Hierarchy drop down).

Regards,

Manesh

[Former Member]

Hi,

In that new selection see if you can put some other top level objects which can be excluded from selection and effect the report functionality. The it will not display the node name.

[Former Member]

Hi,

Even that did not work.

But I found a work-around. Please tell me if this is okay.

I am getting a blank row after I did this.

1. Create a New Selection with the Description as " " (white space without quotes)

2. Select the Cell Definition and go to the right column of this row and right click and select New Formula.

3. In the formula window just add 0

4. Close the formula window

5. On the Cell Definition Screen, right click on the column where we just created a formula and go to Properties and under Hide drop down, select 'Always Hide'

6. Close the Cell Definition and save the query and execute.

7. It works for me.

let me know if this is fine or if it has got any side-effects.

Regards,

Manesh

[Former Member]

hi,

My next step I am going to advice is to go for quotes. Well if your requirement can be met with that, go ahead. But you needn't define any cell definitons for blank rows. i said you to check the definition for the carryforward bal which was in other cell. Just don't define any cell definitions for blank rows. They will work fine. I will be back after four hours...will see if any doubts.

Message was edited by: AVR - IGA

[Former Member]

Hi,

I did not understand the second part of this message which i have italicised. Can you walk me through those steps if you dont mind.

Regards,

Manesh

hi,

My next step I am going to advice is to go for quotes. Well if your requirement can be met with that, go ahead. <i>But you needn't define any cell definitons for blank rows. i said you to check the definition for the carryforward bal which was in other cell. Just don't define any cell definitions for blank rows. They will work fine. </i>I will be back after four hours...will see if any doubts.

[Former Member]

Hi,

Try to add a new selection. Add there the set of KF and chars that you used in the other selection. Restrict a char(s) by values not existed in the master data/transaction data.

Best regards,

Eugene

[Former Member]

Hi,

I tried to do that, but only the second column(where the value is shown) is getting blank, but the first column(the selection name) appears still.

This is how i am looking at the data to be prsented.

Right now I have 2 selections to represent the <i>TOTAL VALUE</i> and the <i>GROSS FY 2006 BUDGET</i>

<b>

TOTAL VALUE $291,805,786

-

---

Blank row---

-

---

Blank row---

GROSS FY 2006 BUDGET $291,805,786

</b>

I am looking at having blank rows(irrespective of the number of columns) between them.

Regards,

Manesh

[former_member188325]

Hi,

i guess both TOTAL VALUE and GROSS FY 2006 BUDGET are elements of a sturcture in rows.

try this:

after executing Query,insert rows between them using normal excel functionality and save it as workbook.

Thanks

[Former Member]

Yes You are right.

Both of them are elements of the structure(<i>selections</i> precisely).

If I try to add columns after the query is executed,it does not serve the purpose.

I am looking at a way to include the rows before the execution, so that once the query gets executed, the rows will be displayed.

Regards,

Manesh

[Former Member]

This is basically required in a a structure where the blank rows need to be inserted between different <i>Selections</i>