Dynamic Custom Report

September 11, 2018 at 7:00 AM

Hi,

We're looking to make a report that auto creates extra columns as soon as they're found in a table, example below:

tblObjectProperty has all of the names of object properties that a user has added, at the moment you can create a static report to pull all this data. What I'm wanting to do is that as soon as a new object property (not value) is added, it automatically gets added to the report.

It's basically a FOR EACH ROW IN TABLE would be in the script.

Has anyone done this or have any experience doing this?

Detlev Eufinger (Flexera Software)
8 years ago
Hi Laim, you can use this little query as an examp le . (All Custom Fields for Computer) select c.hostname ,cf.* from tblcomputer c left outer join dbo.tabCID_cf_cmp_1 cf on cf.ElementID = c.ComputerID
Expand Post
Oliver Berger (Flexera Software)
8 years ago
Hi ?, well, I created something like this before. It's not fun... But here we go. Here are some things you should know. A report in Snow could also include executable code, not only one select statement. You need to take care of Name duplicates in columnames, and seperate Objecttypes. Columns which are not in the Custom Report definition, but present, are available in the Column Chooser. So with a little bit of pivoting and dynamic sql, it should work. Or you abuse the Procedure dbo.objectsearch with something like this exec dbo.ObjectSearch @cid = 1,@userid=1,@objecttypeID= 34 (wished I had the option to use this nice shortcut in my use case...) But it would require always a manual change, using the column chooser, to have them in the report directly. You could get on step further, you would need to change the dbo.tblreport table to add those columns, using a Trigger CREATE TRIGGER (Transact-SQL) | Microsoft Docs , (would be executed when the table dbo.objectproperties changes), attaching something to the data updatejob (so only daily changes) or something inside the report itself (changes for the next report execution). Let me know what you think. Cheers, Oliver
Expand Post

Related Product Forums

→ Flexera One

→ Snow Atlas

→ FlexNet Manager

→ Snow License Manager

→ App Broker

Need help finding an answer?

Ask a Question →

Dynamic Custom Report