Can You Filter DataLink’s To A SQL Database With Values From A Matrix?
Trying to link to a data source and I’d like to be able to give the users a simple set of canvas boxes they can use to input some filter parameters. Is there a way using the Qualifiers section of the DataLink Wizard, or any other way, to let users dynamically control the filter parameters?
I found the solution in an older forum post. In SQL expert mode the syntax is:
{MatrixName::UpperCategory(#):TargetCategoryItem}
ex
Where Table.ColumnValue IN({Plans::Planners:Name1})
In this fictional matrix there are many plan names stored in a Names Category with a Planner Category sitting above. This will pass to SQL all the plan Name1 values associated with all the Planners. The main pitfall is don’t include the Category of the target category item in the string ({..Names:Name1} =/= good).
You appear to be able to increase specificity of selection like so:
Where Table.ColumnValue = {Plans::Planner1:Name1} which should return to SQL the first plan name for the first planner in the series.
This should help for anyone else who wants to let model users filter data before importing it into the model without them needing to touch the underlying SQL, just a simple data entry canvas.
If anyone sees errors in my syntax let me know so I can correct it. This is all from me trawling the forum so i wouldn’t call it a deep and error proof understanding of the system.