Query Builder-Report Details

[naveen_kumar29091984]

Please tell us how to create build a query for above table.If somebody help it will be much appreciated.The query which I have written above not fetching Scheduling Recipients .

Regards

N

[former_member185603 · Accepted solution]

Use this query to get the sender name info.

Select SI_NAME,SI_UPDATE_TS,SI_SCHEDULEINFO.SI_DESTINATIONS from

CI_INFOOBJECTS WHERE SI_PARENT_CUID= '<CUID of the report>'

SI_SENDER_NAME: lists recipients names

[naveen_kumar29091984 · Accepted solution]

Hi Jawahar,

But I Have list of Report Names approx 700 reports?

I want to put directly Report Name and also it should show one report one schedule details not so many instances.

I don't want any other details apart from this .

[former_member185603 · Accepted solution]

Use this query

select SI_UPDATE_TS,SI_SCHEDULEINFO.SI_DESTINATIONS  from

CI_INFOOBJECTS WHERE SI_name= '<report name>' and si_recurring=1

[former_member185603 · Accepted solution]

You can try this as well.

SELECT top 10 SI_SCHEDULEINFO.SI_DESTINATIONS, SI_NAME, SI_ID FROM CI_INFOOBJECTS WHERE SI_KIND = 'webi' AND SI_SCHEDULEINFO.SI_DESTINATIONS LIKE '%CRYSTALENTERPRISE.SMTP%' AND SI_INSTANCE = 1

[naveen_kumar29091984 · Accepted solution]

Hi Jawahar,

Can u please tell me how to give the report name?

If i give report name,I need the details like above table .it should not display other details

[former_member185603 · Accepted solution]

SI_NAME in where clause,

You can use "select * from" and pick the fields you need.

[naveen_kumar29091984 · Accepted solution]

Thanks

[naveen_kumar29091984 · Accepted solution]

Can you please also tell me which object name I need to use for frequency --Daily ,weekly or monthly?

[Former Member · Accepted solution]

si_scheduleinfo.si_schedule_type contains the frequency.

It returns an integer value. refer to the below table to get correcponding type with the value.

Thanks,

Prithvi

[naveen_kumar29091984 · Accepted solution]

Thanks,But could you please tell me what each integer indicates 1,2,3,4,5,6,7, etc

[Former Member · Accepted solution]

Its specified in the above image.

Refer to the values corresponsing to each type.

Thanks,

Prithvi

[naveen_kumar29091984 · Accepted solution]

Sorry Pritivi,I didn't see full Image...

Nice thanks a lot

[naveen_kumar29091984 · Accepted solution]

Hi

Is there anyway that I can Pull all Scheduled reports in above format.

[Former Member · Accepted solution]

You would need to use si_instance=1 in the where clause of your query

Something like select * from ci_infoobjects where si_instance=1.(Instead of * use the columns yopu require)

By default it would result in 1000 objects. If you require more than that, you would need to use sql TOP function

Thanks,

Prithvi

[naveen_kumar29091984 · Accepted solution]

No Pritivi,

If we put Instance=1 ,it will take all instances only..I think it is something we need to do with SI_Update_TS

[Former Member · Accepted solution]

You required query to get all scheduled reports. Right?

I gave you a query through which you would get all the instances. As when a report is scheduled it generates an instance.

If you want to know which main reports are scheduled, you would want to check the property si_lastruntime property.

Thanks,

Prithvi

[naveen_kumar29091984 · Accepted solution]

Yes,Correct..I do agree.But  we need only recurring instance not paused nor success at that time , we need to give Si_recurring =true? is it correct?

[Former Member · Accepted solution]

If you want only recurring you would need to include si_recurring=1 and si_schedule_status!=8 in your where clause.

Si_recurring contains both recurring and pending as this property is true for both. schedule status 8 is the status of paused.

Thanks,

Prithvi

[naveen_kumar29091984 · Accepted solution]

Thanks a lot ,can you also please send me SI_Schedule_status for all integer.

[Former Member · Accepted solution]

See below for the list

[naveen_kumar29091984]

[Former Member]

Can you give more details around your question ' Is it possible to send list of documents for Query Builder like each object what it will do?'

As far as your second question goes, as per my understanding, you want to know which reports are not scheduled at all.Let me know if my understanding is correct. If yes, for that you would want to look for the property si_lastruntime. The reports which do not contain this property are the once not scheduled at all.

select * from ci_infoobjects where si_instance=0 and si_last_run_time=null

(Instead of * use the columns you require)

Thanks,

Prithvi

[naveen_kumar29091984]

Hi Prithvi,

Thanks,

In this below Query ,I am getting only publication format ,I am not getting Deski,Excel,PDF,Txt.