Search This Blog

2011-08-19

Crystal Report - Retrieve report name from database

Product: Crystal Report XI

BusinessObject stores its table as binary data type. However, they provided a web interface called Query Builder which can used to query the API-base virtual table

URL: http://crystalreport_cmc_web_server/AdminTools

Login as the same ID as Central Management Console

To display Crystal Report schedule job going to be executed, enter following query into the text box


SELECT SI_NAME, SI_NEXTRUNTIME, SI_SCHEDULEINFO.SI_STARTTIME, SI_PROCESSINFO.SI_RECORD_FORMULA
FROM CI_INFOOBJECTS WHERE SI_RECURRING=1

Most of the forums only show programming API to access property of above virtual table. In my example, I am showing that even using SQL, it is possible to extract the next level object. The limitation is still joining this virtual table where API will be more convenient

Use following query to map file names stored in frs://Input to report name. Please be aware of any duplicate name


SELECT SI_ID, SI_CUID, SI_KIND, SI_NAME, SI_FILES FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_NAME like '%Agent Performance - Daily Common%'

Other useful queries

Get Full Client Reports 
SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( 'webi' , 'CrystalReport') 

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

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

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

Get All Users 
SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER' 

get all personal categories 
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='PersonalCategory'

Get all groups 
Select * from CI_SYSTEMOBJECTS Where SI_KIND='UserGroup' 

get all folders 
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder' 

get all categories 
select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='Category' 

Tag: reverse lookup CMS report name, BO AdminTool tips

No comments:

Blog Archive