This discussion is archived
1 Reply Latest reply: Nov 15, 2012 4:36 AM by Srini VEERAVALLI RSS

Data Modeling and Report Logic

user607649 Newbie
Currently Being Moderated
I am using OBIEE10g.
I have one table

Table_Bank(Very huge volumn of data available in this table)
Date SendBank ReceivedBank SendCountry ReceivedCountry INDICATOR UnicRef
10/11/2012 ICICI Hdfc UK IND SEND 12
11/11/2012 ICICI BARCLAY IND UK SEND 13
10/11/2012 ICICI SCB SG MY SEND 14
10/11/2012 SBI ICICI SG SE RECEIVED 15
11/12/2012 KARUR ICICI SW IND RECEIVED 16.

Note: ICICI send amount to diff country and ICICI received amount from diff country

Now My requirement is

Dashborad prompt
prompt1_Date,Country_prompt

Note:Country_prompt should be display all distinct country(Send or Receive country values)
In above report, i should filter based on prompt.
In the report I dont know how to set filter column.
B'cos country available sender_country and Receiver_country also.


Report:
Month Receive_Count Send_Count
NOV-2012 1 3
Dec-2012 1 0

Report Query:
Received_Count
Select month(date),count(uniref) from table_bank
where INDICATOR='SEND'
group by month

Send_Count:
Select month(date),count(uniref) from table_bank
where INDICATOR='RECEIVED'
group by month


How to implement this.
1.Can i implement report itself
2.Can use administrator tool to create view or.
3.How to implement In the report filter.B'cos country available sender_country and Receiver_country also

Could you please let me know with details and send me logic also
  • 1. Re: Data Modeling and Report Logic
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I would suggest to use Receive_Count and Send_Count in the prompt instead of single column for both.
    Since you are asking for 1 column for country, use presentation variable for country and set that in the report
    Receive_Count = PS variable or Send_Count= PS variable.
    That means report should fecth (for selected date and country from prompt) for that date country is in Receive_Count or Send_Count

    Create measures in rpd
    case when INDICATOR='SEND' then uniref end and set aggregation to count and do the same for
    INDICATOR='RECEIVED'

    Or else you can do this in the report pull column uniref twice and for each use filter button from fx and add above condition.

    If helps pls mark else send me email

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points