| |
|
|
Generic Excel multilevel sorting and grouping macro class
Integra does provide the ability to define sorting and grouping either within and Integra profile or in the Quick Reports run-time dialog. When using this feature the Excel report would be activated with the data sorted and grouped according to the settings. However, there are situations where one would prefer to provide the user with a macro button to sort and group in different ways (and to remove the grouping if required) and leave export the data in its "raw" format. This is usually the case when one also wants to add Pivot tables to the Excel template attached to the Integra profile. Pivot tables do not work on grouped data and therefore grouping is only useful on a manual basis where a user can group and ungroup depending on whether the data is to be looked at grouped or using the Pivot tables.
This FAQ provides an Excel macro class module (see attached file) that provides the same sorting and grouping functionality used in Integra, which can be included in any Excel template used by Integra. To use the class one only has to add simple macros which can in turn be assigned to a button inside the Excel spreadsheet.
Note: the sorting and grouping class require the use of the Use Tags = Yes option in the Integra profile. Excel supports up to three level grouping.
Methods of IntegraSortAndGroup class
.AddGroup() - adds a level of sorting and grouping (it can be called up to 3x)
.Execute() - executes the sorting and grouping
.AddGroup parameters:
| SortBy | String, Excel tag name of the column that is to be sorted (as defined in Selected Field List of the Integra profile) |
| SortAscending | Boolean, True will sort ascending, otherwise descending |
| Group | Boolean, True will also group, otherwise sorting only |
| GroupBy | String, Excel tag name of the column that is to be grouped (usually = SortBy; as defined in Selected Field List of the Integra profile) |
| Function | String (constants), see table below |
| FunctionFor | String Array, one or more tag names (columns) on which the function is calculated on. Subtotals are shown in the columns defined here. |
| PageBreak | Boolean, True will add page break after each sub-total |
| TotalsOnTop | Boolean, True will place total rows above data, otherwise below |
Sorting and Grouping functions supported by Excel:
Average
Count
CountNums
Max
Min
Product
StDev
StDevP
Sum
Var
VarP
.Execute parameters:
ClearGrouping - Boolean, True will remove any previous grouping (recommanded)
The following shows an example macro called GroupByDomainAndServer
The following macro will sort and group the data first by domain, then by server. It will build sub-totals and a total for dbsize and show them on top of the data in the dbsize column.
Sub GroupByDomainAndServer()
Set SrtGrp = New IntegraSortAndGroup
'first level of sorting and grouping (by domain)
Call SrtGrp.AddGrp("DOMAIN", 1, True, "DOMAIN", "Sum", FncFd, True, False)
'second level of sorting and grouping (by server)
Call SrtGrp.AddGrp("SERVER", 1, True, "SERVER", "Sum", FncFd, True, False)
Call SrtGrp.Execute(True)
End Sub
Please refer to the Microsoft Excel documentation with regards to how to include (import) a class module, to create macros as shown above and how to assign them to macro buttons. |
. |
|
FAQ# 0162
|
|
|
| |