| |
|
DynamiCube Support
Started by danielwatts at 09-07-2010 10:57 AM. Topic has 2 replies.
  
|
|
Sort Posts:
|
|
|
|
 09-07-2010, 10:57 AM
|
danielwatts
Joined on 05-09-2005
Posts 4
|
Problem with calculated fields when using ADO and some Page-level columns
|
|
|
|
|
Hi,
I can't figure out how to configure Dynamicube to calculate a formula using sums of other columns (e.g. percentages). The problem occurs when the cube involves an ADO database and some Page-level columns.
For example, in the Dynamicube 3.0 help file there is a section named "Adding Custom Calculated Fields". If I follow the instructions, the Total Sales column is correct: Unit Price * Total Quantity for each Product.
If I then drag the ProductName column to be a Page level field, the Total Sales now shows the total of all the products' "Total Sales" values in each Category. For example, the 1st row is:
CategoryID: 1, Unit Price: $11,811.65, Total Quantity: 9532, Total Sales: $8,570,171.75.
Total Sales in this case isn't 11,811.65*9532 - instead, it is the sum of the Total Sales value for each Product in that Category.
But what if I actually wanted the calculation to actually be 11,811.65*9532? An example where this type of calculation would make sense is when you want to show a percentage for the Category, such as Net Income / Total Sales. You wouldn't want to add up all the income/sales percentages for each product in the category.
This type of calculation worked fine for us when using DCConnectType of DCCT_UNBOUND (i.e we fed in data one line at a time), but not when we use an ADO database as a source.
I assume this has something to do with the way that SQL uses a GROUP BY clause. With the unbound data we didn't do any grouping, but it was way too slow when working with large amounts of data. I tried setting QueryByPass to True, but that seems to do no totalling at all .
Is it possible to calculate formulas this way using an ADO database? If so, can someone please point me to an example, or perhaps tell me how I would change the settings in the help file's "Adding Custom Calculated Fields" section so that , in the example above, Total Sales is calculated as 11,811.65*9532?
I'm using Dynamicube 3.2.1.1177, Visual Basic 6, Windows XP 32, and the sample Northwind database from Access 2003.
Thanks,
Dan.
|
|
|
|
|
Report
|
|
|
|
|
09-07-2010, 11:58 PM
|
Andrey - DD
Joined on 08-01-2007
Russia, Novosibirsk
Posts 671
|
Re: Problem with calculated fields when using ADO and some Page-level columns
Attachment: dcCalc.zip
|
|
|
|
|
|
Dan,
The problem here is that you didn't specify groupfooter for calculated field, so its simply puts SUM of the column to calculated field total instead of multiplication of totals. When you move ProductName to Page area, all data you see in cube view are totals for each CategoryID. You need to specify calculated field totals for each Row/Column field you need.
This KB article might be helpful:
INFO: GroupFooterType Property in DynamiCube 3:
Also this info might be helpful:
INFO: Calculated Fields in DynamiCube 3.0:
Lets look at this in more details:
'Connection to database
Dim f As DynamiCubeLibCtl.Field
Dim categoryField As DynamiCubeLibCtl.Field
Dim productField As DynamiCubeLibCtl.Field
DCube1.DCConnectType = DCCT_ADO
DCube1.DCConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\NWind.mdb;Persist Security Info=False"
DCube1.DCRecordSource = "SELECT * FROM products INNER JOIN [order details] ON products.productid = [order details].productid"
'adding row fields
Set categoryField = DCube1.Fields.Add("CategoryID", "CategoryID", DCRow)
Set productField = DCube1.Fields.Add("ProductName", "ProductName", DCRow)
'adding data fields. Note: you should specify VarName of data field because we will use this VarName (not field Name) in calculated field
Set f = DCube1.Fields.Add("Quantity", "Quantity", DCData)
f.AggregateFunc = DCSum
f.VarName = "sumQuantity"
Set f = DCube1.Fields.Add("[order details.UnitPrice]", "UnitPrice", DCData)
f.AggregateFunc = DCSum
f.VarName = "sumUnitPrice"
'Now adding calculated field. We should specify field VarName too because we will use it in total lines calculations
Set f = DCube1.Fields.Add("sumQuantity*sumUnitPrice", "Total Sales", DCData)
f.Calculated = True
f.VarName = "Total"
'Now setting Row fields totals for calculated field
categoryField.GroupFooterType("Total") = DCFCalculated
categoryField.GroupFooterExpression("Total") = "groupsum(sumQuantity) * groupsum(sumUnitPrice)"
productField.GroupFooterType("Total") = DCFCalculated
productField.GroupFooterExpression("Total") = "groupsum(sumQuantity) * groupsum(sumUnitPrice)"
I've attached a sample which demonstrates this behaviour.
Thank you,
Andrey T.
|
|
|
|
|
Report
|
|
|
|
|
09-16-2010, 8:36 AM
|
danielwatts
Joined on 05-09-2005
Posts 4
|
Re: Problem with calculated fields when using ADO and some Page-level columns
|
|
|
|
|
|
Andrey,
Thanks very much - this solved the problem, even with our most complicated cube.
It hadn't occurred to me that the GroupFooterExpression setting would have an effect on cube rows that aren't subtotals.
Dan.
|
|
|
|
|
Report
|
|
|
|
|
GrapeCity » Product Support » DynamiCube Supp... » Re: Problem with calculated fields when using ADO and some Page-level columns
|
|
|
|
|