Reporting on MVRS along with other Variables

[Sue Frost]

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 · Accepted solution]

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.