Creating SSRS Reports In Microsoft Dynamics 365 F&O
Every organization generate tons of data every year. It is important to have a organized way to show that data in a manner which makes sense. Either to analyze the progress, or to make business decisions. So, here Reports comes into the picture.
SSRS reports stands for SQL Server Reporting Service, it is used by Microsoft Dynamics 365 F&O for generating business reports. In this article we will learn how to create a SSRS report.
We will create the following artifacts in the process:
- Query to fetch the data
- Data Contract class
- Temporary Table
- UI Builder Class
- Data Provider class
- SSRS Report with precision design
- Output Menu Item
Here we are taking an example of a scenario where want to generate a report of a hospital based on from date, to date and department. We want to see how many patients came in the given time period, for which department.
Step 1 | Create An AOT Query
Step 2 | Create Contract Class
In this class, we have added the parameter methods which are taking input for generating the report. In our case, we have three parameters fromDate, toDate, and department. Also, the SysOperationValidatable class is implemented for validations.
[DataContractAttribute,SysOperationContractProcessingAttribute(classStr(SLD_ReportUIBuilder))]class SLD_ReportContract implements SysOperationValidatable{TransDate fromDate;TransDate toDate;// SLD_Department_Enum departmentName;List departmentName;[DataMemberAttribute("From Date"),SysOperationLabelAttribute (literalStr(@FromDate))]public TransDate parmDateFrom(TransDate _fromDate = fromDate ){fromDate = _fromDate;return fromDate;}[DataMemberAttribute("To Date"),SysOperationLabelAttribute (literalStr(@ToDate))]public TransDate parmDateTo(TransDate _toDate = toDate ){toDate = _toDate;return toDate;}[DataMemberAttribute("departmentName"),AifCollectionTypeAttribute("return",Types::String),SysOperationLabelAttribute (literalStr(@Department))]public List parmDepartment(List _departmentName = departmentName){departmentName = _departmentName;return departmentName;}public boolean validate(){boolean isValid = true;if (fromDate == dateNull()){isValid = checkFailed("From date cannot be blank, please provide some valid date.");}else if (toDate == dateNull()){isValid = checkFailed("To date cannot be blank, please provide some valid date.");}else{if (fromDate > toDate){isValid = checkFailed("To date must be less than from date");}}return isValid;}}
Step 3 | Create Temporary Table
We create a temporary table only with those fields which we want to show in the report so that we can dynamically insert data into it while the report generates and then display the data.
Step 4 | Create UI Builder Class
User Interface (UI) Builder Class is created to define the layout of the parameter dialog box that opens before a report is run. It is used to add the customizations as well as additional fields in the dialog. It is not a compulsory artifact but can be implemented as per the requirement.
In our case, we are implementing it to add the multi-select lookup for the departments field so that we can select multiple departments at a time.
class SLD_ReportUIBuilder extends SrsReportDataContractUIBuilder{SLD_ReportContract reportContract;DialogField dialogDepartmentName;DialogField dialogFromDate;DialogField dialogToDate;public void getFromDialog(){reportContract = this.dataContractObject();super();}public void initializeFields(){reportContract = this.dataContractObject();}public void build(){reportContract = this.dataContractObject();dialogDepartmentName = this.addDialogField(methodStr(SLD_ReportContract,parmDepartment),reportContract);dialogFromDate = this.addDialogField(methodStr(SLD_ReportContract,parmDateFrom),reportContract);dialogToDate = this.addDialogField(methodStr(SLD_ReportContract,parmDateTo),reportContract);}public void postBuild(){super();dialogDepartmentName.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(SLD_ReportUIBuilder,lookup),this);}public void lookup (FormStringControl _control){Query query = new Query();container cont;QueryBuildDataSource queryBuildDataSource;queryBuildDataSource = query.addDataSource(tablenum(SLD_Departments));queryBuildDataSource.addSelectionField(fieldNum(SLD_Departments,DepartmentName));SysTableLookup sysTableLookup;if (_control!=null){SysLookupMultiSelectGrid::lookup(query,_control,_control,_control,cont);}}}
Step 5| Create Data Provider Class
In this class, we are implementing two methods. One for getting the data from the temporary table. And, the second one is for processing the report. In processReport() we are first fetching the passed parameters. Then we are building the query and putting range in it. After that, execute the query to insert the data into the temporary table.
[SRSReportQueryAttribute (querystr(SLD_ReportQuery)),SRSReportParameterAttribute(classStr(SLD_ReportContract))]class SLD_ReportDP extends SRSReportDataProviderBase{SLD_ReportTable reportTable;SLD_ReportContract sld_reportContract;TransDate _fromDate, _toDate;SLD_Patient patient;SLD_Doctor doctor;SLD_Departments departments;SLD_DiagnosisDetails diagnosisDetails;List departmentName;ListIterator listIterator;
[SRSReportDataSetAttribute('Sld_Report_Table')]public SLD_ReportTable getSldReportTable(){select * from reportTable;return reportTable;}
public void processReport(){sld_reportContract = this.parmDataContract();_fromDate = sld_reportContract.parmDateFrom();_toDate = sld_reportContract.parmDateTo();departmentName = sld_reportContract.parmDepartment();Query query;QueryRun qr;QueryBuildDataSource queryBuildDataSource1,queryBuildDataSource2;QueryBuildRange queryBuildRange;// Get the query from the runtime using a dynamic query.query = this.parmQuery();// Add parameters to the query.queryBuildDataSource1 = query.dataSourceTable(tablenum(SLD_DiagnosisDetails));queryBuildDataSource2 = query.dataSourceTable(tablenum(SLD_Departments));if(_fromDate && _toDate){queryBuildRange = queryBuildDataSource1.findRange(fieldnum(SLD_DiagnosisDetails, DiagnosisDate));if (!queryBuildRange){queryBuildRange = queryBuildDataSource1.addRange(fieldnum(SLD_DiagnosisDetails, DiagnosisDate));}if(!queryBuildRange.value())queryBuildRange.value(queryRange(_fromDate, _toDate));}if(!departmentName.empty()){listIterator = new ListIterator(departmentName);while (listIterator.more()){queryBuildRange = queryBuildDataSource2.addRange(fieldnum(SLD_Departments, DepartmentName));queryBuildRange.value(queryValue(listIterator.value()));listIterator.next();}}// Run the query with modified ranges.qr = new QueryRun(query);ttsbegin;delete_from reportTable;reportTable.clear();while(qr.next()){diagnosisDetails = qr.get(tablenum(SLD_DiagnosisDetails));patient = qr.get(tableNum(SLD_Patient));doctor = qr.get(tableNum(SLD_Doctor));departments = qr.get(tableNum(SLD_Departments));reportTable.PatientName = patient.Name;reportTable.PatientAge = patient.Age;reportTable.PatientPhone = patient.Phone;reportTable.DoctorName = doctor.Name;reportTable.Diagnosis = diagnosisDetails.DiseasesDiagnosed;reportTable.Department = departments.DepartmentName;reportTable.insert();}ttscommit;}}
Step 6| Create SSRS Report
Now, we create the object of the Report with the precision design. We can make the design level changes as per our requirements.
Step 6 | Create Output Menu Item
Finally, an output menu item is created with object type SSRSReport and the report’s object is assigned.
Preview Of The Parameters UI & Report
Summary
In this article, we learned to create a SSRS report from scratch involving multiple artifacts for different purposes. There are always multiple ways of doing a thing, we could have used inline query instead of AOT query too. But, there are pros and cons related to performance. I highly encourage to share your insights about other ways too. Feel free to read, share and respond.
