blank first column when exporting report to excel

building reports desktopintelligence
1

i have tried everything i could think of but cannot get the data to show in column A of the excel spreadsheet. works fine if i do it manually via the data view dialog box but not when scheduled automatically in BCA or via a macro. :crazy_face:

this is a ridiculous thing to be wasting my time on and the solution is probably something simple. any ideas?

whoa :canada: (BOB member since 2005-03-03)

2

If your 1st column has heading of the data available for the data, then make sure that you select the option first row contains header in the data provider.

Hopw this solves your issue

NEO

KhoushikTTT :us: (BOB member since 2005-02-24)

3

Thanks but thatā€™s not the issue. Header and all rows show up fine but the first data COLUMN shows up in column B of the spreadsheet. The whole data grid is one column to the right.

whoa :canada: (BOB member since 2005-03-03)

4

Go into the ā€˜Format Tableā€™, select the Appearance tab, and set the horizontal position as relative to upper section, with a left setting of zero.

That should do it

Cheers
Scruffy

scruffy :australia: (BOB member since 2005-01-19)

5

Appreciate the reply. Tried it before. Tried again. No luck.

:reallymad:

whoa :canada: (BOB member since 2005-03-03)

6

I have been reading around for a while, looked at a business objects document ā€œBest Practices when exporting to excelā€ but I really can find no solution to the first blank column.

tired page setup, tried moving margins in page layoout, tried table format (setting to zero)ā€¦

any solution??

I am on BO 6.1b and have used it for 3 years, so I am pretty comfortable with the tool.

Ideas?

HollisL (BOB member since 2005-09-15)

7

If you are saving as Excel then forget about page setup; the whole issue is the positioning of the table.

Get rid of page layout view and have a look at the table. If it is not in the top left corner then it needs to be. If it is, then check the buglist on the BO website as this may be a glitch in 6.1b
Iā€™ve not had any problems in 6.5.1 with being about to do this.

8

Blank rows and columns are generated because the table is not aligned correctly to the Section. Iā€™ve resolved this by aligning tables to be Horizontally and Vertilcally aligned to 0 pts away from the section, or aligned directly to one of the Margins.

digpen :us: (BOB member since 2002-08-15)

9

Same problem here. Save as excel saves data from to cell B2 not to A1 as it should do. Table format Appearance tab: Horizontal position Relative set to: Right margin, Vertical Position Relative set to: Top margin. Save as excel, open it in excel and top row and first column are left blank in excel.
BO versions 651 and 652.

Any suggestions?
Rgds,
Andu

andu (BOB member since 2005-04-13)

10

I seem to follow all the ā€œrulesā€ but no luckā€¦ must be a bug
Document1.xls (114.0 KB)

HollisL (BOB member since 2005-09-15)

11

Does someone already have a solution for the problem. I have the same problemā€¦

Jemo :netherlands: (BOB member since 2005-02-16)

12

The solution is really easyā€¦
As you are using a macro, you can write a 4 line query to
delete column A and Row 1 whenever the report runs.
The code may be as below:
Sub DelRowCol()
xlsApp.Rows(ā€œ1:1ā€).Select
xlsApp.Selection.Delete Shift:=xlUp
xlsApp.Columns(ā€œA:Aā€).Select
xlsApp.Selection.Delete Shift:=xlToLeft
xlsApp.ActiveWorkbook.Save
End Sub

Hope this will resolve the issue.

Anand Sahu (BOB member since 2005-08-03)

13

I too had a similar issue, but found a way to work around it using VBA. When i send to BCA I execute a macro that first creates a .XLS file, then reopens it in the background and I then use Excel VBA to delete the first column & first row (as both were blank in my situation).

jresendez :mexico: (BOB member since 2004-05-03)

14

Iā€™ve just taken SP2 and Feb 2006ā€™s hot fix updates; this has inflicted the bug upon me. Does anyone know which particular patch needs to be reversed? I really that itā€™s not the one to fix what I took the patch for in the first place. :cuss:

15

This behavior was by design in 6.x FullClient Excel export. Not a bug.

It is changed in XI R2. Reports exported as Excel (I am talking about the SaveAs feature, not the cube export from the data manager dialog) now donā€™t offset everything by one row and one column anymore.

There are two workarounds for 6.x :

  • use a post-processing routine, such as the written in VBA above. In essence, you can start an Excel instance, load the .xls document, remove the row and column, and save it.
  • export the report as Html and reopen it in Excel 2000, XP or 2003. Opening the Html report in Excel can also be achieved with some VBA code.

Hope this helps,

Stephane Rodriguez (BOB member since 2005-10-11)

16

Well itā€™s certainly not the way that it worked in 6.5.1 which I was happy with. If I wanted an offset, Iā€™d have set an offset on my table.
Iā€™m not a VBA coder, Iā€™m a Data Warehouse Developer.
I donā€™t want to be a VBA coder.

Very poor decision imo.

It feels like BO are turning into Microsoft and presuming to know what we want rather than actually asking us. Leaving row 1 and column A blank are slack - thatā€™s what my page margins are for. There should at least be an option within the ā€œSave As Excelā€ functionality.

As for the macro solution, how would that work in an environment where macro security is high and itā€™s distributed via email?

17

It doesnā€™t take a degree in programming to write a VBA macro or any automation code that does what said above. As for macro security, you can actually embed that macro right inside the .rep document if that helps. In other words, you donā€™t need to run this from outside.

Also, I think you could ask BO support if the removal of the Excel row/column offset can be backported to 6.x. If you donā€™t ask, you can be sure it wonā€™t happen. :wink:

Hope this helps,

Stephane Rodriguez (BOB member since 2005-10-11)

18

Already raised Stephane. 8)

I took SP2 and the patch to fix the bugs that SP2 introduced ( :shock: ) and Iā€™ve asked them to establish what needs to be removed to get back the buggy utopia of sticking the report in A1. :lol:

As for the .rep VBA process, I certainly havenā€™t got the time let alone the inclination to insert and maintain VBA routines in the 150 reports that weā€™ve already built. VBA doesnā€™t look particularly difficult and I can make sense of it, but itā€™s not something that I particularly want to add to my skill set. Not because I donā€™t think itā€™s worth having, purely because itā€™s not a direction that I want my career to go in.

Regards,
Mark

19

Someone from BO support told you that a combination of the above would remove the row/col offset? Iā€™ll repeat myself, this is not a bug, it is by design and I only made the change in XI R2 (once I was still working there last year).

You can always ask BO support to backport the changes to 6.x, but I would be surprised that theyā€™ve made the change purposedly already.

I get the idea, but there is nothing stopping you from doing a bulk Excel reopen/resave in a simple loop. Actually there is a number of such code snippets available in this website, in the VBA section.

Hope this helps,

Stephane Rodriguez (BOB member since 2005-10-11)

20

1.) For most of my users, itā€™s beyond my expectations for what they could do.

2.) Many of us are not licensed for the SDK, so canā€™t/shouldnā€™t use VBA any way!

Anita Craig :us: (BOB member since 2002-06-17)