I am having trouble figuring out how to make my query dynamic. I will have to give some history of why I am doing things the way I am first.
I needed to be able to basically design my query to be a report because I am not able to use the reporting module due to formatting issues that occur on export that can only be solved by using a PDF maker that I do not have the option to use. I was able to get a summary query to look close to the way I wanted by using a cross-tab query. I needed it to have locations across the top and tasks performed across the top. The problem was that when I formatted it to do percentages, it wanted to run the percentages of the rows instead of the columns. To get around this I reversed the rows/columns and the percentages came back correct. I can then export the result to Excel and use Transpose to switch it back to the proper format.
Now the problem. The percentages are calculated fields (example: =[Task1]/[Total of Hours]) and the tasks will not always be present from month to month. I can build it so all possible tasks have a calculated field, but if the task doesn't exist in the data it will throw an error. How can I build it to check if the task exist and build the calculated field on the fly?
Sorry so long-winded, I didn't want to leave anything important out.
coachdan32
I needed to be able to basically design my query to be a report because I am not able to use the reporting module due to formatting issues that occur on export that can only be solved by using a PDF maker that I do not have the option to use. I was able to get a summary query to look close to the way I wanted by using a cross-tab query. I needed it to have locations across the top and tasks performed across the top. The problem was that when I formatted it to do percentages, it wanted to run the percentages of the rows instead of the columns. To get around this I reversed the rows/columns and the percentages came back correct. I can then export the result to Excel and use Transpose to switch it back to the proper format.
Now the problem. The percentages are calculated fields (example: =[Task1]/[Total of Hours]) and the tasks will not always be present from month to month. I can build it so all possible tasks have a calculated field, but if the task doesn't exist in the data it will throw an error. How can I build it to check if the task exist and build the calculated field on the fly?
Sorry so long-winded, I didn't want to leave anything important out.
coachdan32
