Recent Posts

Pages: [1] 2 3 ... 10
1
Cognos Administration / Re: Issues with audit data base
« Last post by misscognos on Today at 10:07:52 am »
Any errors in the logs after the job starts? Ie. If you are in a fully working state, then you run the job to "break it", what do you see in the cogserver.log?
2
Report Studio / Difference between two dataitem of two different query
« Last post by lahdeb on Today at 07:53:14 am »
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


3
Report Studio / Re: monthly reports
« Last post by lahdeb on Today at 07:25:48 am »
Thank you "ImSteve" for your reply
4
Framework Manager / Re: SCD 2 and Fact Table
« Last post by Sep2013 on Today at 05:18:37 am »
also... no other modelling levels above this. although i was thinking ti create star schema groupings and separate out all star models in namespaces with shorcuts.
For now i have to use a separate query to filter claims for ss 2017-01-01 and then do an inner join to.main query to get
 inner join.

But i thought there could be a better way to design the.model so that i can apply filter in main query instead of separate query .

i used kimballs techniques for modelling.. I somehow feel my model is too normalised and.may be thats why hard to use?
5
Framework Manager / Re: SCD 2 and Fact Table
« Last post by Sep2013 on Today at 05:14:32 am »
Hi MF.
You are right. These two tables are joined on naturak keys and crash n claimant name no. can have multiple rows in dim claimant as its a ss table.
1) If i use surrogate key ... payments in lifetime of a claim will.br attributed to different snapshots of claimant and thus i cant derive all payments made until 2017-01--01 for claimant record as it looked like on 2017-01-01. because only payments made for claim during that snapshot will appear n ignore any previous payments.

2) I have only one query in report studio with detail filter as 2017-01-01 between cast (from date, date) and cast (yo date, date).
i am casting dates as they are in datetime format and until i fix this issue in fm for next realesse i am resolving this temporarily.

6
Framework Manager / Re: SCD 2 and Fact Table
« Last post by MFGF on Today at 05:00:03 am »
Hi MF,

It does appear like a stitch query, but I cant think of a solution
Attached is FM model with cardinalities.

For now I have to have separate queries for claim and payments and then do an inner join in report studio.
I feel my model has some issues as below:
1) Connection of SCD 2 with Fact tables (fro fact claim its OK as it also joins to dim claimant on ss dates as a new fact is also created if claim details change otherwise I get outer join and all snapshots)
2) I cant select facts from multiple fact tables in same query as unwanted data is produced e..g No . of claims from fact claim, no. of briefs from fact brief, no. of writs from fact writ and paid amount from fact payments.

The only SCD2 in this model is dim claimant.

Below is the Cognos SQL whenI select data from Dim claimant .dim payment and fact payments, it can be seen as in bold text that ss date filter is applied to claimant but then full outer join ignores it:

with
Dim_Claimant7 as
    (select
           Dim_Claimant.FROM_DATE  as  FROM_DATE,
           Dim_Claimant.TO_DATE  as  TO_DATE,
           Dim_Claimant.CRASH_NO  as  CRASH_NO,
           Dim_Claimant.CLAIMANT_NAME_NO  as  CLAIMANT_NAME_NO,
           Dim_Claimant.ITEM_STATUS_CODE  as  ITEM_STATUS_CODE,
           Dim_Claimant.ITEM_STATUS  as  ITEM_STATUS
     from
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_CLAIMANT Dim_Claimant
     where
           (DATE '2017-09-30' between cast(Dim_Claimant.FROM_DATE as date) and cast(Dim_Claimant.TO_DATE as date))
    ),
D5 as
    (select
           Dim_Claimant7.ITEM_STATUS  as  Item_Status,
           Dim_Claimant7.CLAIMANT_NAME_NO  as  Claimant_Name_No,
           Dim_Claimant7.CRASH_NO  as  Crash_No,
           Dim_Claimant7.ITEM_STATUS_CODE  as  Item_Status_Code,
           Dim_Payments.CERTIFIED_DATE  as  Certified_Date,
           XSUM(Fact_Payments.PAID_AMOUNT  for Dim_Claimant7.ITEM_STATUS,Dim_Claimant7.CLAIMANT_NAME_NO,Dim_Claimant7.CRASH_NO,Dim_Claimant7.ITEM_STATUS_CODE,Dim_Payments.CERTIFIED_DATE )  as  Paid_Amount
     from
           Dim_Claimant7,
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_PAYMENTS Dim_Payments,
           "Datamart Presentation Area"..DTMP_PSTATIC.FACT_PAYMENTS Fact_Payments
     where
           (Dim_Claimant7.ITEM_STATUS_CODE = 'L') and
           ((Fact_Payments.CRASH_NO = Dim_Claimant7.CRASH_NO) and (Fact_Payments.CLAIMANT_NAME_NO = Dim_Claimant7.CLAIMANT_NAME_NO)) and
           (Fact_Payments.PAYMENT_KEY = Dim_Payments.PAYMENT_KEY)
     group by
           Dim_Claimant7.ITEM_STATUS,
           Dim_Claimant7.CLAIMANT_NAME_NO,
           Dim_Claimant7.CRASH_NO,
           Dim_Claimant7.ITEM_STATUS_CODE,
           Dim_Payments.CERTIFIED_DATE
    ),
D6 as
    (select distinct
           Dim_Claimant8.ITEM_STATUS  as  Item_Status,
           Dim_Claimant8.CLAIMANT_NAME_NO  as  Claimant_Name_No,
           Dim_Claimant8.CRASH_NO  as  Crash_No,
           Dim_Claimant8.ITEM_STATUS_CODE  as  Item_Status_Code
     from
           "Datamart Presentation Area"..DTMP_PSTATIC.DIM_CLAIMANT Dim_Claimant8
     where
           (DATE '2017-09-30' between cast(Dim_Claimant8.FROM_DATE as date) and cast(Dim_Claimant8.TO_DATE as date)) and
           (Dim_Claimant8.ITEM_STATUS_CODE = 'L')
    )
select
       coalesce(D5.Item_Status,D6.Item_Status)  as  Item_Status,
       coalesce(D5.Claimant_Name_No,D6.Claimant_Name_No)  as  Claimant_Name_No,
       coalesce(D5.Crash_No,D6.Crash_No)  as  Crash_No,
       coalesce(D5.Item_Status_Code,D6.Item_Status_Code)  as  Item_Status_Code,
       D5.Paid_Amount  as  Paid_Amount,
       D5.Certified_Date  as  Certified_Date
 from
       D5
        full outer join
       D6
        on ((((D5.Item_Status = D6.Item_Status) and (D5.Claimant_Name_No = D6.Claimant_Name_No)) and (D5.Crash_No = D6.Crash_No)) and (D5.Item_Status_Code = D6.Item_Status_Code))

Hi,

Can you elaborate on the join between Dim Claimant and Fact Payments? I'd expect this to be on the surrogate key in Dim Claimant (Claimant Key?) but the query appears to be using a combination of Claimant Name No and Crash No to join them? If you have multiple dimension rows with the same Claimant Name No and Crash No (because of Type 2 SCD changes) you're going to get double-counting of your facts?

Can you elaborate on how you are applying this filter to your report? Is it a regular detail filter with no casting? Are you using the same query subject for the filter as you are for the items in the main query?

Are there any other modelling levels above / below the diagram you posted that re-define relationships?

MF.
7
Cognos Administration / Re: Issues with audit data base
« Last post by karthik12345 on Today at 03:37:58 am »
Thanks for reply!!

yes, userlogon recording the data, but we have problem with Runreport table. its not recording the data ones we run the job.
we are taking the details for runreport table.

Thanks,
Karthik..
8
Framework Manager / Re: Calculated Time Periods using DMR Time Dimension
« Last post by ukflix on Today at 03:20:36 am »
Hi CognosPaul

Yes indeed it is a crosstab layed out exactly like you say.  Here is the XML.
FYI if I create the Current Week Member calculation using a MUN defined in a data item, I can then create the report and see the correct values in each column.  However, going forward, I want to try and avoid this because I have about 50 of these time periods to create plus I also want to try and feed in a selected day so that the periods become dynamic and not just based on today.  Basically trying to mimic the functionality that I have in my OLAP cube but using a DMR time dimension.  The first step is to be able to feed in a current day and produce the other levels (weeks/months/quarters etc) from this.  I was trying using "ancestor".

Thanks for looking

Regards



<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en">
            <modelPath>/content/folder[@name='Packages']/folder[@name='Development']/package[@name='2 Views - Time Calculations']/model[@name='model']</modelPath>
            <drillBehavior modelBasedDrillThru="true"/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItem name="Amount_in_Local_Currency" aggregate="total"><expression>[presentation].[Fact AP].[Amount_in_Local_Currency]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Current_Day_BOD" aggregate="none" rollupAggregate="none"><expression>[presentation].[Current_Day_BOD]</expression><XMLAttributes><XMLAttribute name="RS_dataUsage" value="unknown" output="no"/></XMLAttributes></dataItem><dataItem name="Current_Week_BOD" aggregate="none" rollupAggregate="none"><expression>[presentation].[Current_Week_BOD]</expression><XMLAttributes><XMLAttribute name="RS_dataUsage" value="unknown" output="no"/></XMLAttributes></dataItem><dataItem name="Currency_Code" aggregate="none" rollupAggregate="none"><expression>[presentation].[Currency].[Currency_Code]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
               </query>
            </queries>
            <layouts>
               <layout>
                  <reportPages>
                     <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
                        <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
                           <contents>
                              <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
                                 <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Amount_in_Local_Currency"/></dataSource></textItem></contents></crosstabCorner>
                                 
                                 
                                 <noDataHandler>
                                    <contents>
                                       <block>
                                          <contents>
                                             <textItem>
                                                <dataSource>
                                                   <staticValue>No Data Available</staticValue>
                                                </dataSource>
                                                <style>
                                                   <CSS value="padding:10px 18px;"/>
                                                </style>
                                             </textItem>
                                          </contents>
                                       </block>
                                    </contents>
                                 </noDataHandler>
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="xt"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              <defaultMeasure refDataItem="Amount_in_Local_Currency"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Current_Week_BOD" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Current_Day_BOD" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Currency_Code" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
                           </contents>
                        </pageBody>
                        <pageHeader>
                           <contents>
                              <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
                                 <contents>
                                    <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
                                       <dataSource>
                                          <staticValue/>
                                       </dataSource>
                                    </textItem>
                                 </contents>
                              </block>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="ph"/>
                              </defaultStyles>
                              <CSS value="padding-bottom:10px"/>
                           </style>
                        </pageHeader>
                        <pageFooter>
                           <contents>
                              <table>
                                 <tableRows>
                                    <tableRow>
                                       <tableCells>
                                          <tableCell>
                                             <contents>
                                                <date>
                                                   <style>
                                                      <dataFormat>
                                                         <dateFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </date>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:left;width:25%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <pageNumber/>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:center;width:50%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <time>
                                                   <style>
                                                      <dataFormat>
                                                         <timeFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </time>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:right;width:25%"/>
                                             </style>
                                          </tableCell>
                                       </tableCells>
                                    </tableRow>
                                 </tableRows>
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="tb"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse;width:100%"/>
                                 </style>
                              </table>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="pf"/>
                              </defaultStyles>
                              <CSS value="padding-top:10px"/>
                           </style>
                        </pageFooter>
                     </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-10-19T18:38:02.587Z" output="no"/></XMLAttributes></report>
9
Report Studio / Re: Cognos - SQL - Aggregate behavior
« Last post by ranalytics on Today at 12:51:43 am »
Hi,

I would check the data item aggregate function and see what is it set to first.

The Data item aggregation and Rollup aggregate function has been set as "Total", but I don't understand why the report is still using  minimum function. Is it because SQL used in the report?

On the other hand, I created summary filter instead of detail filter with after auto aggregation. It worked as expected, but I don't know why detail filter with after auto aggregation didn't work.

Regards
RK
10
Framework Manager / Re: Error in FM CA 11.0.7 with Oracle JDBC
« Last post by the6campbells on 19 Oct 2017 09:19:18 pm »
Do NOT delete the OracleJDBCAdaptor.jar, that is NOT an ORACLE JDBC driver file.

Enable web based modelling simply denotes if the connection can be seen/used from a Data Module.

The error message implies your connection has one or more connection command blocks are triggering the error.


Pages: [1] 2 3 ... 10

       
Twittear