3 Replies Latest reply: May 24, 2012 6:24 AM by RyanSe RSS

    Is there a "question message" object type?

    Caitlin Servilio

      I would like to run a report that can return all the users in the system who have participated in the process of solving a question.  I already know how to get all the names of users who originally asked ("create") a question.  I can also return anybody who has marked that question as "aided" or "resolved."  But I really want to also find out who responded to any questions, whether or not their responses were ultimately correct.

       

      Ordinarily I'd use the "discussion message" object type filter to return people who have responded to a discussion.  I just don't know how to limit these to questions.  Is there a "question message" object type that I'm not seeing?  And if not, does anybody have any suggestions as to how I could get at this information anyway?  Thanks in advance for your help!

        • Re: Is there a "question message" object type?
          RyanSe

          Hi Caitlin,

           

          A question is object type 27.

           

          Personally speaking, I use the data out of the application database to get data similar to what you are looking for. There are a couple tables to look at: jivequestion and jivemessage. You should be able to join them on threadid. Here is something I hacked together. This should tell us the threadid of the question a user replied to, their userid and the # of times they replied in that question. The WHERE constraint is set to "is not null" so that the root message (in other words, the post that started it) is ignored.

           

          select jivemessage.threadid,jivemessage.userid, count(messageid)

          from jivemessage

          inner join jivequestion on jivequestion.threadid = jivemessage.threadid

          where parentmessageid is not null

          group by jivemessage.threadid,jivemessage.userid

          order by count(messageid) desc

           

            • Re: Is there a "question message" object type?
              Caitlin Servilio

              Thanks for your response!!  This looks perfect for what I am trying to find. Unfortunately I don't have access to our application database, but maybe I can work with someone to get this information.  Do you generally find that you can get information from the database that you can't get through BI OD?

                • Re: Is there a "question message" object type?
                  RyanSe

                  I do.

                   

                  That said, I am a bit biased towards just working directly with database tables since the knowledge base I previously worked with had a very poor web reporting package so I was forced to always do my work directly through SQL queries on the backend.

                   

                  Most of the reporting/analysis I do these days is more like the question you asked vs. the typical "how many documents/discussions/views/etc did we have last month" type items that a lot of folks look for. For my audience, we need to do a lot of massaging of data based on our internal company hierarchy data which isn't anywhere in our Jive instances so mostly I just dump data via SQL queries then combine it with other data sources to get what we need.

                   

                  For me, one of the best ways I have learned to become effective with web-based reporting options is to have a solid foundational understanding of the raw data (the database tables) where the information is extracted from. My co-workers often say they can see the Windows "hour glass" after they ask me for data because I am visualizing the tables where the data would come from to see if it is even possible. From there it is fairly easy to just write a query or go to BI OD to pull the report together. More often than not, for us, the data is not information that is pushed to BI OD so I just work with the local copy of the database to pull the data.