Xcelsius All selection in Combo Box

I build and design dashboards pretty much on a daily basis whether it be for a client, POC, or as demo material. One of the biggest things that I dread to hear, is my boss saying either one of these phrases: “How hard would it be….” or “You know what would be cool?” This usually means that I will have to put on my thinking cap and figure out how to pull something off in Crystal Dashboard Design (Xcelsius).

Anyway, as I was saying, I have to pull off some pretty interesting things relatively often so thought why not share these Tips and Tricks with everyone who follows our blog. This will be the first entry in a line of many that will pick out a little pieces of functionality that I have built using standard components inside Crystal Dashboard Design and using QaaWS or Live Office. Topic number one is allowing your dashboard to select All of the entries in a selector.

Summary

What we are trying to accomplish with this functionality is giving the end user the ability to select one or all of the items in a list, menu or a radio button. We had the requirement come across from one of our clients when they wanted the ability to select one or all customers, products and salesperson on their salesperson dashboard. We were able to implement this feature by utilizing QaaWS and optional prompts.

In the example I am going to show you, we are using the eFashion universe that allows the user to select from year(s) and state(s) to get a monthly total of sales revenue and quantity.

eFashion Monthly Comparison Dashboard

Development

Really there are only two things that you need to worry about when adding the functionality. The first is your QaaWS and the second is making sure you add an All selection to your list.

When developing your QaaWS you will need to make sure you create a dynamic list for each selection that you want in your dashboard as well as your main data QaaWS. My dashboard has 3 QaaWS in total: Year, States and data. The year and states are pretty self explanatory with them being just a full list of values.

The data is a little more involved and contains Month, Month Name, Sales Revenue, Quantity and is limited by a prompt for both Year and State. The catch with this is that the Year and State prompts MUST BE OPTIONAL! Without them being an optional prompt, bringing back All Years or All States will not work.

Optional Prompts

The second phase is setting up your list selector. The main thing you need to remember is to make sure you have a place holder for your “All” selector and not to overwrite it with you dynamic list coming back from your QaaWS. In the picture below, you can see I have Years and States all set up and ready to go for my selector components.

List Set up

Here are the steps to complete the set up of you list components.

1. Bind your Year and State QaaWS to your excel model making sure you don’t overwrite your All placeholder
2. Bind your list selectors to your dynamic lists
3. Set up your list selectors to an insertion type of row and have them move to an area designated for them specifically

1. QaaWS Binding

2. Selector Binding

3. Insertion Type and Binding

After your lists components are all ready to go, you need to set up a field that will answer the prompts for your master data QaaWS. When we created this, we made it an optional prompt so that if a null value was sent through it would bring back all records for that prompt. In my example, I need to set this up for two prompts, year and state.

The easiest way to do this is to create a formula in excel that checks to see if the value that has been selected is “All”. If it is great, make the cell blank, if not then make it whatever value that the user has selected. Your formula should look like this: =IF(I2=”All”,”",I2). After you have the formula, map your QaaWS prompts to the appropriate cells and you should be good to go.

Hopefully this little trick can help you out in one of your future dashboards! Be sure to subscribe to our RSS feed so you don’t miss out on the rest of our Tips and Tricks for Crystal Dashboard Design.