Business Report with SQL Server 2000

• Árpád Lovas-459892

  Grasshopper

  Points: 15

  More actions

  October 6, 2008 at 4:54 am

  #194382

  Hi All,

  I need to find a solution for the problem below.

  There are 7-8 different Excel spreadsheets, each of them contains more than one worksheet. They are complex business reports – there are more than one data table on a worksheet, they are formatted, etc.

  These 7-8 spreadsheet (let’s call them entry level spreadsheets) are updated by different users during the day. Every night I should export the data from this entry level spreadsheets into a SQL Server 2000 database. That’s the first task. Then based upon the imported data I should be able to generate two next level reports whenever it's needed in a pre-defined (by my manager) format. These reports naturally would be complex business reports as well – several worksheets, each with several tables, summaries, formatting…

  Importing/exporting simple Excel spreadsheets into/from SQL Server 2000 is not a problem with DTS.

  But now the input and output formats are complex as well... Nice,colorful business reports with several

  data tables on one worksheet...

  Could some suggest a tool/solution for this problem? I have found a tool called OfficeWriter from SoftArtisans which maybe able to do the export part... But for the import...

  If you have any idea please let me know.

  Thanks,

  Arpad

• Greg Edwards-268690

  SSC-Insane

  Points: 20680

  More actions

  October 7, 2008 at 8:33 am

  #881711

  Is creating an application to allow data entry / maintenance - getting the data into SQL server, out of Excel, an option?

  Then you wouldn't have to worry about importing. And would have much better control of data types, integrity, etc.

  Greg E

• Gosta Munktell

  SSCertifiable

  Points: 6004

  More actions

  October 8, 2008 at 1:43 am

  #882124

  To solve a situation you describe is exactly what I do as a main part for living.

  There are many solutions but I to be short don't use DTS or SSIS.

  I use SQLserver 2005 and Excel 2003 or 2007 with VBA programming

  and ADO and OLE remote DB to transfer data from the database to Excel and

  back again. Often I design a data warehouse to store the data (and aggregated data)

  and rules! how to populate the data in different workbooks and sheets.

  To give code examples is worthless unless the whole solution is presented. Maybe if

  time I will write an article about it.

  If you are interested we can off line have a contact.

  Gosta M