Reporting service question

hello there,

i have created a report using SSRS 2005. which is working my question is the data which i use to create the report is given to me in excel format.then i put them in db and create the report every i want to know is there some way to reduce this that the HRM dept will themselve put the excel in the db and generate the report.give me me suggestion.

Who is Participating?
Jai STech ArchCommented:
it is machine data source --> after selecting the machine data source tab --> select EXCEL files and give OK --> it will prompt you to select you EXCEL the EXCEL sheet ( please remmeber that the query is not used here)...after selecting the excel sheett...test the connection by clicking the test connection button..give OK...Click bext in the report wizard...
enter the query "select * from [sheet1$]" (XXX - i have never mentioned to give the EXCEL file name in the query - you have to give your SHEET is noramlly written at the bottom when you open your excel file and by default it is sheet1)...
and afte rthis you will get the columns of your excel and also the data...
the simplest way is to create a DTS package (or whatever its called in SQL 2005, something like SSIS i think), which will import the excel file into "staging" table in SQL 2005, and then after whatever validation, insert those records into your report table.
make sure your HRM dept has security access to whatever directory those excel files are in.
you can then schedule your DTS package to run end of month or whenever.
Jai STech ArchCommented:
is the format of the excel always the same ? then you can go ahead and create a DTS package which can then be scheduled.

If the format is not always the same ? the you have to go for your own custom component...may be written in .NET or other frameworks...check for valid data to be brought into your reports...

and if your EXCEL sheet has proper headers...I have theoratically read that you can creeate reports based on these excel sheet....

create a ODBC for your  excel sheet and then add this ODBC data source to your report and design your reports...
i think you can even retain the format of the report that you are using right now...more help needed ? let me know...

anyway just to confirm - i checked by create a new report project and there seems to be options for wht i have can play around with it to see whether it suits your needs...
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

If the Excel sheet is placed by HRM is named the same all the time, create a job to pull in the file every once in a while (week, day, ... hour even ?) by running a job that does that for you:

-- enable talking to Excel
sp_configure 'Ad Hoc Distributed Queries', 1

-- read excel named area 'HRM' from file C:\Path\To\File.xls into database table YourTable:
insert into YourTable
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="C:\Path\To\File.xls";Extended Properties=Excel 8.0')...[HRM]

Hope this helps ...
zolfAuthor Commented:


how do i create data set using excel sheet in reporting service
zolfAuthor Commented:

--is the format of the excel always the same?

yes it is same
Jai STech ArchCommented:
this is what i followed...

open business dev studio --> new report server project --> select add new reports -->
Select new data source --> select ODBC --> Click edit on the right hand side -->  select use connection string
-->select EXCEL from the list -- say ok --> select you excel sheet in the open dialog window that is being displayed
--> click the TEST CONNECTION button just to make sure that you connection is proper...
--> use a query to select the records from your sheet
you data set now is ready...

zolfAuthor Commented:

thanks for your help.i managed to get the dataset.but now i got stuck in filling the dataset with data.i mean how do i write query to get the columns i need.
Jai STech ArchCommented:
you have to write the query

SELECT * from [Sheet1$]
in the query builder... and you will see the columns that you have in the new report works like a gem...
i think this will be a lot easier for you...
Jai STech ArchCommented:
if you have a name for  your sheet like "Details" ten you have to change the SHEET1 to your sheet name...its obvious but just wanted to mention it...
zolfAuthor Commented:


please bear with me.i follow this step to create the data source.

1. right clcik on Shared Data Source and select ODBC from TYPO
2. then i clcik Edit button which opens another window
3. In there i select Use connection string and click Build Button which opens another window
in here there are two tabs 1.File Data Source 2. Machine Data Source
which one do i choose.
because i get error when i query select * from [test] //test is the name on the excel file
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.