Yesterday, a customer had a question regarding the use of reports on a query. The customer wanted to expose a few specific columns of a query to an external tool. The GetReport hook was exactly the thing that was needed. All the steps were taken to create the report (as explained here)
The result however was not completely what was expected. So let’s go over some specific details that you need to know when working with the reports query
First things first: When creating a report query , you need to specify a user. This user will be used to get the query and it’s items. It is very important to understand this, because Vidyano will use this user to determine the rights for executing the query. This means that, if the selected user doesn’t have query rights on the query, you won’t get any result. In the same way, if the user has some denied rights on a specific column, that column will never be included in the report query.
There are 2 ways to specify the rights:
- You can give query rights on the whole persistent object and deny rights on specific column. This way, new columns will be automatically added to the report query result.
- You can give query rights only on the specific columns you want to expose. if you do this, new columns will never be automatically added to the report query result.
The second thing you need to know, is how null values are handled. The report query will expose the query as xml and will exclude all properties with null values. This means that the number of elements can change for every data row inside the xml. Also important to understand is the difference between null values and empty string values. Empty string values use the empty element syntax. the difference is shown in this small example.
<Straat>Avenue de la Gare</Straat>
the third and last thing you have to remember when working with query reports, is that you are using the query and the underlying persistent object to get and expose the data. This means that every attribute of you persistent object that doesn’t show on the query, will not be included in the query report. This also means that every column that is set to invisible on the query itself, will not be included in the query report. These last two are very handy to remember and can save you a lot of time searching why a specific column isn’t shown in your report query!