Drill Through on Aggregation

[dpetersen]

I'm about ready to lose my mind trying to figure out whether this is possible...hoping someone can help me with a solution or tell me it's not possible so I can stop wasting my time!

I have a list in my report that simply shows a count of distinct transaction IDs by product line. I want to create a drill through link on the count of distinct transactions to generate a target report that is filtered by the Transaction IDs that 'make up' that distinct count in the source report. So, I click on a value of '3' for example, and it delivers the 3 underlying transaction IDs for that product line to the target report's ?TranID? parameter (the target report has a filter expression of "Transaction ID IN (?TranID?)"). So far I've only been able to deliver the count value itself or a single transaction ID.

Is this possible or am I wasting my time?

[cognostechie]

"Transaction ID IN (?TranID?)").

That filter in detail report is wrong. Your summary report which shows the distinct count of Transaction IDs will not have Transaction ID within it's granularity. Using this as an example I will try to explain:

Data:

Product Line - Laptop Computers, Desktop Computers

Let's say there are 13 Transactions
Transaction IDs for Laptop Computers - 1001, 1002, 1003
Transaction IDs for Desktop Computers - 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013

The summary report will show :

                  Transaction Count
                  -----------------
Laptop Computers          3
Desktop Computers        10

There is no Transaction ID within it's scope because it is counting distinct.

The detail report will show:

Product Line              Transaction ID
----------------------------------------

Laptop Computers           1001
Laptop Computers           1002
Laptop Computers           1003

                 OR

Product Line              Transaction ID
----------------------------------------

Desktop Computers          1004
Desktop Computers          1005
Desktop Computers          1006
Desktop Computers          1007
Desktop Computers          1008
Desktop Computers          1009
Desktop Computers          1010
Desktop Computers          1011
Desktop Computers          1012
Desktop Computers          1013

In the Summary report, you will click on 3 or 10 and at that point, you are selecting the data for only one Product Line so the value you have to pass to the detail report is the Product Line. The filter in the detail report should be [Product Line] = ?pProductLine?

[dpetersen]

Thanks for the reply.  I've built a drill-down on the product line already, so I think I'm already achieving your suggestion.  I should have been more clear on the transaction ID piece of this.  A better example might have been a count of distinct customers.  I want to click on the count of distinct customers to see all of the transactions from those customers, regardless of product line.  I think, based on your reply, that I'm correct in thinking it's not possible to drill down on a count to have the underlying customer IDs (Tran IDs in my example) be delivered to the target report.

[cognostechie]

You have changed the scenario completely. That being said, everything is possible but if you have already decided that you want to give up then I am not going to make any efforts to give you the solution. 

[dougp]

I want to click on the count of distinct customers to see all of the transactions from those customers, regardless of product line. 

Easy enough.

Your target report will have a query that gets all of the customers for the various filters, including product line because it's related to what you clicked.  Then you'll join that (on customer) query to another query that does NOT filter by product line in order to get all of the transactions for those customers.

[dpetersen]

You have changed the scenario completely. That being said, everything is possible but if you have already decided that you want to give up then I am not going to make any efforts to give you the solution. 

I tried to simplify my example as best I can, and in doing so I used a bad example of transaction ID, though that really doesn't change the context of my question.  I intuited, apparently incorrectly, that you were saying it couldn't be done (your first paragraph says that the summary report doesn't have transaction ID within its granularity).  Apologies for the apparent offense I have caused you...

[dpetersen]

Easy enough.

Your target report will have a query that gets all of the customers for the various filters, including product line because it's related to what you clicked.  Then you'll join that (on customer) query to another query that does NOT filter by product line in order to get all of the transactions for those customers.

Thank you!  I should have thought of this solution...much appreciated!