Using Multi-select lookup in Dynamics AX EP Listpage

Recently i worked on a task where we have to filter EP listpage based on multi-select lookup having enum values. I wants to share my experience in this post about how we can achieve this task generally.

To display enum in multi-select lookup is not always easy task:

To implement this scenario, i have created a new Tmp table ssyTmpHelp and add the single string field to hold enum values. Than i created new DataSet and add this Tmp table as a dataset and write following code on DS init() method:

public void init()
{
    ssyTmpHelp ssyTmpHelpLocal;
    EnumId   enumId   = enumNum(ssyHelpStatus);
    DictEnum dictEnum = new DictEnum(enumId);
    int      cnt  = dictEnum.values();
    int      counter;

    for(counter = 0; counter < cnt; counter ++)
    {      
        ssyTmpHelpLocal.clear();

        ssyTmpHelpLocal.HelpStatus = dictEnum.index2Value(counter);
        ssyTmpHelpLocal.HelpStatusDescription = dictEnum.index2Label(counter);

        ssyTmpHelpLocal.insert();

    }

    ssyTmpHelp.setTmpData(ssyTmpHelpLocal);
    ssyTmpHelp_ds.research();

    super();
}

Now create a new EP web form and add following script  to display this multi-select lookup control along with label and a text box control that will display comma (;) separated values when user select one or more values from lookup:

Place following C# code on the back end:

using System;
using System.Web.UI;
using System.Linq;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.AX.Framework.Services.Client;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Proxy = Microsoft.Dynamics.Framework.BusinessConnector.Proxy;
using ApplicationProxy = Microsoft.Dynamics.Portal.Application.Proxy;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;
using Microsoft.Dynamics.Framework.Portal;
using Microsoft.Dynamics.Portal.Application.Proxy;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Web;
using System.Globalization;

public partial class ssyHelpExternalListPageFilter: AxBaseUserControl
{  
    private static readonly string ssyHelpStatus = "ssyHelpStatus";
    private static readonly string WebParameterKey = "WP";
    private static readonly string ssyHelpExternalListPage = "ssyHelpExternalListPage";
    public String Status = string.Empty;
    private const string ValueColumnName = "Value";
    private const string PromptColumnName = "HelpStatusDescription";
    private const string RecIdColumnName = "RecId";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.AxQueryString != null && !String.IsNullOrEmpty(this.AxQueryString.WP))
        {
            if (this.Session_SelectedFilter == null)
            {
                this.Session_SelectedFilter = ssyHelpStatusMultiSelect.Text;
            }
        }
        else
        {
            if (this.Session_SelectedFilter != null)
            {
                if (!this.Page.IsPostBack)
                {
                    this.RedirectWithSelectedFilter(this.Session_SelectedFilter);
                }
            }
        }
        if (ssyHelpStatusMultiSelect.Text == String.Empty)
        {
            ssyHelpStatusMultiSelect.Text = this.Session_SelectedFilter;
        }
    }

    private const string SESSION_CustomFilter = "TSSelectedFilter";
    private string Session_SelectedFilter
    {
        get
        {
            ISession session = this.AxSession;
            if (session != null && session.CanCacheItems)
            {
                return session.GetItemFromCache(SESSION_CustomFilter) as string;
            }
            return "0";
        }
        set
        {
            ISession session = this.AxSession;
            if (session != null && session.CanCacheItems)
            {
                session.SetItemInCache(SESSION_CustomFilter, value as string);
            }
        }
    }

    private void RedirectWithSelectedFilter(string dropdownKey)
    {
        AxUrlMenuItem urlMenuItem;
     
        urlMenuItem = new AxUrlMenuItem(ssyHelpExternalListPage);
        urlMenuItem.ExtraParams.Add(WebParameterKey, dropdownKey);

        HttpContext.Current.ApplicationInstance.CompleteRequest();
        Response.Redirect(urlMenuItem.Url.OriginalString, false);
    }

    AxQueryString AxQueryString
    {
        get
        {
            return AxQueryString.GetCurrent(this.Page);
        }
    }


    #region multi-select
    protected void ssyHelpStatusMultiSelectLookupMethod(object sender, AxLookupEventArgs e)
    {      
        AxLookup lookup = sender as AxLookup;
     
        lookup.AllowMarking = true;
        lookup.AllowPaging = false;

        DataSetView datasetView = this.ssyTmpHelp.GetDataSourceView("ssyTmpHelp").DataSetView;
        Microsoft.Dynamics.AX.Framework.Portal.Data.DataSet dataset = datasetView.DataSet;
        //dataset.Init(true);
        lookup.LookupDataSet = datasetView.DataSet;
     
        AxBoundField promptField = AxBoundFieldFactory.Create(lookup.LookupDataSetViewMetadata.ViewFields[PromptColumnName]);
        lookup.Fields.Add(promptField);
        //salesUnitLookup.DataBind();

        if (!string.IsNullOrEmpty(this.ssyHelpStatusMultiSelect.Text))
        {
            string[] allSelectedValue = this.ssyHelpStatusMultiSelect.Text.Split(';');

            this.setssyHelpStatusRecIds(lookup.LookupDataSetView, allSelectedValue);
        }

        if (!string.IsNullOrEmpty(this.Status))
        {
            IEnumerable viewKeys = getViewDataRowKeys(lookup.LookupDataSetViewMetadata, lookup.LookupDataSetView);
            if (viewKeys.Count() > 0)
            {
                lookup.SetMarkedRows(viewKeys);
            }
        }
        lookup.SelectField = PromptColumnName;
     
    }
    private IEnumerable getViewDataRowKeys(DataSetViewMetadata dataSetViewMetadata, DataSetView dataSetView)
    {
        string[] selectedValues = null;
        TableMetadata tableMetadata = MetadataCache.GetTableMetadata(TableMetadata.TableNum("ssyTmpHelp"));

        selectedValues = Status.Split(';');

        List viewDataKeys = new List();

        foreach (string val in selectedValues)
        {
            Dictionary dict = new Dictionary();
            dict.Add(RecIdColumnName, val);

            List index = new List();
            index.Add(tableMetadata.DefaultUniqueIndex);

            viewDataKeys.Add((IAxViewRowKey)AxViewDataKey.CreateFromDictionary(dataSetViewMetadata, dict, index.ToArray()));
        }

        return viewDataKeys;
    }

    private void setssyHelpStatusRecIds(DataSetView dataSetView, string[] selectedColumns)
    {
        this.Status = string.Empty;
        List recIdsList = new List();

        dataSetView.MarkAllLoadedRows(true);

        // Get all the chosen recids
        foreach (DataSetViewRow row in dataSetView.GetMarkedRowsSet())
        {
            string prompt = (string)row.GetFieldValue(PromptColumnName);// Change to something else
            if (selectedColumns.Contains(prompt) && !string.IsNullOrEmpty(prompt))
            {
                Int64 rec = (Int64)row.GetFieldValue(RecIdColumnName);

                recIdsList.Add(rec.ToString());
            }
        }

        dataSetView.MarkAllLoadedRows(false);

        this.Status = this.getConcatenatedStringFromList(recIdsList);
    }

    private string getConcatenatedStringFromList(IEnumerable itemsList)
    {
        string finalString = string.Empty;
        int count = 0;

        foreach (string item in itemsList)
        {
            count++;
            finalString += item;
            if (count < itemsList.Count())
            {
                finalString += ";";
            }
        }

        return finalString;
    }
 
    protected void lookup_OkClicked(object sender, AxLookupEventArgs e)
    {
        List selectedOptionsList = new List();

        // Get all the chosen recids
        foreach (DataSetViewRow row in e.LookupControl.LookupDataSetView.GetMarkedRowsSet())
        {
            Int64 rec = (Int64)row.GetFieldValue(RecIdColumnName);
            string prompt = (string)row.GetFieldValue(PromptColumnName);// Change this to something else

            if (!string.IsNullOrEmpty(prompt))
            {
                selectedOptionsList.Add(prompt);
            }
        }

        this.ssyHelpStatusMultiSelect.Text = getConcatenatedStringFromList(selectedOptionsList);
     
        this.Session_SelectedFilter = ssyHelpStatusMultiSelect.Text;
        this.RedirectWithSelectedFilter(ssyHelpStatusMultiSelect.Text);//ssyHelpStatusMultiSelect.Text);

    }
    #endregion


}

Save above code and deploy the web form.

Next is to link this form with your EP listpage, Go to your EP list page > Edit page and add new webpart as shown below:

Now override initializeQuery() method of your listpage interaction class and add following code:

public void initializeQuery(Query _query)
{
    ssyHelpStatus           ssyHelpStatus;
    str 30        ssyHelpStatusStr;
    List          ssyHelpStatusList = new List(Types::String);
    ListEnumerator  listEnumerator;
    QueryBuildRange queryBuildRange;
    container       conRangeCriteria;
    str             range = '';

    if(this.listPage().listPageArgs().parameters())
    {      
        ssyHelpStatusStr = this.listPage().listPageArgs().parameters();
        ssyHelpStatusList = Global::strSplit(ssyHelpStatusStr, ';');

        listEnumerator = ssyHelpStatusList.getEnumerator();
        while (listEnumerator.moveNext())
        {
            conRangeCriteria += listEnumerator.current();
        }
        range = SysOperationHelper::convertMultiSelectedValueString(conRangeCriteria);
        range = strReplace(range, ";" , ", ");

        queryBuildRange = SysQuery::findOrCreateRange(_query.dataSourceTable(tableNum(ssyHelp)), fieldNum(ssyHelp, Status));
        queryBuildRange.value(range);
        queryBuildRange.status(RangeStatus::Hidden);

    }
    super(_query);
}

Step by Step creating Power view report in Dynamics AX

After configuring your server with for Power view, its a time to create sample Power view report and link it to Dynamics AX. This post includes following steps:

1. Creating power view data source (if not already created)
2. Create power view report
3. How you can share the created report b/w Systems

For the demo purpose I am going to create power view report for Purchase cube. You have to go to Your EP home page and select Site actions > View All site contents

Now select "Power view reports" link, it will open power view report root library where you can see all your power view reports along with their data sources.

In my scenario, purchase cube data source is not available in the library so I have to create power view data source first. To do this select "Document" from action page and select "new document"> Report data source

It will open report data source form, Set Name = "Purchase cube" and rest of the fields as defined in below image and Press OK button.

Set connection string as:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=;Initial Catalog=Dynamics AX initial;Locale identifier=1033;Cube=Purchase cube

Remember Cube=your AX cube name

Your power view library looks like this:

Now you can use this data source to create new power view report. To do this Select newly created data source (which is Purchase cube in our case), it will open power view report designer window along with Cubes measures and Dimensions. Perform your desired operation like Bar chart/pie chart etc (I left it blank) and Press "Save" icon from top right, it will open Save as window like this:

Set proper name of the report and click Save button, now you can see your new report in power view library.
Next you have to link this report with existing EP Power view page named "PowerViewDataSourceGeneratorPV". To access this page, go to Site action>All site content> Enterprise portal

Navigate to to PowerViewDataSourceGeneratorPV and select it

You can see following page

Select Sit action > Edit page

Now add new web part

Now select "SQL server power pivot from Dynamics AX

It will add new web part, now Edit this web part:

Now select the object mentioned in following image in sequential manner

Now you are done with linking power view report to EP. Next step is to publish these changes. To do this Go to Dynamics AX AOT\Web\Web Menu Items\URLs\PowerViewDataSourceGenerator and right click and select "Import page" option.

After importing page, the AOT\Web\Web Files\Page Definitions\PowerViewDataSourceGeneratorPV will also be updated and have reference of your new power view report. Also new Resource is been added as well.

That's it. you are done with your changes. now if you want to move the newly created report to other Server like Live server you just need to take XPO of AOT\Web\Web Files\Page Definitions\PowerViewDataSourceGeneratorPV and \Resources\PowerViewPurchCubeDataSourceReport_rdlx and import it on your destination server, then just Right click on AOT\Web\Web Files\Page Definitions\PowerViewDataSourceGeneratorPV and select "Deploy Element", it will deploy your report on your destination server Power view library. 

Happy Daxing !!!

Microsoft Dynamics AX 2012 White Paper - Enterprise Portal Development Cookbook

You can find EP Dynamics AX cookbook under the following link:

Microsoft Dynamics AX 2012 White Paper: Enterprise Portal Development Cookbook