Skip to main content

Using SQL query in report designer


SadokHanini
Freshman II

Hello

 

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

 

Thanks.

Did this topic help you find an answer to your question?

9 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • May 5, 2021

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

 


Gabriel Michaud
Captain II
Forum|alt.badge.img+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


SadokHanini
Freshman II
  • Author
  • Freshman II
  • 42 replies
  • May 6, 2021

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
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • May 6, 2021

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


SadokHanini
Freshman II
  • Author
  • Freshman II
  • 42 replies
  • May 6, 2021
Naveen B wrote:

@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
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • May 6, 2021

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

 


SadokHanini
Freshman II
  • Author
  • Freshman II
  • 42 replies
  • May 6, 2021

@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
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • May 6, 2021

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. 


BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings