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