How to Build Dynamic JavaScript Reports Using Parameters and Queries

In a previous article, How to Connect JavaScript Reports to a PostgreSQL Database with Express, we walked through the process of building an API-driven reporting pipeline, using PostgreSQL, exposing data through an Express server, and consuming that data inside the ActiveReportsJS Report Designer. But while retrieving data from fixed endpoints is a great starting point, real-world reporting rarely stops there: that’s where parameters and queries come into play.

In this article, we’ll build on the foundation established in the PostgreSQL and Express setup, taking the next steps: introducing parameters in ActiveReportsJS and wiring them into backend queries. Instead of hardcoding endpoints like /ford or /gm, we’ll explore how to pass values from the report to your API, use those values in SQL queries, and return filtered results back to the report.

By the end of this article, you’ll understand how to:

• Passing parameter values to a REST endpoint

• Using parameterized SQL queries in Express for filtering

• Defining and Configuring Report Parameters in ActiveReportsJS

If the previous blog showed you how to connect your reports to live data, this one shows you how to put your users in control of that data, all while maintaining a clean, scalable architecture.

Ready to Check Out the Latest from ActiveReportsJS? Download a Free Trial Today!

Passing Parameter Values to a REST Endpoint

Before we get into actually building out our parameterized report, the first thing that we’ll need to do is set up our Express server to be able to read query values from the endpoint request. In the case of this article, we’ll give our users the ability to query what model of vehicle they want to load into the report.

Our API endpoints on the Express server currently look like so:

app.get('/ford', async (req, res) => {
    try {
        const results = await db.query('SELECT * FROM ford');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});
app.get('/gm', async (req, res) => {
    try {
        const results = await db.query('SELECT * FROM gm');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});
app.get('/stellantis', async (req, res) => {
    try {
        const results = await db.query('SELECT * FROM stellantis');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});

What we’ll need to do is modify them so that they can return data if a query parameter is passed via the URL, as well as if it is just a request to retrieve the entire database table.

To do so, we just need to set the query parameter to a variable name. For this sample, since we’re just creating a single query parameter around a vehicle’s model value, we only need to create a single variable and set it to that query value:

app.get('/ford', async (req, res) => {
    try {
        const model = req.query.model;
        const results = await db.query('SELECT * FROM ford');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});

Expanding this to each of our other API endpoints, our code will look like so:

app.get('/ford', async (req, res) => {
    try {
        const model = req.query.model;
        const results = await db.query('SELECT * FROM ford');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});
app.get('/gm', async (req, res) => {
    try {
        const model = req.query.model;
        const results = await db.query('SELECT * FROM gm');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});
app.get('/stellantis', async (req, res) => {
    try {
        const model = req.query.model;
        const results = await db.query('SELECT * FROM stellantis');
        res.status(200).json(results.rows);
    } catch (err) {
        res.status(500).send('Server Error');
    }
});

Next, we’ll set up the endpoints' ability to use that query value to make our database requests.

Using Parameterized SQL Queries in Express

Now that we have the query value stored in a variable, we can make use of that to determine if a user did pass a value to the server, and if so, use it to build out our SQL statement for the data request.

First, we’ll set up an if/else statement to determine whether or not a query value was passed to the server by the user:

app.get('/ford', async (req, res) => {
    try {
        const model = req.query.model;
        if(model != undefined) {
            // Build out the SQL Statement
        } else {
            const results = await db.query('SELECT * FROM ford');
            res.status(200).json(results.rows);
        }

    } catch (err) {
        res.status(500).send('Server Error');
    }
});

Now, we can build out the SQL statement inside the 'if' statement. A typical SQL statement where we’re requesting specific data from a row would look something like this:

SELECT * from ford WHERE model = 'Mustang';

So, we’ll need to assemble this string using the query parameter passed to us:

app.get('/ford', async (req, res) => {
    try {
        const model = req.query.model;
        if(model != undefined) {
            const results = await db.query("SELECT * FROM ford WHERE model = '" + model + "'");
            res.status(200).json(results.rows);
        } else {
            const results = await db.query('SELECT * FROM ford');
            res.status(200).json(results.rows);
        }

    } catch (err) {
        res.status(500).send('Server Error');
    }
});

And that’s really all there is to it. The /ford endpoint will now be able to check if the user passed a query parameter to it, and if so, build out the correct SQL statement for the database.

If we expand that across all of the endpoints, our code will look like this:

app.get('/ford', async (req, res) => {
    try {
        const model = req.query.model;
        if(model != undefined) {
            const results = await db.query("SELECT * FROM ford WHERE model = '" + model + "'");
            res.status(200).json(results.rows);
        } else {
            const results = await db.query('SELECT * FROM ford');
            res.status(200).json(results.rows);
        }
    } catch (err) {
        res.status(500).send('Server Error');
    }
});

app.get('/gm', async (req, res) => {
    try {
        const model = req.query.model;
        if(model != undefined) {
            const results = await db.query("SELECT * FROM gm WHERE model = '" + model + "'");
            res.status(200).json(results.rows);
        } else {
            const results = await db.query('SELECT * FROM gm');
            res.status(200).json(results.rows);
        }
    } catch (err) {
        res.status(500).send('Server Error');
    }
});

app.get('/stellantis', async (req, res) => {
    try {
        const model = req.query.model;
        if(model != undefined) {
            const results = await db.query("SELECT * FROM stellantis WHERE model = '" + model + "'");
            res.status(200).json(results.rows);
        } else {
            const results = await db.query('SELECT * FROM stellantis');
            res.status(200).json(results.rows);
        }
    } catch (err) {
        res.status(500).send('Server Error');
    }
});

Now, with the server running, if we go to the browser and visit http://localhost/ford?model=Mustang we’ll see the following in the browser:

Defining and Configuring Report Parameters and Data Requests

With the Express server now set up to accept query parameters, as well as the ability to build out the required SQL statements, we can now move on to integrating this functionality into an ActiveReportsJS JavaScript report.

In our application, we have the report designer set up to load in a report template that we’ll modify while building out the report:

The first thing we’ll need to do is set up the parameters the user can enter. We need to do this first, since the data request will use both of them to retrieve the data. In the parameters panel, we’ll be adding two parameters: vTable and vModel:

For the vTable parameter, we’re giving it a name and a prompt, and defining parameter values for the user to select from. Since this is specifically about selecting the table, providing predefined values lets users select from a dropdown to see which tables are available to them.

For the vModel parameter, we’re giving it a name and a prompt, like with vTable, but we’re not giving any pre-defined values to the users. Instead, we’re setting it to allow null and blank values. This will allow users to leave this value blank and retrieve the entire table of data from the database.

The next thing that we’ll need to do is get the Data Source set up; to do so, click on the Data panel in the top-right and, underneath the Data Sources section, click the Add button to bring up the data source creation wizard:

If you’re familiar with ActiveReportsJS, then this looks pretty standard as far as creating a data source, but if not, we’ll step through this really quickly.

First, we give the data source a name (PostgreDB), we define the data format that the data will be delivered in (in this case, JSON), and we give it a web service endpoint, where the Express server is running. Then, all we do is hit the Save Changes button, and the data source is complete.

Next, we’ll use the data source and the parameter values to build out our data set:

Going from top to bottom of the wizard, the first thing that we do is give it a name (Vehicles), and we define the URI/path value. For the path, we’re setting it to the value of the vTable parameter; the @ symbol signifies that this is a parameter value and that the reporting tools should look there for the value.

Then, we’re defining our parameters - in this case, the parameter is ?model and the value is set to our vModel parameter.

Finally, we’re also setting the JSON path to $.*, telling the reporting tools not to filter anything from this point onward, since we’re handling filtering through the API request. Now, if you go to save the changes to the data set, you’ll get a pop-up like so:

This is to ensure that the reporting tools can retrieve data based on the information provided in the user's parameters. If we leave vModel blank, set vTable to Ford, and click Save & Run, the reporting tools will validate the data, completing this portion of the setup.

Next, we’ll build out a simple table that can be used to display our data:

This table setup is fairly straightforward. We add a table that includes all of the fields from our data set, and we’ll add another row above the table that will let us know what vehicle table we’ve currently got loaded in from the database.

Now, if we run the report in the viewer, we should see the following on load:

As you can see, nothing has been loaded into the report yet - all we see is the parameter view that contains the parameters that we’ve set up. We’ll go ahead and uncheck the null box and set the table equal to General Motors, and then when we hit the preview button, we’ll see the following:

Now, to test out filtering by specific models, we’re going to change the table over to the Stellantis table, and we’ll set the model value equal to Charger. When we run the report, we’ll see the following:

Conclusion

In this article, we expanded on the API-driven reporting foundation established in our previous PostgreSQL and Express walkthrough by introducing parameters and query-based filtering into the workflow. Rather than relying on static endpoints that always return entire tables, we enhanced our Express server to accept query string values and use them to dynamically construct SQL statements.

On the backend, we modified each endpoint to read query parameters from the request, using conditional statements to build our SQL queries based on user input. This allowed the same route to either return a full dataset or filter results by a specific vehicle model, demonstrating how a small adjustment to your API layer can significantly increase flexibility.

On the reporting side, we’ve defined and configured two ActiveReportsJS parameters - vTable and vModel - to give users control over both the data source and the filtering criteria. The result is a clean, end-to-end flow in which user input in the JavaScript report viewer drives API requests, which in turn shape the SQL queries executed against the database.

Happy coding!

Ready to Check Out the Latest from ActiveReportsJS? Download a Free Trial Today!