Web Reports Filter (database)

We are running IEM with a DB2 database (I assume this would apply to SQL Server too). We would like to pre-populate the Web Reports database with role filters during the IEM install process, so that these are available to users “out-of-the-box”. This means I will need to manually load the filters into the table using SQL commands. This isn’t an issue.

The issue is that I need to understand the structure/format of the filters. My intention was to simply extract existing filters from the database, understand their structure, create my own new filters in the same way, then add them into the DB table using SQL commands. However, the “saved_filters” table has a column called “filterdescription” which appears to hold the actual filter definition. The problem is that this is a BLOB data type and is not human-readable. I have used the SQL cast function to convert this to a varchar, but the output still appears to be encoded. I have run the output through base64 decoder but this did not help.

Does anyone have any knowledge/understanding of how the filter content must be expressed? Or if IEM converts/compresses this in some way for storage in the DB?

Direct manipulation of the DB is not recommended as we may (and will) change the schema from time to time.

Alan’s warning is definitely correct, and trying to generate the filter definitions outside of Web Reports would likely be error prone. If your filters are using only external content that is provided by IBM, then it should be possible to create the filter in one instance of Web Reports, and then copy the resulting SQL record into a new instance. As you need to support new versions, you can upgrade your source instance to ensure the schemas match.

If you still want to see the definitions, you can use this query (from a 9.1 WR):

SELECT [FilterID]
  ,[FilterName]
  ,[CreatorID]
  ,convert(varchar(max),convert(varbinary(max),[FilterDescription])) as FilterDescription
  ,[IsPublic]
  ,[IsAdhoc]
  ,[LastModified]
FROM [BESReporting].[dbo].[SAVED_FILTERS]

Thanks Steve. This is exactly the approach we would take. Our IEM deployments would all be at the same version level, so we would be able to ensure that the code is developed, tested, and installed against the same version.

I fully agree that directly manipulating the database tables is not ideal. However, at this time the REST API does not support content creation within Web Reports. It is only “read only”. To this end I have raised a RFE (71732).