Using SQL query in report designer


Userlevel 2

Hello

 

Is it possible to write a sql query in report designer ?

 

Thanks.


9 replies

Userlevel 7
Badge +17

Hello Sadok,
You can use SQL queries like below in Report Designer.
 

  1. Create a SQL View for the SQL Query 
  2. Create a DAC for the SQL view.
  3. This DAC you can use in Report Designer :) 

 

Userlevel 7
Badge +10

Not directly. You can, however, create a T-SQL View and a DAC on top of it and you will then be able to use this just like any other table in the Report Designer and Generic Inquiries.

@Doug Johnson wrote a blog post on this topic. You can find it here: Technical Tuesday: Building Reports From an SQL View in Acumatica Cloud ERP Software

Userlevel 2

Thanks @Gabriel Michaud , @Naveen B  , I’m already using this solution 
but i wanna know also if it’s possible to add a field in the report that is a sql query ?

 

 

Userlevel 7
Badge +17

@SadokHanini So, you want to add a field in the report, and for that field, you want to add a SQL query to display the value?

Userlevel 2

@SadokHanini So, you want to add a field in the report, and for that field, you want to add a SQL query to display the value?

Yes

Userlevel 7
Badge +17

@SadokHanini  Sorry, Report Designer will NOT support this requirement. 
We can not use it like this.

 

Userlevel 2

@Gabriel Michaud , @Naveen B 

Hello 

i have an issue right now, like you said i have created a view and im using it in report designer
so in Sql i have the right informations but in report designer is displaying only the first line N times.

 

What i m doing wrong ? 

Userlevel 7
Badge +17

Sadok, Can you please share the SQL View and DAC?

You need to maintain the Key fields in the DAC, otherwise, duplicate records will be displayed in reports. 

Userlevel 7
Badge +4

@SadokHanini @Naveen Boga Isn’t this possible by using a subreport?

Use the relations of the subreport as the query and then either call the subreport in or use a variable off the subreport in the main report?

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved