Needing the Object names live on the CE10 environment 1

eo · Feb 3, 2006

Hi

CE10. Systemdatabase = MS SQL.

We have a change control facility which runs off a sql table which contains all the object names in the CE10 (Audit) environment. This list is obtained by running a report off the CMS_AuditTable > ObjectName

The flaw in this method is that it contains the Object names of all objects, including Folder names, and even objects that no longer exists within this env. This is due to the fact that this is a historical based (Audit) table, so any object that used to exist, is now contained within my list. I simply want an uptodate list of objects (preferably reports only) contained on my system currently.

Is this at all possible. I looked at the CE10 database, but makes no sense to me.

EO
Hertfordshire, England

synapsevampire · Feb 3, 2006

To get all report objects the query would be:

SELECT * FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'

Note that in the CE Launchpad there's a query facility called Query Builder which allows you to query the database.

I think it was Turkbear (CE guru here) that came up with an Excel model for getting information out of the database, and with a few tweaks it worked fine for me.

Also check out Apos tools:

http://www.apos.ca

-k

eo · Feb 6, 2006

Hi

A slight version of the query suggested gave me the required info - SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'
But I want to use this is a table all on its own. So would prefer to do this using a standard SQL query. The objects (table name and field names) do however not exist within the CE10 database...I suppose there is some BOBJ reason for this?

EO
Hertfordshire, England

Turkbear · Feb 6, 2006

Hi,
'Standard' SQL will not work..The CI_INFOOBJECTS is designed for use with CE's services( and for the SDK's methods to access) , through the InfoStore object and the CMS..It is done, presumably, to insure efficient access for the System's servers, to enforce security and to enable data integrity...The CE system objects are not designed for 'normal' user access.
CE ( and BOE XI) has admin tools for most activities/queries regarding the Objects in the system.

To Paraphrase:"The Help you get is proportional to the Help you give.."

eo · Feb 6, 2006

The challenge that I have, is that we use a bespoke change control system which runs over a MS SQL database, which should contain the report object names that are currently on the system. The output from SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'
does not provide me with a format which I can easily (or even better automatically) import into this database.

EO
Hertfordshire, England

Turkbear · Feb 6, 2006

Hi,
There are 3rd party tools ( see the reference to APOS in a previous post) or you could develop asp code that would retrieve those names and output them in a format you could use..I have not tried that yet ( I just need to display the names, etc ) but is should be 'doable'..

Perhaps using this as a starting point:

Code:

 <%@ Language=VBScript %>
<% Option Explicit

%>
<HTML>
<HEAD>
<title>Reports MetaData Sample</title>
<link rel="stylesheet" type="text/css" href="rsc/style/template_style.css"/>
<style type="text/css">
body {
	font-family:Verdana, Arial, Helvetica, sans-serif;
	font-size:12px;
	color:#5F5F5F;
	text-decoration:none;
	scrollbar-arrow-color:#003366;
	scrollbar-base-color:#C0C0C0;
	scrollbar-darkshadow-color:#6699cc;
	scrollbar-face-color:#6699cc;
	scrollbar-highlight-color:;
	scrollbar-shadow-color:;
}

table {
	font-family:Verdana, Arial, Helvetica, sans-serif;
	font-size:12px;
	color:#5F5F5F;
	text-decoration:none;
}

td {
	vertical-align:top;
    }
    
.dropdown {
	font-size:11px;
    }
  
.marginTable {
	margin-left: 10px;
	margin-right: 10px;
}

.heading {
	font-size:18px;
	font-weight:700;
	text-align:center;
	color:#003366;
}

.leftnav {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	color:white;
	text-decoration:none;
}

a:hover.leftnav {
	text-decoration:underline;        
}

.leftnavHeading {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	text-align:center;
	background-color:#99ccff;
	color:#003366;
	text-decoration:none;
}

a:hover.leftnavHeading {
	text-decoration:underline;        
}

.leftnavHeadingLeftAlign {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	text-align:Left;
	/*background-color:#99ccff;  */
	color:#003366;
	text-decoration:none;
}


.tableHeading {
	font-weight:700;
	color:#003366;
	font-size:14px;
}
.tableHeadingGreen {
	font-weight:700;
	color:green;
	font-size:14px;
}

.tableHeadingLeft {
	font-weight:700;
	text-align:left;
}


.tableHeadingLink {
	font-weight:700;
	color:#5F5F5F;
	text-align:center;
	text-decoration:none;        
}

a:hover.tableHeadingLink {
	font-weight:700;
	color:#5F5F5F;
	text-align:center;
	text-decoration:underline;        
}

.tableLink {
	font-weight:200;
	font-size:14px;
	color:#003366;
	text-align:left;
	text-decoration:none;        
}

a:hover.tableLink {
	font-weight:200;
	color:#6699cc;
	text-align:left;
	text-decoration:underline;        
}

.navigationPage {
	font-size:14px;
	font-weight:700;
	text-align:left;
	color:#003366;
	text-decoration:none;
}

a:hover.navigationPage {
	font-size:14px;
	font-weight:700;
	text-align:left;
	color:#6699cc;
	text-decoration:underline;
}

.emailLink {
	font-weight:200;
	color:#5F5F5F;
	text-align:left;
	text-decoration:underline;        
}

a:hover.emailLink {
	font-weight:200;
	color:#333333;
	text-align:left;
	text-decoration:underline;        
}
</style>
</HEAD>


<BODY>


<%

Const APS = "<YOURCMS>"

Const UserID = "<USERWITHADMINRIGHTS>"

Const Password= "<PASSFORTHSTUSER>"

Const Aut = "secEnterprise"

Function Logon(ByRef IStore)

    Dim SessionManager 
    Dim Result
    Result = FALSE
    Set SessionManager = Server.CreateObject("CrystalEnterprise.SessionMgr")
    If Err.Number = 0 then
        Dim Sess
        Set Sess = SessionManager.Logon(UserID, Password, APS, Aut)
        If Err.Number = 0 then 
          Set IStore = Sess.Service ("", "InfoStore")
          Set Session("IStore") = IStore
          Result = TRUE
        End If
    end if
   Logon = Result
End Function

Function MakeWebPage(Reports)
   Dim pRpt
   Dim pRpts
   Dim MyStore  
   Response.Write("<HTML>")
   Response.Write("<BODY")
   Response.Write("<FORM Name='main'>")
   Response.Write("<TABLE>")
   Response.Write("<TH class=tableHeading>Reports</TH>")
   Response.Write("<TR class=tableHeadingGreen>")
   Response.Write("<TD>ID</TD><TD>Title</TD><TD>Description</TD></TR><TR>")
    Set MyStore = Session("IStore") 
     for each pRpt in Reports
     Response.Write("<TD>" & pRpt.ID & "</TD><TD>" & pRpt.Title & "</TD><TD>" & pRpt.Description & "</TD></TR><TR>")
    Next  
  Response.Write("</TR></TABLE></FORM></BODY></HTML>")
  End Function




Sub Main
   Logon IStore
   Set Result = IStore.Query("Select SI_ID,SI_NAME,SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' order by SI_NAME" )  
   Dim IStore
   Dim Result
   MakeWebPage(Result)
    End Sub

Main

%>
</BODY>
</HTML>

Instead of producing tabular output, as it does now, perhaps it could output to a CSV type file to be imported into your database...

To Paraphrase:"The Help you get is proportional to the Help you give.."

eo · Feb 7, 2006

Hi Turkbear

When I save the suggested ASP, and make changes, such as enter my CMS etc, where do I go from there?

EO
Hertfordshire, England

Turkbear · Feb 7, 2006

Hi,
To run it, just place it in any virtual directory of your IIS server and call it like any normal asp page..

To revise it to produce file based output is up to you, I've never done that ..( the VBScript needed should be straightforward, however)

To Paraphrase:"The Help you get is proportional to the Help you give.."

eo · Feb 7, 2006

Cool, can now see tghe output, just one more thing. It seems to show AKLL objects (i.e. historical instances as well, all I want if the actual objects) - any clues as to how to change the code for this?

EO
Hertfordshire, England

Turkbear · Feb 7, 2006

Hi,
Yes..add ( I think)
SI_INSTANCE = 0 to the Query:

Code:

Set Result = IStore.Query("Select SI_ID,SI_NAME,SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_INSTANCE = 0 order by SI_NAME" )

Not really sure however..

To Paraphrase:"The Help you get is proportional to the Help you give.."

eo · Feb 7, 2006

Turkbear

Thanks again! Just one last Q - my generated list only shows Objects up till "U..." and not the rest. Does this code limit the number of object names returned?

EO
Hertfordshire, England

Turkbear · Feb 7, 2006

Hi,
Not That I know of..There are no restrictions in the Query except for the new one we added...
BUT:
One of my systems which has 2600 or so reports only shows 1000 of them..( Odd..never checked on that )

I'll look into it further..

To Paraphrase:"The Help you get is proportional to the Help you give.."

Turkbear · Feb 7, 2006

Hi,
OK - A plain query like I posted will return a Maximum of 1000 records..It is a built-in limitation apparently..

To get around this use the TOP N function ( setting N to a number greater than the total count of your reports).
In my case I used:

Code:

Set Result = IStore.Query("Select [COLOR=red]TOP 3000 [/color]SI_ID,SI_NAME,SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_INSTANCE = 0 order by SI_NAME" )  
[code]

[profile]
[COLOR=blue]
To Paraphrase:"The Help you get is proportional to the Help you give.."
[/color]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Needing the Object names live on the CE10 environment 1

eo

MIS

synapsevampire

Programmer

eo

MIS

Turkbear

Technical User

eo

MIS

Turkbear

Technical User

eo

MIS

Turkbear

Technical User

eo

MIS

Turkbear

Technical User

eo

MIS

Turkbear

Technical User

Turkbear

Technical User

Similar threads

Part and Inventory Search

Sponsor