I am pulling SQL data into Excel through Microsoft Query. I have set of 2 ? parameters for a set of sales data to filter by store # and by item, and I want them to be able to be used as alternative filter options, not necessarily both at the same time.
For example: Usually my users want to look at ALL the items's sales for ONE store, or ALL the stores for ONE item. So I want the 2 parameters to be set as optional to use, and unless filled in, the query to remain unfiltered.
Right now, my query data remains blank until I fill in the ? blanks to add data. Is it possible for me to set the default to be unfiltered first?
Currently I go to Excel --> "Data and Connections" --> "Properties" --> Definition tab --> Command Text: In this SQL query code, I add a WHERE clause for the parameters:
WHERE store_number = ? AND item_name = ?
Then of course I tell it which cell I want to plug the parameter into. As a result, this starts me off with no data, until I fill BOTH parameters in. But I want it to start unfiltered for both, and then filter if I type in a parameter.
I have created a workaround with a macro button that goes to the query code and deletes one of the Where clause lines to eliminate the pre-filtering process, and another macro that "re-activates" the filter by adding the Where clause back in, but I wonder if there's a simpler way. Plus, this workaround is not as user-friendly with my end users who will be using the report.