Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts

Tuesday, October 13, 2015

AX DB Restore Scripts - Moving AX DB from one server to another server

Following SQL script is very useful while moving AX DB from one server to other server. Thanks to http://www.exploreax.com.

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
---Reporting Services---
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

---SSAS Services---
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25
---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ '

---Email Template Settings---
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

--List of users separated by | to keep enabled, while disabling all others
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
--List of users separated by | to disable, while keeping all the rest enabled all others
--Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

--*****BEGIN UPDATES*******---

---Update AOS Config---
delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG)
update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1
where serverid != @AOS -- Optional if you want to see the "affected row count" after execution.

---Update Batch Servers---
delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID)
update BATCHSERVERGROUP set SERVERID=@AOS
where serverid != @AOS -- Optional to see "affected row count"
update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]'
update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]'

---Update Reporting Services---
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set
    SERVERID=@REPORTSERVERID,
    SERVERURL=@REPORTSERVERURL,
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL,
    SERVERINSTANCE=@REPORTINSTANCE,
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID,
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

---Update SSAS Services---
delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set
    SERVERNAME=@SSASSERVERNAME,
    DESCRIPTION=@SSASDESCRIPTION,
ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

---Disable all users except for a specific set---
update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
---Disable specific users---
---update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

--Clean up server sessions
delete from SYSSERVERSESSIONS
--Clean up client sessions.
delete from SYSCLIENTSESSIONS

reference : http://www.exploreax.com/blog/blog/2015/10/13/ax-db-restore-scripts-full-script/

Monday, September 2, 2013

Simple UI Builder Class in SSRS Report in AX 2012

This UI Builder class is simple class with less code and less effort. UI Builder Class is needed when you want to customize your dialog which pop ups when you open a Report. UI Builder Class helps you to add run time lookups and other controls on the dialog form.
Step1 : Your Class must extends SrsReportDataContractUIBuilder
class SimpleDemoUIBuilder extends SrsReportDataContractUIBuilder
{
DialogField dialogEmplId;
DialogField dialogName;
boolean enable;
SimpleDemoContract contract;
}// two fields emplId and Name will reflect in the lookup in dialog form at the time of report opening.
Step2 : Override the build method
public void build()
{
contract = this.dataContractObject();
dialogEmplId = this.addDialogField(methodStr(SimpleDemoContract, parmEmplId),contract);
}// this method used for adding the field  which is from contract class.
Step3 : Write this below code to get lookup
private void emplIdLookup(FormStringControl emplIdlookup)
{
Query query = new Query();
QueryBuildDataSource qbds_EmplTable;
SysTableLookup sysTableLookup;
// Create an instance of SysTableLookup with the current calling form control.
sysTableLookup = SysTableLookup::newParameters(tableNum(FilterDemo), emplIdlookup);
// Add fields to be shown in the lookup form.
sysTableLookup.addLookupfield(fieldNum(FilterDemo,EmplId));
sysTableLookup.addLookupfield(fieldNum(FilterDemo,Name));
qbds_EmplTable = query.addDataSource(tableNum(FilterDemo));
sysTableLookup.parmQuery(query);
// Perform the lookup
sysTableLookup.performFormLookup();
}
Step4 : Override this method
public void getFromDialog()
{
contract = this.dataContractObject();
super();
}
Step5 : Override this method
public void initializeFields()
{
contract = this.dataContractObject();
}
Step6 : Override this method
public void postBuild()
{
super();
dialogEmplId = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SimpleDemoContract,parmEmplId));dialogEmplId.registerOverrideMethod(methodStr(FormStringControl, lookup),
methodStr(SimpleDemoUIBuilder,emplIdLookup), this);dialogEmplId.lookupButton(2);
}
the Contract Class code and DP class have already been added to the blog. please check it for further reference but one important part has to be added to the contract class i.e in class declaration, the following part has to be updated.

[DataContractAttribute,SysOperationContractProcessingAttribute(classstr(SimpleDemoUIBuilder))]

Working with SSRS reports in Dynamics AX 2012

Microsoft Dynamics AX provides a model-based approach for creating reports by providing project templates and modeling tools that are incorporated into the Microsoft Visual Studio development environment. The reporting features provided by Microsoft Dynamics AX are tightly integrated with SQL Server Reporting Services.
Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. The reporting framework includes a set of tools for you to define reports in the Microsoft Visual Studio development environment. The report development experience takes advantage of extended SQL Server tools and components fully integrated into the Microsoft Visual Studio environment. By using the reporting APIs, you can integrate or extend data and report processing in custom applications. The following table provides links to more information on these Microsoft technologies:
You can refere following url for more information about report development with SSRS,Dynamics AX 2012. 

Wednesday, April 3, 2013

Debugging Microsoft Dynamics AX 2009 SSRS reports with Microsoft Visual Studio 2008


While Microsoft SQL Server Reporting Services (SSRS) reports allowed in in Dynamics AX 4.0 a user to create Ad'hoc reports that were only based on the data stored in the Dynamics AX database, in Dynamics AX 2009 Production reports allow also the execution of Business Logic. The Business Logic can either be X++ code in the AOT or C# code directly in the SSRS report.
This also brings up the question how the C# code could be debugged when necessary. You can use the Visual Studio 2008 integrated debugger however you need to follow certain steps otherwise your breakpoints will not be hit.
You need a PC where the following components are installed:
  • Microsoft Dynamics AX 2009 Client,
  • Microsoft Dynamics AX 2009 Reporting Tools,
  • Microsoft SQL Server 2005 or 2008 Reporting Services,
  • Microsoft Dynamics AX 2009 Reporting Extensions, and
  • Microsoft Visual Studio 2008
Note:In the following example we use a report that is based on Microsoft SQL Server Analysis Services (SSAS) data. Therefore the Dynamics AX default cubes need to exist, however SSAS does not necessarily need to be locally installed.
In the following steps I will explain how you can debug the standard SSRS / SSAS reportCust.CashInflowvsCashOutflow for example:
  1. Start the Dynamics AX Client, open the AOT window and go to AOT\Report Libraries
     
  2. Click with the right Mouse button at the Cust report library and select Edit in Visual Studio from the context menu
     
  3. Wait until the Visual Studio IDE has launched completely
     
  4. Make sure the active configuration of the Solution is set to Debug (to check the Active configuration go toBuild - Configuration Manager...)
     
  5. Rebuild the whole Solution by selecting Build - Build Solution
     
  6. Deploy the Solution to your Microsoft SQL Server Reporting Services by selecting Build - Deploy Solution
    (Note: It is important to deploy the Solution from within Visual Studio even if the SSRS reports were already deployed and did not change since then!)
     
  7. Open the report CashInflowvsCashOutflow from the Visual Studio Solution Explorer window
     
  8. In the Report Explorer window expand the node Data Methods and double click at GetDefaultCompany
     
  9. Place a breakpoint (e. g. by pressing the [F9] key) at any line of code
     
  10. Start the Internet Explorer and navigate to your Microsoft SQL Server Reporting Services Report Managerpage (e. g. http://localhost/reports)
     
  11. Click at the Dynamics folder in the Report Manager page
     
  12. Go back to Visual Studio and select Tools - Attach to Process from the menu
     
  13. Make sure the options Show processes from all users and Show processes in all sessions are enabled(checked)
     
  14. The next step is different based on the version of Microsoft SQL Server Reporting Services:
    1. If you are using Microsoft SQL Server 2008 Reporting Services:
      In the list of processes located the process ReportingServicesService.exe and attach to this process
       
    2. If you are using Microsoft SQL Server 2005 Reporting Services:
      In the list of processes located the process w3wp.exe and attach to this process (If you have more than one w3wp.exe process listed, see the appendix at the end how to determine the correct process)
       
  15. Go back to the Report Manager page in Internet Explorer and execute the reportCust.CashInflowvsCashOutflow.FullPage
     
  16. The breakpoint will be hit by the Visual Studio integrated debugger
     
Some more hints:
  • On Windows Vista and Windows Server 2008 make sure you run Visual Studio with elevated privileges (Run as Administrator)
     
  • Once the debugger is attached you can check what modules are loaded in the Visual Studio integrated debugger by selecting Debug - Windows - Modules from the menu (The Assembly the C# code is compiled to is Cust.BusinessLogic.DLL)
     
How can I determine the correct w3wp.exe process to attach to?
The Microsoft SQL Server Reporting Services 2005 are hosted in the Microsoft Windows Server Internet Information Services (IIS). Therefore the process to attach to for debugging is the IIS Worker Process (w3wp.exe). As IIS is starting at least one Worker Process per Application Pool, more than one w3wp.exe processes are usually running on a Windows Server.
In order to see what Worker Process is serving what IIS Application Pool tools exists:
  • On Windows Server 2003 (IIS 6.0) run: iisapp.vbs
  • On Windows Server 2008 (IIS 7.0) run: appcmd.exe list wp
The output of the tools looks as follows:
C:\Windows\system32>iisapp.vbs
W3WP.exe PID: 2056 AppPoolId: AxReportServer
W3WP.exe PID: 5092 AppPoolId: EP - 39014

C:\Windows\system32\inetsrv>appcmd.exe list wp
WP "6916" (applicationPool: SharePoint Central Administration v3)
WP "1356" (applicationPool: AxReportServer)
Once identified the correct w3wp.exe process (based on the Application Pool) use the PID (Process ID) to attach to the correct w3wp.exe process.
--author:Alexander Lachner
--editor:Alexander Lachner
--date:16/06/2009

Source : http://blogs.msdn.com/b/emeadaxsupport/archive/2009/07/16/debugging-the-business-logic-in-microsoft-dynamics-ax-2009-ssrs-reports-with-microsoft-visual-studio-2008.aspx

Thursday, December 15, 2011

Calling a Custom Dynamics AX 2009 SSRS report and passing parameters - From X++

The most recent post, was the following.: Dynamics AX 2009 - Reporting Possibilites

In that post there was a comment left by someone talking about some of the limits that still exist with the custom SSRS reports.

As I point out there are pro's and con's with the custom SSRS reports for Dynamics AX 2009, however one of the things the reader pointed out was passing parameters to the custom SSRS report.

So I thought it would be worth while to do a post on exactly how to do this.

In this example I have created a custom SSRS report for Dynamics AX 2009 instance.



In this you will see I have a Query, that was used for my report, and also a report library I created using Visual Studio 2008 Dynamics AX 2009 Reporting template.

The query, has a Range of SalesId, and in doing that, this was auto added to the report parameters, in the VS2008 projects. (see below image)



This is the name of the parameter in the report def., and therefore important to note for our exercise.

So the task was to take and pass values to reports from X++. I took and create a job that does exactly that. It takes and call my custom reports, output menu item, and pass it a value for the SalesId parameter. The code follows.:


MenuFunction CustRptMI;
Args Args;
;

CustRptMI = new MenuFunction(menuItemOutputStr(srsCustomRpt),MenuItemType::Output)
Args = new Args();
Args.parm("qryCustomSSRS_SalesId=*SO-100004*");
CustRptMI.run(Args);
CustRptMI.wait();


Notice the name of the parameter is exactly the name it is in the VS2008 project for the report def. 'qryCustomSSRS_SalesId'. Then simply and = and then . You can add multiple parameters here with commas, and there you have the report being called, ran, and from X++, parameters being passed and the report generated. (See image below)



So as you can see, you can make use of X++ to call custom SSRS reports, pass in paramaters, and do this pretty simply. You just have to know the exactly parameters names, and you can go from there.

Wednesday, December 14, 2011

Creating SSRS Reports with Visual Studio 2008 for DAX 2009

To develope SSRS Reports in Visual Studio 2008 following requisite should be install on your machine.

1. Reporting Extensions ( From Ax Setup)
2. Visual Studio 2008
3. Reporting Tools ( From Ax Setup)
~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~

Creating SSRS Reports for Microsoft Dyanamics Ax 2009 with Visual Studio 2008.

1. Open VS 2008
2. New --> Project --> Visual C# -->Dynamics-->Dynamics Ax Reporting Project
3. Develop Reports
4. Right click and Save to AOD
5.Or deploy
6. fom ax application goto Reports Libraries-->Select report project and deploy.

To show report on the content pane in the reports column

1. create new output menu for displaying reports
2. In properties select ObjectType as  SQLReportLibraryReport
3. select developed report
4. Place the output menu on your reports pane to be display.

Tuesday, November 15, 2011

Could not load file or assembly 'Microsoft.Dynamics.AX.Fim.PaymentService, Version=5.0.0.0,

While deploying SSRS , I got the below error :
Could not load file or assembly 'Microsoft.Dynamics.AX.Fim.PaymentService, Version=5.0.0.0,………….
To overcome this issue, we need to add the above mentioned dll from client\bin  folder to GAC as below :
Go to Start -> All programs -> Microsoft visual studio 2008 -> visual studio  tools -> visual studio  command prompt , right click and run as administrator :
Run the below  command :
Gacutil /i “location of dll”

Upon successfully added , you will get the message , “Assembly added to the cache”.


Hope it helps you as well.

Monday, November 14, 2011

Dynamics AX Business Intelligence

There is a new blog from Microsoft, focusing specifically on Dynamics AX BI. The blog can be found here.:

Dynamics AX Business Intelligence

AX SSRS Reporting Concepts

ScreenCast - AX SSRS Reporting Concepts on YouTube

Monday, November 7, 2011

Create first SSRS report in Dynamics AX 2012

Today, let us learn how to quickly create report models and report in Visual studio and add to AOT[Dynamics AX 2012] and see a running report.
Some information:
SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Reporting Services is a server-based reporting platform that includes a complete set of tools to create, manage, and deliver reports, and APIs that enable you to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.
In a Visual Studio reporting project for Microsoft Dynamics AX, you can define a report in a report model. A report consists of a collection of items, such as datasets, parameters, images, and report designs. A model can contain more than one report.
Also, for this post I am assuming that all report services are configured in the system.
Let us create a query in AOT as a dataset source for our report.
Create a new query by name – “SR_InventTableQuery” and add InventTable as datasource and add ItemId range to it. I will let you know the significance of adding the range as I proceed further..
So, your new query should like below :

Please note: we can use already existing queries which are in AOT for report as datasource, for better understanding I have created new query above.
Lets proceed further. Open visual studio 2010 and lets us create a new Dynamics AX project.
Once visual studio is launched >> click on file menu >> New project as shown below

Select Microsoft Dynamics AX from the installed templates >> report model and name the model as SR_ReportNewModel as shown below

Now let us add a new report to the newly created report Model as shown below. Right click on the SR_ReportNewModel from the solution explorer, Add >> Report

Rename the report to SR_InventTable by right click and rename option on the newly added report.
Then we need to add the dataset to the newly created report. Right click on the datasets node and chose the option New datset. Rename it to InventTable and go to query property and click on the ellipsis (…) button to select the query which we have created as shown below [picture explains better than 1000 words]

It will open with list of Dynamics AX Queries from which we should select our query “SR_InventTableQuery” and click on next button as shown below

Now , you can select the list of fields and display methods you want to see on your report.
I have selected few fields form the fields and also couple of display methods like site Id and location Id as shown below and click on Ok Button.

wow..There you go… we are done with the datasets part and lets work on the design part real quick now…
Its very simple..Select the InventTable dataset and drag and drop on to your designs node as shown below. It will create autodesign for you :)

In my case, when I expand the designs node, i see my fields and the data methods added in the data nodes. we will look in to other nodes in detail later.
Well there are now some important [not mandatory] properties but beautification properties which make your report look good with style. Once you expnad the designs node, you will find InventTableTable with the dataset name. Right click on it and go to properties and set the style template to “TableStyleTemplate” as shown below.

On to autodesigns, we also need to set an important property called Layout Template – set it to ReportLayoutStyleTemplate as shown below

Now, lets switch to parameters node in the report. If you expand the parameters node , you will find some parameters. Lets work on AX_CompanyName parameter. By default it is hidden. Lets unhide or make it visible it as we want to display the items based on the company [dataaread id] selection by the user.
See the screen shot below

wow..we are getting closer. Now we can preview the data by right clicking the autodesign and by chosing option preview as shown below

Note: you can select the company parameter and click on the report tab to view the report. But our main aim is to deploy back this report model to AX.
To deploy the report to AOT, we have a very simple option. Right click on the SR_ReportNewModel from the soultion explorer and select option Add SR_ReportNewModel to AOT as shown below.

We are done with visual stuido development part. Now lets us switch to AX and see whether the report model has been saved to AOT or not. To do so, open your AX client and ogo to AOT >> Visual studio projects >> Dynamics AX Model projects . you should see SR_ReportNewModel project.
Also, In AOT >> SSRS Reports >> Reports >> you should see SR_InventTable report.
Now we are left out with final thing, creating menuitem for this report. This can be easily done by following hthe below process.
Go to AOT >> Menu items >> Output >> Right click and Select New Menu item and set the following properties as shown below.

Cool.. we are done and hope you are excited to view the report now. Well you can add this menu item to relevant menu and I hope you knw this process.
Now , lets open the report, Right click on the newly created menu item and select open.[You should see the following as shown below]

Since we have made the company parameter visible- we have option of generating the report based onthe dataareaid and since we have added range ItemId to the query – we get twow ranges as shown above.
I have selected ceu as my company and I am leaving ItemId as blank to view all the items in the report. Now lets run the report and see how it renders the data. [Below is the report]

Note: AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration. we shall look in to details in next posts.