Lesson 6

Template Tutorial Text

Exit

Introduction Screen

Template reports allow you the greatest flexibility in creating reports in MERLIN.net. This Template Tutorial will assist you in developing your own customized reports based on options you select from a series of screens.

When you access the Predefined reports in MERLIN.net, you are prompted with choices for certain parameters or filters (e.g. Accounting month) which specify what slice of the data is to be selected. This assists in restricting the size of the returned report.

In Template reports, you have the ability to create a brand new analysis from scratch. This is sometimes referred to as ad hoc reporting. The most important aspect of creating a template report is establishing filters to set the bounds for the returned data. Because your reports are retrieved from a massive database containing millions of records, it is important that you carefully plan and narrow the focus of your question as much as possible. Due to limitations of the Internet, 1000 rows of data is the most you will be able to retrieve in MERLIN.net, so you must outline your report carefully.

Creating a template report only requires you to navigate through four or five screens and will give you the capability to:

Select a template
Select the columns to use for the query
Group or sort records
Choose a report or chart as output
Filter the data
Create a report title and subtitle
Choose the columns to show in the report
Add subtotals and totals
Format the font style and size
Save a report

The Tutorial has been divided into four lessons, and each lesson should take no more than 20 minutes of your time. They are categorized by the focus of each lesson so if you need to refresh your memory at a later time, it is easy to pick out the appropriate lesson.

When using this Tutorial you will be asked to go through each screen to define parameters for a representative report. At the top of each screen you will see informational material and numbered steps you will follow. Although you are not connecting "live" to the data warehouse while using the tutorial, each screen is an exact replica of the screens you will encounter. Because the data is not "live," the report results will return a static data set that is applicable for the parameters you choose.

Screen sizes vary depending upon the type of monitor you are using. You may have to scroll to see all of the options and choices available to you on a screen. Two types of choices will be presented to you on the screens. When you can select multiple items they are preceded by a check box. When you must make a choice between items, they are preceded by a circle (radio button).

You must follow the Tutorial directions exactly. If you choose options other than what the instructions specify, a pop-up error message will appear and you will not be allowed to move to the next screen. For example, if the instructions specify to filter to Agency Number 130, you must enter 130 for the agency filter. When you move to the next screen, you will be unable to go back a screen unless instructed.

Printing the directions would be helpful to review prior screens and/or to assist in the entering the given data for each prompt.

Lessons are designed to take only 10-20 minutes to complete. Let's begin by creating an expenditure report for one fund within one agency for the 1999 Budget Year.

Click Lesson 1 to begin the Tutorial.

LESSON 1
Creating a Basic Report: Sorting, Grouping, and Modifying

Query Report Screen

For your first template report, you would like to see expenditure information for Fund Number 3125 in Agency No. 130 for all of Budget Year 1999 and sorted by Org Code. Your report should appear as shown below.

1. Click here to continue: Continue

New Query from Template Screen

This screen displays a list of the available templates with a brief description. For this report, you want to see agency expenditure summary information. You must scroll down to see all available templates.

1. Click the title of the Expenditures by $ report.

Query Information Screen

On all screens, you will see a green title box which includes the title of the screen as well as options such as Home, Next and Back. This screen, the Query Information screen, displays a list of all available columns for this report template. It is not necessary to include all of the columns, but you must check those columns you want to appear in the report as well as any columns you want to filter without appearing. Columns will appear in the report in the order they are shown on this screen. In a later screen, you will choose the columns you want to physically appear in the report. If you would like to know more about a particular column, click the mouse on the question mark by the column name and a pop up window will display with a description.

1. Click in the box preceding each of the following columns under Select Columns:

Total Expend, Minor Object Code, Major Object Code, Org Code, Fund Nbr, Budget Year, Agency Nbr (SAAS)

Next

Filter Options Screen

As mentioned in the Introduction, this screen, the Filter Options screen, is VERY important in customizing your query. It restricts the data returned in the results of your query. Remember that the query is limited to returning no more than 1000 rows, so it is important to set filters appropriately. If the limit is exceeded, you will see the first 1000 rows of data along with a warning message that is displayed in red. Warning: The record limit has been exceeded and not all lines are displayed.

This report requires your adding three filters which all use the equal to operator. Other types of operators will be used in later reports. The filters must be added in the same orderas the instructions in this tutorial; however, the order is not important when creating reportson the "live" data. If you need to delete a filter, highlight it and click <<Remove Filter. For Tutorial purposes only you must enter the filters in the exact order specified. Therefore, if you must remove a filter to make modifications you must remove all filters below it, and from that point re-enter each filter.

Adding filters requires four steps. The required four steps are documented in the left section of the screen.

*Hint - It may be helpful to print this screen to assist in entering filters.

1. Select Agency Nbr (SAAS) in the Select Column box to highlight it.
2. Select the operator equal to from the Operator drop-down list.
3. Enter 130 in the Enter Value box.
4. Click >>Add Filter to add the filter to the Filter Criteria box. (You might have to scroll to see this.)

Now, add the filter for restricting the fund number to 3125.

1. Select Fund Nbr in the Select Column box to highlight it.
2. Select the operator equal to from the Operator drop-down list.
3. Enter 3125in the Enter Value box.
4.Click >>Add Filter to add the filter to the Filter Criteria box.

Finally, add the filter for restricting the budget year to 1999.

1. Select Budget Year in the Select Column box to highlight it.
2. Select the operator equal to from the Operator drop-down list.
3. Enter 1999 in the Enter Value box.
4. Click >>Add Filter to add the filter to the Filter Criteria box.
5. Click Next to continue.

Report Options Screen

The Report Options window allows you to modify your title and add a subtitle, select the columns you want to appear in your final report, include grand totals and subtotals, and adjust the fonts. Notice that Expenditure by $, the name of the template, automatically appears in the Report Title box, and the filter criteria you entered appears below the Subtitle box. Sometimes, you might want to add a subtitle listing the filters, even though they appear at the end of your retrieved report.

1. Enter Report 1, Agency No. 130, Fund No. 3125, 1999 Budget Year in the Subtitle box.
2. Click in the boxes for all Columns to Show if they are not already checked.
3. Click Run in the green Title box to submit the query.

Query Report Screen

Scroll through the report and note that it contains the detailed information you requested but the data is not in any particular order. You also observe that including the agency number, fund number, and budget year columns are not necessary because those filters appear in your subtitle and at the bottom of your report. Let's modify this report by sorting the data by Org Cd, adding a total for the Expenditures column, and removing the extra columns.

1. Scroll to the bottom of your report to note information such as filter criteria and number of rows returned.
2. Click the Back button in the green title bar three times until you return to the Query Information Screen.

Query Information Screen

Beneath the area where you selected columns, you will see options for Group or Sort Records. If you do not choose to sort, the results will be sorted by the second column in the report.

1. Click Sort under Group or Sort Records.
2. Click Next to continue.

Sorting Options Screen

This Sorting Options screen appears only when you have chosen to sort, as you did on the previous screen. You may sort on any one of the columns.

1. Click Org Cd.
2. Click Next twice to continue to the Report Options screen.

Report Options Screen

Notice that the filters you established earlier are displayed under the Subtitle for your confirmation. This is an ideal time to ensure that you have included enough filters to restrict the result to less than 1000 rows. Now you want to delete some of the excess columns in your report and add a grand total for Total Expend at the bottom of your report.

1. Deselect the following columns under Columns to Show to remove them from your report:

Fund Nbr
Budget Year
Agency Nbr (SAAS)

Total Expend

Columns to Total

Run

Query Report Screen

Congratulations! You have created your first MERLIN.net report. Scroll through the report and notice the rows are now sorted by Org Cd and a grand total appears at the bottom of your report. You are satisfied with the results and would now like to save the structure of this report to rerun each month.

1. Click Back button in the green Title bar once to return to the Report Options screen where you can save your report.

Report Options Screen

You can save the parameters of your query so you can run the same query in the future. The report title and subtitle will appear as the name of your file on a list of your personal saved reports. As you become familiar with the templates, you will no doubt want to preserve the format of several of your personalized queries.

1. Click the Save button in the green Title bar.

Query Information Screen

Your report has been saved! When you click Home, you will be taken back to the Template Selection screen. You can either select a template from this screen or access any personal report you have saved.

1.Click the Home button in the green title bar.

New Query Screen

You can access your previously-saved queries from this screen or you may create a new query from a template.

1.Click Open a Saved Query at the bottom of the screen.

Saved Query Screen

A screen appears with a list of all of your personal queries, sorted by report title. It also includes the subtitle and time and date the report was last modified. To delete a report, you would simply click the red Delete next to the appropriate report. This immediately deletes a report without prompting you to ensure you want to delete it! Rather than delete your first report, let's make some modifications. Keep in mind that when you modify a saved report and run it again, it will replace the previously saved report. You have decided you would prefer to see a report which summarizes the total expenditures by Org Cd using the same filters you chose earlier.

1. Click your saved Expenditure by $ report to continue.

Report Options Screen

When you open a saved report, it will open to the last screen in the series of setup screens so that you have easy access to the Run button. In this case, we want to revise the saved report, so it will be necessary to go back a few screens.

1. Click Back three times to the Query Information screen.

Query Information screen

You have the option of selecting either the Sort or Group function. In the first report, you chose to sort by Org Cd, but now you would like to see the data summarized by Org Cd. Grouping allows you to collapse all identical values in a column to one row with a mathematical operation performed on the variable column. You can usually choose to sum or average a column, but in some templates there are options to select a minimum or maximum.

1. Click Group under Group or Sort Records.
2. Click Next

Grouping Options Screen

The Grouping Options screen allows you to select the column to group together and select either the total or average calculation. When you choose to group, you must also choose a calculation.

1. Select all the columns under Choose the Columns to Group. This will collapse all like rows into one row with the chosen calculation performed on the amount.
2. Select Total: Total Expend under Choose Column Calculations. This will provide a total for all like rows for the columns that you have selected to group on.
3. Click Next twice until you reach the Report Options Screen.

Report Options Screen

Click Run to continue.

Query Report Screen

The results of your report appear as shown below. Running a saved report automatically overwrites the old report. Because you chose to group this report by Major Object Code, the next time you run this report, the results will be grouped as shown. This completes Lesson 1.

1. Click here to:

Return to Tutorial Menu

Continue to Lesson 2

LESSON 2
Using Various Comparison Operators: Between and In This List

Query Report Screen

Let's continue by creating a report that examines expenditures for Minor Object Codes 61615 through 61699 for SAAS Agency No. 130 and grouped by the months of October, November and December of 1998. In your earlier report, you used the equal to operator in setting your filters. This report will require you to use the between operator and the in this list operator. Your completed report should appear as shown below.

1. Click here to continue: Continue

New Query Screen

Since you want to see a summary report that examines agency expenditure information, select the appropriate template.

Click the Expenditures by $ report.

Query Information Screen

In your final report, you want to see only the Tot Exp, Minor Object Cd, and Accounting Month. However, you want to filter on the Budget Year and Agency Nbr (SAAS), so all five of these columns must be checked on the Query Information screen.

1. Click in the check boxes by the columns listed above.
2. Click Group under Group or Sort Records
3. Click Next to continue.

Grouping Options Screen

Select the column you want to group the data by and determine how you want the result calculated.

1. Select Accounting Month under Choose the Columns to Group. This will collapse all like rows into one row with the chosen calculation performed on the amount.
2. Select Total: Total Expend under Choose Column Calculations. This will provide a total for all like rows for the columns that you have selected to group on.
3. Click Next.

Filter Options Screen

Remember, the Filter Options screen is the MOST important in customizing your query. You must include enough filters to ensure your answer is less than 1000 rows. This report requires creating four filters. You will need to scroll down to see the filter options screen. If filters are entered incorrectly, you simply highlight each filter and press <<Remove Filter. For Tutorial purposes only you must enter the filters in the exact order specified. Therefore, if you must remove a filter to make modifications you must remove all filters below it, and from that point re-enter each filter.

*Hint - It may be helpful to print this screen to assist in entering filters.

To filter your report to Agency Nbr 130:

1. Select Agency Nbr (SAAS) in the Select Column box to highlight it.
2. Select the operator equal to from the Operator drop-down box.
3. Enter 130 in the Enter Value box.
4. Click >>Add Filter to add the filter to the Filter Criteria box.

To filter your report to the Budget Year 1998.

1. Select Budget Year in the Select Column box to highlight it.
2. Select the operator equal to from the Operator drop-down box.
3. Enter 1998 in the Enter Value box.
4. Click >>Add Filter to add the filter to the Filter Criteria box

To filter your report to include Accounting Months 10, 11 and 12:

1. Select Accounting Month in the Select Column box to highlight it.
2. Select the operator in this list from the Operator drop-down box.
3. Enter 10 in the Enter Value box and press [Enter] on the keyboard.
4. Enter 11 in the Enter Value box and press [Enter] on the keyboard.
5. Enter 12 in the Enter Value box. (Do not press [Enter after this last value.
6. Click >>Add Filter to add the filter to the Filter Criteria box

To filter your report to include a range of Minor Object Codes from 61615 through 61699:

1. Select Minor Object Code in the Select Column box to highlight it.
2. Select the operator between from the Operator drop-down box.
3. Enter 61615 in the Enter Value box and press [Enter] on the keyboard.
4. Enter 61699 in the Enter Value box. (Do not press [Enter after this last value.
5. Click >>Add Filter to add the filter to the Filter Criteria box
6. Click Next to continue.

Report Options Screen

Because you have decided not to include the year and agency number columns, you might want to include them in the subtitle of this report.

1. Enter Report 2, Agency No. 130, 1998 Budget Year in the Subtitle box.
2. Examine the Filter Criteria to ensure all four filters appear.
3. Ensure that Total Total Expend and Accounting Month are the only columns checked under Columns to Show.
4. Ensure that there is a check by Total Expend under Columns to Total to create a grand total.
5. Click Run to submit the query.

Query Report Screen

Your report includes only three lines because you grouped the data by the three Accounting Months you included in your filter. To save your new report, you must return to the Report Options screen.

1. Click Back to continue.

Report Options Screen

Clicking the Save button will save this report for future use.

1. Click Save to save this report.

Report Options Screen

Your report has been saved! This completes Lesson 2.

1. Click here to:

Return to Tutorial Menu

Continue to Lesson 3

LESSON 3
Creating Subtotals, Printing, and Saving Reports

Query Report Screen

You would now like to create a report that examines each expenditure by vendor (with a subtotal for each vendor) for one agency during one accounting month. You want to further limit the size of the report by looking at a range of minor object codes. Finally, you want to save the report in spreadsheet format. Your completed report should appear as shown below.

1. Click here to continue:Continue

New Query Screen

Since you want to see a report that examines expenditure information by vendor, select the appropriate template.

1. Click the Expenditures by Vendor report.

Query Information Screen

In your final report, you want to see only the Vendor Name, Vendor Nbr, and Tot Exp. However, you want to filter on the Budget Year, Accounting Month, Minor Object Cd and Agency Nbr(SAAS), so all of these columns must be checked on the Query Information screen.

1. Click in the box preceding each of the following columns under Select Columns:

Total Expend, Vendor Nbr, Vendor Name, Minor Object Code, Accounting Month, Budget Year,

Agency Nbr (SAAS)

Next

Filter Options Screen

This report requires creating four filters. For Tutorial purposes only you must enter the filters in the exact order specified. Therefore, if you must remove a filter to make modifications you must remove all filters below it, and from that point re-enter each filter.

1. Create a filter to restrict your report to Agency Nbr 401.
2. Create a filter to restrict your report to Budget Year 1998.
3. Create a filter to restrict your report to Accounting Month 08.
4. Create a filter to restrict your report for Minor Object Codes between 61000 and 62000.
5. Click Next to continue.

Report Options Screen

Remember that this screen gives you the opportunity to select the columns to include in the report. If you choose not to include columns that you have set filters on, you might want to add a subtitle with that information. You will also choose to include a vendor subtotal on this screen.

1. Enter Report 3, Agency 401, 1998 Budget Year, Accounting Month 08 in the Subtitle box.
2. Select only the Total Expend and Minor Object Code columns under Columns to Show. You do not need to show Vendor Name as a column because it will be a subtotaled below.
3. Click Total Expend under Columns to Total.
4. Select Vendor Name in the Column to Group/Subtotal drop down box.
5. Change the Report Font to Times and the Font Size to Normal.
6. Click Run to submit the query.

Query Report Screen

The first several records do not have a Vendor Name associated with the data, so there is no grouping done on those rows. However, as you scroll through the report, you will see Vendor Names as well as subtotals for each vendor. You are pleased with the formatted report and would like to print it. You will use options from your browser's File menu to both print and save the report.

1. Select Print from the File menu of your browser.
2. Select Save As... from the File menu of your browser.
3. Select an appropriate directory to save your report and then click Save.
(Hint: Later, when you open the directory where you have saved the report, remember to specify *.HTML as the file type.)

This completes Lesson 3.

1. Click here to:

Return to Tutorial Menu

Continue to Lesson 4

LESSON 4
Using the "Starts With" Operator

Query Report Screen

Let's continue by creating a report that examines expenditures summarized by month during Budget Year 1998 for one Vendor for Agency No. 651. You are unsure of the complete name of the vendor, but you know that it starts with Jackson Radiology. This report will require you to use the starts with operator in your filter. Your completed report should appear as shown below.

1. Click here to continue: Continue

New Query Screen

Since you want to see a summary report that examines expenditure information by vendor, select the appropriate template. You will have to scroll to the bottom of the page to see the templates.

1. Click Expenditures by Vendor at the bottom of the screen.

Query Information Screen

In your final report, you want to see the Total Expend, Vendor Name, Accounting Month, Budget Year, and Agency Nbr (SAAS), so all five of these columns must be checked on the Query Information screen. In addition, you want to group the information by Accounting Month.

1. Click in the check boxes by the columns listed above.
2. Click Group under Group or Sort Records.
3. Click Next to continue.

Grouping Options Screen

Select the columns you want to group the data by and determine how you want the result calculated.

Filter Options Screen

This report requires creating three filters. Since you are unsure of the vendor name, you will use the starts with operator and type enough of the name to get the proper results. For Tutorial purposes only you must enter the filters in the exact order specified. Therefore, if you must remove a filter to make modifications you must remove all filters below it, and from that point re-enter each filter.

1. Create a filter to restrict your report to Agency Nbr 651.
2. Create a filter to restrict your report to Budget Year 1998.
3. Create a filter to restrict your report to the vendor whose name starts with Jackson Radiology.
4. Click Next to continue.

Report Options Screen

You will want to see all columns that you specified and a grand total at the end of the report.

1. Enter Report 4, Jackson Radiology for a Subtitle.
2. Verify that all column names under Columns to Show are checked. Check any that are not checked by default.
3. Click Total Expend next to Columns to Total.
4. Click Run to continue.

Query Report Screen

This report shows the expenditure for one vendor over a year's time. This completes Lesson 4.

1. Click here to:

Return to Tutorial Menu

Continue to Lesson 5

LESSON 5
The CHALLENGE Lesson

Now that you have completed the first four lessons, you are ready to create the Challenge template report which eliminates step-by-step instructions.

You want to create a report to examine revenue for Agency Nbr (SAAS) 130 grouped by Rev Source Cd for Accounting Month 05 in Budget Year 1998. Include a subtitle and a grand total in your report.

1. Click here to continue: Continue

New Query Screen

You want to create a report to examine revenue for Agency Nbr (SAAS) 130 grouped by Rev Source Cd for Accounting Month 05 in Budget Year 1998.

Query Information Screen

You want to create a report to examine revenue for Agency Nbr (SAAS) 130 grouped by Rev Source Cd for Accounting Month 05 in Budget Year 1998.

Grouping Options Screen

You want to create a report to examine revenue for Agency Nbr (SAAS) 130 grouped by Rev Source Cd for Accounting Month 05 in Budget Year 1998. Include a subtitle and a grand total in your report.

Filter Options Screen

You want to create a report to examine revenue for Agency Nbr (SAAS) 130 grouped by Rev Source Cd for Accounting Month 05 in Budget Year 1998. For Tutorial purposes only you must enter the filters in the exact order specified. Therefore, if you must remove a filter to make modifications you must remove all filters below it, and from that point re-enter each filter.

Report Options Screen

Completed Report

Congratulations! You have completed Lesson 5 successfully! You are now ready to create reports in the MERLIN.net Template Tool.

1. Click here to:

Return to Tutorial Menu

Continue to Lesson 6

LESSON 6
Charting Functions

Lesson 6 is not an interactive lesson. Rather, it requires you to print this page and then to follow the directions to do a chart using the Expenditures by Vendor template.

In Lesson 4, you created a report that examined expenditures summarized by month during the budget year 1998 for a vendor for Agency No. 651. Now you would like to create a chart showing that same information for Vendor No. V0000660060. You will create this report using "live" data. Since you want to see a report summarizing expenditure information by vendor, click the Expenditures by Vendor template.

Query Information Screen

In your final report, you want to see only the Tot Exp and Accounting Month. However, you want to filter on the Budget Year, Vendor Nbr and Agency Nbr (SAAS), so all five of these columns must be checked on the Query Information screen. In addition you want to group by the accounting month and select chart as the output type.

1. Click in the check boxes by the columns listed above.
2. Click Group under Group or Sort Records.
3. Click Chart under Output Type.
4. Click Next to continue.

Grouping Options Screen

Select the columns you want to group the data by and determine how you want the result calculated.

1. Click all columns under Choose the columns to group.
2. Click Total Expend.
3. Click Next.

Filter Options Screen

This report requires creating three filters.

1. Create a filter to restrict your report to Agency Nbr 651.
2. Create a filter to restrict your report to Budget Year 1998.
3. Create a filter to restrict your report to Vendor Nbr V0000660060.
4. Click Next to continue.

Chart Options Screen

Filters will appear at the bottom of your chart, but you might want to also enter them in your title. On this screen, you will choose the chart type and the columns to use for the X and Y axes. There are many other formatting options which you can explore later.

1. Enter Vendor Nbr V0000660060, BY 1998, Agency 651 in the Title box.
2. Select Column from the Chart Type list and select the 2D radio button.
3. Click the Accounting Month column for the X-Axis and Total Total Expend for the Y-Axis.
4. Click Run to submit the query.

Query Report Screen

Your trend chart shows the expenditures for one vendor over a year's time.