BAQ with TopLevel dynamic date range dataset?


(Adam Szymanski) #1

I’m trying to build a dashboard with a chart view and I’d like to start the BAQ with a dataset of specified dates. That is, the top level data should be “today” + 30 days, resulting in a set of dates:



From this top level dataset, I’ll build subqueries to extract specific info for each date.

Is there any way for me to auto generate this dataset in a BAQ?

(Brandon Anderson) #2

In the BAQ, there are options for criteria. One of them is today + (day/moth/year etc). You can set the dynamic filters there. Do you have a BAQ started yet?

As far as getting those dates, I usually grab a table that has the dates I will need and set the criteria that way,. then only show the date and group by it. That gets me a list of dates. It only gets ones that already exist in the table, but most of the time, I’ve found that is acceptable.

The other option is to make a CTE query that loops through to build the data set. That’s a pain.

(Adam Szymanski) #3

I have the other pieces of the BAQ built out, just need this top level to start with a list of dates. I don’t believe I can base the date set off of another table because the range will typically be 30+ days in the future. If I did try this, which table would be a good source with lots of future dates? Joboper?

(Brandon Anderson) #4

What are you joining the dates to? Why not use that table? (I’m probably not understanding the end use case here, so that may not be a valid suggestion)

(Calvin Krusen) #5

If the “+30” is constant, just add a calculated field to the with the formula being the date field you use to filter (say it’s “Field_you_use_to_filter”), with 30 added to it.

Then in the dashboard, keep that newly added field (we’ll call it “Calculated_FieldAdded”) hidden.

Set 2 filters on the Grid to be:

  1. Field_you_use_to_filter >= TODAY
  2. Calculated_FieldAdded <= TODAY