Sunday, 16 September 2012

SSRS Report

What is SSRS..???
SQL Server Reporting Services (SSRS): is a server-based report generation software system. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface.
Report Server: This is the primary database that stores all the information about reports that was originally provided from the RDL files used to create and publish the reports to the ReportServer database. In addition to report properties (such as data sources) and report parameters, ReportServer also stores folder hierarchy and report execution log information.

ReportServerTempDB: This database houses cached copies of reports that you can use to increase performance for many simultaneous users. By caching reports using a nonvolatile storage mechanism, you make sure they remain available to users even if the report server is restarted.

The SSRS Report Server
•          The SSRS report server plays the most important role in the SSRS model. Working in the middle, it’s responsible for every client request to render a report or to perform a management request, such as creating a subscription. You can break down the report server into several subcomponents by their function:
•           Programming interface
•           Authentication Layer (new to SSRS 2008)
•           Report processing
•           Data processing
•           Report rendering
•           Report scheduling and delivery




Reporting Framework Jargons

One of the most noteworthy changes which we have seen in this release of Microsoft Dynamics AX 2012 is that we’ve migrated the reporting framework from the X++ reporting framework to Microsoft SQL Server Reporting Services.
MS has introduced a robust reporting framework wrapping over the basic SSRS reporting functionality. There are many terms used in reporting framework in AX as discussed here:

Report Definition Language: RDL is an XML application primarily used with Microsoft SQL Server Reporting Services. RDL is usually written using Visual Studio. AX has Report Definition Language Contract classes that can generate and build the RDL for an AX SSRS report. This contract provides a weakly typed representation of parameters. It contains methods that can be used to get or set values. It also contains a map of parameter names and the SrsReportParameter class. The base class is SrsReportRdlDataContract.

Report Data Provider (RDP): A framework that helps in building, processing and rendering data to reports. Most of the reports require RDP classes that help in implementing business logic required to process data and provide data in readable, presentable and required formats design. The base class is SrsReportDataProvider. This class has two main sub classes, SrsReportDataProvderBase and SrsReportDataProviderPreProcess.

Report Data Contracts: The Report Data Contracts framework is used to provide and manage the parameters to an SSRS report. The report data contract contains all the other relevant instances like Report Data Provider contracts, print contracts, RDL contracts and query contracts that will be used by a report.

Printing Contracts: The framework that manages report printing (to different mediums). The base class is SrsPrintDestinationSettings. There are other supporting contracts that are used for printing, we will discuss about them in future posts.

Query Contracts: This framework manages the queries used to process report data. This framework is also responsible for providing dynamic filters (similar to our ‘Select” buttons on report dialogs that open the Query specification form to filter data on report queries).

Report Controllers: Report controllers control the report execution and dialog forms. Report controllers can be used to modify report dialogs, validate report parameters and other validations necessary before report execution. The base class is SrsReportRunController. Reports utilizing report controllers can only be used for printing data on client side. Reports controlled by controllers cannot be used in Enterprise Portals.

Report UI Builders: UI Builders are used to modify the report dialogs at run-time or to add additional parameters and write custom business logic to report dialogs. Ex: You want to perform some task based on data modified for one parameter, that affects other parameters or build a custom lookup etc (something that was provided by RunBaseReport framework class in previous versions. The base class is SrsReportDataContractUIBuilder.


http://bhushan.extreme-advice.com/chart-report-in-ssrs/ // Bar and Chart reports
http://www.mssqltips.com/sqlservertip/2483/ssrs-developer-interview-questions/
http://mfmujahidmim.wordpress.com/2012/12/18/simple-ui-builder-class/ - Builder class
==============================================================================
DP Class :
[
    SRSReportQueryAttribute(queryStr(ProjHourUtilisationQry)),
    SRSReportParameterAttribute(classStr(ProjHourUtilisationContract))
]
class ProjHourUtilisationDP extends SRSReportDataProviderbase
{
    QueryRun queryRun;
    ProjHourUtilisationTemp projHourUtilisationTemp;
    Query q;
    ProjId projid;
    ProjCategoryId categoryId;
    ProjWorkerRecId projWorkerRecId;
    FromDate fromDate;
    ToDate toDate;
    Bitmap                              companyLogo;
}
[
SRSReportDataSetAttribute(tableStr(ProjHourUtilisationTemp))
]
public ProjHourUtilisationTemp getProjHourUtilisationTemp()
{
    select * from projHourUtilisationTemp;
    return projHourUtilisationTemp;
}
private Query buildQuery(Query  _query, ProjId _projid,ProjCategoryId _CategoryId,ProjWorkerRecId _projWorkerRecId,FromDate _fromDate,ToDate _toDate)
    {
        if(_projid!="")
      _query.dataSourceTable(tablenum(ProjEmplTrans), 1).addRange(fieldnum(ProjEmplTrans,ProjId)).value(queryValue(_projid));
        if(_CategoryId!="")
        _query.dataSourceTable(tablenum(ProjEmplTrans), 1).addRange(fieldnum(ProjEmplTrans,CategoryId)).value(queryValue(_CategoryId));
        if(_projWorkerRecId !=0)
        {
        if(_projWorkerRecId)
        {
        _query.dataSourceTable(tablenum(ProjEmplTrans), 1).addRange(fieldnum(ProjEmplTrans,Worker)).value(queryValue(_projWorkerRecId));
        }
        }
        if(_fromDate && _toDate )
        {
             _query.dataSourceTable(tablenum(ProjEmplTrans), 1).addRange(fieldnum(ProjEmplTrans,TransDate)).value(queryRange(_fromDate,_toDate));
        }
        return _query;
    }
private void getReportParameters()
{
    ProjHourUtilisationContract ProjHourUtilisationContract = this.parmDataContract();
    if (ProjHourUtilisationContract)
    {
        projid=ProjHourUtilisationContract.parmProjectId();
        categoryId = ProjHourUtilisationContract.parmCategory();
        projWorkerRecId = ProjHourUtilisationContract.parmEmployeeId();
        fromDate = ProjHourUtilisationContract.parmFromDate();
        toDate    = ProjHourUtilisationContract.parmToDate();
    }
 }
[SysEntryPointAttribute(false)]
public void processReport()
{
        ProjEmplTrans projEmplTrans;
    ;
   this.getReportParameters();
    queryRun = newQueryRun(this.buildQuery(this.parmQuery(),projid,categoryId,projWorkerRecId,fromDate,toDate));
    while(queryRun.next())
    {
        projEmplTrans=queryrun.get(tableNum(ProjEmplTrans));
         this.InsertintoProjHourUtilTmp(projEmplTrans);
    }
}
public void InsertintoProjHourUtilTmp( ProjEmplTrans _projEmplTrans)
{
    HcmWorker hcmworkerlocal;
    smmActivities smmactivityloc;
    ;
    projHourUtilisationTemp.ProjId = _projEmplTrans.ProjId;
    projHourUtilisationTemp.CategoryId = _projEmplTrans.CategoryId;
    select hcmworkerlocal where hcmworkerlocal.RecId == _projEmplTrans.Worker;
    projHourUtilisationTemp.EmployeeName = hcmworkerlocal.name();
    select smmactivityloc where smmactivityloc.ActivityNumber == _projEmplTrans.ActivityNumber;
    projHourUtilisationTemp.Activity = smmactivityloc.Purpose;
    if(_projEmplTrans.LinePropertyId == "SpecialOT")
        projHourUtilisationTemp.SpecialOvertime = _projEmplTrans.Qty;
    else
        projHourUtilisationTemp.SpecialOvertime = 0.00;
    if(_projEmplTrans.LinePropertyId == "Breakdown")
        projHourUtilisationTemp.BreakDownTime = _projEmplTrans.Qty;
    else
        projHourUtilisationTemp.BreakDownTime = 0.00;
    if(_projEmplTrans.LinePropertyId == "Idle")
        projHourUtilisationTemp.IdleTime = _projEmplTrans.Qty;
    else
        projHourUtilisationTemp.IdleTime = 0.00;
    if(_projEmplTrans.LinePropertyId == "OverTime")
        projHourUtilisationTemp.Overtime = _projEmplTrans.Qty;
    else
        projHourUtilisationTemp.Overtime = 0.00;
    if(_projEmplTrans.LinePropertyId == "Normal")
        projHourUtilisationTemp.Normaltime = _projEmplTrans.Qty;
    else
        projHourUtilisationTemp.Normaltime = 0.00;
    projHourUtilisationTemp.CompanyLogo = CompanyImage::findByRecord(CompanyInfo::find()).Image;
    projHourUtilisationTemp.insert();
}
Contract  Class :
[
    DataContractAttribute,
    SysOperationContractProcessingAttribute(classStr(ProjHourUtilisationUIBuilder))
    // SysOperationContractProcessingAttribute(classstr(ProjHourUtilisationUIBuilder), SysOperationDataContractProcessingMode::CreateSeparateUIBuilderForEachContract)
    //SysOperationGroupAttribute('PrintOut', "@SYS12608", '2')
]
class ProjHourUtilisationContract implements SysOperationValidatable
{
    ProjWorkerRecId projWorkerRecId;
    ProjId projId;
    ProjCategoryId category;
    FromDate fromDate;
    ToDate toDate;
}
[
    DataMemberAttribute('Category'),
 SysOperationDisplayOrderAttribute('2')
]
public ProjCategoryId parmCategory(ProjCategoryId _ProjCategoryId = category)
{
    category = _ProjCategoryId;
    return category;
}
public boolean validate()
{
    boolean             isValid = true;
    if(!projWorkerRecId)
        throw error("Select Employee ID");*/
    return isValid;
}
UI Builder  Class :
class ProjHourUtilisationUIBuilder extends SysOperationAutomaticUIBuilder
{
    ProjHourUtilisationContract projHourUtilisationContract;
    DialogField dialogCategory;
    DialogField dialogProject;
    DialogField dialogEmployee;
    DialogField fromDate;
    DialogField toDate;
}
public void build()
{
    Dialog      dialogLocal = this.dialog();
     projHourUtilisationContract = this.dataContractObject();
    this.addDialogField(methodStr(ProjHourUtilisationContract,parmProjectId), projHourUtilisationContract);
    this.addDialogField(methodStr(ProjHourUtilisationContract,parmCategory), projHourUtilisationContract);
    this.addDialogField(methodStr(ProjHourUtilisationContract,parmEmployeeId), projHourUtilisationContract);
    this.addDialogField(methodStr(projHourUtilisationContract,parmFromDate),projHourUtilisationContract);
    this.addDialogField(methodStr(projHourUtilisationContract,parmToDate),projHourUtilisationContract);
}
public void getFromDialog()
{
    projHourUtilisationContract = this.dataContractObject();
    super();
}
public void initializeFields()
{
    projHourUtilisationContract = this.dataContractObject();
}
public void lookupCategory(FormStringControl _control)
{
    Query query = new Query();
    SysTableLookup sysTablelookup;
    sysTablelookup =SysTableLookup::newParameters(tableNum(ProjCategory),_control);
    sysTablelookup.addLookupfield(fieldNum(ProjCategory,CategoryId));
    sysTablelookup.addLookupfield(fieldnum(ProjCategory,Name));
    sysTablelookup.addLookupfield(fieldnum(ProjCategory,CategoryType));
    query.addDataSource(tableNum(ProjCategory));
    query.dataSourceTable(tableNum(ProjCategory)).addRange(fieldNum(ProjCategory, CategoryType)).value(queryValue(ProjCategoryType::Hour));
    sysTablelookup.parmQuery(query);
    sysTablelookup.performFormLookup();
}
/// <summary>
/// Override this method in order to register the dialog field methods to capture events.
/// </summary>
public void postRun()
{
    Dialog dialogLocal = this.dialog();
    super();
    // This method should be called in order to handle events on dialogs.
   dialogLocal.dialogForm().formRun().controlMethodOverload(false);
}
public void postBuild()
{
    ;
    super();
    dialogCategory = this.bindInfo().getDialogField(
                         this.dataContractObject(),
                         methodStr(ProjHourUtilisationContract,parmCategory));
    // register override method for lookup customer
    dialogCategory.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(ProjHourUtilisationUIBuilder,lookupCategory),this);
    dialogProject = this.bindInfo().getDialogField(
                         this.dataContractObject(),
                         methodStr(ProjHourUtilisationContract,parmProjectId));
    dialogEmployee = this.bindInfo().getDialogField(
                         this.dataContractObject(),
                         methodStr(ProjHourUtilisationContract,parmEmployeeId));
    fromDate = this.bindInfo().getDialogField(
                         this.dataContractObject(),
                         methodStr(ProjHourUtilisationContract,parmFromDate));
    toDate =this.bindInfo().getDialogField(
                         this.dataContractObject(),
                         methodStr(ProjHourUtilisationContract,parmToDate));
}
Controller Class :
If  you create Controller class, then you need to create output menu item, set  property Object type – class, Object – Controller class created,  Linked permission type – SSRS report, Linked Permission object – SSRS report Created,  Linked permission object type – Design name.
class ProjHourUtilisationController extends SrsReportRunController
{
  #define.ReportName('ProjHourUtilisation.PrecisionDesign1')
    ProjHourUtilisationContract projHourUtilisationContract;
    ProjTable projTable ;
}
protected void prePromptModifyContract()
{
    if (this.parmArgs()             &&
        this.parmArgs().record()    &&
        this.parmArgs().dataset() == tableNum(ProjTable))
    {
        projTable = this.parmArgs().record();
    }
    if (!projHourUtilisationContract)
    {
        projHourUtilisationContract = this.parmReportContract().parmRdpContract();
    }
    projHourUtilisationContract.parmProjectId(projTable.ProjId);
    super();
}
public void setRange(Args _args, Query _query)
{
    QueryBuildDataSource qbds;
    QueryBuildRange qbr;
    if (_args && _args.dataset())
    {
        switch(_args.dataset())
        {
            case tableNum(ProjTable) :
                projTable = _args.record();
                break;
        }
    }

    qbds = _query.dataSourceTable(tableNum(ProjTable));

    qbds.clearRanges();

    //qbr = qbds.findRange(fieldName2id(tableNum(ProjTable),fieldStr(ProjTable, ProjId)));


    if (!qbr)
    {
        qbr = qbds.addRange(fieldNum(ProjTable, ProjId));
    }
    if(ProjTable)
    {
        qbr.value(projTable.ProjId);
    }
}
public boolean showQueryValues(str parameterName)
{
    return true;
}
public static ProjHourUtilisationController construct(Args _args)
{
    ProjHourUtilisationController controller=new ProjHourUtilisationController();
    controller.parmArgs(_args);
    return controller;
}
public static void main(Args _args)
{
    ProjHourUtilisationController controller = new ProjHourUtilisationController();
    controller.parmReportName(#ReportName);
    controller.parmArgs(_args);
    controller.setRange(_args, controller.parmReportContract().parmQueryContracts().lookup(controller.getFirstQueryContractKey()));
    controller.parmShowDialog(true);
    controller.startOperation();
}
=====================================================================
To Make Child lookup - null when you change parent lookup :
 dimensionAttributeName - Parent Lookup
dimensionAttributeSubName - Child Lookup
Suppose you want the child lookup value to set null when you change the parent lookup , we need to override or add below code. 

Postbuild method :
dimensionAttributeName.registerOverrideMethod(methodstr(FormstringControl, Modified),methodstr(MMSBranchAgingUIBuilder,register),this);

 protected void register(FormStringControl dimensionAttributeNameLookUp)
{
     dimensionAttributeName.value(dimensionAttributeNameLookUp.text());
     dimensionAttributeSubName.value("");
   
}
===================================================================
If  suppose  you want to make query ranges to be disabled,  Just  create range in query and set it to hide. 

=================================================================================
Based on Condition deciding visibility of column/row :

1. To make  any row visibility , based on condition. Just  selct tht row and  right click - as to go for properties -  Click - Row Visibility, in Show or hide expression type below :

=iif(Parameters!ProjHourUtilisation_Category.Value = "",true,false)
======================================================================
To make  The value in text box to appear vertical instead of horizontal :
If  you  want  the  Text box Text, to appear in vertical ,
Go to textbox Properties , Alignment - Horizontal - Centre,Vertical - middle
======================================================================

http://msdn.microsoft.com/en-us/library/dd239338.aspx --- > Tutorials
http://technet.microsoft.com/en-us/library/bb630404.aspx ---> Types of reports
http://technet.microsoft.com/en-us/library/cc624720.aspx  --->   To have  a look on sample Precision design
Procedure  to  Develop  Objects :
<!--[if !supportLists]-->1.      <!--[endif]-->Create  a Query – CustBalQuery  with  CustTable and  CustTrans  as Data Sources  having CustTrans with Inner join under  Data Source of CustTable.

<!--[if !supportLists]-->2.      <!--[endif]-->Create  a Temporary Table – CustBalTmp
Fields – AccountNum, CustGroup, Voucher,Txt, TransType,AmountMst, Debit BalanceMST,CreditBalanceMST, Balance

<!--[if !supportLists]-->3.      <!--[endif]-->Create a Class – CustBalRDP
It contains  6  methods
[SRSReportQueryAttribute(querystr(CustBalQuery))]
class CustBalRDP extends SRSReportDataProviderBase
{
    CustBalTmp custBalTmp;
}
[SRSReportDataSetAttribute("CustBalTmp")]
public custBalTmp getCustBalTmp ()
{
    select * from custBalTmp;
    return custBalTmp;
}
private void insertCustBalTmp(CustTrans _custTrans,CustTable _custTable)
{
    custBalTmp.AccountNum = _custTable.AccountNum;
    custBalTmp.CustGroup = _custTable.CustGroup;
    custBalTmp.Voucher = _custTrans.Voucher;
    custBalTmp.Txt = _custTrans.Txt;
    custBalTmp.TransType = _custTrans.TransType;
    custBalTmp.AmountMST = _custTrans.AmountMST;
    custBalTmp.DebitBalanceMST = this.debitBalanceMST(_custTrans);
    custBalTmp.CreditBalanceMST = this.creditBalanceMST(_custTrans);
    custBalTmp.insert();
}
private AmountMST debitBalanceMST(CustTrans _custTrans)
{
    AmountMST debitBalanceMST;

    debitBalanceMST =-(_custTrans.AmountMST);

     return min(debitBalanceMST, 0);

}
private AmountMST creditBalanceMST(CustTrans _custTrans)
{
    AmountMST creditBalanceMST;

    creditBalanceMST = (_custTrans.AmountMST)-(0);

    return min(creditBalanceMST, 0);
}
[SysEntryPointAttribute(false)]
public void processReport()
{
    QueryRun queryRun;
    Query query;
    CustTable custTable;
    CustTrans custTrans;
    QueryBuildDataSource queryBuildDataSource1;
    QueryBuildDataSource queryBuildDataSource2;
    QueryBuildRange queryBuildRange;
    query = this.parmQuery();

    queryBuildDataSource1 = query.dataSourceTable(tablenum(CustTable));
    queryBuildDataSource2   = queryBuildDataSource1.addDataSource(tablenum(CustTrans));
    queryBuildDataSource2.relations(true);
    queryBuildDataSource2.joinMode(joinmode::InnerJoin);
           queryBuildDataSource2.addLink(fieldnum(CustTable,AccountNum),fieldnum(CustTrans,AccountNum));

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
    custTable = queryRun.get(tableNum(CustTable));
    custTrans = queryRun.get(tableNum(CustTrans));
    this. insertCustBalTmp(custTrans,custTable);
    }
}

<!--[if !supportLists]-->4.      <!--[endif]-->Go to VS  Create  a Report  Project  and Add Report  to it.
Name – CustBalReport 
Under  Data Set node of Report ( Set Property  DataSoureType – Report Data Provider, Query – Click Browse and Select  the RDP class you have Created.
Under  Design  node of Report  Create AutoDesign , RC Table  . Drag Fields  Voucher,Txt,  TransType, ,  Debit BalanceMST,CreditBalanceMST to Data Node . Under  Grouping Node drag -  AccountNum,  Under  Header Node drag CustGroup, Balance
Under Header  node  select  the Balance node Set  the property  Expression - =Sum(Fields!AmountMST.Value)
Deploy and  build the Report .

Controller Class :  

http://community.dynamics.com/product/ax/axtechnical/b/klaasdeforche/archive/2011/08/23/ax2012-sysoperation-part-2-sysoperationservicecontroller.aspx

http://krishhdax.blogspot.in/2012/07/ax2012-create-ssrs-report-using-data.html

http://dynamicsaxgyan.wordpress.com/2012/05/11/save-ssrs-report-to-pdf-that-uses-controller-classes-dynamics-ax-2012/
 ==================================================================



Using AX Enum Provider in a Column Chart Report [AX 2012]
http://msdn.microsoft.com/en-us/library/cc554854.aspx

To define a query

1.      Open the Microsoft Dynamics AX Development Workspace.
2.      In the AOT, right-click the Queries node, and then click New Query.
3.      Right-click the node for the new query, click Rename, and then type CustTransactionData. Expand the node for the CustTransactionData query.
4.      In the AOT, right-click the Data Dictionary node, and then click Open New Window.
5.      In the new window, expand the Tables node.
6.      Locate the CustTable table and drag it onto the Data Sources node for the query.
7.      In the CustTable_1 data source node, select the Fields node, in the Properties window, set the Dynamic property to No. You will select the specific fields that will be used on the report instead of sending all data in the table. This will produce faster running reports.
8.      Locate the CustTrans table and drag it onto the Data Sources node located below the CustTable data source.
9.      In the CustTrans_1 data source node, select the Fields node, in the Properties window, set the Dynamic property to No.
10.  In the separate window, expand the node for the CustTrans table > Fields, drag the AmountMST field to the Fields node of the CustTrans_1 data source.
11.  Right-click the Relations node for the CustTrans_1 data source, and then click New Relation.
12.  Select the node for the relation and verify the following default values in the Properties window.
Property
Value
JoinDataSource CustTable_1
Field AccountNum
Related Field AccountNum
13.  In the AOT, right-click the Ranges node for the CustTrans_1 data source, and then click New Range.
14.  Select the node for the range, and in the Properties window, select the TransType field from the drop-down menu for Field.
NoteNote
A report parameter is automatically generated for the range when the query is used in a report dataset and the Dynamic Filters property for the report dataset is set to False.
15.  The TransType field is an enum type. When you define the report, you must know the value of the EnumType property when you define the AX Enum Provider data source. To find the property value, in the AOT, click Data Dictionary > Tables > CustTrans > Fields > TransType. In the Properties window, notice that the EnumType property is set to LedgerTransType.
16.  Locate the CustGroup table and drag it onto the Data Sources node located below the CustTrans_1 data source.
17.  In the CustGroup_1 data source node, select the Fields node, in the Properties window, set the Dynamic property to No.
18.  In the separate window, expand the CustGroup table > Fields node. Drag the Name field to the Fields node of the CustGroup_1 data source.
19.  Right-click the Relations node for the CustGroup_1 data source, and then click New Relation.
20.  Select the node for the relation and specify the following values in the Properties window.
Property
Value
JoinDataSource CustTable_1
Field CustGroup
Related Field CustGroup
21.  Save the query.
Creating a Reporting Project
Next, you will create a reporting project in Microsoft Visual Studio. In this walkthrough, you will use the Report Model template.

To create a reporting project

1.      Open Microsoft Visual Studio.
2.      On the File menu, point to New, and then click Project. The New Project dialog box is displayed.
3.      In the Installed Templates pane, click Microsoft Dynamics AX node, and in the Templates pane, click Report Model.
4.      In the Name box, type SampleChartReport, and in the Location box, type a location.
5.      Click OK.
Creating a Report with a Column Chart
Before you create a chart, you must decide what type of chart to create. There are two types of charts: XY charts and pie or doughnut charts. An XY chart is a column, line, or bar chart. During design, you can switch between related chart types. For example, you can create a column chart and then later change it to a bar or line chart. In this walkthrough, you will begin by creating a column chart. Later in the walkthrough, you will change the design so that the data displays in a bar chart and then in a line chart. You will use the predefined layout and style templates provided by the Visual Studio tools for Microsoft Dynamics AX. You will use the template ColumnChartStyleTemplate to provide the layout for the column chart report. For the following example, you will create two datasets. The first will be bound to the CustTransactionsData query and the second will be used to display the transaction type that is an Enum type.

To create a report that has a column chart

1.      In Solution Explorer, right-click the SampleChartReport project, point to Add, and then click Report.
2.      In Model Editor, right-click the Report1 node, and then click Rename.
3.      Type ColumnChartReport as the name.
4.      Expand the ColumnChartReport node if it is not already expanded.
5.      Right-click the Datasets node, and then click Add Dataset.
6.      Select the node for the dataset.
7.      In the Properties window, specify the following values.
Property
Value
Data Source Dynamics AX
Data Source Type Query
Default Layout ColumnChart
Dynamic Filters False
Name CustomerTransactions
Query
1.      Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use.
2.      Select the CustTransactionData query and then click Next.
3.      Expand the CustTrans_1 node and select the All Fields check box. This will select the AmountMST field.
4.      Expand the CustGroup_1 node and select the All Fields check box. This will select the Name field.
5.      Click OK.
8.      In Model Editor, expand the node for CustomerTransactions > Fields.
9.      Select the AmountMST field, and in the Properties window, set the Aggregate Function property to Sum and the Format String property to Currency.
10.  Right-click the Datasets node, and then click Add Dataset. You will create a dataset with an AX Enum Provider data source for the TransType enum field. This will let you filter the report to show specific transaction types.
11.  Select the node for the dataset.
12.  In the Properties window, specify the following values.
Property
Value
Data Source Dynamics AX
Data Source Type AX Enum Provider
Name LedgerTransTypeEnum
Query LedgerTransType
13.  Drag the CustomerTransactions node onto the Designs node for the report. An auto design called AutoDesign1 is created for the report.
14.  Expand the AutoDesign1 node, expand the node for the chart data region, and then expand the Data node.
15.  Drag the Name field to the Categories node.
NoteNote
The AmountMST field should be the only field that remains below the Data node.
Configuring a Report Parameter
Next, you will configure a parameter for the report. The report contains a parameter for the TransType field because a range based on this field was added to the query and you set the Dynamic Filters property for the dataset to False.
The TransType field is an Enum type. You will update the Values property on the CustomerTransactions_TransType report parameter to reference the AX Enum Provider dataset that you created. By using the AX Enum Provider, the enum parameter can be accessed from Enterprise Portal and also the Microsoft Dynamics AX client.
The following procedure explains how to configure a report parameter.

To configure a report parameter

1.      In Model Editor, expand the Parameters node for the report, and then select the CustomerTransactions_TransType parameter.
2.      In the Properties window, set the following property values:
Property
Value
Allow Blank False
Data Type Integer
Values Click the ellipsis button (...) to open the Select Values dialog box. Set the following values:
o    Dataset: LedgerTransTypeEnum
o    Value field: Value
o    Label field: Label
Make sure that From dataset is marked, and then click OK.
Applying Layout and Style Templates
Next, you will specify layout and style templates for the report. A layout template defines the general layout and style settings for a report. A style template contains the layout and style settings for a data region that displays in the body of a report. You will apply the predefined templates that are provided by the Microsoft Dynamics AX framework. These templates are the standard templates for Microsoft Dynamics AX reports. The following procedure explains how to apply layout and style templates to the report.

To apply layout and style templates

1.      In Model Editor, select the AutoDesign1 node.
2.      In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate. Also, type Customer transactions for the Title property.
3.      In Model Editor, expand the AutoDesign1 node, and then select the node for the chart data region.
4.      In the Properties window, set the Style Template property to ColumnChartStyleTemplate.
5.      Delete the default text for the Title property so that it does not display a title for the data region.
6.      Set the Value Axis Data Scale Minimum property to 5. This will set the starting value on the axis of your report to 5 instead of 0. This is one of many properties that will define the look of your report.
7.      In Model Editor, right-click the AutoDesign1 node, and then click Preview to view the report. Specify a transaction type for the parameter, like Customer, and then click the Report tab to view the report.
8.      Close the Preview window.
Changing the Format to a Line or Bar Chart
During design, you can switch between several related chart types. First, you will switch from a column chart to a bar chart. After that, you will switch it to a line chart. The following procedures explain how to switch between chart types.

To switch the format to a bar chart

1.      In Model Editor, select the node for the CustomerTransactionsXYChart chart data region.
2.      In the Properties window, set the Chart Type property to Bar.
3.      In Model Editor, right-click the AutoDesign1 node, and then click Preview to view the report. Specify a transaction type for the parameter, like Customer, and then click the Report tab to view the report.

To switch the format to a line chart

1.      In Model Editor, select the node for the CustomerTransactionsXYChart chart data region.
2.      In the Properties window, set the Chart Type property to Line.
3.      In Model Editor, right-click the AutoDesign1 node, and then click Preview to view the report. Specify a transaction type for the parameter, like Customer, and then click the Report tab to view the report.

 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)