Display parameter entries not found in database

[RickJ]

Hello all!

I am working on a report where a user provided a list of ID numbers. This list can be for 500+ entries. One of the requests is to show any entries that were not matched to something in the database.

I have tried researching ways to do this but I haven't had much luck. I'm hopeful there is a simple solution.

This is for a report being created in Cognos 8.4. I am storing the list entries in a multi select text box prompt.

Thank you!
Rick

[blom0344]

Hello all!

I am working on a report where a user provided a list of ID numbers. This list can be for 500+ entries. One of the requests is to show any entries that were not matched to something in the database.

I have tried researching ways to do this but I haven't had much luck. I'm hopeful there is a simple solution.

This is for a report being created in Cognos 8.4. I am storing the list entries in a multi select text box prompt.

Thank you!
Rick

"any entries that were not matched"

Do you mean that by entry of a certain value an evaluation should occur with database values? A little example would in order to clarify this..

[RickJ]

Hi,

I simply mean any entry not found in the database.

Ex.

User specified ID 1, 3, 6, 7

DB uses that as the primary filter and displays results for only ID 1, 3, 7. Please note 6 is not displayed because it does not exist in the data.

I want to be able to have the report to display 6 and indicate no data was found.

Rick

[CognosPaul]

what type of db are you using?

[CognosPaul]

Following works in Oracle.

What I'm doing is building a dynamic union statement from the prompt many macro. Then locally I'm filtering where value not in (reportquery.code)

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
<modelPath>/content/package[@name='Audit']/model[@name='model']</modelPath>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents><textItem><dataSource><staticValue>Following don't exist </staticValue></dataSource></textItem><repeater refQuery="Dynamic Union">
<contents><textItem><dataSource><staticValue>, </staticValue></dataSource><conditionalRender refVariable="notFirstrow"><renderFor refVariableValue="1"/></conditionalRender></textItem><textItem><dataSource><dataItemValue refDataItem="Value1"/></dataSource></textItem></contents>
</repeater>
</contents>
</pageBody>
</page>
</reportPages>
<promptPages><page class="pp" name="Prompt Page1">
<pageBody class="py">
<contents><textBox parameter="stuff" numbersOnly="false" multiSelect="true" range="false" required="false"><defaultSelections><defaultSimpleSelection>1</defaultSimpleSelection><defaultSimpleSelection>2</defaultSimpleSelection><defaultSimpleSelection>3</defaultSimpleSelection><defaultSimpleSelection>4</defaultSimpleSelection><defaultSimpleSelection>5</defaultSimpleSelection><defaultSimpleSelection>6</defaultSimpleSelection><defaultSimpleSelection>7</defaultSimpleSelection></defaultSelections></textBox>
</contents>
</pageBody>
<pageFooter class="fp">
<contents>
<promptButton type="cancel" class="bp">
<contents/>
</promptButton>
<promptButton type="back" class="bp">
<contents/>
</promptButton>
<promptButton type="next" class="bp">
<contents/>
</promptButton>
<promptButton type="finish" class="bp">
<contents/>
</promptButton>
</contents>
</pageFooter>
</page>
</promptPages></layout>
</layouts>
<queries><query name="1">
<source>
<sqlQuery name="SQL3" dataSource="Audit">
<sqlText>select 1 as value from dual</sqlText>
<mdProjectedItems><mdProjectedItem name="VALUE"/></mdProjectedItems></sqlQuery>
</source>
<selection><dataItem name="VALUE"><expression>[SQL3].[VALUE]</expression></dataItem></selection>
</query>
<query name="2">
<source>
<sqlQuery name="SQL3" dataSource="Audit">
<sqlText>select 2 as value from dual</sqlText>
<mdProjectedItems><mdProjectedItem name="VALUE"/></mdProjectedItems></sqlQuery>
</source>
<selection><dataItem name="VALUE"><expression>[SQL3].[VALUE]</expression></dataItem></selection>
</query><query name="3">
<source>
<sqlQuery name="SQL3" dataSource="Audit">
<sqlText>select 3 as value from dual</sqlText>
<mdProjectedItems><mdProjectedItem name="VALUE"/></mdProjectedItems></sqlQuery>
</source>
<selection><dataItem name="VALUE"><expression>[SQL3].[VALUE]</expression></dataItem></selection>
</query><query name="4">
<source>
<sqlQuery name="SQL3" dataSource="Audit">
<sqlText>select 4 as value from dual</sqlText>
<mdProjectedItems><mdProjectedItem name="VALUE"/></mdProjectedItems></sqlQuery>
</source>
<selection><dataItem name="VALUE"><expression>[SQL3].[VALUE]</expression></dataItem></selection>
</query><query name="union">
<source>
<queryOperation name="Union1" setOperation="UNION" duplicates="preserve">
<queryRefs><queryRef refQuery="1"/><queryRef refQuery="2"/><queryRef refQuery="3"/><queryRef refQuery="4"/></queryRefs>
<projectionList autoGenerated="true"><queryItem name="VALUE"/></projectionList>
</queryOperation>
</source>
<selection autoSummary="false"><dataItem name="VALUE"><expression>[Union1].[VALUE]</expression></dataItem></selection>
</query>
<query name="Dynamic Union">
<source>
<sqlQuery name="SQL1" dataSource="Audit">
<sqlText>{select
#
join(' as "Value1" from dual UNION ALL SELECT ',
split(',',
  promptmany('stuff','string',sq('1'))
))#
as "Value1" from dual


}</sqlText>
<mdProjectedItems><mdProjectedItem name="Value1"/></mdProjectedItems></sqlQuery>
</source>
<selection autoSummary="false"><dataItem name="Value1"><expression>[SQL1].[Value1]</expression></dataItem></selection>
<detailFilters><detailFilter postAutoAggregation="true"><filterExpression>[Value1] not in ([union].[VALUE])</filterExpression></detailFilter></detailFilters></query>


</queries><reportVariables><reportVariable type="boolean" name="notFirstrow">
<reportExpression>RowNumber()&lt;&gt;1</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable>
</reportVariables></report>

[RickJ]

Hi Paul,

It is an MS SQL database. I should have clarified that (unfortunately) SQL commands are disabled for the staff who will run the report. I figured an SQL option would be the best way...

I honestly wouldn't be surprised if there is no way to accomplish this without the use of a generated SQL Query.

Thanks,
Rick

[CognosPaul]

A nice challenge! The hard part is simply converting the selected values to rows. Are you permitted to write an SP?

I don't have time to write it now, or a way of testing it, but you could write an SP that would split a string into multiple rows.

You could import that into your FM and call it with

Code Select Expand

#sq(promptmany('myparam','string'))#

[RickJ]

I could request a SP created but I can't do it myself unfortunately as the environment is managed by another team. This is also an option request by the client so I might just tell them it can't be done in our current environment.

[tjohnson3050]

Try creating three queries.  Two to join together, with the results in the third (target). 
One query with your data (soure query), another query with a data item that is the parameter from your prompt (promt query). 
Create a join object with 0.1 cardinality on both sides of the join (full outer join).
In the target query, create a filter on the data item used in the join ([source query].[join data item] is null).

This should give you a list of the data items in the prompt that do not have a matching data item in your data.

[RickJ]

Hello all,

Sorry to bump this old post but my user has come back with these requirements and I didn't want to create an all new post for the same request. If I should make a new post please let me know and I will do so.

Based on the above info from tjohnson3050 I'm not sure how to execute your plan. I still feel like I would need to get my parameter itesm from an array into a row format. I don't suppose anyone has had any brilliant ideas in the past couple months? :)

Thank you,
Rick

[CognosPaul]

Does this need to be in any format other than HTML? It would be trivial to write a JS function to get this working. I still think an SP would be the best solution. You could pass it the array of selected values, then it would split the values into rows and except the existing values. If the team that handles the DB doesn't have the expertise to write it, I'd be happy to figure it out.

I'm assuming that your users are entering values into a text box, instead of selecting existing values from a table.

Is there a specific reason users don't have rights to execute SQL? If you could give them permission, then it would be fairly easy to write a query or two to handle this.

[RickJ]

I would accept any output options. So we have the JS option which we could do right now, or the SQL/Stored Procedure option in which I would need to engage the service provider team. I would lean towards the JS option while I explore the possiblity of enabling SQL for users.

Rick

[CognosPaul]

Okay, a bit complicated.

I spent a few minutes trying different methods. The fastest way I could think of was the following.

Create two queries.
Query one returns only the codes. So it has one data item and the various filters.
Query two contains a macro:

Code Select Expand

#sq(join(',',split(';',promptmany('years','string'))))#

These two queries will be used to populate two separate arrays. A function will be run on those arrays and return the non matching elements. You can put those elements where ever you want. Unfortunately this is an HTML only solution, as it relies on JavaScript. The JavaScript is cross browser, and does not use any Cognos functions.

The XML I'm posting will have to be downgraded to your version before you can load it. In theory all you should need to change is the first row.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Models']/package[@name='קובייה תאונות דרכים']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>

<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<HTMLItem description="Start">
<dataSource>
<staticValue>&lt;div id="noMatch"&gt; &lt;/div&gt;
&lt;script&gt;
function compare(arr1,arr2){
var lookup = {}
  ,notMatched =[];

for (var j in arr2) {
      lookup[arr2[j]] = arr2[j];
  }

  for (var i in arr1) {
      if (typeof lookup[arr1[i]] == 'undefined') { notMatched.push(arr1[i]); }
}

return notMatched;
}

var receivedValues = [</staticValue>
</dataSource>
</HTMLItem><repeater name="Repeater1" refQuery="Query1">
<contents><HTMLItem description="Received Values">
<dataSource>

<reportExpression>case when RowNumber() = 1 then '' else ',' end + '''' +
URLEncode ([Query1].[שנה - Key]) + ''''</reportExpression></dataSource>
</HTMLItem></contents>
<propertyList><propertyItem refDataItem="שנה"/><propertyItem refDataItem="שנה - Key"/></propertyList></repeater><HTMLItem description="Middle">
<dataSource>
<staticValue>]
,selectedValues=[</staticValue>
</dataSource>
</HTMLItem><singleton name="Singleton1" refQuery="Query2">
<contents><HTMLItem description="codes">
<dataSource>

<dataItemValue refDataItem="Years"/></dataSource>
</HTMLItem></contents>
<propertyList><propertyItem refDataItem="Years"/></propertyList></singleton><HTMLItem name="finish">
<dataSource>

<staticValue>];

document.getElementById('noMatch').innerHTML = 'The following codes were not matched '+compare(selectedValues,receivedValues).join(', ');


&lt;/script&gt;</staticValue></dataSource>
</HTMLItem></contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<queries><query name="Query1"><source><model/></source><selection><dataItem name="שנה" aggregate="none" rollupAggregate="none"><expression>filter([CubeAccidentsLamasAll].[תאריכים].[היררכית תאריך ראשית].[שנה],[CubeAccidentsLamasAll].[תאריכים].[היררכית תאריך ראשית].[שנה].[שנה - Key] in
(#promptmany('years','string')#)
)</expression></dataItem><dataItem name="שנה - Key" aggregate="none" rollupAggregate="none"><expression>[CubeAccidentsLamasAll].[תאריכים].[היררכית תאריך ראשית].[שנה].[שנה - Key]</expression></dataItem></selection></query><query name="Query2"><source><model/></source><selection><dataItem name="Years"><expression>#sq(join(',',split(';',promptmany('years','string'))))#</expression></dataItem></selection></query></queries></report>

[Lynn]

Do you have any plans to migrate to Cognos 10 in the near term? The ability to use external data sounds like it would give you more options to achieve your requirement. You could use a spreadsheet with the requested IDs row-wise and then you could manipulate results more easily.

I realize that doesn't help you now, but thought I'd mention in case C10 is in your near future.