EZ Enterprise Dashboard Graphs

Everyone loves dashboard graphs – especially managers. Imagine going to management Monday telling them you can provide them real time graphs -showing them the status of anything you are storing in SOLIDWORKS Enterprise PDM…all for free.

Yeah, I see a big raise in your future…maybe even a trip to the tropics as a bonus. Don’t forget the Engineering Data Specialist Man helped get you there!

/* WARNING: This blog contains SQL & VBA programming. Readership discretion is advised.  However, I promise there is not a lot…I’ll step you through. Once you see how easy and powerful these are you’ll know they are worth it. /*

In this example, let’s imagine you would like to be able to have your manager double click on a desktop icon and have a graph display showing all of the engineering change notices that have been created on a month by month basis.

First thing (and perhaps the most challenging) you’ll need to do is come up with a SQL statement to retrieve the information you’ll need:

SELECT COUNT(Documents.Filename) AS NumberofECNs,cast(month(VariableValue.ValueDate) as varchar(2)) + ‘/’ + cast(year(VariableValue.ValueDate) as varchar(4)) as ‘Month’
FROM Projects INNER JOIN
DocumentsInProjects ON Projects.ProjectID = DocumentsInProjects.ProjectID INNER JOIN
Documents ON DocumentsInProjects.DocumentID = Documents.DocumentID INNER JOIN
VariableValue ON Documents.DocumentID = VariableValue.DocumentID INNER JOIN
Variable ON VariableValue.VariableID = Variable.VariableID
WHERE     (Projects.Path = ‘ECNs’) AND (DocumentsInProjects.Deleted = 0) AND (Variable.VariableName = N’Date’)
GROUP BY month(VariableValue.ValueDate),year(VariableValue.ValueDate)

Teaching you SQL is quite a bit beyond the scope of this blog entry. If you have a SQL statement in mind that you need help with, there are tons of people who can help you in the SOLIDWORKS forums or certainly your VAR can help. Besides this isn’t the cool part yet.

In short, the above SQL statement counts all files in the ECNs directory and groups them by month as defined in the “Date” variable. Thus a typical output may look like this:

Certainly we could take this output, paste it into an Excel sheet with a pre-made graph…but can you imagine someone in management being able to do all of these steps? Instead, make a spreadsheet that will automatically update itself every time it is opened. This way you can be assured you are looking at data as fresh as the moment the file was opened.

This Excel file does exactly this. As soon as your user opens the file [via Sub Workbook_Open], it connects to your Enterprise database, counts all of the ECN files via a SQL query, and puts this information into the spreadsheet. The graph is set to automatically scale, so there is nothing left to do except sip on the margaritas.

I’ve added tons of comments in the Excel VBA code. Look in the “This Workbook” module to find it. [The first time you open the file, you will get a VBA crash message, because the routine is trying to connect to my vault, you’ll need to modify the code’s connection string to connect to your vault for it to work properly.]

This was an easy, free solution. Because all of your data is stored in SQL, the sky is the limit for the amount of reporting you can do. I’ve seen pie charts showing how many ECNs were created by each user, then if you pick on a pie piece all of the ECNs created by that user appears in a chart -with hyperlinks to the actual ECN – using Internet Explorer as the interface. Cool, cool, cool.

If you want to get into some of these fancy charts,  read up on “Business Intelligence” built into SQL Server….or stay tuned here, I’ll probably blog about it someday.