SQL cross tab query

[Former Member]

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

[Former Member]

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.

[Former Member]

thanks Sathish, but i would like to achieve this without using custom SQL

[Former Member]

can u try with if Condition

three different objects

for eg:

Object 1: - =Count(If(DateField between Values ;FieldName))

[Former Member]

Yes, you simply use the "if then else" function within webi., either in a variable, or in a formula in a cell.

Thanks

[Former Member]

thanks guys that works fine but what about for another statement I have below:

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

I use this in infoview - i avoid using 'In' as I want to create a simple test:

=Count(If([Mks Clienttype] = "c" And [Mks Reason4fail] = "pri record found"; [Mks Clienttype]))

there is no errors in the variable but when displayed on the report all the values are zero - incorrect there are many non zero values

also what is the importance of the FieldName in =Count(If(DateField between Values ;FieldName)) ???

Edited by: Dennis Alishah on Sep 20, 2010 3:23 AM

Edited by: Dennis Alishah on Sep 20, 2010 3:23 AM

Edited by: Dennis Alishah on Sep 20, 2010 3:24 AM

[Former Member]

In my opinion, the best way to split items out into columns is to provide the split logic in a separate variable that returns a boolean result, and then use the Sum(...) Where (...) syntax. For example, if I wanted to return sales for 2010 I could build a variable named "2010 Flag" that looks like this:

=If ([Year] = '2010') Then 1 Else 0

or if I am on an older version:

=If ([Year] = '2010');1;0)

These two formulas will return a simple 1/0 flag. Then I can do this for 2010 Sales

=Sum([Sales]) Where ([2010 Flag] = 1)

If my logic for my flag is more complex (as in your latest example) then all I have to do is update the initial variable that generates the 1/0 result and my other variable will still work.

[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