Monday, April 9, 2012
Tuesday, April 3, 2012
How to write data into an Excel sheet from Microsoft Dynamics Ax
This article explains
Prerequisite:
static void writeDataIntoExcelSheetFromAx(Args _args)
{
InventTable inventTable;
Container itemIdCon;
COM comRange;
COM comWorkBook;
COM comWorkBooks;
COM comWorkSheet;
COM comCharacters;
COM comAppl;
str test,test1;
int offset = 65-1; //65 means letter 'A'
str 2 columnId;
str fileName;
str time;
int i;
#define.Excel('Excel.Application');
comAppl = new COM(#Excel);
comAppl.visible(true);
comWorkbooks = comAppl.workbooks();
WINAPI::createFile('C:\\test.xls');
comWorkbook = comWorkbooks.open('C:\\test.xls',true,true);
comWorksheet = comWorksheet.new('C:\\test.xls');
comWorksheet = comWorkbook.activeSheet(); //Use comWorkbook.activateSheet(); in case of Ax 3.0
comWorksheet.select();
while select inventTable
{
columnId = num2char(offset + 1);
i++;
test = columnId + int2str(i);
comRange = comWorksheet.range(test);
comCharacters = comRange.characters();
comCharacters.insert(inventTable.ItemId);
columnId = num2char(offset + 2);
test = columnId + int2str(i);
comRange = comWorksheet.range(test);
comCharacters = comRange.characters();
comCharacters.insert(inventTable.ItemName);
}
WINAPI::createDirectory('C:\\AxData');
time = time2str(timenow(),1,1);
time = strrem(time,':');
fileName = 'C:\\AxData\\' + curuserid() + date2str(today(),123,2,0,3,0,4)+ time + 'test' + '.xls';
comWorkbook.saveAs(fileName);
}
- How to write data into an Excel sheet from Microsoft Dynamics Ax using X++ language.
- How to write data into an Excel sheet using COM object in Microsoft Dynamics Ax.
- How to export Microsoft Dynamics Ax data into an Excel sheet at run time / dynamically /programmatically using X++ language.
Prerequisite:
- You should have X++ basic programming knowledge;
static void writeDataIntoExcelSheetFromAx(Args _args)
{
InventTable inventTable;
Container itemIdCon;
COM comRange;
COM comWorkBook;
COM comWorkBooks;
COM comWorkSheet;
COM comCharacters;
COM comAppl;
str test,test1;
int offset = 65-1; //65 means letter 'A'
str 2 columnId;
str fileName;
str time;
int i;
#define.Excel('Excel.Application');
comAppl = new COM(#Excel);
comAppl.visible(true);
comWorkbooks = comAppl.workbooks();
WINAPI::createFile('C:\\test.xls');
comWorkbook = comWorkbooks.open('C:\\test.xls',true,true);
comWorksheet = comWorksheet.new('C:\\test.xls');
comWorksheet = comWorkbook.activeSheet(); //Use comWorkbook.activateSheet(); in case of Ax 3.0
comWorksheet.select();
while select inventTable
{
columnId = num2char(offset + 1);
i++;
test = columnId + int2str(i);
comRange = comWorksheet.range(test);
comCharacters = comRange.characters();
comCharacters.insert(inventTable.ItemId);
columnId = num2char(offset + 2);
test = columnId + int2str(i);
comRange = comWorksheet.range(test);
comCharacters = comRange.characters();
comCharacters.insert(inventTable.ItemName);
}
WINAPI::createDirectory('C:\\AxData');
time = time2str(timenow(),1,1);
time = strrem(time,':');
fileName = 'C:\\AxData\\' + curuserid() + date2str(today(),123,2,0,3,0,4)+ time + 'test' + '.xls';
comWorkbook.saveAs(fileName);
}
How to install and configure Microsoft Dynamics Ax 2009 workflow step by step
NOTE: There is NO warranty on this article, use at your own risk.
I have created a new website called AxWfl as shown above. It’s not mandatory to create a separate website for this but recommended.
Using approver user id approve it and check that the purchase order has been created or not. I hope it will create a purchase order.
This article explains:
How to install Microsoft Dynamics AX 2009 Workflow step by step
How to configure Microsoft Dynamics AX 2009 Workflow step by step
How to setup Microsoft Dynamics AX 2009 Workflow step by step
About Workflow:
The Microsoft Dynamics AX workflow infrastructure enables user configurable workflows in Microsoft Dynamics AX application modules with specific focus on task and approval workflows. The workflow runtime manages configuration and executation of workflows while the application modules will manage activation and business logic associated with workflows.Microsoft Dynamics AX 2009 workflow uses Windows Workflow Foundation (.Net framework), AX batch framework, Internet Information Services (IIS), Active Directory (AD) users, .Net Business Connector for AX and web services.
Prerequisite:
You should have basic knowledge of AX.Basic AX should be installed on your system.Create two users in AD. One is for workflow another is for business connector proxy. The password should not be expired or changed for these users.If you have already created, no need to create now.Add these users to AX.
Steps to follow:
1) Open AX client then open the System service accounts form and add users for workflow.Go to Administrator > Setup > Security > System service accounts.
2) If you want to use a separate website for workflow then create a website in IIS or you can use the default website. If you use default website the setup process will create a sub site under default website.
I have created a new website called AxWfl as shown above. It’s not mandatory to create a separate website for this but recommended.
3) Run AX setup file and install workflow. If .Net Business Connector is not installed AX setup will install it itself.
4) Once the setup process is done successfully open AX client.
5) From Basic main menu create a Calendar. Here I have created the calendar called 24Hours.
6) Create a batch job for workflow. Go to the Basic > Batch job list – user form and create a batch job. Here I have created a job called Wfl.
7) Go the Administration > Setup > Workflow infrastructure configuration wizard form.Run the wizard and follow the instructions and finish it.
8) Now make a setup for Purchase Requisition. Go to Accounts Payable > Setup > Workflow configurations form. Here configure the workflow with required conditions. A Workflow Configuration is bound to a single Workflow Template. In this process you need to select the calendar you created in step 5.
Once the purchase requisition workflow setup is over go to AOT > Forms > PurchReqTable > Designs > Design. Right click on Design then properties and check whether is Workflow Enabled or not.
9) Once you have done all these then create purchase requisition using respective user, perform all the tasks you instructed during workflow configuration/setup and submit it. After submission you can see the history of workflow.
10) Now open AX client using approver user id and see the task assigned and complete it.
Using approver user id approve it and check that the purchase order has been created or not. I hope it will create a purchase order.
How to resolve Ax 2009 SRS reports deployment error or exception
This article explains how to remove
In case of SQL Server 2008
One or more libraries built with warnings. Please see MSBuild log at C:\Documents and Settings\Administrator\Local Settings\Temp\tmp.tmp
Deploying 0 data source(s) and 63 design(s) in 42 culture(s) (2521 item(s) total)...
Unable to connect to http : // [your system name] /ReportServer/ReportService2005.asmx as specified in the config file for the report server at C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config. If this url is not correct please update the config file, otherwise make sure the report server is configured correctly. The deployment log file can be found at "C:\Users\Administrator\AppData\Local\Temp\1\tmp.txt"
In case of SQL Server 2005
One or more libraries built with warnings. Please see MSBuild log at C:\Documents and Settings\Administrator\Local Settings\Temp\tmp.tmp
Deploying 0 data source(s) and 63 design(s) in 42 culture(s) (2521 item(s) total)...
Unable to connect to http : // [your system name] /ReportServer/ReportService2005.asmx as specified in the
config file for the report server at C:\Program Files\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer\RSReportServer.config. If this url is not correct please update the config file, otherwise make sure the report server is configured correctly.
The deployment log file can be found at "C:\Users\Administrator\AppData\Local\Temp\1\tmp.txt"
Resolution:
Check following:
1) Is your report manager working from IIS or Internet explorer? If no then make it workable. If it works then you can see Report Manager Home.
2) Is web service http : // [your system name] /ReportServer/ReportService2005.asmx working?
To make the default Report Manager workable, you can see the installation and configuration guidance.
If you are using SQL server 2008, open the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config file in an editor (Notepad is perfect).
If you are using SQL server 2005, open the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\ rsreportserver.config file in an editor (Notepad is perfect).
Now you have to modify this config file. Before you do this I do recommend you to make a copy of this file as a backup.Press Ctrl + F and find ‘SQLCommandTimeoutSeconds’. By default the value is 60. Increate the value upto 300 and save the file.
Now restart the IIS.
Launch AX SRS report deployment tool. I hope it will work.
Once the AX SRS Reports deployment is finished go back to the above mentioned config file and decrease the value 60 from 300.
~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~
Error: System.Web.Services.Protocols.SoapException: An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC30137] Error saving temporary Win32 resource file 'C:\Users\axadmin\AppData\Local\Temp\vbcF484.tmp': The process cannot access the file because it is being used by another process. ’.
Solution: 1) Restart SQL Server Reporting Services
2) Stop the antivirus application running on the same server.
Note: Use at your own risk. There is no warranty on this article.
- While deploying AX SRS reports from AX client (AOT->Report Libraries), system gives below error / exception
- While deploying AX Reports Deployment tools , system gives below error / exception
In case of SQL Server 2008
One or more libraries built with warnings. Please see MSBuild log at C:\Documents and Settings\Administrator\Local Settings\Temp\tmp
Deploying 0 data source(s) and 63 design(s) in 42 culture(s) (2521 item(s) total)...
Unable to connect to http : // [your system name] /ReportServer/ReportService2005.asmx as specified in the config file for the report server at C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config. If this url is not correct please update the config file, otherwise make sure the report server is configured correctly. The deployment log file can be found at "C:\Users\Administrator\AppData\Local\Temp\1\tmp
In case of SQL Server 2005
One or more libraries built with warnings. Please see MSBuild log at C:\Documents and Settings\Administrator\Local Settings\Temp\tmp
Deploying 0 data source(s) and 63 design(s) in 42 culture(s) (2521 item(s) total)...
Unable to connect to http : // [your system name] /ReportServer/ReportService2005.asmx as specified in the
config file for the report server at C:\Program Files\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer\RSReportServer.config. If this url is not correct please update the config file, otherwise make sure the report server is configured correctly.
The deployment log file can be found at "C:\Users\Administrator\AppData\Local\Temp\1\tmp
Resolution:
Check following:
1) Is your report manager working from IIS or Internet explorer? If no then make it workable. If it works then you can see Report Manager Home.
2) Is web service http : // [your system name] /ReportServer/ReportService2005.asmx working?
To make the default Report Manager workable, you can see the installation and configuration guidance.
If you are using SQL server 2008, open the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config file in an editor (Notepad is perfect).
If you are using SQL server 2005, open the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\ rsreportserver.config file in an editor (Notepad is perfect).
Now you have to modify this config file. Before you do this I do recommend you to make a copy of this file as a backup.Press Ctrl + F and find ‘SQLCommandTimeoutSeconds’. By default the value is 60. Increate the value upto 300 and save the file.
Now restart the IIS.
Launch AX SRS report deployment tool. I hope it will work.
Once the AX SRS Reports deployment is finished go back to the above mentioned config file and decrease the value 60 from 300.
~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~
Error: System.Web.Services.Protocols.SoapException: An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC30137] Error saving temporary Win32 resource file 'C:\Users\axadmin\AppData\Local\Temp\vbcF484.tmp': The process cannot access the file because it is being used by another process. ’.
Solution: 1) Restart SQL Server Reporting Services
2) Stop the antivirus application running on the same server.
Note: Use at your own risk. There is no warranty on this article.
Step by Step Guide How to Install Dynamics AX 2012 - Part 1
How to Install and Configure Dynamics AX 2012 ComponentsOver the past few weeks, I was involved in installing and configuring Dynamics AX 2012 Components on couple of machines running Server 2008 and Windows 7 Enterprise O/S.
Thought I will share my experience here in a series of posts covering installation of all major components of AX 2012 - Database, AOS, Client, EP Components, Sharepoint 2010, Reporting Services and Business Intelligence.
Overall Installation Experience/Highlights: (Good)
a. I can say compared to my previous experiences with installation of Dynamics AX 4.0 and 2009, Dynamics AX2012 is less error-prone and satisfactory even though, the number of Pre-Requisites to be installed is quite a significant one in 2012
b. Validate Pre-Requisite Tool is quite handy which helps one to prepare for installation so that one is not taken by surprise when he/she performs the actual installation of components
c. Summary Report which comes up at the end is quite cool and user-friendly.
Considerations:
a. Requires more computing power in terms of RAM (As per System Requirements Guide it says 4 GB is the minimum). And from my personal experience, I have seen that AOS service (AX32serv.exe at it's peak, especially when you start the service and bring up the AX Client first time, it goes up close to 1 GB).
b. I also tried the fully loaded image from PartnerSource on a machine which had overall 6GB RAM, allocated 4GB to the VM, but still it was crawling. It took me ages to bring up the AOS Service.
c. Since now the AOD is no longer file based and the models reside in SQL Server, you also need to plan ahead for the disk capacity for your SQL boxes. Standard AX database (Application) along with the model database (AOD) bulks up to 3 GB approx and if you want Contoso DB (Demo Data loaded) you should free up atleast 10 GB.
Ok, Now I will jump start the installation process starting with installation of Database, AOS and Client Components. I had captured the screenshots along the way as this will help me and my peers when we setup DEV, Test, Pre-Prod and Prod Environments.
Initial Setup Screen
Pre-Requisite Validation Utility
Note - Here, I'm just installing the components which are checked below: Database, AOS, Client Components, Debugger (My favorite ;-) and Management Utilities
Tip - I would suggest from my personal experience of installing AX, even though Single-Computer Installation sounds easy and quick but we don't get the proper control here, so I prefer Custom Installation and choose my components, easy to troubleshoot and learn as we go
This screen shows you the list of validation errors which means you need to install the base pre-requisites software before you proceed.
The first error shown below is about the SQL Server Full Text Search is not installed or the service is not running
Launch SQL Server Setup and Install Full-Text Search component
Next, comes the Report Viewer Control. You need to download it from here http://www.microsoft.com/download/en/details.aspx?id=6442
Some other pre-requisites which needs to be configured and installed are as follows:
Please Note, as per the Operating System whether it's Server 2008 or Win 7, it might ask you for some other pre-requisites as well, so I would suggest you run the pre-requisite validator and please don't go by the assumption that the above links are the ONLY pre-requisites required.
.
Once the Pre-Requisites are installed successfully, you can kick start the Installation
Tip - You can change the location here
Select the Components you want to install, based on your implementation topology you might want to install different components on different boxes. As I'm setting up a Sandbox at the moment, I just installed everything in one box
You can change the database names at this step below if you want to.
Key in the ports for AOS and Services
Note - For demo or training purposes, it's ok to go with network service but when you are setting up for pre-prod or production environments, you should choose a proper service account for AOS Account
All set and ready to go...
You can take a break now ;-) and come back as this step took me good 15-20 mins time!
And this too.. The Summary Report comes up quite good (This is a new installation feature in 2012 which is a brief summary of the components installed )
Next, you can go ahead with the Compile Checklist.. One other small nice feature which I like is "Processing..." pop up below, it's quite interactive unlike previous versions, where you don't know what's happening and everything looks stuck
Next, I will be posting my experience on other big components of AX2012 starting with Configuring EP, Sharepoint 2010, SSRS and Business Intelligence. Keep looking at this space!! Till then Happy Dax'ing...
c. Summary Report which comes up at the end is quite cool and user-friendly.
a. Requires more computing power in terms of RAM (As per System Requirements Guide it says 4 GB is the minimum). And from my personal experience, I have seen that AOS service (AX32serv.exe at it's peak, especially when you start the service and bring up the AX Client first time, it goes up close to 1 GB).
Ok, Now I will jump start the installation process starting with installation of Database, AOS and Client Components. I had captured the screenshots along the way as this will help me and my peers when we setup DEV, Test, Pre-Prod and Prod Environments.
Pre-Requisite Validation Utility
Note - Here, I'm just installing the components which are checked below: Database, AOS, Client Components, Debugger (My favorite ;-) and Management Utilities
Tip - I would suggest from my personal experience of installing AX, even though Single-Computer Installation sounds easy and quick but we don't get the proper control here, so I prefer Custom Installation and choose my components, easy to troubleshoot and learn as we go
This screen shows you the list of validation errors which means you need to install the base pre-requisites software before you proceed.
The first error shown below is about the SQL Server Full Text Search is not installed or the service is not running
Launch SQL Server Setup and Install Full-Text Search component
Next, comes the Report Viewer Control. You need to download it from here http://www.microsoft.com/download/en/details.aspx?id=6442
Some other pre-requisites which needs to be configured and installed are as follows:
- .Net Framework 4.0 http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17718
- Hotfix for .Net Fx 4.0 http://archive.msdn.microsoft.com/KB2390372/Release/ProjectReleases.aspx?ReleaseId=4917 (Select the correct hotfix based on your OS 32-bit or 64-bit)
- Open XML SDK for Microsoft Office http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124
- (Tip - Install OpenXMLSDKv2.msi first and then OpenXMLSDKTool.msi, otherwise it spits out error)
- SQL Server Analysis Management Objects http://go.microsoft.com/fwlink/?LinkId=130655
- VC++ 2008 SP1 Redistributable (x86) http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5582
- VC++ 2008 SP1 Redistributable (x64) http://www.microsoft.com/download/en/details.aspx?
- VSTO http://go.microsoft.com/fwlink/?LinkId=158918
Please Note, as per the Operating System whether it's Server 2008 or Win 7, it might ask you for some other pre-requisites as well, so I would suggest you run the pre-requisite validator and please don't go by the assumption that the above links are the ONLY pre-requisites required.
.
Tip - You can change the location here
Select the Components you want to install, based on your implementation topology you might want to install different components on different boxes. As I'm setting up a Sandbox at the moment, I just installed everything in one box
Didn't thought of much experimenting with Themes so left it as it is.
You can change the database names at this step below if you want to.
Key in the ports for AOS and Services
Note - For demo or training purposes, it's ok to go with network service but when you are setting up for pre-prod or production environments, you should choose a proper service account for AOS Account
This is kinda cool as you have the option below to choose the installation type as Developer, Administrator or Business and accordingly it will bring up the workspace for you after the install. At this moment, I'm wearing the administrator hat :-)
All set and ready to go...
You can take a break now ;-) and come back as this step took me good 15-20 mins time!
I love this :-)
And this too.. The Summary Report comes up quite good (This is a new installation feature in 2012 which is a brief summary of the components installed )
Next, you can go ahead with the Compile Checklist.. One other small nice feature which I like is "Processing..." pop up below, it's quite interactive unlike previous versions, where you don't know what's happening and everything looks stuck
Next, I will be posting my experience on other big components of AX2012 starting with Configuring EP, Sharepoint 2010, SSRS and Business Intelligence. Keep looking at this space!! Till then Happy Dax'ing...
Dynamics AX 2012 SSRS reports patterns
Here are the design pattern/changes that we need to make sure for AX 2012 SSRS report development:
Functional Area
|
AX2009
|
AX2012
|
Input parameters (definition)
|
Report
|
Data contract (*)
|
Input parameters (validation)
|
Report
|
Data contract (*)
|
Input dialog (simple)
|
Report
|
Data contract (*)
|
Input dialog (complex)
|
Report
|
UI Builder (*)
|
Input parameters (modification)
|
Report
|
Controller (*)
|
Dynamic query
|
Report
|
Query
|
Report data
|
Report
|
Table (temp)
|
Business logic
|
Report
|
RDP
|
Report layout
|
Report
|
SSRS Report
|
Subscribe to:
Posts (Atom)