cancel
Showing results for 
Search instead for 
Did you mean: 

SAP BO 3.1, Report does not pull the right numbers from Universe/Database

Former Member
0 Kudos

Hi BO experts,

I have one derived table which im using to develop my report.

Table  A

Email addressTypeCodeDate
abc@yahoo.comEmail101-1-2013
xyz@gmail.comEmail101-2-2013
abc@yahoo.comEmail121-3-2013
xyz@gmail.comEmail121-4-2013
john@gmail.comEmail121-3-2013
abc@yahoo.comEmail121-20-2013
nik@yahoo.comTXT131-3-2013

Code- 10 means ----sent mails

code- 12 means-- failed mails

code-13 means--just a txt message

1- we want to count all emails and show emails address  in table A where "Type"= 'Emails' and in the same table we want to add new colum where we want to count all the emails which have code='12' and Type= 'Emails' from table A

this is what we expecting

Emails table

Email addressTotal emails Sentfailed email
abc@yahoo.com32
xyz@gmail.com21
john@gmail.com11

Now problem is in WEB 3.1 when we drag the object for failed email  in the above table (with in specific date) it shows only i count regardless of date if there is atleast when failed email in table.

for example:- from Emails table where email address is abc@yahoo.com . i see total emails sent are 3 but failed email 1, instead of showing 2.

our Database is DB2.

what i'm doing wrong? why im not able to show correct numbers of failed emails?

thanks a lot

imtiaz

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Imitiaz,

First of all this calcualtion has nothing to do with database.

How are your count columns defined?

At universe or report?

If at report, than the formula should be like

Total emails sent = count(email_address)

Failed email=count(email address) Where CODE=12

If you are applying the count in universe, just check this

Total emails sent = count(email_address)

Failed email=count(email address) Where CODE=12

The where condition you have applied for failed email will be part of your query, which will effect both.

Also check if the selected date holds the current count.

Thanks

Gaurav

Former Member
0 Kudos

thank you Gaurav,

Total emails sent = count(email_address)

Failed email=count(email address) Where CODE=12

If you are applying the count in universe, just check this

Total emails sent = count(email_address)

Failed email=count(email address) Where CODE=12

thats exactly what i did , please read my probelm i response to sachin .......webi is tricking me @  date ...im pulling this report based on Log date for example ( betwen---1-2-2013----1-30-2013)

but still no match on failed emails count.....in webi it shows one count while in DB it shows there are few more...and they are correct.

i donot know if DB2 and WEBi has different way of formatting date??

Former Member
0 Kudos

You should define your code as a dimension from universe if its not defined already...

Once that is done try with formula below and place in the column... You can change the where clause to code = 12

I have tested the formula to be working in your scenario

=Sum(1) Where ([Type] = "Email")

Answers (2)

Answers (2)

Former Member
0 Kudos

Hope you are doing the steps as below

Table on which report will build is a Table A as shown in the original post

1. On WebI query panel, you can see objects on left side as Email Address, Type, Code and Date

2. You are dragging Email Address, Type, Code and Date in query result pane

3. You are applying filter prompt on date as Date  >=1-1-2013 and Date <=1-20-2013(This filter should be from the same table from where code is coming)

4. You are applying another static filter as Type  = "Email'

5. You are getting first 6 rows on WebI report out of 7 rows shown in Table Afor objects Email Address, Type, Code and Date(Remove this table)

6. Create variable as Email sent =Count(Email Address) where Code = 10

7 Create variable as Email failed = Count(Email Address) where code =12

8. Drag object Email address, Email Sent and Email failed variables on report. It will show correct count.

If you do not want Filter as Type  = "Email' at query level then the variables would be

6. Create variable as Email sent =Count(Email Address) where Code = 10 and Type  = "Email'

7 Create variable as Email failed = Count(Email Address) where code =12 and Type  = "Email'

You can also create same variables on code as well

Please let us know if anything else you are doing. Hope this helps

Former Member
0 Kudos


Hi,

Can you please give answers to following

1. The same query works fine at database level?

2. All objects on WebI report are coming from only derived table?

3. are you giving any date range for WabI report?

4. Why are you not taking count on WebI report based on original table by creating variables instead of Derived table?

Former Member
0 Kudos

First of all thank you very much....!!!

The same query works fine at database level? 

100%, i get the correct results..

2. All objects on WebI report are coming from only derived table?

100%...no futher joins or objects dragging

3. are you giving any date range for WabI report?

Yes,,,,,this the catch.......it gives me the total count of email addresses by date...but also shows no of emails which were failed for that counted emails regardless of date...like if i get 20 emails count between 1-2-2013---1-30-2013.....it will also shows failed email count, even though they were failed at the later date...like 3-3-2013 where some of the email might have got code.12 (failed) ......

my problem i want to see only  sent and failed emails count with in specific time frame, webi is not allowing me doing that...

4. Why are you not taking count on WebI report based on original table by creating variables instead of Derived table?

Yes....i did, i do apply count on all the sent emails: it gives me correct no of emails sent. but when i do the same to failed email, it gives either same no of emails which were sent successfully and then also failed later some time......it showing me the same number in both coulm,, which is wrong...

hope im making sense...

Former Member
0 Kudos

ALSO...one more thing to add....

our database is DB2...

when i run he query in TOAD, it gives me correct number of failed emailed

like abc@gmail.com   1-1-213  4:12:45PM     code-12 (failed)

      abc@gmail.com   1-1-2013 4:20:50 PM  code-12(failed0

it gives two count for email sent but one count for failed email  which should be 2.

it couns perfect at DB level but not in webi when i apply count