Converting VBA code to Java with equivalent APIs

[Former Member]

Hi,

I have the following code that is in VBA that I would like to convert to Java with equivalent APIs.  What I mean is I want the program in Java to behave the same as that of the program in VBA code.  What it does is:

1) Open a document.

2) Add a filter variable - this is for sectioning.

3) Add a complex filter - this is to pull the content that belongs to the filtering (i.e employee Id).

4) Save the content to a file based on the filtering value and format.

I have been searching for in this forum to see any APIs that can do the same in Java, but I could not find anything.  Currently the VBA code processes Deski doucment and I want to do the same and Webi in Java.  Please help.  Thanks!  Code snippet in VBA enclosed below:

    Set myFilterVar = mydoc.DocumentVariables(in_filter_var)

    Set myFilterVarValueName = mydoc.DocumentVariables.Add("Value", "Filter_Value")

    If myFilterVar.ValueCount > 0 Then

        intNumChoices = UBound(myFilterVar.Values(boUniqueValues))

        'collect the number of choices in a variant variable

        myFilterChoices = myFilterVar.Values(boUniqueValues)

        For i = 1 To intNumChoices

   'Get the variable value

   strNextValue = Replace(myFilterChoices(i), "'", "")

   For j = 1 To TabCount

    Set myrpt = mydoc.Reports(j)

    'build filter

    If IsNumeric(myFilterChoices(i)) Then

     myrpt.AddComplexFilter myFilterVar, "=<" & myFilterVar.Name & "> = ToNumber(<" & myFilterVarValueName.Name & ">)"

    Else

     myrpt.AddComplexFilter myFilterVar, "=<" & myFilterVar.Name & "> = " & """" & strNextValue & """"

    End If

    myFilterVarValueName.Formula = strNextValue

    'recompute the report

    myrpt.ForceCompute

   Next j

   'now export to desired format, using the filter value as part of the name

   If g_report_file_type_id = pdf Then

    mydoc.SaveAs (lDir & mydoc.Name & "-" & strNextValue & ".pdf")

   ElseIf g_report_file_type_id = xls Then

    mydoc.SaveAs (lDir & mydoc.Name & "-" & strNextValue & ".xls")

   ElseIf g_report_file_type_id = both Then

    mydoc.SaveAs (lDir & mydoc.Name & "-" & strNextValue & ".pdf")

    mydoc.SaveAs (lDir & mydoc.Name & "-" & strNextValue & ".xls")

   End If       

        Next i

    End If

[Former Member · Accepted solution]

Hi Raghunath,

I believe you are using XI 3.1 Versions.

You would want to use Report Engine Java SDK in case you want to dealt with webi reports.

Look at the Developers guide and API referrence guides for better understanding

http://help.sap.com/businessobject/product_guides/boexir31/en/resdk_java_dg_12_en.zip

http://help.sap.com/businessobject/product_guides/boexir31/en/resdk_java_apiRef_12_en.zip

Object Model Diagram available at

http://help.sap.com/businessobject/product_guides/boexir31/en/resdk_java_omd_12.zip

-Prithvi

[Former Member · Accepted solution]

Hi Prithvi,

I am not able to download those zip files from behind a firewall.  Do you by any chance know the equivalent of APIs in Java or perhaps how to achieve the same functionality using SDK in Java? Thanks!

[Former Member · Accepted solution]

I cannot think of a sample of which achieve the exact requirement you have.

However, please refer to SAP Note: 1447169 - How to add a report filter to a Web Intelligence document using the Report Engine Java SDK , which can give you a better idea about how to create filters in a webi report using Report Engine SDKs.

You would need to have the developers guide and API referrence to understand the Report Engine SDKs better.

It would be best if you ask your firewall team to raise an exception for this download, as the list of jars, The API being used and the interfaces being used in above sample code is mentioned in the guides.

Thanks,

Prithvi

[Former Member · Accepted solution]

Basically what I am looking to do is I want to section the report base on the recipients doing it in Java.  The VBA code does that and I do not know of the equivalent APIs to convert it over.  I am planning on migrating it to 4.0 where the Deski and VBA are dropped.  I want to keep the same functionality moving to Java.

[Former Member · Accepted solution]

Do you have a link to this note  SAP Note: 1447169? I cannot find it on sap website.

[Former Member · Accepted solution]

Hello Raghunath,

As you mentioned you would be migrating to BI 4.0, I would like to bring to your notice that a lot of features have been removed from ReportEngine SDKs from BI 4.x onwards.

The SAP Note provided was for XI 3.1 which will not be applicable to BI 4.x.

You would have to move towards the Webi Restful web services sdks as it is the future and is as an replacement for ReportEgine SDKs. You can choose any language to develop your app as it is platform independent.

You can find the documentation at

http://help.sap.com/bobip40?current=bobi#section7

Refer to 'SAP Crystal Reports RESTful Web Service Developer Guide' as per your product version.

Also in case of any queries regarding the Restful webservices you can post your queries in the below forum

http://scn.sap.com/community/restful-sdk

Refer to the blogs available at below link to understand the RestFull services better.

http://scn.sap.com/community/restful-sdk/content#filterID=contentstatus%5Bpublished%5D~objecttype~ob...

-Prithvi

[Former Member · Accepted solution]

Hi Prithvi - we plan on moving to BI 4.0; however, before we do so, we need to be able to prototype something that can still have the same functionality.  Our current functionality in BI XI 3.1 is we have Macro code that refreshes a report saves the whole report to a xls or pdf file, and then section that same report for each user.  Basically we have a requirement where one whole report goes to a team lead/manager, and each section of the report goes to the respective employee.  Macro seems to fit that in current environment for us.  So, we want to see if there is the same in BI 4.0 in Java side that we can use.  We have been looking at Publication as well, but Publication can do section for each employee only and not saving the whole report for the team lead/manager.  Aside from that, we have used external datasource to feed information to Macro to refresh the reports, so moving to Publication we will lose control of updating our external datasource.  Let's say for example, we have a report xyz where it is time to tell Macro to refresh, once Macro refreshes, it knows how to section and save the reports, and then saves the status in the datasource that the xyz report is done refreshing.  This is what I am looking for to migrate Macro to Java with the same functionality.  Please help if you know of anything or have some sample code to use.  Thanks.

[Former Member · Accepted solution]

Hi Raghunath,

This part of your requirement "Our current functionality in BI XI 3.1 is we have Macro code that refreshes a report saves the whole report to a xls or pdf file" can be achieved with Report Engine java SDKs in BI 4.x versions, However the sectioning part is not possible as those features have been deprecated from the Report Engine Java SDKs.

Please refer to the samples available at below link for refreshing and saving a report as pdf/excel through Report Engine java sdks.

http://www.sdn.sap.com/irj/boc/index?rid=/webcontent/uuid/30e2b6d2-2784-2b10-59b6-95b2d8c50a02

The options you are looking for can only be achieved from REST SDKs for webi in 4.x versions. Reason being SAP has removed most of the features from the Report Engine java SDKs in BI 4.x versions and are focussing on developing towards REST SDKs for webi.

And it is best to move forward to REST as it is the future roadmap SDKs as far as SAP is concerned.

Hope I am clear in my explanation.

Thanks,

Prithvi

[Former Member · Accepted solution]

Hi Prithvi,

For the refreshing and saving of the whole report, I can achieve that.  But I am not able to section the report, you also agree on this.  Based on your suggestion to use REST SDK, could you point me to examples that can achieve that?  Thanks!

[Former Member · Accepted solution]

Hi Raghunath,

Unfortunately, there is no sample available on SCN to the specific requirement you need of sectioning a report.

I would suggest to go through the Webi Restfull webservices guide available at http://help.sap.com/bobip40?current=bobi#section7

You would be interested in the "Report Element" Section of the guides.

Also Raise your queries regarding REST in the below forum which is specifically for RESTFul Webservices SDKs.

http://scn.sap.com/community/restful-sdk

-Prithvi

[DellSC · Accepted solution]

Be aware, though, that the RESTful SDK only works with Crystal for Enterprise reports - it doesn't work with reports that have been generated in Crystal 2011 or 2013.

-Dell

[Former Member]

Hi,

I found this code that is almost able to meet my needs; however, one caveat is I do not know how to loop through each filter to pull out the section belongs to that filter and save to a file.  The filter does not seem to apply individually; I would get mixed up results and some cases no content at all.  I thought I had a solution but not yet.  Please help.  Thanks!

String filterObjectName = "End Key";
String filterValues = "342293745;573500167;630694746";

FilterContainer filterContainer = reportContainer.createFilter(LogicalOperator.OR);//.AND);
ReportDictionary reportDictionary = widoc.getDictionary();

ReportExpression reportExpression = reportDictionary.getChildByName(filterObjectName);
// Create a filter object on the ReportExpression
FilterObject filterObject = filterContainer.createFilterObject(reportExpression);
// Creating an IN LIST filter condition
FilterCondition filterCondition = filterObject.createFilterCondition(Operator.IN_LIST);
// Adding the values to the filter condition
String[] valueArray = filterValues.split(";");
for (int i = 0; i < valueArray.length; i++) {
filterCondition.createFilterConditionConstant(valueArray[i]);
// Apply the changes
widoc.applyFormat();
/************************** DISPLAY THE FIRST REPORT **************************/
// Refresh the Web Intelligence document
widoc.refresh();
// Retrieve the first report
Reports reports = widoc.getReports();
System.out.println(reports.getCount());
Report report = reports.getItem(0); //widoc.getReports().getItem(0);

BinaryView binaryDoc = (BinaryView) report.getView(OutputFormatType.XLS); //OutputFormatType.PDF);    
String fileName = "C:\\BoJava\\" + valueArray[i] + ".xls"; //".pdf";
File file = new File(fileName );
file.setWritable(true);
FileOutputStream fileOuputStream = new FileOutputStream(file);
fileOuputStream.write(binaryDoc.getContent());
fileOuputStream.close();
}