We had a report from BO that we extracted out and pivoted in excel, now we are trying to do similar in BO but for some bizarre reason one of our sums wont add up to the number we get in excel. The report output would be like this:
Department Items Bought Sum of cost of Items bought
======== ========= =========================
10 22 340
20 12 214
30 65 466
The items bought is a count of items per dept and this returns fine, but the items sum gives me slightly more (one is the same value) than than what I get in excel if I extract the raw data and do a pivot. I have about 8 departments all in.
I checked a portion of the data from the original extract where I restrict by one day and the BO report restricted by one day and there are different amounts of raw data - more from BO.
The SQL is just a basic select from statement with 2 filters - 2 date prompts and restrict by departments as below:
select column A
,column B
,column C
FROM Table a inner join table b ON b.id=a.id
WHERE
(
FULLDATE BETWEEN @prompt('Enter End Date(Start):','D','End Date\ End Date',Mono,Free,Persistent,,User:0) AND @prompt('Enter End Date(End):','D','Episode Date\End Date',Mono,Free,Persistent,,User:1)
AND
Department.DEPT_DESC In ( 'Dept A','Dept B','Dept C')
)
The sum of cost of items bought is the result of the difference in the number of bits sold for the most recent checking date minus up to the previous check date. Using this value in a variable this is then summed for each department giving the total for each department for the date range entered in the filter.
the columns and tables are just a mock up as the query is just too much to rewrite and display here. The problem is with the summing and not the query itself.
If I pull the data from BO into excel and pivot it matches what I had from existing pulls, but in the BO report when I try to replicate the pivot it gives me a slightly higher sum value per dept.
In the excel pivot I have the list of departments and sum of cost of items, the items bought isn’t necessary at this stage but I can swop the sum of cost of item with the count of items sold, which gives me the number of items sold per dept.
If I take the sum of costs of item per department and the count per item sold per dept, and divide sum of cost per dept by number sold it gives me the average cost per dept. So when I do this in the BO it gives me slightly higher figures.
The number of items sold per dept are a match but the sum of cost is not when I compare the BO figures with what I have in excel.
It may be a rounding issue. Without knowing or seeing your data I would hazard that as a guess at the moment. Does the raw data match in terms of counts and costs being the same for each?
I just checked the count of rows in my BO extract for one dept and it matches the rows count from excel, the cost per items also matches each part, there are no extras or differences either.
When I extract to excel and sum it matches what I had in the original excel but as I say when in BO it gives me a bit more, roughly about 10-20 more per dept.
There is no proper measure definition, because SQL aggregation is missing from your SQL statement. Revisit your measures in the universe and make sure you have some SQL aggregation (SUM, COUNT…) in the definition (SELECT box) of your measures.
Raw Excel data as a data source, which is also pivoted in Excel and the pivot doesn’t match the BO crosstab but the underlying raw data in BO matches the underlying raw data in Excel?
No, one data source from BO universe, when I pull that data straight to excel and pivot it I get one set of figures, if I replicate that excel pivot in BO I get different figures using the same data source as I used to pivot in excel.
Again, so you used MS Excel as data source for a universe? If so ensure your universe measures are defined properly -> Using a SQL aggregate function as well as projection is IMHO a definate MUST, see: SQL Aggregates
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)