6 Replies Latest reply on May 1, 2018 3:32 PM by MarkDurrant

    Is it possible to add a date range filter in a GoldMine Dashboard?

    SusanSiegler Apprentice

      I have created several dashboards for tracking/reporting purposes.  I need to be able to pull some weekly activity numbers and last week was one of those weeks that run over two months, so I had to drill down to August to get the results from Mon-Wed and then repeat by drilling down to September for Thurs-Fri.  Is there any way to add a date range search to my Dashboards?

        • 1. Re: Is it possible to add a date range filter in a GoldMine Dashboard?
          Doug Castell Expert

          You could accomplish this in a number of different ways -- all of them would involve smart design of your dashboard(s). 

           

          You could add an expression to the select list in your data source to show the week number and sort/filter the columns by those values.


          Here's an example of usage:

           

           

          select userid, ondate, datepart(wk,ondate) as weeknumber, ref from conthist where ondate>getdate()-90

           

          You could populate a dropdown with all the distinct week numbers and then use that as a runtime parameter you pass to the data source of your table view... 

           

          Lots of options, but it all starts with using the week number in the where expression of your data source, somehow.

          1 of 1 people found this helpful
          • 2. Re: Is it possible to add a date range filter in a GoldMine Dashboard?
            Rookie

            To add a Start Date and an End Date, you'd have to follow these general steps:

             

            1. Add two Date Picker fields to your dashboard.  These will be your Start Date and End Date.
            2. Add two Runtime Parameters to your data source for Start Date and End Date.  Note: Your data source has to have a specific date field for this to work.  The Start Date would set the date field to greater than or equal.  The End Date would set the date field to less than or equal.
            3. Add two Events to your dashboard that update the data based on the runtime parameters that match the Start Date value in the date picker with the Start Date runtime parameter and the End Date value with the End Date runtime parameter.
            2 of 2 people found this helpful
            • 3. Re: Is it possible to add a date range filter in a GoldMine Dashboard?
              jst.jdennis@gmail.com Rookie

              Both answers are good options.  I would lean towards adding the date pickers, though.  I do custom dashboard development.  Contact me if you need some help.  I do a free initial consultation.

               

              Kind regards,

               

              -Jeremy

              • 4. Re: Is it possible to add a date range filter in a GoldMine Dashboard?
                MarkDurrant Rookie

                I'm trying to figure out someting related -- how to use the datepicker.

                 

                It's not clear to me how to get the value selected with the datepicker into the query used to pull the dashboard data. I think the part where I am confused is what the Data Binding and Data Source should look like for the Date Picker.

                 

                Does anyone have any insight, screenshot or sample to share?

                 

                Thanks much!

                Mark

                • 5. Re: Is it possible to add a date range filter in a GoldMine Dashboard?
                  John Neighbors SSMMVPGroup

                  It's been a number of years since I used a datepicker in a dashboard for a client (for whatever reasons), so I'm a bit rusty, but, I just got onto a client system and pulled this out, that may help.

                   

                  NOTE:  I don't think Data Binding will come into play here, but rather, most likely only the Data Source.

                   

                  We often write custom SQL functions, that take input and return desired results in a table format, which we THEN use within GoldMine Dashboards.  That is the case here.

                   

                  In this particular dashboard, we have a datepicker object named Latecomer.

                   

                  The SQL query of the applicable Data Source is:

                   

                  SELECT * FROM {{contact_db}}fn_Invoice_Lines(<<Market>>+'',<<Edition>>+'',<<Interval>>+'',<<Contract>>+'', convert(varchar,cast('<<Latecomer>>' as date),112), <<Override>>+'') order by TranNo, SeqNo

                   

                  The SQL function is named fn_Invoice_Lines and takes 6 inputs.  For the first through fourth and sixth inputs, we have to add +'' to the end of each parameter (long story, only learned this through trial and error).

                   

                  The fifth input is the one that uses the datepicker.  As you can see, we simply reference it as <<Latecomer>>. However, because our SQL function actually expects a character string in the format yyyymmdd, we first cast the <<Latecomer>> value (with single quotes around it) as a date and THEN convert that to varchar with a format of 112.

                   

                  Again, it's been several years since I set this particular dashboard up, I but I recollect really having to work with it before I realized the format being passed by <<Latecomer>>, such that I could then cast and convert it as needed.

                   

                  Hope this feedback helps.

                  2 of 2 people found this helpful
                  • 6. Re: Is it possible to add a date range filter in a GoldMine Dashboard?
                    MarkDurrant Rookie

                    Hi John,

                     

                    Much thanks for your detailed response. I really appreciate it.

                     

                    I did make progress with your info and got much closer. I'm still not there but will play a little more.

                     

                    Thanks again!

                    Mark