Lesson 2b: Listing the reports in a folder

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 a hyperlink, allowing the user to navigate to a page that will display the report and its details.

Displaying Crystal Report information in a table

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=" + ParentID;

The items being selected may be described as follows.

Property	Description
SI_NAME	The name of the report.
SI_ID	The ID number for the report.
SI_DESCRIPTION	A textual description of the report.
SI_UPDATE_TS	A Date/Time value specifying when last the report was modified.

Remarks

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.

Note:    To avoid using SI_INSTANCE, you could query by SI_PARENTID instead of SI_PARENT_FOLDER. This would be faster than using SI_PARENT_FOLDER. For example, Select SI_NAME From CI_INFOOBJECTS Where SI_PARENTID = 123. In some cases, however, you may find it necessary to filter out instances of reports.

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
World Sales Report	A Report displaying sales by country.	1/1/2000, 12:34 PM

Go to next step:

Adding the RetrieveReports function

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.

RetrieveReports function

function RetrieveReports(ParentID ,IStore)

{

//This returns an HTML table that is populated with the report name,

//report description, and the last time it was modified.

//Precondition:

//ParentID - The ID of the parent folder containing the reports to be retrieved.

//IStore - The InfoStore object required to interface with server.

//Postcondition:

//Returns an empty string if there are no reports, null if an error occurred, and

//the string if successful.

//Notes:

//The function returns a string that is an HTML table.

    //The query that will select the reports.

    var Query;  

    //The result of the query.

    var Result;

    //A string to hold the HTML table.

    var HTMLTable;

    HTMLTable="";

    //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=" + ParentID;

    //Query the server.

    try {

        Result = IStore.Query(Query) ;

    }

    catch(e) {

        return null;

    }

    if (Result.Count > 0) {

        //Set up the table header.

        HTMLTable="<TABLE width = \"100%\" >" +

        "<TR><TH><B>Name</B></TH>" +

        "<TH><B>Description</B></TH>" +

        "<TH><B>Last Modified</B></TH></TR>";

        for (k=1;k<=Result.Count;k++)

        //Add the report name and details to the table.    

        {

            HTMLTable=HTMLTable +

                "<TR  valign=top><TD>" +

                "<A HRef='ReportDescription.csp?ReportID=" + Result.Item(k).ID +

                "&FolderID=" + ParentID + "' Target='Main'>" +

                Server.HTMLEncode(Result.Item(k).Title) + "</A></TD>" +

                "<TD>" + Server.HTMLEncode(Result.Item(k).Description) + "</TD>" +

                "<TD>" + Result.Item(k).Properties.Item("SI_UPDATE_TS") + "</TD></TR>";

        }

        HTMLTable=HTMLTable+"</TABLE>";

    }

    return( HTMLTable);

}

Remarks

This function is very similar to the RetrieveChildFolders function, except for the loop that builds an HTML string instead of directly printing the links. 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. ReportDescription.csp 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.

Go to next step:

Viewing the table