Beauvais Reports in Prime 14: A Primer
Part Two – Create Custom Reports
No matter how many reports you have in case management, there always seems to be a need for another. There are many good reasons for this: a new funder demands additional information, LSC asks for a different age group, managing attorneys want a new way to review casework; etc.
Prime 14 gives you many ways to create the custom reports you need:
1. Report Creator
2. Custom Report Builder
3. Search Screens
4. Microsoft Queries
5. Microsoft Reports
6. Export to Excel
7. Third Party Software
1. Report Creator
The Report Creator is an extremely powerful wizard that allows you to generate very complex reports by answering simple questions and using a ‘What you see is what you get’ interface. It allows adding or removing fields, grouping, totals, sorting, setting criteria, setting prompts, changing colors, etc. Using the Report Creator normally involves four major tasks:
Setup a Record Source
Create a Report
Run the Reports, and
Modify the Report (and perhaps Save it)
We’ve done most of the work for you in setting up record sources and creating reports. This section concentrates on showing you how to Run a report and then Create a new report by modifying an existing one. Modifying an existing report is very similar to creating a new report from scratch.
Example:
a. Start the Report Creator
Go to the top line ribbon bar and choose Reports. Then select the choice Report Creator.
b. Select your Report Area
Under number 1, choose Clients.
c. Pick a Report
Under number 3, pick the report rc_ClientsClosedFunds
d. Modify the Report
Under number 3, click on the ‘Run and/or Modify’ button.
e. Enter the Parameters
Put in a beginning date closed and ending date closed. Enter a beginning office number and an ending office number. Enter a beginning funding code and an ending funding code. Your report should now appear.
f. Choose to Modify the Report
Click on the button ‘Edit Report’.
g. Edit the Report
The first screen that appears looks like the report and allows us to choose what fields to include, change their fonts, change the colors, add sums, and averages. You can choose to either type in a title or have the program prompt you for one.
i. Title – You can type in a new title name or click the box that will prompt us for a name when we run the report.
ii. Add or Delete Columns – You can add a new field by clicking on an existing column and then pressing the Add Column button. A new column appears to the right of the column you clicked on. Use the pull down to select the field you want the column to hold.
iii. Field Choice include full name – Note at the end of the list of fields on the pull down are choices that include the full names for choices. This means you can include the name instead of the code for a:
Problem Code
Staff Member
Funding Code
Office
Unit
I added in Legal Issue County next to First Name. You can add or delete multiple fields.
iv. Add or Sum – When appropriate, you can put an Add or Sum summary for a field. This choice appears under the example report.
v. Color a Column – To make a column stand out, click on ‘Change Colors’ button. Then click on the palette appearing over the column you want to change. Click on the color you want and then OK. Answer NO to the next question or all the columns will change to the color you selected.
vi. Group the Information – Click on the Next button to move forward. The next page allows us to set how to group the information. You group by up to four fields. This report is set up to group by Office and Funding Code. We could choose to display the Funding Code first, and then show the break down of cases by Staff Number under each funding code. Change the choices to read Funding Code and Staff Name. Leave FundsName untouched and change Office_Nam to Staff. Press Next.
vii. Set the Criteria – We can then set our criteria. This can be done by choosing a field and typing in the values. You can also click the box titled ‘Prompt’ and the report will ask you for values when run. You can have up to five criteria in the report. (this could be expanded by changing the code). You can make changes or just leave the existing criteria in place. Substituting Rclosed for Dclosed and choosing a blank value would provide only open cases. Click on ‘Next’.
viii. Go Back – You can go back to previous screens and adjust your choices by pressing the Back button.
ix. Choose how to Print the Report – The next screen will let you choose how to print the report and whether you want row shading, etc. It will automatically set the printing mode to landscape if it appears needed.
x. Click on Finish – Click on Finish to get a Print Preview of the report. You can press on Edit Report if you want to go back and make modifications.
xi. Save the Report – When you close the report, it will ask you if you want to save the report. If you do, follow the naming convention. In our example we would name it rc_ClientsFundingCodeStaff.
xii. Modify the Report in Access – Because the report that is created is in the Access Report format, you can go in and tune it using Microsoft Access. This means you can change colors, groupings, fields etc. using Access.
xiii. Share the Report – If you want others to be able to use the report you will have to copy it to the Master copy of the front end and have it pushed out to all users.
2. Custom Report Builder
The Custom Report Builder is a utility to make writing and saving your own queries easier. The advantage of this Utility is that it can be made available to others, since it stores the created SQL language in a table on the Server.
Example:
a. Go to the ribbon bar and choose Reports and the Report Selector. On the Report Selector look under Specialized/Admin Reports and choose ‘Custom Report Builder’.
b. Choose whether you want to base your report on a particular table, query, or both. The reason to choose a query is that you can use multiple tables in your report.
c. The items that you can select appear on the right.
d. Select the table you want to base your query upon.
e. Once you have made your selection, note that the fields from that table are shown under Item 2 – Choose fields. This item allows you to select which fields to include in your report.
f. When you select a field by clicking on it, the right-arrow button in the middle will then become available (it is no longer gray). You can click on that button to move the field to the right box (Fields to Output). You can also choose the field by double-clicking on it.
g. Items 4 (Criteria) and 5 (Sort) are optional. If you wanted to return everything in the table and in no particular order, you could leave these blank. If you want to limit the amount of information returned, you will have to put something in the Criteria selection boxes.
i. Criteria (Item 4) allows you to specify what data you want returned. Perhaps you only want a certain problem code. You would drop down the first field under Criteria and change it to Pcode. You will note that only the fields you selected in item 3 are available. Additionally, when you select a field, the computer assumes that you want to match the field (thus the = sign appears in the box immediately following your selection). On the right, you set the value you want to equal. For example, you can use the drop down box under value to select ‘63’ for Landlord/Tenant.
After that, you notice a box labeled and/Or, this allows you to tell the computer that the data returned must meet all the criteria you specify (AND), or that data meeting any one of the criteria should be returned (OR). If we wanted to also include divorces, we would select OR and then put in Pcode on the next line followed by the = 32.
ii. Sort (Item 5) will allow you to specify how you want the data sorted. Again, the data field will only display those fields we selected in Item 3. The computer again assumes that we want the order Ascending.
h. To get the data, you have to select how you want Clients to output it. Item 6 (Output) allows you to do this. The Print feature will merely show the data on the screen in a print preview. From here you could send the report to a printer. To Text and to RTF will put the data in plain text or Rich Text Format file. It requires you to specify a file name and place for it to be saved. HTML will put the data in HTML format (web based). To Excel will put the results in an Excel spreadsheet and save the file. Leave the option as it (Print), and click on the Produce Form button to look at and/or print the final report.
3. Search Screens
Search Screens can act as easily understood mini-report writers for you. When run under Access 2007 or later, you can use greater than, less than, equal, between, contains, by the week, month and other conditions to find the data you want. As discussed later, you can also export your Search Screen results to Excel.
The first step in using a Search Screen is deciding what you are looking for. There are Search Screens for all the major tables such as Clients, Eligibility, Time, Calendar, Callback, Conflicts, etc. In some instances, such as Clients, several search screens are available (Clients, Conflicts, PBI Clients).
Since all the Search Screen work virtually the same, learning how to use one means you will understand how to use all of them.
Example: Working with the Client Search Screen
Open up the Clients Search Screen from the ribbon bar (Search, Client, Clients).
a. Speed Bar
The first major feature we see is the speed bar on the left. By clicking on one of the selections we can quickly see:
Only my cases
Cases from my office
Cases from my unit
Cases where I’m listed as the Intake Worker
Cases where I’m listed as the Co counsel
b. Search Field
A second method of finding things is to put a value in the search field next to Client’s Last name. If Smith is typed in and Enter is tapped, a list of Smiths appears.
Note that this list can be affected by the Speed Bar. For example, if we choose to find ‘Smith’ while the Speed Bar has Office tagged, we will only see Smiths that were handled by my office.
A trick that can make finding a client MUCH easier is that the search for the client’s last name allows you to use first name also. So instead of putting in Smith and then having to scroll through the list of Smiths, you can enter Smith, John. This will immediately display only the clients named John Smith.
Note that where Client’s Last Name appears is a pull down. This allows you to select almost any field on the intake to Search by, such as address, zip code, county, etc.
In Search Screens you can use wild cards to modify your search. Putting Sm* in the client name field and tapping Enter brings up Smith, Smudly and Smurr. The asterisk (*) represents multiple characters. A question mark (?) represents a single character. If you put J?N next to the Search Field that’s been changed to look for Client First Name, it will display Jan, Jen and Jon, but not John or Jane.
Wild cards are also useful for quickly bringing up cases where you are unsure of the spelling of a client’s name. For example, if you know there is only one client with the name Wzlenski in the ClientW Table, you could search by using Wz*.
You can also use wild cards to find values for fields other than name. For example, change the pull down from Clients Last Name to Address. Then put *Main* in the Search Field and tap Enter. All the clients living on Main Street, Main St., Main Ave., etc. will be displayed.
How do we know they live on these streets? You can choose a column, go to the top and change the pull down from city to address to see the addresses of the clients we selected above.
How do we change the order in which the Search Screens display the results? From the Ribbon bar choose Home (or in the newer versions of Prime 14 you can also choose Search). What appears is a group called Sort & Filter. This allows you to click on any field on the search screen and then choose A to Z or Z to A to sort the screen. Advanced users might want to look at Advanced Options that would allow them to sort on multiple fields at once (for example, sort a list of cases first by office and then by staff number in each office).
Pressing the Arrows that appear above the grid allow you to go back to a previous search you just performed. You can then go forward to the search you just left.
Search fields are not cumulative. Choosing one value will replace the previous search.
c. Sorting
If you searched for a client by last name, such as Smith, you’ll notice that the list that appears might not be sorted by first name. Alphabetizing by first name (or any other field) is easy to do. Click on any first name with your left mouse button. Then click with your right mouse button and choose (A to Z) from the Filter & Sort group. Choose Descending (Z to A) to order the list by first name from Z to A. This also works with other fields such as date opened.
d. Drill Down
A third way to search is to use the ‘drill down method.’ Highlight all or a portion of a field value on the screen and go to the Filter & Sort group mentioned above. Under Selection you can find values that:
Equal your selection
Do not equal your selection
Contain your selection
Do not contain your selection
These choices vary dependant on the type of field chosen, e.g., number, text, date, etc.
If you click on a date, then click on the filter icon in the Sort & Filter group, a menu shows up next to the date that allows you to select date filters. Clicking on this date filter choice presents you with 40 different date filters. These filters include Before, After, Between, This Week, This Quarter, Next Month, Last Month, This Month, etc.
If you then pick another field value to filter by, the results are cumulative.
Example: If you click the value ‘1’ under Snum (staff number) and then tap Selection, Equals 1, all of advocate number 1’s cases are displayed. Next, click on ‘63’under PC (problem code) and Selection, Equals 63 to get all of advocate 1’s Landlord/Tenant Cases. Then click on a Date Closed that is blank and Selection, Equals Blank to get all of advocate 1’s open Landlord/Tenant Cases. Last, click on Atlanta and press Selection then Does not include Atlanta to get all of advocate 1’s open Landlord/Tenant Cases where the clients doesn’t live in Atlanta.
You can search using only a portion of a field. For example, highlighting the ‘/14’ portion of the Date Opened Field will display all of the cases that were opened in 2014. The drill down method can be made even more useful by changing the fields displayed on the screen and using those for a search.
e. Stop the Search from Disappearing
To prevent the Search Screen from disappearing when you return to it after looking at a record, press the Status Button before selecting the client. The label changes from ‘Search Closes’ to ‘Search Remains Open’. When done looking at the intake, press the Close Button to return to the Search Screen. Before closing the intake, you can add a Time Slip or Calendar Slip by pushing the appropriate button on the third Tab of the intake.
f. Print
Print the list by choosing Add-Ins from the ribbon bar, then Print Preview. You will see a preview of the printout of your search screen, including any new fields chosen to be displayed.
4. Microsoft Queries
Microsoft Queries are used by experienced users of Prime because of their speed, flexibility and ease of use. We spend a whole day at our national training covering just some of the features of queries, so what follows is a very basic example of setting up and using a query.
Example: Lets assume that we want to display a list of clients who are 60 years of age or older and are below 100 percent of poverty for our open cases. This is a very simple query to setup and run.
a. Click on the Create button on the Ribbon bar.
b. Select Query Design
c. When the grid appears, double click on ClientsW and then Eligibility to put them in the gray area above the grid (you can also click on the Show Table button to add them after highlighting). Close the Show Table Window.
d. If a line doesn’t join the two tables, point at the Casenum field in ClientsW and drag and drop it on to the Casenum Field in Eligibility.
e. Double click on the Casenum, Clname, Cfname, Dclosed, Age, Adults and Children fields in ClientsW to add them to the grid. (You can also drag and drop them one at a time.)
f. Double click on the PovPercent field in Eligibility to add it to the grid.
g. We now need to restrict which cases are displayed. On the criteria line under the Age column put >= 60. On the criteria line under PovPercent put < 100. A case is open if it isn’t closed. Put the word Null on the Criteria line under Dclosed. (Null means blank.)
h. On the ribbon bar, choose Design. There is a red exclamation button that shows up that says Run. Press that to see the results of your query.
i. If you want to revise your query, click on the Home choice on the ribbon bar and press the Blue Triangle which puts you back in the Design View. ( you can also right click on the query tab if you have tabs set or the query itself and choose Design View)
5. Microsoft Reports
A full discussion of using Microsoft Access reports is beyond the scope of this article. You can create your own basic report by going to the top line ribbon menu, choose Create, Report Wizard and then follow the prompts. Hint, if you choose a query rather than a table as a record source, it allows multiple tables in your report. For example, if we chose the query we created above with seniors below a certain poverty level, we would be including both the Client and the Eligibility table in the report.
An advantage of a Report over a Query is that it allows you to have totals, sums, counts, groupings, etc., while at the same time showing a list of the cases. Queries usually will show only one or the other. Reports have a more professional look when printed.
6. Export to Excel
In some instances you may want to export your data to an Excel spreadsheet in order to use the report writing capability of that software. There are two primary ways to do this in Prime 14.
a. Use a Search Screen
Most of the Search Screens in Prime have a button that allows you to export your information to Excel. Pressing the button will export the cases displayed on the screen directly to an Excel file. You can also enter the name you want on the Excel Spreadsheet tab.
There are two options when exporting to excel this way.
i. Choose to export all of the fields
When you click on the export button, this is the default setting. All the fields for the records you chose will be sent to Excel.
ii. Choose to export only the displayed fields
If you uncheck the box after pressing the export button, only the fields being displayed on the screen will be sent to Excel. You can change which fields are sent to Excel by using the pull downs at the top of each column to change which field is displayed. For example, you might change the city field to county, the reason closed to number of adults and the staff number to the number of children. These fields would be the ones sent to Excel.
b. Generate the Report to Excel
i. From a Criteria (QBF) Screen
A little used option shows up on the Criteria Screen when running reports. This option allows the user to export the report they are running to several different formats, one of which is Excel.
Although this option exists, some reports export better than others. You might want to try out a particular report, and if you’re not happy, you might try the cut and past method mentioned below.
ii. From Other Reports
c. Use a Ribbon Bar Choice
There is a ribbon bar choice that can be clicked on to send your results to Excel. Generate a report. When the preview appears on the screen, choose the Print Preview choice on the ribbon bar. One of the buttons that appears is Excel. If you click on that your report will be sent to Excel.
d. Cut and Paste
In some instances, you can highlight the contents of the report, copy it using Ctrl-C, and then paste it into Excel using paste or Ctrl-V. We’ve seen very good results using this method on Access Reports (reports whose name begin with an R).
7. Third Party Software
In some instances, you might have an employee this is experienced with a third party report writing software. You might also have a need for a particular report not otherwise available. Because Prime uses Microsoft SQL Server to store its data, which is one of the most widely used database programs, almost any third party software will work with Prime. One example of this is Crystal Reports.