Difference between two dataitem of two different query

[lahdeb]

Hi community ,
I will try to explain my need , and problems I m facing :
Actualy I want to calculate the difference between the nombre of rows of some tables , but the problem is that these  tables exists in two different database (for two different server :/)

For example : Tables A1,A2 from DB A ,
                     Tables B1,B2 from DB B,
I want to have a result which contains :
- Nbrows(A1) , Nbrows(A2) , NumbRows(B1) , NbRows(B2), NbRows(A1)-NbRows(B1),NbRows(A2)-Nbrows(B2)

Solution :
I m using the direct sql since I don't have a package that contains these informations , so what I did is :


Sql1
select count(*) NbA1
from A1 where date>=_add_days((getdate(),-5)
Union
select count(*) NbA2
from A2 where date>=_add_days((getdate(),-5)

Sql2
select count(*) NbB1 from B1 where date>=_add_days((getdate(),-5)
Union select count(*) NbB2 from B2 where date>=_add_days((getdate(),-5)

and I created two requete : 1-with dataitems NbA1 (which contains two rows : NbA1 et NbA2)
                                         2- with dataitems NbB1 ((which contains two rows : NbB1 et NbB2)

so I could create my liste 

------    A     ------    B    -------------   A-B  -----
         NbA1            NbB1                     ?
------------------------------------------------------
         NbA2            NbB2                     ?

but I don t know how to calculate the difference A-B :/, is there a way to do difference between two liste , or to identify a colonne of liste ?

Any idea please

Thank you

[New_Guy]

Hi,
Check the below code and see if it helps.
Good luck
New guy

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='ABCD']/package[@name='ABCD']/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/>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2017-08-01T12:34:04.364Z" output="no"/></XMLAttributes><queries><query name="Query1">
<source>

<sqlQuery name="SQL1" dataSource="great_outdoors_sales">
<sqlText>Select   count(*),1 from COUNTRY
union
Select   count(*),2 from ORDER_METHOD</sqlText>
<mdProjectedItems><mdProjectedItem name="COUNT(*)"/><mdProjectedItem name="1"/></mdProjectedItems></sqlQuery></source>
<selection><dataItem name="COUNT(*)"><expression>[SQL1].[COUNT(*)]</expression></dataItem><dataItem name="1"><expression>[SQL1].[1]</expression></dataItem></selection>
</query><query name="Query2">
<source>

<sqlQuery name="SQL2" dataSource="great_outdoors_sales">
<sqlText>Select   count(*),1 from SALES_REGION
union
Select   count(*) ,2 from RETURNED_ITEM</sqlText>
<mdProjectedItems><mdProjectedItem name="COUNT(*)"/><mdProjectedItem name="1"/></mdProjectedItems></sqlQuery></source>
<selection><dataItem name="COUNT(*)"><expression>[SQL2].[COUNT(*)]</expression></dataItem><dataItem name="1"><expression>[SQL2].[1]</expression></dataItem></selection>
</query><query name="Query3">
<source>

<joinOperation>
<joinOperands>
<joinOperand cardinality="1:N"><queryRef refQuery="Query1"/></joinOperand>
<joinOperand cardinality="1:N"><queryRef refQuery="Query2"/></joinOperand>
</joinOperands>
<joinFilter>
<filterExpression>[Query1].[1] = [Query2].[1]</filterExpression>
</joinFilter>
</joinOperation></source>
<selection><dataItem name="COUNT(*)"><expression>[Query1].[COUNT(*)]</expression></dataItem><dataItem name="1"><expression>[Query1].[1]</expression></dataItem><dataItem name="COUNT(*)1"><expression>[Query2].[COUNT(*)]</expression></dataItem><dataItem name="11"><expression>[Query2].[1]</expression></dataItem><dataItem name="Data Item1"><expression>[COUNT(*)] - [COUNT(*)1]</expression></dataItem></selection>
</query></queries></report>

[lahdeb]

Hi New guy and thank you for your reply ,

But I realy dont know how to open your xml specification , I m trying to analyse it .

Can you please tell me if we can just calculate the difference between the containt of two singletons ?

Exemple ; singletons 1 contains : 94 |  singleton 2 contains 4
I want two have some thing like singleton 3 =94-4 =90?

Thank you

[New_Guy]

Hi,
Copy the xml and go to tools menu item dropdown in reportstudio and select 'open report from clipboard' option and then you will see a error msg saying cannot find the package click ok. After that you can check the sql and the join condition. Singleton gets only one row of data and we dont which one it gets. Let me know if you are successful with the xml part.
Good luck
New guy

[lahdeb]

Hi

Thank you New_guy , this is what I m actualy doing , but I have a lot of union since I will use 5 tables in each databse , but the problem is that the join with 1,n-->1,n didn't work very well :/ I have a compisit primary key which is the combination of three colomns .

So what I m trying to do now:
1)  is to select the count(*) as "nb of row T1" in one query (it will return a number 94 for expl) and put this data item in a singleton 1.
2) select count(*) as "nb of rows T2" in an other query (it will return an other number 4 for expl) and put this data item in a singleton 2.
3) I don t know if cognos can let me put these two singletons in two variables or use them in an html block for calculate the difference between their values :/ ,

[New_Guy]

Hi,
You cannot use singletons in a calculations without any join condition between them. Can you try to create a view as per your requirements in the 2 db's and then use them?
Good luck
New guy