in addition to parameters specified in data contracts, reports can also use queries. It’s very handy, because users can specify filters and sorting in exactly the same way as they’re used to from other places in Dynamics AX, they can use query expressions and so on.
A relative common request is printing parameter values on the report itself. It’s easy with parameters defined in data contracts – an expression like =Parameters!UsersToInclude.Value will do the job. But the same approach doesn’t work with dynamic parameters (that’s how query parameters get represented in SSRS).
I recommend the following approach. Use a report data provider class – very often, it’s already in place. Add a new data table to hold information about query ranges and expose it to the report. I’m using an existing table, TmpSysQuery, so we don’t have to discuss the design of the table. In processReport(), we extract ranges from the current query and save them to our temporary table.
class LedgerJournalPostControlDP extends SRSReportDataProviderBase
{
TmpSysQuery tmpSysQuery;
}
[SRSReportDataSetAttribute(tablestr(TmpSysQuery))]
public TmpSysQuery getTmpSysQuery()
{
select tmpSysQuery;
return tmpSysQuery;
}
public void processReport()
{
tmpSysQuery = MyQueryUtils::rangesToTable(this.query());
}
public static TmpSysQuery rangesToTable(Query _query)
{
QueryBuildDataSource qbds;
QueryBuildRange queryBuildRange;
TmpSysQuery tmpSysQuery;
LabelType tableLabel;
int occurrence;
int dataSourceNo;
int i;
if (!_query)
{
return tmpSysQuery;
}
for (dataSourceNo = 1; dataSourceNo <= _query.dataSourceCount(); dataSourceNo++)
{
qbds = _query.dataSourceNo(dataSourceNo);
if (qbds.enabled())
{
occurrence = SysQuery::tableOccurrence(_query, qbds.table(), dataSourceNo);
tableLabel = tableId2pname(qbds.table()) + SysQuery::tableOccurrenceText(occurrence);
for (i = 1; i <= qbds.rangeCount(); i++)
{
queryBuildRange = qbds.range(i);
if (queryBuildRange.value() && queryBuildRange.status() != RangeStatus::Hidden)
{
tmpSysQuery.clear();
tmpSysQuery.DataSourceNo = qbds.uniqueId();
tmpSysQuery.TableLabel = tableLabel;
tmpSysQuery.FieldLabel = fieldId2pname(qbds.table(), queryBuildRange.field());
tmpSysQuery.RangeValue = queryBuildRange.value();
tmpSysQuery.insert();
}
}
}
}
return tmpSysQuery;
}
Then we add a new data set in our report and show query ranges in a tablix in exactly the same way as other data: