Dynamics Ax 2012 Reporting SSRS Reports.

SQL Server 2012 Reporting Services UAC (User Access Control)

May 25, 2014 by alirazazaidi

While configuring Reporting server with Dynamics Ax 2012 R23, I got error that Dynamics Ax 2012 R3 did not validate the Reporting Server. On Opening reporting server in Browser I got following error.

“User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

After searching and trouble shooting, I found that nor Dynamics Ax Client or IE run with administrator rights.

First I run the IE with Administrator rights
From Start > All Programs > Internet Explorer, Right Click and choose Run as administrator

Enter the Reporting Server Address.

This open the Reporting server in IE.

After That I open the Dynamics Ax client By right click and open with Administrator

On Administrator section, on validate the configuration settings, and SSRS successfully configured on My Machine.

Simple Query Based Report in Dynamics Ax 2012

February 25, 2014 by alirazazaidi

The SSRS reports need a data source from where data can be fetched and displayed on the report. Since we are going to develop query based report therefore we will use an AOT query as a data source.

For creating report, First we have to create A AOT Query. For this we have A simple AOT query base on Bank Deposit table

BankDeposit table.

By Default there is no field selected under field.

Click on it and set dynamic property to ‘YES’, so all possible filed will appear on table.

You can see all the fields of the table.

Now open Visual studio and create a new SSRS report named it as QueryBasedReport. Use this query as data source

Now you need to create a data set, QueryBasedReportDS, that will refer to an AOT query created earlier. In data set properties set ‘Data Source Type’ to ‘Query’ and in query property select the AOT query SampleQuery created earlier.

All the fields are visible under Fields node of QueryBasedReportDS. These fields won’t be visible if you haven’t set ‘Dynamic’ property of Fields to ‘Yes’ in AOT query and you won’t be able to see any field on report. Now add an auto design by right clicking on Design node and name it Report. Drag the data set of your report “QueryBasedReportDS” to report design named ‘Report’.

Make sure that the table you are using has some records in it. If there is data in table and still you are unable to see records on report, you might need to do following steps. Go to ‘Parameters’ node of your report ‘QueryBasedReport’.Right click on AX_ComapnyName and select properties. Set the value of the property ‘Visibility’ from Hidden to Visible and save the changes.Now right click on report design to preview it. Following dialog will appear. Enter AX_CompanyName ‘CEU’.

Now click on Report tab. Following report output will be shown.You can also view the developed report from browser using following url http://localhost/reports but before this you need to deploy the report on report sever. This can be done by right clicking the solution in

visual studio and selecting deploy. default.

If this service is not started, start it. If it is already started, you need to restart it. After restarting the service, go to visual studio and deploy the solution. Your report is successfully deployed on the server.

Now you can see the report result from any browser. Go to http://localhost/Reports click on DynamicsAX folder and click on the report you want to run. In our case, it is QueryBasedReport.Report. If you can’t find any record, change the company name from DAT to CEU.

Now you can see data on the report.

Running report Inside Dynamics AX Client, you have to output menu item. Save following properties and save the changes.

You will see records on the report.

So these were the errors and solutions, I came across so far. I hope it helps. If you have encountered some more, please do share. Keep rocking 🙂

Power script commands and Dynamics Ax 2012 SSRS reports.

January 13, 2014 by alirazazaidi

When you install dynamics Ax 2012, default reports automatically deployed. But sometimes we have to deployed the reports manually. Dynamics Ax provides build in PowerShell commands to perform various operations. For testing these commands, you have to run Power shell Management Shell as Administrator.

$reports = Get-AXReport -ReportName *
$reports

Above power shell scripts helps to get the list of Dynamics Ax 2012 SSRS reports available for deployment.

We can also filter the list of report . Dynamics Ax 2012 provide the only two filter options Report Name and changed date. You can use following statement.

$reports | Select-Object Name,ChangedDate

For example if we want all reports similar to ABC classification we can filter something like this

$reports | Select-Object Name,ChangedDate | Where { $_.Name -like "ABC*" }

If reporting Server is install and configured with Dynamics Ax 2012, we can use following statements to deploy all available reports.

If we want to deploy a single required report we have to use following statement.

Publish-AXReport –Id SSRSConfigID -ReportName CustTransList

In my case SSRSConfigID will be machine name

Publish-AXReport –Id dynamicsworld -ReportName CustTransList

If we have to deploy all reports then we can use following statement.

Publish-AXReport –Id SSRSConfigID –ReportName *

Above statement also works if we did not use SSRSConfigID.

Publish-AXReport –ReportName *

 
if you configured the multiple AOS and multiple reporting server then following command works

Publish-AXReport -Id SSRS_2712 –ReportName * -RestartReportServer -ServicesAOSName  <MACHINE NAME> -ServicesAOSWSDLPort 8101

Dynamics Ax 2012: How to duplicate the existing report for modification

November 14, 2013 by alirazazaidi

I got task to extend the existing dynamics ax SSRS report. Client also wants to sustain the existing report. They want a new report that will be replicate the original one with extended features. Data provider classes are easily duplicated by right clicking on it and select duplicate, I renamed the duplicate classes in AOT. Initially I did not change anything in renamed classes so duplicate report will work with old data set.

But I did not find any duplicate option on AOT. After R&D I found that report can duplicate in visual studio. I create a new visual studio project Report project. Open Application Explore, Expand SSRS report node and drag it into visual studio project. Visual studio automatically duplicated the existing report

You can open Application explorer in Visual Studio è Viewè Application Explorer.

. Now the duplicated report will pointed to old Data provider class. Next step I copied the names of data set fields and parameters and properties into Notepad. When I point the data set to duplicated classes, a new data set created, and parameters became duplicate. I removed the old parameters and set the new parameters with new names and their properties to as they were in deleted parameter. After this I checked that report layout is working with new data Set, Now I started modification in report and classes with requirements.

Dynamics Ax 2012: How to Edit the Existing Report.

November 10, 2013 by alirazazaidi

In Ax Dynamics usually work is pop out regarding customization of what is existing In Dynamics Ax. Usually is one of them is Report customization. If you are new in Dynamics Ax, Report locate and open mode will create more headache then modifying it. Let’s explore what steps require to open report in Edit Mode.

First step is determining the Name of report. You can identify it by menu item from it is running.

For example we want to edit the following report as per MSDN link

Menus > InventoryAndWarehouseManagement > Reports >BaseData > Quantity setup

Now Go to AOT and expend InventoryAndWareHouseManagement and right click to view the properties of Quantity Setup.

From Property you can identify the Name of MenuItem and its type. The menu item for Quantity setup is InventTableQuantity.

Now expand from AOT and Expend Menu Items and then Output Node. Because there are too many menu Items in AOT Node so you have to type when you reach on InventTableQuantity right click on it and explore the properties. You can find the Object property described the report Name of Quatity Setup is InventTableQuantity.

Now third step is go to locate the Visual Studio Projects and then Edit it.

For this purpose in the AOT, expand the node for Visual Studio Projects and then expand the node for Dynamics Ax Model Project. The project name will be similar to report Name, There are too much visual studio project so type InventTablQuantyReport. Right click on it and select edit.

Report project will be open to Visual studio, where you can modify.

Dynamics Ax 2012 : “Only integrated security is supported for AX queries.”

October 19, 2013 by alirazazaidi

Recently I update my dynamics Ax development Environment by restoring Database from Production Dynamics AX Db. After the restoring the database, when I run any SSRS report form Dynamics Ax, it starts to give error “Only integrated security is supported for AX queries.”

The reporting Services are working perfectly other than Dynamics Ax reports.

One way to apply the Windows Integrated Security on required report. By following way

Open Reporting Services Configuration Manager ( All Programs > Microsoft SQL Server 2008/2012 > Configuration Tools > Reporting Services Configuration Manager)
Go to “Report Manager URL” Tab and click on URL
Typically, URL will be in the format http://<ServerName>:80/Reports
Select the Report under Dynamics Ax Folder

In the opened page, select Data Sources on the left pane.
And apply Windows integrated security and click Apply.

But I found that error on all SSRS reports for Dynamics Ax 2012. For this, best way to delete all reports and redeploy them.
Steps are as follows:

First step to delete all reports, by selecting Dynamics Ax folder in Report server and delete it, this will all reports inside the reporting reports.

Then go to Report servers form in Dynamics Ax 2012 and click on “create report folder” button to create the Folder Dynamics Ax again

Now you can open Powershell (Administrative Tools > Microsoft Dynamics Ax 2012 Management Shell). Make sure you run Powershell as Administrator.
Deploy all the reports with the help of command:
Publish-AXReport -ReportName *
Wait for completing the report deployment after it all reports will run fine.

Dynamics Ax 2012: Very simple report in dynamics ax 2012

January 29, 2013 by alirazazaidi

There two common and mostly used ways to develop reports in dynamics Ax 2012

Query
Report data Provider.

Here we have to create a very simple report using AOT query. For this purpose I have create a custom table Student with 4 fields as

Rollnumber
First Name
Last Name
Address

The sample data in custom table is as follow.

Now we create a new Query in AOT which will be used in our Report to get data from AX to display on report.

In Query node of AoT create a new query Named it StudentsQuery.

Right click and on data source click on add new data source and on property window select or right Student table as follow.

Click on Fields and from property window dynamics to yes. Like as

Now open a new Project In Visual studio 2010. Select form Install templates select Dynamics and then Report Model

Now next step to add a new report in newly created Report Model project

I named it simple student report.

Now right click on Dataset and add a new dataset in report and Name it StudentDs and from property of data set you have set following property

Click on Query and form dialog box you have to search and select Query selected Query form Dynamics Ax. In this report we created the query with StuentsQuery. On selecting and press next a new dialog open you from here you can select fields required for report in this report we are just use 4 fields

After selecting field now create a new precision design. Add new table and drop the field on column of table as follow. Press on review pane you find result like as

Dynamic Ax 2012: Complete guide to Dynamics Ax 2012 Reporting

January 28, 2013 by alirazazaidi

Hi folks, Today i am searching for sample to write a blog entry for Ax deport development, i found very informative link at msdn which covers all portions of report development in Ax 2012.

http://msdn.microsoft.com/en-us/library/cc653472.aspx

Regards

Ali Raza Zaidi

Dynamics Ax 2012 : Exploring the Radio buttons as a report parameter

January 27, 2013 by alirazazaidi

It is common scenario in report development to use check boxes or radio button as parameter. Any Boolean variable define at data contract class become check boxes at report dialog. But how we make radio button on report dialog. When we require radio button then it means we have more than two values and one value is required to select. For this purpose we have to create a new enum at AOT. Let see we create a new base enum with Name Enum student.

Right click on EnumStudent and open property window and set Style property as Radio button

Now you have to create a new data contract class where we define parameter that will contain the selected value for enum.

[DataContractAttribute]

public class StudentDataContacts

{

EnumStudent enumStudent;

}

[ DataMemberAttribute('Student By'),SysOperationLabelAttribute('Student By')

]
public EnumStudent parmStudent(EnumStudent _EnumStudent =

enumStudent )

{

enumStudent = _EnumStudent;

return enumStudent;

}

When we deploy the report and run from menu Item, Report dialog will be appear like as follow screenshot.

Now questions is that how we used this radio button parameter.

At report level, we can use this radio button parameter for grouping or such other operations. It return string value. For example I used here to just display what value of radio button selected in report Expression as

We can use this parameter inside the Process report method of Data Provider class as

public void processreport()
{
EnumStudent _EnumStudent;
str _StudentType;
StudentDataContact _DataContact;

_DataContact = this.parmDataContract();

_EnumStudent= _DataContact.parmStudent();

_StudentType=enum2str(_EnumStudent);

_TempStudentList.Firstname ="Ali";
_TempStudentList.LastName="Raza";
_TempStudentList.StudentType=_StudentType;
_TempStudentList.insert();

_TempStudentList.Firstname ="Raza";
_TempStudentList.LastName="Ali";

_TempStudentList.StudentType=_StudentType;
_TempStudentList.insert();

}

Hope this helps.

Dynamics Ax 2012: Exploring the Dynamics Grouping in Dynamics Ax SSRS reports.

January 25, 2013 by alirazazaidi

Dynamics Ax provide complex and interactive reports because they help gain valuable insights. One common report requirement is dynamic grouping which allows the users to choose which fields to group their data around.

In SSRS, dynamic grouping can be set up using the following steps.

Define the grouping parameters

To enable a dynamic grouping on a set of fields, create a parameter. List the fields you want to use for grouping and add labels. If you want grouping to be optional, also include null as a value (with a label like “None”).

Click on Parameters, and then Add Parameter. In the Report Parameter Properties window, set the parameter properties.

Select Available Values in the Report Parameter Properties window and add label and value pairs for all groups. Some times Report level parameter is not work, then you have to define this parameter at Data contract class. And used it as report level parameter.

Repeat these steps for each group of fields that you wish to enable dynamic grouping.
Create the report with grouping
Create a table report with the dynamic groups you set up. Groups like month, year and category are very common.

Set the group expression
Open the Group Properties window and Add the desired group expression.
Here is an sample expression where we perform some basic checks on our Month, Year and Category grouping and set the default display.
iif (Parameters!Group1.Value is Nothing,1,Fields(iif(Parameters!Group1.Value is Nothing, “Year”,Parameters!Group1.Value)).Value)
The outer iif checks for empty parameters and displays the entire group if this condition is met.
Otherwise, the second iif checks if the parameter value is Nothing which means none is selected from the drop-down and then it will return the value of the Year field without using it for any sorting or grouping.

Change the grouping display value (label) on basis of selected report parameter
The final step is to change the group label depending on the display value.
In this example, we are setting the Null value to display “Year” as the label in order to match the behaviour in the previous expression.
=Fields(if(Parameters!Group1.Value is Nothing “Year”, Parameters!Group1Value)).Value

« Previous Page

Dynamics Ax 2012 Reporting SSRS Reports.

“User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”