Articles in this section


  • Data Set Output (One Row Per)

    When building a data set, you have the option to change the output from the default "One Row per Request" setting. This controls how data will display in reports you run using this data set.

    Data_Set_Output.png

    Depending upon the type of reports you intend to run with the data set, changing the output can allow you to see the level of detail with the data as expected. For example, if you're running an evaluation report, you would likely want to see data for each evaluation, for each request. Changing the output to One Row per Evaluation would allow you to do so. 

    The different options for the output setting are described below: 

    • One Row per Request: This is the default setting. When this option is selected, a report will display one row of data for reach request. 
    • One Row per Form: When this option is selected, a report will display one row of data for each form.
      • If a request includes three different forms (e.g. LOI, application, decision), there will be three rows of data for that request - one for each form. 
    • One Row per Evaluation: When this option is selected, a report will display one row of data for each evaluation. 
      • If a request was assigned to four separate evaluators, there will be four rows of data for that request - one for each evaluation. 
    • One Row per Installment: When this option is selected, a report will display one row of data for each installment.
      • If a request has two installments, there will be two rows of data for that request - one for each installment. 
    • One Row per Payment: When this option is selected, a report will display one row of data for each payment.
      • If a request has three payments recorded, there will be three rows of data for that request - one for each payment. 
    • One Row per CharityCheck: When this option is selected, a report will display one row of data for each CharityCheck. 
  • Error When Running a Report

    This article takes you through troubleshooting steps to address a reporting error. If you need additional assistance at any point, please reach out to the Support team (support@foundant.com). 

     

    Problem

    When attempting to run a report, you encounter an error similar to the one below, referencing "a plugin." 

    If the error message references a "hexadecimal" error, please reach out to the Support team (support@foundant.com) for assistance, as the troubleshooting steps below will not address that error.

     

    Solution

    1. Navigate back to the main Reports & Data Sets page, then click the pencil icon to edit the data set for the report you're trying to run.
    2. Examine the selected fields in the data set, and look for the following: 
      • Does more than one field in the data set have the same label?  
      • Do any field labels have special characters?
      • Do any field labels start with a number?
    3. If you identify any of the issues listed above, you have two options: relabel the field(s) directly in the form, or rename the field(s) in the data set. 

     

    Option 1

    To relabel a field (question) in the form, follow these steps: 

    1. Identify the process and form where that question exists (e.g. the application form of the 2020 Example Grants process).
    2. Navigate to the form in the process, then edit the question and change the label.

     

    Option 2

    If two or more fields have the same label in the data set, first consider if those fields should be combined into one column. 

    • If you want the fields combined into one column, follow the steps below, but select each of the fields you'd like to combine in step 1 and add a space in the Separator field in step 3. Refer to this video for additional details. 
    • If you want the fields to be separate columns, rename each field following the steps below.

    To rename a field in the data set, follow these steps: 

    1. Select the box next to the field.
    2. Click the square icon, then click Combine/Rename Fields.
    3. Enter a new name for the field, delete the semicolon in the Separator field, then click OK
      • You can include a number in the field, just not as the first character in the label.
    4.  Rename additional fields as needed in the data set.
  • Common Reporting Formulas

    The dropdown list when adding a formula to a report contains many useful formulas. Below are some additional commonly used formulas for reference: 

    • Rename values (for example, if you want to create anonymous labels for evaluators in the report): 
      • IIF([Column] = “Bob”, “Evaluator 1”,IIF([Column] = “Joe”, “Evaluator 2”,IIF([Column] = “Mary”, “Evaluator 3”,IIF([Column] = “Jane”, “Evaluator 4”,IIF([Column] = “Sally”, “Evaluator 5”, 0)))))
    • Correct blank number cells (that should display a 0): 
      • [Column]+0
    • Combine columns:
      • [Column]+[Column]
    • Formula for "contains": 
      • IIF(InStr(1,[Column], "What you're checking if it contains", 1)<>0, "Output if it does contain", "Output if it doesn't contain")
    • Put installment (or decision date, payment date, etc.) into correct fiscal year: 
      • IIF(Month([Installment Due Date])<7,Year([Installment Due Date]),Year([Installment Due Date])-(-1))
        • Note: the "7" in the formula above indicates the month. In this example, 7 for July.
  • Include Related Form Data in a Data Set

    When building a data set with the output set to something other than One Row per Request, it's advised that you check the box for Include Related Form DataThis allows data from related forms for a request to appear in a report that might not otherwise show that data. 

     

    Include Related Form Data

    To access this setting: 

    Example

    To explain why this is helpful, consider the example of running a report with the output set to One Row per Evaluation. This means that each evaluation for all included requests will display as one row of data in the report, as in the image below. 

    Include_Related_Form_Data_in_a_Data_Set_3.png

    If you include a field from the application form (which is not shared onto the evaluation form) in the report, it is not part of the evaluation data. That means the data for that field will not show up in the report by default. In the image below, Geographic Area Served is a question on the application form, so it isn't displaying data in this One Row per Evaluation report. 

    Include_Related_Form_Data_in_a_Data_Set_4.png

    If you check the Include Related Form Data box, fields from the application form would then populate data in the One Row per Evaluation report. In the image below, now we see data for Geographic Area Served.

    Include_Related_Form_Data_in_a_Data_Set_5.png

  • Create a Canceled Installments Report

    If you have canceled installments for requests in GLM/SLM, the steps below will help you run a report to view that information in one place, and to view the remaining installment balance for a process(es). 

    For a refresher on the basics of building a data set and running a report, refer to the Reports & Data Sets course, found in Foundant Courses in your Sandbox site. 

    1. Build your data set to pull in the appropriate process(es) and request statuses. 
    2. On the Choose Data Fields page, change the output to one row per installment.
    3. Select all fields you’d like included on the report, including the Installment Canceled field. 
      • Other recommended fields include Installment Amount, Installment Paid, Installment Balance, Installment Due Date, as well as identifying fields such as Project Name and Organization Name. 
    4. Run the report. 
    5. Click the Gear icon. 
    6. Click Group then select Project Name in the Grouping Column dropdown. 
    7. Click Add to finish grouping the report.
      • This grouping step is especially helpful if a request could have more than one installment. 
    8. You will now see one row of data for each installment, organized by request (Project Name). If an installment is canceled, it will display Yes in the Installment Canceled column. 
    9. At this time, the balance of a canceled installment does not display as $0 in reports. Applying a formula will correct the installment balance in the report. 
      • Click Formula.
    10. Give the formula a name, such as "New Installment Balance." This will become the name of the new column. 
    11. Copy and paste this formula into the Formula box: IIF([Installment Canceled]="Yes","0.00",[Installment Balance])
    12. Change the Display Format dropdown to the currency format. 
    13. Click Add.
      • Now any canceled installments will correctly show $0 as the balance in the new column of data.  
    14. Click the Gear icon. 
    15. Click Aggregate. 
    16. Select the new column created by the formula (in our example, this is labeled New Installment Balance) in the Data Column dropdown.
    17.  Select Sum in the Aggregate Function column.
    18. Click Add.
    19. Now you'll see the overall remaining balance for the requests in this report. 
      • You'll also see the remaining balance for each request.
  • Using IIF Statements To Import Your Data Into FIMS

    Many GLM users export their data out of GLM into FIMS regularly in order to cut checks and perform other accounting duties.  Some field values differ between GLM and FIMS and therefore can not be imported without manually changing the value in Excel.  

    For example, in GLM an approved grant’s status can be “Approved, Follow Up(s) Assigned, or Closed” whereas in FIMS the status needs to be just “Approved”.  Another example is that you may have GLM set up to capture Program Areas such as “Arts & Culture, Education, Health and Youth Development” but these areas are categorized as “A, B, C D” in FIMS.

    By using the Formula tool in a generated Report, these values can be changed into the value needed to import the data into FIMS without having to do a “Find & Replace” or manually editing the data in Excel.

    Setting up this formula may take a little time but in the long run will save you hours of editing your data.  Here at Foundant we like to call this a “Nested IF Statement” which basically means you are telling the system to look at a specific column and if the value is “A” change it to “B” and if the value is "C" change it to "D" and so on.  

    Before you start, please note that it is very important to type the value in exactly as it shows up in the Foundant GLM system and how it appears in FIMS.  That means if it is all caps, you type it in all caps and put spaces and punctuation exactly where they appear. The examples below refer to my examples above so please feel free to copy them and edit as needed.  It is also very important that you have the exact values needed to be imported into FIMS before you start, to save yourself some work.

     

    The following formula will change the values in the “Request Status” column into the “FIMS Status Field” values needed to import the data:

    IIF([Request Status]="Approved", "Approved", IIF([Request Status]="Follow Up(s) Assigned", "Approved", IIF([Request Status]="Closed", "Approved", IIF([Request Status]="Denied", "Denied",0))))

     

    The following formula will change the values in the “Program Area” column into the “FIMS Program Area”:

    IIF([Program Area]="Arts & Culture", "A", IIF([Program Area]="Education", "B", IIF([Program Area]="Health", "C", IIF([Program Area]="Youth Development", "D", 0))))   

     

    The fields below are commonly imported into FIMS:

    • Process Name
    • Program Name
    • Organization Name
    • Profile Number - many administrator have repurposed our “DUNS Number” field in the organization’s profile to be used for the FIMS Profile Number so it can be pulled into reports and exported.
    • Tax ID
    • Request Amount
    • Program Area
    • Address
    • Contact Name
    • Contact Email Address
    • Action
    • Amount Awarded
    • Status
  • Exporting Data from Foundant and Importing it into FIMS

    This tutorial will walk you through how to export information from Foundant GLM and import it into FIMS.

    These instructions are specific for importing into the FIMS Grants Module.

    Exporting Grants/Scholarships from Foundant and Importing Grants/Scholarships into FIMS - It is best practice to prepare your report after recording your decisions in GLM.

    1. From your Foundant GLM site, click on Reporting in the top navigation and select Reports & Data Sources from the drop-down list.
      If you do not already have a report created for this instance, follow the steps below:
      • Select “Add New Data Set” from the top right corner of the page
      • Give this data set a name and a description (not required) and select “Create Data Set
      • Select a process or processes and select the following Request Statuses: Approved (including all Follow Up options), Denied, and Closed. Then select “Save Filters
      • Add the fields you will need in your report. These may include the organization contact fields, Project Name, Process Name, Amount Awarded and any additional form fields that are necessary
      • Once all the fields you need are under the Selected Fields box, select “Run New Report
      • Make sure everything looks correct and click “CSV” at the top of the report to export the data as a .csv file
      • The file should download to your computer. Locate the file and open it using Microsoft Excel
        • Populate/edit any data needed
        • Format the amount awarded and amount requested as General
        • Save the file as a .csv file – remember the location for the import process
    2. For a Grant Import, go to the FIMS Grants Module and select Grant Import under File Maintenance(For a scholarship import, we recommend using the FIMS Scholarship Import module).
      • Select the correct Import File
        • Since there will be a header row in your import file you will want to start your Import Row from row 2
        • Fill out the Grant Import screen and then you have the option to save those selections for future imports by selecting “Defaults” and then select “Add” from the next pop up.
          • The next time you complete an import using the same settings you can select the template you have just added.
        • If you have already created a default template, feel free to use that at this time instead of manually completing each field on the Grant Import screen.
          • This can be done by selecting “Defaults”, choosing the correct template, clicking “Load Selection” and then clicking “Done”.
    1. Check the import layout (note there are several radio buttons for Profile, Contact and Other at the top of the screen and General Coding, Population Coding, Additional Detail and Other at the bottom of the screen).
      • When using the Scholarship Import module you will have the ability to import the FIMS user-defined fields.
    2. Click Import to start the Grant Import process click “Run Report
      • A preliminary page will appear to show you some basic information about the import. If you see that there are errors, you will not be able to import the data.  Close the window to cancel the import, return to the csv file to fix the errors.
    3. When you cancel the import you will have to start from the beginning of the FIMS import process (Step 2 above).
      • If the information is correct, click on Create Grants.
    4. Review the imported application records in FIMS. If you find that there are problems you can easily either correct the applications in FIMS or delete the applications, correct the information in Foundant and redo the export/import process.

    TIPS:  A possible error is having blank columns in the export file from Foundant. If this occurs simply open your import (csv) file in excel and delete the blank columns and resave the file as csv.  Rerun the import in FIMS and make sure you check the column fields you selected in case they have changed if you removed or resorted columns.

    Columns containing award and requested amounts should be formatted as general, stripped of any formatting.

     

  • Build an Overdue Follow Ups Report

    The steps below will help you run a report to view overdue follow up forms.

    For a refresher on the basics of building a data set and running a report, refer to the Reports & Data Sets course, found in Foundant Courses in your Sandbox site. 

    1. Build your data set to pull in the appropriate process(es), select the Follow Up(s) Assigned request status, and select the FollowUp form type.Creating_an_Overdue_Follow_Up_Report_1.png 
    2. On the Choose Data Fields page, change the output to One Row per Form.Creating_an_Overdue_Follow_Up_Report_2.png 
    3. In addition to any other fields you'd like to include in the report, select the following fields:Creating_an_Overdue_Follow_Up_Report_3.png

      • Organization Name (if applicable) 
      • Project Name
      • Process Name
      • Form Name 
      • Due Date
      • Initial Submit Date
      • Submission Status
    4. Run the report.Creating_an_Overdue_Follow_Up_Report_4.png 
    5. Your report will display one row of data for each follow up form, for each included request.Creating_an_Overdue_Follow_Up_Report_5.png

    6. Add a filter to only view follow ups that are not yet submitted.Creating_an_Overdue_Follow_Up_Report_6.png 
      • To do this, filter on the Initial Submit Date column. Set the Comparison field to "=" and leave the Value field set to "Specific Date." Keep the additional Value field blank. 
      • Once added, this filter will only show follow up forms that do not have a date in the Initial Submit Date column, meaning they have not been submitted. 
    7. Add another filter to only view follow ups with a due date prior to the current date.Creating_an_Overdue_Follow_Up_Report_7.png

      • To do this, filter on the Due Date column. Set the Comparison field to "<" and the Value field to "Sliding Date." Select "Today" in the additional Value field.  
      • Once you add this filter as well, the report will only show unsubmitted follow up forms with a due date prior to today's date, meaning they are overdue.
    8. Remember to save all changes you make to the report.Creating_an_Overdue_Follow_Up_Report_8.png
  • Creating Weighted Scoring Questions in An Evaluation

    There is no exact weighting that can be done within the form itself, but there are other options. 

    The scoring questions allow you to choose a minimum and a maximum and the range is sequential or in other words, you can't skip numbers. This means you cannot have a scoring question with the options being 2, 4, and 6 but you can have one with the options as 1, 2, and 3. 

    • You could set up your scoring questions to have different ranges altogether. For example, some of your questions could have a 1-5 scale and others could have a 5-10 scale.
    • The most common option is to use the reporting tool to weight questions.
      • Using formulas you can,
        • Multiply the score by a number to make it worth that much more
          • Example: Multiply a score by 2 to make that score have twice the weight it originally had
        • Multiply by a number less than 1 to show a score that is worth X% of the total score
          • Example: Multiply a score of 0.20 to make that score worth 20% of the overall score
      • Each formula will create a new column. You can then use those new columns to add and/or average the new, weighted score.

    As always, contact Foundant Support if you have additional questions.

     

     

  • Working with Evaluation Data

    The evaluation stages in a process are a helpful way to collect information from individuals reviewing requests. There are several options available for working with the evaluation data.

    • The Share option for an evaluation stage allows evaluators to view other evaluators' responses. This gives visibility to your committee(s) during the review process. 
    • Build an evaluation report. While doing so, you have many options for how to present the data, and can export the report outside the system. 
    • Print evaluation summary PDFs for each request. This can be done from the Evaluation Assigned or Evaluation Close workload page, or each Request Summary.Working_with_Evaluation_Data.png

     

     

     

     

  • Build an Evaluation Report

     

    The steps below will help you run a report to view evaluation data in your site. For information on weighting scoring questions, please refer to Step 6.

    For a refresher on the basics of building a data set and running a report, refer to the Reports & Data Sets course, found in Foundant Courses in your Sandbox site. 

    1. Build your data set to pull in the appropriate process(es) and request statuses.
      • If evaluations are still in progress, be sure to select the Evaluations Assigned status. If evaluations are closed, select the Evaluations Closed status, and any other relevant statuses where requests could be located.Build_an_Evaluation_Report_1.png 
    2. On the Choose Data Fields page, change the output to One Row per Evaluation.Build_an_Evaluation_Report_2.png 
    3. Select all fields you'd like included on the report. See below for fields commonly used in evaluation reports.
      • From the Request tab:Build_an_Evaluation_Report_3.png  
        • Submission Assignee First Name and Submission Assignee Last Name: These fields provide you with the name of the evaluator, and it's helpful to combine them into one field. 
        • Score: If you utilize scoring questions on the evaluation form, this provides you with the overall score of the evaluation.
        • Evaluation Finished: This can be helpful for filtering in your report to only see completed or uncompleted evaluations.  
        • Form Name: If you utilize multiple evaluation stages and would like to view only one stage of evaluation data on this report, include the Form Name field for filtering purposes. 
      • From the Evaluation Stage tab (located within the tab for the LOI or application):
        • Fields from the evaluation form, including each scoring question, if applicable.Build_an_Evaluation_Report_4.png

    4. Run the report.Build_an_Evaluation_Report_5.png 
    5. Your report will display one row of data for each evaluation, for each included request.Build_an_Evaluation_Report_6.png 
    6. Common actions taken on evaluation reports include: 
      • Group the report by request (Project Name, Contact Name fields, etc.) or by evaluator (Submission Assignee fields). 
      • Apply an aggregate function to the Score field to see the average score for a request or an evaluator.
      • Filter the report to only show a certain evaluation stage (filter on the Form Name column) or to only show completed evaluations (filter on the Evaluation Finished column).  
      • Add a formula to weight scoring questions.
        • For example, you could multiply a particular question (column in the report) by 2 to make that question have twice the original weight. 
        • Once you've added formulas to weight each question as desired, you can add an additional formula to add and/or average the new, weighted score.  
    7. Remember to save all changes you make to the report.Build_an_Evaluation_Report_7.png
  • Add a Chart to a Report

    After running a new report or opening a saved report, there are many actions you can take to work with the data. One of these options is adding a chart to visually summarize the data. 

    Please note that charts will not be included if you export a report from your GLM or SLM site. Charts are a helpful tool for summarizing data in reports that you intend to work with or share directly in your site.


    Add a Chart

    1. On your report, click Add Chart.Add_a_Chart_to_a_Report_1.png

    2. Click the type of chart you'd like to build.Add_a_Chart_to_a_Report_2.png 
    3. Depending upon the type of chart you select, there will be a variety of different options available to customize the chart. As you change your selections, the chart will automatically update.Add_a_Chart_to_a_Report_3.png 
    4. Remember to save the changes you've made to your report when you're done.Add_a_Chart_to_a_Report_4.png

     

    Chart Types

    A description of each chart type is listed below. Please note that some chart types might not be available, depending upon the type of data in your report.  

    • Bar: The length/height of each bar represents a numeric value across another variable.Add_a_Chart_to_a_Report_5.png 
    • Line: This shows the change in one numeric variable across another variable, with a straight line fit to the data.Add_a_Chart_to_a_Report_6.png 
    • Curved Line: This shows the change in one numeric variable across another variable, with a curved line fit to the data.Add_a_Chart_to_a_Report_7.png 
    • Pie: The pie pieces represent the distribution of one variable across another.Add_a_Chart_to_a_Report_8.png 
    • Scatter Plot: The placement of dots represent the distribution of one variable across another.Add_a_Chart_to_a_Report_9.png 
    • Heatmap: This shows the distribution of one variable across one or two others, represented by colors and sizes of blocks.Add_a_Chart_to_a_Report_10.png  
    • Gauge: This shows the value of a variable in comparison to different goal values.Add_a_Chart_to_a_Report_11.png 
  • Crosstabs in Reports

    A cross tab is a summary tool for analyzing the data in a report. For example, a crosstab in an evaluation report might look at the score given by each evaluator to each organization/applicant. A crosstab examining awarded grants might sum the amounts awarded by program area and process.

    Remember to save your changes after building a crosstab. The crosstab can be exported in an Excel, CSV, or PDF format.

    Follow these steps to build a crosstab: 

    1. Within a report you've built in GLM/SLM, click Add Crosstab. 
    2. Choose a header values column (the variable to display at the top of the crosstab), a label values column (the variable to display on the side of the crosstab), and an aggregate values column (the variable that will be aggregated within the crosstab). 
    3. Select an aggregate function. This is the mathematical function to be performed on the aggregate values column, based on the header and label values columns.
    4. In this example, the amount awarded is being aggregated, and we've chosen a sum as the function. This means that the highlighted cell shows the following data: The sum of the amounts awarded for requests with "Health" as the program area, and that are within the 2018 Fall Grants process. 

     

     

  • Apply an Aggregate Function to Data in a Report

    After running a new report or opening a saved report, there are many actions you can take to work with the data. One of these options is to apply an aggregate function to the data (e.g. calculate totals, averages, etc.). Before doing so, perform any grouping as needed to the data.

    1. On your report, click the Gear icon if the options within it are not already visible.Apply_an_Aggregate_Function_1.png

    2. Click Aggregate.Apply_an_Aggregate_Function_2.png 
    3. Select the Column of data on which you'd like to perform the aggregate function.Apply_an_Aggregate_Function_3.png 
    4. Select the Aggregate Function you'd like to perform.Apply_an_Aggregate_Function_4.png

    5. Click Add.Apply_an_Aggregate_Function_5.png 
    6. The aggregate function will now be performed on the column of data selected. If you first grouped the data, you'll see the results at the group level as well as at the overall level for the column.Apply_an_Aggregate_Function_6.png 
    7. If you'd prefer for the overall results to be at the bottom of the report, select Bottom in the Results Positioning drop-down. You can also hide the function name by checking the Hide Function Names box.Apply_an_Aggregate_Function_7.png
  • Add a Formula to a Report

    After running a new report or opening a saved report, there are many actions you can take to work with the data. One of these options is adding a formula to the report. A formula allows you to perform a function on the data in a report. Some of these functions include: 

    • Perform mathematical functions on the data in your report.
    • Use logical functions to replace data.
    • Remove unwanted spaces or shorten text.
    • Format a number as currency or a percentage.
    • Format dates and time.
    • Remove decimal points and round numbers.
    • Calculate elapsed time.
    • Count the number of characters in a response.

    When you add a formula to a report, a new column is automatically added as well, containing the data resulting from the formula. Follow the steps below to add a formula to a report. 

    1. On your report, click Formula.Add_a_Formula_to_a_Report_1.png 
    2. Enter a Name for the new column, which will contain the data resulting from the formula.Add_a_Formula_to_a_Report_2.png

    3. Select a formula from the Formula drop-down (which will automatically insert it into the Formula box), or type a formula directly into the Formula box.Add_a_Formula_to_a_Report_3.png 
      • You can also click Formula Help for a list and descriptions of common formulas.Add_a_Formula_to_a_Report_4.png 
    4. Once your formula is in the Formula box, place your cursor or highlight the placeholder text where you need to refer to an existing column of data in your report.Add_a_Formula_to_a_Report_5.png

    5. Select the column from the Insert a Column drop-down, or manually type the label of the column inside brackets (e.g. [Project Name]). If you select the column from the drop-down, it will automatically insert where you placed your cursor or highlighted text.Add_a_Formula_to_a_Report_6.png

    6.  Select the Data Type of the data that will result from the formula.Add_a_Formula_to_a_Report_7.png 
    7. If there is a specific format in which you want to see the resulting data, select it in the Display Format drop-down. Otherwise, leave the drop-down blank.Add_a_Formula_to_a_Report_8.png  
    8. Click Add.Add_a_Formula_to_a_Report_9.png 
    9. You'll now see the new column with the resulting data added to the report.Add_a_Formula_to_a_Report_10.png
  • Using Filters in Reports

    An easy way to filter out unnecessary data before you run your report is to use the Pre-Filters stage. This stage allows you to select specific processes, request statuses, and form types to report on. If you choose to not select something, it will automatically be filtered out of your report when you run it for the first time.

    TipExampleMany reports will initially contain requests that were marked as "Abandoned" before a decision was made. One common use of pre-filtering is to eliminate these requests from your report by un-checking that request status from the Request Statuses section.

    You can use the Filters button to eliminate rows you don't want in your report. In addition to hiding unwanted data, filtering eliminates rows from any totals or averages you have in a report. Rows that you have filtered out will not be included in any charts or crosstabs you create, either.

    To begin filtering, click the Filter button and choose the column you want to include in your filter. Then select the type of comparison you would like to make in the column. Finally, add the value you would like to include in the comparison. For example, the filter below would allow you to see only requests within the 2019 Fall Grants process in your report. Requests from other processes would be filtered out.

    When filtering on numeric data or dates, you can filter by a specific value, or you can filter by a range. In the example below, the filter includes requests with a decision date between January 1, 2019 and a sliding date of "Today". You can also filter on other sliding dates. Those would allow you to filter requests for a date range that would be updated every time you run the report. For example, you could filter out all but the grants made last month, or all but payments due next quarter (updated each time your run the report).

    You may need to add more than one filter to a report. In the example below, two filters have been added. The first filters the amount awarded to only include values over $500. The second includes requests with a decision date between January 1, 2019 and a sliding date of today.

    Note that the example below filters amount awarded to display values over $500 AND those with a decision date between those two dates. Click the AND button to change the operator to OR.

    In some situations, you may need to group filters together with parentheses. 

    • This can be done by using the arrows that appear to the right of each filter when you have more than one filter in a report. 
    • The example below has a single filter that displays amount awarded values greater than $500. The two specific date range filters are placed between parentheses, telling the report to include requests from 1/1/2019-6/1/2019 OR 1/1/2018-6/1/2018.  
      • The combination of these filters reads as: Show requests with an amount awarded greater than $500, AND that have a decision date between 1/1/2019-6/1/2019 OR 1/1/2018-6/1/2018.
    • Click the (-) icon to remove a set of parentheses, or the (+) icon to add a set.
  • Group Data in a Report

    After running a new report or opening a saved report, there are many actions you can take to work with the data. One of these options is grouping the data.

    1. On your report, click the Gear icon if the options within it are not already visible.Group_and_Sort_Data_1.png

    2. Click Group.Group_and_Sort_Data_2.png

    3. Select the Column on which you'd like to group the data.Group_and_Sort_Data_3.png 
    4. Click Add.Group_and_Sort_Data_4.png 
    5. Your report data will now group according to the selected column.Group_and_Sort_Data_5.png

    6. You can repeat these steps to add sub-grouping. In the example below, we grouped by Organization State within the Focus Area grouping.Group_and_Sort_Data_6.png
  • Report Columns and Paging

    You can adjust column widths and column order, hide columns, and change the number of rows that appear in the report.

    • Click the left side of a column to drag and drop the column elsewhere in the report. 
    • Click the right side of a column and drag it in either direction to make the column larger or smaller. 

     

    You can also hide columns you don't need (or show any hidden columns). Click the gear icon, then click Columns. Deselect any columns you do not want to see on the report. Then click OK.

    TipYou may need a column in a report for a filter or a formula, but if you don't need that data displayed you can hide the column. It will still be available for filtering and formulas.

     

    Paging options allow you to show all report data on one page, or to adjust the number of rows per page on your report. Click the gear icon, then click Paging. Make your desired selection, then click OK.  

     
  • Exporting Data from Reports

    Once you have run a report, you can export it to an Excel, CSV, or PDF document. Click the export icon and choose your desired file type for the data. The data will then download to your computer. 

    TipEven when you plan on working with the data in Excel, it is often easier to export to CSV, which will normally open in Excel.

  • Running a Report

    You can run a new report as soon as you have saved the data set by clicking the Run New Report button in the upper right hand corner of your screen.

    Another way to create a new report is by clicking on the Run Report button associated with a data set you have already created under the "Reporting Data Sets" tab.

    Once you run a new report your data set will open in a new tab.

    Once you have created a new report, you can save the changes you have made by clicking "Save New Report" in the upper right hand corner of your screen.

    When editing an existing report, you can save the changes you have made by clicking "Save Changes" or you can save the changes as a new report by clicking "Save New Report."

     

     

  • Combine or Rename Fields in a Data Set

    When building a data set to run a report, you can combine data from different fields into one column in the report. You can also rename a field, which will only affect the column label in the report (it does not affect the actual field elsewhere in your site).

     

    Combine Fields 

    1. Navigate to the Choose Data Fields page of the data set.Combine_or_Rename_Fields_in_a_Data_Set_1.png

    2. In the Selected Fields list, select the fields that you'd like to combine into one field (or column of data in a report).Combine_or_Rename_Fields_in_a_Data_Set_2.png

    3. Click the Square icon.Combine_or_Rename_Fields_in_a_Data_Set_3.png

    4. Click Combine/Rename Fields.Combine_or_Rename_Fields_in_a_Data_Set_4.png

    5. Enter the Combined Field Name, which becomes the name of the column of data in a report run with this data set.Combine_or_Rename_Fields_in_a_Data_Set_5.png 
    6. Enter a Separator for the data in this combined column. This is what separates the data from the different combined columns within each cell of the new column.
      • If you're combining the Contact First Name field with the Contact Last Name field, for example, you might enter a space as the separator.Combine_or_Rename_Fields_in_a_Data_Set_6.png 
    7. Click OK.Combine_or_Rename_Fields_in_a_Data_Set_7.png

    8. In the Selected Fields list, you'll now see the fields you combined listed under the newly named field (or column of data in a report).Combine_or_Rename_Fields_in_a_Data_Set_8.png 

    Rename a Field

    1. Navigate to the Choose Data Fields page of the data set.Combine_or_Rename_Fields_in_a_Data_Set_1.png

    2. In the Selected Fields list, select the field that you'd like to rename.Combine_or_Rename_Fields_in_a_Data_Set_9.png

    3. Click the Square icon.Combine_or_Rename_Fields_in_a_Data_Set_10.png 
    4. Click Combine/Rename Fields.Combine_or_Rename_Fields_in_a_Data_Set_11.png

    5. Enter the Combined Field Name, which simply renames the column of data for this field in a report run with this data set.Combine_or_Rename_Fields_in_a_Data_Set_12.png

    6. Delete anything in the Separator text box.Combine_or_Rename_Fields_in_a_Data_Set_13.png 
    7. Click OK.Combine_or_Rename_Fields_in_a_Data_Set_14.png 
    8. In the Selected Fields list, you'll now see the original field listed under the newly renamed field.Combine_or_Rename_Fields_in_a_Data_Set_15.png
  • Search while Building Data Sets

    When building or editing a data set you can use the search option in the top right of the page to locate specific fields. 

    To use the search box, just start typing the label of the question for which you are searching. As you type, the tab titles (Contact, Request, Application, etc.) will be updated with the number of times the text you have entered appears in each tab.

    If you expand the tab, you will only see questions that contain the text you entered. If they have already been selected, they will have a check in the box next to their name.

  • Building New Data Sets

    "Reporting Data Sets" is where you go to select the information you want to include in reporting or to export out of the system. To access the reporting data set tools, from within GLM, click on the Reporting Data Sets tab from the Reports & Data Sets page. 

    All sites will have five default reporting data sets listed in the Reporting Data Sets tab. These sets cannot be edited or deleted, but they can be copied and used as building blocks for other data sets.

    If you do not want to use the predefined data sets, or if you want to build a data set from scratch, click the "Add New Data Set" button in the upper right hand corner. This will take you to the screen on which you can create a new data set.

    When you add a new data set, you will be prompted to add a Name and an optional Description. Be sure to name it something you’ll recognize in the future! building_new_data_sets_2.png

    Once the name and description have been added, click "Create Data Set" in the upper right hand corner.

    building_new_data_sets_2.2.png

     

    Next, you will be prompted to select the Processes, Request Statuses, and Form Types you would like to include in this data set. By default, the Request Statuses and Form Types sections are collapsed.  Click on the bar to open that section which will allow you to select those additional pre-filters.

    Clicking “Save Filters” will bring you to the next step.

    The next stage is where you select specific data fields to report on.

     

    Contacts Tab: Information Captured during Registration

    In a GLM site, this information will include organization and contact information. In an SLM site, there will not be organization information.

     

    Request Tab: Default Information within Grant Lifecycle Manager

    Important fields within this section include: Process Name, Project Name, Request Status, Submission Assignee information, Evaluator Name (first and last), Installment and Payment Information

     

    LOI, Application, and Follow Ups Tabs

    These tabs include the forms the applicant/grantee filled out as well as the evaluations tied to the relevant stages (follow ups do not contain evaluations). Within each tab, each process that includes the form will be in its own accordion box. When you expand each process's accordion box, you will see all of the selected form's questions for that process.

     

    Decisions Tab: Approval, Installment and Denial Form Answers

     

    Selecting Data

    You can add questions from any of the tabs, which means you can report and export data from multiple forms within the processes you selected to build this data set. This capability will be very important for building outcomes report; you will be able to take information from the application and put it next to information on a follow up.

    To select a question to include in your data set, just check the box next to the question's label.

    Right after you check the box, that question will show up in the list of selected fields on the right side of the screen.

     

    Running a Report/Saving a Data Set

    Once you have defined your data set with the desired fields and organization options, you can either click the "Save" or "Run New Report" buttons. Note that if you click "Run New Report" it will automatically save the data set.

     

    Clicking "Run Report" will take you into the reporting tool. Clicking "Save" will save your data set. You can then access the data set from the Reporting Data Sets tab on the Reports Home page.

    If you edit a data set any saved reports built off of that data set will be updated.

    • You would copy a data set if you want to re-use most of its definition (the select questions, etc.), but you do not want to change any reports tied to it.