Current filter:
                                You should refresh the page.
                                Support Center
                                0
                                  • Hi,

                                    I would like to calculate the totals in one column, based on a total and grand total from another column. Is this possible and how ?

                                    If you look at the screenshot, I'd like to take 228.00 / 18503.00 *100 and put that result where currently 1.60 is displayed.

                                pivotgrid2.png
                                0

                                Hello Wim,

                                The easiest way is to bind the Percentage field to the Urin column and set the SummaryDisplayType to PercentOfRow. This will produce a result shown at the screenshot. There are 12.03 % = 15652.56 / 147137.76 * 100 %.
                                11.87 % = 147137.76 / 1239855 * 100 %. Where 1239855 is a grand total of product sales for all years included into the report.

                                Thanks,
                                Ivan

                                0

                                Hi Ivan,

                                I think there is some confusion here. The percentages in the screenshot are not calculated against the other column. They are entirely different. Only the totals are related. So I should be able to change the totals without touching the values in the percentage column.

                                0

                                Hi Wim,

                                The solution will be slightly more difficult. Handle the CustomCellDisplayText event as follows:

                                private void pivotGridControl1_CustomCellDisplayText(object sender, DevExpress.XtraPivotGrid.PivotCellDisplayTextEventArgs e) {
                                     if(e.DataField == fieldPercentage && e.RowValueType == DevExpress.XtraPivotGrid.PivotGridValueType.Total) {
                                          decimal value = Convert.ToDecimal(e.GetCellValue(fieldUrin)),
                                               grandTotal = Convert.ToDecimal(e.GetRowGrandTotal(fieldUrin));
                                          e.DisplayText = string.Format("{0:f2}", value / grandTotal * 100);
                                     }
                                }

                                Here fieldPercentage and fieldUrin mean your Percentage and Urin fields.

                                Thanks,
                                Ivan

                                0

                                This indeed does the trick. There is one problem though. When I collapse a field, it again uses the summarytype (e.g. it just takes the sum). How can I also deal with this ?

                                0

                                Hello Wim,

                                Change the if case. For example, as follows:
                                if(e.DataField == fieldPercentage && (e.RowValueType == PivotGridValueType.Total || (e.RowValueType == PivotGridValueType.Value && !e.IsFieldValueExpanded(fieldType))))

                                Thanks,
                                Ivan

                                0

                                Hi Ivan,

                                The above code doesn't seem to work. It affects all cells in the column and not only the collapsed cells.

                                I've also discovered a second problem based on my previous problem, when I put the pivotgrid in print preview everything is fine, however when I then use the excel export function, I'm seeing the values before the event handler ran.

                                0

                                Hello Wim,

                                Unfortunately, I can't reproduce both of your problems. Here is a sample project that works correctly.

                                You can modify the condition of the event if you need. You can find the description of the properties of the PivotCellDisplayTextEventArgs object in our documentation:
                                PivotCellDisplayTextEventArgs

                                Thanks,
                                Ivan

                                WindowsApplication6.zip
                                0

                                Hi Ivan,

                                The first problem was my fault, was using the wrong field.

                                The excel problem however also appears in your sample application. Can you please check this again ? When you look at the values handled by the event handler, they are correct in print preview, they are strangely enough also correct when exporting to pdf. However, when you export to excel, the values appear as if the event handler never ran. I'm using excel 2003 SP3 if that would be important.

                                0

                                Hi Wim,

                                Export the PivotGrid to XLS as follows:
                                pivotGridControl1.ExportToXls("d:\\test.xls", false);
                                The last parameter makes the PivotGrid to use the display text instead of the cells' values.

                                If you don't want to export all cells as strings, set the fieldType.UseNativeFormat property to false and export the PivotGrid as usual.

                                Thanks,
                                Ivan

                                To start a chat you should create a support ticket


                                If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383

                                FOLLOW US

                                DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, along with high-performance HTML JS Mobile Frameworks for developers targeting iOS, Android and Windows Phone. Whether using WPF, Silverlight, ASP.NET, WinForms, HTML5 or Windows 8, DevExpress tools help you build and deliver your best in the shortest time possible.

                                Copyright © 1998-2014 Developer Express Inc.
                                All trademarks or registered trademarks are property of their respective owners