Creating ReportWriter Reports
4/8/2010
Contents
Scenario 1
Create a profile and entry 1
Starting ReportWriter 1
The Tables tab 3
The Fields tab 4
The Filters tab 5
The Preview tab 7
Creating a household and related entries 8
Creating a report 9
The Options tab 12
The Counting tab 13
Saving a query 13
Saving a report to Excel 15
Running a saved report 18
The Options tab 19
The Preview tab 19
The Filters tab 20
Questions 22
Scenario
SigOther entered our program alone on 3/10/2010. He leaves on 3/22/2010, not expecting to return. However, that evening, he returns with his significant other and child.
Create a profile and entry
We create a profile and an entry for him. He leaves on 3/22/2010.
Starting ReportWriter
We create a report to see his entry/exits and any households he is a member of. On the ServicePoint main page click on Reports or select ReportWriter from the Reports menu.
If you clicked Reports from the left side of the ServicePoint main page, this is the next screen you see. Click on ReportWriter.
The Tables tab
A new window opens showing the ReportWriter Tables tab.
We’ll select the Clients, Entry Exit, and Household Relationships tables.
The Fields tab
Then we’ll go to the Fields tab. Notice there is an additional table displayed – we did not select the Households table. Because of the way tables are linked in ServicePoint, selecting certain tables will cause other tables to be included in the report. We selected Household Relationships which caused the Households table to also be selected. We’ll select the fields we want to see by holding down the Ctrl key on the keyboard and clicking on each of the fields we want in each table. From the Clients table, we’ll select ClientID, FirstName, LastName, and Inactive. From the Entry Exit table we’ll select EntryDate, ExitDate, ReasonForLeaving, OtherReasonForLeaving, and Inactive. From the Household Relationships table, we’ll select DateEntered, HeadOfHousehold, Relationship, and Inactive.
The Filters tab
Next we’ll go to the Filters tab. This is where we’ll select the records we want on the report. Notice that a filter has automatically been created for each of the tables we selected. ReportWriter creates a filter for any table that has an Inactive field. We can remove any of these filters by clicking on them. For now, we’ll keep them all.
To add a filter, we’ll click on the drop down list for the Client Table and then click on the ClientID field.
Next, we’ll click the Add Filter button and filter for SigOther’s ClientID. (2519 in this example)
In this case, we want to see records where ClientID equals 2519. Then we’ll click the Add button.
Back on the Filters tab, we see the filter we just added.
The Preview tab
Finally, we go to the Preview tab. There are no records displayed. We asked for a report to show us every Entry Exit record for ClientID 2519 that has a Households record with the Inactive field equal to No as well as a Household Relationships record with the Inactive field equal to No. Since SigOther didn’t belong to any household, there were no Households or Household Relationships records and, so, there were no records to display.
On the Tables tab, we need to remove the Households and Household Relationships tables from the report.
Then when we go to the Preview tab, we see the Entry/Exit record.
Creating a household and related entries
SigOther returns with his family. We’ll create a household with all three family members.
Then create their Entry records.
Creating a report
Now we’ll create a report to show the family’s Entry/Exit and Household information. In ReportWriter, on the Tables tab, select the Clients, Entry Exit, and Household Relationships tables.
We’ll select all the fields we selected before. We’ll also select an additional field, GroupID, from the Entry Exit table and two fields, HouseholdID and Type, from the Households table.
On the Filters tab, we’ll have the same filters as before, including the ones for the Households Inactive field and the Household Relationships Inactive field. We’ll add two additional filters – one for the husband (in this example, ClientID Equals 2518) and one for the son (in this example, ClientID Equals 2520).
Note: Be sure to use OR. No single record can have a ClientID equal to 2518 and 2519 and 2520.
The Options tab
Next, we’ll go to the Options tab and organize the report. Select EntryDate Ascending for the order of the report. Click on the drop down arrows next to each of the fields and select the sequence to show the fields. Then click the Update Order button.
When we go to the Preview tab, we see our report. Notice the first record for SigOther. Even though he was not part of a household at the time of this Entry/Exit, the household information is still displayed. Also take note of the GroupID column. On the last 3 rows, you can see that SigOther, HOH, and Child Family all entered together.
The Counting tab
If we go to the Counting tab, we can see a breakdown of the report by total unique clients and number of duplicates, as well as a count of the number of occurrences of values in several other fields.
Saving a query
To save this query so that the report can be run again at a later date, we’ll go to the bottom of the Options tab and type a name for the query into the Save Present Query As box. The text to the right of this field indicates the date the query was made and the creating provider. Then we’ll enter a description of the query in the box on the right and click the Save Quick Query button. The screen will refresh and our query will be listed in the previously saved report list.
Saving a report to Excel
To save the report to a zipped Excel file, we’ll go to the Preview tab and click on Download Report.
The Download Report window will open and we’ll enter a file name to save this report as, and we’ll have to enter a password. We will need to remember this password in order to view the downloaded Excel file. Next, we’ll click the Save button.
The window will refresh and we’ll click on Click here to download file.
We’re given the choice of opening the file or saving it. We should always save it. What follows is what will happen if we opt to open the file. We select to open the file with Excel…
…and the file opens in Excel. The compressed file was password protected. Since we didn’t get the opportunity to enter our password, this is what we see:
So we will save the file.
Then unzip the file and enter the password.
Then we will see our report in Excel.
Running a saved report
For the purposes of this demonstration, let’s delete HOH Family from the household to see what our report looks like. (This is not the recommended way to remove a household member. In this case, you should enter a date removed on HOH’s record, change the household type, and assign SigOther as Head of Household.)
Now HOH is not a member of any household.
The Options tab
Now we’ll go into ReportWriter and select our report from the previously saved report list on the Options tab and click the Ok button to continue.
The Preview tab
Then we’ll go to the Preview tab. HOH Family is not listed because she is no longer a member of any household.
The Filters tab
Since HOH Family was once a member of a household, she does have a Household Relationships record. It was made inactive when she was deleted from the household. If we remove the filter on the Household Relationships Inactive field…
…when we go to the Preview tab, we can see her information. This only works because she was once a member of a household. Remember the first query we ran to see SigOther’s Entry/Exit and Household information – we didn’t get any records since she had never been a member of a household.
Questions
Question:
If an agency creates a quick query (report), can other agencies access that report?
Answer:
All quick queries saved by a System Administrator II are visible to all users under the "Options" tab. The data returned in these reports may differ based on the security configuration of your site. All reports created by users other than the SAII are visible only to that user's agency and the agency's children.
Question:
Why do some of the data fields in ReportWriter appear as "Restricted"?
Answer:
To ensure that no one circumvents the security model, any data that is not visible to a user in ClientPoint will always appear as "Restricted" in ReportWriter.