Altek Solutions Business Intelligence Blog

Welcome to episode 2 of our Tips and Tricks for Crystal Dashboard Design (Xcelsius 2008). This post is going to be a short one but should help in making your dashboard stand out among the rest. Ever since I started working with Xcelsius 4.5, I had clients wanting functionality built into their dashboards that just wasn’t feasible with the standard out of the box components.

One of the biggest request was to take the calendar control and to make it so that they didn’t have to click the toggle button to open the calendar, click the date and then click the toggle button to close the calendar. Too many clicks in the end users eyes. So with every new fix pack / service pack for Xcelsius, I have tried to build the functionality that will allow the end user to click a date and have the calendar disappear. With the addition of the push button, we are now able to do this.

Before I get started with the implementation of this in a dashboard, I just wanted to mention that the real idea behind this episode was a BOB forum topic that I replied to and got some positive feedback that it did actually work. As we continue on down the road of our tips and tricks series, I will be continuing to look for new content whether it be on BOB, SAP SDN or just internal ideas. If you have any ideas of a tip and trick for Xcelsius put a comment on this post and we will look into adding it as a post in the future. Thanks for your help in advance!

Logic

The first thing we need to set up is the Excel logic. This will consist of a couple things that will allow us to get this set up and work as expected.

Excel Setup (Calendar Values)

1. Calendar Value
   • This will be the date that is selected by the end user and is populated by the Xcelsius Calendar Component.
   • The Day, Month and Year are just Excel functions that check to see if the Calendar Value date is blank. I it is, make the cell blank else fill it in the the appropriate value.
     • =IF(ISBLANK(B2),”",DAY(B2))
2. Default Value
   • This is exactly what you would expect it to be. My example shows the default date as =today() but yours can be anything you want it to be.
   • The Day, Month, Year just pulls the value using excel functions.
   • This does not need the if is blank logic because the default date is always populated.
3. Query Value
   • This is the value that will be sent through to QaaWS or any other data source.
   • The Day, Month, Year uses logic that says if the user has selected a value, then use that one else use the default.
     • =IF(ISBLANK(B2),C3,C2).
       
   • The date cell is just a excel formula that takes the Day, Month and Year and makes it into a date.
     • =DATE(E4,D4,C4)

This is a nice little template that I use on all dates that needed to drive my QaaWS and it should be pretty straight forward on how to set up. Lets move on to the last little bit of the excel set up.

Excel Setup (Visibility Controls)

1. Push Button Value
   • This is the second push button we are going to use and it will be the one that allows the user to select Today or Default as a date.
   • The actual value is null. (Confused yet? Me too!)
2. Calendar Visibility
   • This tells the calendar to either show (On) or to hide (Off).
   • =IF(B3=B2,”On”,”Off”)
     

Alright lets look in detail what the above means. How we have it set up is that the calendar will only show when the value that is in the Calendar Value matches the Default Value. Seeing that the Calendar Value is null by default the calendar will never show on opening the dashboard. This will require us to have another push button so that the default date gets pushed out to the correct cell to display the calendar. At that point, any value selected other than today’s date will close the calendar. Lets move on to the components so that what we are trying to do is a little more clear.

Components and Mapping

We will need 2 push buttons, a calendar control and a value component. Once we get those on the canvas we can map them appropriately and give a quick demo on how it works.

Components

1. Value Component
   • Displays the Current Selected Date or Default Date by mapping to the Query Value Date from above.
   • Always Viewable
2. Push Button 1 (Push)
   • Opens the Calendar Component by sending the Default Date (Source Mapping) to the Calendar Date (Destination Mapping).
   • Always Viewable
3. Calendar Component
   • Automatically shows/hides based on if the Default Date matches the Calendar Date (Dynamic Visibility: Calendar Visibility = On)
   • Selection of another date triggers the Calendar Visibility to change to Off and hides the calendar.
   • Default Custom Date mapped to the Default Date in the excel
   • Insert Date goes to the Calendar Value in the excel
4. Push Button 2 (Today or Default)
   • Takes a null value and pushes it into the Calendar Value cell in excel.
   • Visible only when Calendar Visibility = On

Beware!

Don’t be afraid of this caption. I just want to make everyone aware of a couple items that you should expect to see. The should not be show stoppers but then again every client is different.

1. If the end user selects the same date or the default date, the calendar will not disappear. This is why we implement the second push button (Today)
2. When the user selects the Today push button, the selected date in the calendar will be the same as it was prior to clicking on the push button

Finished Product

Hopefully this is straight forward enough that you can implement this in your own dashboard. At first it does seem a little confusing but in the end I believe that it is a solid solution to the issue that it seems a lot of people are having. Check out the working example below!

Be sure to subscribe to our RSS feed to get access to new tutorials as soon as they are published!

Related Posts:

1. Crystal Dashboard Design (Xcelsius 2008): Tips and Tricks ep3: Automatically Moving your Tab Set
2. Crystal Dashboard Design (Xcelsius 2008): Tips and Tricks ep4: Navigation using Label Based Menus and Push Buttons
3. Crystal Dashboard Design (Xcelsius 2008) Tips and Tricks ep1: Adding a Select All to your Selector
4. Crystal Dashboard Design (Xcelsius 2008): Tips and Tricks ep5: Parent and Child Dashboards from InfoView using Doc Download!
5. Xcelsius … Crystal Dashboard Design … Xcelsius 2008 Fix Pack 3.2 Now Available!