SQL Reporting Newbie Question
Hi,
Have been tasked with the project of creating reports from excel source and displaying to users after our DBA left in a huff. I have basic experience working on SQL mostly admin work but have not worked on the reporting aspect at all.
Our reports run on SQL Server 2005. The other reports just display data to the users through a webpage. They click on a link which asks them to select the report they want to see and it displays it. No inputs are required from user. The sources tend to be Excel files which are updated on a regular basis and this must be reflected on the reports. The DBA has a created a database for reporting and has tables in it from which reports are linked/pulled.
Where do I look for the source of these excel files through SQL Mgmt Studio? Do I need to create a linked server since the sources are on another physical server? What other information can I furnish you guys?
I am hoping to try to copy the method that he has used so that I can figure this out better till we get some additional help for Reporting. Any suggestions? TIA
Have been tasked with the project of creating reports from excel source and displaying to users after our DBA left in a huff. I have basic experience working on SQL mostly admin work but have not worked on the reporting aspect at all.
Our reports run on SQL Server 2005. The other reports just display data to the users through a webpage. They click on a link which asks them to select the report they want to see and it displays it. No inputs are required from user. The sources tend to be Excel files which are updated on a regular basis and this must be reflected on the reports. The DBA has a created a database for reporting and has tables in it from which reports are linked/pulled.
Where do I look for the source of these excel files through SQL Mgmt Studio? Do I need to create a linked server since the sources are on another physical server? What other information can I furnish you guys?
I am hoping to try to copy the method that he has used so that I can figure this out better till we get some additional help for Reporting. Any suggestions? TIA
ASKER
Thanks for getting back tedbilly.
I figured out how to get the data from Excel using a Export/Import process on the database. I still have some questions:
1. How do I get it to show up on a webpage? Is it an IIS thing or something through SQL Mgmt Studio (Views)?
2. How can I apply security restrictions based on groups on who can see what kind of reports?
3. How can I get the reports to refereh? Currently I did not save the SSIS job that I created. Do I need to save one and set it up as a scheduled job?
TIA.
I figured out how to get the data from Excel using a Export/Import process on the database. I still have some questions:
1. How do I get it to show up on a webpage? Is it an IIS thing or something through SQL Mgmt Studio (Views)?
2. How can I apply security restrictions based on groups on who can see what kind of reports?
3. How can I get the reports to refereh? Currently I did not save the SSIS job that I created. Do I need to save one and set it up as a scheduled job?
TIA.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks tedbilly for pointing me in the correct direction. I will post here if I need additional help.
He could be using a linked server for a Excel data source but that would not be a best practice solution.
The other thing he might be doing is running an SSIS or DTS package to import the Excel data into tables in SQL then using the tables as data sources.