Share
Report
571Since 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 my 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 share my refex dashboard here and on GPUG.com. 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 sharing it with others?
What have I done to make it "as generic as possible"?
- I created each Data Connection this way:
- Data >> From Other Sources >> From Microsoft Query
- I selected a Data Source Name 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!)
- In the SQL Server Login dialog, I select "Trusted Connection"
- Close the "Add Tables" dialog
- Click the "SQL" button in the "Microsoft Query" dialog
- Paste the SQL code from the View; e.g.,
SELECT 'DYNAMICS..SY00800' AS TableName, *
FROM DYNAMICS..SY00800WHERE USERID NOT IN (SELECT USERID FROM DYNAMICS..ACTIVITY);
- Click OK at the "SQL Query can't be represented graphically. Continue anyway?" prompt
- Close the Microsoft Query window
- Choose a place for the "Import Data" dialog to plant the table on a tab of the refex and click OK
- I then modify the new Data Connection like this:
- Open the Data >> Connections dialog
- Select the new connection and click the "Properties" button
- Change the Connection name
- Select "Refresh every" and change 60 minutes to 2 minutes
- Select "Refresh data when opening the file" and "Remove data from the external data range before saving the workbook"
- Click on the "Definition" tab
- 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.
- The "Connection string", however, shows my DSN; like this:
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;
- I don't want all that stuff. To make it more "DSN-less", I replace all that with the following:
DRIVER=SQL Server Native Client 11.0;SERVER=MyServer;Trusted_Connection=Yes;DATABASE=WOW;
...and click OK
- 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:
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 here or at GPUG.com, I can give instructions describing how 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 and missing a much easier way to do this? Have any of you posted refexes? How did you overcome the server/database/DSN issues?
Regards,
*This post is locked for comments
All responses (
Answers (
