on 08-27-2015 5:33 PM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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
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.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.