Exporting CR without grouping or formatting

Hi, I'm currently evaluating Crystal Reports XI, testing it's exporting capabilities.

All I want to do is export from a report, just the data (rows of underlying records) with just the field headers (names) in the first row. Just as you would when exporting a query/table from applications such as MS Access into a Tab delimited text file, or comma delimited file.

What I do not want to export is any additional information on the report I.e. Report headers, page headers, group headers and information, totals.

I have tried in CR, csv, ttx, txt, xls (data only), but all of these include grouping information of some sort if the report itself is grouped on the report side.

Has anyone managed to export just the data from a report that has grouping on the report?

Any ideas?

Any help would be greatly appreciated.If you want to export only some part of report then that should be in subreport and export the subreport

Export To PDF Loses VbCRLF Formatting

Hi

I've simplified this so I can test it's not me and so it's easier to explain.

I have one table in SQL server called Table1. Five fields called: One, Two, Three, Four, Five. Values of NULL, 2, 3, 4, NULL.

Reporting services, put a table in a report. Two fields. Header of Field1 and Field2.

Value field data for Field1 ="One: " & vbcrlf & "Two: " & vbcrlf & "Three: " & vbcrlf & "Four: " & vbcrlf & "Five: "

Value field data for Field2 =Fields!One.Value & vbcrlf & Fields!Two.Value & vbcrlf & Fields!Three.Value & vbcrlf & Fields!Four.Value & vbcrlf & Fields!Five.Value

Preview the report, all is well, you get this:

Field1 Field2

One:

Two: 2

Three: 3

Four: 4

Five:

Export it to a PDF, all is not well, I get this:

Field1 Field2

One: 2

Two: 3

Three: 4

Four:

Five:

I can make it work by checking the NULL fields and replacing them with a character. But why does it ignore the vbCRLF when exported to a PDF?

Any ideas...

give minimum space (width and hight) for that field and try|||

Hello merin

Not sure how I give it minimum space?

Also the field is being outputted with enough space (I think), as 234 appear in that field. If I replace the NULLS with a hypen, it works perfectly. But that NULL in the table in field one is causing the VbCRLF to be ignored when it is exporting to a PDF.

With a hypen as a replacement for the NULL I get this preview and it outputs perfectly to a PDF. (This is how I've fixed the problem, but I'm still interested as to why this happens)

Field 1 Field2

One: -

Two: 2

Three: 3

Four: 4

Five: -

|||

Unfortunately, this is not something we support very well at all in SQL Server 2005. You'll see inconsistent behavior - for instance, exporting to TIFF will act as if the tabs are not present so the words will run together and exporting to PDF will act as if the tabs are spaces except for underline which won't cover the complete text.

Your best bet will be to do this via code. Switch to a fixed-width font and simulate tabs with multiple spaces.

Sorry I don't have a better answer.

|||

Thanks for this. The NULL replace with a hypen works for me for now. Hopefully in the next SP it will be a little more consistant with the formatting, here's hoping anyway.

Export to Excel: Date formatting issue

When I export my report to Excel, the dates in the report are formatted as
text and need further work before they can be manipulated as dates by
formulae.
Is there a method to export the dates so that they arrive in my Excel
worksheet as date formatted cells?
Thanks for any help.What about the CDate function?
"MarkC" wrote:
> When I export my report to Excel, the dates in the report are formatted as
> text and need further work before they can be manipulated as dates by
> formulae.
> Is there a method to export the dates so that they arrive in my Excel
> worksheet as date formatted cells?
> Thanks for any help.

export to excel without formatting

Is is possible to render a report to excel without formatting?
For example, I have a report that displays like this in my web browser.
Country State City Sales
USA MN A 100.00
B 50.00
C 50.00
CA A 500.00
B 25.00
TX A 225.00
B 63.25
C 100.00
When I export it to excel I would like every cell to be filled in.
Country State City Sales
USA MN A 100.00
USA MN B 50.00
USA MN C 50.00
USA CA A 500.00
USA CA B 25.00
USA TX A 225.00
USA TX B 63.25
USA TX C 100.00
Any info or help on this would be great.Will export to CSV work?
Steve MunLeeuw
"retkow" <retkow@.discussions.microsoft.com> wrote in message
news:FD8A536E-9B93-414E-850F-9772A810E98F@.microsoft.com...
> Is is possible to render a report to excel without formatting?
> For example, I have a report that displays like this in my web browser.
> Country State City Sales
> USA MN A 100.00
> B 50.00
> C 50.00
> CA A 500.00
> B 25.00
> TX A 225.00
> B 63.25
> C 100.00
> When I export it to excel I would like every cell to be filled in.
> Country State City Sales
> USA MN A 100.00
> USA MN B 50.00
> USA MN C 50.00
> USA CA A 500.00
> USA CA B 25.00
> USA TX A 225.00
> USA TX B 63.25
> USA TX C 100.00
> Any info or help on this would be great.
>|||Exporting to CSV does work, but the users and customers who run the reports
will not consider this a reasonable option. The reporting tool my company is
using right now allows exporting to excel without formatting.
"Steve MunLeeuw" wrote:
> Will export to CSV work?
> Steve MunLeeuw
> "retkow" <retkow@.discussions.microsoft.com> wrote in message
> news:FD8A536E-9B93-414E-850F-9772A810E98F@.microsoft.com...
> > Is is possible to render a report to excel without formatting?
> >
> > For example, I have a report that displays like this in my web browser.
> >
> > Country State City Sales
> > USA MN A 100.00
> > B 50.00
> > C 50.00
> > CA A 500.00
> > B 25.00
> > TX A 225.00
> > B 63.25
> > C 100.00
> >
> > When I export it to excel I would like every cell to be filled in.
> >
> > Country State City Sales
> > USA MN A 100.00
> > USA MN B 50.00
> > USA MN C 50.00
> > USA CA A 500.00
> > USA CA B 25.00
> > USA TX A 225.00
> > USA TX B 63.25
> > USA TX C 100.00
> >
> > Any info or help on this would be great.
> >
>
>|||what is the difference'
i mean seriously?
they have one additional step where they need to use 'SaveAs' instead of
Save?
write a clientside macro for them
"retkow" <retkow@.discussions.microsoft.com> wrote in message
news:0ECC938F-1B10-494A-BF33-8E8BB5DCE471@.microsoft.com...
> Exporting to CSV does work, but the users and customers who run the
> reports
> will not consider this a reasonable option. The reporting tool my company
> is
> using right now allows exporting to excel without formatting.
>
> "Steve MunLeeuw" wrote:
>> Will export to CSV work?
>> Steve MunLeeuw
>> "retkow" <retkow@.discussions.microsoft.com> wrote in message
>> news:FD8A536E-9B93-414E-850F-9772A810E98F@.microsoft.com...
>> > Is is possible to render a report to excel without formatting?
>> >
>> > For example, I have a report that displays like this in my web browser.
>> >
>> > Country State City Sales
>> > USA MN A 100.00
>> > B 50.00
>> > C 50.00
>> > CA A 500.00
>> > B 25.00
>> > TX A 225.00
>> > B 63.25
>> > C 100.00
>> >
>> > When I export it to excel I would like every cell to be filled in.
>> >
>> > Country State City Sales
>> > USA MN A 100.00
>> > USA MN B 50.00
>> > USA MN C 50.00
>> > USA CA A 500.00
>> > USA CA B 25.00
>> > USA TX A 225.00
>> > USA TX B 63.25
>> > USA TX C 100.00
>> >
>> > Any info or help on this would be great.
>> >
>>|||The problem with a csv file is if some of the values returned in a report
have commas in this will cause the report to not load correctly in excel.
"aaron_kempf@.hotmail.com" wrote:
> what is the difference'
> i mean seriously?
> they have one additional step where they need to use 'SaveAs' instead of
> Save?
> write a clientside macro for them
>
>
> "retkow" <retkow@.discussions.microsoft.com> wrote in message
> news:0ECC938F-1B10-494A-BF33-8E8BB5DCE471@.microsoft.com...
> > Exporting to CSV does work, but the users and customers who run the
> > reports
> > will not consider this a reasonable option. The reporting tool my company
> > is
> > using right now allows exporting to excel without formatting.
> >
> >
> >
> > "Steve MunLeeuw" wrote:
> >
> >> Will export to CSV work?
> >>
> >> Steve MunLeeuw
> >>
> >> "retkow" <retkow@.discussions.microsoft.com> wrote in message
> >> news:FD8A536E-9B93-414E-850F-9772A810E98F@.microsoft.com...
> >> > Is is possible to render a report to excel without formatting?
> >> >
> >> > For example, I have a report that displays like this in my web browser.
> >> >
> >> > Country State City Sales
> >> > USA MN A 100.00
> >> > B 50.00
> >> > C 50.00
> >> > CA A 500.00
> >> > B 25.00
> >> > TX A 225.00
> >> > B 63.25
> >> > C 100.00
> >> >
> >> > When I export it to excel I would like every cell to be filled in.
> >> >
> >> > Country State City Sales
> >> > USA MN A 100.00
> >> > USA MN B 50.00
> >> > USA MN C 50.00
> >> > USA CA A 500.00
> >> > USA CA B 25.00
> >> > USA TX A 225.00
> >> > USA TX B 63.25
> >> > USA TX C 100.00
> >> >
> >> > Any info or help on this would be great.
> >> >
> >>
> >>
> >>
>
>|||I believe you can set the CSV rendering device info to put quotes around
strings. Then you will have no problem with commas in the data. The only
device info I have used is to make the CSV rendering in Report Manager to be
ASCII. This is what I put in rsreportserver.config:
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Note that you can put device info on your url string so you could test this
without changing the file. My guess is that you would do this:
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<Qualifier>"</Qualifier>
</DeviceInfo>
</Configuration>
</Extension>
I searched on CSV in the help for RS 2005 (btw, while you can put device
info on the URL, making a change to the config file only works with RS2005).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"retkow" <retkow@.discussions.microsoft.com> wrote in message
news:61E0F0BB-02EF-4145-A70E-392C0C986312@.microsoft.com...
> The problem with a csv file is if some of the values returned in a report
> have commas in this will cause the report to not load correctly in excel.
>
> "aaron_kempf@.hotmail.com" wrote:
>> what is the difference'
>> i mean seriously?
>> they have one additional step where they need to use 'SaveAs' instead of
>> Save?
>> write a clientside macro for them
>>
>>
>> "retkow" <retkow@.discussions.microsoft.com> wrote in message
>> news:0ECC938F-1B10-494A-BF33-8E8BB5DCE471@.microsoft.com...
>> > Exporting to CSV does work, but the users and customers who run the
>> > reports
>> > will not consider this a reasonable option. The reporting tool my
>> > company
>> > is
>> > using right now allows exporting to excel without formatting.
>> >
>> >
>> >
>> > "Steve MunLeeuw" wrote:
>> >
>> >> Will export to CSV work?
>> >>
>> >> Steve MunLeeuw
>> >>
>> >> "retkow" <retkow@.discussions.microsoft.com> wrote in message
>> >> news:FD8A536E-9B93-414E-850F-9772A810E98F@.microsoft.com...
>> >> > Is is possible to render a report to excel without formatting?
>> >> >
>> >> > For example, I have a report that displays like this in my web
>> >> > browser.
>> >> >
>> >> > Country State City Sales
>> >> > USA MN A 100.00
>> >> > B 50.00
>> >> > C 50.00
>> >> > CA A 500.00
>> >> > B 25.00
>> >> > TX A 225.00
>> >> > B 63.25
>> >> > C 100.00
>> >> >
>> >> > When I export it to excel I would like every cell to be filled in.
>> >> >
>> >> > Country State City Sales
>> >> > USA MN A 100.00
>> >> > USA MN B 50.00
>> >> > USA MN C 50.00
>> >> > USA CA A 500.00
>> >> > USA CA B 25.00
>> >> > USA TX A 225.00
>> >> > USA TX B 63.25
>> >> > USA TX C 100.00
>> >> >
>> >> > Any info or help on this would be great.
>> >> >
>> >>
>> >>
>> >>
>>

Export to excel number formatting problem

Hi,

when i try to export reports to excel number fileds has exported as text !!

I use SQL server with database in US codepage, Reporting Services in english version but excel with Italian codepage settings.

So i must convert the defaul decimal separator from "." to "," within the report generation. This cause that i can't use te cdbl() conversion directly in the report field.

Anyone have sugestion ?

P.S. I can't change the database and excel codepage settings

hi,

Please read this article: http://blogs.msdn.com/bimusings/archive/2007/01/11/happiness-is-automatic-sql-reporting-services-localization.aspx

I also used it.

regards,

Janos

|||

SOLVED !!!!

Thank you

Export to Excel exports HTML formatting too

Is there a way to control exporting only the data in a report and not the html with it?

By "HTML" I think you mean the layout of the report and the style information associated with each item.

The Excel Renderer is a layout renderer, which means it tries to maintain the closest visual fidelity possible to the report definition and other renderers (HTML, PDF). There isn't a way to export only data using the Excel rendering extension.

You might be able to use the CSV renderer for your data; CSV is a data-only renderer and can be opened in Excel.

Export to Excel Custom Format

Hi,

I have a problem when exporting a report to Excel.

The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409].

To reproduce this error:

Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error.

Tnx of your time and effort.

Sorry for my bad english.

G

Hi,

Did you get a solution to your problem? I am having a similar problem: I would like to convert a value in the [$-1010409]Standard format into an integer. Do you know how that can be done? I am using VSTO.

Thanks in advance,

Pedro

|||

Hi Pedro,

No, I never got the solution for that problem. Sorry

I dont need the solution anymore because Im using SSAS (Cubes in Analisis Service) and Excel 2007 (pivot tables). Not using SSRS.

Export to Excel Custom Format

Hi,

I have a problem when exporting a report to Excel.

The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409].

To reproduce this error:

Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error.

Tnx of your time and effort.

Sorry for my bad english.

G

Hi,

Did you get a solution to your problem? I am having a similar problem: I would like to convert a value in the [$-1010409]Standard format into an integer. Do you know how that can be done? I am using VSTO.

Thanks in advance,

Pedro

|||

Hi Pedro,

No, I never got the solution for that problem. Sorry

I dont need the solution anymore because Im using SSAS (Cubes in Analisis Service) and Excel 2007 (pivot tables). Not using SSRS.

export to excel - formatting number output

Hi all,

Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).

I

have a relatively simple task - create an excel spreadsheet with 3

columns of data - Id, Description and Sales. ID and Description are

text, sales is int.

So my SP aggregates and creates my resultset

in my OLE DB Source in the Data Flow. It proceeds to the Excel

destination, and that all seems fine. My issue is that the data is

being written as text. Looking at the excel destination in Advanced

editor:
the Excel Destination Input, Input columns are formatted as

I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and

DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.

The

target excel spreadsheet is being created from a template file. That

template file has header columns. The target column for the Sales has

the entire column formatted to NUMBER (0 decimals). Yet to now avail.

When

I check the spreadsheet, the column has retained the cell formatting,

and I have a 'I' pop-up to inform me that 'someone' has inserted text

data into the number column (even though the data IS number).

Since

the SP spits out INT, it isn't a case of receiving a text value, imho.

While trying to change the external column data type in the advanced

editor, SSIS is quite happy to let me change the value for the Sales

output to DT_I4, apply, and ok. Then, when I open it immedaitely

aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't

handle it, at least tell me when I try and change it. don't let me

change it, and then revert back without telling me! Grumble grumble...

So - anyone know a way around this?

There are no perfect solutions for many behaviors of Jet with the Excel driver. However the driver is always happiest when the destination "table" has been created by the driver itself, which could be done through the Excel Destination or the Import and Export Wizard or ADO code. Excel column formatting seems to be disregarded completely.

The most comprehensive list of Excel driver-related issues is in 257819 How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819. Many of these will surface in SSIS in one form or another.

-Doug

|||

Hi Douglas,

As I read

the links I’ve found so far, IMEX=1 specifies text? Is there a way to have the

IMEX setting per column? If so, what would the correct IMEX setting for numeric

be?

TIA

|||

IMEX makes all data one type. By default that's Text (based on a Registry entry), and I've never heard of anyone changing it.

Please trust me on this -- if you want more predictable behavior, create the destination table in Excel by using the Excel driver. There appear to be hidden Excel column or cell settings that the driver is aware of but that are not exposed in the Excel UI (and not through cell formatting options).

-Doug

|||Thanks Douglas. I'll try that approach. It would seem to imply something like this:

CREATE TABLE `Sales Rank` (

`CatalogueItemReference` NVARCHAR(8),

`DescriptionText` NVARCHAR(100),

`TotalSalesForPeriod` INTEGER

)

that seem about right?|||

I'm not sure whether Jet SQL recognizes NVARCHAR but always have trouble finding the list. Somewhere on your disk there's a JETSQL40.CHM or SQLJET40.CHM or similar. I'd be more inclined to let the Import and Export Wizard or the Excel Destination write the statement for me.

-Doug

|||Thanks Doug, the sample I wrote was a slightly modified version of the

table create that the ssis excel destination task generated. I'll post

if it works :-)