Reporting on MVRS along with other Variables

Sue Frost · ‎07-28-2023

We recently built a service request for overtime reporting.
The SR has two multi-row variable sets and a number of other variables.

I need to create a report that uses both sets of variables.

This post was very helpful. I've built the database view to join the MRVS sets to the RITM. I'm stuck on accessing the other variables.

Here's the RITM. I need a report to show:

- On behalf of user

- Organization/Function

- the fields from the Oncall MRVS

- the fields from the Overtime MRVS

Here's the database view, based on the article linked above.

The database view results are correct - but they only display the MRVS and the RITM.

And here's the report - easy enough to filter down to the correct SR, but I cannot select columns from the RITM (Approval, Approval set and State in the screen shot).

Column selections:

Do I need to add the other variables to the database view?

Am I selecting the columns incorrectly?

Sue Frost · ‎08-14-2023

Replying to myself with my final solution ...

I wasn't able to get a database view or report built that would join the RITM, regular variables and the MRVS.

My solution was to:

built a table that will store the data I need to report on
write a scheduled job (Job A) to gather all the data and write out records in the new table with the data
- the job contains two functions:
  - first to delete any existing records from the table &
  - second to create new records
create the actual report
schedule that report to run shortly after the Job A script.

I'm attaching the script of the scheduled job.

View solution in original post

Muhammad Khan · ‎07-28-2023

Hi Frost,

If you want to see those fields in the Database View as columns, then you will have to add those fields in the View Fields related list.

Below image might help you.

Sue Frost · ‎07-28-2023

Is that screen shot showing the 'View Table' (sys_db_view_table) table?
I'm not seeing the View Fields related list. The list that is there is 'View Table Field' and it doesn't have a 'Field' field.

Muhammad Khan · ‎07-28-2023

Yes, that image is showing "View Table [sys_db_view_table]".

From there you can add the fields which you want to display as columns in the database view.

Sue Frost · ‎07-31-2023

Thank you.

Oddly, I see different field names on that table, but it's the right one.

Unfortunately, I don't see an option to the Variables [+] option either in the View Table Fields and definitely not in the configuring the column layout or on reports.

I need to get to another variable on the RITM, in addition to the variable set. Ideally, I'd like to do that on reporting so that the user can select the Item that they are reporting on and get the variables for that. (Otherwise, I'm building separate db views for each Item.)