Green IT Consulting

A recent study revealed that living vegan can save up to 670 kg Co2 a year. But not only veganism saves Co2, using public transport like train instead of car also helps to save the environment. Public transport companies, like the Austrian Raiload Company (ÖBB) rely 100% on green electricity and explicity promote the Co2 reduction on their train tickets.

Co2 avoided compared to driving by car on train ticket

Therefore I collected all my train tickets from the last year (2018) and summed up Co2 reduction. Moreover, I looked up the distance in km and calculated the costs for the company I’m working at if they had to pay me for driving by car. I compared it to a mean ticket price, because the price varies on how early you buy the ticket e.g. between 9€ to 35€ for a ticket from Linz to Vienna

DateFromToCo2 Red. (kg)Distance (km)
18.1.2018LinzVienna38,1178
20.1.2018ViennaLinz38,1178
30.1.2018LinzSalzburg26,0130
31.1.2018SalzburgLinz26,0130
11.2.2018LinzZurich119,2581
15.2.2018ZurichLinz119,2581
20.2.2018LinzFrankfurt114,8541
24.2.2018FrankfurtLinz114,8541
11.4.2018Linz Vienna 38,1178
13.4.2018 Vienna Linz38,1178
1.5.2018LinzBudapest91,0434
4.5.2018BudapestLinz91,0434
12.5.2018Linz Vienna 38,1178
13.5.2018 Vienna Linz38,1178
23.5.2018Linz Vienna 38,1178
25.5.2018 Vienna Linz38,1178
1.6.2018Linz Vienna 38,1178
2.6.2018 Vienna Linz38,1178
20.6.2018Linz Vienna 38,1178
21.6.2018 Vienna Linz38,1178
23.8.2018LinzInnsbruck64,5313
24.8.2018InnsbruckLinz64,5313
16.10.2018Linz Vienna 38,1178
19.10.2018 Vienna Linz38,1178
12.11.2018 Vienna Linz38,1178
30.11.2018Linz Vienna 38,1178
1.12.2018 Vienna Linz38,1178
3.12.2018Linz Vienna 38,1178
7.12.2018 Vienna Linz38,1178
Sum:1554,9 kg7380,0 km
€ per km0,42 €3099,3 €
Mean ticket Price30 €870 €

The numbers show that just by using public transport for some business trips (not all) I was able to save a huge amount of Co2. Compared to the study, twice the amount of Co2 like living vegan 🙂 Moreover, using train instead cars save the company a lof of money: 2229€ in one year. My conclusion is that it’s not necessary to change the complete way of living to make a sustainable impact on Co2 reduction.

Connect Azure Data Lake Storage with PowerBI dataflow

PowerBI dataflow performs ETL (Extract Transform Load) workloads in the cloud. PowerBI Pro and Premium Users get dataflow storage without additional charges. However, this storage is managed by PowerBI and you cannot access it directly. Therefor BYOSA (Bring Your Own Storage Account) is support to connect you own Azure storage account with PowerBI dataflow. I’ve made a video, following the documentation, how to connect an Azure storage account with PowerBI. Please find my video youtube:

Configure Azure Data Lake storage with PowerBI dataflow

Send SSRS Report per Mail (.xpo Download)

Like requested by some folks, I’ve added the .xpo file for the SSRS Report Mailer in the source code folder: https://erpcoder.blog/source-code/

Don’t get confused, the folders name is “Dynamics 365” but the XPO is for AX 2012

Find broken reference in Excel

I recently had to find a broken reference in an Excel file, where data was copied from one version of the file to another. In the original version there was a data validation rule with a dropdown to a list of allowed base data from another excel sheet.

Drop down to choose from list of value

In a next step the original file was copied and modified to fit new requirements. Meanwhile the original file was used and data was collected in the old file.

After the new file version was approved, the data from the old version was copied (CTRL+C , CTRL+V) to the new file and the old file was removed. However, when opening the new file excel reported a broken link to the old file.

Broken reference after copying data

While this problem can easily be identified in a small excel, this can be a challenge in a huge file with lot of sheets. One way to identify the problem is to unzip the excel file (e.g. using winrar, 7zip, etc). A excel file contains many XML and other files. The worksheets for examle can be found in the xl/worksheets folder

Excel file opend in Winrar

After extracting the worksheet folder to the file system, you may use a tool like Visual Studio Code and open the folder.

Open extracted excel folder in VS Code

Simple by searching for the name of the broken reference you will find the place where to fix the problem

Broken reference in excel

Call REST Webservice with HTTP Basic Authentication from X++

Calling a web service with HTTP Basic Authentication is easy in C#. Here I’m using a REST service via HTTP GET which is secured via Basic Authentication.

C# Code

A HTTP GET webservice call using System.Net.WebRequest and System.Net.Webrespose in C#

string url = “http://yourhost.net/service”;
string user = “YourUserName”;
string pass = “YourPassWord”;

byte[] bytes = System.Text.Encoding.UTF8.GetBytes(user+”:”+ pass);
string base64  = System.Convert.ToBase64String(bytes);

WebRequest request = WebRequest.Create(url);
request.Headers.Add(“Authorization”, “Basic “+base64);
WebResponse response = request.GetResponse();
string wsResponse = new StreamReader(response.GetResponseStream())
.ReadToEnd();

Console.WriteLine(wsResponse);
Console.ReadKey();

X++ Code

Since Dynamics 365 FO does not support packages and some syntactic sugar from C# the code is more wordy.

str url = “http://yourhost.net/service”;
str user = “YourUserName”;
str pass = “YourPassword”;

System.Text.Encoding encoding = System.Text.Encoding::UTF8;
System.Byte[] bytes = encoding.GetBytes(user + “:” + pass);
System.String base64 = System.Convert::ToBase64String(bytes);

System.String headerKey = “Authorization”;
System.String headerValue = “Basic ” + base64;
System.Net.WebRequest request = System.Net.WebRequest::Create(url);       
System.Net.WebHeaderCollection headers = request.Headers;
headers.Add(headerKey,headerValue);

System.Net.WebResponse response = request.GetResponse();
System.IO.StreamReader reader = new System.IO.StreamReader(
response.GetResponseStream());
str wsResponse = reader.ReadToEnd();

info(wsResponse);

Write product configuration model attributes to BOM and ProdBOM

pctn

https://www.youtube.com/watch?v=BaHYbDQJB_A

The product configuration model in Dynamics 365 for Finance and Operations is a great tool dynamically generate production orders including bill of materials (ProdBOM) and routes. In the product configuration model, the user is free to define attributes of different types, and use these attributes for calculations and conditions within the model. However, I had to come up with a solution to write the attribute value from the production configuration wizard to the generated ProdBOM. Here is an example code how this can be done:

Data type, table and form extensions

  1. Create a new string EDT and call it ERPPCStringAttribute
  2. Create table extensions for the BOM and ProdBOM and add a new field based on the ERPPCStringAttribute EDT
  3. Create a form extension for the ProdBOM and add the new field in the grid (Tab > Overview > Grid)

Extend the PCBOMLineDetails form

This is a tricky part. In the form for the BOM line details in the product configuration model, create a section for the attribute that looks the same like all the others. Here is a screenshot from Visual Studio:

image

You need to create a group, hat contains another group, with a checkbox and group with a string edit field and a radio button. Make sure to name the elements like shown in the screenshot.  If you are not sure, compare it with the other existing groups e.g. the SubContractor. You will need to compare the properties of each element with the corresponding properties of an existing element to make it look similar. 

image

Extend PCBOMLineDetails Form UI logic

You cannot overwrite methods on UI elements in form extensions e.g. clicked() . Therefore you have to implement the logic for the UI elements in a separate class. Create a new class ERPPCBomLineDetailsEventHandler and implement the following UI logic for clicked(), modified() and lookup()

class ERPPCBomLineDetailsEventHandler
{
   
    [FormControlEventHandler(formControlStr(PCBOMLineDetails, 
                             AllocateStringAttribute),
                             FormControlEventType::Clicked)]
    public static void AllocateAttributeString1_OnClicked(FormControl sender,
                                                     FormControlEventArgs e)
    {
        FormCheckBoxControl chx = sender;

        FormControl allocationGroup_AttributeString1 =
                sender.formRun().design().controlName(formControlStr(
                                             PCBOMLineDetails,
                                             allocationGroup_StringAttribute)
                                                       );
        FormControl attributeString1_Allocation =
                 sender.formRun().design().controlName(formControlStr(
                                             PCBOMLineDetails,
                                             StringAttribute_Allocation)
                                                       );

        PCModelingLibrary::templateSetEnabledStatus(
                                            (chx.value() == NoYes::Yes), 
                                            allocationGroup_AttributeString1,
                                            attributeString1_Allocation
                                                   );
    }

    [FormControlEventHandler(formControlStr(PCBOMLineDetails, 
                             StringAttribute_Allocation),
                             FormControlEventType::Modified)]
    public static void AttributeString1_Allocation_OnModified(
                                                      FormControl sender,
                                                      FormControlEventArgs e)
    {
        FormRadioControl radio = sender;
        FormStringControl attributeString1 =
                      sender.formRun().design().controlName(formControlStr(
                                                            PCBOMLineDetails,
                                                            StringAttribute)
                                                             );

        str label = attributeString1.labelText();

        attributeString1.text(”);

        if (radio.selection() == PCAllocation::Value)
        {
            attributeString1.extendedDataType(
                                        extendedTypeNum(ERPPCStringAttribute)
                                              );
        }
        else
        {
            attributeString1.extendedDataType(
                                        extendedTypeNum(ERPPCStringAttribute)
                                              );
            attributeString1.label(label);
        }
    }

     
    [FormControlEventHandler(formControlStr(PCBOMLineDetails, 
                             StringAttribute),
                             FormControlEventType::Lookup)]
    public static void AttributeString1_OnLookup(FormControl sender,
                                                 FormControlEventArgs e)
    {
        Object PCBOMLineDetails = sender.formRun();
        PCClass component = PCBOMLineDetails.component();
        FormRadioControl radio = 
           
sender.formRun().design().controlName(formControlStr(
                                                  PCBOMLineDetails,
                                                  StringAttribute_Allocation)
                                                 
);

        if(radio.selection() == PCAllocation::Attribute)
        {
            PCModelingLibrary::attributeLookup(sender, component);
        }       
    }

}

Extend the PCBOMLineDetails class-behind

Like many forms in Dynamics 365 for Finance and Operations the form has a class-behind that implements the business logic. You need to extend this class in order to deal with the newly created  Attribute group. Create a new class ERPPCBomLineDetails_Extension and impelement the following logic:

[ExtensionOf(formStr(PCBOMLineDetails))]
final class ERPPCBomLineDetails_Extension
{
    public PCClass component()
    {
        return component;
    }

    [ExtensionOf(FormMethodStr(PCBOMLineDetails,loadAllocations))]
    public void loadAllocations()
    {
        PCTemplateAttributeBinding      templateAttributeBinding;
        PCTemplateAttribute             fieldReference;


        PCTemplateAttributeBinding    findBindingByIDs(TableId _tableId, 
                                                       FieldId _fieldId)
        {
            PCTemplateAttributeBinding binding;

            fieldReference =
                 templateFind.findTemplateAttributeByTableIdAndFieldId(
                                                                _tableId,
                                                                _fieldId
                                                                );
           
            select  firstonly binding
            where   binding.TemplateAttribute == fieldReference.RecId
            &&      binding.TemplateComponent == templateComponent.RecId;

            return binding;
        }

        next loadAllocations();

        templateAttributeBinding = findBindingByIDs(tableNum(BOM), 
                                                    fieldNum(BOM,
                                                    ERPPCStringAttribute));


        PCModelingLibrary::templateLoadStringAllocation(component,
                                            templateAttributeBinding,
                                            StringAttribute,
                                            AllocateStringAttribute,
                                            StringAttribute_Allocation,
                                            AllocationGroup_StringAttribute);


    }

    [ExtensionOf(FormMethodStr(PCBOMLineDetails,updateRadioControls))]
    public void updateRadioControls()
    {
        next updateRadioControls();

        PCModelingLibrary::templateSetEnabledStatus(
                            (AllocateStringAttribute.value() == NoYes::Yes), 
                            AllocationGroup_StringAttribute,
                            StringAttribute_Allocation);

    }

    [ExtensionOf(FormMethodStr(PCBOMLineDetails,writeAllocations))]
    public void writeAllocations()
    {
        PCTemplateAttribute             fieldReference;

        next writeAllocations();

        if ((AllocateStringAttribute.value() == NoYes::Yes))
        {
            fieldReference = 
                   templateFind.findTemplateAttributeByTableIdAndFieldId(
                                      tableNum(BOM),
                                      fieldNum(BOM, ERPPCStringAttribute));


            PCModelingLibrary::templateSaveStringAllocation(component, 
                                               templateComponent,
                                               fieldReference,
                                               StringAttribute,
                                               StringAttribute_Allocation);
        }
    }

}

Extend the product configuration model framework

Implement the following classes to extend the product configuration model framework in Dynamics 365 Finance and Operations:

PcAdaptorBOMLine class:

[ExtensionOf(classStr(PcAdaptorBOMLine))]
final class ERPPcAdaptorBOMLine_Extension
{
    public ERPPCStringAttribute parmWANPCStringAttribute(
            ERPPCStringAttribute _stringAttribute = bom.ERPPCStringAttribute)
    {
        EcoResTextValue value;

        value.TextValue = _stringAttribute;
        this.fieldAssignment(bom.TableId,
                             fieldnum(BOM, ERPPCStringAttribute),
                             value);

        bom.ERPPCStringAttribute = _stringAttribute;                   
        return bom.ERPPCStringAttribute;
    }

}

PCGenerateBOMLine

[ExtensionOf(classStr(PCGenerateBOMLine))]
final class ERPPCGenerateBOMLine_Extension
{
    protected void setupBOM(
        BOMId       _bomId,
        InventDim   _inventDim,
        InventTable _inventTable,
        boolean     _isProduction
    )
    {
        next setupBom(_bomId,_inventDim,_inventTable,_isProduction);

        bom.ERPPCStringAttribute = templateFind.getBindingValueAsString(
                                       tableNum(BOM),
                                       fieldNum(BOM,ERPPCStringAttribute));
    }

}

Re-Initialize the framework template records

By default the framework does not recognize to create template records for the newly added attribute field. Therefore you have to delete the existing templates and trigger the framework to reinitialize. Be aware, this might harm your existing models!

PCTemplateInitialize class:

[ExtensionOf(classStr(PcTemplateInitialize))]
final class ERPPCTemplateInitialize_Extension
{
public static void main(Args _args)
{
PCTemplate tableTemplate;
PcTemplateInitialize init = PcTemplateInitialize::construct();
delete_from tableTemplate;
init.run();
}
protected void createTemplatesForBOM()
{
next createTemplatesForBom();
PCTemplate tableTemplate;
select firstonly tableTemplate where
tableTemplate.ReferencedTableId == tableNum(BOM);
this.createFieldTemplate(tableTemplate,fieldNum(BOM,
ERPPCStringAttribute ));
}
}

Start this class from Class Runner or add a menu item to call it by hand.

Test your Implementation

  1. Create a new product configuration model
  2. Add a new attribute “notes” of type string
  3. Add a BOM line to the model
  4. Open the BOM line details and assign the notes attribute to the string attribute 1
    image
  5. Save the model
  6. Create and approve the model version
  7. Create a new production order
  8. Use the configuration wizard to provide a text value for the notes attribute
    image
  9. Create the production order
  10. Verify that you can see the attribute value in the ProdBOM of your production order
    image

Send SSRS Report as Email from Report Viewer in Dynamics 365 FO

A very common task required by Dynamics 365 Finance and Operations clients is to send a report directly from the report viewer. This can be achieved with a view lines of Code. Here is video on Youtube how it works:

image
https://www.youtube.com/watch?v=0yQ8wvzzJSg

To achieve this create an extension of the SRSReportViewerForm, add a “Send Email” button. Create a OnClicked Event Handler in a Class.

[FormControlEventHandler(formControlStr(SrsReportViewerForm,
FormButtonControl1), FormControlEventType::Clicked)]
public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e)
{
SrsReportViewerControl ct = sender.formRun().design(0).controlName('ReportViewerControl');
object viewerForm = ct.formRun();
SrsReportRunController ctr= viewerForm.controller();
Microsoft.Dynamics.AX.Framework.Reporting.Shared.ReportingService.ParameterValue[] parameterValueArray;
Map reportParametersMap;

SRSReportRunService srsReportRunService = new SrsReportRunService();
srsReportRunService.getReportDataContract(ctr.parmreportcontract().parmReportName());
srsReportRunService.preRunReport(ctr.parmreportcontract());
reportParametersMap = srsReportRunService.createParamMapFromContract(ctr.parmReportContract());
parameterValueArray = SrsReportRunUtil::getParameterValueArray(reportParametersMap);str fileName = ctr.parmReportName()+ ".pdf";

SRSPrintDestinationSettings settings;
settings = ctr.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::File);
settings.fileName(fileName);
settings.fileFormat(SRSReportFileFormat::PDF);
SRSProxy proxy = SRSProxy::constructWithConfiguration(SRSConfiguration::getDefaultServerConfiguration());

System.Byte[] reportBytes = proxy.renderReportToByteArray(ctr.parmreportcontract().parmreportpath(),
parameterValueArray,settings.fileFormat(),settings.deviceinfo());

if(reportBytes.Length > 0)
{
SysMailerMessageBuilder messageBuilder = new SysMailerMessageBuilder();
messageBuilder.setSubject(fileName);
messageBuilder.addAttachment(new System.IO.MemoryStream(reportBytes),fileName);
SysMailerFactory::sendInteractive(messageBuilder.getMessage());
}
}