Skip to main content

Using SQL query in report designer

Hello

 

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

 

Thanks.

9 replies

Naveen Boga
Rank
Userlevel 7
Badge +19

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 :) 

 

Naveen B
Gabriel Michaud
Rank
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

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
SadokHanini
Rank
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 ?

 

 

Naveen Boga
Rank
Userlevel 7
Badge +19

@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?

Naveen B
SadokHanini
Rank
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

Naveen Boga
Rank
Userlevel 7
Badge +19

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

 

Naveen B
SadokHanini
Rank
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 ? 

Naveen Boga
Rank
Userlevel 7
Badge +19

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. 

Naveen B
BenjaminCrisman
Rank
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


Terms & ConditionsCookie settings