Creating a Report from Query

(0) Share

Report

Posted on by Lionel07

495

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

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report

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

Was this reply helpful? Yes No

Community site session details

Creating a Report from Query

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Creating a Report from Query

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates