I am creating a report with a list in it. It consist of a query containing year, county, gender, age group and population count and a second query with year, county, gender, age group, case count and category. I have joined the tow queries based on year, county, gender and age group. The list will show a case count and population count showing the county, gender and age group the count belongs to. There will also be some additional data items calculated from the case count and population count. Population count is a number already in the database for each combination of County, Gender and Age Group. Case Count is a data item, created in the framework model, which assigns the number 1 to each row, as a fact, so we can total the case counts data item to show the # of cases for the selected category based on Gender, County and Age Group.
The report starts off with a prompt page with value prompts to allow them to select the category, year, counties, gender and age groups they wish to view. Up to this point the report works correctly. If a person selects all for all three filters they see the data for 14 counties, 2 genders and 10 age groups for a total of 280 rows of data. Now I want to add the ability to hide a column like age group if they do not pick any age groups to view. The list would then hide the age group column and show the aggregated data for each county and both genders for a total of 28 rows of data.
Using a variable I am able to use conditional rendering to hide the column when the age group parameter shows null because no selections have been made. The issue is I am still getting all 280 rows of data like the age groups had been selected and the column was still showing. I have Auto Group & Summarize set to yes for the query and I have the case count and population count set to total for aggregation. The only way I can get the results to show the correct totals and rows is to delete the age group from the query completely.
I have considered separate queries using conditional blocks to display a unique list for each scenario but I would like do this with one query. Any ideas on what I am doing wrong or suggestions on how I can accomplish this would be greatly appreciated.