Problem to do a DB based in a picklist value.

[manoel.ss]

Admin Note : Moved to the correct forum, please post in the correct forum, ty

Dear friends, I have two cubes to calculate the contracts costs of my company and I am having a little problem to do a simple DB to consolidate the informations.

First Cube CONTRACTS_ADJUSTMENT with dimensions:
ALL.D.YEAR
ALL.D.MONTH
ALL.D.CONTRACT
ALL.M.ADJUSTMENT: with a input of the adjustment (%) and a total value of contract who have to come from "Contracts_Value".

Second Cube CONTRACTS_VALUE
ALL.D.YEAR
ALL.D.MONTH
ALL.D.ACCOUNT
ALL.M.VALUE: with a input value of contract($) and a picklist cell who contains the CONTRACTs dimension.

My problem is, I can't do the DB to get the value of CONTRACT_VALUE to CONTRACT_ADJUSTMENT (because the picklist is not a dimension). Can some one help me?
Thats what I am trying:
['value'] = N:
IF (DB('CONTRACTS_VALUE',
!ALL.D.YEAR,
!ALL.D.MONTH,
'Total Accounts',
'Contract') @= !ALL.D.CONTRACT,
DB('CONTRACTS_VALUE',
!ALL.D.YEAR,
!ALL.D.MONTH
'Total Accounts',
'Value'), 0);

But the IF condition returns false all time (maybe because the contract name is in the level 0 of the ALL.D.ACCOUNT, and I am using the 'Total Accounts').
Anyone know a better way to do that?

[jim wood]

Firstly, I'm not sure what a picklist has to do with it? Also which cube are doing the DB in. I think I got it, but it's not clear. If you're pulling data from cube to another (important it's a pull not a push) you have to specify an element within each dimension where there is no commonality. Hence why I'm wondering what the picklist to do with it.

[manoel.ss]

Helo Jim, thank for try help!

About the picklist, my two cubes work that way:
The Buyer will put in the cube CONTRACTS_ADJUSTMENT the contracts and the adjusments (%) who should be used by the operational areas.

The operation area will use the cube CONTRACTS_VALUE to project the expenses of his operation ($), and to relation the expense with the contract, he will select the contract in a picklist (thats how the cube already are done).

Now I have the demand to show to the buyer the total money wich is tied at his contract. So I created an element in the CONTRACTS_ADJUSTMENT where I want to do a DB to get the values of the CONTRACTS_VALUE.

Sorry my bad english, I am Brazilian learning Engish.

[TM1inChickcago]

You can try using Performance Modeler to create a [accumulation link] Link between the two cubes. The link will create the rule(s) and corresponding feeders. Prior to doing this ensure you save your existing rules in a text file so that you can copy them back if needed as Performance Modeler has been know to sporadically overwrite manual rules. Depending on the size of your cubes, Performance Modeler could potentially generate thousands of lines for feeders. In general Performance Modeler is a bit quirky.

If you don't want to use PM, create a third cube via Architect - year, month, account, contract (the picklist dimension), and whatever measures/value elements are necessary (Users do need to see this cube)

Create a rule in this cube from the Contracts_Value using the picklist value to match the correct contract element.

Create a rule in the Contracts_Adjustments from the new cube - contracts will map correctly and you can select Total Accounts.

[manoel.ss]

Hello TM1inChickcago thank for reply.

Realy I think the better way will be creat a new cube to do the report.

I will study the Performance Modeler too, by now I just know use to create a simple Contributors.

I conclude that, the reason why my rule this not work is because I was using the consolidate level of ACOUNTS, but the contract picklist are in the level 0 only.

Thanks.