Filtering data with data model parameters
1. Filtering data with data model parameters
Welcome to chapter three! In this final chapter, we'll review two more features of data models, parameters and column-level security, before taking a step back and looking at a fully-featured case study.2. Concept - What are parameters
To start, let's discuss parameters. Parameters are control elements, used inside a data model to filter data sources. Unlike workbook control elements, they can be controlled from outside the data model.3. Concept - What are parameters
Let's think about a simple example.4. Concept - What are parameters
Imagine a data model with a base table of transactions. Users often perform analysis based on a dollar range of transaction amounts, such as examining all transactions with an amount greater than 1000 dollars.5. Concept - What are parameters
Without parameters, this use case would actually be difficult to implement in a data model. Consider some possibilities that don't use a parameter.6. Concept - What are parameters
We could set the desired range as a filter on our table, but then this data model would only work for transactions in the range specified in the filter. What happens when users ask for another set of transactions? Or want to select a custom range? Filtering the table like this is not scalable or customizable.7. Concept - What are parameters
Conversely, we could provide users the entire transactions table from the data model, which they could then manually apply a filter to each time they need to slice the data. But, while this provides flexibility, it introduces problems for consistency and efficiency. What if users filter by different ranges, different fields, or start from different defaults?8. Concept - What are parameters
Parameters help us get the best of both worlds. By adding a parameter, we can set a default value, while also providing flexibility to our end users.9. Demo
Let's setup a control in a datamodel and use it as a parameter in a workbook.10. Demo
Let's start with a data model with two tables for loans and applications. This data model is used to analyze applications and loans by start date. Each month, it shows all the loans that began, and the applications that were received in that month. Let's use a parameter to filter both tables by date.11. Demo
In the data model, use the Add element bar to add a date range control.12. Demo
In the editor panel, under properties, set a control ID value. Let's call it loan-date-range, and make it required.13. Demo
In the Targets tab, select Add filter target, and target the Start date column of the Loans table. Add another filter target, and select the Application date column of the Applications table. Because our control is set to required, both tables are blank until we enter a control value.14. Demo
In the control, let's set a default value. Set it to the range January 1st to January 31st 2025. Both tables update to show data from that range.15. Demo
Now, let's publish, and review this content in a workbook.16. Demo
In the workbook, add both tables from the data model. They appear filtered, using the default date range.17. Demo
Add a date range control to the workbook. In the editor panel, under properties, open the target tab. Add a source parameter, and select the loan-date-range control from the data model. Now, we can enter a new date range, like March 1st to 31st of 2025, and the tables update, allowing us to override the default.18. Demo
Now we can see the use case for parameters, allowing us to apply a default value to multiple elements while still providing flexibility to our users.19. Let's practice!
Now it's your turn!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.