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.
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:
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();
Here, we need to create report definition and then add columns with various settings in it.
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")); }
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); }
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; }
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; }
ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name"); column->SetVisibility(false);
ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name"); column->SetGroupBy(true);
ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Name"); column->SetColumnAggregateOption(SummaryAggregateOptions::SUM);
ColumnDefinitionNodePtr column = AddColumn(*rpt, schemaName.c_str(), L"Component", L"Length"); column->SetColumnSummaryOption(SummaryAggregateOptions::SUM);
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.