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 Microsoft Employee

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

All responses (15)

Answers (1)

Nidhi semwal 25 on at

Like (0)

Report

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

check "vendbalanceList" report in standard, you will get the whole process for your query.
Community Member Microsoft Employee on at

Like (0)

Report

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

hi,

I found another solution:

// Check if the query will return data.
if (reportRun.init())
{

reportRun.resetQueries();

...

regards
Community Member Microsoft Employee on at

Like (0)

Report

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

I have noticed one minor thing here. I have used the same code before and the query was returning null. I struggled a lot to fix that but of no use. I believe the AOS restart might also have made an impact to retrieve the query. FYI.
Community Member Microsoft Employee on at

Like (1)

Report

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

Thanks Sukrut,

I was away for some time and couldn't verify the suggestion.

I was struggling to retrieve the query underlying the SSRS report(My SSRS is query type).

LedgerJournalController helped me,

controllerObject.parmReportContract().parmQueryContracts().lookup(controllerObject.getFirstQueryContractKey() is retrieving the query.

I'm able to make the changes to the query as I need.

Thanks you so much.

Praveen.
Verified answer

Sukrut Parab 71,680 Moderator on at

Like (0)

Report

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

if you still didn't get any resolution on this one , you can look at class ,LedgerJournalController main method.
Community Member Microsoft Employee on at

Like (0)

Report

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

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.
Community Member Microsoft Employee on at

Like (0)

Report

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

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.
Suggested answer

Sukrut Parab 71,680 Moderator on at

Like (0)

Report

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

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();
Johnkrish 781 on at

Like (0)

Report

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

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

Regards,

Johnkrish
Community Member Microsoft Employee on at

Like (0)

Report

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

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.