Skip to Content
avatar image
Former Member

SQL cross tab query

how do you do the equivilent in a webi report?

I dont think case statements are supported

e.g.

Code:

SELECT

SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and '2004-08-31' THEN amount ELSE 0 END) As m2004_08,

SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and '2004-09-30' THEN amount ELSE 0 END) As m2004_09,

SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and '2004-10-31' THEN amount ELSE 0 END) As m2004_10

FROM purchases

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 15, 2010 at 01:21 PM

    u can place this query in data provider custom query. But number of columns and objects in result objects should be same with same data type. Is this you are expecting.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      your split logic sounds like a great idea Dave thanks

      I am trying to produce this statement:

      SUM(case when mks_clienttype ='C' and mks_reason4fail in ("",'pri record found') then clients end) as Client_email

      variable clientTypeFlag

      code =If([Mks Clienttype] = "C") Then 1 Else 0

      variable reason4failFlag1

      code =If([Mks Reason4fail(trim)] = "pri record found" Or [Mks Reason4fail(trim)] = "") Then 1 Else 0

      variable clientEmail

      code ?

      how to actually get the sum of both clientTypeFlag and reason4failFlag1? I am trying to figure it out with no luck I have tried this:

      variable: ClientEmail1

      code: =Sum([clientTypeFlag]) Where ([clientTypeFlag] = 1)

      variable: ClientEmail2

      code: =Sum([clientTypeFlag]) Where ([reason4failFlag1] = 1)

      variable: clientEmail

      code: =[ClientEmail1] + [ClientEmail2]

      want the end result to look like this:

      Name | clientemail

      john | 5

      bob | 3

      jo | 2

      Edited by: Dennis Alishah on Sep 21, 2010 5:58 AM