BusinessObjects Board

Save WebI reports to a text file

sdk development
#1

Hi,

I am on BO XI 3.1. I know that WebI does not have the option to save to a text file. What I want to find out is, is there any workaround for this? The client does not want us to develop the reports in DeskI, so that is ruled out. Any suggestions?

Thank you,
BO Enthusiast.

MPSDEEP (BOB member since 2006-09-26)

#2

In XI3, a WebI report can be saved as .csv.

Joe

joepeters :us: (BOB member since 2002-08-29)

#3

Hi Joe,

But .csv output does not retain any report-level aggregation, calculations, sorting, breaks, character formatting, or column ordering. It just gives the output of SQL query that the objects generate. Do you know of any SDK or any such solution to achieve this?

Thank you,
BO Enthusiast.

MPSDEEP (BOB member since 2006-09-26)

#4

Oh, gotcha. I don’t think the Report Engine SDK can be used to get values from a table structure, but it might be possible.

Or, you could save the report in Excel format, then use Excel VBA to save it as text.

Joe

joepeters :us: (BOB member since 2002-08-29)

#5

Joe, thank you for your quick response. Can you please elaborate a little more on the possibility (or not) of the Report Engine SDK in saving the WebI report to .txt.

And on the second option of having an excel VBA to convert it to text file, I have a couple of questions. 1) Pardon me, if my question comes off as ignorant. But if MS Excel allows you to save an excel sheet to a text file, why do we need a Macro to do that? I cannot think of any reason other than being able to add a delimiter. 2) How can these text files be automatically saved to the folders in Infoview?

MPSDEEP (BOB member since 2006-09-26)

#6

You can check the SDK docs here. I took a quick peek and didn’t see anything that would indicate that you can retrieve values from a block.

Not ignorant :slight_smile: Yes, you can definitely do it manually (via Save As), but I assumed you were looking to automate the process as much as possible

Well, if go with the Excel VBA method, you could use a variation of my code here to publish the csv back to the CMS.

Joe

joepeters :us: (BOB member since 2002-08-29)

#7

Joe, thank you again. So, going back to the original question, is it not possible to achieve conversion to text format directly via SDK in your opinion? ( I have yet to go through the SDK documentation that you have pointed to.)

And regarding converting the excel files to text using Excel VBA, how is this conversion triggered? It is my understanding that such a conversion can be automated by tying it to an event such as opening the excel workbook. Do you think it can be time-triggered? What I mean by that is, can the macro be made to execute at say 9:00 AM every day? The reason I ask this question is, if the scheduler runs at a particular time each day or month depending on the report refresh frequency and saves the output excel files to an FTP location. Then can we automate this macro to run at 10:00 AM, assuming that all the reports take an hour to refresh. Then use the VB macro that you have posted in BoB can be used to put all these text files back to CMS. I know, I am talking in hypotheticals now. But it would be great to know your view on this, if its doable or not.

In an ideal world, I would have loved it if the WebI let us save it directly to text files, like DeskI does. :slight_smile:

MPSDEEP (BOB member since 2006-09-26)

#8

I really don’t think it’s possible using the WebI SDK. I didn’t see anything in there relative to saving report pages as csv or even accessing the values of individual cells.

Using the VBA route, I would think that you would schedule the WebI report to generate Excel output to share via a UNC path. Then have something that uses Excel to pick up that file and do a simple “Save As” operation to generate the csv. That something could be either an Excel workbook with an auto-open macro, a VB app that calls Excel, or (if you want to be really hokey), a DeskI report using VBA.

Joe

joepeters :us: (BOB member since 2002-08-29)

#9

You can add a context menu item that branches out to some custom jsp, fetches the current storage token from the session, gets to the documentinstance, exports it as xml, then you have the actual data.

M :de: (BOB member since 2003-11-25)

#10

@Joe,

Thank you for the response. I am working implementing your suggestion with regards to Excel VBA and am bummed as to how to go about doing the conversion for about 95 reports in one go. Any Suggestions?

@M,

Thank you for your response. Can you please elaborate on the solution that you have provided?

Thanks,
BO Enthusiast.

MPSDEEP (BOB member since 2006-09-26)

#11

This probably needs some tweaking, but it’ll go through all .xls files in a directory and re-save them as csv:

Sub Whatever()
    Dim xFile As String
    Dim strPath As String
    strPath = "c:\data\"
    Dim strNewName As String
    
    xFile = Dir(strPath & "*.xls")
    With Application.Workbooks
        While xFile <> ""
            strNewName = Left(xFile, Len(xFile) - 4) &amp; ".csv"
            .Open (strPath &amp; xFile)
            Call .Item(xFile).SaveAs(strPath &amp; strNewName, xlCSV)
            .Item(strNewName).Close (False)
            xFile = Dir()
        Wend
    End With
End Sub

joepeters :us: (BOB member since 2002-08-29)

#12

Joe, thanks for the reply. I will work on it and let you know if that works. It looks like the client is on the verge of OK’ing DeskI.

-BO Enthusiast.

MPSDEEP (BOB member since 2006-09-26)

#13

http://www.mnsoft.org/uploads/media/NottM_BOInsight2007_Final.ppt

slide 45 ff

M

M :de: (BOB member since 2003-11-25)