cancel
Showing results for 
Search instead for 
Did you mean: 

How to use a Combo Box to filter a UNX Query Prompt instead of Query Prompt Selector ?

Former Member
0 Kudos

Hello everyone,

I am trying to filter my query prompts (from a .unx universe) in Dashboard 4.1 SP6 using a combo box but it doesn't work at all.

I created a query which displays the amount by product. The query have 2 prompts : on the year and on the name of the month.

I want to use a combo box to filter the year and an other one for the month instead of using the Query Prompt Selector : the combo box property enables to send a selected value to a query prompt (Destination). But in fact it doesn't.

In the Preview, selecting a value in the combo box have no effect on my query, even when I refresh it (with the Refresh button).

I need to let the Insertion Type on "Filtered Lines" to avoid redundancy but even with the other types, it doesn't work. I let the "Source Data" empty.

I also try to put the selected value in a cell and bind this cell to the query prompt and it works but I get an error in the beginning cause the cell contains "none" (before I select a value) so the query is refreshed with "where year = none and month = none".

I tried to replace this "none" with the excel function NA() but it is not supported.

I need to display all the data first and then let the user select a value for the year and/or the month so I cannot set a default value for the prompt or in the cell binded.

Any idea ?

Thx for reading!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

I have been to same case where on first time load i had to display all data by default, so what i did was i took "ALL" as first value in all of my combo box's and made it as by default selection label 1, then on excel i applied formula if value in that cell become's all then it should go blank.

for example:- suppose my destination cell of combo box is A1, then i applied formula on A1 cell "=if(A1="ALL","",A1)".

i Hope this will help you.

Regards

Rahul Sharma

Former Member
0 Kudos

Waaaouw your solution Rahul and Suman seems to work !! I will try it tomorrow to be sure (I can't now) and validate your response but I think it's OK

Thank you very much!!

EDIT : It works very well ! Thank you all for your help!

Answers (1)

Answers (1)

former_member182541
Active Contributor
0 Kudos

Take the year & month values in the combo box and give the destination to the particular cell, ex: A1 & B1. Mark the A1 to year prompt & B1 to Month Prompt. Now concatenate both the cell in C1 and use this cell as a trigger cell.

Make sure you pass the default value to pick the current month & year.

Hope this helps!!!

Former Member
0 Kudos

Thank you for your reply

I already try this but I need to display all the months of all years first : the user have to see all the data and decide if he wants to filter them or not.

I wish I can use cells, but I need to tell Dashboard not to read them when they're empty (so the query have no "where") or set the cells with all my values : I try to put "2012,2013" or "2012;2013" in a cell to take all the years but that doesn't work. I also try to mark two cells (A1=2012 and A2=2013) to a prompt but once again, that doesn't work for me.

I think I just don't know how to adjust the properties of a combo box to make it work with Query Data.

Former Member
0 Kudos

Hi,

So the first time when you preview the dashboard keep the excel cells empty. And preview.
You should be able to see the entire data?

Is this not the case?

And also in the query browser queries I have seen that the prompt value provided to the query while the query is last edited is retained. May be you can edit and provide value so as to retrieve all data and preview the dashboard.

And is it ok to keep the prompt optional?? You can give this a try.

former_member182541
Active Contributor
0 Kudos

Keep the prompts as blank in the background, in the front try to pick "ALL" character from the list of values. This will definitely works, keep the prompts as optional.

Former Member
0 Kudos

Hi Abhilasha,

Thx and sorry for the late

The cells binded to the prompts are empty, they are just marked as destination of the selection's user. But I still not see the entire data.

I try all the possibilities with the prompts parameters in the query browser : the prompts are optionnal and have all the values by default and the last edited value is retained. But even with that, when I preview, there's no data displayed


Former Member
0 Kudos

Hello,

What do you mean by "pick "ALL" character from the list of values" ? I don't have "ALL" as a possibility unfortunately.

former_member182541
Active Contributor
0 Kudos

You need to hard-code "ALL" text at the top of the list of values.

Example: if its like

2000

2001

2002

then hard-code as

All    

2000     2000

2001     2001

2002     2002

take the left as a list of values and pass the right one to the destination, here when you select all it will pass a blank values in the prompt. Keep the prompt as optional.