[Steve Erbach]

Dear Admins,

Since the time I became the accidental GP Admin at WOW Logistics, I’ve glommed onto the SQL scripts that show what’s what in GP: users logged in, stuck batches, SQL sessions, etc.

But what the scripts lack is that immediacy of a dashboard. So I made one in Excel. All of the SQL views I’ve stolen — from people like Béat Bucher, David Musgrave, Victoria Yudin, and Leslie Vail — went into data connections in the refex (REFreshable EXcel workbook). There are 7 tabs with data from 10 views in Excel tables on those tabs.

What I’d like to do is to upload a copy of my refex dashboard to GPUG. My concern, of course, is that it’ll take some jiggering for others to get it to work right; i.e., having to make changes to all of the Data Connections to reset the SQL Server and Database parameters in the connection strings.

So, what I’m asking here is this: is the tactic I’ve employed to make the refex as generic as possible sufficient for the purpose of disseminating it via GPUG?

What have I done to make it “as generic as possible”?

1. I created each Data Connection this way:
   1. Data >> From Other Sources >> From Microsoft Query
   2. I selected a Data Source pointing to our GP server/database to begin with… there doesn’t seem to be a way to create a DSN-less connection directly (I welcome advice on this matter!)
   3. In the SQL Server Login dialog, I select “Trusted Connection”
   4. Close the “Add Tables” dialog
   5. Click the “SQL” button in the “Microsoft Query” dialog
   6. Paste the SQL code from the View; e.g.,
      1. SELECT 'DYNAMICS..SY00800' AS TableName, *
FROM DYNAMICS..SY00800
WHERE USERID NOT IN (SELECT USERID FROM DYNAMICS..ACTIVITY);
   7. Click OK at the “SQL Query can’t be represented graphically. Continue anyway?” prompt
   8. Close the Microsoft Query window
   9. Choose a place for the “Import Data” dialog to plant the table and click OK
2. I then modify the new Data Connection like this:
   1. Opened Data >> Connections dialog
   2. Selected the new connection and clicked “Properties” button
   3. Changed the Connection name
   4. Selected “Refresh every” and changed 60 minutes to 2 minutes
   5. Selected “Refresh data when opening the file” and “Remove data from the external data range before saving the workbook”
   6. Clicked on the “Definition” tab
   7. The “Connection Type” is already “Database Query”, which is what I want; the “Connection file” prompt is empty; the “Command Type” is “SQL”; and the “Command text” contains the query.
   8. The “Connection string”, however, shows my DSN; like this:
      1. DSN=DYNAMICS_GP2016;
Description=DYNAMICS_GP2016;
UID=sa;
Trusted_Connection=Yes;
APP=Microsoft Office 2013;
WSID=XXXXXX;
DATABASE=WOW;
AutoTranslate=No;
QuotedId=No;
AnsiNPW=No;
   9. I don’t want all that stuff. To make it more “DSN-less”, I paste in the following:
      1. DRIVER=SQL Server Native Client 11.0;
SERVER=MyServer;
Trusted_Connection=Yes;
DATABASE=WOW;

         …and click OK

   10. Of course, when I check up on the Connection Definition again by clicking Properties >> Definition in the “Workbook Connections” dialog, I see that Excel has done its usual meddling and inserted some extra info into the nice, clean Connection String I gave it:
       1. UID=steveer;
APP=Microsoft Office 2013;
WSID=XXXXXX;

          …have been inserted. I guess it’s a case of “no harm, no foul”… but it’s sort of maddening.

So now I’ve altered the Connection String to be more “DSN-less-like” for each of the Excel tables in my refex.

Is that enough? If I upload it to GPUG.com, I can give instructions saying to edit the Connection Strings for each of the data connections… and that should allow the refex to work in your enviornment, yes? Unless, of course, you’re not using version 11.0 of the SQL Server Native Client!

Am I spacing out about a much easier way to do this? Have any of you posted refexes? How did you overcome the server/database/DSN issues?

Regards,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
——————————
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————