How to read History Status values of a Report

[Former Member]

Hello Friends,

Can someone let me know how to read history status values like (Title, Document Type, Status, Destination, Owner, Creation Time, Start Time, End Time, Duration, Server Used, PID, Parent Object Path, Remote Instances in Federated Clsuster, Expiry, Formats, Parameter)?

I am trying to read history status values (existing instances) and export them to an excel.

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Rakhy,

It would be best to start with the developers guide and API referrence guides available at http://help.sap.com for better understanding the businessobjects sdks and implementing the requirement.

Below are the links to guides
XI 3.1
http://help.sap.com/boe31#section6
Refer to 'BusinessObjects Enterprise Java SDK Developer Guide' and 'BusinessObjects Enterprise Java API Reference'

BI 4.0
http://help.sap.com/bobip40#section7
Refer to 'Business Intelligence platform Java SDK Developer Guide' and 'Business Intelligence platform Java API Reference (Javadocs)'

BI 4.1

http://help.sap.com/bobip41#section7
Refer to 'Business Intelligence platform Java SDK Developer Guide 4.1' and 'Business Intelligence platform Java API Reference (Javadocs) 4.1'

As far as your requirement goes, you can get this information from the cms repository.

So the steps you would follow would be
1. Login to Enterprise System
2. Get the service of Infostore
3. Query the Infostore and retrieve the collection of IInfoObjects
An example for the query would be
SELECT * FROM CI_INFOOBJECTS WHERE SI_INSTANCE=1 AND SI_KIND='CrystalReport'

The above query retrieves all the properties of  1000(by default) crystal report instances. The amount of objects returned would be based on the user security with which you are logged in.

4. Once you have all the properties, you can use the IProperties class of BusinessObjects SDKs API and get the values for the specific property you need or you can utilize the available methods from the IInfoObject class.

5. You would need to use POI(Excel)jars to export your results to Excel.

Refer to SAP Note:1748041 - How to retrieve complete scheduling information for all report instances (Crystal, Webi, Deski) using Enterprise SDK for better understanding.

-Prithvi

[Former Member · Accepted solution]

Hi Prithviraj,

Thanks for the detail info.

I am able to read promptID and there values using below code and export them to excel using POI jars.

Prompts prompts = doc.getPrompts();

//for loop{

Prompt prompt = prompts.getItem(i);

String promptId = prompt.getID();

String[] promptValue = prompt.getPreviousValues();

//for loop end }

Similarly I am trying to read History status ID and there values. As you suggested I will try to do the same using IProperties class and let you the the output.

Also I am facing

An internal error occurred while calling 'openDocument' API. (Error: ERR_WIS_30270) issue while opening an crystal report.

Error at

DocumentInstance doc = reportEngine.openDocument(infoObject.getID());

It is working fine for Webi Reports (.wid extenson) but not for Crystal Reports(.rpt extension).

Report Engine service code is

reportEngines = (ReportEngines) enterpriseSession.getService("ReportEngines");

reportEngine = reportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);

Can you please guide me in the right direction? How can I open Crystal reports?

Regards,

Rakhy.

[DellSC · Accepted solution]

reportEngine will only work with Webi reports - it doesn't work with Crystal.  There is an IReport interface that will get you to the properties of a Crystal report so that you can get to the parameter names if you need them.  Otherwise just use the OpenDocument URL

http://<server>:8080/BOE/OpenDocument/opendoc/openDocument.jsp

You can find OpenDocument documentation at http://help.sap.com/bobip.  It contains information about how to set parameters and the logon token.

-Dell

[Former Member · Accepted solution]

Hi Rakhy,

As Dell rightly mentioned, you would not be able to open a crystal report using Report Engine SDKs(Specifically foe WebIntelligence Reporting).

The line
DocumentInstance doc = reportEngine.openDocument(infoObject.getID());
is specifically to open a webi report.

If you want to open a crystal report there are two mechanisms
1. Use Open Document/Crystal Report Url reporting Technique.
2. You can use either RAS/Processing Server Services and Use the BOE/RAS SDKs to open the report.You would additionaly require the Viewers SDK to invoke the crystal report viewer component.

Note: If you have a requirement to simply view a crystal report, use Processing Server Services(PSReportService). We do not recommend to use RAS(Report Application Server) unless you have a requirement to do runtime modification for a report.

Example Snippet would be for using PSReportService

==============================
IReportSourceFactory reportSourceFactory = (IReportSourceFactory) enterpriseSession.getService("", "PSReportFactory");
 
  IInfoStore infostore = (IInfoStore) enterpriseSession.getService("InfoStore");
  IInfoObjects reports = infostore.query("Select SI_ID, SI_CUID From CI_INFOOBJECTS Where SI_NAME='" + reportName + "' And SI_INSTANCE=0");
  IInfoObject report = (IInfoObject)reports.get(0);    
      
  IReportSource reportSource = reportSourceFactory.openReportSource(report.getID(), java.util.Locale.ENGLISH);
=====================================

Now you can pass the reportSource to the viewer to view the report or utilize the IReport interface of BOE SDKs to do further implementations. If you want to retrieve the parameters of a crystal report, you can use IReportParameter interface and this can be achieved with out opening a crystal report.

Refer to the Viewers java sdk api referrence guide and developers guide available at the links shared in earlier post.

For RAS Samples refer to the below link
http://wiki.scn.sap.com/wiki/display/BOBJ/Java+RAS+SDK+Samples

-Prithvi

[Former Member · Accepted solution]

Thanks Dell, for the information, I will work on suggested solutions next week and let you know the output.

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Prithvi,

Thanks again for the detail solution/Information, currently I am working on a prod issue, I shall work on suggested solution next week and let you the output.

Thanks again, you guys are really helpful.

Regards,

Rakhy.

[Former Member · Accepted solution]

HI Prithvi/Dell,

I am trying to read data from a webi report, is it possible?

Basically I have a trigger report which runs every day and get the latest RACN number from the database. I am trying to get that RACN number from trigger report using BOBJ SDK.

Please let me know how can I achieve this.

I posted as a new discussion @ 3579655 thread.

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Prithvi/Dell,

Thanks for the suggestions, I am able to open crystal reports using IReportSourceFactory!

Now I am trying to get prompt values of the crystal reports. Please guide me to the right direction.

I am able get prompt values for webi report as below.

DocumentInstance widoc = wiRepEngine.openDocument(infoObject.getID());

  // Refresh the document

  widoc.refresh();

  // Set prompts

  Prompts prompts = widoc.getPrompts();

  for (int i = 0; i < prompts.getCount(); i++) {

  Prompt prompt = prompts.getItem(i);

  System.out.println(prompt.getID());

  String answer = answers.get(prompt.getID());

  System.out.println("answer = " + answer);

  String[] strArray = new String[] { answer };

  if (answer != null) {

  prompt.enterValues(strArray);

  for (String value : strArray) {

  System.out.println("  " + value);

  }

  }

  }

  widoc.setPrompts();

Similarly I am trying to get prompt values for crystal reports.

Regards,

Rakhy.

[DellSC · Accepted solution]

I don't use IReportSourceFactory.  I get an IInfoObject and then cast it to an IReport to get the parameter info.

Here's the code that I use to load the prompt information into a class that I then use to export information about reports.  This will even get the current values of the prompts.

private void loadCrystalPrompts(IInfoObject o, Logger log) throws SDKException{
    IReport rpt = (IReport) o;
    List<IReportParameter> rps = rpt.getReportParameters();
    for (IReportParameter rp : rps){
        crystalPrompts.add(new CrystalPrompt(rp, log));
    }
}
------------------------------------------------------------------------------
public class CrystalPrompt {
    private String name = "";
    private String dataType = "";
    private String promptText = "";
    private String reportName = "";
    private boolean inUse = false;
    private boolean isDynamic = false;
    private boolean isMulti = false;
    private boolean isOptional = false;
    private boolean isNullEnabled = false;
    private boolean isRange = false;
    private ArrayList<String> values = null;
    private ArrayList<String> descripts = null;

    public CrystalPrompt(IProperties param, Logger log){
        name = PropertyHelper.getStringProp(param, "SI_NAME", log);
        dataType = getDataTypeFromProp(PropertyHelper.getIntProp(param, "SI_PROMPT_TYPE", log));
        reportName = PropertyHelper.getStringProp(param, "SI_SUBREPORT", log);
        int iProp = PropertyHelper.getIntProp(param, "SI_OPTIONS", log);
        isMulti = (iProp == 12);
        isRange = (iProp == 128);
        IProperties vals = PropertyHelper.getIProps(param, "SI_CURRENT_VALUES", log);
        if (vals != null) {
            values = new ArrayList<String>();
            loadValues(vals, log);
        }
    }

    private String getDataTypeFromParam(Integer type){
        String result = "";
        switch(type){
            case 0:
                    result = "Number";
                    break;
            case 1:
                    result = "Currency";
                    break;
            case 2:
                    result = "Boolean";
                    break;
            case 3:
                    result = "Date";
                    break;
            case 4:
                    result = "Time";
                    break;
            case 5:
                    result = "DateTime";
                    break;
            case 6:
                    result = "String";
                    break;
            case 7:
                    result = "Member";
                    break;
        }
        return result;
    }

    private String getDataTypeFromProp(Integer type){
        String result = "";
        switch (type) {
            case 0:
                    result = "Number";
                    break;
            case 1:
                    result = "Currency";
                    break;
            case 2:
                    result = "Boolean";
                    break;
            case 3:
                    result = "Date";
                    break;
            case 4:
                    result = "String";
                    break;
            case 6:
                    result = "Time";
                    break;
            case 7:
                    result = "DateTime";
                    break;
        }
        return result;
    }

    private void loadValues(IProperties vals, Logger log){
        int cnt = PropertyHelper.getIntProp(vals, "SI_NUM_VALUES", log);
        for (Integer i = 1; i <= cnt; i++){
            IProperties val = PropertyHelper.getIProps(vals, "SI_VALUE" + i.toString(), log);
            if (val != null){
                IProperties rangeProp = PropertyHelper.getIProps(val, "SI_MIN", log);
                String descr = "";
                if (rangeProp == null){
                    //this isn't a range property, set the single value
                    values.add(PropertyHelper.getStringProp(val, "SI_DATA", log));
                    descr = PropertyHelper.getStringProp(val, "SI_DESCRIPTION", log);
                    if (!descr.isEmpty()){
                      descripts.add(descr);
                    }
                } else {
                    values.add(PropertyHelper.getStringProp(rangeProp, "SI_DATA", log));
                    rangeProp = PropertyHelper.getIProps(val, "SI_MAX", log);
                    if (rangeProp != null){
                       values.add(PropertyHelper.getStringProp(rangeProp, "SI_DATA", log));
                }
            }
        }
    }
    ...<getters for various properties>...
}

-Dell

[Former Member · Accepted solution]

Thanks Dell for your code, I am able to read crystal param values using IReport.

I really appreciate it.

When you have time, can you please check my other post @ 15179510.

That is regarding read data from a webi report.

Regards,

Rakhy.

[DellSC · Accepted solution]

I don't know how to read webi data from a report, so that's not something I can assist with.  Sorry!

-Dell

[Former Member · Accepted solution]

np Dell, I will try and if it works I will share it here.

Regards,

Rakhy,

[Former Member · Accepted solution]

Hi Dell,

Are you able to view crystal report?

I am able to view Webi reports using below code.

DocumentInstance widoc = wiRepEngine.openDocument(infoObject .getID());

BinaryView binaryView = (BinaryView)widoc.getView(OutputFormatType.PDF);

How it can be done for Crystal reports?

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Rakhy,

Do you need to view crystal reports in the viewer(Crystal Report Viewer) or you need to directly export the report to PDF/Excel?

You can look for RAS SDK samples at below link.

http://wiki.scn.sap.com/wiki/display/BOBJ/Java+RAS+SDK+Samples

Look specifically for Managed samples as you have reports stored inside the enterprise repository.

You can find viewing samples as well.

However if you only have the requirement to view a crystal report, use the  IReportSourceFactory interface.

If you have an requirement to export a report in PDF for example, you can go for RAS SDK and the above link has a relavent sample for the same.

-Prithvi

[Former Member · Accepted solution]

Hi Prithvi,

I am trying to export the crystal report document to PDF.

For Webi Reports I am able to enter the prompts values and export to PDF as below.

DocumentInstance widoc = wiRepEngine.openDocument(infoObject .getID());

  widoc.refresh();

  // Set prompts

  Prompts prompts = widoc.getPrompts();

for (int i = 0; i < prompts.getCount(); i++) {

  Prompt prompt = prompts.getItem(i);

  String promptId = prompt.getID();

  System.out.println("prompt ID = "+prompt.getID());

  String answer = null;

  if(promptId.equalsIgnoreCase(programObjectConstants.ENTER_AS_RACN_VALUE) &&

  answers.get(prompt.getID()).equals(programObjectConstants.NNNN)){

     answer = pob.getRacnNumber();

  }else{

  answer = answers.get(prompt.getID());

  }

  String[] strArray = new String[] { answer };

  if (answer != null) {

  prompt.enterValues(strArray);

  for (String value : strArray) {

  System.out.println("  " + value);

  }

  }

  }

widoc.setPrompts();

BinaryView binaryView = (BinaryView) widoc .getView(OutputFormatType.PDF);

Where HashMap<String, String> answers have my prompt values.

Similarly I am trying to do it for crystal reports as below.

ReportClientDocument crdoc = reportAppFactory.openDocument(report, OpenReportOptions._openAsReadOnly , java.util.Locale.ENGLISH);

  Fields<IParameterField> parameterFields = crdoc.getDataDefController().getDataDefinition().getParameterFields();

  for(int i =0; i < parameterFields.size(); i++){

  IParameterField parameterField = (IParameterField)parameterFields.getField(i);

  System.out.println("parameterField = " + parameterField.getDescription());

  IParameterField newParameterField = (IParameterField) parameterField.clone(true);

  newParameterField.getCurrentValues().removeAllElements();

  ParameterValueRangeKind kind = parameterField.getValueRangeKind();

  if (kind == ParameterValueRangeKind.discrete)

  {

  String crystalParameterName = parameterField.getDescription();

  System.out.println("crystalParameterName = " + crystalParameterName);

  String answer = answers.get(crystalParameterName);

   String discreteValueText = answer;

   ParameterFieldDiscreteValue discreteValue = new ParameterFieldDiscreteValue();

   discreteValue.setValue(discreteValueText);

   newParameterField.getCurrentValues().add(discreteValue);

  }

  ParameterFieldController parameterFieldController =  crdoc.getDataDefController().getParameterFieldController();

  parameterFieldController.modify(parameterField, newParameterField);

  }

After modifying the Crystal prompts, I am not sure if I need to set prompt again? and how to export it to PDF.

I tried to export the crystal file using getPrintOutputController() method as below

BinaryView binaryView2 = (BinaryView) crdoc.getPrintOutputController().export(ReportExportFormat.PDF);

but its throwing an error.

Let me know if you know how to set prompts for Crystal reports and export them to a PDF.

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Rakhy,

There is an error here

BinaryView binaryView2 = (BinaryView) crdoc.getPrintOutputController().export(ReportExportFormat.PDF);

reportClientDocument.getPrintOutPutController().export(ReportExportFormat.PDF) returns an input stream and not a BinaryView.

You would need to look at the API referrence guide available at

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_rasjava_apiRef_en.zip

The guide is for BI 4.0 and aaplies to BI 4.1 version as well.

Refer to this sample for understanding how to export a report in PDF

http://scn.sap.com/docs/DOC-6312

Refer to this sample below to understand how to set the parameters.

http://scn.sap.com/docs/DOC-6311

http://scn.sap.com/docs/DOC-6313

Thanks,

Prithvi

[Former Member · Accepted solution]

Thanks Prithvi for your response, I am able to export the crystal report now!

Logic was to export the report in InputStream and convert it to Byte Array. It worked!

Thank you Dell!

Below is the code logic

InputStream byteInputStream = (InputStream)crdoc.getPrintOutputController().export(ReportExportFormat.PDF);

  byte[] bytes = IOUtils.toByteArray(byteInputStream);

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Prithvi and Dell,

I have one more question.

How to set multiple values to a prompt for crystal report in XI 4.1?

From the below code, I am getting new prompt values as discreteValueText and setting it to discreteValue.

It is working fine for single prompt value,

example : discreteValueText  = test;

but it is not working for multiple prompt values,

example : discreteValueText  = test, test1, test 2.

for (int i = 0; i < parameterFields.size(); i++) {

  IParameterField parameterField = (IParameterField) parameterFields

  .getField(i);

  IParameterField newParameterField = (IParameterField) parameterField

  .clone(true);

  newParameterField.getCurrentValues().removeAllElements();

  String discreteValueText = null;

  String[] newString = null;

  ParameterValueRangeKind kind = parameterField

  .getValueRangeKind();

  if (kind == ParameterValueRangeKind.discrete) {

  String crystalParameterName = parameterField.getDescription();

  discreteValueText = answers.get(crystalParameterName);

  ParameterFieldDiscreteValue discreteValue = new ParameterFieldDiscreteValue();

  discreteValue.setValue(discreteValueText);

  newParameterField.getCurrentValues().add(discreteValue);

  }

  ParameterFieldController parameterFieldController = crdoc

  .getDataDefController()

  .getParameterFieldController();

  parameterFieldController.modify(parameterField,

  newParameterField);

  }

Please let me know how to achieve this?

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Prithvi and Dell,

I am able to pass multiple values using below logic.

for (int j = 0; j < newString.length ; j++) {

  ParameterFieldDiscreteValue discreteValue1 = new ParameterFieldDiscreteValue();

  discreteValue1.setValue(newString[j]);

  newParameterField.getCurrentValues().add(discreteValue1);

  }

It worked!, thanks for your support!

Regards,

Rakhy.

[Former Member · Accepted solution]

Hi Prithvi and Dell,

When you get chance can you please have a look at my new post @

Regards,

Rakhy.