Carrying Randomly Selected Records into the next query

[gordread]

Hey guys, I've got a client who is doing random audits.  I work for an insurance company needs me to build a report that selects claim records at random and then builds a report based off of cost information associated with these randomly selected claims.  I will need to use multiple queries to build the report.  I randomly select the claim records in the first query by assigning random numbers to each row and then sorting by these random numbers and selecting the top 25 (or however many I need).  Each time the report is run, it assigns different random number and I get a different list of claims.  This is exactly what I want. Except....

When I use the first query to feed a subsequent query, it seems to regenerate the random numbers, for each subsequent query, so the results for each subsequent query are different than each other.  Not really great when dealing with auditors.  sigh.

I have tried to assign a new 'selected' column and use that to identify the select subequent information, but that doesn't seem to have helped, I'm still getting different selected rows is subsequent queries.

Does anyone know of a way to make this work?  Or maybe a different approach?

Gordon

[AnalyticsWithJay]

Have you tried setting "Use Local Cache" on the parent query? And is that parent query being used for more than one data container (list, chart, etc) on the report (directly -- not by feeding queries)? The reason I ask is to determine whether or not we can leverage caching on that query.

Also, what's the purpose of the parent query? Why do you need it to feed multiple queries? Depending on its usage, I'm wondering if we can hide pre-selected prompt values and pass them as parameters. This would only work if you have an existing prompt page of course.

[gordread]

CognoidJay,
Thanks for your reply.  I spent some time noodling around with your suggestions.  I tried to set the 'Use Local Cache' and ended up with the same result.

The parent query is not being used in any data containers.  It is only used as a feeder to the next several (four) queries.

The purpose of the parent query is to collect all of the transaction information that I need and to randomize it for selection by the next set of queries.  Each of the next four queries selects several records from the parent query.

This report does not use prompt pages.

[AnalyticsWithJay]

Gordon,

I was able to get this working. I created an example with 3 queries:

Query 1: Contains Date, Random Number, and a Running Count that I use to get less than 10 records
Query 2: Contains Random Number
Query 3: Contains Date and Random Number

I created three lists, and saw the same random numbers in each list. I also saw the same Dates in Query 3.

Query 1 feeds Query 2 and Query 3.

I set Query 1 to "Use Local Cache". I also selected the list that uses Query 1, and I set "Share Result Set" to Yes. This allowed me to use the results in other queries.

I'm assuming you have no use for the feeder query on your report. Place it on the report, and set the Box Type to 'None' so it does not appear when you run the report.

Let me know how it goes.

Jay

[gordread]

Thanks Jay.

I created a new report, one with less data so that I could do tests faster.  I followed your instructions, but I'm still getting differing results.  Even when I use the SAME query for two different lists.

I created Query 1, which has a list of 10 payment transactions in it.  I set the Use Local Cache on the query.  I created a list on the report page, and set it to 'Share Result Set'.  Then I added another list, using Query 1, and ran it.  I got different random numbers.

Random    Payment Transaction Reference Number
8               62942754
3               64785949
9               65945015

Random    Payment Transaction Reference Number
4               62942754
3               64785949
6               65945015

Just in case, I also tried this using Query 2 <- Query 1 and also got different random numbers.

For some additional information the database that I am using is DB2 and the random function is round( [Payments Count]*rand(),0).  [Payments Count] is the maximum of row count for the report.  I tried using a constant number instead (10) and got an error message '[IBM][CLI Driver][DB2/LINUXX8664] SQL0583N.  The use of routine or expression "RAND" is invalid because it is not determinstic or has an external action.  SQLSTATE=42845.  But if I use the [Payments Count] it works fine.

Frustratedly (though appreciatively) yours.

Gordon

[AnalyticsWithJay]

Could you attach the report specs of the simplified report? what version of Cognos are you on?

I'll take a look to see if there are any differences in our approach.

[gordread]

We have Report Studio 10.2.1.

What do you mean by 'Report Specs'.  I have attached the XML output from 'Copy Report to Clipboard'.  I hope that is what you meant.

Thanks,

Gordon

[AnalyticsWithJay]

We have Report Studio 10.2.1.

What do you mean by 'Report Specs'.  I have attached the XML output from 'Copy Report to Clipboard'.  I hope that is what you meant.

Thanks,

Gordon

Yes, that's what I mean. Thanks.

I believe I know where the issue is in the example you provided:

Share Result Set
Specifies whether to share an identical query between data containers that use it. To share a query, data containers must be lists, repeaters, or repeater tables and must use the same grouping structure and list of properties. The data containers cannot be part of a master detail relationship. When set to Yes, the query sends only one request to the database and shares the result. When set to No, the query is not shared.

1. Make sure all the lists that you want to share data with have "Share Result Set" set to yes. In the example provided only the parent list has it set to yes.

2. Notice how it says you must have the same grouping structure and list of properties? Select each list individually, and edit the "Properties" property, which is right about Share Result Set. Select all columns and click OK.

Now we've made sure your lists are grouped the same and have the same properties. You should be able to see the same data in all.

By the way, I tested this with "Use Local Cache" set to Yes as well as set to No. In both instances, they share the result set. Therefore, you do NOT have to have it set to yes in order for this solution to work, although it will improve performance on subsequent runs.

[gordread]

That did it.   Woo Hoo.... Select all in the 'Properties' and 'Share Result Set' to Yes.

Fantastic.  Thank you, Thank you, Thank you.

Gordon

[AnalyticsWithJay]

That did it.   Woo Hoo.... Select all in the 'Properties' and 'Share Result Set' to Yes.

Fantastic.  Thank you, Thank you, Thank you.

Gordon

You're welcome Gordon :).

Cheers,
Jay