44 Comments

1. Good post Martin. Thanks for sharing.

2. Everything works fine with query and it’s predifiend ranges. But it does not catch new range fields and values added in prompt dialog.
   static void Job155(Args _args)
   {
   Query q = new Query();
   QueryBuildDataSource qbds;
   TmpSysQuery tmpsysquery;
   QueryRun qr;
   ;

   qbds = q.addDataSource(tableNum(CustTable));
   qbds.addRange(fieldNum(CustTable, AccountNum)).value(“d*”);
   qbds.addRange(fieldNum(CustTable, custgroup)).value(“c*”);

   qr = new QueryRun( q);

   if( !qr.prompt()) // apply new range fields and values
   return;

   tmpsysquery = MyQueryUtils::rangesToTable( qr.query()); // previously provided method

   while select tmpsysquery
   {
   info( strFmt(“%1.%2 == %3”, tmpsysquery.Tablelabel, tmpsysquery.FieldLabel, tmpsysquery.RangeValue));
   }
   // prints the following
   /*
   Customers.customer == d*
   customers.customer group == c*
   */

   }
   any ideas how to fix so all ranges with values will be printed? Testend on Ax2012 R2 and R3

3. Ok, found solution. Apparently added ranges to query in prompt() are referred as QueryFilter objects, so additionaly the query must be traversed something like this:
   QueryFilter queryFIlter;
   ;

   //after qr.prompt()
   for( i =1; i<=qr.query().queryFilterCount(); i++)
   {
   queryFilter = _query.queryFilter( i);
   // do the same as for the QueryBuildRange, or e.g:
   info(queryFIlter.value());
   }

   • Can you please help me to get Table name and Filed name for this?

     • The post already uses methods returning table ID and field ID to show table label and field label. If you don’t like the label and you want the physical name instead, use tableId2name() instead of tableId2pname() and fieldId2name() instead of fieldId2pname().

       For example, you’ll get field name by calling fieldIdpname(qbds.table(), queryBuildRange.field());

4. Thanks Martin, this is something I had been looking for, and is great. However, is it possible to get the resolved text for the filter? i.e. the filter might say (monthRange(-1,-1)) which equates to string “01/01/2015″..”31/01/2015”. I could enumerate sysQueryRangeUtil and check if the filter string contains a valid function name and then use the sysQueryRangeUtil function to resolve the value, but it must be doing this itself somewhere so can I get the resolved value.

   • It surely is done somewhere, but it likely means somewhere in AX kernel. I tried to set such a filter in a form and put a breakpoint to SysQueryRangeUtil. This is what debugger showed as the caller code:
     Path: \anytype wrapper(){ return SysQueryRangeUtil::dayRange(); }

     • Thanks Martin.so it looks like I will have to roll my own resolveFilterForReportDisplay class/function. Why are users never happy!

5. this is wrong
   MyQueryUtils::rangesToTable(this.query()); in ax2012

   • You probably didn’t realize that rangesToTable() is defined in this blog post.

6. this classe (LedgerJournalPostControlDP ) not contant this method (this.query())

   • Aha, why didn’t you say it straight away?
     I updated this.query() to this.parmQuery(). Thank you for pointing it out.

7. i have created this class to get dynamics parameters of query in report design but it not appearing.
   i have created a tmp table and this code but unable to acheive this , not getting data on report.
   can any one suggest me???
   Below is the code of my DP class for ranges

   class ItemOutFromHODP extends SRSReportDataProviderBase
   {
   CopyOfTmpSysQuery copyOfTmpSysQuery;
   }
   /////////////////////////////////////////////////////////////
   [SRSReportDataSetAttribute(tablestr(CopyOfTmpSysQuery))]
   public CopyOfTmpSysQuery getTmpSysQuery()
   {
   select copyOfTmpSysQuery;
   return copyOfTmpSysQuery;
   }
   ////////////////////////////////////////////////////////////////////
   public void processReport()
   {
   copyOfTmpSysQuery = ItemOutFromHODP::rangesToTable(this.parmQuery());
   }
   ///////////////////////////////////////////////////////////////////////
   public static CopyOfTmpSysQuery rangesToTable(Query _query)
   {
   QueryBuildDataSource qbds;
   QueryBuildRange queryBuildRange;
   CopyOfTmpSysQuery copyOfTmpSysQuery;
   LabelType tableLabel;
   int occurrence;
   int dataSourceNo;
   int i;

   if (!_query)
   {
   return copyOfTmpSysQuery;
   }
   info(copyOfTmpSysQuery.RangeValue);
   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)
   {
   copyOfTmpSysQuery.clear();
   copyOfTmpSysQuery.DataSourceNo = qbds.uniqueId();
   copyOfTmpSysQuery.TableLabel = tableLabel;
   copyOfTmpSysQuery.FieldLabel = fieldId2pname(qbds.table(), queryBuildRange.field());
   copyOfTmpSysQuery.RangeValue = queryBuildRange.value();

   copyOfTmpSysQuery.insert();
   info(copyOfTmpSysQuery.RangeValue);
   }
   }
   }
   }

   return copyOfTmpSysQuery;
   }

   kindly suggest i am stuck in this.
   my query parameters are :
   Ship Date : 02/04/2013..02/04/2015 (date parameter)

   • muhammad, debug your code to see where it fails. I can’t do it for you.
     If your problem is about filters, I’ll probably have to extend the sample code to avoid further questions about the same thing.

8. 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.

9. Hi Martin,
   Great post! I am trying to use your information to display user supplied date range on the Asset Disposal report, but cannot determine the RDP class for the report. I found the contract and added code there to capture the parameter and then added a data source to the report, but the dataset is never populated. Do I need to create a new RDP?

10. Hi Martin,
    when we add the new dataset for the gettng query ranges which one will execute at first.
    If the dataset which you created here will execute it might be wrong since It’s displays the previous generated report ranges.

    • My code is in processReport() – this method executes when the report is printed, which is obviously after users set all parameters. If you modify ranges after calling rangesToTable(), it’s your bug and you have to fix your code.

11. Good post Martin.

    Is it possible to have an RDP report which is based on both – a contract and a query? For example, if my report has 2 parameters, is it possible that one of them is defined within a contract while the other one is define within a query?

    Thanks

    • Yes, it’s possible and commonly used. Simply use both SRSReportQueryAttribute and SRSReportParameterAttribute (see an example in https://technet.microsoft.com/en-us/library/gg724119.aspx).
      Nevertheless it’s not really about “Printing dynamic parameters”. If you have more questions, I suggest you ask in a forum. I’m participating in community.dynamics.com and dynamicsuser.net and there are many more people who can give you some answers.

12. When I paste the method code into the MyQueryUtils class I created, I get a syntax error on
    “public static TmpSysQuery rangesToTable(Query _query)” . Why would I receive this error when no one else has mentioned it being a problem?

13. Hi Martin,
    Post is really helpful. I have developed a report on customer transaction table(CustTrans). Requirement is “When I select the dimension business unit from report filter menu then on report it should appear like ‘Dimension.BusinessUnit’, instead of that it is showing ‘Dimension value'”.

    I know actually the statement

    reportPrintingCriteriaTmp.FieldLabel = fieldId2pname(qbds.table(), queryBuildRange.field());

    shows the label of the field which is located in the table whihch is custTrans.DefaultDimension. Is there any way to get the label printed as ‘Dimension.BusinessUnit’. Because user can filter the data on several different dimensions(Department, Business Unit, Location).

14. Hello Martin,

    I have a two dataset in the report, one is pointing a query and the other is the above RDP class to print the range, but when I run this report the RDP class is not executing.

    Any clue?

    Kind regards

    Shacki

    • Hi Shacki, I would have to know much more about your report, and it doesn’t seem to be the place for such a discussion. I suggest you explain your problem (with more details) in an AX discussion forum, where you’ll find many more people who might help you, you can add screenshots there and so on.

15. Hi Martin,

    I would like to do this but cannot get it to work. I would like to add this to the CustAgingReportDP. In public class CustAgingReportDP extends SRSReportDataProviderPreProcessTempDB
    {
    I have added
    [
    SRSReportDataSetAttribute(tablestr(TmpSysQuery))
    ]
    public TmpSysQuery getTmpSysQuery()
    {
    select tmpSysQuery;
    return tmpSysQuery;
    }

    Then can public static TmpSysQuery rangesToTable(Query _query) be added in same RDP? Or does this need to be added in a totally new class/file? I tried adding above processreport method.
    Then in processreport method at end I added tmpSysQuery = myQueryUtils::rangesToTable(this.parmQuery());

    but code does not compile. I get error the name’myQueryUtils’ does not denote a class, a table, or an extended data type. The name ‘myQueryUtils’ does not designate a class or table. Where are you declaring this? Can you please modify your post so you code is runnable? myQueryUtils does not seem to be declared anywhere. Forgive me I am a SQL BI developer by trade and am new to AX development, so I am struggling. Thank you.

    • Use the name of your class where you’ve put rangesToTable() method. If you haven’t, you missed a step.

16. Hi Martin,

    Thanks for your fast response. Ok I created new class called MyQueryUtils containing your rangesToTable method. I created a copy of TmpSysQuery as TempDB because report i’m working on public class CustAgingReportDP extends SRSReportDataProviderPreProcessTempDB gave me error using tables as InMemory. reporting solution is compiling, i created new tablix in report with fields TmpSysQueryCopy.DataSourceNo
    TmpSysQueryCopy.TableLabel
    TmpSysQueryCopy.FieldLabel
    TmpSysQueryCopy.RangeValue
    from your rangestotable method (and new dataset in SSRS report). I added custaccount in ‘records to include section (dynamic parameter)’ report is filtering by this. I would expect to see my cust account value in one of those fields in new tablix when running report however tablix is not returning any data. Can you help? I’m using D 365. Thanks.

    • Trying to debug your code just by looking at whether a tablix get data or not isn’t very efficient and likely to succeed. Forget the report for a moment and first verify whether your RDP class returns right data. If not, fix it (without wasting any time with looking at the report, because it can’t work without right data). And if it works, you’ll know something is wrong with the report design and the problem isn’t in the RDP class.
      For example, it might be a filter instead of a range, or there may be any kind of bug.

17. Ok how do I do this? I’ve tried adding breakpoints but it’s not hitting them… i.e. i have set one in processreport method.

    • Please ask such questions in a discussion forum next time (I contribute to dynamicsuser.net and community.dynamics.com). It’s a better place for longer discussions and there many more people to help you.
      Regarding your question, look at How to: Configure the Debugger to Debug a Report Data Provider Class (https://technet.microsoft.com/en-us/library/gg724081.aspx).

      • Hi Martin, I managed to get the debugger working, I can see data being assigned to table columns. However table is returning nothing. I will post and make as clear as possible in https://community.dynamics.com/enterprise/f/759 please keep an eye out. Have a good weekend, thanks for your help.

18. Hi Martin,
    I have used your process ,but the report is getting only predefined ranges but not the ranges which are selected after prompt

    Please suggest
    Devi

    • You’re probably talking about filters, while my example shows only ranges. You can iterate the in a similar way as ranges, with queryFilterCount() and queryFilter().

19. Hi Martin,

    I would like to add the parameters from any contract class also to be part of the query ranges to be printed on the report
    As suggested ,made a different class with query as parameter
    and calling it from the dp class by passing the parmquery as parameter.
    Now for the contract parameters ,i am able to do it by passing the controller class if they are using a controller class .
    But from DP how do i get the controller object so that i can refer back contract parameters on the new class so that i can use this class uniquely.

    Regards
    DP

    • Your design won’t work – input value must be in the contract, not in controller. Use the controller (e.g. preRunModifyContract()) to add ranges to the query in the contract.

20. Hi Martin,

    I would like to add the parameters from any contract class also to be part of the query ranges to be printed on the report
    As suggested ,made a different class with query as parameter
    and calling it from the dp class by passing the parmquery as parameter.
    Now for the contract parameters ,i am able to do it by passing the controller class if they are using a controller class .
    But from DP how do i get the controller object so that i can refer back contract parameters on the new class so that i can use this class uniquely.

    Regards

    • Forget about the controller class; that’s not what you need. You’ll get the contract in your RDP class by simply calling this.parmDataContract().

      • Hi Martin,

        Thank you .Figured out to do it from DP class .Passed Metadata as a parameter to the new class from there designed the logic as per your prior code.

21. Hello Martin – this post is very helpful, thank you!

    When the user does not select any business unit, the report processes “all” business units internally, however, ALL business units do not get printed on the report. How do we achieve this? Thanks!

    • If you mean that no *filter* for business unit is shown, that’s the correct behavior. You said that user didn’t add any filter for business units, so the report shows unfiltered data (for all business units) but the list of filters is empty (because no filter was added).
      You can print all business units on your report if you want, but that’s something else than showing filters (which was the topic of my post).

22. So how can we display all business units if no filter is provided? Could you please share/point me to another link as this is only about filters? Thanks.

23. Hi Martin,

    I’ve created all the steps above and validated that the tmp table is getting the data inserted. On the report, I created the dataset, I see all the fields in the report and when I create a new tablix and set it to the tmp dataset. I create the fields for the report, build and deploy the report. In the AOT, I deploy the element then open the report. The issue I’m getting is that the data comes in empty. I’ve cleared cache, remove auc, restarted AOT services, and restarted the ssrs report server and I’m still not receiving any data for those fields from the TMP dataset. Any suggestions on what I am missing or why I’m not getting any data?

    Thank you,
    chris

24. Thanks Martin That is very useful, as always.

25. Thanks Martin for sharing your thoughts and experience on the subject.
    I’m currently using the approach in one of the reports on customer’s Ax 2012 R3 environment and it helped me a lot.
    The only thing I got into trouble with is the temporary table type and scope (problem is explained and discussed between you and post author on https://community.dynamics.com/365/financeandoperations/f/dynamics-365-for-finance-and-operations-forum/234605/ssrs-display-dynamic-parameters-on-report).
    Basing on the above, I’ve ended up with almost identical approach as described in your post except for:
    – custom TempDB temporary table instead of InMemory table
    – passing range value tempDB buffer to the method (and slightly changing the method itself) instead of using assignment, like:
    MyQueryUtils::rangesToTable(this.parmQuery(), tmpMyRangeValuesTable);
    instead of:
    tmpMyRangeValuesTable = MyQueryUtils::rangesToTable(this.parmQuery());

    Hope this could help someone having similar issues to mine.

    Thanks again and kind regards

Comments are closed.