ServiceNow - Custom report issue

Hi, Happy new year to everyone.

I need some help on pulling the report. I have a requirement where to pull a report for specific catalog item based on specific variables since i cannot do it directly on RITM table i am trying to pull it from sc_item_option table. I see this table has lots of data can anyone please suggest what will be the best of doing this.

My requirement is i need to pull a report a on Top 50 application which were submitted via catalog item. Since we have a Application variable’s like Application #1, Application #2,Application #3,Application #4,Application #5 so i am thinking to a pull a report on sc_item_option table and say Question startswith Application and group by Value and sort by descending which give’s the top to bottom application names.

You can configure a report on sc_req_item or sc_task table and give condition of your catalog item in condition builder of report.also, you can verify variable values in same report. check below

It sounds like the way the data is stored is part of the problem. You can’t report on 50 variables as if they were one.

Now, you could probably make a database view to do this maybe?

Really to report the way you’re describing you want all application variables in one column.

| ----- | ---------- |
| task1 | okta      |
| task1 | servicenow | 
| task2 | okta     | ```

You could do that w/remote tables,
you could do that w/scripted metrics I guess
you could do that w/a scripted ui page or portal

I dont think there’s a way to report on it w the reports in sn today though.

You reporting on sc_item_option is a way too you could additionally qualify that to say and my parent has a item of “insert item name here”

Thanks for the reply and what you understand is correct i tried with Database view i dont see a correct data i am not sure what is wrong:

I tried with query but it did not worked for me, it gives inaccurate data

I never tried with remote tables, do you have references?

after reading about Remote table i am assuming since the data is stored temporarly may be the data is lost but we want this to have for ever since i want to have this is on report

So is the intent to provide them with a one time report, or they want to be able to make these kinds of reports?

No, we need to add this report on a dashboard and they monitor the top application and decide which application getting more request

I would look at improving design of existing catalog item. you shouldn’t have variable for each app name, you can have one reference field which maintains app names. this way it will be easier to manage and maintain in future. e.g if you add another app in future, you don’t have to create another variable