Report Creator

Using the Report Creator is a four-step process: Setup a Record Source, Generate a Report, Run Existing Reports, and Modify (and perhaps Save) Existing Reports.

a) Setup a Record Source We’ve already set up record sources for most reports that are needed. We include this section for informational purposes only. Skip to Section b if you are not interested.

b) Although this may ‘sound’ difficult, a record source is merely choosing what table(s) you are going to build a report on. Since this is a Database Administrator function, it is located under the Admin and you must have permissions to do this. If you don’t have permission to set up a record source, skip to Generate a Report below.

c) A record source can be a table. The problem with this is that many tables store information as codes rather than their English description. For example, the ClientsW table stores the problem code as 63 rather than ‘Landlord/Tenant’. This is the advantage of using queries. They can hold the information for multiple tables. In the above example, our query would include ClientsW and subFunds. This allows us to use FundsName, the field that holds the actual name of the funding code.

d) You would build the query in Microsoft Access. In order to recognize the queries we’ve created, we have named them qrc_Name (with Name being your name describing the query, such as qrc_StaffMembersByOffice). The next step is put this record source into the Report Creator. Since you have been provided with a group of record sources, you could either use or edit them.

e) To increase the speed of some of these record sources, we linked them not to a query but to a View located on the SQL Server. This makes them run much smoother and faster.

f) Generate a Report

1) The next step is to build and generate the report you want. The first thing you do is go to Report Report Creator.

2) This opens up the Report Creator page. What we now need to do is select the record source of the report. If we followed the naming convention, supporting queries begin with qrc_ and reports begin with rc_. The system will now show the reports already generated using that record source.

3) Clicking on the pull down reveals a list of record sources we can use to build a custom report.

4) Once we choose a record source, reports already built using that record source show up. We can run a report, modify it to meet our need, and then save it under a new name. We can also start a new report.

5) The first screen we see allows us to choose what columns to include, change their fonts, change the colors, add sums, averages, and either hard code a title in or allow the program to prompt us for one.

6) Building a new report is very similar to modifying an existing one. Let’s pick one to modify so most of the choices have been made. Here we chose Client Open by Office.

7) This is the resulting report

8) After being prompted for Office number, the report provides open cases by Office and then in each office by Advocate. The reports that are created are compatible with Microsoft Access and can be modified with Access. However, they have the special Edit report feature. This opens a screen just like building a new report but allows us to modify the existing one.

9) Here we’ve selected Add Column and we are going to put the Case Type field in. We also asked to be prompted for the title when the report runs. Because we want the new column to stand out, let’s change its color. Click on the Change Colors button and select yellow.

10) Now that column will show up as yellow.

11) If we choose Next, the screen allows us to choose how we want to group the information that shows up. Let’s change it from Office and Advocate to Office and Funding Code. We’ll choose the Funding Code name rather than number.

12) We will group first by Office and then by Funding Code Name.

13) When we press Next, we get to choose the criteria for the report. This can be hard-coded in, so that it never changes, or we can choose to be prompted for a value. By choosing a blank value for Rclosed (reason closed), it will only use open cases. Here we changed from being prompted (notice the check) for the Offices to being prompted for a County. Sort Order can also be changed here.

14) If you forget something, you can hit the Back button and make any change. The last screen allows you to choose how the report prints.

15) Here is the resulting report. If you like, you can click on the Edit Report and go back to change the criteria or appearance using the Report Creator menus.

16) If you prefer, you can choose to modify or tune the report directly using Access, just as a standard Access report. This is the Access view of the report.