[MSCE U17 C++] Create Report with Calculated Column, Group By, Aggregation, Summary & Show/Hide Column feature

If you want to get customized reports while using existing report results, you can use calculated column, Aggregation, Group by and Summary to do so. For more details about feature, please check below video:

In Microstation 2023, we have introduced Calculated column, Grouping and Aggregation, Summary & Column Visibility features in Reports in order to perform various mathematical, logical operations on report results and create customized reports. We have added a new API's, to SetVisibility, SetColumnAggregateOption, SetColumnSummaryOption,SetGroupBy, CreateCalculatedColumnDefinition & SetExpression. 

SDK Sample:

This is available at: ..\examples\DgnEC\ReportCalculatedColumnExample\

This blog provides some code snips that demonstrate how to use these APIs to achieve Calculations, Group By, Aggregation, Summary and Visibility Control in Report . Follow the steps below:

  • Create Item type library with item types and properties & attach those item types to various shapes to create report out of it.
  • Generate report definition with various columns and use SetVisibility, SetColumnAggregateOption, SetColumnSummaryOption, SetGroupBy, CreateCalculatedColumnDefinition & SetExpression APIs to apply settings on columns.
  • Export result of newly created report definition to .csv file at defined MS_TMP path.

Step 1: Create Report Data

  •          Create Item type library with item types and properties. Attach those item types to various shapes to create report out of it.                                                                                                                                         
        DgnFileP dgnfilePtr = Bentley::MstnPlatform::ISessionMgr::GetActiveDgnFile();
    
        WString itemName = L"Component";
        ItemTypeLibraryPtr lib = ItemTypeLibrary::Create(L"Report Data", *dgnfilePtr);
        ItemTypeP itemSet = lib->AddItemType(itemName.c_str());
        CustomPropertyP prop1 = itemSet->AddProperty(L"Name");
        prop1 = itemSet->AddProperty(L"Length");
        prop1->SetType(CustomProperty::Type::Double);
        prop1->SetUnits(DgnECUnit::FromID(L"INCH"));
        lib->Write();
    
        //create data
        DgnModelP dgnModelP = ISessionMgr::GetActiveDgnModelP();
     
        EditElementHandle eeh;
        CreateCircle(eeh, *dgnModelP, 0, 0, 0);
        CustomItemHost host = CustomItemHost(eeh, false);
        itemSet = lib->GetItemTypeByName(itemName.c_str());
        DgnECInstancePtr item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Circle"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateCircle(eeh, *dgnModelP, 10, 0, 0);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Circle"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateShape(eeh, *dgnModelP, 10, 0, 150, 150);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Shape"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateShape(eeh, *dgnModelP, 20, 0, 150, 150);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Shape"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateShape(eeh, *dgnModelP, 30, 0, 150, 150);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Shape"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateLine(eeh, *dgnModelP, 50, 0, 200, 100);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Line"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateLine(eeh, *dgnModelP, 60, 0, 200, 100);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Line"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateLine(eeh, *dgnModelP, 50, 0, 100, 100);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Line"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateLine(eeh, *dgnModelP, 50, 0, 100, 100);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Line"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateLine(eeh, *dgnModelP, 100, 0, 80, 100);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Line"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
    
        CreateLine(eeh, *dgnModelP, 100, 0, 80, 100);
        host = CustomItemHost(eeh, false);
        item = host.ApplyCustomItem(*itemSet);
        item->SetValue(L"Name", ECValue(L"Line"));
        item->SetValue(L"Length", ECValue(10.00));
        item->WriteChanges();
     

Step 2: Generate Report Definition

            Here, we need to create report definition and then add columns with various settings in it. 

  •          Quantity Report Definition:                                                                                                                                                                                                                                                                                             
    void GenerateQuantityReportDefinition()
        {
        DgnFileP dgnfilePtr = Bentley::MstnPlatform::ISessionMgr::GetActiveDgnFile();
        ItemTypeLibraryPtr libPtr = ItemTypeLibrary::FindByName(L"Report Data", *dgnfilePtr);
    
        //create report
        WStringCR schemaName = libPtr->GetInternalName();
        ReportCategoryNodePtr cat = ReportCategoryNode::Create(L"Qauntity Reports", -1, *dgnfilePtr);
        ReportDefinitionNodePtr rpt = cat->CreateReportDefinition(L"Component Qauntity Report", -1);
        DgnECHostSpecification hostSpec;
        hostSpec.GetPrimaryClasses().push_back(SchemaNameClassNamePair(schemaName + L":" + L"Component"));
        DgnECHostSpecificationList hostSpecs;
        hostSpecs.push_back(hostSpec);
        rpt->SetHostSpecifications(hostSpecs);
    
        //add columns
        ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name");
        column->SetGroupBy(true);
    
        column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Length");
        column->SetColumnAggregateOption(SummaryAggregateOptions::SUM);
    
        column = AddCalculatedColumn(*rpt, L"Count", L"1", 0, CustomProperty::Type::Integer);
        column->SetVisibility(false);
        column->SetColumnAggregateOption(SummaryAggregateOptions::SUM);
    
        AddCalculatedColumn(*rpt, L"Quantity", L"IIf (Column[\"Name\"] = \"Line\", Column[\"Length\"], Column[\"Count\"])", 0, CustomProperty::Type::Double, DgnECUnit::FromID(L"INCH"));
        }
    
         
  •         Summary Report Definition:                                                                                                                                                                                                                                                                                            
    void GenerateSummaryReportDefinition()
        {
        DgnFileP dgnfilePtr = Bentley::MstnPlatform::ISessionMgr::GetActiveDgnFile();
        ItemTypeLibraryPtr libPtr = ItemTypeLibrary::FindByName(L"Report Data", *dgnfilePtr);
    
        //create report
        ReportCategoryNodePtr cat = ReportCategoryNode::Create(L"Summary Reports", -1, *dgnfilePtr);
        ReportDefinitionNodePtr rpt = cat->CreateReportDefinition(L"Element Count Report", -1);
    
        DgnECHostSpecification hostSpec;
        hostSpec.GetPrimaryClasses().push_back(SchemaNameClassNamePair(L"DgnElementSchema:LineElement"));
        DgnECHostSpecificationList hostSpecs;
        hostSpecs.push_back(hostSpec);
        rpt->SetHostSpecifications(hostSpecs);
    
        //add columns
        ColumnDefinitionNodePtr column = AddColumn(*rpt, L"DgnElementSchema", L"LineElement", L"ElementDescription");
    
        column = AddColumn(*rpt, L"DgnElementSchema", L"LineElement", L"TotalLength");
        column->SetGroupBy(true);
        column->SetColumnSummaryOption(SummaryAggregateOptions::SUM);
    
        column = AddCalculatedColumn(*rpt, L"Count", L"1", 0, CustomProperty::Type::Integer);
        column->SetColumnAggregateOption(SummaryAggregateOptions::SUM); 
        column->SetColumnSummaryOption(SummaryAggregateOptions::SUM);
        }

  1.         Add Column :                                                                                                                                                                                                                                                                                                                    
    ColumnDefinitionNodePtr AddColumn(ReportDefinitionNodeR rpt, WCharCP schema, WCharCP ecclass, WCharCP accessString, WCharCP name)
        {
        if (NULL == name)
            name = accessString;
    
        ColumnDefinitionNodePtr col = rpt.CreateColumnDefinition(name, -1);
        if (nullptr == col.get())
            return nullptr;
    
        ReportColumnAccessorList accessors;
        accessors.push_back(QualifiedECAccessor(schema, ecclass, accessString));
        col->SetAccessors(accessors);
        return col;
        }
     
  2.        Add Calculated Column:                                                                                                                                                                                                                                                                                                 
    ColumnDefinitionNodePtr AddCalculatedColumn(ReportDefinitionNodeR rpt, WCharCP name, WCharCP expression, WCharCP failuerValue, 
                                                CustomProperty::Type type, DgnECUnitCR unit)
        {
        CalculatedColumnDefinitionNodePtr col = rpt.CreateCalculatedColumnDefinition(name, -1);
        if (nullptr == col.get())
            return nullptr;
    
        //prepare expression accessor
        ReportColumnAccessor accessor;
        accessor.SetExpression(expression, failuerValue, (int) type, unit);
    
        ReportColumnAccessorList accessors;
        accessors.push_back(accessor);
        col->SetExpression(accessors);
        return col;
        }
      
  3.        Set Visibility to column:                                                                                                                                                                                                                                                                                                   
    ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name");
    column->SetVisibility(false);
          
  4.        Set Group By to column:                                                                                                                                                                                                                                                                                                  
    ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name");
    column->SetGroupBy(true);
     
  5.        Set Aggregation to column:                                                                                                                                                                                                                                                                                             
    ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name");
    column->SetColumnAggregateOption(SummaryAggregateOptions::SUM);
  6.       Set Summary option to column:                                                                                                                                                                                                                                                                                       
    ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Length");
    column->SetColumnSummaryOption(SummaryAggregateOptions::SUM);

Step 3: Export result to CSV

  •       Here we will try to export report results to .csv file. This report results can be placed as Table using Table API's.                                                                                                                                                            
    void ExportReportResultsToCSV()
        {
        PrintResult(L"Quantity Reports\\Component Quantity Report");
        }
        
    void PrintResult(WStringCR reportName)
        {
        DgnFileP dgnfilePtr = Bentley::MstnPlatform::ISessionMgr::GetActiveDgnFile();
        BeFileName filename(dgnfilePtr->GetFileName().c_str());
    
        WString tempFilePath;
        if (SUCCESS != ConfigurationManager::GetVariable(tempFilePath, L"MS_TMP"))
            {
            mdlOutput_messageCenter(OutputMessagePriority::TempRight, L"Please set MS_TMP variable.", NULL, OutputMessageAlert::None);
            return;
            }
    
        tempFilePath = tempFilePath.append(BeFileName::GetFileNameWithoutExtension(filename));
        tempFilePath = tempFilePath.append(L".csv");
    
    
        //Create Empty csv file
        std::wofstream csvFileStream(tempFilePath.c_str());
        if (!csvFileStream.bad())
            csvFileStream.close();
        else
            {
            WString message;
            mdlResource_loadWString(message, NULL, STRINGLISTID_ReportCalculatedColumnExampleMessages, MSG_ERROR_FILENOTCREATED);
            WString::Sprintf(message, message.c_str(), tempFilePath);
            mdlOutput_messageCenter(OutputMessagePriority::TempRight, message.c_str(), NULL, OutputMessageAlert::None);
            return;
            }
          
        BeFileStatus openStatus;
        BeTextFilePtr file = BeTextFile::Open(openStatus, tempFilePath.c_str(), TextFileOpenType::Write, TextFileOptions::None, TextFileEncoding::CurrentLocale);
        if (BeFileStatus::Success != openStatus || !file.IsValid())
            {
            WString message;
            mdlResource_loadWString(message, NULL, STRINGLISTID_ReportCalculatedColumnExampleMessages, MSG_ERROR_FILENOTOPENED);
            WString::Sprintf(message, message.c_str(), tempFilePath);
            mdlOutput_messageCenter(OutputMessagePriority::TempRight, message.c_str(), NULL, OutputMessageAlert::None);
            return;
            }
    
        //Get report definition.
        DgnModelP activeDgnModel = ISessionMgr::GetActiveDgnModelP();
        ReportDefinitionNodePtr reportNode = ReportDefinitionNode::FindByPath(reportName.c_str(), *dgnfilePtr);
        if (!reportNode.IsValid())
            {
            WString message;
            mdlResource_loadWString(message, NULL, STRINGLISTID_ReportCalculatedColumnExampleMessages, MSG_ERROR_REPORTDEFINITIONNOTFOUND);
            WString::Sprintf(message, message.c_str(), reportName);
            mdlOutput_messageCenter(OutputMessagePriority::TempRight, message.c_str(), NULL, OutputMessageAlert::None);
            return;
            }
    
        //Get Report results
        ReportResults results(*reportNode, activeDgnModel);
        results.WriteToCsv(*file);
    
        WString message = L"";
        mdlResource_loadWString(message, NULL, STRINGLISTID_ReportCalculatedColumnExampleMessages, MSG_SUCCESS_PRINTRESULT);
        WString::Sprintf(message, message.c_str(), tempFilePath);
        mdlOutput_messageCenter(OutputMessagePriority::Info, (message.c_str()), NULL, OutputMessageAlert::None);
        }

Note: SDK sample should be available in the Microstation 2023 release.