View source for Kemp's Reporting Services

==Overview==
Has the management of your organization overtaken your life?  Do you schedule hours out of each week, or days out of each month, to run reports and review data in your system?  Do you wish you could spend more time on management and less time running reports and gathering data?  If you use Prime 16 hosted on Venture Technologies, we have just the thing for you:  Kemp’s Reporting Services (KRS)!

KRS is the newest, optional benefit offered for hosted Prime 16.  It gives you administrative support using automated/scheduled reports, data mapping, and long term comparison reports.  Instead of performing standard management by running through pages of plain data, KRS takes that data and provides it visually.  A simple glance at the graphics tells you all you need to know about your organization.  You can even use graphical mapping by county to tell your data story.

KRS is now available to administrative users in your program.  With this system, we provide more than 15 reports for your use.

Administrators can use these reports to keep abreast of events in their organization.  Like Prime, you can run these reports manually, when you desire.  However, you can automate these reports, so they are waiting for you in email when you arrive in your office, automating your management—allowing you to concentrate on managing your organization and letting the reports you desire come to you on your schedule.  Not happy with the reports provided?  You may modify existing reports and create your own.  The reports you create may also be automated and emailed when you want.

You can schedule each report to be run and emailed to you as often as hourly, daily, weekly, or monthly.  You can even specify a time to stop the reports, so they run for a period and then stop.  For a report that allows parameters (for example, the selection of running a report just for a specific office), you can schedule the report to run with different parameters at different times.

==Setup and Use	==

===Modifying Existing Reports===
To modify existing reports, administrators may use the Report Builder icon from the web portal.
[[File:Screen Shot 2020-04-01 at 9.05.07 AM.png|thumb|none]]


This starts the Report Builder.  A “Getting Started” box may show up first (shown here), close this box to get to the Report Builder main screen.   

[[File:Screen Shot 2020-04-01 at 9.09.03 AM.png|thumb|none]]


In the lower left corner, click the “Connect” link.

[[File:Screen Shot 2020-04-01 at 9.12.05 AM.png|thumb|none]]


In the “Connect to Report Server” box, type in:   http://jandc002-kps01/reportserver_kemps          Then click “Connect”.

[[File:Screen Shot 2020-04-01 at 9.13.08 AM.png|thumb|none]]


Once connected, this information should appear in the lower left of the Report Builder.

[[File:Screen Shot 2020-04-01 at 9.15.19 AM.png|thumb|none]]


Use the menu button at the top left to access the “New” and “Open” options for creating a new report and for opening an existing report, respectively.

[[File:Screen Shot 2020-04-01 at 9.17.32 AM.png|thumb|none]]


Clicking “Open” brings up the “Open Report” box.  Find and double-click on your organizations folder.

[[File:Screen Shot 2020-04-01 at 9.19.56 AM.png|thumb|none]]


Subfolders separate the various types of provided reports:

[[File:Screen Shot 2020-04-01 at 9.21.17 AM.png|thumb|none]]


Double-click on a folder to see the reports available, for example, here are the Multi Year Reports:

[[File:Screen Shot 2020-04-01 at 9.38.47 AM.png|thumb|none]]


Choose a report to open by double-clicking on it.  Here is the 3 years closed and opened by month report:

[[File:Screen Shot 2020-04-01 at 9.41.10 AM.png|thumb|none]]


To run the Report, click the “Run” button in the Home-->Views menu.  

[[File:Screen Shot 2020-04-01 at 9.48.20 AM.png|thumb|none]]


The report runs and displays its data:

[[File:Screen Shot 2020-04-01 at 9.47.07 AM.png|thumb|none]]


To get back to the design view, click “Design” in the Run menu.

[[File:Screen Shot 2020-04-01 at 9.50.05 AM.png|thumb|none]]


In the design view, the “Report Data” pane shows all the information about the data that will be used in the report:

[[File:Screen Shot 2020-04-01 at 9.51.21 AM.png|thumb|none]]


The “Data Sources” folder contains the connection information to get to your database.  We provide a single connection item for your report system.  That connection information should not be changed.

[[File:Screen Shot 2020-04-01 at 9.52.38 AM.png|thumb|none]]


The “Datasets” folder contains the data that will be used in the report.  There may be more than one data set, depending on the nature and complexity of the report.

[[File:Screen Shot 2020-04-01 at 9.53.46 AM.png|thumb|none]]


If your report has parameters that must be entered, information about those parameters is shown in the “Parameters” folder.  

[[File:Screen Shot 2020-04-01 at 9.54.56 AM.png|thumb|none]]


Finally, other fields that are available in the report are shown in the “Built-in Fields” folder—this includes items such as page number, report name, etc.

[[File:Screen Shot 2020-04-01 at 9.56.34 AM.png|thumb|none]]


You can get more information about the Report Builder and how to use it from this [https://docs.microsoft.com/en-us/sql/reporting-services/report-builder-tutorials?view=sql-server-2016&redirectedfrom=MSDN Microsoft website].


When working with data sets, if you are ever prompted to “Enter Data Source Credentials” as shown here, simply select “Use the current Windows user” and press OK:

[[File:ReportBuilder11.png|thumb|none]]

===Creating a New Report===

Within Report Builder, click on the File button and choose New.  

[[File:Screen Shot 2020-04-01 at 10.03.45 AM.png|thumb|none]]


Choose “New Report” in the “New Report or Dataset” box.

[[File:Screen Shot 2020-04-01 at 10.05.25 AM.png|thumb|none]]


On the right, choose the type of report you want to create.

[[File:Screen Shot 2020-04-01 at 10.06.34 AM.png|thumb|none]]


For this example, we are going to choose “Chart Wizard”.

[[File:Screen Shot 2020-04-01 at 10.09.03 AM.png|thumb|none]]


In “Choose a Dataset”, select “Create a dataset” and choose “Next”.

[[File:Screen Shot 2020-04-01 at 10.10.00 AM.png|thumb|none]]


In “Choose a connection to a data source”, select “Browse”.

[[File:Screen Shot 2020-04-01 at 10.11.38 AM.png|thumb|none]]


Double-click on your connection in your organization’s folder (or single-click and choose "open").  Note that you may have to double-click on the server connection, then your organization's folder to see the Datasource

[[File:Screen Shot 2020-04-01 at 10.12.57 AM.png|thumb|none]]


The Datasource should be shown.

[[File:Screen Shot 2020-04-01 at 10.15.00 AM.png|thumb|none]]


Click “Next”.  

[[File:Screen Shot 2020-04-01 at 10.15.57 AM.png|thumb|none]]


Again, if you are prompted to enter data source credentials, click the “Use the current Windows user”:

[[File:ReportBuilder15.png|thumb|none]]


Use the “Design a Query” box to manually set up your data set.  Choose tables and fields on the left.  As you select fields, they will show up in the top right.  You can apply filters (criteria) in the lower right box.  Here we have chosen a few fields from the ClientsW table, and have applied a filter to only show those with a case type of staff (S).  You can also create the query manually by clicking on the “Edit as Text” button.

[[File:Screen Shot 2020-04-01 at 10.18.55 AM.png|thumb|none]]


Click Next to go to the "Arrange chart fields" page.  This is where you will determine what categories you are interested in and what values you want to show.  Here we are categorizing based on problem code (PCODE) and showing the value of the count of the cases (Count(Casenum)).

[[File:Screen Shot 2020-04-01 at 10.22.15 AM.png|thumb|none]]

Press Next to see an example preview.

[[File:Screen Shot 2020-04-01 at 10.23.19 AM.png|thumb|none]]


Press "Finish" to see the report in design view.  You can change the title or other parts of the report.

[[File:Screen Shot 2020-04-01 at 10.24.29 AM.png|thumb|none]]


Again, for assistance with using this form, please refer to the tutorials located [https://docs.microsoft.com/en-us/sql/reporting-services/report-builder-tutorials?view=sql-server-2016&redirectedfrom=MSDN here].

==Scheduling Reports - Automatically Running Reports - Subscribing to Reports==

To schedule and automate reports, run the “Kemp’s Prime Reports” system.

[[File:ReportBuilder17.png|thumb|none]]


This opens Internet Explorer pointed to the report system.  Enter your Venture username and password in the “Windows Security” box:

[[File:ReportBuilder18.png|thumb|none]]


After entering your credentials, give the system a moment to set up your environment.  When complete, you should see your organizations folder in the report system.

[[File:Screen Shot 2020-04-01 at 10.35.01 AM.png|thumb|none]]


Click on that folder to view the reports and data connections available.  Remember that the provided reports are broken up into several subfolders.

[[File:Screen Shot 2020-04-01 at 10.36.41 AM.png|thumb|none]]


Navigate to the report you want to automate.  Click on the ellipses button and choose “Subscribe”:

[[File:Screen Shot 2020-04-01 at 10.38.20 AM.png|thumb|none]]


Enter appropriate values in the “New Subscription” form.  First, enter a description for the subscription.  This should be descriptive enough to tell multiple subscriptions apart as you may have more than one subscription for the same report.  For example, "Error Report to Mark at beginning of month".  

[[File:Screen Shot 2020-04-01 at 10.42.27 AM.png|thumb|none]]


Scroll down to the "Schedule" section and click on the "Edit schedule" option.

[[File:Screen Shot 2020-04-01 at 10.44.02 AM.png|thumb|none]]


Use the "Schedule details" page to determine how often you want the report to run, together with starting and ending date for running the report.  If you want the report to run forever into the future, leave the "Stop this schedule on" box blank.

[[File:Screen Shot 2020-04-01 at 10.45.07 AM.png|thumb|none]]

Click apply to return to the main subscription page.  In the "Destination" section, choose email and enter an appropriate email address(es) for the "To", "CC", "BCC" entries.  If you are sending this to someone else, you may want to put your email address in the "Reply-To" entry, so if someone replies to the email, it will come back to you and not the automated system.  (The automated system is NOT monitored for emails).  You can also put in a handy, descriptive Subject, or leave  it as is.  A comment might also be nice.

[[File:Screen Shot 2020-04-01 at 10.50.14 AM.png|thumb|none]]


If a parameter is required for a report, it will appear in the “Report Delivery Options” page.  Enter the parameter for that subscription—you can have multiple subscriptions to the same report, each with its own parameter entry.  This is where you can enter a different parameter for each subscription, so you get different reports to different people.

Finally, press "Create subscription" to actually create this subscription.

[[File:Screen Shot 2020-04-01 at 10.51.48 AM.png|400px]]

==Manually Running Reports==

To run a report, open it using Report Builder, then click the Run button as demonstrated in the Setup and Use/Modifying Existing Reports section.  Alternatively, log into the Kemp’s Prime Reports system, and click on the report link there.

==Example Reports==

The following are examples of the reports provided and how they can be useful in managing your organization.

===Error Reports===

====Detailed Weekly Error Report====

Shows the errors that exist in the system (as of the moment the report is run) and the name of the person who is shown the error.  Also shows the number of errors by staff person, and the number of times each error is made.  This information can be helpful in assuring errors are being corrected in the system, as well as knowing what type of training may be necessary. 

We suggest scheduling this report to run once a week during the early morning hours (e.g., 5 AM).  Keep in mind that times are shown local to Central time.

Example of specific errors and the staff person who would see them, with specific case information:

[[File:ReportBuilder30.png|thumb|none]]

Example of the number of errors a particular staff person is shown in the system:

[[File:ReportBuilder31.png|thumb|none]]

Example of the number of times an error appears in the system:

[[File:ReportBuilder32.png|thumb|none]]

===Multi Year Reports===
Multi Year Reports generally run three years from the last full month.

====3 years case and non-case time====

Shows the total of time broken down into two graphs.  One shows case time by month; the other non-case time by month.  As it covers the last three years, it gives management an idea of how time is being spent on case handling versus non-case time.  This can be helpful in diagnosing problems, especially if case time should inexplicably dip.  The three-year duration gives a baseline against which you can compare the time breakout.

[[File:ReportBuilder33.png|thumb|none]]

====3 years closed and opened by month====

Shows the cases that were opened and closed during each month over the last three years.  One graph shows the cases closed during that month.  The other shows the cases opened during the month.  This can be helpful in diagnosing staff changes or other issues that may be represented by a remarkable change in opening and closing cases.

[[File:ReportBuilder34.png|thumb|none]]

====3 years eligibility total and accepted====

Shows the total eligibility intakes made by the program over the last three years.  A second graph shows the eligibility entries that were accepted (where the application went beyond the eligibility stage).  Like other reports, this can help management in determining whether there are efficiency issues that may exist in the organization.

[[File:ReportBuilder35.png|thumb|none]]

===Open Cases===

====Month Opened_Office_unit_Case Type====

Graph 1 – Shows the count of currently open cases by the month when they were opened.  This can be helpful in resolving problems with timely closure of cases.  Perhaps, in the example data shown below, a case was opened in 2007 and is still open today.  (Only months in which a case was opened, that still remains open, will be shown in the graph.)
Graph 2 – Shows the count of currently open cases by the unit to which they are assigned.  This can help with overall management in knowing the current case load of specific units.
Graph 3 – Shows the count of currently open cases by the office to which they are assigned.  As with the unit report, this can help with knowing the case load of offices.
Graph 4 – Shows the count of case entries by the case type assigned.  This will help in knowing the number of case entries that may be awaiting assignment to a PBI attorney, holding for a decision on whether to accept as an actual case, etc.

[[File:ReportBuilder36.png|thumb|none]]

====Problem Codes_Days Open_People_Case Type====

Shows the count of cases by the main problem code group set in those cases, the average number of days open of cases by main problem code group, the total number of adults and children represented in each main problem code group, and the number of cases within each case type.

[[File:ReportBuilder37.png|thumb|none]]
[[File:ReportBuilder38.png|thumb|none]]

====Staff Member====

Shows the number of open cases assigned by staff person.  This report is very helpful in reviewing case assignment and load of staff members.

[[File:ReportBuilder39.png|thumb|none]]

===Prior 6 months===

These reports all show data from the prior, full six months.

====Case Time by Office====

Shows the total time entered over the last 6 months by office.  Sudden dips or surges change indicate changes in staffing or the caseloads assigned.

[[File:ReportBuilder40.png|thumb|none]]

====Closed Cases by Month====

This report allows the entry of an office number to limit the data displayed to one office.  You can, when you subscribe to this report, choose the office on which you report.  Subscribe to the report multiple times and enter different office numbers to get reports on each office.

As with other reports, sudden increases or decreases in closed cases can be investigated.  The 6-month review allows you to compare the most recent months against one another.

[[File:ReportBuilder41.png|thumb|none]]

====County Map Closed Cases====

Show the number of closed cases by county in your service area.  Use the maps for reports and other articles you may publish.

[[File:ReportBuilder42.png|thumb|none]]

====Eligibility Intake by Month====

This report requires the entry of the office number and then displays the total eligibility entries by month for that office within the last 6 months.  You can investigate any sudden changes to determine the reason.

[[File:ReportBuilder43.png|thumb|none]]

====NonCase Time by Office====

This report shows totals of non-case time by month for each office.  Is one office remarkably different from the others?  Is there a valid reason for that, such as being an intake center?

[[File:ReportBuilder44.png|thumb|none]]

===Prior Month===

These reports run for data within the prior full month.  These reports are good to run at the beginning of a month to get the prior month’s information.

====All Time by Staff====

Displays the total of each person’s time entries during the prior month, broken out into color representing the various activity types (Case, PBI Case, Matter, Support Activity).  You can quickly review staff members’ entries to ensure total time is correct, as well as be able to tell how their time entries break out by function.

[[File:ReportBuilder45.png|thumb|none]]

====Case Time by Staff====

Shows the case time entries of staff during the prior month.  See how each staff member’s case time entries compares against others.

[[File:ReportBuilder46.png|thumb|none]]

====Closed Cases by Staff====

This report requires the entry of an office number.  Staff who closed cases in the last month are displayed together with the number of cases they closed during that month.  Quickly compare case closures within an office, across staff in that office.

[[File:ReportBuilder47.png|thumb|none]]

====Eligibility Intake by Staff====

Shows the number of intakes by a staff person in an office.  This report does require the entry of office number to run.  Compare intake staff in an office.

[[File:ReportBuilder48.png|thumb|none]]

====Matter and Support Time by Staff====

Compare the amount of matter and support time entered by staff during the last month.  Each staff person has two columns—one representing matter time and the other representing support activity times.    Make sure staff are focusing on appropriate time entries.

[[File:ReportBuilder49.png|thumb|none]]

====Top 10 Eligibility Problem Types====

See the top 10 eligibility problem code types within a particular office.  Office number is required to run this report.  See if your office is focused on the right problem types.

[[File:ReportBuilder50.png|thumb|none]]