How do I create a custom scorecard?
Neoserra comes standard with several built-in system scorecards. Located under Manage|Scorecards|Layouts, these system-defined scorecards are typically based upon detailed SBA or DLA reporting requirements. You cannot modify Neoserra' system scorecards in any way, but you can create duplicate copies of them for modification. You can also create scorecards from scratch. A custom scorecard can include any metric that you want to track, and may look something like this:
In this FAQ, we'll cover the process of creating custom scorecards:
- Creating a New Scorecard Layout Record
- Creating Line Item Calculations
- Importing Line Item Calculations
Creating a New Scorecard Layout Record
The steps for creating a new scorecard layout record are outlined next:
- Select Manage|Scorecards|Layouts
- Click the New link at the top of the screen.
- Enter a descriptive name for your new layout.
- If you are creating a completely custom scorecard, then you may want to choose to create A blank layout. If you are planning on slightly modifying an existing layout then you should choose to create A copy of an existing layout and choose the layout that you want to copy from the drop down provided.
- Click Save to create your custom scorecard.
With the newly create scorecard on the screen you can edit the information entered by clicking the Edit link at the top of the screen. One of the fields listed here is the Has both current and cumulative date columns? check box, which determines whether a scorecard puts out numbers for both a current and cumulative date range or for a single date range only. Note, that the DLA 1806-T scorecard does not support both columns due to the nature of the calculations of the line items.
If you believe that this scorecard will typically be generated with a funding source limitation in place (i.e. the scorecard will only consider records with a particular funding source or set of funding sources, put your cursor in the Funding Source(s) field in order to select the appropriate funding source(s). The funding source(s) chosen here merely provide a funding source default for subsequent definition records. They exist to make it more convenient for you when generating multiple definition records for the same scorecard that all have the same funding source limitations. In other words, the funding source(s) selected here are not what Neoserra will look to when actually generating the scorecard; instead, Neoserra will look to the funding source restrictions set in the definition record.
If you made any changes, click Save and you are ready to customize your layout line items.
If the calculation you want Neoserra to perform cannot be found in any other scorecard, you can write it from scratch. Scorecard calculations can be very basic or you can build them out to create a more complex series of restrictions and calculations.
Note: If you want to create a line item that performs calculations using a very specific SBA or DLA definition (e.g. extended engagement clients (SBA) or active client count (DLA)), you cannot create the line item from scratch. You must import it from the appropriate scorecard because calculations involving these "special" definitions cannot be built from scratch, but they can be built upon. But you should be careful, any modifications made to these specially calculated line items will give results that may or may not meet your SBA/DLA requirements. Especially the DLA 1806-T should not be modified due to its complicated nature.
The process for creating line item calculations is divided into five parts for documentation purposes. First, we'll discuss how to create a very basic line item calculation, which, in many cases, may be all you need to do to get the number you're looking for. But, for more complex calculations, we'll also take you through the addition of filters, operators, and components.
- Getting Started with a Basic Line Item Calculation
- Adding an Operator to Your Line Item Calculation
- Adding a Filter to Your Line Item Calculation
- Adding a Second Component to Your Line Item Calculation
In this section, we're going to get you started on the creation of a very basic line item calculation. To provide context, the next set of steps will highlight a very specific line item calculation: total number of counseling session hours.
- To create a brand new line item calculation, click the New link as highlighted by the #1 in the graphic below.
- Enter a title for your new line item in the Label field as denoted by the #2 in the graphic below. If you would like the line item's title to be bolded in the scorecard, select the Bold label? check box.
- If this line item is merely a label on your report, without any calculations associated with it, then click the Save? button now. Label-only line items can be helpful when categorizing a group of calculations. On the other hand, if you would like to add a calculated line then click New the #3, which will provide the components interface:
- Now that Neoserra knows that this line item will require some calculations it provides you with some additional line item controls. In the top portion of the screen click on the Format field to select how many decimal places you'd like present in the line item's generated value, as well whether it should display as a number or currency.
- The Goal Target and Goal Polarity fields, require you to jump ahead in your thinking and anticipate how you would go about goaling youe calculation, assuming you will ultimately create goals for this scorecard and display the bar chart that shows the progress towards those goals on your dashboard. If you never intend to track progress toward goals for this particular scorecard, then you can ignore these two fields and skip to the final step.
- At the Goal Target field, you are specifying whether progress toward a specific goal should take into account how much time has elapsed during the scorecard period. You can leave the default "Relative to time elapsed" option selected to show that goal progress is directly tied to the amount of time that has passed or you can select "Absolute (doesn't take time elapsed into consideration)" to show that goal progress is not tied to the amount of time that has passed.
- An example of when you would leave the "Relative to time elapsed" option selected: Assume for a moment that you are tracking counseling session hours over the period of a fiscal year and you set the total annual goal to be 5,000 counseling hours. One week into the scorecard period, your program has provided 100 hours of counseling, putting you perfectly on track with your goal. However, if you do not ask Neoserra to take into consideration the amount of time elapsed when calculating goal progress, your goal chart will indicate that you are woefully short of your 5000-hour goal when, in reality, you're right where you should be.
- An example of when you would select the "Absolute (doesn't take time elapsed into consideration)" option: More times than not, you'll leave the default "Relative to time elapsed" option in place because it's most common that your numbers will build as time progresses. However, there are situations when Neoserra should not further adjust your goal progress according to the amount of time that has elapsed. Most notably, this is when calculating averages. For example, if you were calculating the number of counseling hours provided per government contact awarded, this average is unaffected by how much time has elapsed. You would not want Neoserra to deduce that since only 50% of the scorecard time has elapsed, the average only needs to be 50% of what you state as the goal average in order for the goal to be met. Unlike numbers that build over a period of time, for averages, you are capable of fully meeting the goal regardless of how much time has elapsed.
- At the Goal Polarity field, you are specifying the context of a calculation's resulting number. There are three options here:
- High values are desirable. By default, the "High values are desirable" option is selected, causing Neoserra to assume that the higher the number returned, the more desirable it is. For example, if you are calculating the number of awards won, and the more awards the better, Neoserra will graph going beyond the goal as a good thing (i.e. the bar will display in green when the goal is exceeded).
- Low values are desirable. But what if your program is tracking counseling session travel and it's trying to reduce the overall number of travel hours? Then you would want to select the "Low values are desirable" option so that Neoserra will visually show that going past the goal is a bad thing (i.e. the bar will display as red when the goal is exceeded).
- Only target goal is desirable. There's one last option and that is that neither falling under or above the goal is desirable. What's most desirable is staying as close to the goal as possible. For example, perhaps your program is tracking the contact time spent on initial counseling sessions and believes that 90 minutes is the optimal duration. Less than 90 minutes is not enough time to impart all the necessary information, but more than 90 minutes is considered overwhelming to the client and not a good use of counselor time. In this scenario, you'd select the "Only target goal is desirable" option as the goal polarity. Neoserra would then visually show that either falling short or exceeding the stated goal is undesirable.
Obviously, there are many other data sources available in addition to "Client Counseling Sessions." The majority of them are self explanatory and correlate directly with data found in Neoserra. There are two data sources, however, that require further explanation:
- Centers. The "Centers" data source allows you to use user-defined center record fields (configured by the system administrator under Administration|Configuration|User-Defined fields, as constants in your line item calculations. Only user-defined fields with a data type of number or currency can be used. Mainly, this data source is here so that you can use global budget numbers in your line item calculations (e.g. cost per training event, or cost per counseling session, etc.). If you do not have any user-defined fields set up for center records, this data source does not display as an option.
- Scorecard Constants. The "Scorecard Constants" data source provides you with a number of built-in relevant constants such as the number of days or weeks (or elapsed days or weeks) in the scorecard definition period to use in your line item calculations. In addition, you are able to enter custom constants of your own, which would then require the completion of the Constant Value field.
- Note: You cannot source on custom forms.
The options available from the Data Field field's drop-down menu depend solely upon the data source selected in the previous step.
Note: If you chose one of the survey responses data sources, you must first select the name of the survey you are reporting on before you can choose a corresponding piece of data from the Data Field field. Then, Neoserra presents you with only those questions from the survey that could be selected for calculation. In other words, the survey questions have to be able to be expressed numerically. So, for example, you could select a yes/no question or a currency question, but not a free-form text question. For more information on scorecarding survey responses, see the FAQ titled How do I scorecard survey questions?.
As shown in the above set of steps, the creation of a line item calculation can be very simple and straightforward. However, there will be occasions when you want more complex line item calculations. For example, you might want to:
- Calculate the average number of hours per counseling session, which would require that you add an operator to the line item calculation created in the above set of steps.
- Calculate the total number of hours for initial counseling sessions only, which would require that you add a filter to the line item calculation created in the above set of steps.
- Calculate the average number of counseling hours provided per government contract awarded, which would require that you add a second component to the line item calculation created in the above set of steps.
When you want to do more than report on a single data field, such as find the average dollar amount of capital funding or the average number of attendees per training event, you need to add an operator to your line item calculation. To continue with our scenario, the next set of steps is going to expand upon the line item calculation created in the previous section (total number of counseling session hours) in order to find the average number of contact hours per counseling session.
- After establishing some basic settings for your line item calculation, along with the first piece of data in your line item calculation (as discussed in the steps in the Getting Started... section), you can perform an additional calculation on it by clicking on the mathematical operator drop down to display another various operations you can perform on your data element, as highlighted in the next graphic. To satisfy our stated goal, select "Divided by (/)" to indicate that we want to divide the total hours of all counseling sessions by the total number of counseling sessions.
- As soon as you choose an operator a second data element drop down will appear. Notice that there are only a select number of data elements available for selection here. This is because a single component's calculations cannot cross data sources (with the exception of center and scorecard constants). If you need to include a data field from another data source in your line item calculation, you'll need to add a second component to the calculation, rather than just an operator. To satisfy our stated goal of finding the average number of hours per counseling session, select "Client Counseling Sessions" from this field's drop-down menu.
- Verify that the information in the top section is still valid for your calculation (as discussed in the previous section) and click Save button. That's it! You've just created an advanced line item calculation.
Note: Advanced line item calculations that include more than one component cannot be "audited" once you run the results. In other words, you cannot click into the results if the line item is generated based upon a mathematical equation.
When you want to restrict the records that Neoserra considers for a calculation (e.g. instead of all milestone records, only "responded to survey" milestone records), you need to add a filter to your line item calculation. To continue with our scenario, the next set of steps is going to expand upon the line item calculation created originally (which is total number of counseling session hours) in order to find the total number of prep+contact counseling session hours provided with the primary counseling area of "Business Plan".
Note: The concept of creating a filter for a scorecard line item calculation is identical to the concept of creating filters elsewhere in Neoserra, which is discussed in detail in the FAQ titled What is a filter?.
- After establishing some basic settings for your line item calculation, along with the first piece of data in your line item calculation (as discussed in the steps in the Getting Started... section), you can click the filter drop down restrict the calculation to only those records that meet a specific set of criteria.
- From the drop down, select the field(s) whose values will determine the records that you would like to restrict to. To satisfy our stated goal of finding the total number of counseling session hours provided with the primary counseling area of "Business Plan" select "Counseling Area" in the left hand side of the filter.
- Place your cursor in the field on the right and indicate the values that records must (or must not) match on. In our case, we will choose "Business Plan"
- Click the Save button. You have just added a filter into your line item calculation.
Note: If you add multiple filter criterion then these are strung together as "AND" statements.
When you want a line item's calculations to cross data sources, you must add another component to your line item calculation, rather than just adding an operator. To continue with the scenario we've been using thus far, the next set of steps is going to expand upon the line item calculation created originally (which is total number of counseling session hours) in order to find the average number of counseling hours provided per government contract awarded.
- After you are finished creating the calculation's first component (component A), which is detailed in the steps in the Getting Started... section, click the New link to create a second component (component B).
- For the purposes of our stated goal, create component B with a data source of "Awards" and a data field of "Number of Awards." No operators or filters are necessary.
Note: If you had the need, you could create a third (or fourth, and so on) component for your line item calculation.
- Notice, a new field has been added to the top section of your scorecard lin. The field: Expression is used to enter a mathematical expression that shows how the components should interact with one another to create the final line item value. For the purposes of our stated goal, enter "A/B" into the Expression field, as shown in the next graphic.
- Click Save. That's it! You've just created a line item calculation with multiple components.
Note: To reiterate, a second component is only necessary when you want a line item's calculations to cross data sources. If you're just looking to divide the total dollar amount of investments by the total number of investments to come up with an average amount per investment, you can do this within a single data component by simply adding an operator as discussed above.
Note: Calculations that include more than one component cannot be "audited" once you run the results. In other words, you cannot click into the results and see the individual records if the line item is generated based upon a mathematical equation. If your scorecard includes a line item with multiple components, then we recommend that you include both individual rules on the scorecard for individual auditing with the added line item below.
When adding line items to a custom scorecard, an easy way to get started is by importing line item calculations from other scorecards.
The steps for importing line item calculations are outlined next:
- To import a line item calculation from another scorecard, click the Import link at the top of the Layout interface:
- From the Layout drop down in the "Import" window, select the name of the scorecard that has the line item you want to import.
- Then, highlight the line items that you would like to import and click Save to import the line item.
Once you've imported one or more line item calculations, you can use the Reorder link at the top to rearrange their order, edit them, or duplicate them.
Want more? Browse our extensive list of Neoserra FAQs.