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

Image

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

Image

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

Image

 

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

Image

Click on "Full Apply"

and you should get the below new Date Format

Image

Similarly create anothe date Field and call it To Date

Image

 

Now we can use this date for any Excel Query

Below is our sample SQL with hard coded date values

Image

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

Image

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.

Join InfoCaptor and Get Free Dashboard Software

Posted in Excel, How-to | Tagged , , , , , , , , , , , , , , , , , , ,


Would you like to build your dashboard now?

If so, click to buy now, including 60 minutes of dashboard development for free!