Defining different columns based on one measure columns

[rajeurokz]

Hi,

I have a requirement, where i need to derive different measure columns based on a column depending on a join condition.

The base column is COSTPAID from Trans_F table and i have to derive three other columns AO CostPaid, BO CostPaid and Co Costpaid.
These columns should be based on a condtion i.e.

AO Costpaid = Costpaid where  CT_COSTTYPE_D.TYPECODE='aoCost'
BO Costpaid = Costpaid where  CT_COSTTYPE_D.TYPECODE='boCost'
CO Costpaid = Costpaid where  CT_COSTTYPE_D.TYPECODE='CoCost'

Pls help me in acheiving this.

Thanks in Adv!
rajurokz

[nav_shiv]

Hi rajeurokz

Are you using Report Studio for this report?  Could you not create 3 queries in the report which would each have the necessary filter for the TYPECODE?

So all queries will have AO CostPaid, BO CostPaid and CO CostPaid. the AO query will have a filter for 'aoCost' and data item AO Costpaid will have Costpaid.  The other 2 will have a value of 0.  Once all queries are setup you union them into one 'ReportingQuery' so you can drag the 3 CostPaid columns into your report.

Would that work or have I completely misunderstood? :)

Thanks

[rajeurokz]

Thankd for the reply!

Please suggest me if this can be acheived at the framework level. I'm not including CT_COSTTYPE_D.TYPECODE in the package.

thanks
Rajesh Musturu

[MFGF]

Thankd for the reply!

Please suggest me if this can be acheived at the framework level. I'm not including CT_COSTTYPE_D.TYPECODE in the package.

thanks
Rajesh Musturu

Sounds like you need to add three calculated query items to your query subject in FM, using the following approach:

if ([your Typecode query item] = 'aoCost') then ([your Costpaid query item]) else (null)

Then similar expressions for the remaining two

Cheers!

MF.

[rajeurokz]

Thanks for the help! This works fine!!

Now, I see a problem with the Query Item name.

We are going by 3 layer approach, Design view, Business and Presentation views. Presentation view is the virtual copy(Shortcuts) of the buissiness view. As I added the new column in BV, it is being reflected as '(en-zw) New Query Item', My design lang is en-zw. Now I want to change this to 'AO CostPaid'.

Please guide me.

thanks in advance,
Razz

[MFGF]

Thanks for the help! This works fine!!

Now, I see a problem with the Query Item name.

We are going by 3 layer approach, Design view, Business and Presentation views. Presentation view is the virtual copy(Shortcuts) of the buissiness view. As I added the new column in BV, it is being reflected as '(en-zw) New Query Item', My design lang is en-zw. Now I want to change this to 'AO CostPaid'.

Please guide me.

thanks in advance,
Razz

Right-click on the query item in the Business View and use the 'Rename' option :)

MF.

[rajeurokz]

I treid to rename, but it is still reflecting the same.

[MFGF]

I treid to rename, but it is still reflecting the same.

So after you rename a query item in the Business View, the item somehow reverts back to its previous name in the Business View? Is that what you're saying?

MF.

[rajeurokz]

The desired title is showingup in the business view, but when the package is publised and checked in report studio changes are not reflecting. Its just showing as '(en-zw) New Query Item'.

[MFGF]

The desired title is showingup in the business view, but when the package is publised and checked in report studio changes are not reflecting. Its just showing as '(en-zw) New Query Item'.

If you select the query item, then look at the Properties dialog at the bottom, you will see there is a Language tab next to the Properties tab. You should get access to all language variants of the name in there, allowing you to change them all.

MF.

[rajeurokz]

You nailed it! Thats the solution I was looking for.. thanks a ton!!

language tab resolved the problem I'm facing.


Thanks !!