Topic
2 replies Latest Post - ‏2013-02-28T02:04:26Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts
ACCEPTED ANSWER

Pinned topic Need to get Top 5 elements into cognos report

‏2013-02-27T11:21:32Z |
Hi Guys,

I am trying to create a chart which shows me top 5 claims. My Source is a TM1 cube and I am creating an active report.
My Chart would have claims on the x-axis and the 'Net amount' on the y-axis.One contract has multiple claims.
I am passing the 'Contract' info to the chart through master detail relationship.Now I want to display only top 5 claims under each contract.

I created a Data Item1 as 'tuple(Net amount due in this certificate ( A - B ),IPC.Contracts.Contracts->?p_Contract?,currentMember(IPC.IPC IDS.IPC IDS),Malaysian Ringgit)'
After that I used Rank func on 'Data Item1'. i.e. Rank(Data Item1)..and named it 'Data Item2'...I then added a detail filter as Data Item2<6. But this is giving me error as follows:

The DMR subquery failed to plan and produced an un-expected error: java.lang.NullPointerException

Any help would be really appreciated.

Thanks
Updated on 2013-02-28T02:04:26Z at 2013-02-28T02:04:26Z by SystemAdmin
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: Need to get Top 5 elements into cognos report

    ‏2013-02-27T12:42:51Z  in response to SystemAdmin
    It looks like you are using the SQL Rank instead of the OLAP Rank.... and OLAP Rank function would look like:
    RANK([Revenue] within set [SetExpression]
    


    Since you want the Top 5 elements , instead of Ranking, then filtering, you may want to have a look at the topCount function which does most of these things for you.
    Updated on 2014-03-25T07:42:40Z at 2014-03-25T07:42:40Z by iron-man
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Need to get Top 5 elements into cognos report

      ‏2013-02-28T02:04:26Z  in response to DanielWagemann
      Thanks Daniel,

      Its working.
      I also have one more question related to the same model where my source is TM1 and I have a measure dimension whose one of the members is a 'Period Ending Date'.The other dimension being claims and contract. One contract can have multiple claims.
      In my report I want to get 'Net Amount'for only those claims(for my selection on contract),whose 'period ending date' is the last submitted date.
      Could you also help on this?