Performance Monitor counters for SQL

SQL Server works with objects and counters, with each object comprising one or more counters. For example, the SQL Server Locks object has counters called Number of Deadlocks/sec or Lock Timeouts/sec.

Access Methods – Full scans/sec: higher numbers (> 1 or 2) may mean you are not using indexes and resorting to table scans instead.

Buffer Manager – Buffer Cache hit ratio: This is the percentage of requests serviced by data cache. When cache is properly used, this should be over 90%. The counter can be improved by adding more RAM.

Memory Manager – Target Server Memory (KB): indicates how much memory SQL Server “wants”. If this is the same as the SQL Server: Memory Manager — Total Server Memory (KB) counter, then you know SQL Server has all the memory it needs.

Memory Manager — Total Server Memory (KB): much memory SQL Server is actually using. If this is the same as SQL Server: Memory Manager — Target Server Memory (KB), then SQL Server has all the memory it wants. If smaller, then SQL Server could benefit from more memory.

Locks – Average Wait Time: This counter shows the average time needed to acquire a lock. This value needs to be as low as possible. If unusually high, you may need to look for processes blocking other processes. You may also need to examine your users’ T-SQL statements, and check for any other I/O bottlenecks.

Configuring the Python Environment

Please follow the following link to install the python on windows

https://matthewhorne.me/how-to-install-python-and-pip-on-windows-10/

Once python is installed we will be faced with a requirement to manage the python libraries. Python ships its package manager which is called pip.

The above link also has the details on downloading the script for pip installation. The script file is called get-pip.py and should be executed using the python command line to update the pip installer.

To install numpy please use the pip installer as shown in the screen below

Basic Docker Commands

Image is a class and container is an object of the class


To delete a container
=====================
docker ps -a //The command docker ps only displays running containers. To see all containers, including the stopped ones, use the -a parameter:
docker rm -f
docker system prune //clean up any resources — images, containers, volumes, and networks — that are dangling (not associated with a container):


How to see the log of a container
=================================
docker logs bcsandbox //every time the container starts is logs some important information like admin password etc


Get a list of images
====================
docker images or docker image ls


Run an executeable on a docker image (test is the container)
====================================
docker exec -it cmd //it stands for interactive 


Spin a container from an image (or create an object from a class)
==============================
docker run [options] image[:tag] [command] [args]
eg:
docker run -e accept_eula=Y 53ee8b0703ad //53ee8b0703ad is the name of the image


-e stands for an environment variables

There are certain environment variables that used by Navision images
--hostname (-h) specifies the hostname of the container. This is the name you will use to connect to the containers web client and the name you can ping. If you do not specify the hostname, the first 12 characters of the container Id will be used as the hostname.
--name specifies the name of the container. The name is used when referring to the container with other docker commands. If the name is not specified, a random name will be generated using a verb and a name (like friendly_freddy)
--memory (-m) specifies the max. amount of memory the container can use. The default for this option depends on how you are running the container. When you run Windows Server Containers there are no implicit memory option and the container can basically use all memory available to the host. When you run HyperV containers, the default max. memory is 1Gb.
--volume (-v) specifies a folder from the host you want to share with the container and the path inside the container, where you want to mount this folder. --volume c:\myfolder:c:\run\my shares the existing c:\myfolder on the host to the container and the content is in c:\run\my.
--restart specifies the restart options for the container.


command :
docker run -e accept_eula=Y --name bcsandbox -h NAVBC -m 4G -e useSSL=N  -v c:\myfolder:c:\run\my --restart always -e exitonerror=N -e locale=en-us microsoft/bcsandbox

to start using my license file placed in c:\myfolder
docker run -e accept_eula=Y --name bcdev -h DEVBC -m 4G -e useSSL=N  -v c:\myfolder:c:\run\my -e licensefile=c:\run\my\alfazance.flf --restart always -e exitonerror=N -e locale=en-us microsoft/bcsandbox


elaboration:
Which will accept the eula and run the dynamics-nav:devpreview container with 4Gb of memory, test as the name and hostname, http (not https), restart option set to always, locale to en-US, and use the licensefile, which is located in c:\myfolder\license.flf on the host computer.



//Publishing the Docker images on the Network
1. Stop any running containers -> Docker stop bcsandbox
2. Stop the docker service -> Stop-Service Docker
3. Remove the current Containe network-> Get-ContainerNetwork | Remove-ContainerNetwork -Force
4. modify daemon.json file -> '{"bridge":"none"}' | Set-Content C:\ProgramData\docker\config\daemon.json
5. create transprent network (use of of the two commands) -> docker network create -d transparent tlan
New-ContainerNetwork -Name tlan -SubnetPrefix 192.168.0.0/24 -GatewayAddress 192.168.0.1 -Mode Transparent -DNSServers 192.168.0.164,91.74.74.74
6. Start-Service Docker or restart the docker service
7. Run a container with the new adapter created ->
docker run --network tlan --ip 192.168.0.58 --name devcontainer -e accept_eula=Y -h BCDEV -e username=admin -e password=pass@word1 -e useSSL=N  -v c:\myfolder:c:\run\my -e licensefile=c:\run\my\alfazance.flf --restart always -e exitonerror=N -e locale=en-us microsoft/bcsandbox


 Create an container using navcontainerhelper
 ============================================
new-navcontainer -accept_eula -includeCSide -containerName test -licenseFile c:\myfolder\alfazance.flf -imageName microsoft/dynamics-nav:devpreview
new-navcontainer -accept_eula -includeCSide -containerName test -licenseFile c:\myfolder\alfazance.flf -auth NavUserPassword -imageName microsoft/bcsandbox


commit container as a new image
===============================
docker commit navcontainer navbcuaeloc/ver1:lan adapter changed
docker run -d navbcuaeloc/ver1


Fixing the Error = HNS failed with error : Element not found.
=============================================================
link https://github.com/docker/for-win/issues/750

stop-service hns
stop-service docker
del 'C:\ProgramData\Microsoft\Windows\hns\hns.data'
start-service hns
start-service docker

C:\myfolder\\CleanupContainerHostNetworking.ps1 -Cleanup -ForceDeleteAllSwitches
Restart-Computer -Force




docker run --network tlan --ip 192.168.0.59 --name bcangshudev -e accept_eula=Y -h BCANGSHU -e username=admin -e password=pass@word1 -e useSSL=N  -v c:\myfolder:c:\run\my -e licensefile=c:\run\my\alfazance.flf --restart always -e exitonerror=N -e locale=en-us microsoft/bcsandbox
docker run --network tlan --ip 192.168.0.60 --name bckirtidev -e accept_eula=Y -h BCKIRTI -e username=admin -e password=pass@word1 -e useSSL=N  -v c:\myfolder:c:\run\my -e licensefile=c:\run\my\alfazance.flf --restart always -e exitonerror=N -e locale=en-us microsoft/bcsandbox

New-NavContainer -containerName bcsan -accept_eula -alwaysPull -assignPremiumPlan -auth NavUserPassword -doNotExportObjectsToText -enableSymbolLoading -imageName microsoft/bcsandbox -includeCSide -memoryLimit 3G -shortcuts Desktop -updateHosts



CONVERT OBJECT TO AL
====================
Get-Command -Module NavContainerHelper
Export-NavContainerObject -containerName devcontainer -objectsFolder c:\myfolder -filter 'Type=Report;Id=206'
Convert-Txt2Al -containerName devcontainer -myDeltaFolder c:\myfolder -myAlFolder c:\myfolder\al -startId 70140931

Git Basics

Git is a local version control tool. Git also has an online centralized repository which is known as GitHub.

Getting started

• Once you have installed git you can use the GitBash console to configure the local git. 
• git config --global user.name "abc@abc.com'"
• git config --global user.email "abc@abc.com"
• git config --global core.editor "code --wait --new-window"  

The global setting are saved in an xml file on the  as done above can be viewed using

• git config --edit --global 

connecting with the GitHub might require the user name and password to be authenticated each time. This can be avoid by using the SSH connection to the GitHub. To generate an SSH key use the command prompt and type the following command:

• ssh-keygen -t rsa -b 4096 -C "yourmail@domain.com"

Before the next step, please ensure that the service for ssh is running in windows services "Open-SSH authentication agent". Please ensure that that you are in the %userprofile% directory before you issue the next command you can do 

• C:\cd %userprofile% 
• ssh-add ./.ssh/id_rsa : This command will add the identity created to the ssh agent running
• cd .ssh
• type id-rsa.pub 

Goto gitHub-> Profile -> settings and add the contents of the id_rsa.pub to the ssk section and give it a description from which computer this was added. To test if the ssh connection is working fine please use

• ssh -T git@github.com

Concept

======

Git is a distributed source code management software. In centralized VSS tools entire history is on the central server and only the latest copy (checked in version) is in the local directory. In git the entire history of all the versions and check ins is on every local copy as well. 

• Branches : These are features being worked on. A branch is created locally and when finalized is pushed. One user can be working on multiple features at the same time and hence can have multiple branches open at the same time. 
• Push : process of sending finalized changes to the GitHub 
• Pull : process of pulling the latest copies from GitHub 
• File states
• Committed : stored locally
• Modified: changed but not committed 
• Staged : marked for next commit.snapshot. 
• File areas
• Working directory: editing areas
• Staging : files waiting for the commit
• .git repo : committed files
• Repositories: its a folder for your project integrated with git. A new  

Git process outline:

=============

• git init : creates a local repository
• git clone: clone a repository into a local directory and create a remote branch
• git add : add a file into the staging area
• git commit: commit the changes to the local repository
• git push origin master: push the changes from the local master to the origin (remote repo)
• git pull remote master: 

Branches:

=======

In a nutshell a branch is parallel copy of the original source code which can at sometime be merged into the original source. In traditional system this was done using a copy of the source being hosted as a separate repository. 

Git is based on snapshot (differential and not incremental changes) and hence its easier for Git to implement this concept. Each commit creates a differential snapshot and files changed are marked in the snapshot. If a file is not changed it remains pointed to the original commit pointer. Hence a branch is also a pointer to one of the commits

The master is the default branch and it always points to the last commit made. It also automatically moves forward when a commit is made. 

Branches should be used to work on a feature and should only be committed to the master once completed and tested. To avoid conflicts the branches should be logically arranged to avoid the same object in multiple branches, this would ease the merge operations. 

• git branch <branchName>: This command is used to create a new local branch. 
• git branch -a : This command will give a list of all the local and remote branches. 
• git branch -vv: This command will give the local branches and their corresponding upsteam branches on remote repo. 
• git checkout : At any given point there can be multiple branches that are active and the active branch can be changed using checkout command. If the branch is missing it will get created. 
• git checkout -- . :this command can be used to discard all the unstaged changes and revert to the last committed stage on the current branch.
• git push -u origin <newbranch>: This command pushes the branch created locally to GitHub make sure the branch name is same locally and on remote. 
• git fetch : This command is used to fetch any branch from GitHub to the local repository. 
• git pull : this is used to merge branches. A pull request is from the owner of the branch to the owner of the master branch (collaborators) to merge the changes.
• git log --all  --oneline --decorate --graph: This command can be used to look at all the commit log and branches there are. 
• git --rebase:  If you don’t want a bunch of commits in a pull request, you may need to use git rebase to “squash the commit history”.

Remote repo

========= 

remote repositories are the once located on GitHub. Origin keyword in an alias that is created by default for the remote repository if a name is not specified when we clone it. Alternatively if we initialized a local repo (not cloned), we can link it to a remote repo using: 

• git remote add origin https://github.com/santoshkmrsingh/abc.git

we can also have multiple remote alias created (this would be logical when we are using forks (copies) of the same repository)

• git remote add afzMobile https://github.com/Alfazance/mobile.git

given the above two commands we have two remote alias created. These can be seen using 

• git remote: refers the link to the master branch on the remote repository.  
• git remote -v : list the full details of the remote links

remote tracking branch tells us what the current branch looks like at origin. The remote tracking branches cannot be directly worked at. We will always work with local branches. 

• origin/master : refers to the master branch on the remote repository. 
• git merge origin/master: merges the current branch with the remote repository 

pull and push

==========

pull and push work from the local branch to a corresponding branch on the remote repository. It hence important that before a pull or a push is used, the local repo and branch have a corresponding link to a remote repo and a branch. A push always works from the current local branch to a branch on the remote repo. Hence, it is important that before a push request is issued the correct branch is checked out. 

To link the current branch to one on the remote, you can use the  following command. 

• git branch --set-upstream-to origin/branch: This command is used to link a local branch to a branch on the origin

Once the link is established the following commands can be used to push the local commits to the remote repo

• git push : this command will push the contents of the current branch to the corresponding branch ont the remote server
• git push -u origin: : this command will push the contents of the current branch to the branch on the remote repo. If the branch does not exists on the remote repo it is created.
  Note: the branch name on the local repo should match the branch name on the remote. You cannot push changes from one branch name in the local directory to another branchname on the remote. 

Pull request:

Pull request can be more relevantly called merge request. Once the local contents have been pushed to the remote server the pull request can be used to merge these changes on the master branch. 

To have better control we can configure the pull requests branch rules (in settings on GitHub) where we can configure options like merging can only happen through pull requests and not directly through a local merge. 

Merge:

=====

Merge is used whenever there is a conflict i.e more than one user has made a change on the same file and same line. Merge can happen locally or on the server. 

If the local timestamp of a file in a branch is older then the one on the server, then the same should be locally merged (using a pull command), before it can be pushed up.  For the below commands please assume that we have the master branch checked out

• git merge feature: this command will merge the feature branch into the current branch (master in this case) along with all the commits of the feature branch.
• get merge --squash feature : this will merge the feature branch into the current branch (master) without the commit history of the feature branch.This should be followed by a commit on the master branch git commit -m "squashed commit with features".
• git rebase master: If the current branch has a base in the master, the rebase will bring the latest commit from the master into the current feature branch and reapply all the local commits in the feature branch on top of it. Thus we will have a new base with the latest changes from master. 
• git reset --hard <commitid>: This command deletes all the commits post the commitId this can be used to undo any unwanted commits.  
• git stash : stash commands can be used to undo and redo changes in the current working tree. This command can be used to revert all the current changes and revert to the last clean working directory status. Each time a stash command is used the changes are pushed to the stash list. 
• git stash push -m "comment" : this can be used to push a change to the stash with a custom message.
• git stash list : this can be used to list all the changes that are pushed to the stash.
• git stash apply:  will reapply all the changes that are parked in stash. If there are a no of stash on the stack we can choose which one to apply from the list using its index. 
• git stash drop <index>: this can be used to drop a given checkpoint from the stack.
• git stash clear : can be used to clear the stack maintained for stash.

Fork:

====

Fork is just a copy of the repository. Any changes to the fork does not impact the original repo. This is however internally still linked to the original repo. All the other commands to interact with a fork are the same as with any GitHub repository. 

Forks are very useful when it comes to accepting public contributions to your repository. One would not want changes to be done to the original repository, hence full access is provided to forked copies. Changes from the forked repository to the seed repository can only be done by the developer who has an write access to both the repositories. 

While working with a scenario like this, the need to have multiple named remote repositories might get evident.

$ git remote -v
> origin    https://github.com/YOUR_USERNAME/YOUR_FORK.git (fetch)
> origin    https://github.com/YOUR_USERNAME/YOUR_FORK.git (push)
> upstream  https://github.com/ORIGINAL_OWNER/ORIGINAL_REPOSITORY.git (fetch)
> upstream  https://github.com/ORIGINAL_OWNER/ORIGINAL_REPOSITORY.git (push)

In the example above the origin would refer to the clone repository created on GitHub and upstream is another remote link added to the original seed repository. Additional remote repo link is creating using :

$ git remote add upstream  https://github.com/ORIGINAL_OWNER/ORIGINAL_REPOSITORY.git

If you are hoping to contribute back to the original repository, you can send a request to the original author to pull your fork into their repository by submitting a pull request.

NOTES
=====
1. HEAD refers to the changes in the current branch

2. When prompted for message
press "i"
write your merge message.
press "esc"
write ":wq"
then press enter.

3. Create a file not possible using windows explorer (eg. a filename starting with period(.))
touch

4. To see a history of commits
git log --oneline --decorate --graph --all

5. Edit the .gitignore file
That can be done using the vim editor.
Simply type vi [path-to-file]\file and you will open the vim terminal editor.
Press a to toggle the edit mode
Edit stuff
Press Esc to finish edit mode and toggle the command mode
Press/type : to indicate you want to type a command
Type and enter wq to save the changes and exit


If you want to delete all your commit history but keep the code in its current state, please use the following:

Checkout
git checkout --orphan latest_branch

Add all the files
git add -A

Commit the changes
git commit -am "commit message"

Delete the branch
git branch -D master

Rename the current branch to master
git branch -m master

Finally, force update your repository
git push -f origin master

Outlook Duplicate Calendar Entries

use the following command to reset the calendar pane

outlook.exe /resetnavpane

AX2012 Sharepoint EP Portal performance

The performance for the Enterprise portal is highly dependent on the caching service pointed in the image below. 

Please try to restart the service to get the performance back. 

Move AX database between environment

If the active directories are different then the sysadmin role mapping with the active directory will be required to be updated

select SID, Networkdomain, networkalias from userinfo
where networkalias = ''

Find the SID for the user to be mapped in the new Active directory 

Whoami /user

Copy the new SID for the desired user and update the same in the userinfo table 

update userinfo set SID='', Networkdomain = '', networkalias = '' where id = 'admin'

Windows 10 Task Bar Problems

Had a strange problem today when in the task bar search stopped working. Also the taskbar was opening disabled and i had to open it up multiple time to enable it back.


Follow the below steps to restore the taskbar setting using powershell script

1. Find C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

2. Right-click and Run as Administrator

3. Copy this line:

Get-AppXPackage -AllUsers | Foreach {Add-AppxPackage -DisableDevelopmentMode -Register "$($_.InstallLocation)\AppXManifest.xml"}

Power BI D365 Integration

Every external application that needs an access to an Azure resource should be assigned necessary permissions. The registration process for Applications takes care of this.

To view the app registrations follow the following path: -
Goto Services and find App Registrations

Click on App Registrations and set the filter to All Apps
Then search of the name of the application as used when the app was registered on the PowerBI portal (in this case 365-hcpm-demo Power BI Integration )

Once you click on the app you will be able to see the details

At this location one should be able to view and manage most of the properties of the application.

Note: The secret key is only visible once when it is being created and thereafter hidden so if at all you have forgotten your key please delete the existing one and create a new one.

Name, Object id and other properties are visible using the properties tab page.

Reply urls tab page is used to alter the redirection page. 

Save RDP credentials

To be able to use saved credentials

1. Open Group Policy Editor via cmd -> gpedit.msc

2. Navigate to Local Computer Policy\Computer Configuration\Administrative Templates\System\Credentials Delegation\
3.Open Setting Allow Delegating Saved Credentials with NTLM-only Server Authentication, set it to Enabled

click on button Show... and in Show Contents window add Value TERMSRV/* Close all windows by pressing OK.

4. Run cmd and enter gpupdate command to update your policy

Cannot create a record in Workflow tracking status table (WorkflowTrackingStatusTable). Correlation ID: {70B17AE2-AF1A-47A9-937F-39F206AF91DE}, Pending. The record already exists.

Encountered this issue once the system recovered from the RecId max out in the workflow tables. There were certain transactions that were submitted and updated as In-review however the workflow was not triggered for them. The workflow bar was missing and on trying to Re-Submit the workflows the error was received.

Solution :

1. Shutdown all the AOS instance
2. Bring up any one AOS, synchronize the database.
3. Re-Submit the workflow for the failed records.

e.g: 

    TSTimesheetTable    timesheets;

    WorkflowTrackingStatusTable  trackingstatus;

    while select timesheets

    where 1==1

        && timesheets.ApprovalStatus == TSAppStatus::Pending

        //&& timesheets.TimesheetNbr == 'CCM-175040'

    notexists join trackingstatus

    where trackingstatus.ContextTableID == 4627

    && trackingstatus.ContextRecId == timesheets.RecId

    {

        /*

        ttsBegin;

        timesheets.ApprovalStatus = TSAppStatus::Create;

        timesheets.update();

        ttsCommit;

        */

    Workflow::activateFromWorkflowType("TSDocumentTemplate",

                                       timesheets.RecID,

                                       "Resubmitted due to error",

                                       false,

                                       timesheets.createdBy );

    info( timesheets.TimesheetNbr );

AX 2012-Cannot create a record in Workflow tracking status table

Got the message in the workflow. The messages can be seen in the EventViewer and can also be seen on screen if the workflow is triggered using the tutorial form.

The reason for this error is when the RECID of the tables involved and systemSequences is out of sync. Which means the RecId that the system tries to generate for a new record is something which is duplicate and has been generated in the past.

The reason why this mismatch occurs could be anything and is not known to me; however what is important at this point, is to get out of this situation. With the little R&D that i did, i realized the following facts.

1. The RecIDs are not generated real time from the systemSequences table. In reality each AOS caches a certain amount of RecID and tries to use them. 
2. Due to the above stated fact the max RecId that is visible directly from SQL database, is different from the one that actually exists in the AOS. Hence it is important that when we are correcting the NextVal in the systemSequences table, the value is fetched using the AOS table and not from the database directly (using SQL scripts).

Solution : 

1. Check of the Maximum value of the RECID column for the table resulting in the error, in this case it is WorkflowTrackingTable. 
2. Make sure you check for the value of RECID from AOS and not using the SQL table as there might be records that are not committed to the database. 
3. Sort the table by RECID in descending order and copy the maximum RecID as reported by AOS. 
4. Update the systemSequences table by adding 250 to this value to be on the safer side and to avoid cached values. 
5. Compile the table giving the error on each of the AOS so that the cache's is cleared. 

AX2012 Deploy ListPage with QuickLaunch menu

Generally when a List page is deployed it only gives an option to be deployed in the subsites that have been created in EP.  There could be a scenario where one would want the list page to open with a quick launch menu (navigation pane on the left )

We had created a new list page which had to appear inside the Timesheet Quick Launch Menu. The problem with the deployment was there was no option to deploy inside the Timesheet node, and if the list page was deployed under the EmployeeServices node, the user would lose the Quick Launch Menu each time the url was clicked.

Below are the steps involved to deploy a list page inside a Quick Lauch menu

Step 1. Create a Display menu for the listpage

Step 2. Right click the display menu and deploy the list page to the root site. When the list page is deployed to EP the deployment process will automatically create a WebURL

Step 3. Right click the WebURL menu created and import the page.

Step 4. Select the page (in my case the page name is AFZ_AllocationsListPage) and change the WebModule address. I have added Timesheets in the existing link

Step 5. Now right click on the Page Definition and deploy the page to the new address that has been specified.

Step 6. Once the page has been deployed at the new location the URL can be updated to refer to the new address

Step 7. At this stage the WebURL can be added to the quick launch menu created for timesheets, and when this link is click it would open with the left navigation pane in place as shown below.

Remove Sharepoint Upgrade Bar

To remove the upgade bar the following command can be used in the sharepoint 2013 management shell

Note: Please make sure that the mangement shell is executed using elevated administrator privileges.

Get-SPWebApplication http://afz-svr-int:90 | Get-SPSite -Limit All -CompatibilityLevel 14 | Foreach-Object {$_.AllowSelfServiceUpgrade = $false }

The above command can be executed for each of the hosted sites. 

Read a given segment value for a given account from the GL

Assuming that one has to read all the values from a MainAccount '20151026' where the Employee dimension 'D6_Employee' = 'E0028'

Below is a sample job for the same 
static void Job10(Args _args)
{
    GeneralJournalAccountEntry          GJAE;
    GeneralJournalEntry                 GJE;
    FiscalCalendarPeriod                FCP;
    SubledgerVoucherGeneralJournalEntry SLVGJE;
   
    DimensionAttributeValueGroupCombination DAVGC;
    DimensionAttributeLevelValue            DALV;
    DimensionAttributeValue                 DAV;
    DimensionAttribute                      DA;
    SPYAmount                               claimAmount;
    HcmPersonnelNumberId                    personnelNumber = HcmWorker::find(5637146086).PersonnelNumber;
   
    while select *
    from GJAE
    join GJE   
    where GJE.RecId == GJAE.GeneralJournalEntry       
        && GJE.AccountingDate >= mkDate(1,1,2017)
        && GJE.AccountingDate <= mkDate(31,1,2017)
        && GJAE.MainAccount == MainAccount::findByMainAccountId('20151026').RecId
    join SLVGJE
        where SLVGJE.GeneralJournalEntry == GJE.RecId
        && SLVGJE.VoucherDataAreaId ==  curext()  
        //reading the individual segments of the entry
    join DAVGC
        where DAVGC.DimensionAttributeValueCombination == GJAE.ledgerDimension
    join DALV
        where DALV.DimensionAttributeValueGroup == DAVGC.DimensionAttributeValueGroup                   
        && DALV.DisplayValue == personnelNumber
    join DAV
        where DAV.RECID == DALV.DimensionAttributeValue
    join DA
        where DA.RecID == DAV.DimensionAttribute       
        && DA.Name == 'D6_Employee'        
    {       
        claimAmount += GJAE.AccountingCurrencyAmount;
    }
}

SSRS Report UI Builder

UI Builder report is used to manipulate the request form rendered by the AX framework before a report is executed. The request form for a report is generated by the framework based on the meta data provided by the contract class.

 Below are the steps that are required to manage the User Interface of a report

1. Create a UI builder class by extending the class SrsReportDataContractUIBuilder.
2. We will have to create event handlers for the control on the report contract. 
3. The event handlers created in step 2 will have to be attached to the control created  by the reporting framework using the PostBuild method of the UIBuilder class.
4. The contract class has to be modified to include a reference to the UIBuilder class. This is done by including the SysOperationContractProcessingAttribute on the contact class declaration.
   [
       DataContractAttribute
       ,SysOperationContractProcessingAttribute(classstr(STPLedgerBalanceReportUIBuilder))
   ]
    

class STPLedgerBalanceReportUIBuilder extends SrsReportDataContractUIBuilder
{
    STPLedgerBalanceReportContract contract ;
}

public void build()
{
    super();

    Contract      = this.dataContractObject();  
}

public void lookup(FormStringControl _control)
{
    container cnt;

    Query query = new Query();
    QueryBuildDataSource    qbds, qbdsCI;
    QueryBuildRange         range, rangeDA;

    qbds = query.addDataSource(tableNum(USERDATAAREAFILTER));
    range = qbds.addRange( fieldNum( UserDataAreaFilter, User) );
    range.value( SysQuery::value( curUserId())) ;

    qbdsCI = qbds.addDataSource( tableNum( CompanyInfo) );
    qbdsCI.addLink( fieldNum( UserDataAreaFilter, DataArea) , fieldNum( CompanyInfo, DataArea) );
    qbdsCI.joinMode(JoinMode::InnerJoin);

    if ( Global::hasTableAccess( tableNum( STPRowDefinitionLine) , AccessType::Delete) == false )
    {
        rangeDA = qbds.addRange( fieldNum( UserDataAreaFilter, DataArea) );
        rangeDA.value( SysQuery::value( curext()) );
    }

    qbds.fields().clearFieldList();
    qbds.fields().addField(fieldNum(USERDATAAREAFILTER, DataArea));
    qbdsCI.fields().addField ( fieldNum( CompanyInfo,Name) ) ;


    SysLookupMultiSelectGrid::lookup(query, _control, _control, cnt);
}

public void postBuild()
{
    DialogField     companyList;
    super();

    companyList = this.bindInfo().getDialogField( this.dataContractObject(),  methodStr(STPLedgerBalanceReportContract, parmCompanyList) ) ;

    companyList.registerOverrideMethod(
          methodStr(FormStringControl, lookup),
          methodStr(STPLedgerBalanceReportUIBuilder,Lookup),
          this);
}

 public void getFromDialog()
{  
    super();
}

AX2012 invoke menuitem using X++ code

Below is the code to invoke a menu item on a click on command button.

The command button was required to execute some x++ code and then trigger the menu item, hence the menu item was not directly inserted as menu option instead the code was written on the click event.

   Args    _args = new Args();   

    new MenuFunction(identifierstr(STPLedgerBalanceCurrencyTranslation), MenuItemType::Action).run(_args);

AX2012 EP Pass Record context beween EP forms

Requirement : Open one modal form from another and pass the context.

The best way to pass the context is using the Dataset init method of the relevant table. In my case i had to pass the InventJournalTrans table's reference from a grid to a new form, where the details would be entered. Below are the steps for the same.

Step 1: Create the init method in the relevant table under the dataset.



The definition of the init method would be as follows:
public void init()
{
    Common              callerRecord = element.args().record();
    super();

    if ( callerRecord.TableId == tablenum(InventJournalTrans) )
    {
      this.query((EPQuery::makeRecIdQuery(this.query(),tablenum(InventJournalTrans),callerRecord.RecId)));
    }
}

Once the above is done we are sure that a filter would be imposed on the database if the calling form is sending the record context of the InventJournalTrans table.

Step 2: Is to ensure that a record context is passed from the source. We first create a new sharepoint page with a new web control and render a presentation that we want to be displayed.

A url type of WebMenuItem would be required to point to the newly created sharepoint page and the new userControl created. Create a new webMenuItem as shown below




Step 3: Attach the menu item in the parent UserControl such that it has the right context. In my case it happens to be a grid control which has a toolbar attached to it. So simply drag the url menu item to the relevant webmenu, which in this case is AFZEPInventJournalLineToolbar