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!!!
Any views??? :(
You have to add filter as Revenue code]='C1' instead of creating Data item i guess.
i want to get 3 columns of booked transactions for 3 different revenue codes. so filter condition doesnt work rite.
Yeah then try this code for each
if([Revenue code]='C1' ) then
([Revenue code] )
else
('')
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'
Try this
if([Revenue code]='C1' ) then
( [Booked transaction])
else
(0)
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.
what output its showing now ???
also you have put [Booked Tax Original Amount] instead of [Booked Transaction]. check it.
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.
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
No, prince my client needs it to be a list.
its very urgent pll someone help me out..... any help would be highly appriciated.. ???
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
---
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
:) a big thanks to sekhar and Prince. It worked!!!