Advanced Usage of Web Dashboard: Data Management with Custom Parameters and SQL Queries

[TR ARTICLE]

Hello friends! Today I’ll share some important information about using parameters and managing them on the Dashboard. You’ll learn how to define, use, and manipulate parameters while working with Web Dashboard. Let’s get started!

1. Parameter Selection and Definition
2. Using Parameters
3. Parameter Manipulation on the Backend
4. AllowExecutingCustomSql and ValidateCustomSqlQuery: Management of Dashboard Data Sources and Custom SQL Queries
5. Conclusion
6. Final Notes
7. Resources

Parameter Selection and Definition

1. Parameter Definition

• Fill in the relevant fields to define parameters.
• Click here to access detailed parameter settings, details, and rule sets.

2. Saving Parameters:

• After all operations are completed, click the Save button on the Dashboard menu.

Using Parameters

1. Selecting Data Source:

• Click on the Data Sources option in the Dashboard menu.
• Click the Add button to select the SQL data source, and specify the query name and SQL string in the Add Query section.

2. Parameter Configuration:

• The parameter name you specify should match the parameter name you will use in the WHERE clause.
• Select the correct data type in the Expression and Result Type sections (in this example, String is used).

3. Assigning Parameter Values::

• Complete the process by selecting the parameter choice you defined in the Value section.

Parameter Manipulation on the Backend

It’s possible to access and manipulate parameters from the backend. However, these changes won’t be reflected in the parameter definition screen, you’ll only see the changes in the results.

1. Using CustomParameters Event:

• You should use the **CustomParameters **event for changes related to parameters.
• Example usage:

this.CustomParameters += MultiTenantDashboardConfigurator_CustomParameters;

private void  MultiTenantDashboardConfigurator_CustomParameters(object sender, CustomParametersWebEventArgs e)
{
 //throw new NotImplementedException();
}

AllowExecutingCustomSql and ValidateCustomSqlQuery: Management of Dashboard Data Sources and Custom SQL Queries

• You can manage SQL queries using **AllowExecutingCustomSql and [ValidateCustomSqlQuery** ](https://docs.devexpress.com/XtraReports/115119/web-reporting/common-features/application-security/custom-sql-query-validation)events.
• You can advance the process by capturing the SQL string expression in the event when adding the data source by clicking the Add button.
• You can control changes on the dashboard using the ValidateDashboardCustomSqlQueryWebEventArgs event.

1. Adding a Custom SQL Query to the Data Source

1. Entering Dashboard Designer Mode:

• Access Dashboard Designer mode and go to the relevant menu to access data sources.

2. Adding Data Source:

• Click the Add button in the data sources section.
• Select your application’s custom database data source.

3. Adding Query:

• Click the Add Query button on the screen.
• Enter the query name and SQL string. If we’re not using parameters at this step, we can simply click “Finish.”

2. Using AllowExecutingCustomSql and ValidateCustomSqlQuery Events

1. Defining Events:

• In the Dashboard Designer, define events in the code base to manage your SQL queries using AllowExecutingCustomSql and ValidateCustomSqlQuery events.
• // The AllowExecutingCustomSql event allows SQL queries to be executed in the database. We can control query execution permission using this event.

this.AllowExecutingCustomSql = true;

• // The ValidateCustomSqlQuery event validates the validity of custom SQL queries. We can check the query’s correctness using this event.

this.ValidateCustomSqlQuery += DashboardMultiTenantConfigProvider_ValidateCustomSqlQuery;

1. Event Handling:

Conclusion

Using parameters and managing them on the Dashboard makes your data analysis processes more flexible and effective. By following the steps in this guide, you can successfully define and manage your parameters.

Final Notes

I’ve shared my experiences and tips with you in this process. I hope it’s helpful to you when using the Dashboard. Feel free to share your own experiences and questions in the comment

Resources:

• DevExpress Documentation on Custom Parameters
• Creating a Dashboard Parameter
• Using Query Parameters
• Specifying Dashboard Parameter Values
• Dashboard Parameters
• Custom SQL Query Validation