First things first: The following elements are required to set RDP as your data source type.
step 1: Query
step 2: Temporary table – RDP class fills a temporary table with data that will be used by Reporting Services to display the report.
[msdn help]
step 3:Data contract class – To define the parameters in the report.
step 4:Report data provider class – processes business logic based on parameters and a query, and then returns the tables as a dataset for the report. [msdn help]
Let me explain all the steps mentioned above with an example
For step 1: Create a new query by name SR_CustTable and add the dataasource as CustTable as shown below
Next step 2: Let us create a new temporory table by name TmpSR_CustTable and add 4 new fields as shown below. Ensure that you set the Table type propert as TempDB or InMemory
Use an InMemory temporary table if the dataset is small, for reports that will display fewer than 1000 records.
Use a TempDB temporary table for large datasets to improve performance.
In the below Table: I have set the Table type property to TempDB to improve the performance.Also, I have dragged dropped 4 fields AccountNum, Blocked, priceGroup, Currency as shown below.
Step 3: Now we need to Data Contract class – which will help to define parameters in the report.
Create a new class by name "SRSRDPCustTableContractClass" and add a AccountNum global variable as shown below
class SRSRDPCustTableContractClass
{
AccountNum accountNum;
} ______________________________________________
Add one more parm method to it as shown below
[DataMemberAttribute("AccountNum")]
public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)
{
accountNum = _accountNum;
return accountNum;
} ________________________________________________
Done…Lets move to Step 4
Step 4:
Now we need to create Report Data provider class
Create a new class by name "SR_CustTableRDP" that should extend SRSReportDataProviderBase class.
Attach the SRSReportQueryAttribute attribute to specify the query to use to get the data for the report.
For this example, set the attribute to the SR_CustTable query.
Attach the SRSReportParameterAttribute attribute to specify the data contract class that defines the report parameters for the report.
For this example, set the attribute to the SRSRDPCustTableContractClass.
[ SRSReportQueryAttribute (querystr(SR_CustTable)),
SRSReportParameterAttribute(classstr(SrsRDPCustTableContractClass))
]
class SR_CustTableRDP extends SRSReportDataProviderBase
{
TmpSR_CustTable tmpSR_CustTable;
} _________________________________________________________
Now we need 3 more methods to be added
///
/// Processes the report business logic.
///
///
/// Provides the ability to write the report business logic. This method will be called by
/// SSRS at runtime. The method should compute data and populate the data tables that will be
/// returned to SSRS.
///
[SysEntryPointAttribute(false)]
public void processReport()
{
QueryRun queryRun;
Query query;
CustTable custTable;
SRSRDPCustTableContractClass srsRDPCustTableContractClass;
AccountNum accountNum;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
query = this.parmQuery();
srsRDPCustTableContractClass = this.parmDataContract() as SRSRDPCustTableContractClass;
accountNum = srsRDPCustTableContractClass.parmAccountNum();
// Add parameters to the query.
queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));
if(accountNum)
{
queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, AccountNum));
if (!queryBuildRange)
{
queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, AccountNum));
}
// If an account number has not been set, then use the parameter value to set it.
if(!queryBuildRange.value())
queryBuildRange.value(accountNum);
}
queryRun = new QueryRun(query);
while(queryRun.next())
{
custTable = queryRun.get(tableNum(CustTable));
this.insertTmpTable(CustTable);
}
} ________________________________________________
///
/// This method inserts data into the temporary table.
///
///
/// Table buffer of CustTable table.
///
private void insertTmpTable(CustTable _custTable)
{
tmpSR_CustTable.AccountNum = _custTable.AccountNum;
tmpSR_CustTable.Blocked = _custTable.Blocked;
tmpSR_CustTable.PriceGroup = _custTable.PriceGroup;
tmpSR_CustTable.Currency = _custTable.Currency;
tmpSR_CustTable.insert();
} ____________________________________________
[SRSReportDataSetAttribute("Tmp_SRCustTable")]
public tmpSR_CustTable getTmpSR_CustTable()
{
select * from tmpSR_CustTable;
return tmpSR_CustTable;
} ________________________________________________
wow…we are done with all the steps mentioned above and I am excited to help you guys understand how to use the RDP class as a datasource for the dataset. For this we need to create a new report in the visual studio .
Follow me friends…[I am assuming that all the visual tools have been installed and you have report model template available to created the reports]
Open visual studio. Click on File >> New project and follow the below process.
Now add a new report to the report model by right clicking and selecting new report as shown below
Rename the report to "SR_CustTableRDPReport" as shown below by going to the properties of the report
Now the next thing what we have to do [the most awaited...] is to create a dataset for this report.
Right click on the DataSet and add new dataset as shown below. Rename it to CustomerDataSet as shown below
Go to its properties and rename it to "CustomerDataSet"
Now the real trick, we have a datasource type property on the dataset properties. Select report data provider as the datasource type as show below.
Select the query property and Click on the ellipsis (…) button to selct the RDP class which you have created in the step 4 as shown below
This will come up with all the RDP classes available in AX. Select SR_CustTableRDP Class and click on next button to select the fields from the tmpTable to shown it on the report.
Follow the below screen shot to select the fields and click on Ok button
wonderful..we are done with dataset..Only few steps now..Now we need to create design. Drag and drop this CustomerDataSet to Designs node. It will automatically create the Autodesign1 as shown below
Few more properties for good look and feel of reports : Right click on the Autodesign1 and set the
LayOutTemplate as ReportLayoutStyleTemplate as shown below
Then set the style template as TableStyleTemplate as shown below
Also, since I dont have data in default DAT Company, I would like to use company parameter as well. so I am unhiding the company parameter to select the company parameter along with the Account number [step 3 parameter]
To do this, follow me and change the hidden property to visible as shown below for company parameter
Thats it..Now let us run this report.
Right click on the Autodesign1 and select option Preview and select the parameters as shown below
Select the report Tab to view the report. It will take some time to render data.
Here comes the final data on the report – from the RDP class by inserting in to temporary table and showing the same on to report.
