I have a number of Queries which all are based on the same data but different filteration. Each query has a parameter setup for Year - so when a year is typed in, the calculated fields count using datepart for month and year to give the number of quality issues.
My problem as you probably understand is that the parameter is asking to be completed for every query, so at the moment it would require to be input 3 times for the user (I have more queries to add too).
I hope a publish subscribe scenario could work, but I’ve been reading posts which state they don’t work with parameter use. Can anyone help me achieve this ?
One thought, since your columns are all the same in your queries, is to create a union query out of the 3 and add an additional column called ‘record type’ (or something similar) to filter by. Then create one query in your dashboard with 3 tabs filtering on record type.
A second option, is to have the date hidden in all of the queries and have the first one respond to the parameter and have that one publish the year. The other two can subscribe to the year field in the first query.
A union query is two tables on top of each other with the same number of fields and the same data types in each field. That is pretty much it. If you have complex queries, you will need to make sure the subquery list is ordered correctly so it knows how to process the union. UNION ALL will return all records from each union level and UNION will group the stack.
To start, create a top level query and design how you want. Then, create a subquery and make sure the column count and data types are the same and select your subquery union type.
Take a look at the example and let me know if you have other questions.
Try this:
CASE WHEN cast(sum(ISNULL(SummAllJobsByMonth.Calculated_SumOctJobs,0)) As Float) <> 0 THEN
cast(sum(ISNULL(QC102.Calculated_October,0))As Float) / cast(sum(ISNULL(SummAllJobsByMonth.Calculated_SumOctJobs,0))As Float)*100
ELSE 0 END
The difference is that it checks for a zero before it divides and it converts null values to zero so the math keeps working.
Depending on your query, it might be better to some of the math elsewhere but this is a starting point.
This worked perfectly thankyou. However I am now back to my original issue - Ive rolled 3 queries into one, and ive rolled another 3 queries into one. so both 2 new queries ill add to the dashboard but want to apply Year parameter only once:)
So perhaps your second solution would work here: A second option, is to have the date hidden in all of the queries and have the first one respond to the parameter and have that one publish the year. The other two can subscribe to the year field in the first query
Right click on your first query, go to the publish tab and select your Year column. Inexact example below:
Click OK on your first query. Right-click on your second query, select the FILTER tab and map your second query year field to your published query field from the dropdown box. Another inexact example below:
Hmmm thankyou- first question if I may – I do not have a year column – I have a date column (using a datepart function based on a parameter I enter on load)
I was thinking create a Year calculated field on each query and put DATEPART(YEAR,< yourdatefield >) in it. This will give you the dynamic year. Group, filter as you will.
In the dashboard, it will have one parameter in the tracker view. It looks like you are using a BAQ parameter, which should still work as long as you have a year field in the first query to publish to the subscribing queries.
The 2nd/3rd queries can filter on this published year column from the same query.
The only way I’ve been able to accomplish your original idea was to deploy the dashboard and use the DynamicQueryAdapter to pull into a new DataView and display the other grids via customization.
