Limited fields being retrieved from BW

[Former Member]

Hi

We have designed reports in Crystal 2008 using BEx queries. There is a calculation logic defined in BEx due to which we have a huge number of Key figures out of which 34 are being displayed on the report (actuals & budgets for each month; 4 quarters and Total) Also, the report consists of two groups - Cost centre's and Cost elements; cost elements nested within Cost centre's. The report appears as below

Apr May June July Aug Sept

Finance x x x x x x

Manpower Cost x x x x x x

Travel Cost x x x x x x

Operations x x x x x x

Manpower Cost x x x x x x

Travel Cost x x x x x x

The issue that we are facing is that as we increase the number of columns, some of the rows or the cost centre's being displayed on the report automatically get suppressed. For example if in the parameter 10 cost centre's are selected, the report displays only 6; if we display all 34 key figures. However If we reduce the key figures being displayed, then all 10 cost centre's get displayed. Is this due to a limitation on the data that can be fetched from BW? Is there any patch that can be applied or any registry that can be increased so that all the required details can be displayed..Also, the performance of the report is very slow, it takes about 12 mins to open. Kindly help.

Thanks.

[IngoH · Accepted solution]

Hi,

how is this designed in the BW query right now. Is this a query with a single structure and a set of characteristics or a query with two structures ?

Ingo

[Former Member · Accepted solution]

Hi

Thanks for your reply. The query has a single structure for the months. The rows are added on the report using hierarchies.

[IngoH · Accepted solution]

ok - so you have a hierarchy in the rows and a set of keyfigures in the columns. what exactly is the issue ?

ingo

[Former Member · Accepted solution]

The issue that we are facing is that as we keep adding key figures on the report, the number of cost centre's displayed in the hierarchy reduces even though we make no other changes in the selection parameter or the record selection. For example, when we have 30 key figures, all the 10 cost centre's are displayed but the moment we add the 31st key figure, only 8 cost centre's are displayed. Kindly advise. thanks

[IngoH · Accepted solution]

Hi,

remember that the tool is asking for NON EMPTY. Do the cost centers that are then being removed have values for the 31st keyfigure ?

Ingo

[Former Member · Accepted solution]

Hi Ingo

I have checked those cost centre's for data. When I run the report selecting only those cost centre's in the parameter, keeping all the key figures on the reports, the report works perfectly and there are values in the 31st key figure as well. Its only when I select a huge group of cost centre's in the parameter, that some cost centre's do not appear on the report.

Regards,

Payal

[Former Member · Accepted solution]

Hi Ingo

We raised this issue to SAP support and after they did a Webex, it was concluded that the issue occured due to the MDX query hitting the 1Million cell restriction at the SAP BW level. As such they could not provide us with an resolution and so we have now proposed a re-designing to the client for these particular reports.

In case you could provide us with a resolution for this limitation it would be of great help for the future.

Thanks

Payal

[IngoH · Accepted solution]

ok - I wasn't considering that because you were talking about 10 cost centers and 34 keyfigures - which is far away from 1 million cells.

ingo