*Expression pharsing error*

[Nandini.t]

Hi All,

I have 2 data items one is measure and other is code like
[Booked Transactions]  &  [Revenue code]

I have many revenue codes like 'C1','C2','C5' etc
I want to get Booked Transactions for 3 of the revenue codes('C1','C2','C5' ).
so,
i created different data items as
[Booked Transactions]  for [Revenue code]='C1' 

but am getting expression pharsing error so pll help me out to how to write the expression inside.   

Thanks in advance for any help!!!

[Nandini.t]

Any views???  :(

[HalfBloodPrince]

You have to add filter as Revenue code]='C1'  instead of creating Data item i guess.

[Nandini.t]

i want to get 3 columns of booked transactions for 3 different revenue codes. so filter condition doesnt work rite.

[HalfBloodPrince]

Yeah then try this code for each

if([Revenue code]='C1' ) then
([Revenue code] )

else
('')

[Nandini.t]

no, i want to get booked transaction for 3 diff revenue codes say i have 4 columns

policy no |  Booked transaction for 'C1' | Booked transaction for 'C2' | Booked transaction for 'C5'

Booked transaction for 'C1' = [Booked transaction] for [Revenue code]='C1'
Booked transaction for 'C2' = [Booked transaction] for [Revenue code]='C2'
Booked transaction for 'C6' = [Booked transaction] for [Revenue code]='C6'
 

[HalfBloodPrince]

Try this

if([Revenue code]='C1' ) then
( [Booked transaction])
else
(0)

[Nandini.t]

Hi Prince,

i used case statement  but iam getting wrong result.

case
when
([Revenue Code]='C1')
then
[Booked Tax Original Amount]
else (0)
end

the thing is [Booked Transaction] contains the transaction for all the codes together.
if i check [Booked Transaction] seperately for each code like
[Booked Transaction]--( without any filter) >> {Booked Transaction=5,187.74}
[Booked Transaction] for C1---(with filter for C1)>>{Booked Transaction= 2,526.09}
[Booked Transaction] for C2---(with filter for C2)>>{Booked Transaction=2,483.73}
[Booked Transaction] for C6---(with filter for C6)>>{Booked Transaction=177.92}

[Booked Transctn]=[Booked Transaction] for C1+[Booked Transaction] for C2+[Booked Transaction] for C6
        5,187.74    = 2,526.09 +   2,483.73  +  177.92

so, how to extract data from booked transaction for each code for the same list.

[HalfBloodPrince]

what output its showing now ???
also you have put [Booked Tax Original Amount] instead of [Booked Transaction]. check it.

[Nandini.t]

instead of filtering the amount for each code its taking entire Booked Transaction in to a only one column.

K i will clear you the flow of report,

Based on branch selected the report output should give ploicy numbers with associated name and booked transactions of 3 columns( for c1, c2 and c6)

Each policy number will be related to 1 or 2 or 3 codes sometimes.

for a policy number 'X' Report should like below

Policy no | Name  | Booked Trans | Booked Trans for C1 | Booked Trans for C2 | Booked Trans for C6
-----------------------------------------------------------------------------------------------------------------------------
   X         |    A       | 5,187.74         |    2,526.09                |    2,483.73                         |   177.92                 
              |             |                        |                                  |                                           |     


But the outoput iam getting is different if i use the case statement

Policy no | Name  | Booked Trans | Booked Trans for C1 | Booked Trans for C2 | Booked Trans for C6
-----------------------------------------------------------------------------------------------------------------------------
   X         |    A       | 5,187.74         |                 0                |    5,187.74                |   0                 
              |             |                        |                                   |                                   |     

So please suggest any ohther expression rather than case or if.
         

[HalfBloodPrince]

Hi Nandini,
                 Simple thing is used Cross tab  as
In  Columns            |Booked Trans |[Revenue Code]
also put  |Booked Trans | in Default measure
In Rows     Put  as         
  Policy no | Name|

so that it wil provide u total value in Booked Trans and also distributed by Revenue Code

Try and let me know

[Nandini.t]

No, prince my client needs it to be a list.

[Nandini.t]

its very urgent pll someone help me out..... any help would be highly appriciated.. ???

[Gyana.Sahoo]

Create three Query whith different filter (for C1,C2,C6) then Union these 3 Query. Use that final Query in the List.

Query1
------------
Policy no
Name
Booked Trans for C1 ..................................use filter for c1
Booked Trans for C2 (make it as 0)
Booked Trans for C6(make it as 0)

Query2
-------Policy no
Name
BooBooked Trans for C1 (make it as 0)
Booked Trans for C2 ........................................use filter for C2
Booked Trans for C6(make it as 0)

Query3
-----------
Policy no
Name
BooBooked Trans for C1 (make it as 0)
Booked Trans for C2 (make it as 0)
Booked Trans for C6..................................filter for C6

Union Query
--------------------
Policy no
Name
Booed Trans( BooBooked Trans for C1 +Booked Trans for C2+ Booked Trans for C6
Booked Trans for C1
Booked Trans for C2
Booked Trans for C6


---

[HalfBloodPrince]

Hi Nandini,

Lets try This

Create Query 1 with columns
Policy no ,
Name, Booked Trans,
and Calculation of If(Region code=q1) then (Booked Trans) else (0).

same way create Query 2 only change last calculation as (Code=Q2 )

then go to query explorer-> click on Queries

drag a Join add Query1 and Query2
double click on join condition -> create link to  Policy no to  Policy no and  Name to Name with 1.1 relation ship.

then double click on query 3 and add columns as required
Policy no -Query1
Name-Query 1
Booked Trans-Query 1
Calculation Q1- Query1
Calculation Q2-Query 2


same way create Query 4 with Remaining Calculation and join it with query 3. Drag Required  columns in Query 5 From Query 3 and 4.

Check its working or not

[Nandini.t]

 :) a big thanks to sekhar and Prince. It worked!!!