Report Parameters to insert a list into a MongoDB query

<p>I have a mongoDB aggregation query where the $match section contains a $in construct.</p>
<p> </p>
<div><strong> $match: {</strong></div>
<div><strong> "strategyName": "us market",</strong></div>
<div><strong> "basket.assetId": {"$in" : [1071,1080]}</strong></div>
<div><strong> }</strong></div>
<div> </div>
<div>The collection (performance) that this query is run on contains performance history for a large collection of stocks (assets). Each of these assets belongs to a sector (e.g. INDUSTRIALS) and each sector contains many assets. The performance collection only contains the assetId (a numeric primary key). It does not contain any information regarding sectors.</div>
<div> </div>
<div>I have two other collections of relevance:</div>
<ul><li><strong>sector</strong> - this contains the mapping between the numeric sectorId PK and sectorName, a string containing a text description.</li>
<li><strong>asset - </strong>this contains, among other things, a mapping of assetId to sectorId (i.e. it tells us which assets reside in which sector). A single sector will contain many assets</li>
</ul><div> </div>
<div>I want to be able to study sector performance and to do so I need to be able to use the <strong>$in</strong> construct shown above.</div>
<div> </div>
<div>To do what I want I have to perform 3 steps:</div>
<ol><li>Display sectorName and return sectorId (using a Report Parameter)</li>
<li>Using sectorId from (1) build a distinct list of assets (using a Data Set that actions a distinct query)</li>
<li>Insert the list of assets into the aggregation query and run the report. (???</li>
</ol><p> </p>
<p>The data set query is as follows:</p>
<p> </p>
<p><strong>{distinct: "asset", key: "_id", query: { "sector.sectorId" : 3 }}</strong></p>
<p> </p>
<p>The Value '3' in this query is replaced by the user selected Report Parameter (i.e. the sector they want to examine). The query returns a list of assets in a field 'values' (this is a list of integers but could easily be treated at a string)</p>
<p> </p>
<p> </p>
<p><strong>Question</strong></p>
<p> </p>
<p>How do I get the list of assets from the above dataset into a variable and then have that variable inserted into the $match statement of my main Data Set (shown at the very top).</p>

Find more posts tagged with

Comments

Clement Wong

<p>You'll need to run the query for Step 2 using either Design Engine API, or build a hidden table that runs the query and builds the string saving the output into a variable. Then feed the output of either method to the Data Set of Step 3 using the <em>beforeOpen </em>this.queryText replace method.</p>
<p> </p>
<p>It's easier to do the later method if you are starting out with BIRT.</p>
<p> </p>
<p>1. In the report's <em>beforeFactory</em> event, define a (string) variable to be used later in step 6 and 7. For example:</p>
<pre class="_prettyXprint">
assetList = "";
</pre>
<p>2. Create a Data Set that will execute the list of assets. </p>
<p>3. Drag that Data Set to the Layout pane. Make it the top most item.</p>
<p>4. You can delete the report items from the table, but not the Detail Row itself. That is, the Detail Row can be blank.</p>
<p>5. Select the Detail Row.</p>
<p>6. Go to the Detail Row's Script > <em>onCreate </em>event.</p>
<p>7. Build your string list of assets in the <em>onCreate</em> event. For example:</p>
<pre class="_prettyXprint">
if (assetList == "")
assetList = this.getRowData().getColumnValue("ASSET_ID");
else
assetList = assetList + ", " + this.getRowData().getColumnValue("ASSET_ID");</pre>
<p>8. Overwrite the <em>beforeOpen </em>event in the Data Set for Step 3:</p>
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("1071,1080", orderList );</pre>
<p>Attached is a sample based on the Classic Models sample database. It will take a customer's number and return a list of order numbers (equivalent to your asset IDs). Then the list of order numbers will be used in a SQL IN clause to display the order details for those orders.</p>

richard.bibb

<p>I couldn't hope for a more detailed answer Clement. Thank you very much. I'll give it a go and report back</p>