As of Aptify 5.5.4, Aptify supports the use of SQL Server Reporting Services (SSRS) reports. This topic provides step-by-step instructions for discusses creating a basic client-specific SSRS report that users can run from Aptify. If you already have experience using SSRS reports, you may only need to refer to the Guidelines section below, which provides a summary of the recommendations and requirements for writing SSRS reports for Aptify.
...
Aptify recommends that you follow these guidelines when creating SSRS reports for Aptify:
- Aptify strongly recommends that you use the base views in the entity definition database (typically, Aptify) as the source for SSRS reports rather than the base tables.
Aptify reports are list-driven. When you run a report, the system first creates a List that contains the records from the specified view. The system then uses this List as the source for the SSRS report. Therefore, to display the correct records in the report, you must add the vwListDetail base view as a data source for the report and link the vwListDetail.RecordID field to the ID field of the report's service.
Info If you create an SSRS report that does not include vwListDetail as a data source, the report will display all records in the service rather than only those displayed in the selected view.
- Do not define any Record Selection filters within SSRS reports, or attempt to create a report from a specific set of records from the selected view . Any filtering of the records should be done in the selected view.
- Aptify provides two sample SSRS reports, Person Roster_SSRS (for Persons) and Invoice_SSRS (for Orders), with the baseline Aptify product. You can use either of these reports as a starting point for your custom reports.
- You can find a report's SSRS report (*.rdl) file in the C:\Program Files\Aptify 5.5 folder on a client computer that has recently run the report.
- Alternatively, you can download the report file to your client computer's C:\Program Files\Aptify 5.5 folder by right-clicking the report's Object Repository Objects record in the Object Repository Administration dashboard and selecting Download Object from the pop-up menu.
- If the entity in which the report is to be associated with has field level security enabled, the Verify on First Refresh option must be cleared under the Crystal Report Options.
Step-by-Step Instructions
The steps below provide an overview of how to create a SSRS report for Aptify using the version of Report Viewer included with Visual Studio. This same process is generally applicable to other versions of Report Viewer, but you may want to refer to the documentation that came with your version of Report Builder for specific instructions.
...
Microsoft
...
...
- Determine what information you want the report to display.
- Identify the service from which users will be able to run the report.
- Identify which base views the report will need to access in order to retrieve all of the desired information.
- Sketch a design layout for the new report.
...
- In Visual Studio, click New > Project... > Visual Basic > SSRS Reports Application and specify the project details.
...
- These general instructions assume that you have selected the Standard option. Refer to the Microsoft user documentation for information on creating other report types.
...
- The Data dialog appears.
...
- This Data Source Name (DSN) is created automatically when you run a report in Aptify. Therefore, if this DSN does not already exist, run a report in Aptify first and return to this screen to select the APTIFY DSN.
- If you connect to multiple Aptify database servers in your development environment, you may want to check the APTIFY DSN to confirm it is linked to the correct server (from the Data Sources (ODBC) administrative tool).
...
- By default, sa appears in the User ID field.
- Alternatively, you can select the Trusted Connection option if your Windows user has administrative privileges for the Aptify database. However, for best results, Aptify recommends that you specify the sa user.
...
- Aptify now appears under the ODBC (RDO) folder in the Data dialog.
...
- You need to use vwListDetail in your report because reports in Aptify are list-based.
...
- Add the view for all entities that contain information that you want to display on the report.
- For example, in the figure above, the Selected Tables column also includes vwVehicles and vwRentalAgreements. You can use these views to create a Vehicles report that includes information from related Rental Agreements.
...
- The Link dialog appears. This dialog lets you specify how each of the views you selected should be linked together.
...
- Crystal Report automatically links fields that share the same name. In general, this is not a correct assumption for Aptify entities. You must clear these default links and add the links manually.
...
- This is not a requirement but it can make it easier to identify the links between the various tables.
- For example, you may want to move the vwListDetail table to the far left of the window since it is the first base view that the report will access (since the members of the list identify the records to include in the report).
...
- Select RecordID in the vwListDetail table. While holding down the mouse -button, drag RecordID to the ID field of another base view. Then, release the mouse button to create the link between these two fields.
- In the following example, the vwListDetails.RecordID is linked to vwVehicle.ID.
...
- For example, if you want to incorporate Rental Agreement information into a Vehicles report, link vwRentalAgreements.VehicleID to vwVehicles.ID.
...
- In the figure below, five Vehicles fields and six Rental Agreements fields have been moved to the Fields to Display column. Note that you can use the up and down arrows to arrange the fields in any order.
...
- Refer to the Crystal Reports user documentation for information on grouping.
- In the example figure, Rental Agreements will be grouped by a Vehicle's ID.
...
- Refer to the Microsoft user documentation for information on summaries.
...
- Refer to the Microsoft user documentation for information on group sorting.
...
- Refer to the Microsoft user documentation for information on charting.
...
- Refer to the SSRS Reports user documentation for more information on customizing reports.
- Use the Design tab to modify the report's layout and the Preview tab to monitor how design changes affect the report's appearance when data is added.
...
- From the menu bar, select Crystal Reports > Reports > Report Options.
- Clear the Verify on First Refresh option.
...
Visual Studio includes an add-on tool (SQL Server Data Tools – Business Intelligence (SSDT-BI)) to write SSRS reports. You find more information on using