on 12-03-2013 3:53 AM
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 | 10 | 1-1-2013 | |
xyz@gmail.com | 10 | 1-2-2013 | |
abc@yahoo.com | 12 | 1-3-2013 | |
xyz@gmail.com | 12 | 1-4-2013 | |
john@gmail.com | 12 | 1-3-2013 | |
abc@yahoo.com | 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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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??
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.