Skip to Content
0
Former Member
Aug 05, 2016 at 05:01 PM

Webi sums adding EVERY measure value instead of grouping

3274 Views

I am working on a Webi document that has two tabs: one for the detail, and one for the summary. The data on the detail looks good. But on the summary tab, instead of summing the measures for each dimension it is summing the entire list of measures and putting that value with each dimension.

I do not have access to make any changes in the Universe.

My version of Webi: SAP BusinessObjects BI Platform 4.2 SP 2, version/build 14.2.2.1975

I've been searching online for a few hours and haven't found anything applicable, and I've tried all of the methods I can think of to solve this but without success.


This document has two queries:

Query1
This query returns stores, the vendors that have delivered products to those stores, and what products they've delivered.

All of the values coming back from this query are dimensions (as displayed in the "Available Objects" pane in the report designer).

StoreNum StoreName VendorNum ProductNum 1234 Store 1234 1111 P1 1234 Store 1234 2222 P2 1234 Store 1234 3333 P3 1234 Store 1234 4444 5678 Store 5678 5555 P4 5678 Store 5678 6666 P5 5678 Store 5678 7777 P6


Query2
This query returns specific product information for the products listed in the first query.

"VendorNum" and "ProductNum" are dimensions, "ProductCost" is a measure (as displayed in the "Available Objects" pane in the report designer).

VendorNum ProductNum ProductCost 1111 P1 49.55 3333 P3 75.00 5555 P4 10.00 6666 P5 9.82

There are two conditions which exist which make things a little odd, but seemed to be no problem in the detail report:
1. Sometimes the ProductNum is blank in the first query
2. Sometimes the ProductNum is does not exist in the second query

These are both reflected in the detail report and the users will be specifically looking for those cases to go back and clean things up. But being that they show up in the Detail report just fine I don't believe that they hinder what I'm trying to accomplish.


In the report designer I have merged the following dimensions:
VendorNum
ProductNum

My detail report looks as expected:

StoreNum StoreName VendorNum ProductNum ProductCost 1234 Store 1234 1111 P1 49.55 1234 Store 1234 2222 P2 1234 Store 1234 3333 P3 75.00 1234 Store 1234 4444 5678 Store 5678 5555 P4 10.00 5678 Store 5678 6666 P5 9.82 5678 Store 5678 7777 P6


The summary report should show the store name and the sum of the product costs.


My goal is for the summary report to look like this:

StoreName ProductCost Store 1234 124.55 Store 5678 19.82


but it looks like this:

StoreName ProductCost Store 1234 144.37 Store 5678 144.37


In the "ProductCost" column, I have tried the following formulas, both with ProductNum dimensions merged and unmerged, with the following results:

Merged? Formula Result Yes ProductCost Same Yes Sum(ProductCost) Same Yes Sum(ProductCost) ForEach(StoreName) Same Yes Sum(ProductCost) Where(Query1.ProductNum = Query2.ProductNum) all values blank Yes Sum(ProductCost) ForEach(StoreName) Where(Query1.ProductNum = Query2.ProductNum) all values blank No ProductCost Same No Sum(ProductCost) Same No Sum(ProductCost) ForEach(StoreName) Same No Sum(ProductCost) Where(Query1.ProductNum = Query2.ProductNum) #INCOMPATIBLE No Sum(ProductCost) ForEach(StoreName) Where(Query1.ProductNum = Query2.ProductNum) (formula error)

To help troubleshoot, I created a third report:

StoreName Query1.ProductNum ProductCose Store 1234 P1 49.55 Store 1234 P2 Store 1234 P3 75.00 Store 5678 P4 10.00 Store 5678 P5 9.82 Store 5678 P6

This looks correct, but as soon as I delete the "Query1.ProductNum" column, it becomes the incorrect summary I listed above.


Can anyone see what I'm doing wrong? Or can anyone suggest something else I can try?

Thanks!