Create new Financial Dimension and use on form

Perform Below steps.

a)      Open AOT>>Data Dictionary>>Extended Data Types type/select DimensionDefault and drag it in table which will be used further as a datasource in form where you have to show the Dimensions. Do Remember  that you have to drag it in table not at DataSource.
b)     Open Table in the Data, Dictionary which will be used as a Datasource, and create a realtion with table DimensionAttributeValueSet .
c)      Right Click the Relations. Select ‘New Realation’.  Select properties. Set name as DimensionAttributeValueSet, Table as DimensionAttributeValueSet.
d)     Right Click the this newly created Relation DimensionAttributeValueSet, select New>>Normal.
e)      Set the properties of Normal Realtion as:  Field=TheFieldwhichwillsaveDimensionNumberInYourTable
Source EDT= DimensionDefault
Related Field=RecId

2.      Verify that the table that will hold the foreign key to the DimensionAttributeValueSet table is a
data source on the form(the one on which you have to show dimensions).
3.      Create a tab that will contain the financial dimensions control. This control is often the only
data shown on the tab because the number of financial dimensions can be large.
4.   set properties of Tab as under
a)      Set the Name metadata of the tab to TabFinancialDimensions.
b)     Set the AutoDeclaration metadata of the tab to Yes.
c)      Set the Caption metadata of the tab to @SYS101181 (Financial dimensions).
d)     Set the NeedPermission metadata of the tab to Manual.
e)      Set the HideIfEmpty metadata of the tab to No.
       5.  Override the pageActivated method on the new tab
public void pageActivated()
{
    dimDefaultingController.pageActivated();

    super();
}
      6.   Override the following methods on the form.
class declaration
public class FormRun extends ObjectRun
{
    DimensionDefaultingController dimDefaultingController;
}
init (for the form):
public void init()
{
    super();
    dimDefaultingController=DimensionDefaultingController::constructInTabWithValues(
      true,
      true,
      true,
      0,
      this,
      tabFinancialDimensions,
      "@SYS138487");

    dimDefaultingController.parmAttributeValueSetDataSource(myTable_ds,
    fieldstr(myTable, DefaultingDimension));
}
    7.      Override the following methods on the form data source
            public int active()
{
    int ret;
    ret = super();
    dimDefaultingController.activated();
    return ret;
}
public void write()
{
    dimDefaultingController.writing();
    super();
}
public void delete()
{
   
    super();
  dimDefaultingController.deleted();
}

SQL Database Restore Query

USE [master]
ALTER DATABASE [DB Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DB Name FROM  DISK = N'\\File Path' WITH  FILE = 1
,  NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE DB Name SET MULTI_USER

GO

Get currnt company name in SSRS reports

=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.
GetFullCompanyNameForUser(
Parameters!AX_CompanyName.Value,Parameters!AX_UserContext.Value)

Serial Number in SSRS ( Normal and with Group)

we can get the sequence number directly in SSRS report directly by below code in text box expression. it's basically depends on row number so you can get it from expression -   Common Function -Miscellaneous - row Number.

=RowNumber(Nothing)

The challange to use this expression is when we will use grouping in same table. Sequesnce of the number can be disturb by grouping. We can handle it by below code.

=Runningvalue(Fields!Name.value,countdistinct,"Dataset1")

** Here field name is the actual field which we are using in grouping with database name.

Address in one line in AX or SSRS

=Replace(First(Fields!VendAddress.Value, "PurchPurchaseOrderDS"),Chr(10),", ") 

Query not taking BackSlash " \ " value in AX

As AX doesn't support back slash value "\" value in X++ code but allows the same value in data base field value (In Table Field value) , So some time it's hard to get the expected value in query . I had the same problem which i got by below solution.

Problem : -

 LedgerJournalTrans hold a value with back slash (MP\PV16\000014). When i was getting the value in query to pass it in a range it was removing "\" Automatically which was the case of data mismatch and value was not coming from table. Our report was showing blank data in report.

Solution:-  

Query doesn't recognize single back slash. so we use to use double back slash "\\" to define a single slash in a value. If we are getting a from any table then we have to convert single slash in to double slash. For that we have to type four slash "\\\\".

voucher     = strReplace(contract.parmVoucher(),"\\","\\\\");

here our value (MP\PV16\000014)contain single slash  which we are replacing with double slash by four time slash. 

AX 2012 - Understanding AOT Maps with an example

Background

Maps are not new concept in software development actually. They are table wrappers to achieve general behavior. However for lame dude(s) like me, these definition words have not been enough.

Problem

The term allotted to Maps, "Map" itself is a bit confusing. But an example can really help understanding. And Maps are used everywhere, but in comparison to tables and classes ( see these building blocks are essential for any kind of most basic development), use of maps are quite rare.

Example

I have been browsing PurchTableHistory table. A method came into my consideration where I see the use of Map.  

The method, PurchTableHistory[T] >> initFromPurchTable() declares several maps based on localized buffers. Look at the following code

// Code example

PurchTableMap purchTableMap;

purchTableMap.data(_purchTable.data());

this.data(purchTableMap.data());

First, the row from PurchTable is copied to the map variable, 'purchTableMap', and then, the history buffer gets the same row but form map, and not from the original source, purchTable buffer

WHY ? Their must be a genuine need ? Why don't we copy from the original PurchTable buffer  ? Becuase we cannot :) becuase of schema change. See if most of the fields are same but not with exact field names, types and filed list count, we cannot copy a row from one table to another using xRecord[C].data() simply because of schema mismatch.

Here is where map can work, since the different name same type and purpose fields are MAPPED in the MAP :). Fields with different names from different tables are mapped to consistent names in map, so that you can use them to map one row ( from one table buffer) to another table.

One more thing, when using maps, field count doesn't matter since the map will only care and execute on the schema defined in the map. It simply would not care for schema defined in the mapped tables, it would only care for the schema defined in the MAP

How to find or create default Dimension from X++ in AX 2012

This is static method to find or create default dimension:

static DimensionDefault createDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                               result;
    int                                                      i;                            
    DimensionAttribute                            dimensionAttribute;
    DimensionAttributeValue                   dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue
                    dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

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.

What we are doing in below Script is - 

1. Delete SSID, domain and domain user for the Admin users. First user to login after that, will be made administrator.
2. Delete the old AOS server from AX's list of servers in SYSSERVERCONFIG , SYSSQLSETTINGS and SYSSERVERSESSIONS.

3. Delete contents of SYSBREAKPOINTLIST.


Batter to take the data from Server's table before update. 

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

 

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

Sometimes you may get this error when trying to launch your report server webpage:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

While the error is descriptive enough to let you know that Reporting Services could not find the report server database, it may be a challenge to some who have no idea where this database is and what is it used for. To your surprise, there is more than one database involved.

Reporting Services requires two databases to run:

• ReportServer
• ReportServerTempDB

In short, the Report Server database is needed to store report definitions, report history and snapshots, report models, shared data sources, resources, scheduling and delivery information, metadata and several other objects. ReportServerTempDB is where Reporting Services stores session and execution data, cached reports, and work tables that are generated by the report server. For more information about Reporting Services database visit: http://msdn.microsoft.com/en-us/library/ms156016.aspx.

To solve the error mentioned above you need the following information:

1. What is the SQL Server Reporting Services instance you are trying to reach?
2. Where are the SQL Server Reporting Services databases hosted?

Assuming you have the right permissions, you will need to run the Reporting Services Configuration Manager and point to the Reporting Services instance as seen on the image below. You would normally find  Reporting Services Configuration Manager under Start->Programs->Microsoft SQL Server 2008 R2->Configuration Tools

Once you connect to your Reporting Services instance through Reporting Services Configuration Manager, go to the  Database tab located on the left pane to view the name of the SQL Server instance where the ReportServer database is being hosted and the name of the ReportServer database as seen in the image below.

the Database Name and SQL Server Name is the main point from where the system will take the connection for reports deployment. 

Keep in mind that in some environments the ReportServer database may be hosted in a separate SQL Server database instance and the ReportServer database may have been renamed to other than the default “ReportServer” database name. You will usually find this type of configuration in a scale-out architecture. For more information on scale-out architecture visit: http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx.

Once you located the SQL Server database instance where the ReportServer and ReportServerTempDB database are hosted you need to follow this checklist:

1. Is the SQL Server database instance running? Is the SQL Server service started?
2. Can you verify connectivity to the SQL Server database instance?
3. Are the ReportServer and ReportServerTempDB databases attached and online?
4. Does the Reporting Services service account have read/write access to the ReportServer and ReportServerTempDB databases?

Once all of this is verified to be true and issues have been fixed, than the last step would be to restart the Reporting Services instance. If everything is right you should be able to access your reports. It is highly recommended to frequently backup both the ReportServer and ReportServerTempDB databases. If you lose you ReportServer database you may re-deploy reports, data sources and datasets, but you will lose all subscriptions, schedules and report parts that users may have created.

Also, keep in mind that the ReportServerTempDB database does not behave like the SQL Server TempDB. Per MSDN Books on Line:

“Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.” For more details visit:http://msdn.microsoft.com/en-us/library/ms156016.aspx

Error while setting server report parameters. Error message: The report server has encountered a configuration error. Logon failed for the unattended execution account. (rsServerConfigurationError) in ax 2012

Error :-

Error while setting server report parameters. Error message: The report server has encountered a configuration error. Logon failed for the unattended execution account. (rsServerConfigurationError)

Soluction:-  The error mostly occured when we reset or change the password of the account which we are using in the services execution.


After change the password and fill it on the requiered palace we should go tot the SQL Reporting Services Account Configuration.

Choose built in Account from the Service Account and choose the SNK in file location and give a password. That is the temporary information log which could track the details only.

 
After that again login with "USE Another Account " with new password. This will update you password with all the ports and account internally.