3.3 X-Function ReportTree Example

X-Function ReportTree

Summary

In this example, we will show how to do statistics on the selected data in a worksheet, and generate a report in a new hierarchy sheet. The selected data can be one column, multiple columns, or any one subrange in the worksheet.

As the output data type is ReportTree, the dialog will have a Recalculate combo box, which can set the recalculate mode of the report sheet to "Manual", "Auto Update" or "None".

You will learn

1. How to get data from the selected data rangeSelect Data Range.
2. How to do error handling in event1, before_execute and the main function.
3. How to generate report tables in a hierarchy sheet with a recalculation lockHierarchy SheetReport TableReport with Auto UpdateAuto UpdateRecalculation.

Steps

1. Hit F10 to open X-Function Builder, enter the X-Function name and variables, as in the following picture, and then click Save. .
   
2. Open this X-Function in Code Builder to edit the source code. First include the needed header file, as below.

   #include <ReportTree.h> // needed for ReportTable class

3. Add error checking code into StatsReport_event1 to check the input data range, like in the following.

   DataRange drInput;
   drInput.Create(trGetN.iy.strVal);
   
   // if input is invalid, show error message 
   // on the bottom of dialog
   // and disable OK button
   if ( !drInput.IsValid() || drInput.GetNumRanges() < 1 ) 
   {
   	strErrMsg = "Please select valid data for input";
   	bOKEnable = false;
   }

4. Add error checking code in report_stats_before_execute.

   DataRange drInput;
   drInput.Create(trGetN.iy.strVal);
   
   // if input is invalid, print out error message
   // and abort the X-Function execution
   if ( !drInput.IsValid() || drInput.GetNumRanges() < 1 ) 
   {
   	out_str("Invalid Input Data");
   	nRet = XFEVT_ABORT;
   }

5. Add a static function to check the input range after the line //put your own support static functions here

   static	bool	_check_input(const Range& iy)
   {
   	int nRanges;
   
   	if ( !iy.IsValid() )
   		return false;
   
   	nRanges = iy.GetNumData(DRR_COLUMN_INDEX |
    	                        DRR_NO_FACTORS);
   
   	if ( nRanges <= 0 )
   		return false;
   	return true;
   }

6. Add any needed macros under the static function. The macros will be used in the X-Function main function.

   // ID can be any value, but must be unique.
   #define	TABLE_ID_BEGIN		0x1000
   #define	ROW_ID_BEGIN		0x0001

7. In the X-Function main function StatsReport, add the following code to get the data from the specified data range, do statistics, and generate a report sheet. X-Function, Statistics

   if ( !_check_input(iy) )
   {
   	// if input is not valid, 
   	// show error message and 
   	// abort X-Function execution.
   	XF_THROW("Invalid input data");
   	return;
   }	
   
   //create table to show statistics summary.
   ReportTable rt = report.CreateTable("Summary",_L("Summary"), 
                                       TABLE_ID_BEGIN);
   
   //report table's column headers.
   const vector<string> vsColLabels = {
   	"N",
   	"Number of Missing",
   	"Mean",
   	"SD",
   	"SEM",
   	"Sum",
   	"Variance"
   };	
   int nRowID = ROW_ID_BEGIN;
   int nRanges = iy.GetNumData(DRR_COLUMN_INDEX |
                               DRR_NO_FACTORS);
   
   for ( int nRange = 0; nRange < nRanges; nRange++ )
   {
   	// get the subrange - one column
   	DataRange drOne;
   	iy.GetSubRange(drOne, DRR_COLUMN_INDEX |
                          DRR_NO_FACTORS, nRange);		
   	
   	// get range string, like [Book1]Sheet1!A
   	string strDataLabel;
   	drOne.GetRangeString(strDataLabel);
   
   	vector vInput;
   	drOne.GetData(&vInput, 0);		
   	if ( vInput.GetSize() == 0 )
   	{
   		// print out warning message when column is empty
   		// and then go to the next column
   		warning_msg_box(
                      strDataLabel + ", empty column found.", 
                      false, 'W');
   		continue;
   	}
   	
   	int N, Missing;
   	double dMean, dSum, dVariance, dSD, dSE;
   	int nRet = ocmath_basic_summary_stats(vInput.GetSize(), 
   			vInput,
   			&N, &dMean, &dSD, &dSE, &dVariance, &dSum,
   			NULL, NULL, NULL, NULL, NULL, NULL, &Missing);
   	if ( STATS_NO_ERROR != nRet )
   	{
   		// print out warning message when
   		// statistics function failed.
   		warning_msg_box(
                      strDataLabel + ", statistics fails.", 
                      false, 'W');
   		continue;
   	}
   	
   	vector vResults(vsColLabels.GetSize());
   	vResults[0] = N;
   	vResults[1] = Missing;
   	vResults[2] = dMean;
   	vResults[3] = dSD;
   	vResults[4] = dSE;
   	vResults[5] = dSum;
   	vResults[6] = dVariance;
   	
   	//add new row to report table.
   	string strName = "Data" + nRange;
   	rt.AddRow(strName, vResults, strDataLabel, 
   	          vsColLabels, NULL, nRowID++);	
   }

Run the X-Function

Keep one Worksheet active with some data, highlight two columns, and type StatsReport -d in the Script Window to open the X-Function dialog.

Click OK , and a new report sheet will be generated.