Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2013 at 05:46 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

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

  • author's profile photo Former Member
    Former Member
    Posted on Dec 04, 2013 at 09:57 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2013 at 05:23 AM


    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?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.