Open Forum

Expand all | Collapse all

Dashboard on a Dime file downloads ready!

  • 1.  Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 11, 2019 05:14 PM
    24 downloads already as of 4:10 CST on Friday, January 11th... and it was only posted 80 minutes ago!

    Here's the link to the webinar downloads.

    The list of documents (12 of them) might look a little odd: all the links to them have the same name: Dashboard on a Dime for GP Sys Admins! But if you hover over the links you'll see the actual file names. For example:


    There you see GPDashboard.xlsm. When you click on the "Download" button for each file, the downloaded file will have the proper file name.

    There are two TXT files that give instructions for enabling each of the two Excel components and running the SQL scripts:

    Read Me.txt
    GP Admin Dashboard Instructions.txt

    I expect to see discussions here about both of these refexes!

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------
    GPUG Summit - Post


  • 2.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 11, 2019 05:24 PM
    36 downloads as of 4:22 pm CST... about 90 minutes since the files were posted!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 3.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 11, 2019 05:26 PM
    Oh, wait! That's the number of files downloaded... not the entire group of files. 36/12 = 3. Oh, well! I guarantee that you'll like the downloads if you're a GP Admin!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 4.  RE: Dashboard on a Dime file downloads ready!

    GOLD CONTRIBUTOR
    Posted Jan 14, 2019 06:19 AM
      |   view attached
    Steve, thanks for the presentation - it was excellent, and one I will get much use out of!  One request (I already emailed Beat on this); the uploaded scripts were renamed and make a mess of your numbering convention.  Call it 'The case of the missing '2''  Could you ask GPUG to correct or send out a cheat sheet with the actual file names translated from the jumble that they became?
    Thanks!

    ------------------------------
    Charles Ray
    Senior Systems Specialist
    CAL Business Solutions, Inc.
    Harwinton CT
    ------------------------------

    GPUG Summit - Post


  • 5.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 14, 2019 08:44 AM
    @Charles Ray,

    Good grief! I see the 8 SQL scripts that Béat and I provided, and the actual names of the files are in there... but jeez!

    Here are the file names for the SQL scripts I provided:

    We were asked to rename the .SQL scripts with .TXT extensions because the GPUG content management system would reject .SQL files... so they have to be renamed after you download them.

    As I laid out in the webinar, the five SQL scripts should be executed in order 1-5 on your DYNAMICS database.

    I will pass your observations along to GPUG.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 6.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 14, 2019 10:26 AM
    Hi Charles,
    Yes, as Steve stated it, somehow the upload on the GPUG server scrambled the file names somehow.. We do not control this as we had to send the files in and also change extensions as .sql isn't allowed to upload.
    Below is the list of the scripts associated with my Excel file :



    There is also a Read Me file to detail how to use the scripts.
    Hope this helps.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    GPUG Summit - Post


  • 7.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 14, 2019 03:27 PM
    I hope this trickles down to everyone who saw the webinar!

    I've been told that all of the TXT downloadable files (ten of them) have been ZIPped into an archive. There are only three files to download now and the file names of the text files will remain intact after un-zipping.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 8.  RE: Dashboard on a Dime file downloads ready!

    SILVER CONTRIBUTOR
    Posted Jan 15, 2019 12:01 PM
    Thanks for the information and the work on the webinar and the dashboard. However, I'm testing this in our lab environment, and when I open the Excel document and follow all of the steps to configure it, I get errors as I refresh the data. The errors I'm getting are "A cursor with the name 'comp_cursor' does not exist" & "Invalid Column Name 'EnableGLReporting'." Am I missing something in the process to set this up? Any help is appreciated as always.

    ------------------------------
    Jon Godwin
    Database Administrator
    Boulder CO
    ------------------------------

    GPUG Summit - Post


  • 9.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 15, 2019 12:47 PM
    @Jon Goodwin,

    The comp_cursor​​ from the error message is created in a couple of the stored procedures: uspFillTempSY00500Table and uspLocksInUse. Those two stored procs step through all of the GP companies to find relevant information for the tabs in the GP Admin Dashboard.

    The EnableGLReporting field was added to the DYNAMICS..SY01500 table in GP version 2015. Do you have an older version than that? If so, I would comment out that field in those two stored procs like this:

    EXISTING CODE:

    WHERE a.CMPANYID > -1
        AND a.EnableGLReporting = 1;';


    MODIFIED CODE:

    WHERE a.CMPANYID > -1;';


    Actually, @Beat Bucher found that bug. That is, the stored procs only working as-is in GP 2015 or newer. Try that edit in the stored procs and report back. I'm sorry about the bug. I don't have older versions of GP to test this out.

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 10.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 15, 2019 01:30 PM
    Hi John,
    This is true and I actually discovered the bug a while back and told Steve, but the truth is that this condition should have been removed I think as it doesn't play any role within the entire Dashboard.
    The EnableGLReporting is a new field ​​that was added in 2015 for Management Reporter support and DataMart. This can be ignored for now.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    GPUG Summit - Post


  • 11.  RE: Dashboard on a Dime file downloads ready!

    SILVER CONTRIBUTOR
    Posted Jan 15, 2019 02:09 PM
    Thanks, Beat & Steve. I'm on 2013 so that would explain the issue with the new field.

    ------------------------------
    Jon Godwin
    Database Administrator
    Boulder CO
    ------------------------------

    GPUG Summit - Post


  • 12.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 15, 2019 02:17 PM
    @Jon Goodwin and @Beat Bucher, et al,

    Béat and I discussed a way to eliminate TEST databases from the list of companies that are polled for those two stored procedures. I remember that Béat thought that a new function could be added that would look for the term "<TEST>" in the name of the company. I thought that adding a little reference table with the IDs of the companies that the user wants polled would be a little easier than coming up with all the various ways that people designate TEST companies.

    Then I remembered upon seeing the EnableGLReporting field why I used it: for the very purpose of narrowing the list of companies that the Admin Dashboard polls.​​​ That field is found in the DYNAMICS..SY01500 table, and is filled in from the Company Setup Options window (Microsoft Dynamics GP >> Tools >> Setup >> Company >> Company >> Options).

    But since it didn't exist until version 2015, I agree with my co-presenter that it could safely be removed from the stored procedures.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 13.  RE: Dashboard on a Dime file downloads ready!

    SILVER CONTRIBUTOR
    Posted Jan 16, 2019 02:18 PM
    Just wanted to share that removing the line of code worked great for GP 2013 R2. The only downside is that TEST company also appears in the Dashboard but that's no big deal for my purposes. Thanks again for putting this together and helping me getting it to work in my system.

    ------------------------------
    Jon Godwin
    Database Administrator
    Boulder CO
    ------------------------------

    GPUG Summit - Post


  • 14.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 16, 2019 03:08 PM
    @Jon Goodwin,

    On behalf of my co-presenter, @Beat Bucher, and myself, we're glad to help.

    I don't suppose that you'd be willing to post a screen shot or two of your operational GP Admin Dashboard, would you?

    Sincerely,​​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 15.  RE: Dashboard on a Dime file downloads ready!

    SILVER CONTRIBUTOR
    Posted Jan 17, 2019 02:37 PM
    Sure thing. I've attached the screenshots here. Not sure if they'll be of much use because I had to scrub some data for security purposes. Thanks again for all the help.

    ------------------------------
    Jon Godwin
    Database Administrator
    Boulder CO
    ------------------------------

    GPUG Summit - Post


  • 16.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 17, 2019 03:52 PM
    @Jon Godwin,

    Cool, Jon! Thank you! It's a kick to see someone else getting use out of the Dashboard.

    In case I glossed over the issue a bit quickly during the webinar last week, when you see problems with Active Batches, there isn't anything you can do directly outside of GP to address them. Whenever I see error statuses on batches, I ​let the appropriate people know.

    I see that I actually neglected to have a column appear for the USERID. I added it to the uspFillTempSY00500Table stored proc; to the CREATE TABLE and the SET statement for the @sql variable (the INSERT INTO and the list of fields). Now the USERID appears at the far right on the "Active Batches" tab.

    Thank you for sending those images. It helped spur this line of thought. I'm going to gather all of the comments I've gotten on the Dashboard and write up a blog post... Real Soon Now, as the great Jerry Pournelle used to say!

    Sincerely,​​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 17.  RE: Dashboard on a Dime file downloads ready!

    TOP CONTRIBUTOR
    Posted Jan 17, 2019 05:11 PM
    I finally had a little time to try this out. And I was successful (mostly)! It was my first experience with a refex.

    Unfortunately, I am on SQL Server 2008. So I am getting errors on the FORMAT function. I modified the ones I found in the stored procedures but I am still getting errors.

    But I am still getting some data:
    Dashboard screen shot
    I even see some errors - these are test companies though:
    Active Batches screen shot
    Thanks for the webinar and making this available!

    ------------------------------
    Sandy Righi
    Senior Programmer Analyst
    Howard Hanna
    Mayfield Heights OH
    ------------------------------

    GPUG Summit - Post


  • 18.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 17, 2019 05:42 PM
    @Sandy Righi,

    Thank you so much for jumping in with your experiences!

    Let's see... first: the ​list of Active Batches showing errors (BCHSTTUS = 11) means that those batches will likely have to go through Batch Recovery. I've seen plenty of times where the user isn't even aware that a batch had problems. So everything's normal there.

    What version of GP are you running?

    During development of the GP Admin Dashboard, I only had access to SQL Server 2012, 2014, and 2016... couldn't test on older versions of SQL Server. Sorry about that! FORMAT has changed over the versions, for sure.

    >> but I am still getting errors <<

    Do tell! Don't keep us in suspense! I might be able to help.

    I see that GP is reporting 16 sessions, but you're not seeing any User Names. We've seen issues with the fact that I've only had GP 2016 to report on. I know that different versions have had changes to table structures... and you might be seeing such an issue with your User List and your Session list... but there's no way for me to test. Looks like you're a guinea pig!

    Send your error messages to my Gmail address (serbach) and I'll help you work things out.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 19.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 17, 2019 06:11 PM
    @Sandy Righi,

    It occurred to me that there might be an issue with the udfDatediffToWords scalar function. My cohort, @Beat Bucher, reported​ an issue with that function in some of our early testing. It's certainly a non-essential function. It's only purpose is to format the length of time someone's been logged in into days, hours, and minutes instead of just minutes.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 20.  RE: Dashboard on a Dime file downloads ready!

    TOP CONTRIBUTOR
    Posted Jan 18, 2019 11:35 AM
    Success! I have a working dashboard!

    After following Steve's suggestion to edit and test the SQL connections in the refex, I modified the code where needed to remove the "FORMAT" function and I have data.

    Thank you again to Steve and Beat for sharing!

    ------------------------------
    Sandy Righi
    Senior Programmer Analyst
    Howard Hanna
    Mayfield Heights OH
    ------------------------------

    GPUG Summit - Post


  • 21.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 18, 2019 11:47 AM
    @Sandy Righi,

    Outstanding! Thank you for posting a follow-up.

    The GP Admin Dashboard isn't rocket science, to use an over-worked phrase! It's simply a more approachable way to present information from queries put together by many people over the years.

    Sincerely,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------

    GPUG Summit - Post


  • 22.  RE: Dashboard on a Dime file downloads ready!

    SILVER CONTRIBUTOR
    Posted Jan 24, 2019 12:07 PM
    Steve, I just found the stored procedure uspFillTempSY00500Table in both my master and dynamics databases. TBH, I may have accidentally deployed it there but can't remember. Can you advise if it should be in master or dynamics? Thanks for the help, again. I know I've asked a ton of questions.

    ------------------------------
    Jon Godwin
    Database Administrator
    Boulder CO
    ------------------------------

    GPUG Summit - Post


  • 23.  RE: Dashboard on a Dime file downloads ready!

    GPUG ALL STAR
    Posted Jan 24, 2019 12:29 PM
    Hi Jon,
    you probably run the script against the master DB the first time..  This is personal preferences for many DBa to create non-specific app scripts or functions within a separate DB, in the case of the Dashboard it is only used by GP, so I'd just suggest to let it in the DYNAMICS DB.
    I've been creating many SP's, Views and the like over the years, and always put them in the DYNAMICS DB when they were not related to a specific GP company. Multi-company views are best off the DYNAMICS DB as you grant access only in one DB thru the view.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    GPUG Summit - Post


  • 24.  RE: Dashboard on a Dime file downloads ready!

    TOP CONTRIBUTOR
    Posted Jan 19, 2019 05:35 PM
    Thank you @Steve Erbach and @Beat Bucher for these!
    My IT team was stoked to see what they can do with this and already thinking of other things they could monitor on GP and our server!​​

    ------------------------------
    Aleiha Hanson
    Royal Basket Trucks, Inc - IT Manager
    GPUG Milwaukee WI Chapter Leader
    ahanson@royal-basket.com
    ------------------------------

    GPUG Summit - Post


  • 25.  RE: Dashboard on a Dime file downloads ready!

    Posted Jan 24, 2019 12:47 PM
    @Jon Godwin and @Steve Erbach
    I have the stored procedure uspFillTempSY00500Table in the  dynamics database. I do not have any in the Master DB. Hope that helps.

    ------------------------------
    Darin Vialpando
    IT Business Applications Manager
    Kroenke Sports & Entertainment
    Denver CO
    ------------------------------

    GPUG Summit - Post


  • 26.  RE: Dashboard on a Dime file downloads ready!

    SILVER CONTRIBUTOR
    Posted Jan 24, 2019 12:51 PM
    Thanks, @Beat Bucher & @Darin Vialpando. I was pretty sure I had accidentally installed it in Master as I tend to only keep DBA tools in Master like the Ola & Blitz Scripts. I appreciate the info before I dropped the sproc. ​​

    ------------------------------
    Jon Godwin
    Database Administrator
    Boulder CO
    ------------------------------

    GPUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role