Hi. For reportingobjectgroup, say you write a query that pulls up a group of encounters. If you intersect this with the Cohort 'all patients in a particular program', it will filter out the encounters owned by patients not in the program. Meaning that for any given patient in the program, there can still be N encounters in the result. This, i think, avoids your problem of having duplicate patients in your Cohort query condensing to 1. I originally got hung up on the ability to intersect with a Cohort based on the default reporting framework UI, which optionally allows you to select a base Cohort, but in thinking about it, it allows us to ask interesting questions pretty easily. Things like 'to what degree do patients in the HIV program also seek out primary care services outside of their monthly HIV visit' -- this is extremely straightforward using this intersection.
Its funny that the perception is that the reporting framework is inflexible, because in truth I've found it to be the opposite. I wrote the reportingobjectgroup module over a week, because i was facing the simple problem of counting primary care encounters at a health center over an arbitrary time period (usually a month). So my thought was, 'what if I replicate SqlCohortDefinition functionality, almost exactly, but allow the IDs to correspond to the ID of any OpenmrsObject'. This is the basic definition of an ObjectGroup. I took this approach having cracked open the code of the reporting framework, found CohortSQLDefinition, and then decided to copy it, figuring that it wouldn't be very hard... It of course turned into a bit more of a project than i had expected, but we're using the results in production now, and we've been able to build some nice stuff on it. That being said, I don't really want to maintain the module forever, and Mike has pointed out to me that there were other approaches -- one was building a SQLDataSetDefinition with a row-per-encounter structure and then building indicators off of this (not sure how much coding this would take?). Another was writing a LogicDataSource and creating a Rule for 'given a patient, how many times did they come into the health center for a primary care visit over a give timeperiod?' (is there a problem passing Reporting run-time parameters to logic Rules?). I don't have the bandwidth to try all three approaches, unfortunately, and I sort of wish that i had tried the SQLDataSetDefinition first (i'm not sure if it was already implemented at the time?). Ultimately, the goal should be to be able to write 5 lines of code defining the actual SQL you want, for any Object, and then define any number of indicators. I'm not sure which approach is going to ultimately win, but i'm glad we're having this conversation, and i am willing to put in some programming time once there are firm specifications for how exactly a SQLIndicator works. Transferring the objectgroup indicators we have in production to a more core-supported strategy would make me feel like we're on slightly firmer ground. d On Sat, Aug 20, 2011 at 4:04 AM, Michael Seaton <[email protected]> wrote: > I see no problem at all with a SqlIndicator, and I doubt that there is any > reason why this wouldn't "just work" with the SDMX-HD module. We always > envisioned there would be all sorts of Indicator implementations (or > Aggregated Data Element implementations for you Bob) - the CohortIndicator > was just meant to be the beginning. > > As an alternative (or in addition), we could consider implementing something > like a AggregatedDataSetColumnIndicator, which takes in: > > 1. A Mapped<DataSetDefinition> > 2. A column name > 3. An Aggregation > > Since we already have SqlDataSetDefinitions implemented, which simply return > a table of Data that can contain absolutely anything, this indicator would > wrap on of these, and then perform a particular aggregation on one of the > columns in order to produce an Indicator value. This would be pretty easy > to implement... > > Bob - are either of these something you would be interested in taking on > development-wise, or would you need one of us to take this on soon? > > Mike > > > > On 08/19/2011 05:08 PM, Bob Jolliffe wrote: >> >> On 19 August 2011 19:18, Dave Thomas<[email protected]> wrote: >>> >>> This is exactly what the reportingobjectgroup module that i wrote was >>> for -- the idea that you might want to use SQL to select groups of any >>> OpenmrsObject, and still be able to intersect it with a base cohort. >> >> Hi Dave. >> >> I haven't looked at the reportingobjectgroup module yet. That was >> going to be step two after we figured out the "easy" reported >> dataelements which involved counting heads rather than counting other >> openmrs objects. And I'm not sure that I understand fully the nuances >> of what you say above but I am worried that you still want to >> intersect with a base cohort ... as long as we are talking of a cohort >> then I'm guessing we don't count the same person twice. So if I want >> to know how many opd encounters there were last month, "intersecting" >> this with a base cohort sounds like it will filter out the duplicates >> which will again produce an incorrect result. Or maybe I am wrong - >> sorry to be speaking from ignorance having not yet looked at the >> module. >> >>> I'd really like to talk strategy about how to roll this module into >>> reporting core (or substitute a core solution for the things we >>> already have built on it). >> >> From our perspective (at least me and Viet :-) ) we are looking for a >> sweet spot. The implementors of the highly customized version of >> openmrs running in Shimla, India, have already to a large extent >> "solved" their reporting problems by creating Birt reports containing >> the various odds and sods of aggregate dataelements they need to >> produce. The flexibility of using birt meant they could execute >> whatever queries they want to populate the various reports. >> >> The downside being that the query, the data and the presentation all >> become hopelessly entangled in the birt report. And this doesn't >> really help when you want to produce data to be consumed by another >> system (eg dhis). Its possible of course to extract the data from the >> birt report but that is a bit of a hack, particularly when you need to >> map the anonymous birt dataelements. Having aggregated dataelement >> (or indicator) objects defined within the system makes much more >> sense. >> >> The strength of the reporting module, and why I have been its loudest >> advocate, is that it separates the notion of reported dataelements (or >> Indicators as they are known as in this context) from the rendering or >> presentation of reports. I am going to continue to use the term >> aggregate dataelement rather than indicator, but otherwise the >> semantics are not that important for the current discussion. The >> ability to define aggregate dataelements, datasets and composite >> reports independently of how they are rendered is really a powerful >> and even essential notion if we are to have a reporting capability >> which meets a wide variety of use cases. So the reporting module >> really does move in the right direction ... >> >> But at the highest level of abstraction, an aggregate dataelement >> object need only have a name, a description, and a mechanism (query) >> for deriving a value. It should not be a cast iron requirement that >> there is an underlying cohort derived directly, or via an >> intersection. I can see for many cases this is very useful ... ie to >> have an underlying cohort to drill down into. But equally often it is >> not and all you want is a count or some other aggregation. So what we >> find currently is that people argue that using the reporting module is >> too inflexible and don't understand why we can produce certain reports >> relatively simply in birt, but not in the reporting module. And >> naturally conclude that we should stick with Birt. >> >> In order to find the sweet spot of retaining the flexibility of birt >> together with the organising structure of the reporting module, it >> seems we need to have a class of aggregate dataelement whose only >> constraint is that it must result in a number, but which can be >> derived from any sql query. I think this is what Darius is also >> foreseeing in his SqlIndicator. If we had such SqlIndicators, we >> could (i) reuse the queries which have already been developed for birt >> and (ii) render the resulting reports in various ways. I think I even >> suggested in a previous mail that an ideal outcome of this might be >> that one of the possible renderings could in fact be a Birt report, in >> which case we will have closed the circle and have available all the >> presentation capabilities of Birt, but separated the definition of >> aggregated dataelements from the presentation of them. >> >> Apologies if I have misinterpreted many things re cohorts, >> intersections etc. And if the reportingobject module meets the above >> requirement then I am already delighted. I'm going to look at it >> tonight ... >> >> Regards >> Bob >> >> >>> d >>> >>> On Fri, Aug 19, 2011 at 5:30 PM, Darius Jazayeri >>> <[email protected]> wrote: >>>> >>>> We talked off-list, and it turns out that: >>>> >>>> Many/most of the indicators Bob wants to build are not really cohort >>>> indicators, but rather counts of encounters, obs, log entries, etc. >>>> They'd mostly be calculated via SQL. >>>> They need to be able to export these via the sdmx-hd module, into DHIS. >>>> >>>> @Mike, @Ryan, >>>> If we were to do a SqlIndicator implementation (which wouldn't be too >>>> much >>>> work), would that easily fit into the current SDMX-HD export module? Or >>>> is >>>> that hardcoded to cohort indicators? And how much work would it be to >>>> change >>>> that? >>>> -Darius >>>> >>>> On Fri, Aug 19, 2011 at 7:33 AM, Bob Jolliffe<[email protected]> >>>> wrote: >>>>> >>>>> On 19 August 2011 15:07, Darius Jazayeri<[email protected]> >>>>> wrote: >>>>>> >>>>>> You're not doing a count distinct, so if your opd_patient_queue_log >>>>>> can >>>>>> have >>>>>> the same patient_id more than once, that'd be why you get a >>>>>> difference. >>>>>> -Darius >>>>> >>>>> Thanks Darius. You are absolutely right. I also just figured that >>>>> out a few minutes ago. >>>>> >>>>> Though it has left me with a sinking feeling about how to use the >>>>> reporting module. It makes sense now that the penny has slowly >>>>> dropped, that a cohort query is in fact a query to select a distinct >>>>> group, or cohort, of patients. Which you could then drill down into >>>>> etc. >>>>> >>>>> But at the level of a typical service indicator, I am really not >>>>> interested in who the individual patients are. I need to know how >>>>> many patients had OPD encounters this month, for example. Using a >>>>> cohort query for this seemed to make sense, but of course it doesn't >>>>> as it filters the duplicate patients. So I should in fact be counting >>>>> the encounters rather than the patients, but then its not a cohort >>>>> query :-( >>>>> >>>>>> On Fri, Aug 19, 2011 at 5:37 AM, Bob Jolliffe<[email protected]> >>>>>> wrote: >>>>>>> >>>>>>> I am trying to compose an indicator which makes use of a join with a >>>>>>> custom table. >>>>>>> >>>>>>> Does anyone have an idea why executing the query directly as: >>>>>>> mysql -u ... -e 'Select count(patient.patient_id) from patient inner >>>>>>> join opd_patient_queue_log on >>>>>>> patient.patient_id=opd_patient_queue_log.patient_id' >>>>>>> >>>>>>> results in 16593, >>>>>>> >>>>>>> but when I create a sql cohort query as above (without the count), I >>>>>>> get a result of 13592. >>>>>>> >>>>>>> How does openmrs count the size of the resultset? It seems its not a >>>>>>> simple count ... >>>>>>> >>>>>>> Regards >>>>>>> Bob >>>>>>> >>>>>>> _________________________________________ >>>>>>> >>>>>>> To unsubscribe from OpenMRS Developers' mailing list, send an e-mail >>>>>>> to >>>>>>> [email protected] with "SIGNOFF openmrs-devel-l" in the >>>>>>> body >>>>>>> (not >>>>>>> the subject) of your e-mail. >>>>>>> >>>>>>> [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l] >>>>>> >>>>>> ________________________________ >>>>>> Click here to unsubscribe from OpenMRS Developers' mailing list >>>>> >>>>> _________________________________________ >>>>> >>>>> To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to >>>>> [email protected] with "SIGNOFF openmrs-devel-l" in the body >>>>> (not >>>>> the subject) of your e-mail. >>>>> >>>>> [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l] >>>> >>>> ________________________________ >>>> Click here to unsubscribe from OpenMRS Developers' mailing list >>> >>> _________________________________________ >>> >>> To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to >>> [email protected] with "SIGNOFF openmrs-devel-l" in the body (not >>> the subject) of your e-mail. >>> >>> [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l] >>> >> _________________________________________ >> >> To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to >> [email protected] with "SIGNOFF openmrs-devel-l" in the body (not >> the subject) of your e-mail. >> >> [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l] > > _________________________________________ > > To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to > [email protected] with "SIGNOFF openmrs-devel-l" in the body (not > the subject) of your e-mail. > > [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l] > _________________________________________ To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to [email protected] with "SIGNOFF openmrs-devel-l" in the body (not the subject) of your e-mail. [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l]
