EXCEL_DATA_ONLY

[Former Member]

Hi,

We have Crystal Report Server w/ Business object.

We are using Business objects in order to access reports via Web services.

We are able to access a report and get it in PDF or excel formats.

Now we need to get a report in EXCEL_DATA_ONLY format

according to this thread

It is only possible to achieve via scheduling (Please see the end of aforementioned thread).

When I implemented suggested solution

.....

string query = string.Format("cuid://<>", crystalReport.CUID);

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

Edited by: Robert Parker on Nov 5, 2010 10:52 PM

[Former Member · Accepted solution]

Your query is still referring to the original document, not the latest instance of that document. I know that after a report is scheduled, you can use the SI_NEW_JOB_ID property to get the ID of the scheduled instance. In the Java Enterprise SDK, that would be

newInstance.properties().getProperty("SI_NEW_JOB_ID").getValue()

Hopefully that helps. I can't provide more specifics -- it has been a while since I've used the Web Services SDK or .NET, I work mainly with the Java Enterprise SDK.

[Former Member · Accepted solution]

I tried

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

string query = string.Format("cuid://<>", schedObject.NewJobID);

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

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

I got Excel newInstance however newInstance.SchedulingInfo is null, I decided to skip the loop:

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

{

boResponseHolder = bipService.Get(query, null);

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

}

and tried

RetrieveData boData = new RetrieveData();

RetrieveView boView = new RetrieveView();

RetrieveMustFillInfo boMustFillInfo = new RetrieveMustFillInfo();

RetrievePromptsInfo boPromptsInfo = new RetrievePromptsInfo();

boMustFillInfo.RetrievePromptsInfo = boPromptsInfo;

boData.RetrieveView = boView;

Action[] actions = new Action[1];

actions[0] = new Refresh();

DocumentInformation boDocInfo = boReportEngine.GetDocumentInformation(reportCUID,

boMustFillInfo,

actions,

null,

boData);

got error on boReportEngine.GetDocumentInformation "getDocumentInformation exception (Error: WRE 99998)"

I searched the forum but did not find anything,

Thank you,

Robert

[Former Member · Accepted solution]

I succeeded to get it going for report w/o parameters:

CrystalReport crystalReport = (CrystalReport)boInfoObjects.InfoObject[0];
 if (crystalReport.PluginProcessingInterface == null)
   crystalReport.PluginProcessingInterface = new ReportProcessingInfo();
if (crystalReport.PluginProcessingInterface.ReportFormatOptions == null)
   crystalReport.PluginProcessingInterface.ReportFormatOptions = new CrystalReportFormatOptions();
crystalReport.PluginProcessingInterface.ReportFormatOptions.FormatSpecified = true;
crystalReport.PluginProcessingInterface.ReportFormatOptions.Format = ReportFormatEnum.EXCEL_DATA_ONLY;
crystalReport.PluginProcessingInterface.ReportFormatOptions.FormatInterface = new ExcelDataOnlyFormat();
crystalReport.PluginProcessingInterface.ReportFormatOptions.UseExportOptionsInReport = true;
            
if (crystalReport.SchedulingInfo==null)            
     crystalReport.SchedulingInfo=new SchedulingInfo();
            
crystalReport.SchedulingInfo.RightNow = true;
crystalReport.SchedulingInfo.RightNowSpecified = true;
if (crystalReport.PluginProcessingInterface.ReportParameters!=null)
{
 ReportParameter[] repParams = crystalReport.PluginProcessingInterface.ReportParameters;
foreach (ReportParameter parameter in repParams)
{

   PromptValue[] dPromptValues;
   switch (parameter.ParameterName)
   {
      case "Company Code":
               dPromptValues = new PromptValue[1];
               dPromptValues[0] = new PromptValue { Data = "LPL" };
               dPromptValues[0].Options = new ReportParameterValueOptionEnum[] {    
                   ReportParameterValueOptionEnum.VALUE_HAS_NO_LOWER_BOUND, 
                   ReportParameterValueOptionEnum.VALUE_HAS_NO_UPPER_BOUND };
               parameter.CurrentValues = dPromptValues;
               break;
   case "AS_FUNDID":
               dPromptValues = new PromptValue[1];
               dPromptValues[0] = new PromptValue { Data = "LPL.2q.1724404" };
               dPromptValues[0].Options = new ReportParameterValueOptionEnum[] { 
                        ReportParameterValueOptionEnum.VALUE_HAS_NO_LOWER_BOUND, 
                        ReportParameterValueOptionEnum.VALUE_HAS_NO_UPPER_BOUND };
              parameter.CurrentValues = dPromptValues;
              break;
   case "As of Date":
             dPromptValues = new PromptValue[1];
             dPromptValues[0] = new PromptValue { Data = "2010-10-27" };
             dPromptValues[0].Options = new ReportParameterValueOptionEnum[] { 
                   ReportParameterValueOptionEnum.VALUE_HAS_NO_LOWER_BOUND, 
                   ReportParameterValueOptionEnum.VALUE_HAS_NO_UPPER_BOUND };
             parameter.CurrentValues = dPromptValues;
             break;
    }
}
}
InfoObject schedObject = bipService.Schedule(boInfoObjects).InfoObject[0];
string runningCuid = schedObject.NewJobID;
string query = "query://{Select * From CI_INFOOBJECTS Where SI_CUID = '" + runningCuid + "' }";
ResponseHolder boResponseHolder = bipService.Get(query, null);

SchedulingInfo schedulingInfo = null;
            if (boResponseHolder.InfoObjects.InfoObject[0] is CrystalReport)
            {
                CrystalReport reportRunning = (CrystalReport)boResponseHolder.InfoObjects.InfoObject[0];
                if (reportRunning.SchedulingInfo != null)
                {
                    schedulingInfo = reportRunning.SchedulingInfo;                    

                    while (schedulingInfo.Status != ScheduleStatusEnum.COMPLETE
                        && schedulingInfo.Status != ScheduleStatusEnum.FAILURE)
                    {
                        boResponseHolder = bipService.Get(query, null);
                        reportRunning = (CrystalReport)boResponseHolder.InfoObjects.InfoObject[0];
                        schedulingInfo = reportRunning.SchedulingInfo;
                    }
                }
            }
if (schedulingInfo==null || schedulingInfo.Status == ScheduleStatusEnum.FAILURE)
                throw new Exception(schedulingInfo != null?schedulingInfo.ErrorMessage:"report was not scheduled");

using (FileStream stream = new FileStream("C:\\ReportExcelDataOnly.xls", FileMode.Create))
{
    using (BinaryWriter writer = new BinaryWriter(stream))
      {
         string downloadFileId;
         int startIndex = 0;
         DownloadStatus dlStatus=null;
         do
         {
             downloadFileId = bipService.StartSingleDownload(runningCuid, 0);
             dlStatus = bipService.DownloadFile(downloadFileId, dlStatus!=null?dlStatus.NextReferencePosition:0);
             writer.Write(dlStatus.BinaryData, 0, dlStatus.BinaryData.Length);
         } while (dlStatus.EndOfFile != true);
                                        
         bipService.FinishDownload(downloadFileId);        
         writer.Close();
    }
}

When I schedule report with parameters iit fails with error "Information is needed before this report can be processed.".

When I check web client I can see that scheduled instance does not have parameters that code added.

What I noticed in code samples posted in forum people are using

CurrentValues oCurrentValues = new CurrentValues();
    BusinessObjects.DSWS.BIPlatform.Desktop.PromptValue[] oPromptValue = new BusinessObjects.DSWS.BIPlatform.Desktop.PromptValue[1];
    oPromptValue[0] = new BusinessObjects.DSWS.BIPlatform.Desktop.PromptValue();
    oPromptValue[0].Data = "Test";
    oCurrentValues.CurrentValue = oPromptValue;
    repParams<i>.CurrentValues = oCurrentValues;

I can not find CurrentValues class in proxy classes created by Visual Studio based on WSDL of Web services

Any ideas will be really appreciated

Edited by: Robert Parker on Nov 15, 2010 7:39 PM

Edited by: Robert Parker on Nov 15, 2010 7:48 PM

[Former Member · Accepted solution]

I tried to do comparison (using query builder query: Select * From CI_INFOOBJECTS Where SI_NAME like '" + strReportName + "%')

between instances I schedule manually via web client and instances my code schedules.

It shows that in both cases parameters are there, it seems the only difference is current values of a parameter

that comes from code does not have SI_NUM_VALUES

Any ideas what should be set in order to have it?