I am building an analytic app in which I want the user to be able to group by one of several levels of aggregation (ie. group by Product, Product Category, or Product Class) using a list box or drop-down menu. Ideally, this would occur In-Database.
I am able to update a single variable with an action tool-- so I can change "Group By Product" to "Group by Product Category". The problem is, I want to be able to group by Product, Product Category, and Product Class together. Then I want the user to be able to select just Product, and have it update to eliminate the other two dimensions from the group by clause. I can't quite figure out how to achieve this functionality. To make matters worse, I am blending flat files in to temporary tables in the database before the summarize function, so I can't directly alter the group by clause in SQL.
I hope this makes sense, and thanks for the help! This is my first time posting here, but it certainly won't be my last.
I don't use In-Database tools all that often, so this answer is somewhat generic. If you run into issues, I can try and do a deeper dive.
I'm assuming your 3 fields are named consistently. If that is the case, you can use a List Box interface to let users check off which fields they want to group by (assuming that you want to let them use any combination of these fields). You would likely need a condition to handle the case of all boxes being unchecked. You can use an option in the List Box called "Generate Custom List", which would let you create a list of your three fields, separated by commas.
Then you would connect this list box to the tool where your Group By is currently occurring using an Action Tool, and replace your default Group By statement.
Alternatively, if you only want three options (group by all 3, group by product, group by product category), a Drop Down tool could replace the List Box in the above example.