Seeking help with Database View to produce a list of Reports and the Dashboard(s) in which they are displayed

[apjohn2]

Hello!

I followed the advice in this post to create a Database View that I hoped would produce a list of Reports and the Dashboard(s) they're in, to attempt to update all Reports (for one particular Dashboard), en masse, either by list edit or as a way to help build a background script.

When I click the 'Try It' related link in that DB View, I get two identical errors (image below):

Syntax Error or Access Rule Violation detected by database ((conn=857505) Unknown column 'prefep.value' in 'on clause')

Syntax Error or Access Rule Violation detected by database ((conn=857505) Unknown column 'prefep.value' in 'on clause')

I honestly have no idea why these errors are showing, what they mean, or how to correct them to achieve my goal. This is what I'm seeking help with from the best community evar!

Note: I have gone to the sys_portal_preferences table and verified there is in fact a 'value' column there.

Backstory/Why this is Important

A colleague at work created a Dashboard that contains 27 different Reports in various tabs. She needed help modifying the Share settings in these reports so that 'itil' role users can see them in the Dashboard (which had already been shared with 'itil' role). I was having a hard time finding a way to produce this list such that I can modify the sharing in the Reports in bulk, or so that I could export the list to Excel to help with building a background script--something like that.

The closest I could get was using the Performance Analytics > Admin Console, selecting 'Dashboards' and then the specific Dashboard I need, then selecting the 'Reports' tab. This is a great first step, but it's not quite sufficient for what I need, which involves using List Edit and/or exporting the list to manipulate outside of ServiceNow--none of which I can do in the Admin Console.

The DB View:

The Errors:

Thank you in advance!!

[tkrishna29 · Accepted solution]

Hi,

Instead of using t.page = p.page, use t_page = p_page. ServiceNow syntax is bit different from typical SQL syntax.

Something like this:

Update all the rows and try again.

If it still doesn't work, just try adding 1 row at a time for joins to see where it fails.

Regards,

Krishna

[apjohn2]

It worked but it took some trial and error. I changed all the "Where clause" values, replacing all the "." with "_". I got different errors, but this was good because it pointed me to a typo I missed previously (a missing letter in one of the prefixes).

I fixed that, tried again, and got two errors very much like the original two. So I changed the "_", in the "Where clause" values the errors referenced, back to ".", and ran it again.

I got two new errors, and again, found the referenced "Where clause" values and changed them from "_" to ".".

Finally after this it worked. In the end, then, I have a mix of "." and "_" in the "Where clause" values. This is bizarre and seems really buggy to me but it works and I don't have to dink with it very often so leaving "well enough" alone.

Marking your answer as correct Krishna, with many thanks!