Add date parameters and how to use them in Excel Dashboards

A new Date Type parameter is introduced to simplify Dashboard creation and query.

When you define the Date Parameter you can specify the desired format

Select ‘DATE’ from the drop down list for DataType and specify your own Date Format.

When you create the date parameter with the above defaults it appears as below

When you click on the indicated button it expands to show the complete calendar

Date with Excel Dashboards

If you are querying an Excel Date Field then we need to use the following format

[ and date between #11/1/2008# and #11/17/2008# ]

The date format needs to be as “Month/Day/Year”

In order to meet the desired date condition we will need to change the date format in the Date Parameter

Click on “Full Apply”

and you should get the below new Date Format

Similarly create anothe date Field and call it To Date

Now we can use this date for any Excel Query

Below is our sample SQL with hard coded date values

Now we will replace it to use our date Parameters

Remove the text after between

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between

When you select the submenu you will see the SQL query change as below

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between ‘G_PARAM<p37:Display>’ /*value*/

Format the SQL by removing the /*value*/ strings and replace the single quotes with #

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #G_PARAM<p37:Display>#

Now repeat the process and add the To date field

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #G_PARAM<p37:Display># and #G_PARAM<p38:Display>#

Click on apply and your query will respond to any date selections followed by dashboard refresh.

ShareThis

Read More