how to get a SIMS report of report names?

[iceman]

I have made quite a few reports that all begin with the same word. Is there a way to get a textual report that lists the names of all reports?

 

Preferably this would be filtered by a certain owner/author or better still by some sort of 'report name begins with...' filter.

 

This would be very handy to get the names of the reports that I need to give to the network manager so he can set them up as scheduled reports, rather than copying the name of each of them out by hand.

[howartp]

What are the reports and who needs them?

 

Your question presumably refers to CommandReporter, but are you aware you can schedule your own reports inside SIMS?

 

(I wasn't until recently)

[iceman]

The SIMS reports are ones that I made. They export data to xml files which are then processed by an external MIS application that I wrote in order to produce some specific managment reports for SLT in a specific format. I need the data from SIMS to be up to date on a daily basis, hence the need to have the reports scheduled overnight.

 

The scheduling isn't a problem (well apart from the nornal issues discussed in places like this edugeek thread http://www.edugeek.net/forums/mis-systems/115030-sims-scheduled-reports.html !) My slight 'problem' is the accurate copying of the many report names so that we can arrange for them to be scheduled outside of SIMS if we have the expected hassel doing it inside.

 

However It would also be handy to have the list of report names to go into the documentation that I am writing that records how the system operates and how the different parts interact - I can hear the gasp of incredulity at the thought of documenting a system for future managers of the system, but it's being done anyway.

[SteveB]

How about exporting them, extract to folder, and then use CMD to list and copy

[iceman]

Yes, I started doing that and then thought it'd be quicker just to open each report in turn, select and copy the name and paste it into notepad. This is what I did in the end but then wondered if there was an easier way by asking sims to tell me all the names via a report!

 

Odd that I can get a report of all the aspects / templates / marksheets / categories etc that I have made but it doesn't seem possible to get a report of all the reports I have made.

[Greg]

You can always ask SQL

 

This will give you a list of all of the Reports in SIMS and the user that created them (Pre defined reports will be NULL fir user name)

 

SELECT TOP 1000

[sims].[sims].[db_user].[login_name]

,[sIMS].[sims].[rpt_report_summary].[user_id]

,[report_name]

,[report_description]

FROM [sIMS].[sims].[rpt_report_summary]

FULL OUTER JOIN [sims].[sims].[db_user]

ON [sims].[sims].[db_user].[user_id]=[sims].[sims].[rpt_report_summary].[user_id]

[iceman]

You can always ask SQL

 

I'd love to do that but how do you send SQL to the sims database - and get the results back. What protocol does it use? I've written applications to talk to MySQL, SQLSever, SQLite and even MSAccess but am not aware how to interrogate the sims database.

[vikpaw]

You don't send it to SIMS, you send it to the SQL server which houses your SIMS database.

WARNING : This is unsupported and you do it at your own risk.

If you know what you are doing, are confident, and have access to the SQL, you can fire read queries at the database.

This is why I love my new Engage MIS so much, I can pass custom SQL into report definitions, so powerful

[Greg]

WARNING : This is unsupported and you do it at your own risk.

 

If it was writing to the database then I completely agree and would not even contemplate advising, reading will do no harm (as long as you know what you're doing ).

 

Engage sounds interesting, one of the problems of SIMS is there is so much useful information in there and no GUI based way to access it, hence the threads like this one.

[matt40k]

Isn't there a DBDiag script you can run that gives you the names? Its basically a SQL script, but Capita have signed it - which gives you that extra level of confidence. One I'm thinking also gives you the physical path of the report - I know I've used it historically when needing to restore individual reports.

[howartp]

Isn't there a DBDiag script you can run that gives you the names? Its basically a SQL script, but Capita have signed it - which gives you that extra level of confidence. One I'm thinking also gives you the physical path of the report - I know I've used it historically when needing to restore individual reports.

 

There is, but a) the Edugeek app wouldn't let me reply the other day, and b) I wasn't sure whether it was publically available or whether you needed permission from Capita to get / use it so I wasn't sure whether to post or not.

[vikpaw]

If it was writing to the database then I completely agree and would not even contemplate advising, reading will do no harm (as long as you know what you're doing ).

 

Engage sounds interesting, one of the problems of SIMS is there is so much useful information in there and no GUI based way to access it, hence the threads like this one.

 

I just like to emphasise (and I'm sure @matt40k will chide me if I don't) that running any sort of direct code is not recommended. A read is safe, but what is to control where they next get a script from and it's not been given with a warning, what if it's a drop or update. I run updates on my system, but I have them verified by a developer first. I have permission to edit, others don't. In some setups i'm sure techs probably have very high level privileges due to the nature of their role, even if they don't understand how something works. So the server is set to let windows logins of the schooladmins domain group be admin / dbo. Dangerous path.

 

Apologies if it seemed I was correcting you, I just wanted to add the warning explicitly for posterity.

[matt40k]

@vikpaw Cheers, yer, often found the "only has read access" turns into db_owner which then turns into "just change this field... and..." which is followed by a Capita bill. In corporate land we say everything has to be developed in DEV and passed to UAT for peer review before running it in PROD, so on the bases 99.9% of schools don't have a DEV or UAT, I'd stick with the, has Capita written it \ peered reviewed it before running it. We actually get told off by the DBAs if we run even select statements on PROD environments as it can affect performance. "the indexes are designed and optimized for the application, not for our queries" is the common response (unlike the warehouse\data marts)

@howartp Can you post files on MyAccount like SupportNet?

[vikpaw]

@howartp Can you post files on MyAccount like SupportNet?

Yes, but it's embedded into a forum thread, so there isn't a general resource area, but you can attach as part of a conversation.

The old useful uploads have been made available via a document with links in it, and you can request that others are made available.

[howartp]

@howartpCan you post files on MyAccount like SupportNet?

I could, but the version I have is four years old.

 

I'd rather folk request it from Capita - the DBDiagnose script is called "reports_in_sims.net_new.sql"

 

( @vikpaw - I think you've got the wrong end of the stick; Matt is on about MyAccount which replaces Capita's SupportNet.)

 

Peter

[vikpaw]

( @vikpaw - I think you've got the wrong end of the stick; Matt is on about MyAccount which replaces Capita's SupportNet.)

That was exactly what I was thinking of. My understanding is that file sharing is gone, and you have to add files as attachments to a "MyAccount" Forum thread.