By using this website, you agree to our Terms of Use (click here)
Good Afternoon,
I'm having an issue that seems to affect any custom database view when attempting to access the data in the report designer. When tied to the report designer, only the first row displays, for the number of times that results exist. For example, I have a view that is the current months birthdays. It has 12 results this month, in the report designer I get 12 rows of the first result when placed in the detail section. I'm currently on version 5.30. The use of views and custom SQL code has been critical to writing "maintainable code". For example, after converting a code file written completely in the report designer to SQL, we went from 5500 lines of code (report designer pseudo language) to 380 lines of SQL code. Insane reduction, has anyone seen this issue before? Also is there a known fix?
Also, wanted to add. In the general inquiry, the data behaves as expected.
Wow, that's weird. I have not seen this before, but I'd be interested to know if you get the same behavior on a recent version of Acumatica.
Hi Tim,
I was able to figure this out. Apparently if you do not use the IsKey = true in the dac defintion. The report designer gets confused and can not tell when a new row occurs. When setting the key you want to ensure that it is unique to every row. The easiest way to do this in SQL is to make a row defined as a running row count and then make that column the key indicator. This is at least true in the 5.3 versions.
Thanks for sharing your solution @sqlrunner. And I'm glad that you were able to get it working.