10 Replies Latest reply on Aug 10, 2017 4:45 PM by FOzali

    How can make a report from History of Activity with a filter?

    FOzali Rookie

      Hello everyone,

       

      Situation:

      Each contact (client) will have appointment that get assign to one of the user (staff).

      Every time the appointment finish, we put the "Result" to records whats the result from the appointment.

       

      Goal:

      Able to have a report for how many appointment that particular user seen with the result

       

      For example:

       

      Weekly Report

      AGENT NAME                                       ISSUED                                   SEEN                                DNQ                            COMMENT

      Joe                                                                10                                             9                                      3                                  asdfb

       

       

      Note:

       

      ISSUED -> Number of appointment that assign to him

      SEEN -> Number of appointment that he actually see, because sometimes client cancel last minute

      DNQ - > Number of appointment's result that client that is not qualified

       

      First step that I am stuck with is that pull up a query or filter for Activities that has been completed.

      I cannot figure it out what table I should call.

      Once I know what table that saved all the Activities List that has been completed, I can make a "Count to produce the number of the "Issued" or "Seen"

       

      If anyone know how can I get the list of activities that has been completed for all contact that would be great!Thank you!!

        • 1. Re: How can make a report from History of Activity with a filter?
          Doug Castell Expert

          CONTHIST is the table you want. 

           

          Where are you building this report/query/?  There's an excellent DASHBOARD tool built into GoldMine that this might be a perfect job for.

          1 of 1 people found this helpful
          • 2. Re: How can make a report from History of Activity with a filter?
            FOzali Rookie

            Hi Doug,

             

            Thank you very much for your answer, I appreciate that!

             

            Anyway, at the moment I manage create a matrix table like I want from DASHBOARD.

             

            I just have a one problem with that, how can I sort that list weekly?

             

            I know it will be in parameter when we set up "Data Source", but I am having difficulty to create set up.

             

            My goal is to have the report like my previous post and have drop down button to select which week that I want to select.

             

            Thank you

            • 3. Re: How can make a report from History of Activity with a filter?
              Shaul.Bel Apprentice

              If you have already built the query that pull the needed data from GM database you can use power bi desktop to create your dynamic report the only con with it is that you can't go from it to the specific contact record in GM which you can do with GM dashboard.

              Power Bi Desktop can be downloaded fro Microsoift. It's free and very power full with lots of filtering tools and drill down options.

              • 4. Re: How can make a report from History of Activity with a filter?
                Doug Castell Expert

                You'd probably want to make a dropdown with the week numbers and then add a runtime parameter to your data source that incorporates the week number into a comparison to ONDATE.

                 

                Of course, now we're getting into minutiae of dashboard design and plenty of assumptions must be made about what you've already built in there, so it's tough to offer much further guidance here on a forum.  If you'd like to contact me directly, I can possibly further assist with (or simply DO) your dashboard work.

                • 5. Re: How can make a report from History of Activity with a filter?
                  FOzali Rookie

                  Hi Doug,

                   

                  I am having difficulty to set up "runtime parameter".

                  I still do not understand how can connect the dropdown with weeks with the data souce, like the one in "Activity Dashboard" has
                  "quarter" or "yearly".
                  Could you please explain about this?

                   

                  ps. do you want me to create a new thread for this question?

                   

                  Thanks heaps!

                  • 6. Re: How can make a report from History of Activity with a filter?
                    Doug Castell Expert

                    It is somewhat complicated and may be a bit beyond this forum format to fully discuss.
                    In short, you are basically making a variable that represents a piece of the where clause. 

                     

                    That variable clause then gets values dropped into it via events happening on the drop-down box (such as the user changing the selected value.)

                     

                    You might want to clone an existing dashboard and then take a look at the guts to get a feel for the mechanics of it all. 

                     

                    The most annoying problem you'll probably face is producing a drop-down with the numbers 1 - 52 in it... ;)

                    • 7. Re: How can make a report from History of Activity with a filter?
                      FOzali Rookie

                      I already copy paste the "Activity Summary Dashboard" and play with the runtime parameter.

                      However, I still cannot figure it out how table can connect with drop down list button.

                       

                      What I did was I delete the drop down list (year) and create exact new one (same detail in properties, data binding and source) but it still does not work.

                      Is there any extra step that I have to do to set it up?

                       

                      Thank you

                      • 8. Re: How can make a report from History of Activity with a filter?
                        Shaul.Bel Apprentice

                        For creating a drop down list of the week in the year You should create a new manually data source that will give you the numbers from 1 to 53.

                        The query would be something like this

                         

                         

                        select 1 as text,1 as value UNION ALL

                        select 2,2 UNION ALL

                        select 3,3 UNION ALL

                        select 4,4 UNION ALL

                        select 5,5 UNION ALL

                        select 6,6 UNION ALL

                        select 7,7 UNION ALL

                        select 8,8 UNION ALL

                        select 9,9 UNION ALL

                        select 10,10 UNION ALL

                        select 11,11 UNION ALL

                        select 12,12 UNION ALL

                        select 13,13 UNION ALL

                        select 14,14 UNION ALL

                        select 15,15 UNION ALL

                        select 16,16 UNION ALL

                        select 17,17 UNION ALL

                        select 18,18 UNION ALL

                        select 19,19 UNION ALL

                        select 20,20 UNION ALL

                        select 21,21 UNION ALL

                        select 22,22 UNION ALL

                        select 23,23 UNION ALL

                        select 24,24 UNION ALL

                        select 25,25 UNION ALL

                        select 26,26 UNION ALL

                        select 27,27 UNION ALL

                        select 28,28 UNION ALL

                        select 29,29 UNION ALL

                        select 30,30 UNION ALL

                        select 31,31 UNION ALL

                        select 32,32 UNION ALL

                        select 33,33 UNION ALL

                        select 34,34 UNION ALL

                        select 35,35 UNION ALL

                        select 36,36 UNION ALL

                        select 37,37 UNION ALL

                        select 38,38 UNION ALL

                        select 39,39 UNION ALL

                        select 40,40 UNION ALL

                        select 41,41 UNION ALL

                        select 42,42 UNION ALL

                        select 43,43 UNION ALL

                        select 44,44 UNION ALL

                        select 45,45 UNION ALL

                        select 46,46 UNION ALL

                        select 47,47 UNION ALL

                        select 48,48 UNION ALL

                        select 49,49 UNION ALL

                        select 50,50 UNION ALL

                        select 51,51 UNION ALL

                        select 52,52 UNION ALL

                        select 53,53

                         

                        I would add to it this week and last week

                         

                        Insert into your dashboard a drop-down list which is data source should be the data source created by the above query.

                         

                        You will have to add to the drop-down list an event that will update the chart or table view when you change the week in the drop-list.

                        This event should trigger runtime parameter in the query behind the table view or chart.

                         

                        As I have wrote above you will be able to do it easier with power bi desktop.

                         

                        I gave you here only the basic of what you should do.

                        1 of 1 people found this helpful
                        • 9. Re: How can make a report from History of Activity with a filter?
                          Shaul.Bel Apprentice

                          Send me your main query and I will try to help you.

                           

                          Anyway the runtime parameter should be something like

                          DATEPART(ww, CONTHIST(h).ONDATE)=<<Value>>

                           

                          The question is do you have it to start it with AND

                           

                          From my experience the best way to know what query is running is to run profiler and see what query is running and change things so GM will set the query in the right way.

                          Otherwise you are like a blind man.

                          • 10. Re: How can make a report from History of Activity with a filter?
                            FOzali Rookie

                            Hi Shaul!

                             

                            Thank you for your reply!

                             

                            Good news! I managed to fix my query and create an event to connect the table with the drop down list.

                             

                            However, when I try to do exact the same thing with the chart, it does not work.

                             

                            My query for the chart is:

                             

                            SELECT userid, count(*) as issued from conthist

                             

                            WHERE srectype = 'A'

                            AND datepart(ww, ondate) = datepart(ww, getdate())

                             

                            GROUP by userid

                            ORDER by issued desc

                             

                            With RUNTIME parameter:

                             

                            datepart(ww,ondate) = <<VALUE>>