| Author |
Topic |
|
gds514
Starting Member
7 Posts |
 Posted - 2010-01-04 : 15:06:30
|
| I have 4 filtersOrganizationCommunityCategoryAgentI would like either or all of Agent, Community of Category to change the list available in OrganizationSo in other words, if I put a commmunity filter as Community A - when I look at the drop down filter of Organization, only those organizations in Community A would appear. If I then added the Category filter of Political, then only those in Community A that are Political would appear in the Organization drop down.Thankx in advance for your assistance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gds514
Starting Member
7 Posts |
Posted - 2010-01-04 : 15:12:53
|
| T-SQL 2005 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gds514
Starting Member
7 Posts |
Posted - 2010-01-04 : 16:02:25
|
Its for the reporting that I need help:This is a sample of the datasetAdjoint Agents pkNoCommunaute Class Category Name CityKelly André 7, 8, 9, 10, 11 4 Political Organization Cree Regional Authority MontréalKelly André 7, 8, 9, 10, 11 4 Political Organization Cree Regional Authority NemaskaDanièle Gérald 26, 30, 32, 33 9 First Nation Organization Regional Admin of the Police Port CartierDanièle François 45, 46, 47, 49 4 Political Organization Kativik Regional Government Kuujjuaq All 8 Political Organization Assembly of First Nations (AFN) Ottawa Danièle Gilles All 4 Political Organization Ass. of FN of Québec and Labrador WendakeKelly Serge 26, 30, 35, 36 4 Political Organization Mamu Pakatatau Mamit Assembly Sept-Îles 7, 8, 9, 39, 48 7 Economic Development Cree Outfitting and Tourism Ass. Oujé-Bougoumou All 7 Economic Development First Nation Business Association Wendake 6, 13, 15, 24 7 Economic Development Algonquin Development Association Notre-Dame-du-Nord My pkNoCommunaute ties into a list with actual namesI have drop down filters on Agent, Community, Category and Organization (Name)If I select an agent a do generate report, I get all the Organizations atributed to that agent but I have to go page by page to see them all. I would like the agent filter to change the list of organizations in the organization filter drop down. So once an agent is selected, the list refreshes and I only see the organization with that agent. The same is each filter so that my list gets smaller (i.e drill down) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-05 : 03:28:49
|
| how are you currently populating agent, organisation,etc dropdowns? also are you using sql reporting service? |
|
|
|
gds514
Starting Member
7 Posts |
Posted - 2010-01-05 : 12:09:46
|
| The dropdowns on the filters are populated straight from the database columns like the example above just like a filter in Excel.I am using Visual Studio 2005 SQL Server Analysis Reporting |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 03:09:18
|
quote:
Originally posted by gds514
The dropdowns on the filters are populated straight from the database columns like the example above just like a filter in Excel.I am using Visual Studio 2005 SQL Server Analysis Reporting
ok great. then what you need to do is pass the higher level dropdown selected value as a parameter for next dropdown population query. ex: pass value of selected agent as a parameter for query to retrive organisations and then use it to filter and get only organisations belonging to that agent |
|
|
|
gds514
Starting Member
7 Posts |
Posted - 2010-01-06 : 10:14:28
|
| Right - I just don't see any place to pass that parameter |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 10:22:12
|
quote:
Originally posted by gds514
Right - I just don't see any place to pass that parameter
Have you used option from query for populating parameter values in parameter properties tab? |
|
|
|
gds514
Starting Member
7 Posts |
Posted - 2010-01-11 : 15:32:27
|
| I can parameterize solely if I create a hierachy order. For example if I made an order of 1.Agent 2. Community 3.Category 4.Organization Name - and thus 2 is dependant on what was selected in 1 and so on and so forth.what I wanted to know was if there is a way to do it so that each filter is interdependant on the other filters regardless of order. In other words, if a user decided to filter on Category first and then see all the corresponding communities. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-12 : 00:36:23
|
quote:
Originally posted by gds514
I can parameterize solely if I create a hierachy order. For example if I made an order of 1.Agent 2. Community 3.Category 4.Organization Name - and thus 2 is dependant on what was selected in 1 and so on and so forth.what I wanted to know was if there is a way to do it so that each filter is interdependant on the other filters regardless of order. In other words, if a user decided to filter on Category first and then see all the corresponding communities.
then you need to pass every other filters as a parameter for query which generates value for a filter. then based on if they have a value or not (optional) do filter in query to get related values. |
|
|
|
gds514
Starting Member
7 Posts |
Posted - 2010-01-13 : 12:03:15
|
| When I do that, I get parameter undefined because all haven't been established because no matter what I have to put some order to it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-13 : 12:07:57
|
quote:
Originally posted by gds514
When I do that, I get parameter undefined because all haven't been established because no matter what I have to put some order to it.
always follow order in which you create them. start from top level hierarchy to lower. for each sent all above it as paramsSQL Server MVP |
|
|
|
|
Drill Down on Filters