BEx input query with totals nexting two charactrisitcs

[tim_atkins]

Hi there,

I am trying to create an input query that combines data already loaded in the system and user input.

In columns I will have Transaction Types (opening balance, movement 1 movement 2 movement 3, closing balance)

In the rows I have 0ACCOUNTS.

The idea is that the input sheet will show all jounrals for Opening balance, Input journal ID for movements, and All journals for closing balances.

I'll need to next the transaction type characteristic and journal characteristic, but the challenge is getting the right journal value for each movement characteristic.

The easy answer is to use a structure, but that means that the columns need to be fixed. the list of movements will need to changed based on the what accounts are chosen. This will be define using a variable in the filter of the query.

Is this something that can be done, or do I need to rethink the overall design idea.

Tim

[gregor_dieckmann]

Hi Tim,

the question is whether you have an 'account model' (i.e. just one technical key figure and the rest are characteristics) or whether you have a key figure model?

Depending on this you might have to use a structure and then the number of structure elements is given in the query definition. You might used variables in restricted key figures to set the right characteristic values on the fly.

Using the account model you can create a characteristic relationship that creates the correct combinations. The you use Account in the rows and Transactions types, etc in the columns. You then can use 'access mode for result values' based on characteristic relationships to 'create' the correct combinations in the query.

Might be that also a mixture of account and key figure model is usable. But characteristics drilled-down in the columns will lead always to a cart. product with the modeled structure elements in the columns and thus you might get combinations you don't need.

Regards,

Gregor

[tim_atkins]

Hi Gregor,

Thanks for your reply. We are using an account based model. Below is a picture of what I am trying to do. The moments in yellow need to be dynamic. A variable will return a list movements based on the accounts chosen. Opening and Closing need to always be present, but looking at Total Audit trail, not Input.

The characteristic relationship almost worked, but it would return total and all children of the total in the hierarchy. It also applied to the whole cube, not just the query.

Would you be able to expand of the variable in the restricted key figure, this may be the answer where the audit trail needs to be a member based on the ZMOVE member.

Thanks so much

[gregor_dieckmann]

Hi Tim,

you have not mentioned hierarchies before and you have also not mentioned what kind of characteristic relationship you have used. If you have implemented a characteristic relationship of type exit then you have to implement also the CREATE method and there you get the query filter. Another option is to create a DSO or aDSO (release 7.50, SP04) with the valid combinations then everything should work out of the box.

Since you have no structure you have to make the Opening Total and Closing Balance Total not input-ready usind a data slice. Ensure that different users have not overlapping selections to avoid lock problems: if Opening Total and Closing Balance Total are used by different users and 0ACCOUNT selection for different users does overlap you will get a lock since data protected by data slices are not 'subtracted' from the locked data region.

Regards,

Gregor