Export to Excel Data only

[Former Member]

Is there any way to export to excel data only format through web services? I can figure out how to export to excel but not data only.

Thanks in advance...

[joshua_morin · Accepted solution]

Agreed Caleb. The ViewReport object and refresh don't do anything. I have a support ticket with SAP on this and I will make sure to post the answer when I get it.

Ciao.

[joshua_morin · Accepted solution]

Caleb,

I'm told that executing the report by scheduling it in code, then exporting to Excel in the scheduling job logic will work. Sit tight - will post when I know more.

[aasavaribhave · Accepted solution]

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using BusinessObjects.DSWS;

using BusinessObjects.DSWS.Session;

using BusinessObjects.DSWS.BIPlatform;

using BusinessObjects.DSWS.ReportEngine;

using BusinessObjects.DSWS.BIPlatform.Desktop;

Edited by: Aasavari Bhave on Oct 14, 2009 12:42 PM

[aasavaribhave · Accepted solution]

public partial class ExportExcelDataOnly : System.Web.UI.Page

{

String connectionURL = "http://<server name:8080/dswsbobje/services/Session";

String username = "administrator";

String password = "";

String cmsname = "<cms name>";

String reportname = "<report name>";

protected void Page_Load(object sender, EventArgs e)

{

BusinessObjects.DSWS.Connection boConnection = new BusinessObjects.DSWS.Connection(connectionURL);

EnterpriseCredential boCredential = new EnterpriseCredential();

boCredential.Login = username;

boCredential.Password = password;

boCredential.Domain = cmsname;

Session boSession = new BusinessObjects.DSWS.Session.Session(boConnection);

SessionInfo boSI = boSession.Login(boCredential);

string[] strRepEngURL = boSession.GetAssociatedServicesURL("ReportEngine");

ReportEngine repEngine = ReportEngine.GetInstance(boSession, strRepEngURL[0]);

string[] strBIPlatformURL = boSession.GetAssociatedServicesURL("BIPlatform");

BIPlatform bipService = BIPlatform.GetInstance(boSession, strBIPlatformURL[0]);

string query = "path://InfoObjects/Root Folder/Report Samples/Demonstration/world sales report@*";

ResponseHolder rh = bipService.Get(query, null);

if (rh == null)

return;

InfoObjects oInfoObjects = rh.InfoObjects;

CrystalReport oInfoObject = (CrystalReport)oInfoObjects.InfoObject[0];

if (oInfoObject.PluginProcessingInterface == null)

oInfoObject.PluginProcessingInterface = new ReportProcessingInfo();

if (oInfoObject.PluginProcessingInterface.ReportFormatOptions == null)

oInfoObject.PluginProcessingInterface.ReportFormatOptions = new CrystalReportFormatOptions();

oInfoObject.PluginProcessingInterface.ReportFormatOptions.FormatSpecified = true;

oInfoObject.PluginProcessingInterface.ReportFormatOptions.Format = ReportFormatEnum.EXCEL_DATA_ONLY;

[aasavaribhave · Accepted solution]

InfoObject schedObject = bipService.Schedule(oInfoObjects).InfoObject[0];

query = "cuid://<" + schedObject.NewJobID + ">";

rh = bipService.Get(query, null);

Excel newInstance = (Excel)rh.InfoObjects.InfoObject[0];

while (newInstance.SchedulingInfo.Status != ScheduleStatusEnum.COMPLETE)

{

rh = bipService.Get(query, null);

newInstance = (Excel)rh.InfoObjects.InfoObject[0];

}

Response.Clear();

Response.ContentType = "xls";

Response.AddHeader("Content-disposition", "inline;filename=myreport.xls");

String downloadFileID = bipService.StartSingleDownload(newInstance.CUID, 0);

System.Int64 startIndex = 0;

DownloadStatus dlStatus = bipService.DownloadFile(downloadFileID, startIndex);

Response.OutputStream.Write(dlStatus.BinaryData, 0, dlStatus.BinaryData.Length);

while(dlStatus.EndOfFile != true)

{

dlStatus = bipService.DownloadFile(downloadFileID, dlStatus.NextReferencePosition);

Response.OutputStream.Write(dlStatus.BinaryData, 0, dlStatus.BinaryData.Length);

}

Response.End();

bipService.FinishDownload(downloadFileID);

boSession.Logout();

}

}

[aasavaribhave · Accepted solution]

Couldn't post the code in 1 reply so I have broke it in last 3 replies. This code using WS to schedule a Crystal Report to Excel data Only, checks that the scheduled instance is successful and then using response object views the Excel data only instance with MS Excel. You can add the code to delete the report instance after it is viewed.

[Former Member · Accepted solution]

Thanks Aasavari, I will test this method out in our project soon.

Is it necessary to use the scheduler for this?

Edited by: Caleb Cittadino on Oct 15, 2009 7:57 PM

[aasavaribhave · Accepted solution]

you can't do it without sceduling in WS.

[joshua_morin · Accepted solution]

The code that's been posted in this thread works in our environment. Thanks Aasavari.

[aasavaribhave · Accepted solution]

Can the the person who started this thread mark it as answered?

[Former Member · Accepted solution]

Hi,

I am using WCF to retrieve reports as PDF.

When I tried to run

....

rh = bipService.Get(query, null);

Excel newInstance = (Excel)rh.InfoObjects.InfoObject[0];

while (newInstance.SchedulingInfo.Status != ScheduleStatusEnum.COMPLETE)

{

rh = bipService.Get(query, null);

newInstance = (Excel)rh.InfoObjects.InfoObject[0];

}

...

I am getting CrystalReport type in rh.InfoObjects.InfoObject[0]; and not Excel.

Any ideas why?

Thank you very much,

Robert