Report Design Component Programming

book list add book to my bookshelf create a bookmark purchase this book online

mastering crystal reports 9
Chapter 20 - ASP Web Applications
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

We will use some of the reports from previous chapters in this book to put together the beginnings of a web application that contains Crystal Reports. One particular focus of these examples will be to create some reusable Active Server Page code, so that once a basic reporting engine is in place, putting a new report in your library becomes an easier task.

The first step (after successfully installing Crystal Reports 9 on your web server) is to create an application in IIS and point it to a virtual directory on your network. Then you should copy the files listed in Table 20.1 from your local Crystal 9 installation to that directory.

Table 20.1: Files from Crystal 9 Installation

File

Description

Cleanup.asp

Removes instances of RDC objects in memory, for both licensing and resource reasons.

framepage.asp

Used by the HTML viewer to set up frames.

htmstart.asp

Contains the start page of the HTML viewer.

rptserver.asp

Holds shared code that interfaces the viewers to the RDC.

SmartViewerActiveX.asp

Contains the ActiveX viewer component (IE).

ActiveXPluginViewer.asp

Contains the ActiveX viewer component (Netscape).

SmartViewerJava.asp

Contains the Java viewer component (IE).

JavaPluginViewer.asp

Contains the Java viewer component (Netscape).

toolbar.asp

Used by the HTML viewer.

Note 

These files, assuming you installed Crystal 9 in its default location, can be found in folder C:\Program Files\Crystal Decisions\Crystal Reports 9\Samples\En\Code\Web\Report Designer Component.

Next you should hunt down some sample code that Crystal Decisions provides online. The file is called aspxmps8.exe and can be found by going to http://support.crystaldecisions.com and searching for that filename. There are dozens of examples in this download, but for the time being we’re interested only in two files with the following names:

  • AlwaysRequiredSteps.asp

  • MoreRequiredSteps.asp

These two files are located multiple times, in many of the folders created by the download, so you should have no trouble finding them. Copy these two files into the IIS application virtual directory with the other files from above.

Now that you’ve stolen code from multiple places for your own evil purposes, it’s time to write some code yourself. Create a new file named GenericReportViewer.asp in the same virtual directory folder with all the other files, and fill it with the code from Listing 20.1 (either in Visual Interdev, Notepad, or the ASP editor of your choice).

Listing 20.1: GenericReportViewer.asp<% LANGUAGE=”VBSCRIPT” %>

start example
<% Response.Expires = 0 %> <html> <head> <title>Report Viewer</title> </head> <%     ReportName = Request.QueryString("rpt")    cCom       = Request.QueryString("com")    cSel       = Request.QueryString("sel")    Server.ScriptTimeOut = 180 %> <!-- #include file="AlwaysRequiredSteps.asp" --> <%     session("oRpt").ReportComments = cCom    session("oRpt").RecordSelectionFormula = cSel %> <!-- #include file="MoreRequiredSteps.asp" --> <!-- #include file="SmartViewerActiveX.asp" --> </body> </html>
end example

Note that this code makes use of the include file capabilities of Active Server Pages. Three ASP files are included in this one listing. The first two, AlwaysRequiredSteps.asp and MoreRequiredSteps.asp, are the ones you swiped from the example code found online. The last one, SmartViewerActiveX.asp, places the ActiveX Report Viewer on this web page.

You should be ready to see your first report online. Place an RPT file in your virtual folder, and then type the following URL into your web browser:

 http://localhost/crystal/GenericReportViewer.asp?rpt=CH02.rpt 

Your URL will vary based on the name of your web server, the name you gave your web application in IIS (the one above is named crystal), and the name of the report that you wish to view. Assuming all is well, you should see something like the Report Viewer shown in Figure 20.1.

Warning 

Remember that the report contains the connection information and that the server that is running this report needs the same connectivity information for the report to run. When you first test, we suggest copying the VistaNation’s database to the virtual directory and building a report from that database. That way you can concentrate on getting the ASP instead of connectivity. We will show you how to go to other databases later.

click to expand
Figure 20.1. Report Viewer

Note that you included the name of the report to display in the query string of the URL. This means that you could display virtually any Crystal report using this same code simply by changing the query string. This second query string displays a different report:

http://localhost/crystal/GenericReportViewer.asp?rpt=CH7.rpt

How It All Works

You just cobbled together a web-reporting solution using ASP files from several different sources, so some explanation might be in order as to how these files work together.

The top of the GenericReportViewer.asp program, shown in Listing 20.1, is pretty standard for a web page. The first actual lines of ASP code set three variables named ReportName, cCom, and cSel.

The ReportName variable is referenced in the file AlwaysRequiredSteps.asp, a portion of which is shown here (with the comments removed):

Path = Request.ServerVariables("PATH_TRANSLATED")    While (Right(Path, 1) <> "\" And Len(Path) <> 0)    iLen = Len(Path) - 1    Path = Left(Path, iLen) Wend If IsObject(session("oRpt")) then    Set session("oRpt") = nothing End if On error resume next Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)

The purpose of this block of code is to retrieve the physical path in which the current ASP resides and to initialize the session-level variable named oRpt by opening the Crystal report having the name stored in variable ReportName. The oRpt variable is the Report object part of the RDC, as you saw previously in Chapter 19. Likewise, the oApp variable is an instance of the RDC Application object. The rest of AlwaysRequiredSteps.asp does some error checking to make sure the Report object was created successfully, sets a few properties on the Report object, and then discards any data that might have been saved with the report:

If Err.Number <> 0 Then   Response.Write "Error Occurred creating Report Object: " & _ Err.Description   Set Session("oRpt") = nothing   Set Session("oApp") = nothing   Session.Abandon   Response.End End If session("oRpt").MorePrintEngineErrorMessages = False session("oRpt").EnableParameterPrompting = False session("oRpt").DiscardSavedData

This marks the end of the AlwaysRequiredSteps.asp file, so code execution resumes in Generic- ReportViewer.asp, where two properties of the Report object are set based on values retrieved at the top of this page:

session("oRpt").ReportComments = cCom session("oRpt").RecordSelectionFormula = cSel

You’ll see the purpose of these properties a bit later in this chapter. Next comes the execution of MoreRequiredSteps.asp, shown here in its entirety:

<% '====================================================================== ' Retrieve the Records and Create the "Page on Demand" Engine Object '====================================================================== On Error Resume Next session("oRpt").ReadRecords If Err.Number <> 0 Then    Response.Write "Error Occurred Reading Records: " & Err.Description   Set Session("oRpt") = nothing   Set Session("oApp") = nothing   Session.Abandon   Response.End Else   If IsObject(session("oPageEngine")) Then      set session("oPageEngine") = nothing   End If   set session("oPageEngine") = session("oRpt").PageEngine End If %>

This code is responsible for reading the records out of the database and into the report and checking to determine whether this read was successful. The code also initializes the PageEngine variable, used later. The PageEngine class is also part of the RDC—it is the object that generates each page of the report and sends it to the client.

Finally, SmartViewerActiveX.asp is included:

<HTML> <HEAD> <TITLE>Crystal Reports ActiveX Viewer</TITLE> </HEAD> <BODY BGCOLOR=C6C6C6 ONUNLOAD="CallDestroy();" leftmargin=0 topmargin=0 rightmargin=0 bottommargin=0> <OBJECT     CLASS    WIDTH=100% HEIGHT=100%    CODEBASE="/viewer9/activeXViewer/activexviewer.cab#Version=9,2,0,442" VIEWASTEXT> <PARAM NAME="EnableRefreshButton" VALUE=1> <PARAM NAME="EnableGroupTree" VALUE=1> <PARAM NAME="DisplayGroupTree" VALUE=1> <PARAM NAME="EnablePrintButton" VALUE=1> <PARAM NAME="EnableExportButton" VALUE=1> <PARAM NAME="EnableDrillDown" VALUE=1> <PARAM NAME="EnableSearchControl" VALUE=1> <PARAM NAME="EnableAnimationControl" VALUE=1> <PARAM NAME="EnableZoomControl" VALUE=1> </OBJECT> <SCRIPT LANGUAGE="VBScript"> <!-- Sub Window_Onload    On Error Resume Next    Dim webBroker    Set webBroker = CreateObject("WebReportBroker9.WebReportBroker")    if ScriptEngineMajorVersion < 2 then       window.alert "IE 3.02 users on NT4 need to get the latest version of VBScript or install IE 4.01 SP1. IE 3.02 users on Win95 need DCOM95 and latest version of VBScript, or install IE 4.01 SP1. These files are available at Microsoft's web site."    else       Dim webSource       Set webSource = CreateObject("WebReportSource9.WebReportSource")       webSource.ReportSource = webBroker       webSource.URL = "rptserver.asp"       webSource.PromptOnRefresh = True       CRViewer.ReportSource = webSource    end if    CRViewer.ViewReport End Sub --> </SCRIPT> <script language="javascript"> function CallDestroy() {    window.open("Cleanup.asp"); } </script> </BODY> </HTML>

This client-side script instantiates an instance of the ActiveX Crystal Report Viewer. The Object tag defines which ActiveX object we’re instantiating. ActiveX controls are differentiated by their ClassID. This long hexadecimal number is called a GUID (globally unique identifier) and is unique for every ActiveX control. Internet Explorer automatically determines whether the client machine has this ActiveX control installed at runtime and, if not, downloads it from the specified source. This is exactly why web-based reporting is so easy from the standpoint of client installs —because IE does it automatically.

Note 

A separate include file, named ActiveXPluginViewer.asp, should be used for Netscape clients.

The various PARAM tags configure the ActiveX viewer. Most of the buttons on the viewer can be made visible or invisible. The standard include file as shown above leaves all buttons and the group tree visible.

Finally, an instance of the WebReportSource9.WebReportSource object is created and is set to point to the file rptserver.asp. This last ASP file is quite large and does all the actual report rendering. You don’t need to understand the contents of this file to comprehend how the basic components of your RDC web-reporting server function.

Note 

Keep in mind that the first time users try to use the ActiveX viewer they will be prompted to download the viewer. If the security settings are set high in IE, this download may be prevented.

Changing the Database Connection String

The two report examples shown earlier use the VistaNations Access database and reports found earlier in this book. They both assume that the web server can “see” the Access MDB file in the same physical location as when the report was developed (on the root of the C: drive). If the Access database is not found, you may see this error when you attempt to view the report in your browser.

Error Occurred Reading Records: Logon failed. Details: DAO Error Code: 0xbd0 Source: DAO.Workspace Description: Could not find file 'C:\VistaNations.mdb'.

Of course, you could fix this by copying the Access database to the root of the C: drive of your web server, but your network manager may hit you with something. She probably doesn’t want “stuff” mucking up the C: drives of her servers. Clearly, you’re going to need a way to dynamically change the location of the database in the report.

The code you’ll need to write depends on exactly where the database is going to be located. If the database is going to be in a known, “hard-coded” location on the server, you can do something like the following:

<%    cDB = "D:\Database\VistaNations.mdb"    for each t in session("oRpt").Database.Tables       t.Location = cDB    next %>

Note how the code loops through all of the tables in the report and sets their location to the location specified by variable cDB. If the database is located in the same folder as the rest of the files, then the following code will retrieve the name of that folder and set all the tables in the report to that location.

<%    Path = Request.ServerVariables("PATH_TRANSLATED")    While (Right(Path, 1) <> "\" And Len(Path) <> 0)       iLen = Len(Path) - 1       Path = Left(Path, iLen)    Wend    for each t in session("oRpt").Database.Tables       t.Location = Path & "VistaNations.mdb"    next %>

This code is similar to the prior one but uses the PATH_TRANSLATED variable available in Active Server Page code to get the physical path of the current page.

The code examples above would be placed in the GenericReportViewer.asp application, between the include commands for AlwaysRequiredSteps.asp and MoreRequiredSteps.asp.

SQL Server Database Connections

Setting Microsoft SQL Server connection information is done much differently than for Access connections:

svr = "MyServer" usr = "MyUser" pwd = "MyPwd" db = "MyDB"     session("oApp").LogonServer  "PDSSQL.DLL", cStr(svr), cStr(db),cStr(usr), cStr(pwd)

This code is explicit to Microsoft SQL Server because of the PDSSQL.DLL parameter used as the first parameter to the LogonServer method. Logging onto different servers is done with different DLL names.

Note 

You won’t find a PDSSQL.DLL if you look for it on your PC. The parameter, although it looks like a filename, no longer corresponds to the actual name of the DLL containing the SQL Server connection code. Refer to the help file for the other driver names for different databases.

The method shown is also different than the PropertyBag method used in Chapter 19, but both methods serve an equivalent purpose.

This LogonServer call on the RDC Application object (stored in session variable oApp) is necessary before the call to ReadRecords, which can be found in the MoreRequiredSteps.asp file. The example above shows hard-coded SQL Server credentials. You would more commonly put this information in session variables in the global.asa file or some other more easily accessible place. Your environment may also require the dynamic retrieval of the SQL Server logon parameters based on the current intranet user if your SQL Server uses its own security logins or mixed-mode authentication.

Changing Selection Criteria

The example so far can load and display any report and dynamically change the database location. Another useful ability is to dynamically change the selection criteria of the report so that it can display records for a certain date range or location in the alphabet or whatever other selection criteria that make sense for your report.

The GenericReportViewer ASP code listed earlier already supports changing the selection criteria on the fly. All that needs to be done is to pass the criteria in the query string, like so:

http://localhost/crystal/GenericReportViewera.asp?rpt=CH02.rpt&sel={Resorts. StateProvince}='NV'

You should be greeted with the filtered report shown in Figure 20.2.

click to expand
Figure 20.2. Filtered report

The key to this functionality is that the selection criteria are pulled off the query string and into the variable cSel:

cSel       = Request.QueryString("sel")

The criteria are later placed into the RecordSelectionForumula property of the RDC Report object:

session("oRpt").RecordSelectionFormula = cSel
Tip 

Once we have our ASP environment set up, we can focus on the RDC objects and manipulate their methods and properties. Chapter 19 provides further RDC examples.

Note 

We are passing the selection string on the address line for simplicity; ideally you would build an interface where users can choose the selection criteria and build the selection string behind the scenes, as shown later in this chapter.

Report Comments

The Report Comment field in a Crystal Report (found in Special Fields) can be used in any section of a report. One common use for the Report Comment is in the Page Header to serve as a subtitle, often explaining in plain text to readers of the report which records are selected.

In the GenericReportViewer example listed above, report comments are handled in the identical way as record selection criteria. A third query string, named com, supports the entry of a comment field:

http://localhost/crystal/GenericReportViewera.asp?rpt=CH02.rpt&sel={Resorts. StateProvince}='NV'&com=Resorts%20in%20NV

This field is extracted from the query string and later placed into the ReportComments property of the RDC Report object:

cCom  = Request.QueryString("com") . <code removed> . session("oRpt").ReportComments = cCom

We are setting the RDC ReportComments property with the com value from the URL. You can see the Report Comment at the top of the report in Figure 20.3.

click to expand
Figure 20.3. Report comment

Form Posting

The method of setting up a report by passing query string information in the URL can handle many different reporting cases, but it’s sometimes difficult for a programmer to build that long of a query string. The super-long query strings are also a bit more unsightly. Fortunately, there is another, more robust method to send information from one web page to another, and that’s by using an Active Server Page form on one page and posting that information to the second page.

Consider the following Active Server Page, named CH7Params.asp, which sets up a simple data entry form:

<%@ LANGUAGE="VBSCRIPT" %> <html> <head> <meta NAME="GENERATOR" Content="Microsoft FrontPage 3.0"> <title>Resort Report</title> </head> <body bgColor=lightyellow> <font face=Tahoma> <p><font size=4>Select Letter that Resort Begins With</font></p> <hr> <form  name="fFRM" method="post" action="GenericReportViewer.asp"> <table> <tr><td>Enter the Start of the Resort Code</td></tr> <tr><td><INPUT type="TEXT" name=tbStart></td></tr> </table>  <p> <input type="submit" value="Submit" name="cbGo"></P> <input type="hidden" name="hCom"  value=" "> <input type="hidden" name="hRpt"  value="CH7.rpt"> <input type="hidden" name="hSel"  value=" ">  </FORM> <script LANGUAGE="VBSCRIPT"> Sub cbGo_OnClick    cLet  = fFRM.tbStart.Value    cSel = "{Resorts.ResortCode} startswith '" & cLet & "'"        fFRM.hSel.Value = cSel    fFRM.hCom.Value = "Resorts Beginning with Letter '" & cLet & "'"    fFRM.Submit end sub </script> </FONT> </body> </html>

This code creates a form with four input elements (only one of which is visible), as shown in Figure 20.4.

The visible input element is a text box that the end user can type into. The three hidden elements represent the Report Name (hRpt), the Selection Criteria (hSel), and the Report Comments (hCom). Note how the Report Name element is prefilled with the name of the report we will display (this element could be changed on the fly based on elements the user selects; for example, you could set up a Summary Report Only check box that would change the report displayed).

click to expand
Figure 20.4. Form with four input elements

The client-side VBScript procedure cbGo_OnClick is processed when the user clicks the Submit button. This code takes the contents of the text box and places it in both the selection criteria and the report comment variables and then submits the form. The form is programmed to post to your old friend, GenericReportViewer.asp. This web page still needs a bit more modification, however. It is currently designed to retrieve the three important variables from the URL, or query string. It needs to be modified to retrieve the variables from the preceding page’s form. Instead of creating an almost identical page except for this retrieval method, it is easy to combine both methods into one page:

<%     'report information can come via form OR querystring    'check form first        ReportName = Request.Form("hRpt")    cCom       = Request.Form("hCom")    cSel       = Request.Form("hSel")    if len(trim(ReportName)) = 0 then       ReportName = Request.QueryString("rpt")       cCom       = Request.QueryString("com")       cSel       = Request.QueryString("sel")    end if           Server.ScriptTimeOut = 180 %>    <!-- #include file="AlwaysRequiredSteps.asp" -->

This code first looks for variables on a posted form having the names hRpt, hCom, and hSel (the exact names of the three hidden elements on page CH7Params.asp). If the hRpt variable is not found or is empty, then the query string (the data on the address line of the browser, like we saw above) is parsed for the same information (this page will not work without a valid Crystal report name specified, so it is assumed that if the hRpt variable is blank, then the query string method is being used to send the data to the page).

The GenericReportViewer has just gained quite a bit of functionality. You can pass necessary information to display any report, with any selection criteria, along with comments, as a URL string. Alternatively, you can set up a "parameters" Active Server Page with user interface elements so that the user can specify date ranges, partial search strings, or any other elements, and these values can easily be posted over to the generic viewer as the selection criteria for any Crystal report. The data received from the parameters page can be used to modify the selection criteria or the group selection criteria of the RDC Report object.

Choosing a Different Viewer

Not content to provide only one way to view a report when three or four can do it better, Crystal Decisions provides different types of web-enabled viewers. Actually, because of the evolving web- browser market, they had no choice. The samples above use the ActiveX viewer. In addition, there are a Java-based viewer and a frame-based HTML-only viewer. The ActiveX viewer provides the closest rendition of the report in a browser but will work only with Internet Explorer. The Java viewer is the next best choice for rendering your report but requires a Java-compatible browser. Both viewers will download code to your browser, and your users will receive warning messages that a viewer will be installed. The HTML and HTML frames viewer will work on all browsers, but your report will lose formatting. You can base your choice on what technology works best for your environment.

To change viewers, modify the last line in GenericReportViewer.asp. To use the ActiveX viewer, the line should appear as one of the following:

<!-- #include file="SmartViewerActiveX.asp" --> <!-- #include file="JavaPluginViewer.asp" --> <!-- #include file="FramePage.asp" -->

Screenshots of the Java viewer and the HTML viewer are shown in Figure 20.5 and Figure 20.6.

Note 

The Java runtime environment must be present on your machine in order to use the Java viewer. You will be prompted to download it automatically the first time you attempt to use the Java viewer (assuming you have a valid Internet connection).

click to expand
Figure 20.5. The Java viewer

click to expand
Figure 20.6. The HTML viewer

Reports with Parameters

The previous sections give enough examples to put together a generic, web-based Crystal viewer that can show multiple reports and allow for the changing of selection criteria and report comments at runtime. Of course, we have access to the entire RDC from Active Server Page code, and we can design any kind of application we would like.

One such feature is the use of report parameters. These are often used in conjunction with report-selection criteria to limit the data shown in a report. Parameters are a bit more difficult to use in Active Server Page code, only because the report’s parameters are exposed as a collection, and this collection must be looped through in order to find a certain parameter by name and set it by value. Here is an example of some Active Server Page code that does that:

For Each oParam in session("oRpt").ParameterFields     Select Case oParam.ParameterFieldName        Case "pShowallValues"           iValue = Request.QueryString("Show")          oParam.SetCurrentValue Clng(iValue)       End Select  Next

This loop looks for a parameter named pShowallValues and, if/when found, sets that value to the value in the Show portion of the query string.

Of course, if you know that your report has only a single parameter, then this loop is not necessary—you can specify the first (and only) parameter in the collection:

Set oParam = session("oRpt").ParameterFields(1) iValue = Request.QueryString("Show") oParam.SetCurrentValue Clng(iValue)

Suppressing Detail Lines (or Other Sections)

Another useful property exposed by the RDC is the Suppress property on the Section object. Using this, you can dynamically suppress any section in a report. This might be useful for offering the option of a detail or a summary only by using a single Crystal report file, with a check box on the Options page selecting whether to suppress or not. The line below shows the suppression of Section number 3 in a report:

session("oRpt").Sections(3).Suppress = true

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement
 
Conello © 2000-2003     Feedback


Mastering Crystal Reports 9
Mastering Crystal Reports 9
ISBN: 0782141730
EAN: 2147483647
Year: 2005
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net