Report with Excel as Database (Part)
in [Reports]
|
Prev: How to use reporting Services from our web site link?
Next: Is it possible to call reporting Services from our internet web si
From: Björn Stüber on 12 Jan 2010 13:47 Hello NG, I searched and tested for hours but didn't find out something. I want to build a report with a SQL2008 procedure as my Dataset. In the procedure I want to select a table from a EXCEL File stored in 'X:'. I have no problems to select the table out of the SQL-Managementstudio but in the Reportdesigner he says he could not list the fields because of a Dataelement access Problem. So I tried to build a tempor�ry Table (#tmp) and select my data into it. I also tried to add a linkedserver... Everytime it's the same. I can Select in ManagementStudio but not in Reportdesigner. I know that the Designer has no Problems with TempTables! I you have any questions like the detailed error, i will have to do it tomorrow at work, sorry. Why I am doing that: I work in a company with 700 people using the ssrs. Somethines they have a Excel-File with Customer IDs stored on my SQL2008 Server. To accomplish their Table with the Adress of the Customer I had the Idea of the using 'local' Excel Files. Each User has a unique Drive X: for the own Data...so the Server will use the File of the Person that started the report (i hope). For any better/other Idea doing that i would thank you. BTW: Sorry for the awful english ;) thx Bjoern Stueber
From: Uri Dimant on 13 Jan 2010 05:48 Hi Do you have 64 bit SSRS? Is that possible of JET drive does not support for 64 bits But if you can insert into a table from the Excel in SQL Server what is the problem to create a database to run SELECT from that table? "Bj�rn St�ber" <gibmirnews(a)gmx.net> wrote in message news:u7iCse7kKHA.5568(a)TK2MSFTNGP02.phx.gbl... > Hello NG, > > I searched and tested for hours but didn't find out something. > I want to build a report with a SQL2008 procedure as my Dataset. > In the procedure I want to select a table from a EXCEL File stored in > 'X:'. > > I have no problems to select the table out of the SQL-Managementstudio but > in the > Reportdesigner he says he could not list the fields because of a > Dataelement access Problem. > > So I tried to build a tempor�ry Table (#tmp) and select my data into it. > I also tried to add a linkedserver... > Everytime it's the same. I can Select in ManagementStudio but not in > Reportdesigner. I know that the Designer has no Problems with TempTables! > I you have any questions like the detailed error, i will have to do it > tomorrow at work, sorry. > > Why I am doing that: I work in a company with 700 people using the ssrs. > Somethines they have a Excel-File with Customer IDs stored on my SQL2008 > Server. To accomplish their Table with the Adress of the Customer I had > the Idea of the using 'local' Excel Files. Each User has a unique Drive X: > for the own Data...so the Server will use the File of the Person that > started the report (i hope). > > For any better/other Idea doing that i would thank you. > > BTW: Sorry for the awful english ;) > > thx > > Bjoern Stueber
From: Björn Stüber on 13 Jan 2010 12:24 Am 13.01.2010 11:48, schrieb Uri Dimant: > Hi > Do you have 64 bit SSRS? Is that possible of JET drive does not support for > 64 bits Yes it's the 64bit version... don't know about Problems with that, but I will search for. > > But if you can insert into a table from the Excel in SQL Server what is the > problem to create a database to run SELECT from that table? Everything is OK when starting the INSERT in the Managementstudio just for testing. My Problem is: The User gets an EXCEL-File from wherever with customer IDs and wants to Add the Address of the Customers. Today he sent us the File and we complete the data. But this is a silly work and I want to automate this. So my Idea was that the Report looks in a special file like 'X:\MyCustomerIDs' with the IDs in Column A and return a table with the IDs and the Address. Every User has a own Drive X: so that the Report will use the correct File. Because of the different Files I cannot INSERT the Date for the Users. Today I think It's better to build a .Net Program with a OpenDialog that returns the original Table Column plus the Address... Looking forward for any other idea and/or solution. Thanks for you Answer Uri > > > > > > > "Bj�rn St�ber"<gibmirnews(a)gmx.net> wrote in message > news:u7iCse7kKHA.5568(a)TK2MSFTNGP02.phx.gbl... >> Hello NG, >> >> I searched and tested for hours but didn't find out something. >> I want to build a report with a SQL2008 procedure as my Dataset. >> In the procedure I want to select a table from a EXCEL File stored in >> 'X:'. >> >> I have no problems to select the table out of the SQL-Managementstudio but >> in the >> Reportdesigner he says he could not list the fields because of a >> Dataelement access Problem. >> >> So I tried to build a tempor�ry Table (#tmp) and select my data into it. >> I also tried to add a linkedserver... >> Everytime it's the same. I can Select in ManagementStudio but not in >> Reportdesigner. I know that the Designer has no Problems with TempTables! >> I you have any questions like the detailed error, i will have to do it >> tomorrow at work, sorry. >> >> Why I am doing that: I work in a company with 700 people using the ssrs. >> Somethines they have a Excel-File with Customer IDs stored on my SQL2008 >> Server. To accomplish their Table with the Adress of the Customer I had >> the Idea of the using 'local' Excel Files. Each User has a unique Drive X: >> for the own Data...so the Server will use the File of the Person that >> started the report (i hope). >> >> For any better/other Idea doing that i would thank you. >> >> BTW: Sorry for the awful english ;) >> >> thx >> >> Bjoern Stueber > >
From: Uri Dimant on 14 Jan 2010 01:23 Hi I have another idea. You can have SSIS package to read on time period comming files and insert the data into a table. Then users can modify those files and SSIS package can import the data back to the file.... "Bj�rn St�ber" <gibmirnews(a)gmx.net> wrote in message news:eACGLVHlKHA.6096(a)TK2MSFTNGP02.phx.gbl... > Am 13.01.2010 11:48, schrieb Uri Dimant: >> Hi >> Do you have 64 bit SSRS? Is that possible of JET drive does not support >> for >> 64 bits > Yes it's the 64bit version... don't know about Problems with that, but I > will search for. >> >> But if you can insert into a table from the Excel in SQL Server what is >> the >> problem to create a database to run SELECT from that table? > Everything is OK when starting the INSERT in the Managementstudio just for > testing. My Problem is: The User gets an EXCEL-File from wherever with > customer IDs and wants to Add the Address of the Customers. Today he sent > us the File and we complete the data. But this is a silly work and I want > to automate this. So my Idea was that the Report looks in a special file > like 'X:\MyCustomerIDs' with the IDs in Column A and return a table with > the IDs and the Address. Every User has a own Drive X: so that the Report > will use the correct File. > Because of the different Files I cannot INSERT the Date for the Users. > > Today I think It's better to build a .Net Program with a OpenDialog that > returns the original Table Column plus the Address... > > Looking forward for any other idea and/or solution. > > Thanks for you Answer Uri >> >> >> >> >> >> >> "Bj�rn St�ber"<gibmirnews(a)gmx.net> wrote in message >> news:u7iCse7kKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>> Hello NG, >>> >>> I searched and tested for hours but didn't find out something. >>> I want to build a report with a SQL2008 procedure as my Dataset. >>> In the procedure I want to select a table from a EXCEL File stored in >>> 'X:'. >>> >>> I have no problems to select the table out of the SQL-Managementstudio >>> but >>> in the >>> Reportdesigner he says he could not list the fields because of a >>> Dataelement access Problem. >>> >>> So I tried to build a tempor�ry Table (#tmp) and select my data into it. >>> I also tried to add a linkedserver... >>> Everytime it's the same. I can Select in ManagementStudio but not in >>> Reportdesigner. I know that the Designer has no Problems with >>> TempTables! >>> I you have any questions like the detailed error, i will have to do it >>> tomorrow at work, sorry. >>> >>> Why I am doing that: I work in a company with 700 people using the ssrs. >>> Somethines they have a Excel-File with Customer IDs stored on my SQL2008 >>> Server. To accomplish their Table with the Adress of the Customer I had >>> the Idea of the using 'local' Excel Files. Each User has a unique Drive >>> X: >>> for the own Data...so the Server will use the File of the Person that >>> started the report (i hope). >>> >>> For any better/other Idea doing that i would thank you. >>> >>> BTW: Sorry for the awful english ;) >>> >>> thx >>> >>> Bjoern Stueber >> >> >
From: Bjoern Stueber on 15 Jan 2010 12:50
Hi Uri, sorry for my late answer. Yes that is a cool Idea. They can place the files in a tranfer Path and SSIS can add the IDs plus a UserID((maybe the Filename is the UserID) in a Table... after Using the Data in SSRS the Data with the USERID can be deleted. When I understand your Advice, your Idea was: One File with Data ... but then the SSIS can not know for which User the Data is. The User can insert the IDs and his User ID..but that is not easy enough for beginners. But your Idea is the easiest way for poweruser .. I will give it a try. Many thanks for that. Bjoern Am 14.01.2010 07:23, schrieb Uri Dimant: > Hi > I have another idea. You can have SSIS package to read on time period > comming files and insert the data into a table. Then users can modify those > files and SSIS package can import the data back to the file.... > > > > > "Bj�rn St�ber"<gibmirnews(a)gmx.net> wrote in message > news:eACGLVHlKHA.6096(a)TK2MSFTNGP02.phx.gbl... >> Am 13.01.2010 11:48, schrieb Uri Dimant: >>> Hi >>> Do you have 64 bit SSRS? Is that possible of JET drive does not support >>> for >>> 64 bits >> Yes it's the 64bit version... don't know about Problems with that, but I >> will search for. >>> >>> But if you can insert into a table from the Excel in SQL Server what is >>> the >>> problem to create a database to run SELECT from that table? >> Everything is OK when starting the INSERT in the Managementstudio just for >> testing. My Problem is: The User gets an EXCEL-File from wherever with >> customer IDs and wants to Add the Address of the Customers. Today he sent >> us the File and we complete the data. But this is a silly work and I want >> to automate this. So my Idea was that the Report looks in a special file >> like 'X:\MyCustomerIDs' with the IDs in Column A and return a table with >> the IDs and the Address. Every User has a own Drive X: so that the Report >> will use the correct File. >> Because of the different Files I cannot INSERT the Date for the Users. >> >> Today I think It's better to build a .Net Program with a OpenDialog that >> returns the original Table Column plus the Address... >> >> Looking forward for any other idea and/or solution. >> >> Thanks for you Answer Uri >>> >>> >>> >>> >>> >>> >>> "Bj�rn St�ber"<gibmirnews(a)gmx.net> wrote in message >>> news:u7iCse7kKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>>> Hello NG, >>>> >>>> I searched and tested for hours but didn't find out something. >>>> I want to build a report with a SQL2008 procedure as my Dataset. >>>> In the procedure I want to select a table from a EXCEL File stored in >>>> 'X:'. >>>> >>>> I have no problems to select the table out of the SQL-Managementstudio >>>> but >>>> in the >>>> Reportdesigner he says he could not list the fields because of a >>>> Dataelement access Problem. >>>> >>>> So I tried to build a tempor�ry Table (#tmp) and select my data into it. >>>> I also tried to add a linkedserver... >>>> Everytime it's the same. I can Select in ManagementStudio but not in >>>> Reportdesigner. I know that the Designer has no Problems with >>>> TempTables! >>>> I you have any questions like the detailed error, i will have to do it >>>> tomorrow at work, sorry. >>>> >>>> Why I am doing that: I work in a company with 700 people using the ssrs. >>>> Somethines they have a Excel-File with Customer IDs stored on my SQL2008 >>>> Server. To accomplish their Table with the Adress of the Customer I had >>>> the Idea of the using 'local' Excel Files. Each User has a unique Drive >>>> X: >>>> for the own Data...so the Server will use the File of the Person that >>>> started the report (i hope). >>>> >>>> For any better/other Idea doing that i would thank you. >>>> >>>> BTW: Sorry for the awful english ;) >>>> >>>> thx >>>> >>>> Bjoern Stueber >>> >>> >> > > |