I need to send an email to a group, but it should show dynamic ( Assigned to is dynamic "ME" )

[Gaspar Fiks]

Hi Community! 

I'm trying to send a report to all ITIL users, but this reports should show dynamic results for each user. 

If I create a Scheduled Report it will show for all users the same report. 

I was thinking in create a Scheduled Job , loop through all ITIL users and send the report. 

Could anyone help me ?

Thanks!

[jMarshal]

This will be difficult if you are trying to "send" the report (assuming you mean a scheduled email).

You can set it to "run as"...but you would need each user to initiate that, for it to dynamically read them as "me".

I would recommend the scheduled email be a link, not the actual report...and the link takes them to a live dashboard using the same report that you're referring to (with the dynamic "me" assigned-to), which will always be accurate for the person who is clicking on that link.

If you really want to do it via hard-generated report, for each itil user, you'll need a specific report for each one. You can use a one-time background script to create this...but it will need to be maintained by creating a new report for the itil user, each time a new itil user is onboarded.

I suppose you could write a daily script that will delete and rebuild the reports based on itil group membership ....also, remember that there isn't an "itil group" oob, so the logic there is a bit "tricky" too - you'll need to recursively build the group based on a querey against sys_user, looking at the associated role column of each row.

[Gaspar Fiks]

Thanks @jMarshal for your response. Can you elaborate a little more on " one-time background script to create the hard-generated report"?

The stakeholder didn't want the link to the report. So I'm trying to find other solutions. 

Thanks again! 

[jMarshal]

You bet! 

It's quite elaborate, but should work...and since I don't have a script(s) written for you, it might end up being just as much work to write the script(s) as it is to do this manually (depends on how many itil users we're talking about).

...and to be clear, this is theoretical, I've never actually done this. The method I'm suggesting is no different from doing it manually with "insert & stay", you're just leveraging a script to do the repetitive work -- the theory behind what you are doing is no different.

To my knowledge, there is no oob method/feature in S-Now to dynamically build multiple unique reports for export, from a single (dynamic) report record.

---------

Basically, you're gonna need a separate report for each user, hard-coded with their name as the "assigned to" in the query/filter...and each of these reports will be an individual record on the sys_report table.

If you have one report already (one report built as a template), you could write a script that would read the values from that record and insert a new record (technically, you don't *need* the template as your script, you could hard-code everything, if you want) to the table with all appropriate values (copy everything except for the report name and query/filter).

At the end of the script, you would have a for-loop which glides to the sys_user table (where user role = itil) and for each record returned in the glide query, generate a unique report name and unique report query/filter, leveraging the specific user (name/sysID) info being returned and insert to the report table (the insert is in the loop).

You can run once in background and it will create as many rows on sys_report as you have iterations for your loop @ the end of the script (all of the users on sys_user who have ITIL role)...and each should be hard-coded to be unique for each itil user...which you can then use for a 'scheduled report send' job.

You can take a similar approach (scripting) to creating the scheduled job record as you'll need a unique record on the "sysauto_report" table, for each individual who is receiving the report, also.

Further, since this is not dynamic, it needs to be maintained. When a new itil user is added, a new report and scheduled send job needs to be created for them.

...of course you could write business rules (on sys_user) that would automatically create the report & scheduled send job when a new itil user is added...but that's just another consideration for your hypothetical solution.

You could even go so far as to write a series of scripts that run daily and look for the reports that were created the day before and deletes them, then runs again to rebuild them (ensuring you always have a single report for each itil user, every day)...and does this for the scheduled jobs, too. Then it's a bit more of an "ongoing solution" than a one-time thing, which needs manual maintenance.

Hope this helps, I understand the need/desire to dynamically build multiple unique reports for export, from a single (dynamic) report record...perhaps you can put that through ideation on Now Support and it will be added as a platform feature, in the future.