Making Data Item's Persist Through Joins

[willg]

Hi all,

I currently have an issue where I need to apply different detail filters on queries to get a count. And then I need to subtract one count from the other, the issue I'm having is that because the detail filters are applied on each query separately before the join, after the join the detail filters are lost;



Above is a screenshot of the Data Item's I'm talking about, is there a way to be able to subtract one count from the other and then output the subtracted count? Or is there another/simpler way of achieving this (subtracting one count from another using the same data item, but diffferent detail filters)?

[Lynn]

Try using a union instead of a join.

--> First query should return 0 for count of closed and 701 for count of all distinct.
--> Second query should return 631 for count of closed and 0 for count of all distinct.

Union these two queries together and set the aggregate property for each item to total.

[willg]

Thanks for the reply Lynn, when I used a Union it either merged the two data item's into one, or one of them wasn't available to use in the new Query.

After running the report with the union I also got an error;


So I cannot tell whether that method would of worked or not. Is there no way just to store the calculated count as an actual value through the joins, rather than an expression linking back to the previous queries?

[Lynn]

In order to do a union both queries must have the same number of query items and each must be of the same data type. The error message indicates that one of those rules was violated.

Both queries should have two data items, such as: [CountClosed] and [CountAllDistinct]

These should both return a number (not a string) data type. For the first query, return zero for the CountClosed and the actual value for CountAllDistinct. In the other query do the opposite.

The union result should be just one row with the two items populated so you can then display and compute further.

The other way to go about it would be to use a single query that brings back all the data needed and has two data items with case expressions to bucket things accordingly.

The expression for [CountClosed] might be

Code Select Expand

case [status] when 'Closed' then 1 else 0 end

Some ideas for you to play around with.

[willg]

Ok I think I've decided to go with the single query rather than using a Union.

Currently I have the code;

Code Select Expand

total (case [status] when 'CLOSED' then 1 else 0 end)

Which works perfectly except I need to only count when the primary key (id) is distinct.

Something along the lines of

Code Select Expand

count(distinct [ticketid]) where [status] IN 'CLOSED'

But this returns a parsing error, does anyone know how to write an expression similar to the one above without errors?

[willg]

Update: I think I've solved it, for anyone who may be interested, the code I got to work is as follows;

Code Select Expand

case when [status] IN ('CLOSED') then [ticketid] else null end

And then I just used a count distinct aggregate function on the data item.