Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Creating a Report from...
Microsoft Dynamics AX (Archived)

Creating a Report from Query

(0) ShareShare
ReportReport
Posted on by Lionel07 485

Hello Guys, I have a question.... I need to created a report in AX 2012 R2...

I already know how to create the report and how to do everything in visual studio but each time I do it am only getting data form the first table(datasource) in the query...

These are the tables am using:   USTINVOICEJOUR, CUSTTRANS, CUSTSETTLEMENT

This is the query in SQL which works fine....

SELECT
    [INVOICEID]
   ,[INVOICEACCOUNT]
   ,[INVOICINGNAME]
   ,[CUSTSETTLEMENT].DUEDATE
   ,[CUSTSETTLEMENT].CREATEDDATETIME
   ,CUSTSETTLEMENT.TRANSDATE
   ,CustSettlement.[SETTLEAMOUNTREPORTING]  
   ,[INVOICEAMOUNT]
   ,[PAYMENT]      
   ,[INVOICEAMOUNTMST]
  FROM [CUSTINVOICEJOUR] join [CUSTTRANS] ON CUSTTRANS.INVOICE=CUSTINVOICEJOUR.INVOICEID AND
 [CUSTTRANS].AccountNum = [CUSTINVOICEJOUR].InvoiceAccount

 and [CUSTTRANS].TransDate = [CUSTINVOICEJOUR].InvoiceDate

 and [CUSTTRANS].Voucher = [CUSTINVOICEJOUR].LedgerVoucher
 join CUSTSETTLEMENT

 on CUSTSETTLEMENT.TransCompany = [CUSTTRANS].dataAreaId

 and CUSTSETTLEMENT.TransRecId = [CUSTTRANS].RecId

 and CUSTSETTLEMENT.AccountNum = [CUSTTRANS].AccountNum

 WHERE [CUSTSETTLEMENT].DUEDATE = [CUSTINVOICEJOUR].DUEDATE

 AND [CUSTSETTLEMENT].DUEDATE between'2018-03-01 01:00:000' AND '2019-03-15 23:00:000'
 AND [CUSTSETTLEMENT].TRANSDATE <'2019-02-28 0:0:0'
 AND [CUSTINVOICEJOUR].INVOICEID LIKE 'ACP%'
 AND [CUSTINVOICEJOUR].INVOICEACCOUNT='110214M'

I would like for the where conditions to be dynamic where the user will then go and enter this info and the report prints according. IO know whats i get the query then dropping that in visual studio should not be a problem

Any help would be greatly appreciated if this can be achieved.

*This post is locked for comments

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: Creating a Report from Query

    Hi Lionel,

     

    Have you considered using Procedure and editing code to complete logic?

    Procedure: It can set some variables for you to enter, specify the time range, and specify the InvoiceID.

    create procedure Createreportdynamically(

       //Define variables ,for example,

       @Begindate datetime,

       @Enddate datetime,

       @TransDate datetime,

       @Invoice nvchar(50),

       @InvoiceAcc nvchar(50))

    as

    BEGIN

    Insert your SQL here

    WHERE （Match with your custom variables)

    END

     

    exec Createreportdynamically '2018-03-01 01:00:000','2019-03-15 23:00:000','2019-02-28 0:0:0','ACP','110214M'

    Code: Backend development can access the database and call Procedure directly. Of course, parameters are what you are asked to enter when you create a report

    You can refer ：https://www.cnblogs.com/armanda/p/6045963.html.

     

    Best Regards ,

    Blue Wang

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Creating a Report from Query

    You should not use direct SQL in AX because of various reasons you can easily find in the web. For simple report you can use AX query as described here community.dynamics.com/.../developing-ssrs-report-using-query-in-microsoft-dynamics-ax-2012

    But personally I prefer to build RDP based reports.

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans