Database View - sc_request and sc_task

[shane_davis]

I am trying to create a database view which will show all Requests (sc_request), Requested Items (sc_req_item) AND all Catalog Tasks (sc_task).   My goal is to create a report off of the database view to where I can show the columns for the requests, items, and catalog tasks.   Our reason for this is because we used to create REQs without any child structure.   Now, we use the REQ>RITM>TASK structure.   We need 1 report to show all tickets.

Thank you for any help you can give!   Below is a screenshot of what I have, but I do not know how about the Where clause nor Left join.

[Deepak Ingale1]

Any particular reason why you want to go with database view only?

You can do reporting on task table as well and can select task type 'req' or 'ritm' or 'catalog task' etc.

If you do it on even 'catalog task' table, you will also be able to add 'RITM' and 'REQ' fields by doing dot walking

You will then be required to group those by 'REQ'

[shane_davis]

Deepak,

        I created a report on the Task table, but it did not let me include the Catalog Task variables in the report.   If I create the report off of the Catalog Task table, I can't show REQs which have no children (no RITMs nor TASKs).   This is the reason I need the database view.

Shane

[Shree14]

Hi @shane_davis 

We have the similar Requirement to get all the RITMs and TASKs irrespective of the fact that Tasks created or not. But we are not getting Variables under Available Section or on Filter Conditions while creating report using Database view. 

Please suggest if you have any thoughts on the same.

[shanedavis]

Hi @Shree14, I looked at my database views, but there isn't one for this.  Since this was 7 years ago, I definitely don't remember what I did, but it wasn't a database view.  Maybe you aren't getting variables because they are stored in the item_option_new table?  That's just a guess since I don't know anymore about your design.  I'm sorry that I can't be of more help!