Report with Multiple combinations in a row axis

[Former Member]

hi Team,

I have a report requirement where  I have Profit Center and Account in Row axis and Calendar year in Columns. Requirement is keep specifi Profit Center and Account combination in each row in a report. There are around 15 combinations I have to place in the report as shown below. what is the best approach ?

[Former Member · Accepted solution]

Hi Vijay,

Firstly, please try to create a new report which has the profit center and account dimensions respectively in row,

Secondly, click to the button in the picture that I have attached in the excel add-in action pane to see where the first report ends.

Finally, click the next cell where the first report ends. Then create a new report and select first reports row axis as the basis for the second report's row axis. Assign your necessary dimension to the column axis and click ok.

I guess now you will be able to open a report(second one) which is based on static dimension member selection in first report.

I hope this suggestion helps and what you are asking,

Mahir Parlakay,

[former_member186338 · Accepted solution]

Hi Mahir,

What is the idea of 2 reports? How it will help?

Vadim

[Former Member · Accepted solution]

Hi Mahir

Can you explain your solution more in detail?

If possible, please keep the screenshot of the 'output'file because I want to try the same requirement.

Thanks

Venkat

[Former Member · Accepted solution]

Mahir,

You solution will certainly work when we just few combinations in a row. But what if I have more combinations of ProfitCenter and Gl Accounts in rows. Is it not difficult to create so many queries?

Also, later I have realized that certains rows will have Profit Center and Range of GL Accounts. In such case, it is very difficult to manage things. This kind of things are very much possible in BW Query Designer using Structures in Rows.

[Former Member · Accepted solution]

Hi Vijay,

As your combinations are unique, you shall paste some sign value for instance 1 to each combination.

Then hide your combination column for the first report. In the second report you can open your new report according to the first's combinations. I will try to sample the process at the pictures below:

@Vadim: Idea of the second report is that if he needs to make combinations from these two dimensions and use it to enter a value to a new dimension in the second report. I presumed that he would require a second report because he said "combinations". Otherwise he would simply enter his values from a static selection of dimension values like a input form.

Thanks,

Mahir Parlakay,

[former_member186338 · Accepted solution]

Hi Mahir,

Still do not understand how second report will help to achieve the goal. And what for to enter some values???

The static solution works fine and on the current SP it's stable. The alternative is to exclude unwanted combinations but it's hard to maintain.

B.R. Vadim

[rajkumar_kandula]

Hi Vijay,

Another way of achieving this is to make use of the property " Exclude Members" in the report editor options.

1.  Just create a static report with Profit Center and Account in the rows.

2.  Now, go to the "Exclude Members" Tab of the report editor

3.  Add all the combinations of Profit Center and Account. So, now in the layout you will be able to see only the required combinations.

Note: This process may need more efforts if in case you want to exclude majority of the profit center and account combinations. In that case next easier option would be the one suggested by in his earlier post to make use of a property.

Hope this helps. Thanks.

Regards,
Raj

[former_member186338]

Hi Raj

What is the idea to repeat my last answer about exclude member option?

"The alternative is to exclude unwanted combinations but it's hard to maintain."?

Property will not work also - it's not one to one relationship.

Vadim

[former_member186338]

Hi Vijay,

Static rows report is one solution. Just use member recognition and type required combinations of Profit Centers and accounts.

If the relation is one to one (one Profit Center - one account) then you can define a property of Profit Center and based on this property select the required account.

B.R. Vadim

[Former Member]

The relationship is not one to one but requirement is to show only some specific combinations in the report.  I have already tried the method of defining static rows and works good for some time. Suddenly, not sure what happens the report blows up and end up having unwanted rows with all possible combinations of Profit Center and GL Accounts.

This is really strange not having a standard functionality in EPM Add in as this is pretty much possible BW query designer using Structures and it is very stable. And I kept hearing from people that this is a must needed functionality in many consolidation reports.

[former_member186338]

Hi Vijay,

BPC behavior depends on the SP level. Upgrade to the latest and you will have more or less stable report. I have a stable sheet with 7 report sharing column axis with static column axis (3 dimensions).

B.R. Vadim