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

[Former Member]

Hi BO experts,

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

Table  A

Email address	Type	Code	Date
abc@yahoo.com	Email	10	1-1-2013
xyz@gmail.com	Email	10	1-2-2013
abc@yahoo.com	Email	12	1-3-2013
xyz@gmail.com	Email	12	1-4-2013
john@gmail.com	Email	12	1-3-2013
abc@yahoo.com	Email	12	1-20-2013
nik@yahoo.com	TXT	13	1-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 address	Total emails Sent	failed email
abc@yahoo.com	3	2
xyz@gmail.com	2	1
john@gmail.com	1	1

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

[Former Member · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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")