AX 2012 SSRS displaying dynamic parameter values in reports

(0) Share

Report

Posted on by mwpage

200

Hello, being new to reporting in ax 2012, and new to AX in general, I was wondering if anyone had a possible solution as to how we could display the user entered range values from an AX Query Dynamic parameter in an SSRS report. For example, if there was a predifined range on the query using CustPackingSlipJour on the DeliveryDate field, I want to display in the report that the results are for Delivery Date between A and B, or if they choose to filter certain ItemID's from the inventTable, I would like the user to be able to print that on the report. Any help would be great, Thanks!

-matt

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Andy Adamak 4,074 on at

Like (0)

Report

Me too! Any answer on this matt?

Was this reply helpful? Yes No
Suggested answer

Andy Adamak 4,074 on at

Like (0)

Report

Matt,

What I ended up doing was making a Report Data Contract for the report where you can specify parameters. These are then brought in as uniquely declared parameters in the report designer as opposed to a big dynamic parameter. You can then bind your report design to the value of these parameters.

For example, a simple contract:

CLASS DECLARATION:

[ DataContractAttribute ]
public class MyReportContract
{
   utcDateTime dateFrom;
   utcDateTime dateTo;
}

PARMDATEFROM:
[  DataMemberAttribute('DateFrom'),
   SysOperationLabelAttribute(literalStr("Date from")),
   SysOperationHelpTextAttribute(literalStr("Report start date.")) ]

public utcDateTime parmDateFrom(utcDateTime _dateFrom = dateFrom)
{
   dateFrom = _dateFrom;
   return dateFrom;
}

PARMDATETO:
[ DataMemberAttribute('DateTo'),
   SysOperationLabelAttribute(literalStr("Date to")),
   SysOperationHelpTextAttribute(literalStr("Report end date.")) ]

public utcDateTime parmDateTo(utcDateTime _dateTo = dateTo)
{
   dateTo = _dateTo;
   return dateTo;
}

CONSTRUCT:
public static MyReportContract construct()
{
   return new MyReportContract();
}

Then tag your report data provider class with the SRSReportParameterAttribute(classStr(MyReportContract)) and modify your data query based on whatever parameters you're trying to process from your contract.

For example, once set up your RDP.processReport() would be like this:

[SysEntryPointAttribute(false)]

public void processReport()
{
   Query q;
   QueryRun qr;
   QueryBuildRange qbr;

   contract = this.parmDataContract();
   q = this.parmQuery();

qbr = q.dataSourceTable(tableNum(SomeTable)).addRange(fieldNum(SomeTable, SomeField));

qbr.value(strFmt('((SomeField>= %1) && (SomeField<= %2))',
                                contract.parmDateFrom(),
                                contract.parmDateTo()));

qr = new QueryRun(q);

while(qr.next())
   {
       // insert records
   }
}

Was this reply helpful? Yes No
Suggested answer

mwpage 200 on at

Like (0)

Report

Thanks for the response, I've done things the way that you are describing, and it works, but then you miss out on the flexibility that the dynamic query parameter provides. Plus, if you have report side parameters, you could also just use SSRS expressions to list the parameters and their values.

However, using some of the functions that you referred to, you can indeed enumerate the query ranges that are part of a dynamic parameter. In that case you do not need the data contract class, but I believe you don need to employ the rdp approach.

Anyways in the process report method, you could call your own static method that would loop through all of the data sources in the query, inside that loop you could loop through all of the ranges for each data source. I'm not in front of my AX machine right now, but if you want a sample I can provide that tomorrow.

Was this reply helpful? Yes No
Andy Adamak 4,074 on at

Like (0)

Report

I came across an example in AX yesterday of grabbing dynamic query values in code in the RDP, and was going to update this thread with the info. You beat me to it! Glad to know you found the solution.

Was this reply helpful? Yes No
Andy Adamak 4,074 on at

Like (0)

Report

I needed to do this on another report today (show dynamic parameters entered). For the life of me, I couldn't get the values to pull through. I was using the simple line:

parameterValue = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum([TableName])), fieldNum([TableName], [FieldName])).value();

The report would pull the values as I'd expect when running a Preview in VS2010, but when running it in AX no values would appear on the report. Odd.

Turns out I had to go to the query object on the AOT and drag the field I wanted to display onto the Ranges node of that datasource. Therefore, from what I can tell, you could never have truly dynamic parameters and iterate through them as you'd suggest... unless you actually were able to get that to work? I'd love to see how. We are running AX 2012 R2 base and it doesn't work. Maybe CU6 fixes it.

Cheers!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Andy and Mwpage,

Did you find a better way to do it ? How to iterate through the query object to get the range value without adding from/to parameters in the contract class.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,948 Most Valuable Professional on at

Like (3)

Report

I've documented the approach I use in Printing dynamic parameters (AX 2012 / SSRS).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Martin !

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

If you have a query based report, you can try the following solution. It should work with RDP based reports as well :

Step 1 : In VS, manually add a Parameter(ReportFilterCriteria) to the report [Allow blank = True, Data type = String, Visibility = Hidden, Nullable = True]

Step 2 : Add a method to iterate through the ranges and return a concatenated string

private str getRanges(Query _query)

{

str rangeValue;

str queryRange;

int rangeCounter;

QueryBuildRange qbr;

QueryBuildDataSource qbds = _query.dataSourceTable(tableNum(MyTable));

int rangeCount = qbds.rangeCount();

if (rangeCount)

{

for (rangeCounter = 1; rangeCounter <= rangeCount; rangeCounter++)

{

qbr = qbds.range(rangeCounter);

if (qbr.value())

{

rangeValue = fieldId2pname(tableNum(MyTable), qbr.field()) + " : " + qbr.value();

queryRange = queryRange? (queryRange + ", " + rangeValue) : (queryRange + rangeValue);

}

}

}

queryRange = "Report filter :" + queryRange;

return queryRange;

}

Step 3 : You'll need a controller class and to override preRunModifyContract(). In this method set the parameter value to use the string from Step 2.

protected void preRunModifyContract()

{

    str queryRangeCriteria;

    #define.parameterReportFilterCriteria('ReportFilterCriteria')

    SrsReportRdlDataContract contract = this.parmReportContract().parmRdlContract();

    Query                    query    = this.getFirstQuery();



    queryRangeCriteria = this.getRanges(query);

    contract.setValue(#parameterReportFilterCriteria, queryRangeCriteria);

}

Redeploy the report to make sure the newly added parameter is added correctly.

Finally, use the parameter as you wish on the report design using expression : =Parameters!ReportFilterCriteria.Value

Was this reply helpful? Yes No
jhowe 1,205 on at

Like (0)

Report

I have also used Martin's approach for exactly this purpose displaying report query parameters that have been selected.

Was this reply helpful? Yes No