cancel
Showing results for 
Search instead for 
Did you mean: 

Display and Retrieve only Overall Results in Bex Query

Former Member
0 Kudos

Dear SAP BI members,

My project is using a BO Dashboard for reporting and source for it is BW Bex Query. However, the structure of query is such that Calendar Year/Week is present in columns which is defined with offset to display last 12 week data. And there are 5 keyfigures apart from 1 characteristic in row. The overall result row is displayed at top and calculated as "Counter of all values". The overall output of query is such that it generates lot of cells and gives error - " Result set too large (606762 cells); data retrieval restricted by configuration (maximum = 500000 cells) ". Unfortunately I cannot change the BO part and I have to solve this problem in BW query only. Another point to be noted here is that BO is using only overall result row which is configured to display at top of the report. So basically BO is interested only in top 3 rows (Calweek, keyfigure description, overall result) and rest all data is irrelevant for it. So I now need to find out a way by which I will display only overall results for the query and somehow hide rest of the row/column data. I also tried option of "Calculate single value As - Hide" for all keyfigures but it is still generating same number of empty cells and giving the same above error. So basically this option did not work for me. Is there any other way to solve this problem? Your help is very appreciated. Thanks.

-Abhijit

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

You can simply achieve this by creating a new formula which calculates overall result of location codes and hide the Location code display.

formula: SUMGT 'LocationCode'.

Br,

H

former_member182470
Active Contributor
0 Kudos

Hi Abhijit,

Harish has pointed a right approach. Give a try with that to reduce the cell consumption.

Regards,

SUman

Former Member
0 Kudos

Hi Harish/Suman,

I appreciate your help but I think I did not mention my requirement clear in original post. Let me try to describe again in short here.

I have only 1 characteristic "Location Code" in rows. I have 1 keyfigure in columns which is "Stock Quantity". Overall Result display is enabled for Location Code and it is set to display at top since BO is interested only in this yellow line and not the rest of the data. Overall result is set to calculate as - "Counter of all values". So even though keyfigure is stock quantity, overall result will return me count of stores who actually has reported stock. Thats my objective. If I remove location code from row, then obviously I lose my counter overall result. If I keep my location code in rows and hide it from display, Bex web output is still generating blank empty cells for keyfigures. Please see below screenshot of what exactly I want. I want only row number 1, 2 and 3. Sorrr but I have never used SUMGT before. Can you please tell me how exactly I can apply SUMGT for this scenario? Also location code is char so can't be used in Formula for SUMGT.

Regards

Abhijit

former_member182470
Active Contributor
0 Kudos

Hi,

I am wondering even SUMGT would not suffice your requirement.

SUMGT <Operand>

Delivers the overall results for the operands (see also Percentage Functions %GT).

Year

Region

Sales

SUMGT “Sales”

1999

North

30

180

South

30

180

Result for 1999

60

180

2000

North

60

180

South

60

180

Result for 2000

120

180

Overall Result

180

180

Have you checked with your client on the cells limit extension? Try with SUMGT and if it doesn't solve, then you need to convince your client on extension. No other go..

Regards,

Suman

Former Member
0 Kudos

Hi Abhijit,

Thanks for explaining your scenario.

I have decided upon the below solution for your requirement.

Create a formula as below.

SUMGT ( COUNT ( Location Code) )

This COUNT will assign value 1 for a non-zero location code and SUMGT will give the total sum of the count, which I believe is the desired output.

One more point to add here is that you still can hide the display of location code to compute the count.

Let me know if you need additional clarifications.

Br,

H

Former Member
0 Kudos

Hi Harish,

Sorry but I still did not understand formula solution suggested by you. Location code is a characteristic as shown in above screenshot and it is not allowing me to have characteristic in Formula window. I believe we can only work with keyfigures in this window where we define COUNT/SUMGT formula.

Anyway I have now convinced my client to change query all together and also the dashboard logic so that we can avoid this problem completely. I am thankful to all of you who replied here and showed intent to help.

-Abhijit

anshu_lilhori
Active Contributor
0 Kudos

Hi,

The solution is not going to work because SUMCT and SUMGT functions are not supported in Bobj

Read the 3rd point in the below doc.

http://scn.sap.com/docs/DOC-35444

Hope that helps.

Regards,

AL

former_member182470
Active Contributor
0 Kudos

That's what I told, its better to convince your client and get rid of this problem.

Former Member
0 Kudos

Hi Suman,

Yes after all that was required However I am surprised there is no standard SAP functionality which will just display overall results and we can hide rest of the data

Regards

Abhijit

IngoH
Active Contributor
0 Kudos

Hi Anshu,

the forumla wouldn't work for Crystal Reports, Web Intelligence, Xcelsius but it would for sure work for Analysis Office, Analysis OLAP, Design Studio

Ingo

IngoH
Active Contributor
0 Kudos

Those formulas will work in Analysis Office, Analysis OLAP and Design Studio.

Ingo

Former Member
0 Kudos

Hi Ingo,

My goal was to just have overall result rows as a query output. If you scroll through my previous 2-3 replies, you can see the table I have built which is what I want since I intend to pass only overall results to dashboard. Do you know solution by which output of query will be only result rows and hide rest of the data?

Regards

Abhijit

Prabhith
Active Contributor
0 Kudos

Hi Abhijit,

Please be informed that the functions like 'Report Result','Overall result' etc
is not visible in the BO side.

probably, you can explore the 'Outline'option(in Analysis tab)  in Webi to capture the Result values coming from bex...

Br
Prabhith

anshu_lilhori
Active Contributor
0 Kudos

Thnx for the info.

Regards,

AL

IngoH
Active Contributor
0 Kudos

Hello Abhijit,

and you have the Bex query that delivers the correct result ?

ingo

IngoH
Active Contributor
0 Kudos

Hello Prabhith

as mentioned already, that statement is incorrect.

The Analysis Suite - Analysis Office, Analysis OLAP, Design Studio is able to leverage those elements.

Web Intelligence, Crystal Reports, Dashboards using the direct Bex Query connectivity not.

regards

Ingo Hilgefort, SAP

Former Member
0 Kudos

Hi Ingo,

Please see my problem description again here. And yes I have query which is giving below results.

I appreciate your help but I think I did not mention my requirement clear in original post. Let me try to describe again in short here.

I have only 1 characteristic "Location Code" in rows. I have 1 keyfigure in columns which is "Stock Quantity". Overall Result display is enabled for Location Code and it is set to display at top since BO is interested only in this yellow line and not the rest of the data. Overall result is set to calculate as - "Counter of all values". So even though keyfigure is stock quantity, overall result will return me count of stores who actually has reported stock. Thats my objective. If I remove location code from row, then obviously I lose my counter overall result. If I keep my location code in rows and hide it from display, Bex web output is still generating blank empty cells for keyfigures. Please see below screenshot of what exactly I want. I want only row number 1, 2 and 3. Sorrr but I have never used SUMGT before. Can you please tell me how exactly I can apply SUMGT for this scenario? Also location code is char so can't be used in Formula for SUMGT.


-Abhijit

Former Member
0 Kudos

Hi Prabhith,

My Bex query itself is not giving output. It gives error - "Result set too large (606762 cells); data retrieval restricted by configuration (maximum = 500000 cells)". So I cannot explore any options in Xcelsius Dashboard side. My problem is, since query output is failing due to cell restrictions, I want to explore any setting in BW which will hide all query data and just output overall result only which is needed for Dashboard.

Regards

Abhijit

IngoH
Active Contributor
0 Kudos

Hello Abhijit,

so you have the BEx query and you are using the local calculations in the BEx query to just get the count.

correct ?

In case you are using the Local Calculation in the BEx Query then the local Calculation will only be available to the Analysis Suite (Analysis Office, Analysis OLAP, Design Studio)

regards

Ingo

anshu_lilhori
Active Contributor
0 Kudos

Hi,

Instead of local calculation make use of Exception aggregation.I do not know how much this will help but just thought of sharing.

Exception aggregation----Count of all values that are not equal to zero

Reference characteristic ----Location code.

Now try removing the location code  and see what value it returns.

Regards,

AL

Former Member
0 Kudos

Hi Ingo,

That is not correct. I have this query running in production and working fine. It has country level selection and it works fine for certain queries who does not have lot of data. It creates problem only with those country who has lot of data and output is crossing 500000 cell restriction limit in BW. As mentioned many times above, overall result in my query is set to calculate as "Counter of all values" and it configured to display at top of report. BO Dashboard just picks these top 3 rows from query output and gives perfect results without any limitations.

Regards

Abhijit

Former Member
0 Kudos

Hi Anshu,

This could be worth a try. I will check it out on Monday and see how are the results. Thanks for your inputs.

regards

Abhijit

IngoH
Active Contributor
0 Kudos

Hello Abhijit

remember that there are two connection options for DAshboards (e.g. Xcelsius). The option will leverages the OLAP Connections from the BI4 server will not show BEx queries which are using local calculation - that is the statement I made and that statement is correct.

The issue you are facing with the amount of cells is something you need to solve on the BW layer already, that is not something you can solve on the reporting layer.

regards

Ingo Hilgefort, SAP

Former Member
0 Kudos

Hi Anshu,

This worked perfectly fine with the help of exception aggregation and now effectively I get back only 1 row from query which is as good as overall result row for me. So many thanks for your solution.

Regards

Abhijit

anshu_lilhori
Active Contributor
0 Kudos

Hi,

I am really glad that it worked.Actually that local calculation word helped me in giving that solution.

Regards,

AL

Former Member
0 Kudos

Thanks Anshu for your great reply.

For me also it works fine..

Thanks,

Purushotham

anshu_lilhori
Active Contributor
0 Kudos

Elated to Know that.

Regards,

AL

Answers (2)

Answers (2)

IngoH
Active Contributor
0 Kudos

Hi Abhijit,

when you define the Bex query, you defining a view. so in case your BEx query contains elements in the rows, then those will also be transferred to the Dashboards client.

To me it looks like you have a BEx query which does return a large amount of cells, but you only want to have one row.

if that is the case, the easiest way to solve the problem is to change the BEx query to what you actually need for the dashboard, which is only the top row.

regards

Ingo Hilgefort, SAP

Former Member
0 Kudos

Hi Ingo,

Thank you for replying in this thread. I forgot to mention one point in my original post. The result row of query is actually calculating result based on count of values of characteristic in row. So if 5 location code returns value, overall result should be 5. This is the reason I am forced to have characteristic in row and thus it is producing huge output based on data. So what I am trying to achieve here is somehow without changing query I should be able to display only result row in query output and not the actual data. But unfortunately I have not found any solution for this.

Regards

Abhijit

former_member182470
Active Contributor
0 Kudos

This may give you some idea http://scn.sap.com/docs/DOC-35444

I don't think your query output size is causing the issue. I mean even after not showing the lower three rows. It is giving you the same error.

What is the query read mode used?

Former Member
0 Kudos

Query output is indeed a problem because, I have 12 cal weeks for each of 5 keyfigures and there are many values in rows. So total number of cells that get generated are 12 x 5 x 1000s . And I didnt understand what you mean by "I mean even after not showing the lower three rows. It is giving you the same error." I did try to hide single value but it is still giving empty cells in output so it is not all hidden in true sense. If you see the error above, it is specifically about Cells - "Result set too large (606762 cells); data retrieval restricted by configuration (maximum = 500000 cells)"

I will check the link you attached. Thanks.

former_member182470
Active Contributor
0 Kudos

May help you:

http://scn.sap.com/thread/943286

http://scn.sap.com/thread/1699574

You did not answer my question about the query readmode?

Former Member
0 Kudos

Links mentioned by you above regarding settings of query max cells seems useful. I am out of office as of now so unable to check query readmode. Will check tomorrow and let you know. Thanks for the useful pointers.

Former Member
0 Kudos

The link which explained how we can increase cell max restriction, solved my problem by maintaining RSADMIN table. Thanks for the links and help. Very much appreciated.

-Abhijit

former_member182470
Active Contributor
0 Kudos

That's great to hear. All the best..Thank you

Former Member
0 Kudos

Bad news. Unfortunately client has not accepted this solution of increasing cell restrictions as its against policy. So back to square one for me. Now am back to my question -- Is there any way I can display only overall results rows and hide all detail rows? I already tried option of Calculate Single Values As - Hide, for all keyfigures but it is still generating that many empty cells and hence giving same error of Result set too large. and my query read mode is - H (query to read when you navigate or expand hierarchy)

Message was edited by: ABHIJIT TEMBHEKAR

former_member182470
Active Contributor
0 Kudos

Hi,

When you run your Bex query in BEx Analyzer, you get the output like below. Whatever the result occupies the cells, that area will be considered for BO. I have just used one field in rows pane, then i just get the grand result. You have to fine tune your report by considering a field(should not be granular). This way you may reduce the cell consumption.

Former Member
0 Kudos

Hi,

Unfortunately I have only 1 characteristics in row which is mandatory but my problem is cal week with offset of last 12 weeks is used in columns and that gets multiplied for each of 6 keyfigures. So you can imagine it is resulting into huge matrix and obviously crosses default cell restriction of 500000. Unfortunately I cannot even change the way dashboard is working because that would mean changing whole lot of things. So I was hoping if I could do something with query which will display only result rows and skip all data part.

former_member182470
Active Contributor
0 Kudos

As you already have only one char in rows, and Keyfigures cannot be trimmed as per your requirement. There is no other way to reduce your cell consumption. You have to convince your client to increase the size for only this report. I don't see any other workaround is left.

IngoH
Active Contributor
0 Kudos

Hi,

but your goal is exactly what here ?

just to have the overall totals row or you want all of them ?

regards

Ingo