We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

How can I create a report with details on sites and users

kristiakkristiak Posts: 1,338 ✭✭✭
The standard reports that you can create within OpenClinica only lets you create at the subject level. How do I create reports at the site or user levels?
Tagged:
«1

Comments

  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    The standard exports are by subject, but can can be aggregated by site in
    Excel, Access, SAS, R, etc.

    To aggregate by user is more difficult as a user "owns" an item or CRF or
    Event or Subject and may be different at each layer, and there is also a
    "last updated" user for each. So at worst there might be 8 users associated
    with each subject data point, and you must decide who wins.

    Just a listing of users by site is straightforward though.

    What kind of information do you need?
  • kristiakkristiak Posts: 1,338 ✭✭✭
    ---and also list of users and sites including selected details!
  • kristiakkristiak Posts: 1,338 ✭✭✭
    via Email
    Hi Lindsay,

    I need to create list of users with user ID's per site. Also list of sites with the users. I need to print them and possibly export them to MS ACESS

    Best regards

    Krister
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Fortunately, Gerben's Access Data-listing tool can help you do that.

    Unfortunately it requires a minor edit to the code.

    Fortunately I have written it for you + instructions here:

    https://gist.github.com/lindsay-stevens-kirby/00711b0ae46963cf0f76
  • kristiakkristiak Posts: 1,338 ✭✭✭
    via Email
    Terrific Lindsay, thanks a lot,

    Best regards
    Krister
  • kristiakkristiak Posts: 1,338 ✭✭✭
    via Email
    Hi Lindsay,

    Can I also list the sites ordered by start date (site start date) with the number of patients recruited by site up till now?
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Yeah kind of. You'll need some clinicaldata to get subject information, so
    you have to create an XML export and choose at least one item where all
    subjects have a value. There's a similar kind of code edit required as
    before, to get the site oid in the table "tblStudySubject".

    The site start date isn't included in the metadata though, so they can't be
    sorted on this.

    If you have other similar or more complex reports to write, maybe have a
    look at setting up datamart. For basic setup all you need is a postgres
    9.3+ instance. This will let you access everything OpenClinica has stored.
    And you can still use Access to write your reports, by connecting to
    postgres. There are instructions for that also.
  • kristiakkristiak Posts: 1,338 ✭✭✭
    via Email
    That's great Lindsay. During my years at Merck & Co Inc I used MS Access connected to ORACLE via an ODBC link. I assume this can also be set up to the Postgre database.., ,,
    Thanks for your help and tips!

    Regards
    Krister
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Yep it works on ODBC - anything that can connect to postgres, really. Just
    need the ODBC driver for postgres. It's covered in the "clients" part of
    the docs.
  • GerbenRienkGerbenRienk Posts: 827 ✭✭✭
    Thank you for the code, Lindsay! I added it to the version you can download from http://www.trialdatasolutions.com/tds/howto/datalisting.jsp
    And Krister: I definitely recommend having a look at the datamart. It takes some time setting up, but from there it will save you heaps of time.
This discussion has been closed.