Add Code to Customize Your Report

Add Code to Customize Your Report

You can add code that allows you to add options to your report. Use this feature to create an Advanced Report which receives an Option value. The Option value determines the processing type in the Visual Basic part of the report.

You can use Advanced Reporting Options to create one Advanced Report which you can modify at run-time. A value is passed to the Option through the Advanced Reporting profile. Visual Basic code is required to receive this Option value, as well as perform specific functions based on the value passed through the profile.

1. Open the Active Report Designer in Visual Basic, and click View Code.
   You see the Code dialog box.

2. Move the cursor to the end of the code, under the last End Sub line. Enter the following code. You must add this code into your Designer if you want to include any options in a report:
   Private Function GetOption(name As String) As String
Dim rs As Recordset
Set rs = m_db.OpenRecordset("Select * from pcre_parm where parmset="
& m_id & " and name='" & name & "'")
If Not rs.EOF Then

   GetOption = iif(isNull(rs("Val")), "" , rs("Val"))

   Else

   GetOption = "Invalid"

   End If

   rs.Close

   set rs = Nothing

   End Function
   When you paste the code into the designer code, one line appears in red font.

3. Place your cursor at the end of the first line of red code, just after parmset=" and press Delete on your keyboard.
   The red code is now on one line and is black.
   Next, add code that allows you to filter data in an Advanced Report using an Advanced Reporting option.

4. From Visual Basic, click View Code, if the code is not yet displayed on your window.
   You see the Code dialog box.
   Make sure that the two drop‑down lists at the top of this window read ActiveReport and ReportStart.

5. Enter the following code just above the line of code End Sub in the ActiveReport_Report Start() section.

   Set DAODataControl1.Recordset = m_db.OpenRecordset("select * from
targettablename where [column name]= '" & GetOption("OptionName") & "'", dbOpenSnapShot)

   The lines of code appear in red.

6. Place your cursor at the end of the first line of red code, just after from, and click Delete and then the spacebar on your keyboard.
   Some of the red code is now on one line and is black. Some of the code remains red.

7. Place your cursor at the end of the first line of black code, just after GetOption("OptionName"), click Delete, and then the spacebar on your keyboard.
   The red code is now all on one line and is black.

8. Make the following changes to the code you added:
   Replace targettablename with the target table name from the Advanced Report Rule.
   Replace column name with the Field Attribute Name that contains the Transaction Type.
   Replace OptionName with the Advanced Report Option Name.
   At the location indicated, add the belowcode. This code overrides the RecordSource located within the DAO.
   Set DAODataControl1.Recordset = m_db.OpenRecordset("select * from OptionName where [security description] <>’total’ and [transaction type]='" & GetOption("Name_trans_code") & "'")

9. Save the project.

Preview and Register the Report

Next, preview and register the report you created.

1. On the File menu, click Make ProjectName.dll.
   You see the Make Project dialog box.

2. Click OK.

3. From the Windows desktop, double-click the Advanced Report Tester button.
   You see the Advanced Report Tester window.

4. In the Report File (MDB or ADV) section, click Ellipsis.

5. Access the target folder, and select the Access Database (the name of the database that was defined in the Advanced Report Profile), and then click Open.

6. In the Select Report DLL Name section, type the Project Name. This is the name of the project that you just created in Visual Basic.
   After you populate this field, the Select Report Name section is populated automatically with the name of your designer. The Select Report Name section lists all of the designers in the project.

7. Click View.
   The report appears in a window. You can make changes to the report, including positioning fields or editing field formats.

8. To finish the cycle, copy the Project DLL (project) to the Auto Registry engine common files location directory on the PACE Server.
   The file is registered and moved to the destination directory automatically. The project and designers names are added to the PACE_MASTER.DBO.ADV_REPORT_LIST table.

9. Return to PACE and edit your Advanced Report Rule. Then, select the project and designer name from the Available Reports drop‑down list and click OK.
   The report appears in the Advanced Reporting folder in Reports Explorer when it is complete.

10. Click Finish.

11. Submit the Advanced Report Profile.
    The report appears in the Advanced Reporting folder on the Results tab when it is complete. The following shows an example Advanced Report with options.