Solved

How do I create a report with an Excel front end and SQL server back end?

Posted on 2004-04-20
7
223 Views
Last Modified: 2010-04-03
I've been looking but can't find the best way to do this.
The ideal situation would be an Excel file with a SQL Server database.
The data is coming from different tables within one database.  Various calculations have to be done to the data based on information in these tables.  
The end user should be able to just click a button on a web page and download a copy of the report/spreadsheet.  
The data is updated everyday, and the spreadsheet should update automatically.  For example, todays report would include days in April from the 1st to yesterday, the 19th.  Tomorrow's report should include data from the 1st through the 20th.

Can someone point me in the best direction for accomplishing this?

Thanks.

BTW, my background is with ASP and SQL Server.
0
Comment
Question by:jholton
  • 2
  • 2
7 Comments
 

Author Comment

by:jholton
ID: 10872380
The report will also need input parameters such as Region ID and Month.

Thanks.
0
 
LVL 1

Expert Comment

by:CrackerJack326
ID: 10872917
It sounds like you're asking a design kind of question.  If you're looking for working code I can't help but I can help you conceptualize what the code would have to do.

Something like this maybe:
It starts with your ASP, you click on a link to generate a report.  The ASP passes a query or series of queries to the SQL Server to retrieve the data which returns it to the ASP.  Now you have a choice to make - will the data be manipulated server-side or client-side.  Either way ASP will have to call an ActiveX/ OLE/COM/whatever it is now object on the CLIENT computer telling to prepare to receive Excel spreadsheet data, (or you may just have to declare the MIME type for Excel to accomplish this).  This is pretty routine stuff.  Should be easy to pick up some snippets or documentation.

If you decide to manipulate the data client side you're probably going to have to learn a few things about the XLS file format.  Can't help you there.

If you decide server-side you might be able to simply pass the data in a simpler form that Excel may be able to interpret for you into it's own format. But maybe not.

Or for one other option you may find it worth your while to pass the data in whatever form to the client as XML.  Then you can do just about whatever you want with it.  But you're also talking about more work.

For setting the date range, all you have to do is use the function that retrieves to day's date and stores it in one variable and another variable that's always set to the first day of the month and use them as the test boundaries for the data coming across.

I hope you find this helpful.  If you  don't, I'm not offended.  It probably means I'm way in over my head here.

Good luck,
Cracker
0
 

Author Comment

by:jholton
ID: 10872936
I know how to create a spreadsheet in ASP by setting the MIME type.  
Do you know if I can open an exiting file though, and dump the data on a "tab" in it?

Thanks.
0
 
LVL 1

Accepted Solution

by:
CrackerJack326 earned 125 total points
ID: 10875679
If you're talking about an existing file on the client side this is not possible directly.  For security reasons, web languages, especially scripting languages are not permitted to read or write to client hard drives.

There are, of course indirect ways of doing this.  This requires the use of a plugin or ActiveX control "signed with permissions" which already exists on the client or can be automatically downloaded.  However since it is required to do potentially dangerous things with client data, the client must give permission.  This is common with ActiveX or Java based programs.  It does however require you to get your code authenticated by a third party.  Well, it's not required, but not doing it is a bad idea.  Basically you show them your code and they will make sure that it's not going to pull any funny business with the end-user, and then they provide a digital signature for it.

As far as an existing server-side file goes, if ASP can't (and it may or may not), I'm sure other well established server-side languages can handle file I/O.

If anything doesn't make sense let me know.

Good Luck,
Cracker

P.S. The technology may not be called ActiveX any more.  I haven't kept up on my terminology in that department.  But it will definitely be something like COM/COM+/DCOM (D for distributed) or possibly ADO (ActiveX Data Objects).  These are all extensively documented at http://msdn.microsoft.com and freely available.
0
 
LVL 2

Assisted Solution

by:pek99
pek99 earned 125 total points
ID: 10876150
What would I do is the following:

On the server side
1. Desired Excel Table (empty) assign as an ODBC source.
2. The SQL DB as another ODBC source
3. write a small C++ program running as Windows service, which keeps the data up to date on regular basis (copying from one ODBC to another, should be simple enough for you.)
4. Make the Excel file available for download from Web by any browser.

Maybe you can achieve the step 3 with ASP, too.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Don't buy such-and-such disk drives, they are not reliable!    Boy, how many times have we read a post from a well-meaning person who had some bad experiences with a drive and now they exclaim the entire company's products can't be trusted.  First, …
This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now