BOE: Query Builder API to pull Printer Info

In this BOE post I'm pulling Printer information, instead of User information. The Printer status is a sub-property, so it's slightly more complicated pulling this information using a query.


This is the query I used to pull the printer status for each report.

SELECT SI_NAME,SI_PROCESSINFO.SI_PRINTER_INFO 
From CI_INFOOBJECTS 
WHERE SI_PROGID='CrystalEnterprise.Report'

Which results in an HTML page like this for every report.

I need a .csv file that returns the Report name (in this case, 'Life Insurance Enrollment'), the printer enable status ('false'), and the printer name if any ('Adobe PDF').

I use my function that takes my query above as a string. Then creates and returns a IInfoObjects using the query.

public IInfoObjects getUserReport(String myquery, String token) {         IInfoStore iStore = getInfoStore(token);  //Creates session using my login token         IInfoObjects reports = iStore.query(myquery);                        return reports; }

In my controller, I call the getUserReport function to get the object with my query data.

IInfoObjects reportinfo = BoeService.getUserReport(query,tok);         recordcount = reportinfo.getResultSize();    //grab the number of records

I have an array of IInfoObject (not to be confused with IInfoObjects) for each row of data from the query.

IInfoObject[] report = new IInfoObject[recordcount];                for ( x = 0; x < recordcount; x = x+1 ){                    report[x] = (IInfoObject) reportinfo.get(x);                    }                    String outdata;              //.csv file for output         File uf = new File(u.exportfile)         String newfile=null         newfile = u.exportfile                        BufferedWriter out = new BufferedWriter(new FileWriter(newfile))         //Create Header         out.write("status,reportname,printername\n");          //Pulling data for each row   for ( y = 0; y < recordcount; y = y+1 ){                /* The SI_PRINTER_INFO is stored under "Processing Info". So use the function         getProcessingInfo() to point to this data   */             IProcessingInfo processinfo = report[y].getProcessingInfo();                  /* Retrieve all the data under Processing Info   */             IProperties processinfo_detail= processinfo.properties();        /* Just retrieve the data called SI_PRINTER_INFO and using a new IProperties*/             IProperties printerdetail = processinfo_detail.getProperties("SI_PRINTER_INFO");           /*Now you can retrieve the subdata under SI_PRINTER_INFO using IProperty*/             IProperty prop1 = printerdetail .getProperty("SI_ENABLED"); //printer enable setting             IProperty prop2 = printerdetail .getProperty("SI_NAME"); //printer name /* Create a .csv row of Printer status, Report Name, and Printer name */ outdata = prop1.getValue().toString() + ',' + report[y].properties().getProperty(CePropertyID.SI_NAME).getValue() + ',' + prop2.getValue().toString();             outdata = outdata + '\n';   //carriage return between rows             out.write(outdata);  //write to file         }                 out.close()  //close writer

This is probably not the easiest way, but it's the first way I got it to work. Hopefully this will help you get started.

Misc Queries:

List of all possible relations:
SELECT SI_NAME FROM CI_SYSTEMOBJECTS WHERE si_relation_table_name='RELATIONS'
 
All folders
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder'

All groups
Select * from CI_SYSTEMOBJECTS Where SI_KIND='UserGroup'

All universes
Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND ='Universe'

All reports
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report'
And SI_INSTANCE=0

(RELATIONS) List all Universes used by the report called 'Financial Summary Page'
Select   SI_ID, SI_NAME, SI_OWNER From CI_APPOBJECTS Where
CHILDREN("SI_NAME='Webi-Universe' ","SI_NAME='Financial Summary Page'")

(RELATIONS) List all reports that have a Universe called 'Activity'
 SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
 Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Activity'")

Other RELATIONS examples:

User groups
Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS
Where PARENTS("SI_NAME='UserGroup-User'", "SI_NAME='Admin'")

Report Shortcuts
SELECT SI_NAME,SI_PROCESSINFO.SI_RECORD_FORMULA
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE parents("si_name = 'InfoObject-Shortcuts'","SI_NAME = 'Job Entry Report'")


Check out these sites for more help:
BOE SDK Query Language Reference
Interesting Post about ProcessInfo
Another good post by Ted Ueda
InfoStore Query Builder program

BOE: Using Query Builder API to get User Info

As I've been learning Grails, I've also been reading up on the BOE API. The Business Objects query builder returns results in HTML format, which isn't friendly data. So I've been using Grails to run BOE queries and returning the results as .csv files.

The first query I ran and output wasn't too complicated. The second query was a bit more complicated.
These are some notes on what I did.

Every quarter or so, we need to verify that the BOE users are still active and no one is missing. The query builder allows you to pull the users with this query:

SELECT SI_NAME, SI_USERFULLNAME,SI_EMAIL_ADDRESS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User'

Which results in an HTML page like this for every user.

I wanted to pull this info into a .csv file.  (Email, Userfullname, Name). With a user on each line.

I have some code in Grails that connects to BOE using a token to login. Then I have a function that takes my query above as a string. Then creates and returns a IInfoObjects using the query.

public IInfoObjects getUserReport(String myquery, String token) {         IInfoStore iStore = getInfoStore(token);  //Creates session using my login token         IInfoObjects reports = iStore.query(myquery);                        return reports; }

In my controller, I call the getUserReport function to get the object with my query data.

IInfoObjects reportinfo = BoeService.getUserReport(query,tok);         recordcount = reportinfo.getResultSize();    //grab the number of records

I have an array of IInfoObject (not to be confused with IInfoObjects) for each row of data from the query.

IInfoObject[] report = new IInfoObject[recordcount];                 for ( x = 0; x < recordcount; x = x+1 ){                     report[x] = (IInfoObject) reportinfo.get(x);   //Set new object with a row of data                     }              //Strings to hold data for each column         String user;         String email;         String fullname;         String line;          //Create .csv file to export data to               File uf = new File(u.exportfile)       //original file         String newfile=null         newfile = u.exportfile                         BufferedWriter out = new BufferedWriter(new FileWriter(newfile))                    //Each row in object gets written to a string for ( y = 0; y < recordcount; y = y+1 )    {            user = report[y].properties().getProperty(CePropertyID.SI_NAME).getValue();    email = report[y].properties().getProperty(CePropertyID.SI_EMAIL_ADDRESS).getValue();    fullname = report[y].properties().getProperty(CePropertyID.SI_USERFULLNAME).getValue();                     //Set NULL variables to something else           if ( email == '') { email = 'NoEmail'}           if ( fullname == '') { fullname = 'NoFullName'}           if ( user == '') { user = 'NoUser'}           //Write data to the .csv file           line = email + ',' + fullname + ',' + user + '\n'           out.write(line); }        out.close()  //close writer