EZ Enterprise Dashboard Graphs

April 23rd, 2010


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:

NumberofECNs Month
500 12/2009
250 1/2010
375 2/2010
507 3/2010
423 4/2010

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.

ECNs

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.

Jeff Sweeney

CSWE Engineering Data Specialist 3DVision Technologies

Tags: , , ,

Loading Facebook Comments ...

5 Responses to “EZ Enterprise Dashboard Graphs”

  1. Lou Gallo says:

    Nice post Jeff. I do a very similar thing using SQL’s Reporting Services. It is a great way to extend this report-type data in a visual envelope and allow it to be accessed via the company’s intranet in a browser. Keep-um coming man! Good stuff! ~Lou

  2. Jeff Sweeney says:

    Thanks!

    No question SQL’s reporting services/business intelligence are far superior to Excel’s capabilities. I love the fact that they are accessed through the browser. Someday when I get time, I think I’ll cover that topic…it’s always “someday”.

    Can’t beat Excel if you are looking for something quick, dirty and easy though. :)

  3. Oleg says:

    Jeff, Good point. People love dashboards. Could you imagine them on iPad? Does it make sense? — http://plmtwine.com/2010/04/29/plm-excels-and-the-ugly-truth-of-ipad/. Thanks, Oleg

  4. Joy Garon says:

    Jeff –

    Engineers still love Excel – take a look at the free add-in called SQL Drill. This will allow you to easily embed SQL Queries within Excel without programming.

    Joy

  5. Jeff Sweeney says:

    Great catch Joy! Even easier dashboard graphs!

Leave a Reply

Follow
Get every new post delivered to your inbox
Join other followers
Powered By WPFruits.com
Bear