Managing Your Report Dependencies on the Universe, with custom Code

1. Managing Your Report Dependencies on the Universe, with custom Code Dell Stinnett, BOCP-BOE ZC Sterling

2. Agenda • Introduction • Universe and Connection Management Issues • In-memory Dataset • Load Data • View Data in Crystal • Conclusion • Q & A

3. Introduction • ZC Sterling • A leading provider of mortgage outsourcing services • Hazard insurance • Real estate tax servicing • Voluntary products • Customer care • BusinessObjects XI R2 • Migrated from Seagate Info 7.5 in 2006 • Custom programs • Named user licensing • Upgrading to 3.1 later this year

4. Report Management Issues • What’s affected by • Universe changes • Connection changes • Database changes • Why you need to know this • Analyze impact of changes • Know what to test after changes • Prevent “Broken” reports

5. In-Memory Dataset .NET solution uses five tables in an ADO.NET dataset

6. Load Data • “You can’t get there from here” • There’s no simple query that will get Connection, Universe, and Report info all together • Load a list of connections • Load a list of universes • Load all Webi report templates • Load Crystal report templates that use a universe.

7. Program Flow

8. Load Connection List • Get the name and ID of every Connection in the system string qry = "Select SI_ID, SI_NAME from CI_APPOBJECTS where " + "SI_Kind = 'MetaData.DataConnection'"; using (InfoObjects conns = _common.BOEInfoStore.Query(qry)) { dsData.Connection.AddConnectionRow(0, "*All"); for (int i = 1; i <= conns.Count; i++) { dsData.Connection.AddConnectionRow(conns[i].ID, conns[i].Title); } }

9. Load Universe List • Get the Name and ID of every universe in the system string qry = "Select SI_ID, SI_Name from CI_APPOBJECTS where " + "SI_Kind = 'Universe'"; using (InfoObjects unvs = _common.BOEInfoStore.Query(qry)) { if (unvs.Count > 0) { dsData.Universe.Clear(); //add the first row for the filter display dsData.Universe.AddUniverseRow(0, "*All"); for (int i = 1; i <= unvs.Count; i++) { dsData.Universe.AddUniverseRow(unvs[i].ID, unvs[i].Title); } } }

10. Load Webi Report Data string query = "Select SI_ID, SI_NAME, SI_PARENTID, SI_UNIVERSE," + "SI_OWNER From CI_INFOOBJECTS Where SI_KIND = 'Webi' and " + "SI_Instance = 0"; using (InfoObjects reports = _common.BOEInfoStore.Query(query)) { for (int i = 1; i <= reports.Count; i++) { dsData.Report.AddReportRow(reports[i].ID, reports[i].Title, _common.GetPathFromFolderID(reports[i].ParentID), "Webi", reports[i].Properties["SI_OWNER"].Value.ToString()); //Convert to Webi so that we can get to the properties we need. using (Webi rpt = (Webi)reports[i]) { for (int j = 1; j <= rpt.Universes.Count; j++) { dsData.ReportUniverses.AddReportUniversesRow( Convert.ToInt32(rpt.Universes[j].ToString()), rpt.ID); } } } }

11. Load Crystal Report Data string qry = "Select SI_ID, SI_NAME, SI_PROCESSINFO, SI_OWNER from CI_INFOOBJECTS where”+ “SI_KIND = 'CrystalReport' and "SI_INSTANCE = 0"; using (InfoObjects rpts = _common.BOEInfoStore.Query(qry)) { for (int i = 1; i <= rpts.Count; i++) { using (InfoObject rpt = rpts[i]) { owner = rpt.Properties["SI_OWNER"].Value.ToString(); for (int j = 1; j <= rpt.ProcessingInfo.Properties.Count; j++) { prop = rpt.ProcessingInfo.Properties[j]; if (prop.Name == "SI_UNIVERSE_INFO") { dsData.Report.AddReportRow(rpt.ID, rpt.Title, _common.GetPathFromFolderID(rpt.ParentID), "Crystal", owner); unvCount = prop.Properties.Count; for (int x = 2; x <= unvCount; x++) { unvCuid = prop.Properties[x].ToString(); addCrystalUniverse(rpt.ID, unvCuid); } } } } } }

12. Load Crystal Report Data (continued…) • The Universe in ProcessingInfo.Properties is a CUID private void addCrystalUniverse(int rptID, string cuid) { string query = "Select SI_ID from CI_APPOBJECTS where " + "SI_CUID = '" + cuid + "'"; using (InfoObjects unvs = _common.BOEInfoStore.Query(query)) { dsData.ReportUniverses.AddReportUniversesRow(unvs[1].ID, rptID); } }

13. Report Creation

14. View Report

15. View Report public DependencyCheckViewer(DependencyCheckData dsData, int selectedUniverse) { InitializeComponent(); rpt = new DependencyCheckReport(); //set the datasource rpt.SetDataSource(dsData); //set the parameters rpt.ParameterFields["Universe"].AllowCustomValues = true; rpt.ParameterFields["Universe"].CurrentValues.Clear(); rpt.ParameterFields["Universe"].CurrentValues.AddValue(selectedUniverse); rptViewer.ReportSource = rpt; }

16. Conclusion • The SDK provides a means to retrieve data to determine which connections and universes reports depend on. • Multiple InfoStore queries must be used to get all of the information needed to determine connection and universe dependencies. • It’s not difficult to display the information gathered from InfoStore queries by using Crystal embedded in an application.

17. Q & A • Questions • Dell Stinnett, Sr. Software Engineer, ZC Sterling • I will repeat questions so that everyone can hear • Contact Info • dell.stinnett@zcsterling.com • http://www.geocities.com/geekgurl7/Intro.html • Java example will be posted to Diamond site soon • https://www.sdn.sap.com/irj/sdn/businessobjects • Robert Horne – robert.horne@sap.com