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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Query Builder to return some results

Status
Not open for further replies.
eo

eo

MIS
Apr 3, 2003
809
I have to create a list of our reports, which folders they are located in, and their datasources. Is there any way I can use Query Builder to return such a result...a simple query would be...
Code: 
SELECT SI_ID, 
SI_NAME 
FROM CI_INFOOBJECTS 
WHERE SI_PROGID = 'CrystalEnterprise.Report'
But this does not go far enough.
I use CE10

EO
Hertfordshire, England
 
Sort by date Sort by votes
HI,
Define 'far enough'...

The CE_SDK.chm help file ( avaialble as part of:
has a full listing of all the query items that can be accessed using the Query Builder ( or your own code using an InfoObject ) - the Query Builder is easier..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Thanks TB.

To define "not far enough" means that my simple query listed does not supply the information I am after.

I am broadly looking for a list of the report names stored within CE & their folders. The query above provides 3000 results, whereas I know we only have about 1100 reports within CE?

Then a second query which will show folder > group access to each folder.

And hopefully a third query which will show report > datasources used within reports (or combine this with the first query if possible).

I have had a look at the SDK, and found the list of query items, but I do not have the nessesary experience to write such coding, it is beyond the realms if the simple code provided in my posting.

Couyld you advise which items I should be looking at?

EO
Hertfordshire, England
 
Upvote 0 Downvote
Hi,
I have posted in this forum some code to do most of that ( except for access rights, that is in development)..
Do a search for my postings and you can find them .

I forget how to cite a specific thread but one example is here:



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Hi, Again..

Here is an asp page that will return all the security
information for either a Folder's reports and all its sub-folder's reports OR, just 1 folder's reports,determined by the QueryStrings passed in the URL.
For Instance, when called with this:
Code: 
[URL unfurl="true"]http://YourServer/SomeDirectory/ShowSelectedRights.asp?ThisType=F&TID=516[/URL]
It will show security info for all reports in all the child folders of Folder ID # 516

While using this:
Code: 
http:YourServer/SomeDirectory/ShowSelectedRights.asp?ThisType=R&TID=518
Will show just those reports in Folder # 518, no subfolders.

Here is the code:

Code: 
 <%@ Language=VBScript %>
<HTML>
<HEAD>
<title>BOE XI Reports MetaData Page</title>
<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:green;
	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 = [i]"<YOUR CMS>"[/i]
Const UserID = [i]"<YOUR USER>"[/i]
Const Password= [i]"<YOUR PASS>"[/i]
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 ViewReportRoles (ReportID)
    ' Get the Report object for which to view the security roles, 
    ' display the report name, and get the Report plugin interface.
     Set MyStore = Session("IStore") 
     Set Reports = MyStore.Query("Select top 1 * From CI_INFOOBJECTS Where SI_ID='"& ReportID & "'")
     Set Report = Reports.Item (1)
     Response.Write("<TH class=tableHeading> The Following Users have permissions  </TH>")
    ' Get the principals for the Report object.
    Set RptSecurityInfo = Report.SecurityInfo
    Set RptSecurityPrincipals = RptSecurityInfo.ObjectPrincipals
    ' Loop through all of the report's security principals and 
    ' display the name and role of each principal.
    ceRoleAdvanced = 0
    ceRoleNoAccess = 1
    ceRoleView = 2
    ceRoleSchedule = 3
    ceRoleViewOnDemand = 4
    ceRoleFullControl = 5  
    For SecLoop = 1 to RptSecurityPrincipals.Count
        Set SecPrincipal = RptSecurityPrincipals.Item (SecLoop)
        Role = SecPrincipal.Role       
        Select Case Role
            case ceRoleAdvanced 
                RoleDescription = "Advanced" 
            case ceRoleNoAccess : 
                RoleDescription = "No Access" 
            case ceRoleView 
                RoleDescription = "View"
            case ceRoleSchedule 
                RoleDescription = "Schedule" 
            case ceRoleViewOnDemand 
                RoleDescription = "View on demand" 
            case ceRoleFullControl 
                RoleDescription = "Full Control" 
        End Select
        Response.Write "<TR><TD>" & SecPrincipal.Name &" - " & RoleDescription & "</TD></TR>"
    Next
  
End Function

Sub Main
   Dim Indx
   Dim Indx2
   Dim IStore
   Dim Result1
   Dim Result2
   Dim tyFlg
   Dim IdIn
   tyFlg = Request.QueryString ("ThisType")
   IdIn = Request.QueryString ("TID")
  ' Set up the InfoStore Object needed for all Queries aginst CI_INFOOBJECTS   
   Logon IStore
 ' Create a Table to organize the display of the data in a readable format
    Response.Write("<TABLE >")
  'See if a Parent Folder is wanted of just 1 folder with its reports
   If tyFlg = "F" then
  'If Folder then this section will cycle through all sub-folders of that parent and create
  'output for all the reports in each sub-folder ( NOTE - Can run for a very long time with large #s of reports)
   Set Result1 = IStore.Query("Select SI_ID, SI_NAME , SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Folder' And SI_INSTANCE=0 AND SI_PARENT_FOLDER='" & IdIn & "' order by SI_NAME" )     
  For indx2 = 1 to Result1.Count
  Response.Write("<tr><td style=color=red><u><h4> Folder Name is " & Result1.Item(indx2).Title & "</h4></u></td></tr>")
  Set Result2 = IStore.Query("Select SI_ID, SI_NAME , SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' And SI_INSTANCE=0 AND SI_PARENT_FOLDER='" &  Result1.Item(indx2).ID & "' order by SI_NAME" )     
     For indx = 1 to Result2.Count
     Response.Write("<tr><td style=color=blue> Report #  " & indx & " is " &   Result2.Item(indx).Title & "</TD></TR>")
      ViewReportRoles(Result2.Item(indx).ID)
     next
     Response.Write("<BR>")
 next
' If just 1 Folder's Reports are requested, this section will handle that...( NOTE: Many Reports = slow return )
elseif tyFlg = "R" then
	Response.Write("<tr><td style=color=red><u><h4> Report Information:</h4></u></td></tr>")
  Set Result2 = IStore.Query("Select SI_ID, SI_NAME , SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' And SI_INSTANCE=0 AND SI_PARENT_FOLDER='" &  IdIn & "' order by SI_NAME" )     
     For indx = 1 to Result2.Count
     Response.Write("<tr><td style=color=blue> Report #  " & indx & " is " &   Result2.Item(indx).Title & "</TD></TR>")
      ViewReportRoles(Result2.Item(indx).ID)
     next
     Response.Write("<BR>")
 else
 	Response.Write("Bad, Bad result")
 end if
  End Sub

Main

%>
</BODY>
</HTML>


Save it in any WEB folder and run it (with your info
added of course)
Feel free to delete any style functions not used to shorten the page.


Much of this code ( especially the ViewReportRoles function ) was derived from the sample code shown in the COM docs ...


Hope it is helpful..







[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lavadan
  • Locked
  • Question
Export to excel with drop down data
Replies
2
Views
141
hilfy
hilfy
smmaruf82
  • Locked
  • Question
How to Fixed 4 Column in Crystal Report Crosstab
Replies
1
Views
140
lbass
lbass
joshidharmesh
  • Locked
  • Question
Crystal report formula field query
Replies
3
Views
186
kray4660
kray4660
IanWaterman
  • Locked
  • Question
Guest Using all Licences
Replies
2
Views
114
IanWaterman
IanWaterman
kdavis6416
  • Locked
  • Question
Find email-addressess with CSP Query Builder and other detail information about reports
Replies
0
Views
59
kdavis6416
kdavis6416

Part and Inventory Search

Sponsor

Back
Top