Step 4: Summary Report For Managers

 2024-04-18
In this tutorial step you'll use more queries and JavaScript to create a report page for application managers, handy information that they can use to help coordinate their efforts to fulfill the requests. The page will look like the following:

See a live example.

Create Custom SQL Queries

First, create three custom SQL queries over the "Reagent Requests" list in order to distill the data in ways that are useful to reagent managers. Here, we create custom SQL queries using the LabKey UI, then use LABKEY.QueryWebPart to display the results as a grid. As part of writing custom SQL, we can add Metadata XML to provide a URL link to the subset of the data listed in each column.

Query #1: Reagent View

First define a query that returns all the reagents, the number of requests made, and the number requested of each.

  • Return to the home page of your "Reagent Request Tutorial" folder.
  • Select (Admin) > Go To Module > Query.
  • Select the lists schema.
  • Click Create New Query.
  • Define your first of three SQL queries:
    • What do you want to call the new query?: Enter "Reagent View"
    • Which query/table do you want this new query to be based on?: Select Reagent Requests
    • Click Create and Edit Source.
    • Paste this SQL onto the Source tab (replace the default SQL query text):
SELECT 
"Reagent Requests".Reagent AS Reagent,
Count("Reagent Requests".UserID) AS TotalRequests,
Sum("Reagent Requests".Quantity) AS TotalQuantity
FROM "Reagent Requests"
Group BY "Reagent Requests".Reagent
    • Click the XML Metadata tab and paste the following (replace the default):
<tables xmlns="http://labkey.org/data/xml">
  <table tableName="Reagent View" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="TotalRequests">
        <fk>
          <fkTable>/list/grid.view?name=Reagent%20Requests;query.Reagent~eq=${Reagent}</fkTable>
        </fk>
      </column>
      <column columnName="TotalQuantity">
        <fk>
          <fkTable>/list/grid.view?name=Reagent%20Requests;query.Reagent~eq=${Reagent}</fkTable>
        </fk>
      </column>
    </columns>
  </table>
</tables>
    • Click Save & Finish to see the results.
  • Depending on what requests have been entered, the results might look something like this:

Query #2: User View

The next query added will return the number of requests made by each user.

  • Click lists Schema above the grid to return to the Schema Browser. (Notice your new "Reagent View" request is now included.)
  • Click Create New Query.
    • Call this query "User View".
    • Again base it on Reagent Requests. Note that by default, it would be based on the last query you created, so change that selection before continuing.
    • Click Create and Edit Source.
    • Paste this into the source tab:
SELECT 
"Reagent Requests".Name AS Name,
"Reagent Requests".Email AS Email,
"Reagent Requests".UserID AS UserID,
Count("Reagent Requests".UserID) AS TotalRequests,
Sum("Reagent Requests".Quantity) AS TotalQuantity
FROM "Reagent Requests"
Group BY "Reagent Requests".UserID, "Reagent Requests".Name, "Reagent Requests".Email
    • Paste this into the XML Metadata tab:
<tables xmlns="http://labkey.org/data/xml">
<table tableName="User View" tableDbType="NOT_IN_DB">
  <columns>
    <column columnName="TotalRequests">
      <fk>
        <fkTable>/list/grid.view?name=Reagent%20Requests;query.Name~eq=${Name}</fkTable>
      </fk>
    </column>
    <column columnName="TotalQuantity">
      <fk>
        <fkTable>/list/grid.view?name=Reagent%20Requests;query.Name~eq=${Name}</fkTable>
      </fk>
    </column>
  </columns>
</table>
</tables>
    • Click Save & Finish to see the results.

Query #3: Recently Submitted

  • Return to the lists Schema again.
  • Click Create New Query.
    • Name the query "Recently Submitted" and again base it on the list Reagent Requests.
    • Click Create and Edit Source.
    • Paste this into the source tab:
SELECT Y."Name",
MAX(Y.Today) AS Today,
MAX(Y.Yesterday) AS Yesterday,
MAX(Y.Day3) AS Day3,
MAX(Y.Day4) AS Day4,
MAX(Y.Day5) AS Day5,
MAX(Y.Day6) AS Day6,
MAX(Y.Day7) AS Day7,
MAX(Y.Day8) AS Day8,
MAX(Y.Day9) AS Day9,
MAX(Y.Today) + MAX(Y.Yesterday) + MAX(Y.Day3) + MAX(Y.Day4) + MAX(Y.Day5) 
+ MAX(Y.Day6) + MAX(Y.Day7) + MAX(Y.Day8) + MAX(Y.Day9) AS Total
FROM
(SELECT X."Name",
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) THEN X.C ELSE 0 END AS Today,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 1 THEN X.C ELSE 0 END AS Yesterday,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 2 THEN X.C ELSE 0 END AS Day3,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 3 THEN X.C ELSE 0 END AS Day4,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 4 THEN X.C ELSE 0 END AS Day5,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 5 THEN X.C ELSE 0 END AS Day6,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 6 THEN X.C ELSE 0 END AS Day7,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 7 THEN X.C ELSE 0 END AS Day8,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 8 THEN X.C ELSE 0 END AS Day9,
CASE WHEN X.DayIndex = DAYOFYEAR(NOW()) - 9 THEN X.C ELSE 0 END AS Day10
FROM
(
SELECT Count("Reagent Requests".Key) AS C, 
DAYOFYEAR("Reagent Requests".Date) AS DayIndex, "Reagent Requests"."Name"
FROM "Reagent Requests"
WHERE timestampdiff('SQL_TSI_DAY', "Reagent Requests".Date, NOW()) < 10
GROUP BY "Reagent Requests"."Name", DAYOFYEAR("Reagent Requests".Date) 
)
X
GROUP BY X."Name", X.C, X.DayIndex)
Y
GROUP BY Y."Name"
    • There is nothing to paste into the XML Metadata tab.
    • Click Save & Finish.

If you do not see much data displayed by the "Recently Submitted" query, the dates of reagent requests may be too far in the past. To see more data here, you can:

  • Manually edit the dates in the list to occur within the last 10 days.
  • Edit the source XLS to bump the dates to occur within the last 10 days, and re-import the list.
  • Create a bunch of recent requests using the reagent request form.

Create Summary Report Wiki Page

  • Click Reagent Request Tutorial to return to the main page.
  • On the Pages web part, select (triangle) > New to create a new wiki.
  • Enter the following:
    • Name: reagentManagers
    • Title: "Summary Report for Reagent Managers"
    • Scroll down to the Code section of this page.
    • Copy and paste the code block into the Source tab.
    • Click Save & Close.

This summary page, like other grid views of data, is live - if you enter new requests, then return to this page, they will be immediately included.

Notes on the JavaScript Source

You can reopen your new page for editing or view the source code below to observe the following parts of the JavaScript API.

Check User Credentials

The script uses the LABKEY.Security.getGroupsForCurrentUser API to determine whether the current user has sufficient credentials to view the page's content.

Display Custom Queries

Use the LABKEY.QueryWebPart API to display our custom SQL queries in the page. Note the use of aggregates to provide sums and counts for the columns of our queries.

Display All Data

Lastly, to display a grid view of the entire "Reagent Requests" list on the page, use the LABKEY.QueryWebPart API, allowing the user to select and create views using the buttons above the grid.

Code

The source code for the reagentManagers page.

<div align="right" style="float: right;">
    <input value='View Source' type='button' onclick='gotoSource()'>
    <input value='Edit Source' type='button' onclick='editSource()'>
</div>
<div id="errorTxt" style="display:none; color:red;"></div>
<div id="listLink"></div>
<div id="reagentDiv"></div>
<div id="userDiv"></div>
<div id="recentlySubmittedDiv"></div>
<div id="plotDiv"></div>
<div id="allRequestsDiv"></div>

<script type="text/javascript">

    window.onload = init();

    // Navigation functions. Demonstrates simple uses for LABKEY.ActionURL.
    function gotoSource() {
        thisPage = LABKEY.ActionURL.getParameter("name");
        window.location = LABKEY.ActionURL.buildURL("wiki", "source", LABKEY.ActionURL.getContainer(), {name: thisPage});
    }

    function editSource() {
        editPage = LABKEY.ActionURL.getParameter("name");
        window.location = LABKEY.ActionURL.buildURL("wiki", "edit", LABKEY.ActionURL.getContainer(), {name: editPage});
    }

    function init() {

        // Ensure that the current user has sufficient permissions to view this page.
        LABKEY.Security.getGroupsForCurrentUser({
            successCallback: evaluateCredentials
        });

        // Check the group membership of the current user.
        // Display page data if the user is a member of the appropriate group.
        function evaluateCredentials(results)
        {
            // Determine whether the user is a member of "All Site Users" group.
            var isMember = false;
            for (var i = 0; i < results.groups.length; i++) {
                if (results.groups[i].name == "All Site Users") {
                    isMember = true;
                    break;
                }
            }

            // If the user is not a member of the appropriate group,
            // display alternative text.
            if (!isMember) {
                var elem = document.getElementById("errorTxt");
                elem.innerHTML = '<p>You do '
                        + 'not have sufficient permissions to view this page. Please log in to view the page.</p>'
                        + '<p>To register for a labkey.org account, please go <a href="https://www.labkey.com/download-community-edition/">here</a></p>';
                elem.style.display = "inline";
            }
            else {
                displayData();
            }
        }

        // Display page data now that the user's membership in the appropriate group
        // has been confirmed.
        function displayData()
        {
            // Link to the Reagent Request list itself.
            LABKEY.Query.getQueryDetails({
                schemaName: 'lists',
                queryName: 'Reagent Requests',
                success: function(data) {
                    var el = document.getElementById("listLink");
                    if (data && data.viewDataUrl) {
                        var html = '<p>To see an editable list of all requests, click ';
                        html += '<a href="' + data.viewDataUrl + '">here</a>';
                        html += '.</p>';
                        el.innerHTML = html;
                    }
                }
            });

            // Display a summary of reagents
            var reagentSummaryWebPart = new LABKEY.QueryWebPart({
                renderTo: 'reagentDiv',
                title: 'Reagent Summary',
                schemaName: 'lists',
                queryName: 'Reagent View',
                buttonBarPosition: 'none',
                aggregates: [
                    {column: 'Reagent', type: LABKEY.AggregateTypes.COUNT},
                    {column: 'TotalRequests', type: LABKEY.AggregateTypes.SUM},
                    {column: 'TotalQuantity', type: LABKEY.AggregateTypes.SUM}
                ]
            });

            // Display a summary of users
            var userSummaryWebPart = new LABKEY.QueryWebPart({
                renderTo: 'userDiv',
                title: 'User Summary',
                schemaName: 'lists',
                queryName: 'User View',
                buttonBarPosition: 'none',
                aggregates: [
                    {column: 'UserID', type: LABKEY.AggregateTypes.COUNT},
                    {column: 'TotalRequests', type: LABKEY.AggregateTypes.SUM},
                    {column: 'TotalQuantity', type: LABKEY.AggregateTypes.SUM}]
            });

            // Display how many requests have been submitted by which users
            // over the past 10 days.
            var resolvedWebPart = new LABKEY.QueryWebPart({
                renderTo: 'recentlySubmittedDiv',
                title: 'Recently Submitted',
                schemaName: 'lists',
                queryName: 'Recently Submitted',
                buttonBarPosition: 'none',
                aggregates: [
                    {column: 'Today', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Yesterday', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day3', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day4', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day5', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day6', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day7', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day8', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Day9', type: LABKEY.AggregateTypes.SUM},
                    {column: 'Total', type: LABKEY.AggregateTypes.SUM}
                ]
            });

            // Display the entire Reagent Requests grid view.
            var allRequestsWebPart = new LABKEY.QueryWebPart({
                renderTo: 'allRequestsDiv',
                title: 'All Reagent Requests',
                schemaName: 'lists',
                queryName: 'Reagent Requests',
                aggregates: [{column: 'Name', type: LABKEY.AggregateTypes.COUNT}]
            });
        }

    }

</script>

Congratulations! You have created a functioning JavaScript application. Return to your tutorial page, make a few requests and check how the confirmation and summary pages are updated.

Related Topics

Previous Step