Welcome to Telelogic Product Support
  Home Downloads Knowledgebase Case Tracking Licensing Help Telelogic Passport
Telelogic SYNERGY (steve huntington)
Decrease font size
Increase font size
Topic Title: Query in excel format
Topic Summary: automativally import data from Synergy Change in an existing excel file
Created On: 19-Jul-2006 14:17
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
Subscribe to this topic Subscribe to this topic
E-mail this topic to someone. E-mail this topic
Bookmark this topic Bookmark this topic
View similar topics View similar topics
View topic in raw text format. Print this topic.
 19-Jul-2006 14:17
User is offline View Users Profile Print this message


Stéphane Thomas

Posts: 8
Joined: 20-Dec-2005

Hi,

I would like to include in our project dashboard (an excel file) some metrics resulting from Synergy Change queries.
For example: the number of PRs found in each lifecycle phase for a specific project.

At this time, project managers have first to execute a report in Synergy Change and then write them in their dashboard.

How can I automatized this work in our dashboard?

Stéphane.
Report this to a Moderator Report this to a Moderator
 20-Jul-2006 21:59
User is offline View Users Profile Print this message


Brian Barde

Posts: 39
Joined: 24-Sep-2004

You could write some VBA in Excel to automatically generate the reports.

Not really sure what your dashboard is (How does the information get put into the dashboard, manually, is it a database)

-------------------------
Thanks,
Brian
Report this to a Moderator Report this to a Moderator
 20-Jul-2006 23:15
User is offline View Users Profile Print this message


Robert Poldervaart

Posts: 5
Joined: 7-Jun-2006

Brian, How will excel get to the database?  I have been working on this exact type of functionality.  Is this done through an ODBC connection?  I just installed an informix ODBC driver this morning, but don't have all the parameters to configure it yet.

Or, are you suggesting that the reports are run from ccm, and the VBA extracts the data from the reports and puts it into the dashboard.

I would like to be able to run some VBA script in excel to get the data out of Synergy and do my operations to build a chart.  Eventually I'd like to do this in .NET for a browser presentation instead of Excel.  But I think my question is still the same as Stephane's.  How can you connect excel to Synergy?

Thanks,
Robert
Report this to a Moderator Report this to a Moderator
 21-Jul-2006 03:52
User is offline View Users Profile Print this message


Brian Barde

Posts: 39
Joined: 24-Sep-2004

Well for Synergy Change I would not have Excel get to the database but rather have a simple query report that is output as an Excel report. Then have the charts automated in Excel.

So it would be a two button automation. We have this now. We have one button in Synergy Change and another in Excel that recognizes the report and creates charts Excel and PowerPoint.

This could be taken another step to automatically run those reports on a weekly or daily basis (cron job) in Perl on the Synergy server side. Then use the Perl OLE module to run the macro in Excel, now your output is ready for viewing on a scheduled basis. In our case a PowerPoint Presentation would be ready for viewing on a scheduled basis.

I am not sure how your dashboard works. But you could use VBA or Perl/PHP to extract the charts for presentation online. PowerPoint has a web presentation export built in. You could also save the charts as pictures.

You could also use the report output and Javacharts to have a completely online experience.

Then there is the Telelogic Dashboard as well as other dashboards that are available.

The real problem is that there are so many options. I hope this helps and I am not making it more confusing.

-------------------------
Thanks,
Brian
Report this to a Moderator Report this to a Moderator
 26-Jul-2006 13:03
User is offline View Users Profile Print this message


Stéphane Thomas

Posts: 8
Joined: 20-Dec-2005

Brian,

I just want is to have in a excel file, a button that enables to record in a sheet all CRs with theirs associated attributes that match to a query.

And at each time I click on that button, new CRs and new attributes values of existing CRs are updated in the excel sheet.

By this way I could build all metrics that I want in other sheets of this excel file.

Stéphane.

Report this to a Moderator Report this to a Moderator
 26-Jul-2006 14:50
User is offline View Users Profile Print this message


Brian Barde

Posts: 39
Joined: 24-Sep-2004

Stéphane,

Option 1:
The easiest way to do this is to run the report from Synergy with Excel as the output.

Option 2:
You could automate the Option 1 process with a Perl script that dumps Option 1's Excel Report file every night (or other time frame). Then you would have the latest report/file every morning.
Just write a Perl Script that runs the specified report and schedule that script to run at some time interval.

Option 3:
Although you could have a button in Excel that uses a VBA Macro to call a Perl script as long as Perl in installed on the machine where the Excel file is located and has access to the Synergy Server. The Perl script would run similar to Option 2 and then poppulate the current Excel Sheets. This one is more complicated. 
VBA code to call Perl:
Shell PerlPath & " """ & PerlScriptName & """", vbHide


Thanks,
Brian

-------------------------
Thanks,
Brian
Report this to a Moderator Report this to a Moderator
 26-Jul-2006 14:52
User is offline View Users Profile Print this message


Robert Poldervaart

Posts: 5
Joined: 7-Jun-2006

I agree with Stephane. But in order to do this, we need some better connectivity to the underlying informix database. I've got an ODBC driver right now and am trying to get it connected. Unfortunately it needs a "listener" port that informix listens on for new connections and I'm having problems getting this from anyone. If I can get ODBC working, then I could do everything within excel. Robert
Report this to a Moderator Report this to a Moderator
 26-Jul-2006 15:04
User is offline View Users Profile Print this message


Brian Barde

Posts: 39
Joined: 24-Sep-2004

An ODBC connection would be nice but we also have the provided quering ability from the Synergy Reporting. As well as the Perl API to Synergy. The attached code is from the Perl API help pages. This way we do not need to know how Synergy stores the information in the database. We can just query for it via the Perl API. There is also a Perl Excel OLE module that allows you to read and write to the Excel application. http://aspn.activestate.com/ASPN/docs/ActivePerl/5.8/faq/Windows/ActivePerl-Winfaq12.html

 



-------------------------
Thanks,
Brian
Report this to a Moderator Report this to a Moderator
Statistics
20925 users are registered to the Telelogic SYNERGY forum.
There are currently 0 users logged in.
The most users ever online was 15 on 15-Jan-2009 at 15:34.
There are currently 0 guests browsing this forum, which makes a total of 0 users using this forum.
You have posted 0 messages to this forum. 0 overall.

FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.