Need macro for XI 3.1 to retrieve info

[former_member185591]

Hello Folks,

We have business requirement to get following kind of data in one excel sheet for SAP BOE Xi 3.1. Please help.

This info is required for our analysis so that we can filter table names(one or multiple), last run etc and make an report optimization exercise. Top level Folder will be 'User Folders' - for Personal folders or Public Folder.

Please let me know if this is possible.

Regards,

Lokesh

XI31infosheet.txt.zip

[DellSC]

It's not difficult to get everything except the tables.  But there is no "macro" as such unless you want to write one in Excel using the .NET SDK.  However, I'm not sure the .NET SDK has everything you need to get the data you're looking for.  You may have to write a Java program for this or look for a third-party tool that will get you this information.

To get the tables, for Crystal you have to use a ReportDocument object (you can load an InfoObject into a ReportDocument in 3.1.)  For Webi reports you need to use the ReportEngine SDK and I'm not entirely sure that the .NET version has what you need for that, but the Java SDK does.

Also, be aware that ReportEngine is deprecated in version 4.x and you would have to use the RESTful Web Services to get the info for Webi reports after you upgrade.

-Dell

[former_member185591]

Hi Dell,

Thanks for reply. I can work on tables list later but need remaining information. I found macro in following link:

BOXI Repository Documentor

If you check Documents tab, it has many things listed but Begin Date and Begin Time is not Last Run time of reports(I verified its not Last run by checking some reports manually). I can select type of reports without instances to fetch from BO using this macro and I did it successfully but need  LAST_RUN_TIME.

This is our urgent requirement. Kindly help.

Regards,

Lokesh

[DellSC]

Last Run can be a challenge - basically you need to query for all of the instances, sort by

SI_UPDATE_TS descending, and get the UpdateTimeStamp for the first record in the list to get the completion of the most recent run.  Here's the CMS Query that I use for this:

Select top 10000 SI_ID, SI_UPDATE_TS, SI_OWNER from CI_INFOOBJECTS where SI_PARENTID = <id of the report template> and SI_INSTANCE = 1 and SI_SCHEDULE_STATUS = 1 order by SI_UPDATE_TS desc

This gets all of the successfully completed instances of a report and sorts them descending on the update timestamp.  The first record in the list will be the most recent run.

So, you'll need to update the VBA code in the file that you downloaded to pull this information.  I don't really work in VBA (I do C# and Java), so I can't give you the exact code for doing that.

-Dell

[former_member185591]

Hi Dell,

I too, don't work on VBA so unable to do it (though I understand what to do here). If you can provide Java code for it, it can work.

Would appreciate if anyone works on VBA can help here.

Regards,

Lokesh

[DellSC]

Here's the Java code that I use for this - this I part of a class that contains information about reports.  The problem you're going to run into with this is that I have "helper" classes that I use for doing things like querying the InfoStore and converting dates (dates in BO are stored as UTC so you have to convert them to your local timezone to get the local time...)

private void loadLastRunInfo(Integer parentId, QueryHelper qh, Logger log){
    Date tmpDate = null;
    String tmpOwner = "";
    String qry = "Select top 10000 SI_ID, SI_UPDATE_TS, SI_OWNER from CI_INFOOBJECTS where SI_PARENTID = %d and SI_INSTANCE = 1 and SI_SCHEDULE_STATUS = 1 order by SI_UPDATE_TS desc";
    try {
        IInfoObjects iobjs = qh.executeRawQuery(String.format(qry, parentId));
        IInfoObject iobj = (IInfoObject) iobjs.get(1);
        tmpDate = iobj.getUpdateTimeStamp();
        tmpOwner = PropertyHelper.getStringProp(iobj.properties(), "SI_OWNER", log);
    } catch (SDKException e) {
        log.error(String.format("Unable to get last run date for %d-%s.  Err=%s", parentId, this.title, e.getDetailMessage()));
    }

    if (tmpDate != null){
        this.lastRun = DateHelper.getLocalDateFromUTC(tmpDate);
        this.lastRunOwner = tmpOwner;
    }
}

-Dell