Microsoft Dynamics AX (Archived)

How to pass parameters to an SSRS report created using Query as report type

(0) Share

Report

Posted on by Community Member

Hi all,

I have created an AOT query with two tables in them, the outer table has Accounting date as range.

I have developed an SSRS report using this query. It runs well without issues.

I have to avoid the report UI dialog asking for date and pass it through the code.

I have from and to dates where the customer can choose them in a form. I have then captured them as args and trying to pass them to the controller class and apply them to the query and run the ssrs report without a UI dialog.

I'm having trouble in applying the from and todate as a range to the query and run the report without UI dialog.

Any help is much appreciated. Please help.

Thanks,

Praveen.

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (1)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You can pass on parameters through the contract class, on which you may do a class decoration to hide the parameter.

community.dynamics.com/.../234476

You can override prePromptModifyContract in the controller, and override parameters there.

http://axguruu.blogspot.com/2015/01/ssrs-report-using-controller-contract.html

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Vilmos,

thank you for replying.

I do not want to create a DP class and create a temptable. The query approach is straight forward and it only has two tables in them with no much complexity.

I do not want to insert the records in to a temptable from query loosing some time.

I agree with creating contract class, but I would want to somehow skip the contract class and DP class creations and utilize the from and to date (args) which comes from the form in the controller class.

Can this be done. Is this approach feasible?

Thanks,

Praveen.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

How about setting the range in the controller class?

community.dynamics.com/.../169586

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Vilmos,

Sorry for the late reply, I was away for some time.

I'm using a query based SSRS report. I need to modify the query that's associated with the SSRS report. I have to give the from and to dates at run time.

I'm using the following code and I noticed, at run time the query object returns empty.

Query query;

QueryBuildRange queryBuildRange;

QueryBuildDataSource queryBuildDataSource;

maInventoryValuationAuditorsContract contractObject;

maInventoryValuationAuditorsController controllerObject;

TransDate parmFromDate = 08\09\2015;

TransDate parmToDate = today();

controllerObject = new maInventoryValuationAuditorsController();

controllerObject.parmReportName(ssrsReportStr(maInventoryValuationAuditorsReport,maDesign));

query = controllerObject.getFirstQuery(); // this line returns nothing.

I want to modify the query as below

controllerObject.queryHelper(controllerObject.getFirstQuery().dataSourceTable(tableNum(GeneralJournalEntry)).rangeField(fieldNum(GeneralJournalEntry, AccountingDate)).value(SysQuery::range(parmFromDate,parmToDate)));

As the controllerObject.getFirstQuery(); returns empty I could not solve my issue.

Please assist in getting the query from ssrs report and apply this range with my values.

Thanks,

Praveen.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi All, someone please help me with my problem here.. Appreciate any help.

Thank,

Praveen.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello all,

I have created an SSRS report of query type.

I have Accounting Date as the range in the query. The user gets a dialog box asking to enter a date, based on the user selected date, the proper records are shown in the SSRS o/p.

My requirement: I want to hide the dialog and pass the custom date to the range at run time and execute the report.

I do not have a contract class or DP class. I have created a controller class and modified the prePromptModifyContract() as following,

protected void prePromptModifyContract()

{

TransDate fromDate = dateNull();

TransDate toDate = dateNull();

fromDate = 08\09\2015;

toDate = today();

SrsReportHelper::addParameterValueRangeToQuery(

this.getFirstQuery(),tableNum(GeneralJournalEntry),

fieldNum(GeneralJournalEntry, AccountingDate),

SysQuery::range(fromDate,toDate ));

super();

}

I have noticed, this.getFirstQuery() is returned empty. If it returns the query used by the SSRS, I can apply the range and satisfy my requirement.

Can someone please help me to achieve this.

Was this reply helpful? Yes No
Johnkrish 781 on at

Like (0)

Report

I'm also having the same kind of issue, did you find any Solution?

Regards,

Johnkrish

Was this reply helpful? Yes No
Suggested answer

Sukrut Parab 71,710 Moderator on at

Like (0)

Report

try getoriginalQuery instead of getFirstQuery() , also try to write your logic in preRunModifyContract() . You need to try both methods (getFirstQuery,getoriginalQuery) in preRunModifyContract. PreRunModifyContract is called by report controller before the report is run.

you can try this as well

str selectedQueryKey= controller.getFirstQueryContractKey();

origQueryPacked = this.getOriginalQuery(selectedQueryKey, queryStr(yourqueryname));

sysQueryRun.origQueryPack(origQueryPacked)

selectedQuery = sysQueryRun.query();

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for replying Sukrut.

I tried your suggestion.

The preRunmodfyContract is triggered after the UI dialog ran.

I have to pass my range before the UI as I'm going to hide the UI dialog and execute the report.

However I tried your code in the preRunModifycontract()

protected void preRunModifyContract()

{

Query selectedQuery;

SysQueryRun sysQueryRun;

container origQueryPacked;

str selectedQueryKey;

TransDate _fromDate = 08\09\2015;

TransDate _toDate = today();

selectedQueryKey = this.getFirstQueryContractKey();

origQueryPacked = this.getOriginalQuery(selectedQueryKey, queryStr(maInventoryValuationReportForAuditQuery));

sysQueryRun.origQueryPack(origQueryPacked); // object not initialized error

selectedQuery = sysQueryRun.query();

super();

}

sysQueryRun.origQueryPack(origQueryPacked); this line is giving error

object not initialized . I tried initializing the object but its not accepting and there's no construct method to use, to initialize the object in class.

kindly suggest.

Thanks,

Praveen.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I found one blog which can help me but I ran into the same problem where the query is returning null from the report.

msdax.wordpress.com/.../how-to-set-the-query-range-on-a-ssrs-report

SRSReportRun reportRun = new SRSReportRun("maInventoryValuationAuditorsReport.maDesign");

// Create variables for setting the range for the query.

Query query;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

int i;

TransDate parmFromDate = 08\09\2015;

TransDate parmToDate = today();

// Check if the query will return data.

if (reportRun.init())

{

// Find the report's query.

query = reportRun.reportQuery("maInventoryValuationReportForAuditQuery"); // query name of the report

if (query != null) // query is null

{

// Get the Customers data source.

queryBuildDataSource = query.dataSourceTable(tableNum(GeneralJournalEntry));

if (queryBuildDataSource != null)

{

queryBuildRange = queryBuildDataSource.addRange(fieldName2Id(queryBuildDataSource.table(), "AccountingDate"));

queryBuildRange.value(queryRange(parmFromDate,parmToDate));

}

}

// Save the report settings.

reportRun.saveSettings();

// Run the report.

reportRun.run();

}

query is empty for some reason.

Please help.

Was this reply helpful? Yes No