web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Filtering Query based ...
Microsoft Dynamics AX (Archived)

Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

(0) ShareShare
ReportReport
Posted on by krishna.rao@dax 3,002

Hi all,

Requirement   : We need to filter dynamically query based dataset based on another dataset which may be of different type(Query based or RDP).

For example let us consider two datasets in an SSRS report.

DataSet1 : Query based (we need to filter this query based on dataSet2)

DataSet2 : Query based or RDP.

both dataSets have one field in common .so based on the dataSet2 we need to filter Dataset1

Could you please guide us how to achieve this.

Thanks,

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Hariharans87 Profile Picture
    Hariharans87 3 on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    I believe there is no option available in AX 2012 SSRS reporting.

    Better, Build the logic (with relationship) in the RDP class and store the records on the temp table. Use a single dataset and achieve your requirement.

    If you are using DataSet1 as datasource for tablix, you can use tablix filter option.

    Select the tablix, go to properties and select filter property and use your field in the condition section.

    Please let me know if you have any doubts on this.

  • krishna.rao@dax Profile Picture
    krishna.rao@dax 3,002 on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    Hi Hari,

    Thanks for the reply.

    Here we wish to use the query based dataset in multiple reports..so we think if we filter the dataset1 based on dataset2 we can reuse the query and no need to right the logic in each report..

    The requirement is as below..

    DataSet1 - holds the query including Global address book tables for fetching the related party data.

    There may be reports specific to Customer(eg. Customer transactions report),Vendor(eg. Vendor transactions report), Company ..So based on the party(in dataSet2) we will pass the parameter partyId to the dataset1 to fetch related data.

    As you said if we use the singe dataset, in future there may be need to add some extra fields based on the client need. so every time we need to change rdp logic and have to add more fields in temporary table.Instead of that if there is a query that would be filtered based on the other ds would reduce the effort. 

  • Suggested answer
    Hariharans87 Profile Picture
    Hariharans87 3 on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    Sorry, We can't relate two datasets in the SSRS report.

    The report supports multiple datasets but as in AX forms, these datasets cannot be joined and they remain independent.

    Please try to create display method and use it on the SSRS report section.

  • Verified answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 48,887 User Group Leader on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    there is a very minor possibility that is relating two DataSources with shared parameter. I am not sure if that can fit to your needs or not.

    technet.microsoft.com/.../cc624720.aspx

  • krishna.rao@dax Profile Picture
    krishna.rao@dax 3,002 on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    Thanks HariHaran,Sohaib,

    @Sohaib : in your link its related to two querybased datasets. is there possibility to link two datasets one is queryBased and other is RDP..I believe it wont possible..

    Please let me know if there is possibility to achieve

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 48,887 User Group Leader on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    does not matter what DataSources whether query based or RDP based.

    if datasources do have a same parameters, it is possible to send a common value.

  • krishna.rao@dax Profile Picture
    krishna.rao@dax 3,002 on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    thanks Sohaib,

    Let we consider the scenario.

    Dataset1 - Querybased (address related tables)

    Dataset2 - Rdp

    Consider we are generating Customer Transaction report (SSRS Reports\Reports\CustTransList) for all the customers in the company.

    In this case how can we pass the parameter to the DataSet1 to  fetch the address of the each customer.

    Report should be in way that

    each customer transactions data should be in different page.

    it should display address of the customer(from dataSet1) in header.

    Thanks,

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 48,887 User Group Leader on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    In this case how can we pass the parameter to the DataSet1 to  fetch the address of the each customer.

    does not matter DataSet1 or Dataset10, a controller class can be used for this purpose to pass parameters.

    each customer transactions data should be in different page

    you can group by customer id and can put page break after each group

    it should display address of the customer(from dataSet1) in header.

    add address in same grid where you will show customer transactions. or add customer address inside group of Customer. so that it will keep on changing with each changing customer. if you will just put one textbox in header you have only 1 option that FieldFirst which will go wrong as it will always show address of only 1 customer. if you want something to change put it under grouping/tablix

  • krishna.rao@dax Profile Picture
    krishna.rao@dax 3,002 on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)


    Thanks sohaib,

    it should display address of the customer(from dataSet1) in header. 


    I can add the address  field in grid. but the address field how it will show the current party address.

    tp display related party address we need to filter dataSet1 with customer party Id.could you please let us know how you filter data for each customer,

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 48,887 User Group Leader on at
    RE: Filtering Query based dataSet in SSRS Report in Visual studio(AX2012)

    your first dataset is query based, you can write a display method on custtrans table and it will return you address. it will give you address of current party

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans