Solved

CTE in DevExpress report designer

  • 6 December 2022
  • 5 replies
  • 112 views

Userlevel 1
Badge +1
  • Apprentice
  • 8 replies

Hi All,

Is it possible to use Common Table Expressions (CTE) in DevExpress report designer like in SSRS where you can use CTE’s in datasets ?

I know you can put CTE in a view in Software Factory and use this view in DevExpress designer. 

I get an error (see picture).

 

icon

Best answer by Mark Jongeling 17 January 2023, 10:45

View original

This topic has been closed for comments

5 replies

Userlevel 4
Badge +3

Hey Tim,

I'm not sure if I understand your question for the full 100%… but I believe that this error notification is a security measure taken by DevExpress to prevent SQL injections: https://supportcenter.devexpress.com/ticket/details/bc3280/default-validation-for-custom-sql-queries-allows-only-select-statements

I also noticed by executing a short search that it is possible to disable this specific validation: https://supportcenter.devexpress.com/ticket/details/t395653/a-custom-sql-query-should-contain-only-select-statements-when-moving-from-15-1-4-to-15

Is it an option to move the CTE to the underlying view that is called upon by the DevExpress report and use the CTE to compile the required data, that you can then call upon from the DevExpress report? Personally I prefer this option because it also simplifies the possible future maintenance on the report.

Userlevel 5
Badge +8

Hi @tim

Can you please let us know if you still require assistance regarding this topic? Or has Renée her answer helped you out?

Userlevel 1
Badge +1

Hi,

I checked this link https://supportcenter.devexpress.com/ticket/details/t395653/a-custom-sql-query-should-contain-only-select-statements-when-moving-from-15-1-4-to-15 but I can’t find the property DisableCustomQueryValidation in DevExpress report deisgner. I’m using the report designer from Thinkwise Community Portal.

Userlevel 7
Badge +23

Hi Tim,

For as far as I can see, this option is False by default and cannot be switch to True currently. I have set out a question to my colleague that managed the product. Once I have response, I or he will reply here.

Userlevel 7
Badge +23

Hi @tim,

Feel free to create an Idea for this if it is still something you would to see added to the DevExpress Report designer.