| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Date Slicer"
| Line 1: | Line 1: | ||
| − | Date Slicers can be based on an Excel range, or direct from SQL. | + | 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). | ||
| + | |||
| + | [[Image:Dateslicer.PNG|centre]] | ||
| − | + | Create an Excel slicer from the Slicer ribbon item: | |
| − | + | [[Image:Dateslicer1.PNG|centre]] | |
| − | [[Image: | + | 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. |
| + | |||
| + | [[Image:Dateslicer3.PNG|350px|centre]] | ||
| − | + | 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. | |
| − | + | [[Image:Dateslicer4.PNG|350px|centre]] | |
| − | + | 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. | |
| − | + | [[Image:Dateslicer5.PNG|250px|centre]] | |
| − | |||
| − | + | ==Using a Date Slicer to Drive a Report== | |
| − | Click | + | Click a date to select it and it will be written as the Excel serial numberin 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 [[Driving Grids from an Excel Range|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. | |
| − | + | [[Image:Dateslicer10.PNG|500px|centre]] | |
| − | + | The calendar now displays the month view. | |
| − | [[Image: | + | [[Image:Dateslicer11.PNG|250px|centre]] |
| − | + | ===Initial Selection=== | |
| − | [[ | + | As with any slicer, you can select which value will initially be displayed ( see [[Dimension Slicers#Initial Value|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. | |
| − | [[Image: | + | [[Image:Dateslicer12.PNG|350px|center]] |
<br clear="all" /> | <br clear="all" /> | ||
Revision as of 12:08, 9 January 2018
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.
Contents
[hide]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).
Create an Excel slicer from the Slicer ribbon item:
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.
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.
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.
Using a Date Slicer to Drive a Report
Click a date to select it and it will be written as the Excel serial numberin 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.
The calendar now displays the month view.
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.
