I am trying to build a BAQ that shows a Supplier total dollars based on Receipt date range.
I was able to put together a Sum in the BAQ to get Vendor Totals - however I cannot figure out how to apply a date range.
I know how a add a custom tracker and add date fields. However, It looks like I have to do a GroupBy on the date in the BAQ and then I get totals by Day (or date) but I only need the Vendor Totals.
If you have tackled this or anything like this, I would appreciate any and all advice.
You can add parameters for filtering out the BAQ based on date range. Just make one greater than, one less than, and filter in the query that you are using to add up the dollars. Every time you refresh in the dashboard you will need to pick dates.
Or you can add a date range in the dashboard, and use the summary options in the grid to add up the totals. You group by vendor (not date) and you will get dollars per vendor. You wouldn’t do any grouping/summing in the BAQ for this, it needs to come to the dashboard raw, which depending on what the users want, may be a pain.
The users were hoping that the totals would be in the BAQ Grid on the Dashboard so they could export to Excel and not have to bring over ALL the records. Just the Vendor Totals.
See screen shots below to set it up. I used a different table, but you get the idea. two filters, less and greater than, by specified parameter. Then set the parameter options as shown (I like to use skip if empty so it grabs everything, and if you want to can set a default that will populate when the parameter comes up). Then when you test the BAQ, you get the date range pop up. The same thing will happen in the dashboard.
Unfortunately the tracker view can only filter the data coming from the BAQ (without some custom work anyways) so grouping and summing without using the grid functions isn’t possible (easily).
Since the parameter sends the info in before the BAQ is processed, it allows more things to be possible.
What I would really like to learn how to do is make a regular text box pass the parameter in so it looks like it’s a regular tracker view filter and skips the pop-up box, but is actually a parameter, which is basically what you want. Not really functionally different but less confusing to the end user.
If anyone has an example of that, Mr. Dave and I would be grateful to see it.
