Programming Reports in Access Projects


Programming Reports in Access Projects

You can program reports in Access projects similarly to the way that you program reports in Access database files. In addition, nearly all the same wizards you know about from Access database files are available for building reports based on SQL Server database objects. Although you aren't likely to find these reports suitable for creating a final, professional report, they're definitely a useful starting point for creating a custom report. Due to the extreme similarity between Access reports in Access database files and Access projects, I recommend you revisit Chapter 6. That chapter demonstrates many useful techniques for generating reports for Access projects.

Both samples in this section cover interesting report topics. The first sample illustrates how to create a report that lets a user specify the sort key and sort direction at run time. The ability to set report features at run time is a topic that has wide appeal . The key to setting report features at run time is knowing the structure of reports and report objects. This sort sample demonstrates the use of two report properties. The second sample reveals how to set and restore input parameters for the record source behind a report. This sample fires a procedure to restore an InputParameters report property after a report closes . Firing a procedure after closing a report is necessary because you cannot modify a report's InputParameters property while it's open in Preview or Print modes. This solution comes in handy for any report application in which you need to restore report properties after a report finishes printing.

Sorting on Any Field in Any Direction

One of the many uses for reports in SQL Server applications is to sort the result set from views (and other database objects) at run time. Unless you specify a TOP clause in the SELECT statement for a SQL Server view, the view's statement will not support an ORDER BY clause. Even if you do anticipate this need and include the TOP clause for a particular sort order, different end users, or even the same user at different points in time, may require the same report sorted in different ways.

The following sample shows how to dynamically control the sort order of a result set from a view when you show that result set in a report. Start with a tabular report for the view. If your view's columns fit nicely on one page, you can use the AutoReport: Tabular Wizard to create the report's layout. You can access this wizard by selecting Reports in the Objects bar on the Database window and clicking New. Then highlight AutoReport: Tabular, and select a view from the drop-down list below the report types in the New Report dialog box. Launch the creation of the report by clicking OK. This technique applies equally well to tables, row-returning stored procedures, and user-defined functions that can also serve as record sources for reports.

If the report's columns do not fit on a single page, choose the Report Wizard in the New Report dialog box. Choose as many columns as you need from the wizard's first screen, and then accept the defaults for the second and third screens by choosing Next . On the Report Wizard's fourth screen, clear the check box with the label Adjust The Width So All Fields Fit On A Page. Then click Finish to create a tabular report that spreads the report's columns across multiple pages.

I created and saved a tabular report named rptvwEmployeesOrderDetails based on the vwEmployeesOrderDetails view. This view served as the source for the form sample in the preceding section and is available in the Access project for this chapter. The custom view displays columns from the Employees and Order Details tables. The column name qualifiers denote the table owners and table names . To merge fields from the Employees and Order Details tables, the SQL statement for the view requires the Orders table. The following T-SQL statement represents the view that serves as the source for the report:

 SELECTdbo.Employees.FirstName,dbo.Employees.LastName, dbo.[OrderDetails].OrderID,dbo.[OrderDetails].ProductID, dbo.[OrderDetails].UnitPrice,dbo.[OrderDetails].Quantity, dbo.[OrderDetails].Discount FROMdbo.EmployeesINNERJOIN dbo.OrdersONdbo.Employees.EmployeeID= dbo.Orders.EmployeeIDINNERJOIN dbo.[OrderDetails]ONdbo.Orders.OrderID= dbo.[OrderDetails].OrderID 

The report sorts rows natively first by EmployeeID , second by OrderID , and third by ProductID . (See Figure 12-14.) This arrangement of records reflects the primary keys for the Employees and Order Details tables. However, sometimes you might prefer to show the output in another order. The next sample, which is based on a pair of procedures, implements the core logic for assigning a sort at run time on any field in a report's record source. In addition, the sample lets you sort in either ascending or descending order on any key. Before analyzing the code, you might find it useful to review how a report handles sorting programmatically. A Report object has two properties to facilitate sorting: OrderBy and OrderByOn . The OrderBy property is a string that represents the ORDER BY clause of an SQL statement without the ORDER BY keyword. Therefore, the OrderBy property should contain a string of field names, followed by an optional keyword designating the sort order on each key field. If you have more than one sort key, separate them by commas. Setting a report's OrderBy property does not by itself establish a sort order based on a list of fields. You must also set the OrderByOn property to True .

click to expand
Figure 12.14: An excerpt from the rptvwEmployeesOrderDetails report. It shows the sort of OrderID and ProductID within EmployeeID .

Access permits you to set both the OrderBy and OrderByOn properties in Preview mode after a report is open. Settings made this way do not persist until the next time the report opens. This feature makes the properties ideal for assigning sort keys at run time. Your property settings apply exclusively to the current Preview session for a report.

The following code listing represents one combination of settings for this sample's functionality. The first procedure allows a user to specify the report name and the field name on which to perform a sort. As an option, you can specify a third argument in the first procedure that designates either an ascending or a descending sort order. If you do specify a sort order argument, the application sorts in ascending order by default. This default assignment takes place in the second procedure. After setting the argument values, the first procedure passes them to the second procedure.

The second procedure accepts three arguments, but the last of these is preceded by the Optional keyword. This keyword applies to the Asc argument. This argument is a Boolean value for designating an ascending sort order. Its default value is True in its declarations for the second procedure. The sample listing doesn't designate a third argument, so the default value for Asc applies. You can designate a descending sort order by specifying a value of False for the third argument in the first procedure, which passes the arguments to the second procedure. The second procedure begins by opening a report. The first argument passed to the second procedure names the report. After creating a reference to the report, the procedure starts an If Then Else End If statement. The Then clause assigns the second argument as the value of the report's OrderBy property. This argument is the field name on which to perform the sort. Because the default order is ascending and there isn't any order specification, the Then clause assigns an ascending sort. The Else clause designates a descending sort order. Before closing, the procedure sets the report's OrderByOn property to True . This assignment activates the OrderBy property setting.

 SubCallSortReport() DimrptNameAsString DimfldNameAsString DimbolAscAsBoolean     'Specifysortnameandsortfield rptName= "rptvwEmployeesOrderDetails" fldName= "Discount"     'Passargumentstoprocedureto 'preparesortedreport SortReportrptName,fldName     EndSub     SubSortReport(rptNameAsString,SortKeyAsString,_ OptionalAscAsBoolean=True) Dimrpt1AsReport Dimstr1AsString     'Openthereport DoCmd.OpenReportrptName,acViewPreview Setrpt1=Reports(rptName)     'Sortinascendingordescendingorderonsort 'keyaccordingtooptionalargumentvalue IfAsc=TrueThen rpt1.OrderBy=SortKey Else str1=SortKey& " DESC" rpt1.OrderBy=str1 EndIf     rpt1.OrderByOn=True     EndSub 

Figure 12-15 shows an excerpt from the report. Notice that this excerpt sorts rows on the Discount value before the primary key settings for the view's source tables. Contrast this with the report excerpt in Figure 12-14 to see the effects of the sort. The primary key settings for the source still apply, but only after the sort specification in the sample code. By resetting the Fieldname argument, users can sort the report on any column containing numeric or string values in the view. You can adapt this sample to permit run-time sorts on more than one field.

click to expand
Figure 12.15: An excerpt from the rptvwEmployeesOrderDetails report after sorting by the preceding code sample.

Setting and Restoring the InputParameters Property

Another typical run-time request is to make the record source for a report dynamic. Instead of just re-sorting the same records on different criteria, users might need to specify different subsets from a record source at run time. One way to do this is to define a stored procedure with parameters. For example, a user might want to view specifications for all cars in a given price range ”for example, $20,000 to $30,000. Another user might want to examine the same specifications report but might need to see cars priced above $50,000. Database consultants often have to make the same report available to different users with different needs. This section shows you how to do this for your clients .

The sample in this section relies on a stored procedure named Employee Sales by Country . This procedure ships with the NorthwindCS database. It returns the sales amount for each order by employee and by the customer's country. Users can specify a start date and an end date that indicate the period for which the procedure should return this data.

The Employee Sales by Country stored procedure is available on the Access project for this chapter along with the view and tables that it references. The T-SQL script from an Access template for the stored procedure appears next. Note that it contains two parameters: @Beginning_Date and @Ending_Date . By setting these parameters just before calling the procedure, users can restrict the range of dates indicating when the stored procedure returns data. If you have a report based on the stored procedure, that report will display only data for the range specified by the @Beginning_Date and @Ending_Date parameters.

 ALTERPROCEDUREdbo.[EmployeeSalesbyCountry] @Beginning_Datedatetime, @Ending_Datedatetime) AS SELECTdbo.Employees.Country,dbo.Employees.LastName, dbo.Employees.FirstName,dbo.Orders.ShippedDate, dbo.Orders.OrderID,dbo.[OrderSubtotals].SubtotalASSaleAmount FROMdbo.EmployeesINNERJOIN dbo.OrdersINNERJOIN dbo.[OrderSubtotals]ONdbo.Orders.OrderID= dbo.[OrderSubtotals].OrderIDONdbo.Employees.EmployeeID= dbo.Orders.EmployeeID WHERE(dbo.Orders.ShippedDateBETWEEN@Beginning_DateAND@Ending_Date) 

The sample Access project for this chapter includes an Access report named rptEmployeeSalesbyCountry that's based on the previous stored procedure. You can create a tabular report such as rptEmployeeSalesbyCountry with steps similar to those used for the preceding report example. However, Access will prompt you for a starting and ending date as you build the report. If you save the report as is, every time a user runs it he or she will have to respond to prompts for the beginning and ending dates for the report data.

You might need to specify some default start and end dates so that these prompts do not appear when a user is willing to accept a default range of dates. More importantly, you will definitely want the ability to specify the starting and ending dates for a report without requiring the user to respond to the report prompts. For example, you might want end users to select values from a custom form in your application. When a user specifies parameter values that differ from the default ones, you will want their selections to override the default values. In any event, you'll probably want your application to restore the default parameter settings after the report closes. The sample application in this section illustrates how to manage these processes.

There are several elements to the application. First, it needs to specify default values for the stored procedure through the report. By specifying the default values through the report, you preserve the integrity of the stored procedure's design for other applications. Second, the sample needs to override the default specification for the starting and ending dates. Third, the sample needs to restore the default parameter settings after the report closes. This is trickier than it might seem at first because you cannot modify the input parameters for a report other than in Design view. However, the report will open and close in Print or Preview mode. Therefore, the solution needs to reopen the report after it closes and then reset its parameter settings in Design view.

Use the Input Parameters setting on the Data tab of a report's Properties dialog box to set default parameters for a stored procedure that serves as the record source for a report. Developers can devise programs that override these default parameters. However, a user opening a report through the Database window will always have the default parameters assigned to the stored procedure. Figure 12-16 shows some settings for the sample application that cause the report to return data for all of 1998. The format for a parameter assignment is parametername datatype = value . If the stored procedure for a report has more than one parameter, you must delimit the setting for each parameter with a comma. Recall from Chapter 11 that SQL Server permits you to set a date parameter with a string. Figure 12-16 demonstrates this syntax.

click to expand
Figure 12.16: The Data tab for a report's Properties dialog box reveals the syntax for assigning parameter values to the Employee Sales by Country stored procedure.

Although it can be useful to have default settings for the parameters, programmatically setting the parameters at run time is more flexible. The following two procedures demonstrate such an approach. Notice that I include the specification of two public variables. You must declare public variables at the top of a module, before declaring any procedures. These public variable declarations aren't absolutely necessary for setting the parameters at run time, but they're very useful for restoring the original default parameters. I'll discuss restoring the default parameters immediately after I explain how to set parameters programmatically.

The first procedure starts by assigning the report name to one of the public variables, pubrepName . Next the code assigns values to two string variables, denoting the start and end dates for which you want the report to display data. These are local variables. The first procedure uses two different mechanisms for passing its variable assignments to the second procedure. The local variables, strStart and strEnd , travel between procedures as passed arguments. Notice that the public variable, pubrepName , does not appear in the argument list. This is because public variables are within the scope of all procedures in all modules and even all projects (if you're concurrently working with more than one project). Therefore, the second procedure can detect the value of pubrepName even though the first procedure assigned the variable a value.

The second procedure opens in Design view the report whose name is in pubrepName and sets a reference ( rpt1 ) to the report. You must use Design view because Access doesn't permit the specification of the InputParameters property from Print mode or Preview mode. Next the procedure saves the setting for the report's InputParameters property in initialparams . (Notice from the declarations before the procedures that initialparams is the name of the second public variable.) After saving the initial InputParameters property value, the procedure makes a new assignment for the property based on the start and end dates passed from the first to the second procedure. The procedure next saves the report with the new InputParameters setting. This step prevents Access from prompting users about whether they want to commit the report changes when the second procedure's last line opens the report in Preview mode.

The last line leaves the report open so that you can examine the values in the report. These values will all be for 1997. Recall from Figure 12-16 that the default parameter settings specify values for 1998. The assignment of arguments passed to the second procedure overrides the default parameter settings.

 PublicpubrepNameAsString PublicinitialparamsAsString     SubCallPreviewrptEmployeeSalesbyCountry() DimstrStartAsString DimstrEndAsString     'Setreportnameaspublicvariable pubrepName= "rptEmployeeSalesByCountry"     'Setstartandenddatesforreport strStart= "1/1/1997" strEnd= "12/31/1997"     'Passargumentstoprocedureforopeningreport PreviewrptEmployeeSalesbyCountrypubrepName,strStart,strEnd     EndSub     SubPreviewrptEmployeeSalesbyCountry_ (repNameAsString,strStartAsString,_ strEndAsString)  Dimrpt1AsReport Dimstr1AsString     'SettheInputParameterspropertyinDesignview DoCmd.OpenReportrepName,acViewDesign Setrpt1=Reports(repName) initialparams=rpt1.InputParameters rpt1.InputParameters= "@Beginning_DateDateTime='" &_ strStart& "',@Ending_DateDateTime='" &strEnd& "'" DoCmd.CloseacReport,rpt1.Name,acSaveYes     'Thenexaminetheoutputbasedontheparameters 'inPrintPreview DoCmd.OpenReportrepName,acViewPreview     EndSub 

When you're done examining the results from the parameter assignment, close the report. Closing the report starts a process that restores the default parameter settings. This process starts with the report's Close event. The report's Close event procedure opens a form named frmToResetReportParameters . This form has a timer event that concludes after the report closes. The Close event is the last to execute before physically closing the report. The following script shows the event procedure:

 PrivateSubReport_Close() 'Starttimerthatconcludesafterreportcloses DoCmd.OpenForm "frmToResetReportParameters"     EndSub 

The frmToResetReportParameters form has two event procedures and a label on it. The label explains the purpose of the form: to reset the parameters. The first event procedure handles the Load event. The procedure sets the form's TimerInterval property to 5000. This schedules a timer event five seconds after the form loads. Because the Close event procedure for the rptEmployeeSalesbyCountry report opens the form, the form's timer event occurs five seconds after the report closes. You can shorten this interval if you find it excessive. In turn , the timer event invokes the RestoreReportParameters procedure in Module1. As an option, you can add code to make sure the rptEmployeeSalesbyCountry report closed successfully. Recall that the AllReports collection supports this function. The two form event procedures appear next.

Note  

The Module1 qualifier for the RestoreReportParameters procedure is optional, unless you have a procedure with the same name in a different module.

 PrivateSubForm_Load() 'Settimerintervaltodelayaf 


Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

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