In this section, you'll complete the right hand side of the frames page so that each time the user clicks on a folder link, the Crystal Reports inside that folder are displayed (see Home Page). You will then use an HTML table to display the name of each report, as well as its description and the last time it was updated. The name of each report will serve as hyperlink, allowing the user to navigate to a page that will display the report and its details.
The query for a report's name, description, and date looks like this.
query = "Select SI_NAME, SI_ID, SI_DESCRIPTION, SI_UPDATE_TS from CI_INFOOBJECTS where " & _
"SI_PROGID='CrystalEnterprise.Report' AND SI_INSTANCE=0 AND SI_PARENT_FOLDER=" & cstr(ParentID)
The items being selected may be described as follows.
| Property | Description |
|---|---|
A Date/Time value specifying when last the report was modified. |
Notice that the SI_PROGID has been changed to 'CrystalEnterprise.Report', and that SI_INSTANCE is set to zero (FALSE), indicating that you wish to retrieve only reports. If you do not include the SI_INSTANCE property, then both reports and instances will be retrieved. The ID number of the report is also retrieved. If more detailed information about this report is to be viewed later on, this number will be needed to access additional information about the report.
This query returns more information than the query used to retrieve the folder names and IDs, and therefore will be dealt with differently. The function to retrieve this information will return a string of HTML code that displays this information in a tabular format as follows.
| Report Name | Description | Modified |
|---|---|---|
Adding the RetrieveReports function
The function to retrieve a report's name, description, and date is shown below. Cut and paste this code into a new file and save it as Reports.csp.
Function RetrieveReports(ParentID ,IStore, Error)
'This returns an HTML table that is populated with the report name,
'report description, and the last time it was modified.
'ParentID - The ID of the parent folder containing the reports to be retrieved.
'IStore - The InfoStore object required to interface with server.
'Error - A variable to hold the error if one occurs.
'Error - Contains the error number: 0 if successful.
'The function returns a string that is an HTML table.
'The query that will select the reports.
'A string to hold the HTML table.
'Create a query that selects all the reports, but doesn't select the instances.
query = "Select SI_NAME, SI_ID, SI_DESCRIPTION, SI_UPDATE_TS From CI_INFOOBJECTS Where " & _
"SI_PROGID='CrystalEnterprise.Report' And SI_INSTANCE=0 AND SI_PARENT_FOLDER=" & CStr(ParentID)
Set Result = IStore.Query(Query)
'There was an error querying the server.
HTMLTable="<TABLE Border=0 width = ""100%"">" & _
"<TR><TD><B>Name</B></TD>" & _
"<TD><B>Description</B></TD>" & _
"<TD><B>Last Modified</B></TD></TR>"
'Add the report name and details to the table
"<A HRef='ReportDescription.csp
"&FolderID=" & ParentID & "' Target='Main'>" & _
Server.HTMLEncode(oReport.Properties("SI_NAME")) & "</A></TD>" & _
"<TD>" & Server.HTMLEncode(oReport.Properties("SI_DESCRIPTION")) & "</TD>" & _
"<TD>" & oReport.Properties("SI_UPDATE_TS") & "</TD></TR>"
HTMLTable=HTMLTable&"</TABLE>"
This function is very similar to the RetrieveChildFolders function, except for the loop that builds an HTML string instead of populating arrays with data. The HTML string that is built uses the ID and name of the report to create a link to a file named ReportDescription.csp and hands it the parameter ReportID, which is the ID of the report currently being processed. This enables you to query the APS for details about a report that the user has selected.
The Folder ID that is also passed enables the Report Description page to display a link back to the current folder. In addition, it is needed to write changes to a report, and also to schedule, and view the contents of a report. See Lesson 3: Viewing, modifying, and scheduling reports.
After you have pasted this code into the Reports.csp file, ensure that you have included the RetrieveIStore function. This page needs it to communicate with the APS. Additionally, don't forget to surround all of the VBScript with the <% and %> tags.
See Reports.csp.
Congratulations! You've reached the end of this lesson. To see a full version of the CSP page that you've created see Reports.csp
Lesson 2c: Displaying the complete path to the current folder
| Crystal Decisions, Inc. http://www.crystaldecisions.com Support services: http://support.crystaldecisions.com |