How to filter reports on Attributes
Filter reports gives you the option to filter on "Attributes". What is the correct incantation to fill in that will give something else than "There was an unhandled SQL error!"? --Ingeborg -- Ingeborg Østrem Hellemo -- ingeborg.hellemo@uit.no Dep. of Information Technology --- Univ. of Tromsø
On Thu, 02 Feb 2023 10:13:28 +0100 Ingeborg Hellemo <ingeborg.hellemo@uit.no> wrote:
Filter reports gives you the option to filter on "Attributes".
What is the correct incantation to fill in that will give something else than "There was an unhandled SQL error!"?
NAV's report system comes with many built-in reports. I cannot find any that mention "attributes". Could you please provide more details? -- Sincerely, Morten Brekkevold Sikt – Norwegian Agency for Shared Services in Education and Research
morten.brekkevold@sikt.no said:
NAV's report system comes with many built-in reports. I cannot find any that mention "attributes". Could you please provide more details?
Sorry, I was sure I mentioned it: /report/room --Ingeborg -- Ingeborg Østrem Hellemo -- ingeborg.hellemo@uit.no Dep. of Information Technology --- Univ. of Tromsø
On Fri, 03 Feb 2023 11:19:59 +0100 Ingeborg Hellemo <ingeborg.hellemo@uit.no> wrote:
morten.brekkevold@sikt.no said:
NAV's report system comes with many built-in reports. I cannot find any that mention "attributes". Could you please provide more details?
Sorry, I was sure I mentioned it: /report/room
The Attributes column is a representation of the JSON blob stored in the room.data field. Unfortunately, I don't think the report tools is equipped to produce queries that can match the contents of JSON data stored in PostgreSQL. I would consider a different approach. Assuming you store structured data in the custom attributes, you can change your room report to extract those as separate columns, which you can then filter as normal columns. The room report selects the column `data`, but let's say you have a custom attribute named 'contact' that you add to rooms, you can make a column out of this by adding this to the select statement:
data->'contact' as contact
I.e. you get something that begins like this:
$description = "Information about rooms."; $sql=" SELECT roomid, count AS nb, locationid, descr, position, position[0] as lat, position[1] as lon, data->'contact' as contact FROM room
-- Sincerely, Morten Brekkevold Sikt – Norwegian Agency for Shared Services in Education and Research
Yes, that makes sense! Thank you for reminding me of the possibilities in report.conf.d It was just since the Attributes field was available for filtering in the standard room report that I began to wonder that you had put some magic in place. -- Ingeborg Østrem Hellemo -- ingeborg.hellemo@uit.no Dep. of Information Technology --- Univ. of Tromsø -----Original Message----- From: Morten Brekkevold <morten.brekkevold@sikt.no> Sent: fredag 3. februar 2023 17:26 To: Ingeborg Østrem Hellemo <ingeborg.hellemo@uit.no> Cc: nav-users@lister.sikt.no Subject: Re: [Nav-users] How to filter reports on Attributes On Fri, 03 Feb 2023 11:19:59 +0100 Ingeborg Hellemo <ingeborg.hellemo@uit.no> wrote:
morten.brekkevold@sikt.no said:
NAV's report system comes with many built-in reports. I cannot find any that mention "attributes". Could you please provide more details?
Sorry, I was sure I mentioned it: /report/room
The Attributes column is a representation of the JSON blob stored in the room.data field. Unfortunately, I don't think the report tools is equipped to produce queries that can match the contents of JSON data stored in PostgreSQL. I would consider a different approach. Assuming you store structured data in the custom attributes, you can change your room report to extract those as separate columns, which you can then filter as normal columns. The room report selects the column `data`, but let's say you have a custom attribute named 'contact' that you add to rooms, you can make a column out of this by adding this to the select statement:
data->'contact' as contact
I.e. you get something that begins like this:
$description = "Information about rooms."; $sql=" SELECT roomid, count AS nb, locationid, descr, position, position[0] as lat, position[1] as lon, data->'contact' as contact FROM room
-- Sincerely, Morten Brekkevold Sikt – Norwegian Agency for Shared Services in Education and Research
On Mon, 6 Feb 2023 12:22:42 +0000 Ingeborg Østrem Hellemo <ingeborg.hellemo@uit.no> wrote:
It was just since the Attributes field was available for filtering in the standard room report that I began to wonder that you had put some magic in place.
The filter form is auto-generated based on the columns returned by the query. It can match against most "standard" fields and data types, but it is too simple to understand that the `attributes` column is a complex data type. The simplest improvement that could be made here is to detect that the column is too complex and *not* generate a filter form entry for it. -- Sincerely, Morten Brekkevold Sikt – Norwegian Agency for Shared Services in Education and Research
participants (3)
-
Ingeborg Hellemo -
Ingeborg Østrem Hellemo
-
Morten Brekkevold