Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • Date Slicer

    Date Slicers can be based on an Excel range, or direct from SQL. The example below demonstrates how to use a date slicer from an Excel range, but a SQL statement which returns the same result set format can also be used.

    Creating a Date Slicer

    To create a date slicer, you will first need two Excel cells to hold the date range (start and end date).

    Dateslicer.PNG

    Create an Excel slicer from the Slicer ribbon item:

    Dateslicer1.PNG

    Set the Slicer Range to point to the start date and end date (cells B1:B2 in this example) and select the Date Slicer type.

    Dateslicer3.PNG

    Under the Behaviour tab, select the cell where you wish the slicer selection to be stored. The date selected is displayed as the serial number equivalent so that the user can then choose to format the date as they need.

    Dateslicer4.PNG

    The date slicer will then be inserted, with the drop down opening a calendar control which can be navigated between the specified start and end dates.

    Dateslicer5.PNG

    Using a Date Slicer to Drive a Report

    Click a date to select it and it will be written as the Excel serial number in the cell location defined in Update Range With Selection.

    You can choose to format this by using the Excel TEXT function eg =TEXT(C4, "dd-mm-yyyy").

    A grid can then be driven from this cell as described here.

    Customising the Calendar

    Day, Month or Year View

    You can include a third cell in your Slicer Range as the Selection Type i.e. Day, Month or Year. Valid selections are D, M or Y.

    Day is taken to be the default selection type and is the default display mode if no selection type if specified.

    Dateslicer10.PNG

    The calendar now displays the month view.

    Dateslicer11.PNG

    Initial Selection

    As with any slicer, you can select which value will initially be displayed ( see Dimension Slicers).

    Selecting the Initial Value as the Default Member for a date slicer gives today's date. This means when the report is first opened, it displays today's data, from which you can navigate using the date slicer.

    Dateslicer12.PNG


    See Also