Get list of Crystal reports by data connection from BOE XI 3

server administration XI Server Discussion
1

I have a need to get list of Crystal reports by data connection from BOE XI 3.1 enterprise.

I could get the list of connections using the sql

SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND=‘MetaData.DataConnection’

I could get all the Crystal Reports Names using the beloew sql

select SI_name from ci_infoobjects where si_KIND= ‘crystalreport’

I have to merry these two sqls but I am not able to get to the final SQL. Can anyone help me getting this acheived.

Thanks in Advance

Chikki (BOB member since 2006-05-08)

2

Are you using the query builder to do this? Why not simply check in the CMC, which reports make use of what connection?

wahey :netherlands: (BOB member since 2007-05-31)

3

I am using the query builder to do this. I have 2000 reports and I am trying to find an easy way instead of going to each report and find the source of the report.

Chikki (BOB member since 2006-05-08)

4

The thing with Crystal Reports is, that they can be build on Universes (and therefore connections) or an ODBC connection or a native connection. The latter two can be retrieved with a query like

Select
  SI_ID,SI_NAME,SI_PROCESSINFO.SI_LOGON_INFO 
From 
  CI_INFOOBJECTS
Where 
  SI_KIND = 'CrystalReport'

Combining queries in the Query Builder is pretty limited as you will have read on other posts.

Orange :netherlands: (BOB member since 2006-09-18)

5

But since you’re on 3.1, you can use the dependence checker to see which reports use what connection, etc.

wahey :netherlands: (BOB member since 2007-05-31)

6

I am not sure how to get to the dependency checker… do we have to do it report by report then, which I wanted to avoid …

Chikki (BOB member since 2006-05-08)

7

SELECT SI_ID, SI_NAME ,si_processinfo.si_logon_info FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’

This SQL gives me the total list.

  1. But how can I get the results more than 1000
  2. I need to go to si_processinfo.si_logon_info.si_logon1.si_server value. But when I use this in the query, it doesnot give anything for this column

SELECT SI_ID, SI_NAME ,si_processinfo.si_logon_info.si_logon1.si_server FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’

Can you please let me know how to write this query.

Thanks

Chikki (BOB member since 2006-05-08)

8

  1. But how can I get the results more than 1000
    Use the top N option, like
    SELECT TOP 100000 SI_ID …

  2. I need to go to si_processinfo.si_logon_info.si_logon1.si_server value. But when I use this in the query, it doesnot give anything for this column

Nope, sore you cant dig this deep using the Query Builder. You will need to use the proper SDK for this.

Orange :netherlands: (BOB member since 2006-09-18)

9

You can’t do it using query only. You need to write code to handle the query results as objects. He is a sample that gets (actually sets) the datasource for a single report object.

public void SetDataSource(String reportName)
        {

            String queryString = "Select SI_ID, SI_NAME, SI_PROCESSINFO.SI_LOGON_INFO "
                       + "          From CI_INFOOBJECTS "
                       + "Where SI_PROGID='CrystalEnterprise.Report' "
                       + "  and SI_INSTANCE_OBJECT = 0"
                       + "  and SI_RECURRING = 'False' "
                       + "  and SI_NAME LIKE '" + reportName + "%'";

            InfoObjects infoObjects = infoStore.Query(queryString);           

            if (infoObjects.Count > 0)
            {
                InfoObject infoObject = infoObjects[1];
                ProcessingInfo procInfo = infoObject.ProcessingInfo;
                procInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_SERVER"].Value = "CCBSTST";
                procInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_USER"].Value = "covrpt";
                //    procInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_PASSWORD"].Value = "covrpt";
                
                infoObject.Save();


            }
            else
            {
                throw new Exception("No Report Found");
            }
        }

sgilmore :us: (BOB member since 2009-06-25)

10

I can’t really tell you how to write your query, but for getting the next 1000 records, you could add a line to your where clause where SI_ID >

You’ll probably want to ORDER BY SI_ID as well to make this work.

thartm (BOB member since 2007-05-31)

11

All:

I was able to use top to get beyond 1000 rows as Query builder limits returning 1000 rows without top.

select top 1200 si_id, si_name from ci_infoobjects

Regarding querying data from next level, I have used following query to test with to retrieve data

SELECT SI_ID, SI_NAME, SI_CRVERSION FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’ and SI_CRVERSION.SI_MAJOR = 12

It returned proper data. Note: You cannot display just the next level data.

Following is Valid query but doesn’t return next level data alone

SELECT SI_ID, SI_NAME, SI_CRVERSION.SI_MAJOR FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’ and SI_CRVERSION.SI_MAJOR = 12

Regards
Ramesh Vasudevan

rameshbalajiv (BOB member since 2008-03-25)