Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Reporting service question

Posted on 2007-09-29
Medium Priority
Last Modified: 2010-03-19
hello there,

i have created a report using SSRS 2005. which is working fine.now 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 month.now i want to know is there some way to reduce this hassle.so that the HRM dept will themselve put the excel in the db and generate the report.give me me suggestion.

Question by:zolf
LVL 19

Expert Comment

ID: 19984000
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.
LVL 14

Expert Comment

by:Jai S
ID: 19984502
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 explained...you can play around with it to see whether it suits your needs...
LVL 18

Expert Comment

ID: 19985587
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 ...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 19986180


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

Author Comment

ID: 19986183

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

yes it is same
LVL 14

Expert Comment

by:Jai S
ID: 19986496
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...


Author Comment

ID: 19986542

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.
LVL 14

Expert Comment

by:Jai S
ID: 19986625
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 wizard...it works like a gem...
i think this will be a lot easier for you...
LVL 14

Expert Comment

by:Jai S
ID: 19986627
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...

Author Comment

ID: 19986671


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
LVL 14

Accepted Solution

Jai S earned 2000 total points
ID: 19986746
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 sheet...select 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 name...it 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...

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

571 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