Sunday, 18 November 2012

Code to retrieve dimension value

The following code snippet will return a specific financial dimension value attached to a record. It can be used for example to retrieve the cost center attached to a customer.

DimensionValue getDimensionValue(RefRecID dimensionSetRecID,Name attributeName) 
{
    
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttribute;
    
    dimAttrValueSet = DimensionAttributeValueSet::find(dimensionSetRecID);
    
    select dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet      == dimAttrValueSet.RecId
    join dimAttrValue    
        where   dimAttrValue.RecId                                  == dimAttrValueSetItem.DimensionAttributeValue
    join dimAttribute        
        where   dimAttribute.RecId                                  == dimAttrValue.DimensionAttribute
        &&      dimAttribute.Name                                   == attributeName;
    
    return dimAttrValue.getValue();        
}  


And to retrieve the default cost center attached to a customer:

info(getDimensionValue(CustTable::find('1101').DefaultDimension,'CostCenter'));

This code shows the general table relations, but you're better off using the DimensionStorage class/API to read and update individual dimension values. A good example can be seen in the responses to this StackOverflow topic: http://stackoverflow.com/questions/8984449/how-to-set-a-single-dimension-value-in-ax-2012


The following code shows how to check all customers with a specific cost center value:

static void ScanRecordsByDimensionValue(Args _args)
{

    // Check all customers that have a specific value for cost centre.    
    
    Name                            attrName = 'CostCenter';
    DimensionValue                  dimValue = 'OU_3566';
    
    CustTable                       custTable;

    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttribute;
    ;

    dimAttribute    = DimensionAttribute::findByName('CostCenter');
    dimAttrValueSet = DimensionAttributeValueSet::find(custTable.DefaultDimension);

    while select custTable
    join dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet      == custTable.DefaultDimension
        &&      dimAttrValueSetItem.DisplayValue                    == dimValue
    join dimAttrValue
        where   dimAttrValue.RecId                                  == dimAttrValueSetItem.DimensionAttributeValue
        &&      dimAttrValue.DimensionAttribute                     == dimAttribute.RecId
    {        
        info(custTable.AccountNum);                       
    }   

}

Sunday, 11 November 2012

SSRS Report example using Data Provider framework


This post will walk through a simple but relatively real-world report example in Ax 2012. In a previous post I covered how to build a report based on an Ax query. That's definitely simpler and less code-intensive, but isn't an option when you need more complicated data aggregation or filtering.

The things that I'll touch on include:
  • Creating a Data Provider and supporting objects in X++
  • Using a report contract class to add extended parameters
  • Creating the report in Visual Studio
  • Adding basic layout and grouping
  • Deploying the report and running it from within Ax

We'll work on the following requirement:
Create a report that shows the balance, last invoice number, and last invoice date of all vendors. The report should be grouped by vendor group, and each vendor group should start on a new page. The user should be able to filter by vendor account and vendor group. They should also have an option to specify the 'to date' for vendor transactions, and a flag to exclude vendors that have had no transactions registered against them.

This is something you'd reasonably expect to come across during an implementation, giving us something that looks like:



First, define a temporary table that will act as a container for the report data.


This contains the field mentioned in the spec, as well as a few others that make sense. Note that we include the vendor group ID and name that we intend to use for grouping / page breaks. We also need the query that defines the primary structure of the report.


There's nothing complicated about it - We just add VendTable as the primary table, and join to the vendor group in case the user wants to filter by group name. Note that for this example we have not defined any sorting, grouping, or aggregation in the query itself, as we'll do that in the report.

VendBalanceReportDP (DataProvider class)


First, the class declaration. Key points here are that we extend SRSReportDataProviderBase, and we make use of two attributes:
  • SRSReportQueryAttribute. This defines the primary query for the report. 
  • SRSReportParameterAttribute. This determines the 'contract' class that defines additional parameters. More info on this below.


[SRSReportQueryAttribute(queryStr(VendBalanceReport)),
 SRSReportParameterAttribute(classStr(VendBalanceReportContract))]

class VendBalanceReportDP extends SRSReportDataProviderBase
{

    VendBalanceReportTmp    reportLine;

}


The data provider needs at least one method that uses the SRSReportDataSetAttribute attribute. This tells SSRS that the method is one that returns a temporary table buffer that can  be used as a Dataset inside the report. When you add the dataset in the report designer, this method will be exposed (based on the table name).

[SRSReportDataSetAttribute(tableStr(VendBalanceReportTmp))]
public VendBalanceReportTmp reportLines()
{
    select reportLine;
    return reportLine;
}


The processReport method is where the bulk of the processing takes place. The general approach is that you'll run the query returned by this.parmQuery( ), and use the results to populate the temporary table buffer. (In this example, the 'reportLine' buffer defined in the class declaration). Check the comments for extra information on what's happening.

public void processReport()
{
    QueryRun                    qr;
    VendTable                   vendTable;
    VendGroup                   vendGroup;
    VendTrans                   vendTrans;

    // Get a reference to the contract for this report. The relevant contract class is      
    // defined by the SRSReportParameterAttribute used in the class declaration.
    VendBalanceReportContract   contract    = this.parmDataContract() as VendBalanceReportContract;
    ToDate                      toDate      = contract.parmToDate();
    boolean                     addLine;

    // If 'toDate' has not been specified (ie left blank), then assume today's date.        
    if(!toDate)
        toDate = systemDateGet();

    // Run the query        
    qr = new QueryRun(this.parmQuery());
    while(qr.next())
    {
        if(qr.changed(tableNum(vendTable)))
        {
            vendTable   = qr.get(tableNum(vendTable));
            vendGroup   = qr.get(tableNum(VendGroup));

            // Setup up a new report line            
            reportLine.clear();
            reportLine.VendAccount      = vendTable.AccountNum;
            reportLine.VendName         = vendTable.name();
            reportLine.VendGroupId      = vendTable.VendGroup;
            reportLine.GroupNAme        = vendGroup.Name;
            reportLine.Balance          = vendTable.balancePerDate(toDate);

            // Select the last invoice transaction for the current vendor                            
            select firstonly vendTrans
                order by TransDate desc
                where   vendTrans.AccountNum    == vendTable.AccountNum
                &&      vendTrans.TransType     == LedgerTransType::Purch;

            reportLine.LastInvoiceDate  = vendTrans.TransDate;
            reportLine.LastInvoiceId    = vendTrans.Invoice;

            // Check final conditions for displaying the line
            addLine = true;

            // Check the 'includeVendorsWithoutTransactions' flag - if it has not been set,                        
            // and there is no invoice transaction against the vendor, then don't display this line.
            if(!contract.parmIncludeVendorsWithoutTransactions() && vendTrans.RecId == 0)
                addLine = false;

            if(addLine)
                reportLine.insert();
        }

    }

}

Note that we don't return anything here. The reportLine buffer is being populated, and will be returned in the 'reportLines' method mentioned above. The processReport method is probably the closest analogy to the old 'fetch' method pre-Ax2012, with the main difference being that instead of 'sending' the records immediately, they're buffered into the temporary table.


VendBalanceReportContract (Report contract class for additional parameters)

The contract class is attached to the data provider (via attribute SRSReportParameterAttribute in the data provider class declaration), and allows us to define additional parameters that don't fit the primary query structure. The class declaration for the contract class should define attribute DataContractAttribute. We also define variables to hold the parameter values. In this example, we have two.

[DataContractAttribute]
class VendBalanceReportContract
{
    ToDate          toDate;
    NoYesId         includeVendorsWithoutTransactions;
}

For each parameter, we need a parm accessor method that sets/gets the value of the corresponding variable. Each must be tagged with at least DataMemberAttribute, as well as others as shown below.

[DataMemberAttribute("ToDate"),
 SysOperationLabelAttribute("Transaction 'to' date"),
 SysOperationHelpTextAttribute("Date up to which transaction should be considered"),
 SysOperationDisplayOrderAttribute('1')]
public ToDate parmToDate(ToDate _toDate = toDate)
{
    toDate = _toDate;
    return toDate;
}

Another parm method for includeVendorsWithoutTransactions:

[DataMemberAttribute("IncludeVendorsWithoutTransactions"),
 SysOperationLabelAttribute("Include vendors without transactions"),
 SysOperationHelpTextAttribute("Whether to include vendors that have no transactions against them"),
 SysOperationDisplayOrderAttribute('2')]
public NoYesId parmIncludeVendorsWithoutTransactions(NoYesId _includeVendorsWithoutTransactions = includeVendorsWithoutTransactions)
{
    includeVendorsWithoutTransactions = _includeVendorsWithoutTransactions;
    return includeVendorsWithoutTransactions;
}


Creating the report

OK now that we've got the X++ and AOT elements needed, the next step is building the report. I won't go into too much detail but the main steps are:
  • Open Visual Studio
  • Create a new Ax report model project, named VendBalanceReport
  • Add a new Ax report to the project, named VendBalanceReport
  • Right-click the project and 'Add to AOT'

All going well, this will connect to Ax and create an AOT entry called VendBalanceReport under Visual Studio Projects / Dynamics Ax Model Projects. Now add the dataset.
  • Create a Dataset in the report, named VendBalanceReportDS. In the properties, set the data source type to Report Data Provider, and select the dropdown under Query. This will let you select the VendBalanceReportDP class we defined earlier. NB this step scans the AOT for all classes that derive SRSReportDataProviderBase.
  • Once that wizard closes (make sure all fields are selected), you'll see several parameters added to the dataset. The ones underscored with Ax_ are system parameters that generally remain hidden, the VendBalanceReportDP_DynamicParameter is a place-holder for all ranges defined in the VendBalanceReport query, and you'll see two for the parameters defined in the contract class. If you're missing those, make sure that you've defined all of the attributes mentioned in the previous steps.

Now to add the design:
  • Right-click 'Designs' and add a new auto design. Set the LayoutTemplate to ReportLayoutStyleTemplate, and the title to Vendor Balance Report.
  • Under the new design, right-click and add a new list. Set the Style template to ListStyleTemplate.
  • Create a new entry under 'Groupings' called 'VendGroup'. Set "Page Break at End" to true, and drag field VendGroup from the dataset fields to the 'Group on' element.
  • Add VendGroupID and GroupName to the 'Header' section of the group.
  • Drag fields VendAccount, VendName, LastInvoiceId, LastInvoiceDate, and Balance from the dataset to the 'Data' section of the list.
Build the solution. NB part of the build step saves the changes back to the AOT.

You should end up with something that looks like this (click to expand):



OK, so now we're ready to deploy the report to SSRS. There are a couple of ways of doing this, and your mileage may vary:

  1. Find the report element in the AOT under SSRS Reports / Reports. Right-click VendBalanceReport and 'Deploy'.
  2. My preferred way is to open an Ax powershell console (Start -> Administrative Tools -> Microsoft Dynamics Ax 2012 Management Console). Type: publish-axreport -reportname VendBalanceReport. The reason I prefer doing it this way is because after a change I can quickly switch to the management console, hit up for the last command, then enter to deploy. 
If all goes to plan, we'll have the report definition project in Visual Studio, which has been synchronised with the Ax AOT, and also deployed from the AOT to the reporting server. If you've hit problems to this point, post a comment and I'll try and offer tips to get around it.

Last thing we need is a menu-item in Ax pointing to the report. Under Menu Items / Output, create a new menu-item called VendBalanceReport. Set:
  • ObjectType to SSRSReport
  • Object to VendBalanceReport
  • It should automatically pick up the first design, which in our case will be called AutoDesign1.
Now, right-click and 'Open', and you should be able to run/test the report from within Ax, and that's it for this example. It's a relatively simple report and as you can see not a small amount of work.

In my opinion, developing reports in Ax 2012 is a lot harder than it used to be, and to be honest a lot harder than it should be. The old-style MorphX reports may be considered the ugly-cousin of the reporting world, but they fit the Ax development model a lot more cleanly, and had none of the deployment/configuration problems SSRS and friends bring. [OK.. End of rant].

Running the report via code

As a last step, the following code shows how to run the report from X++ code. Here we set the output to PDF, add a filter to the query, and set the extended parameters. There's another post about running reports here.

private void generateVendBalanceReport()
{
    SrsReportRunController      reportRunController;
    Map                         queryContracts;
    MapEnumerator               mapEnum;
    Query                       query;
    QueryBuildRange             range;

    SrsReportDataContract       contract;
    VendBalanceReportContract   rdpContract;
    FilePath                    outputPath = "c:\\";
    ;

    // Create the report run controller
    reportRunController = new SrsReportRunController();
    reportRunController.parmReportName('VendBalanceReport.AutoDesign1');
    reportRunController.parmLoadFromSysLastValue(false);

    // Set printer settings (print to file, format, filename, etc).
    contract    = reportRunController.parmReportContract();
    contract.parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    contract.parmPrintSettings().overwriteFile(true);
    contract.parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    contract.parmPrintSettings().fileName(outputPath + "VendBalanceReport.pdf");
    
    // Use our custom-defined report contract class
    rdpContract = contract.parmRdpContract() as VendBalanceReportContract;
    rdpContract.parmToDate(systemDateGet());
    rdpContract.parmIncludeVendorsWithoutTransactions(true);    
    
    // Add a range to the query (filter vendors that begin with '3').
    queryContracts = contract.parmQueryContracts();
    mapEnum = queryContracts.getEnumerator();
    while(mapEnum.moveNext())
    {
        // Get the query and update the datasource as required
        query = mapEnum.currentValue();
        range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendTable)),fieldNum(VendTable,AccountNum));
        range.value('3*');
    }

    // Run the report
    reportRunController.runReport();
}


That's it for this post. Feel free to post questions, comments, or corrections..

Saturday, 28 April 2012

Utility application - Remove duplicate files within directory (C#)


The following is a small (non Ax-related) utility application that will scan a directory and it's sub-directories for duplicates. Any duplicates are removed, and as a final step empty paths are also deleted.

I wrote this to clean-up my Pictures folder where copies had been accidentally moved around.

Additional comments in the code. To build, copy the source into a C# Console-Application.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Collections;

namespace CheckDuplicateFiles
{
    /// <summary>
    /// This program will check for duplicate files within the path specified
    /// (as the first command-line argument). It will remove any duplicates, and 
    /// then finally delete any paths that contain no files within it or it's
    /// child directories.
    /// 
    /// The check to determine a duplicate file is based on the file length,
    /// and a hash of the first 1024 bytes of the file data.
    /// Error checking is minimal.
    /// 
    /// </summary>
    class Program
    {        
        static void Main(string[] args)
        {
            new Program().Run(args[0]);
        }

        Dictionary<string, List<string>> Entries = new Dictionary<string, List<string>>();


        public void Run(string rootPath)
        {
            // Map all files
            this.CheckDirectory(rootPath);

            // Delete duplicates
            int filesDeleted = 0;
            foreach (KeyValuePair<string, List<string>> entry in this.Entries)
            {
                List<string> files = entry.Value;
                if (files.Count > 1)
                {
                    // Duplicates exist
                    Console.WriteLine(files[0]);

                    foreach (string file in files.Skip(1))
                    {
                        File.Delete(file);
                        filesDeleted++;
                    }                    
                }
            }

            Console.WriteLine(String.Format("{0} files deleted", filesDeleted));

            // Delete empty directories (empty meaning no files in it or it's
            // sub-directories).
            this.DeleteEmptyDirs(rootPath);

        }

        /// <summary>
        /// Check whether the specified path is empty (no files in it
        /// or it's subdirectories).
        /// </summary>
        /// <param name="path"></param>
        public void DeleteEmptyDirs(string path)
        {
            if (!Directory.Exists(path))
                return; // May have already been deleted.
            if (FilesInDirAndSubDirs(path) == 0)
            {
                Console.WriteLine(String.Format("PATH DELETED: {0}", path));
                Directory.Delete(path, true);
            }
            else
            {
                foreach (string subDir in Directory.GetDirectories(path))
                    this.DeleteEmptyDirs(Path.Combine(path, subDir));
            }
        }

        /// <summary>
        /// Count files in specified path and all sub-directories (recursed).
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public int FilesInDirAndSubDirs(string path)
        {
            int total = Directory.GetFiles(path).Length;
            foreach (string subDir in Directory.GetDirectories(path))
            {
                total += FilesInDirAndSubDirs(Path.Combine(path, subDir));
            }
            return total;
        }

        /// <summary>
        /// Recursively build map of all files.
        /// </summary>
        /// <param name="path"></param>
        public void CheckDirectory(string path)
        {            
            foreach (string file in Directory.GetFiles(path))
            {
                string key = this.GetKeyForFile(file);

                List<string> array;

                if(this.Entries.ContainsKey(key))
                    array = this.Entries[key];
                else
                    array = new List<string>();

                this.Entries[key] = array;
                array.Add(file);
            }

            foreach (string subDirectory in Directory.GetDirectories(path))
            {
                this.CheckDirectory(Path.Combine(path, subDirectory));
            }
        }

        /// <summary>
        /// Build hash-key for the specified file (based on length and data hash).
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public string GetKeyForFile(string file)
        {
            using (FileStream inFile = new FileStream(file, FileMode.Open))
            {
                long fileLength = new FileInfo(file).Length;
                byte[] buf = new byte[fileLength];

                int readLength = 1024;
                if (readLength > fileLength)
                    readLength = (int)fileLength;

                inFile.Read(buf,0,readLength);
                int hashCode = this.ByteArrayHashCode(buf);

                return String.Format("{0} {1}", fileLength, hashCode);               
            }            
        }

        /// <summary>
        /// Generate hash code for specified buffer. NB byte[].GetHashCode
        /// is not suitable because it just hashes the array memory location.
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int ByteArrayHashCode(params byte[] data)
        {
            unchecked
            {
                const int p = 16777619;
                int hash = (int)2166136261;

                for (int i = 0; i < data.Length; i++)
                    hash = (hash ^ data[i]) * p;

                hash += hash << 13;
                hash ^= hash >> 7;
                hash += hash << 3;
                hash ^= hash >> 17;
                hash += hash << 5;
                return hash;
            }
        }

    }
}

Tuesday, 6 March 2012

Editing existing help in Ax 2012

This post describes how to modify an existing help file in an Ax 2012 environment. As an example, we'll add some text to the help that appears for the Customers form.

As a general rule, the steps you'll go through to modify help are as follows:
  • Find the form in the AOT. For this example, it's CustTable.
  • Determine the help topic ID for the form. This will typically be Forms.[FormName], so for us it's Forms.CustTable. The correct way to find the help topic ID is to right-click the element in the AOT, then select AddIns -> Help properties. This help topic ID is referenced and indexed in the help files.
  • Determine the file that corresponds to the help topic ID. This isn't all that straightforward as far as I can tell, so I've created a helper document and application to assist in resolving the links (See below).
  • Edit the file in-place.
  • The help server will pick the changes up immediately. All content is served centrally so there's no need for any additional deployment to clients.
The help topic / file mappings for a standard installation can be downloaded here: Ax2012 help file mappings.
I've included a small utility application that will scan the entire help directory and build an index of the help topics and their corresponding files. This can be downloaded here. The easiest way to run this is to unzip and copy it to the C:\ of your help server, then run it. The full path to the help server directory must be supplied as an argument. To find that, open IIS, find the Ax help virtual directory, and view the "Physical path", normally in "Basic settings". The source-code is also included - To compile it, copy it into a new C# command-line project.

So for our example, do the following:
  • Find CustTable in the AOT.
  • Right-click then select Add-ins -> Help properties. Make a note of the help topic it shows, although this will generally be Forms.[FormName].
  • Open the mapping file (link above) in your browser, and search (Ctrl+F in Internet Explorer) for the help topic (Forms.CustTable).
  • Open the file and edit as necessary in a text or html editor. Note that you'll need to either log directly onto the help server, or substitute the local path for a network path. Grab a developer if you're not sure how to do that.
The changes should take effect immediately.

Note, the key thing that Ax uses to link a help topic to a file is the "Microsoft.Help.F1" meta-tag. It uses the Windows search/indexing service to build an index of all files and the help topic they reference. If for any reason you need to change the value of that meta-tag (to link it to another form for example, or if you're authoring help for a new form/object), then the change may not be picked up immediately. In this case you can stop/start the Windows search service on the help server to recheck and rebuild the index.


Additional information can be found in MSDN here.