![]() |
Telelogic SYNERGY (steve huntington) | ![]() |
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 |
![]() |
![]()
|
![]() |
|
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. |
|
![]() |
|
![]() |
|
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 |
|
![]() |
|
![]() |
|
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 |
|
![]() |
|
![]() |
|
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 |
|
![]() |
|
![]() |
|
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. |
|
![]() |
|
![]() |
|
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 |
|
![]() |
|
![]() |
|
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
|
|
![]() |
|
![]() |
|
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 |
|
![]() |
Telelogic SYNERGY
» SYNERGY/Change
»
Query in excel format
|
![]() |
FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.