[Moderator Note: Removed duplicate post.]
I am wondering if anyone knows how to save a BO report using a macro as a Text pipe delimited file as opposed to a standered tab or CSV delimited file (that I know how to do).
Thank you,
Zac
Zac Adam 
(BOB member since 2002-11-12)
I know how to do this interactively… :? not sure how to do the equivalent via a macro. If you open a document, click the “cube” button, and click Export then you get the standard export dialog box. If you set up a file type that is not recognized (I often use “out” or “pipe” :-)) then you can enter the delimiter that you want to use.
But via a macro… hmm, don’t seem to be able to specify a delimiter. Or anything other than the standard types. You could do it the “brute force” way and simply work your way through the data, creating a pipe-delimited string, and write that out to a file. It would be ugly and slow, but functional.
Dave Rathbun (BOB member since 2002-06-06)
When you do an export vs a sav as, you are exporting all of the results from the query. When you do a save as, you are just saving what you want (sub set) and that is what I need. I have filters and a cross tab in the report. So that way wont help.
What I am looking at doing now is saving it as a text then writing a program to open the text and replace all tab spaces with pipe characters, but i would rather not do that, you know.
Zac
Zac Adam (BOB member since 2002-11-12)
Aha, didn’t catch the fact that you want to do a “Save As” instead.
What you can do in that case is create a variable that concatenates all of the desired result objects with a pipe symbol and display only that variable on the report that you want to save.
So if you want Year, Quarter, Invoice Date, and Revenue you could create a variable that looks like this:
=<Year> & "|" & <Quarter> & "|" & FormatDate(<Invoice Date>,"MM/DD/YYYY") & "|" FormatNumber(<Revenue>, "$#,##0")Apply your filters (etc) to that block and do a save, see if that works for you.
Dave Rathbun (BOB member since 2002-06-06)
In theory yes that would work, but then it would be a maintance nightname. The actual report will still be scheduled and used by users, just one of the formats was to be pipe delimited to import into some subsystem.
So I do not think that option will work.
Thank you though
Zac Adam (BOB member since 2002-11-12)
You can keep the table with the data to export in a separate tab and attach a macro to export that report tab to a text file, which the BCA runs after it has finished refreshing the data. Because it’s a separate tab, the users can just not bother looking at it.
I’m not sure what would make this a maintenance nightmare.
Paul Williams 
(BOB member since 2002-07-10)
What I was saying that making it all one field on the report wouldn’t work to well due to the fact that the report gets sent to users as a base and then add and remove fields depending on their situation. Also I was trying to make the report look clean and having it all concatenated on to one field wouldn’t have accomplished this.
Also dont know if managent likes the idea of having tabs that users can see but shouldnt touch. We dont have the smartest users here believe it or not. 
Zac Adam (BOB member since 2002-11-12)
Ah! Gotcha.
Will the export contain data from all of the objects in the data provider? If so, would an AfterRefresh() macro that runs through all of the objects (columns?) in the data provider and concatenates their names in a variable be any use?
It’s a rough idea, so this probably won’t work as-is, but would something like this be any use? Create a table with and it should (I think) adapt to the objects in the data provider.
Variables("Var").Value = "="
For each Col in DataProviders(1).Columns
Variables("Var").Value = Variables("Var").Value + "<" + Col.Name + "> | "
Next Col
Variables("Var").Value = Left(Variables("Var").Value, len( Variables("Var").Value ) - 2 )
Paul Williams (BOB member since 2002-07-10)
yes unfortunally when you do an export it brings everything back. I will look in to that table creation though and see if that will work. I would think though that BO would have it so you could save it as any tabulation you want. Do it on a save not just an export. Oh well.
Zac Adam (BOB member since 2002-11-12)