Report Designer - Importing Excel Files Into Datasets

Using the MS SQL Server 2005 CTP on os win2k prof.
I have set up two datasets in a report that are supposed to be referencing
to separate data sources where the following connection strings are aimed at
separate files in the same target folder: -
DataSource1: -
Dsn=Excel
Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
DataSource2: -
Dsn=Excel
Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
I can correctly import the columns of File1.xls to DataSet1 but when
attempting to import File2.xls to DataSet2, the columns of DataSet1 are only
visible.
I have definitely referenced the correct data sources to each dataset.
Any ideas why DataSource2 is not appearing?
Cheers
TrevI have found the solution - set up individual File DSNs for each file and
point to these; they can be generic too.
Thx to those who may have provided feedback if I had not answered my own
question!!!
"auldreidy" wrote:
> Using the MS SQL Server 2005 CTP on os win2k prof.
> I have set up two datasets in a report that are supposed to be referencing
> to separate data sources where the following connection strings are aimed at
> separate files in the same target folder: -
> DataSource1: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> DataSource2: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> I can correctly import the columns of File1.xls to DataSet1 but when
> attempting to import File2.xls to DataSet2, the columns of DataSet1 are only
> visible.
> I have definitely referenced the correct data sources to each dataset.
> Any ideas why DataSource2 is not appearing?
> Cheers
> Trev|||how did you get the excel file to import into the dataset?
i can't seem to figure this out in the Query String window after setting up
the dsn...
"auldreidy" <auldreidy@.discussions.microsoft.com> wrote in message
news:E7921576-2D35-45CC-A742-540C46D2E13B@.microsoft.com...
> Using the MS SQL Server 2005 CTP on os win2k prof.
> I have set up two datasets in a report that are supposed to be referencing
> to separate data sources where the following connection strings are aimed
> at
> separate files in the same target folder: -
> DataSource1: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> DataSource2: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> I can correctly import the columns of File1.xls to DataSet1 but when
> attempting to import File2.xls to DataSet2, the columns of DataSet1 are
> only
> visible.
> I have definitely referenced the correct data sources to each dataset.
> Any ideas why DataSource2 is not appearing?
> Cheers
> Trev|||I set up a shared data source and then referenced it in the report dataset.
When you're setting up the data source, RD allows you to browse for the dsn
so you don't need to figure it out!
Trev
"TroyS" wrote:
> how did you get the excel file to import into the dataset?
> i can't seem to figure this out in the Query String window after setting up
> the dsn...
>
> "auldreidy" <auldreidy@.discussions.microsoft.com> wrote in message
> news:E7921576-2D35-45CC-A742-540C46D2E13B@.microsoft.com...
> > Using the MS SQL Server 2005 CTP on os win2k prof.
> >
> > I have set up two datasets in a report that are supposed to be referencing
> > to separate data sources where the following connection strings are aimed
> > at
> > separate files in the same target folder: -
> >
> > DataSource1: -
> >
> > Dsn=Excel
> > Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> >
> > DataSource2: -
> >
> > Dsn=Excel
> > Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> >
> > I can correctly import the columns of File1.xls to DataSet1 but when
> > attempting to import File2.xls to DataSet2, the columns of DataSet1 are
> > only
> > visible.
> >
> > I have definitely referenced the correct data sources to each dataset.
> >
> > Any ideas why DataSource2 is not appearing?
> >
> > Cheers
> > Trev
>
>

Report Corruption When Exported to Excel

Hello. Anyone please help me with my problem with regards to exporting reports to excel. My report consists of two tables, first table is just plain data displaying from the database; however, it has drilldowns. My second table consists of graphs. My report generates big size depends on the parameters selected by the user. The design of this report is the requirement of our clients, so i cannot do anything about it. So, here comes the problem when i try to export it to excel. The following error message is encountered:

"Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files\Content.IE5\VKP9ZCSW\MyStore? Performance (Estate).xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted."

The report corruption is inconsistent, sometimes, the exported report is ok; however, there are some time where the report exported is corrupted. What could be the possible cause of this report corruption? Is this a known bug for the export feature of the Reporting Services? Does the file size matter? Moreover, i find this so weird, since, we use subscription feature to send reports to our clients and we chose to send these reports in .xls format. I have observed: a report is exported, same date and time, same parameters, two subscriptions were done for a single report; however, one report was corrupted but the other report was ok. What is the problem on this bug?

Anyway, we are using SP2.

Please help me. A reply for this will be very much appreciated. Thank you. May God bless you.

I have encountered this error as well.

Seems to be related to the amount of characters in a cell in my case. My report is all text and when the character count in a cell reaches around 4000 or so the report won't export to excel. It will export in Visual Studio but not when run from our report server.

Any help from Microsoft would be appreciated!!!

|||I dont think i reach that number of characters (4000) for every cell.|||I have a hunch. Try moving the contents of your paged footer to the report body. Make sure nothing is in the page footer.|||

In my case I have no information in the report footers.

The reports that I need to create are based on a database with a lot of textural information and when the character count reaches at or around 4000 characters in a cell then the report will not export to excel. It is interesting that the report will export to excel when run from my Visual Studio environment.

As a test I created a new report. Dropped a table on the body of the report and pasted some random words (4300 characters) in the “Detail” cell. I next created a dataset that would return one row so that the report would run. I next run the report from Visual Studio and export to excel. When I open the excel report everything is fine.

Now I publish the report to our report server (SQL 2K) and run it but when I export the report to excel I get the corrupt data error.

|||

We are also experiencing this problem. I also do not have anything in a report footer. The report we use has a drop down box with values for one of two parameters. The report renders and exports fine for most of the values, however one or two dont export correctly. It is not always the same values that cause the error, but sometimes it works and sometimes it doesnt.

The report does include quite a bit of information with 3 matrixes and 3 tables on top of each other, each with a page breaks so the information will be reported in several tabs in the same Excel worksheet. However, again, it works sometimes and not other times.

Another thing, I noticed in the thread that someone said it worked from the Visual Studio environment, but I did not find that to be true always.

Any help from Microsoft would be appreciated! This is a report that is run for executive management each month and this is the second month we have had a problem with it. We have tried using a different machine that had not been run today at all, in case it was too many windows open at a time, or memory usage, etc. But that did not work either. It finally just worked for the value we needed.

Thanks!

- Glenda

Regeneration Technologies, Inc.

ggable@.rtix.com

|||

It sounds very much like the report isn't being rendered improperly, but the file itself has become corrupt by the time it is opened on the client. Would it be possible to save the file on a client machine when that error occurs, and examine it outside of Excel? If it is possible to save the file, check if it is of a reasonable size. It should be virtually identical to a non-corrupt xls generated by the same request. If there is a large disparity, the file might be getting delivered in a corrupt state, as opposed to rendered in a corrupt state.

|||

There was a bug resolved in SP2 for RS 2000 that addressed issues where rendering would fail when a single textbox had 4000+ characters. Are you currently running SP2 on your server? It is entirely possible that your client has become updated while your server has not, leading it to work in Visual Studio but not on the server.

|||

I too have just encountered this problem on a report that worked successfully on 7/14/06. Is it possible that MS July patch introduced this problem. If not, then it may be my data. I have one LargeNarrative field in the report and there may be new data on the report that goes beyond 4K characters.

In my case does not work from VS 2003 either. Using VS2003 7.1.3088/, SS2005 with SP1, RS 2003 fully patched

|||I have the same problem, in my report, I have few graphs and the color was user defined color, when I change it to pre defined colors, it works. Any body know how to fix that? I need to use the user defined colorsql

Report Corruption When Exported to Excel

Hello. Anyone please help me with my problem with regards to exporting reports to excel. My report consists of two tables, first table is just plain data displaying from the database; however, it has drilldowns. My second table consists of graphs. My report generates big size depends on the parameters selected by the user. The design of this report is the requirement of our clients, so i cannot do anything about it. So, here comes the problem when i try to export it to excel. The following error message is encountered:

"Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files\Content.IE5\VKP9ZCSW\MyStore? Performance (Estate).xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted."

The report corruption is inconsistent, sometimes, the exported report is ok; however, there are some time where the report exported is corrupted. What could be the possible cause of this report corruption? Is this a known bug for the export feature of the Reporting Services? Does the file size matter? Moreover, i find this so weird, since, we use subscription feature to send reports to our clients and we chose to send these reports in .xls format. I have observed: a report is exported, same date and time, same parameters, two subscriptions were done for a single report; however, one report was corrupted but the other report was ok. What is the problem on this bug?

Anyway, we are using SP2.

Please help me. A reply for this will be very much appreciated. Thank you. May God bless you.

I have encountered this error as well.

Seems to be related to the amount of characters in a cell in my case. My report is all text and when the character count in a cell reaches around 4000 or so the report won't export to excel. It will export in Visual Studio but not when run from our report server.

Any help from Microsoft would be appreciated!!!

|||I dont think i reach that number of characters (4000) for every cell.|||I have a hunch. Try moving the contents of your paged footer to the report body. Make sure nothing is in the page footer.|||

In my case I have no information in the report footers.

The reports that I need to create are based on a database with a lot of textural information and when the character count reaches at or around 4000 characters in a cell then the report will not export to excel. It is interesting that the report will export to excel when run from my Visual Studio environment.

As a test I created a new report. Dropped a table on the body of the report and pasted some random words (4300 characters) in the “Detail” cell. I next created a dataset that would return one row so that the report would run. I next run the report from Visual Studio and export to excel. When I open the excel report everything is fine.

Now I publish the report to our report server (SQL 2K) and run it but when I export the report to excel I get the corrupt data error.

|||

We are also experiencing this problem. I also do not have anything in a report footer. The report we use has a drop down box with values for one of two parameters. The report renders and exports fine for most of the values, however one or two dont export correctly. It is not always the same values that cause the error, but sometimes it works and sometimes it doesnt.

The report does include quite a bit of information with 3 matrixes and 3 tables on top of each other, each with a page breaks so the information will be reported in several tabs in the same Excel worksheet. However, again, it works sometimes and not other times.

Another thing, I noticed in the thread that someone said it worked from the Visual Studio environment, but I did not find that to be true always.

Any help from Microsoft would be appreciated! This is a report that is run for executive management each month and this is the second month we have had a problem with it. We have tried using a different machine that had not been run today at all, in case it was too many windows open at a time, or memory usage, etc. But that did not work either. It finally just worked for the value we needed.

Thanks!

- Glenda

Regeneration Technologies, Inc.

ggable@.rtix.com

|||

It sounds very much like the report isn't being rendered improperly, but the file itself has become corrupt by the time it is opened on the client. Would it be possible to save the file on a client machine when that error occurs, and examine it outside of Excel? If it is possible to save the file, check if it is of a reasonable size. It should be virtually identical to a non-corrupt xls generated by the same request. If there is a large disparity, the file might be getting delivered in a corrupt state, as opposed to rendered in a corrupt state.

|||

There was a bug resolved in SP2 for RS 2000 that addressed issues where rendering would fail when a single textbox had 4000+ characters. Are you currently running SP2 on your server? It is entirely possible that your client has become updated while your server has not, leading it to work in Visual Studio but not on the server.

|||

I too have just encountered this problem on a report that worked successfully on 7/14/06. Is it possible that MS July patch introduced this problem. If not, then it may be my data. I have one LargeNarrative field in the report and there may be new data on the report that goes beyond 4K characters.

In my case does not work from VS 2003 either. Using VS2003 7.1.3088/, SS2005 with SP1, RS 2003 fully patched

|||I have the same problem, in my report, I have few graphs and the color was user defined color, when I change it to pre defined colors, it works. Any body know how to fix that? I need to use the user defined color

Report Action + multi parameters

Hi

Using Report Action, Can accept multi parameters from Excel Pivot Table?

Exist some way of doing this.

Please help me

thanks for your commentaries

carlos

Your probably better off using an url action instead of report action(which behind the covers is basically the same). Because if you want to specify more than one value you must specify that parameter over and over again at design time(i think), instead of concatenating a string. This because a url with a multivalue parameter looks like this:

http://localhost/ReportServer?%2fCatalog%2fMultiReport&rs:Command=Render&ParameterTest=1&ParameterTest=2

Look at these two links, i think you will get on how to do it in a URL action:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1047470&SiteID=1
http://msdn2.microsoft.com/en-us/library/ms153586.aspx

|||

Hi, thank for the help.

But, When I select several cells, since I do to pass them as parameters to the URL?

Is it possible?

|||

Actions are executed for specific cells based on the co-ordinates of that cell, so I would not expect the Excel user interface to allow you to launch an action when multile cells are selected.

|||

Ok, thank a lot for you help