Dynamics AX 2009: Creating a simple SSRS Report

Dynamics AX 2009 features support for writing reports using SQL Server Reporting Services. In this post, I’m going to show you how to create a simple SSRS report that accesses data in the AX database.

The overall process I will describe will focus on the first stage of authoring and deploying the report from the developers perspective.

In future posts I’ll cover

• Formatting the report
• Adding menu items to launch the report
• Security
• Accessing OLAP data
• etc.

The basic steps

• Create an AX Query
• Create a new AX Reports Project and a new report in that project
• Create a dataset in the report bound to that query
• Create a design to report to render the dataset
• Preview the design
• Deploy the design to SSRS
• View the report via IE
• Save the Report project back into AX

The report I will create will be a simple unformatted table of customers and customer IDs.

Create an AX Query

Launch the Dynamics Client

Open the AOT

In the AOT, right click on Queries and select New Query

By default a new query will be create with a default name (in this case “Query1”)

Right-Click on that query and select Rename

Give it the name “DemoQueryCustomers” and hit RETURN

Expand that query in the AOT

Right-click on Data Sources and click New Data Source

A new data source will be created and by default attached to some table (in this case it will be called “Address_1”

If you hover over this datasource you will see the query string

Right click on that data source and select Properties

The property window for that data source will appear

Navigate to the Table property and change it to the “CustTable”

Click on the Name property

You’ll notice that the Name changes to “Cust_Table_1”

Close the property window

Click Save All in the AOT

Close the AOT

Create a new AX Reports Project and a new report in that project

Launch Visual Studio 2008

File > New > Project

The New Project dialog will launch

Under Project Types, select Visual C#  / Dynamics

Under Templates select Dynamics AX Reporting Project

Change the Name to “DemoReportsLibrary1”

Click OK

An new AX Report Library project is created

By default it contains a report called “Report1”

Right click on this report and rename it to “DemoReportCustomers”

Create a dataset in the report that is bound to the AX query

In the report, right click on Datasets and select new Dataset

By default the name of the Dataset is “Dataset1".

Rename it to “DataSetCustomers”

In the properties window for the Dataset

Verify that the Data Source is “Dynamics AX”

Verify that the Data Source Type is “Query”

Verify that the Default Layout is “Table”

Click on the Query field

Click on the ellipsis button in the value for the Query field

A dialog will launch that will list all the queries in AX. It will take a few seconds to populate this dialog with all the queries so be patient.

Under Select Query, choose the query you previously created “DemoQueryCustomers”

After you select that query the right column will be filled with fields from the query.

By default All will be selected.

Uncheck All

Check the AccountNum field

Check the City field

Check the Name field

Check the State field

Click OK to close the dialog

You’ll now see that ”DataSetCustomers” contains all the fields you selected.

Select “DataSetCustomers” and drag it into the “Designs” node

After you finish dragging, you’ll see that a design has been created. It will be given the name “AutoDesign1”

Preview the design

With AutoDesign1 selected, click Preview in the toolbar

You’ll notice a message at the top of the preview saying “The design has validation warnings” and you can see the warnings in the error list at the bottom.

We’ll ignore this for now.

Click the Save icon to save the report

Deploy the design to SSRS

Right click on the solution and select Deploy

At the status bar in the bottom you’ll see a message on the left and some animation on the right indicating that the deployment is in progress.

Eventually it will say “Deployment succeeded” in the status bar

View the report via IE

Launch IE and navigate to your SSRS reports server (in this example it is http://isotopex1:81/reports/)

Navigate into the Dynamics link

Find the report we deployed

It will be listed as “DemoReportsLibrary.DemoReportCustomers.AutoDesign1”

Click on it to view the report

And now you’ll see the report

Save the Report project back into AX

Close IE

In the solution, right click the report project (not the solution) and select Save to AOD

Open the Dynamics AX client

Open the AOT

In the AOT expand the Report Libraries node

you’ll see the report library “DemoReportsLibrary” is now in AX

if you need to edit the report library again just locate it in the AOT, right-click it and select Edit in Visual Studio

Limit Number of records in AX 2009 reports

If you want to limit number of records in report upto 30 then following are the steps.

1-Goto saleconfirm report in AOT.

2-In class declaration declare variable.

int rowcount;

3-In init method initialize rowcount variable.

rowcount = 0 ;

4-Goto body section of report and override exectionsection method and write the code below


rowcount +=1;

if(rowcount == 31)
{

element.newPage();
rowcount=1;

}

super();

Select , Insert , update data record from AX 2009 / AX 2012 to Another SQL Server DataBase

static void UpdateEmplAtdDB(Args _args)
{
    ODBCConnection myODBC;
    Statement myStatement;
    LoginProperty myLoginProperty;
    Resultset myResultset;

    ODBCConnection ATDODBC;
    Statement ATDSelStatement;
    Statement ATDInsStatement;
    Statement ATDUpdStatement;

    LoginProperty AtdLoginProperty;
    Resultset AtdSelResultset;
    Resultset AtdInsResultset;
    Resultset AtdUpdResultset;

    str mySQLStatement;
    str myConnectionString;

    str AtdSelSQLStatement;
    str AtdInsSQLStatement;
    str AtdUpdSQLStatement;
    //str AtdConnectionString;

    str myDSN="STGAXDSN";
    str myUserName="Username";
    str myPassword="Password";

    str FEILD1 ;
    str FEILD2;
    str Status = "";
    str AtdStatus = "";

    //-------------//

    str AtdFEILD1 ;
    str AtdFEILD2 ;
    int _ID;
    ;

    myConnectionString=strfmt("DSN=%1;UID=%2;PWD=%3",myDSN,myUserName,myPassword);

    myLoginProperty = new LoginProperty();
    myLoginProperty.setOther(myConnectionString);
    //myLoginProperty.setDSN("STGAXDSN");
    myLoginProperty.setDatabase("AXDB");


    AtdLoginProperty = new LoginProperty();
    AtdLoginProperty.setOther(myConnectionString);
    //AtdLoginProperty.setDSN("STGAXDSN");
    AtdLoginProperty.setDatabase("ATNDB");


    // AX Data Collection
    try
    {
        myODBC = new OdbcConnection(myLoginProperty);
        myStatement=myODBC.createStatement();

        //mySQLStatement="SELECT FEILD 1, FEILD 2 FROM TABLE";
        //info(strfmt("%1", myStatement));
        myResultSet=myStatement.executeQuery(mySQLStatement);
        //info(str2int(myResultSet));
        while (myResultSet.next())
        {

            FEILD1 = myResultSet.getString(1);
            FEILD2   = myResultSet.getString(2);
            if(FEILD1 != "")
            {
                Status = FEILD1;
            }
            else {Status = "";}


            // ATD Data Collection


            ATDODBC = new OdbcConnection(AtdLoginProperty);
            ATDSelStatement=ATDODBC.createStatement();
            //info("FEILD1 : " +FEILD1);
            //info("Status : " + Status);

           AtdSelSQLStatement="SELECT FEILD1 , FEILD2 FROM ATDTABLE "
            +"where FEILD1 = '" + FEILD1+ "'";
            AtdSelResultset=ATDSelStatement.executeQuery(AtdSelSQLStatement);

            while (AtdSelResultset.next())
            {
                AtdFEILD1 = AtdSelResultset.getString(1);
                AtdFEILD2 = AtdSelResultset.getString(2);
            }

            // ATD Insert Data Collection
            if(AtdFEILD1 != FEILD1)
            {
                AtdInsSQLStatement="INSERT INTO AtdTABLE "
                +"(FEILD1,FEILD2) "
                +"VALUES "
                +"('"+FEILD1+"','"+ FEILD2+"')";


                ATDODBC = new ODBCConnection(AtdLoginProperty);
                if (ATDODBC)
                {
                        ATDInsStatement=ATDODBC.createStatement();
                        _id = ATDInsStatement.executeUpdate(AtdInsSQLStatement);
                }
            }
            else
            {
                         // ATD UPDATE Data Collection
       
              AtdUpdSQLStatement= "UPDATE AtdTABLE ";
              AtdUpdSQLStatement= AtdUpdSQLStatement+"SET ";

                                 if(FEILD1!=""){ AtdUpdSQLStatement= AtdUpdSQLStatement +"FEILD2= '"+ FEILD2+"', ";}
                                            else{ AtdUpdSQLStatement= AtdUpdSQLStatement +"FEILD2= '"+ atdFEILD2+"', ";}
                              
                               AtdUpdSQLStatement= AtdUpdSQLStatement+"WHERE FEILD1 = '"+FEILD1+"' ";
               // info(FEILD1+"','"+ FEILD2 );
                ATDODBC = new ODBCConnection(AtdLoginProperty);
                info("Update "+AtdUpdSQLStatement);
                if (ATDODBC)
                {

                        ATDUpdStatement=ATDODBC.createStatement();

                        _id = ATDUpdStatement.executeUpdate(AtdUpdSQLStatement);

                }
            }
        }
    }
    catch
    {
        error('Unexpected error');
    }

}

Date Format and Add Date Time Using SQL

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

DATEADD (datepart , number , date )

Perfect date Format :

select 

convert(varchar(10),VALIDATEDDATETIME,103)+' - '+ 

REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,

DATEADD (hh , 5 ,VALIDATEDDATETIME),100),7)),7),'AM',' AM'),'PM',' PM')) 

as ValidTime, 

this represent Date -- convert(varchar(10),VALIDATEDDATETIME,103)+' - '+

this represent Time --

REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,DATEADD (hh , 5 ,VALIDATEDDATETIME),100),7)),7),'AM',' AM'),'PM',' PM')) 

as ValidTime,  

Datepart

Is the part of date to which an integernumber is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

 Useful link for Format Structure

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime 

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                                        – Oct  2 2008 11:01AM          

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                                        – 02 Oct 2008 11:02:07:577     

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                                        – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

————

SELECT , INSERT AND UPDATE THROUGH AX 2012 TO SQL SERVER 2008

Fetch and Insert Data From Axapta to Access Database

void clicked()


{



loginProperty loginProperty = new LoginProperty();

ODBCConnection odbcConnection;

Statement sql;

Statement sql2;

ResultSet result;



int _id;

str _statement = "Insert into mcsProjectTable ([Project Name],[Project Description],[Project Owner],[Project Owner Contact Name],[Project Owner Email],[Project owner Phone Number]," ;

_statement=_statement + "[Under Bidding],[Awarded],[Main Contractor],[Main Contractor Address],[Main Contractor Email],[Main Contractor Contact Person],[Main Contractor Phone Number],";

_statement=_statement + "[Main Contractor1],[Main Contractor1 Address],[Main Contractor1 Email],[Main Contractor1 Contact Person],[Main Contractor1 Phone Number],[Main Contractor2],[Main Contractor2 Address],[Main Contractor2 Email],";

_statement=_statement + "[Main Contractor2 Contact Person],[Main Contractor2 Phone Number],[Main Contractor3],[Main Contractor3 Address],[Main Contractor3 Email],[Main Contractor3 Contact Person],[Main Contractor3 Phone Number],";

_statement=_statement + "[Status Over All],[Location],[Scope1],[Scope2],[Scope3],[Scope4],[SPG INQUIRY Number],[Notes],[Region],[SalesMan],[Estimation Engineer])";

_statement=_statement + "values ("+ "'" + ProjectName.text() + "'" + "," + "'" + ProjectDescription.text() + "'" + "," + "'" + ProjectOwner.text() + "'" + "," + "'" + ProjectOwnerContactName.text() + "'" ;

_statement=_statement + "," + "'" + ProjectOwnerEmail.text() + "'" + "," + "'" + ProjectOwnerPhoneNumber.text() + "'";

_statement=_statement + "," + "'" + UnderBidding.text() + "'" + "," + "'" + Awarded.text() + "'";

_statement=_statement + "," + "'" + MainContractor.text() + "'" + "," + "'" + MainContractorAddress.text() + "'";

_statement=_statement + "," + "'" + MainContractorEmail.text() + "'" + "," + "'" + MainContractorContactPerson.text() + "'";

_statement=_statement + "," + "'" + MainContractorPhoneNumber.text() + "'" + "," + "'" + MainContractor1.text() + "'";

_statement=_statement + "," + "'" + MainContractor1Address.text() + "'" + "," + "'" + MainContractor1Email.text() + "'";

_statement=_statement + "," + "'" + MainContractor1ContactPerson.text() + "'" + "," + "'" + MainContractor1PhoneNumber.text() + "'";

_statement=_statement + "," + "'" + MainContractor2.text() + "'" + "," + "'" + MainContractor2Address.text() + "'";

_statement=_statement + "," + "'" + MainContractor2Email.text() + "'" + "," + "'" + MainContractor2ContactPerson.text() + "'";

_statement=_statement + "," + "'" + MainContractor2PhoneNumber.text() + "'" + "," + "'" + MainContractor3.text() + "'";

_statement=_statement + "," + "'" + MainContractor3Address.text() + "'" + "," + "'" + MainContractor3Email.text() + "'";

_statement=_statement + "," + "'" + MainContractor3ContactPerson.text() + "'" + "," + "'" + MainContractor3PhoneNumber.text() + "'";

_statement=_statement + "," + "'" + Status.text() + "'" + "," + "'" + Location.text() + "'";

_statement=_statement + "," + "'" + Scope1.text() + "'" + "," + "'" + Scope2.text() + "'";

_statement=_statement + "," + "'" + Scope3.text() + "'" + "," + "'" + Scope4.text() + "'";

_statement=_statement + "," + "'" + SPGInquiryNumber.text() + "'" + "," + "'" + Notes.text() + "'";

_statement=_statement + "," + "'" + Region.text() + "'" + "," + "'" + SalesMan.text() + "'";

_statement=_statement + "," + "'" + EstimationEngineer.text() + "'" + ")" ;









loginProperty.setDSN('AccessDSN');



odbcConnection = new ODBCConnection(loginProperty);

if (odbcConnection)

{

sql = odbcConnection.createStatement();

_id = sql.executeUpdate(_statement);



if(_id ==0 )

{



sql2 = odbcConnection.createStatement();

result = sql2.executeQuery("select * from mcsProjectTable");





delete_from referencedProjectFormTable ;



while (result.next())

{

// referencedProjectFormTable.ProjectID = result.getInt(1);

referencedProjectFormTable.ProjectName = result.getString(2);

referencedProjectFormTable.ProjectDescription = result.getString(3);

referencedProjectFormTable.ProjectOwner = result.getString(4);

referencedProjectFormTable.ProjectOwnerContactName = result.getString(5);

referencedProjectFormTable.ProjectOwnerEmail = result.getString(6);

referencedProjectFormTable.ProjectOwnerPhoneNumber = result.getString(7);

referencedProjectFormTable.UnderBidding = result.getString(9);

referencedProjectFormTable.Awarded = result.getString(10);

referencedProjectFormTable.MainContractor = result.getString(12);

referencedProjectFormTable.MainContractorAddress = result.getString(13);

referencedProjectFormTable.MainContractorEmail = result.getString(14);

referencedProjectFormTable.MainContractorContactPerson = result.getString(15);

referencedProjectFormTable.MainContractorPhoneNumber= result.getString(16);

referencedProjectFormTable.MainContractor1= result.getString(17);

referencedProjectFormTable.MainContractor1Address= result.getString(18);

referencedProjectFormTable.MainContractor1Email= result.getString(19);

referencedProjectFormTable.MainContractor1ContactPerson= result.getString(20);

referencedProjectFormTable.MainContractor1PhoneNumber= result.getString(21);

referencedProjectFormTable.MainContractor2= result.getString(22);

referencedProjectFormTable.MainContractor2Address= result.getString(23);

referencedProjectFormTable.MainContractor2Email= result.getString(24);

referencedProjectFormTable.MainContractor2ContactPerson= result.getString(25);

referencedProjectFormTable.MainContractor2PhoneNumber= result.getString(26);

referencedProjectFormTable.MainContractor3= result.getString(27);

referencedProjectFormTable.MainContractor3Address= result.getString(28);

referencedProjectFormTable.MainContractor3Email= result.getString(29);

referencedProjectFormTable.MainContractor3ContactPerson= result.getString(30);

referencedProjectFormTable.MainContractor3PhoneNumber= result.getString(31);

referencedProjectFormTable.StatusOverAll= result.getString(32);

referencedProjectFormTable.Location= result.getString(33);

referencedProjectFormTable.Scope1= result.getString(34);

referencedProjectFormTable.Scope2= result.getString(35);

referencedProjectFormTable.Scope3= result.getString(36);

referencedProjectFormTable.Scope4= result.getString(37);

referencedProjectFormTable.SPGInquiryNumber= result.getString(38);

referencedProjectFormTable.Notes= result.getString(39);

referencedProjectFormTable.Region= result.getString(41);

referencedProjectFormTable.SalesMan= result.getString(42);

referencedProjectFormTable.EstimationEngineer= result.getString(43);

referencedProjectFormTable.insert();

}



ProjectFormTable_ds.refresh();

odbcConnection=null;

info("Record Added");





}



}

}

SharePoint 2013 Installation on Windows Server 2012 -- Part B

SharePoint 2013 Installation

After installing the operating system & database server we can continue with the actual SharePoint 2013 Installation. Run the setup.bat from the extracted files root folder.

Install software pre-requisites

Click on the Install software prerequisites link to automatically install all the pre required software.

After the above step you can proceed with the actual installation.

Choose the installation as Standalone and click the Install Now button as shown below.

Wait for a few minutes for the installation to be completed.

Once the installation is completed you will be launched with the Products Configuration Wizard.

Specify your database name and credentials in the following page:

Enter the Farm Security Phrase of your own:

Wait for the configuration activities to be completed. This should take around 10 minutes.

Once the Configuration is done you will get the following message. The textboxes shows your appropriate machine parameters.

Central Administration

Following is the screen shot of Central Administration in SharePoint 2013. You can open this from the Start Menu.

Click on the Start the Wizard button as shown above to configure your farm settings. Once the farm configuration is done you should be able to see the following screen.

Default Site

Now you can try opening the default site installed. It should be accessible by the machine name itself as shown below:

• http://localhost
• http://machinename

Following is the blank site template of the default site.

Corrupted Default Site

If you get a time out error or a Connection Close message then probably the default site collection is corrupted. You can do the following activities to fix it:

1. Delete the port 80 site from Central Administration
2. Create a new web application with port 80
3. Add a new site collection to the new web application
4. Try accessing the site using the above URLs

Ensure that you have valid entries in the system32\drivers\etc\hosts file.

Virtual Machine Links

Following are some virtual machine links.

• Link 1
• Link 2

References

• http://msdn.microsoft.com/en-us/library/jj162979(v=office.15).aspx

Summary

In this article we have seen the installation of SharePoint 2013 on Windows Server 2012. Summarizing the activities we have performed the following:

1. Windows Server 2012
2. SQL Server 2012
3. SharePoint 2013
4. Internet Information Services 8.0

Please let me know any problems you face so that I may be able to guide you. In the upcoming article I will come up with the new features of SharePoint 2013.