Variables under Available Colum is not showing up while creating a Report using Database View.

[Shree14]

Hi Experts,

I'm creating a Report using Database View[sc_req_item & sc_task]. 

But while creating the Report I'm not seeing "Variables+" under Available Column to add in the Report. 

Can anyone suggest the work around to get the Variables on Report.

When I checked only for sc_req_item I could get Variables in Available Slush Bucket. But not with the Database View. Why So?

Thanks,

Shree

[Brad Bowman]

You could add the sc_item_option and sc_item_option_mtom tables to your database view, but I'm curious about the purpose of a view on sc_req_item and sc_task.  Would the same purpose be served by a report on the sc_task table - which also contains the variables object - dot-walking on the request_item field to show the fields needed from the sc_req_item table?

[Shree14]

Thanks for the reply, Brad.

The purpose is, I'm creating a Report for a particular Catalog Item which includes RITM, TASK created after Approval and some times we won't have TASK for the Request if it;s still in Pre-Approval Stage. 

If I create the Report on sc_task Table I won't get those Requests without Task created.

Regards,

Shree

[Brad Bowman]

That makes sense.  Adding the variable tables to your Database View will cause there to be a row for each populated variable, so that may not be what you're looking for in this case, but the View would look something like this:

The Question and Value columns from the 'op' table are what you're looking for.  The addition of the sys_user table is due to what you will see regarding reference variables when doing it this way.  The Value of the variable will be the sys_id, so adding in the referenced table allows you to display a field (Name) from that table to make it readable.