on 06-26-2013 12:02 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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?
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
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.
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.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.