Exporting report to excel [How to set worksheet name]

I am inserting page break after a table to put different data on different
sheet.How can i name the resultant work sheet {like table1 should be
DATA,table2 should be MYSummary}unfortunately, it is not possible. to rename your sheets.
Amarnath, MCTS
"Kamii47" wrote:
> I am inserting page break after a table to put different data on different
> sheet.How can i name the resultant work sheet {like table1 should be
> DATA,table2 should be MYSummary}

Exporting Groups to Excel

I have a group set up woth page breaks at the end. When I export it to Excel
it generates a separate sheet (tab) for each page. Is there a way to get it
to export into one sheet, with page breaks in the correct locations?
--
-MTThere appears to be no way around this if <PageBreakAtEnd> is true when
exporting to Excel. When a report is exported to Excel, each page in the
report becomes a worksheet (and you can't name the worksheets either).
--
Adrian M.
MCP
"MT" <MT@.discussions.microsoft.com> wrote in message
news:7552EDA9-F6F9-4008-B902-8A8FA5FFC1C6@.microsoft.com...
>I have a group set up woth page breaks at the end. When I export it to
>Excel
> it generates a separate sheet (tab) for each page. Is there a way to get
> it
> to export into one sheet, with page breaks in the correct locations?
> --
> -MTsql

exporting data excel

Hi,
I have a problem concerning to exporting data to an excel sheet.
I use 2 stored procedures (spStockReport1, spStockReport2): one to
pasta date to sheet 1, and other to paste dat to sheet 2.
The problem is that the first sp gets the result with no problem but
the second one gets the same results. I've already checked both in
query analyser and i don=B4't see any problems.
This is the code i'm using:
Protected Overrides Function generateData(ByVal args As Hashtable,
ByVal serverSession As ServerSession) As ArrayList
Dim totalItems As Integer =3D 0
Dim sucess As Boolean =3D True
Dim results As ArrayList =3D New ArrayList
Dim hashRes As Hashtable =3D New Hashtable
Dim status As String =3D ServiceResponse.STATUS_OK
Dim description As String =3D ""
Dim searchValue As String =3D System.DBNull.Value.ToString
Try
Dim cache As GenericCache =3D GenericCache.getInstance()
Dim entity As String =3D ""
If (Convert.ToString(args("entity")) <> "") Then
entity =3D args("entity")
End If
Dim database =3D
System.Configuration.ConfigurationSettings.AppSettings("database")
Dim rsResult As RecordSet
Dim iQueryB As New mysql.MySqlQueryBuilder
Dim iConn As IConnection =3D
ConnectionManager.GetInstance.GetConnection
Dim tmpData As New ArrayList
Dim tmpData2 As New ArrayList
Dim iCommtype As New
Command.CommandType(CommandType.StoredProcedure, "spStockReport1")
Dim strQuery As String
strQuery =3D "EXECUTE " & database & ".dbo.spStockReport1
'" + entity + "'"
Dim iComm As New Command(iCommtype, strQuery)
rsResult =3D iConn.Select(iComm)
tmpData.Add(rsResult.GetResults)
strQuery =3D "EXECUTE " & database & ".dbo.spStockReport2
'" + entity + "'"
rsResult =3D iConn.Select(iComm)
tmpData2.Add(rsResult.GetResults)
'transform to connector format
results =3D dataToSpreadSheet(tmpData, tmpData2,
serverSession)
'Dim headerLine As New ArrayList
'headerLine.Add("Cliente:" & entity & "")
'results.Insert(0, headerLine)
Catch ex As Exception
Log.error("UmGenStockReport: Erro a gerar excel.", ex)
Throw New Exception("Ocorreu um erro a gerar o mapa.
Por favor tente de novo")
End Try
generateData =3D results
End Function
Private Function dataToSpreadSheet(ByVal data As ArrayList,
ByVal data2 As ArrayList, ByVal serverSession As ServerSession) As
ArrayList
Dim result As ArrayList =3D New ArrayList
'construct invoice synopsis
Dim invoiceSynopsis As ArrayList =3D New ArrayList
Dim invoiceMapHeader As ArrayList =3D New ArrayList
invoiceMapHeader.Add("COD ARTIGO")
invoiceMapHeader.Add("ARTIGO")
invoiceMapHeader.Add("DESCRI=C7=C3O")
invoiceMapHeader.Add("DATA DE VALIDADE")
invoiceMapHeader.Add("ESTADO")
invoiceMapHeader.Add("MARCA")
invoiceMapHeader.Add("LOTE")
invoiceMapHeader.Add("ARMAZEM")
invoiceMapHeader.Add("qt disp")
invoiceMapHeader.Add("qt arm")
invoiceMapHeader.Add("qtOk")
invoiceMapHeader.Add("qt Danif")
invoiceMapHeader.Add("qt disp tot")
invoiceMapHeader.Add("qt arm tot")
invoiceMapHeader.Add("qt Ok Total")
invoiceMapHeader.Add("qt Danif Total")
invoiceMapHeader.Add("Total de Lotes :")
invoiceMapHeader.Add("a")
invoiceMapHeader.Add("b")
invoiceMapHeader.Add("Cliente")
invoiceMapHeader.Add("Marca")
invoiceMapHeader.Add("Armazem")
invoiceMapHeader.Add("LotesPorArmazem")
result.Add(invoiceMapHeader)
For Each record As Hashtable In data
Dim invoiceMapLine As ArrayList =3D New ArrayList
If (record.ContainsKey("itemcode")) Then
invoiceMapLine.Add(record("itemcode"))
Else : invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("name")) Then
invoiceMapLine.Add(record("name"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("description")) Then
invoiceMapLine.Add(record("description"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("expirationdate")) Then
invoiceMapLine.Add(record("expirationdate"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("name")) Then
invoiceMapLine.Add(record("name"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("localcode")) Then
invoiceMapLine.Add(record("localcode"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("name")) Then
invoiceMapLine.Add(record("name"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("availableamount")) Then
invoiceMapLine.Add(record("availableamount"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("storderamount")) Then
invoiceMapLine.Add(record("storedamount"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("itemOk")) Then
invoiceMapLine.Add(record("itemOk"))
Else
invoiceMapLine.Add(" ")
End If
If (record.ContainsKey("itemDanif")) Then
invoiceMapLine.Add(record("itemDanif"))
Else
invoiceMapLine.Add(" ")
End If
result.Add(invoiceMapLine)
Next
'add blank line
result.Add(New ArrayList)
Return result
End FunctionSee if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanansql

Export to Mulitple Sheets in Excel

Hi,

I want to export data from SQL to Excel using Data Flow Task. But Excel file can only contains max. of 65,536 rows per sheet. How can I transfer data to multiple sheets if SQL table contains more that 650000 rows.

I am also looking for an answer to this. Very good question....

export to excel: i get blank with the report name?

when i do export to pdf works fine

but when i do export to excel then i dont get any data in the excel sheet... i get blank with the report name?

any idea

thanks

Whats in the report? Data table? Graph? Chart? Matrix report?

|||tabel row and matrix|||sp2 with sql srs 2000|||

Need to look at the RDL to find out what is going on. Could you please send one?

thanks,

Export to Excel Template with pivot table

We have predefined excel tempaltes
I have a excel report with on main worksheet all data then on other work sheet data based on first work sheet with pivot table.
We were generating with old vb6 application.
Now I want's to use SSRS to generate report then export it to excel as my previous template.
Can i do that ?Any idea ?

Export to excel > 65000 rows

Hi
We are trying to export some data from SQL server
into an excel sheet. The data records will be like
more than 1 lakh records. I noted that Excel has
a constraint of not creating rows more than 65,000.
In this case we need to create multiple workbooks
in the excel sheet.

Could you please help me how to creat multiple
workbooks thru DTS in run time - if it is >65,000?

ThanksI don't know of a way to do without a bit of scripting.

You could output all rows to a csv, then run vbscript that uses the excel object to create a new workbook and create multiple sheets (tabs - this is what you mean, right?), iterating through the csv.

Will people actually be looking at the data, row after row in excel? Why not use an access database? Excel wasn't designed for such large amounts of data, so using it for such a thing might not be a good idea in the first place.

Eric|||Why does it have to Excel?

Is someone going to look at the Data in Excel?

Who's gonna look at multiple tabs of 65,000 rows?

That's what the database is for

What are you ultimatley trying to do?

Export to Excel - Why a blank Sheet 1 with data on Sheet 2?

I'm wondering what I might have done in my report definition to cause this.
When I export a couple of my reports to Excel, it ends up on Sheet 2, while
sheet 1 is a blank spreadsheet. Can anyone point me at what I need to look
into? oh. I've got other reports that seem to export just fine.
Thanks
Rob MillerHave you tried to export to PDF? Does is go to a 2nd page?
If so, your margins or body size are incorrect.
Immy
"Rob Miller" <robert.miller@.acoa-apeca.gc.ca> wrote in message
news:eKKuiCsEFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I'm wondering what I might have done in my report definition to cause
> this. When I export a couple of my reports to Excel, it ends up on Sheet
> 2, while sheet 1 is a blank spreadsheet. Can anyone point me at what I
> need to look into? oh. I've got other reports that seem to export just
> fine.
> Thanks
> Rob Miller
>|||Do you have any document map entries? They would create a title page. Also,
if you have explicit page breaks in your report they will show up as
multiple workbooks.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Miller" <robert.miller@.acoa-apeca.gc.ca> wrote in message
news:eKKuiCsEFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I'm wondering what I might have done in my report definition to cause
> this. When I export a couple of my reports to Excel, it ends up on Sheet
> 2, while sheet 1 is a blank spreadsheet. Can anyone point me at what I
> need to look into? oh. I've got other reports that seem to export just
> fine.
> Thanks
> Rob Miller
>|||Rob,
You've got something causing a page break. When exporting to Excel it
treats page breaks as new sheets. Check the setting of the
'PageBreakAtStart' property for any data regions you've got. I suspect
one is set to 'True'.
Regards
Chris
Rob Miller wrote:
> I'm wondering what I might have done in my report definition to
> cause this. When I export a couple of my reports to Excel, it ends
> up on Sheet 2, while sheet 1 is a blank spreadsheet. Can anyone
> point me at what I need to look into? oh. I've got other reports
> that seem to export just fine.
> Thanks
> Rob Miller

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com

export sql query result to excel spread sheet

Hi,,
I need to export a sql query result to a excel sheet. I can't use DTS. Any
simple t-sql code to do this?
ThanksHI
see this link
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uLo2uU0tHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Hi,,
> I need to export a sql query result to a excel sheet. I can't use DTS. Any
> simple t-sql code to do this?
> Thanks
>
>|||it works,
Thanks a lot!!!!~
\\
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:u52BIE1tHHA.1728@.TK2MSFTNGP06.phx.gbl...
> HI
> see this link
> http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
> regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uLo2uU0tHHA.3944@.TK2MSFTNGP06.phx.gbl...
>> Hi,,
>> I need to export a sql query result to a excel sheet. I can't use DTS.
>> Any simple t-sql code to do this?
>> Thanks
>>
>|||Try www.sqlscripter.com to export your query to Excel.
It's free.
"mecn" wrote:
> Hi,,
> I need to export a sql query result to a excel sheet. I can't use DTS. Any
> simple t-sql code to do this?
> Thanks
>
>

export sql query result to excel spread sheet

Hi,,
I need to export a sql query result to a excel sheet. I can't use DTS. Any
simple t-sql code to do this?
ThanksHI
see this link
http://weblogs.sqlteam.com/mladenp/...7/25/10771.aspx
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uLo2uU0tHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Hi,,
> I need to export a sql query result to a excel sheet. I can't use DTS. Any
> simple t-sql code to do this?
> Thanks
>
>|||it works,
Thanks a lot!!!!~
\\
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:u52BIE1tHHA.1728@.TK2MSFTNGP06.phx.gbl...
> HI
> see this link
> http://weblogs.sqlteam.com/mladenp/...7/25/10771.aspx
> regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uLo2uU0tHHA.3944@.TK2MSFTNGP06.phx.gbl...
>

export sql query result to excel spread sheet

Hi,,
I need to export a sql query result to a excel sheet. I can't use DTS. Any
simple t-sql code to do this?
Thanks
it works,
Thanks a lot!!!!~
\\
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:u52BIE1tHHA.1728@.TK2MSFTNGP06.phx.gbl...
> HI
> see this link
> http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
> regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uLo2uU0tHHA.3944@.TK2MSFTNGP06.phx.gbl...
>

Export sql query result to excel soread sheet

I couldn't get the field titles on the Excel and for the datetime field it
only shows time not date & time.
Thanks
Check the cell format of the Excel spreadsheet to see is it set on "Date >
mm/dd/yy hh:mm" format or not
"Catelin wang" wrote:

> I couldn't get the field titles on the Excel and for the datetime field it
> only shows time not date & time.
> Thanks
>
>

Export sql query result to excel soread sheet

I couldn't get the field titles on the Excel and for the datetime field it
only shows time not date & time.
ThanksCheck the cell format of the Excel spreadsheet to see is it set on "Date >
mm/dd/yy hh:mm" format or not
"Catelin wang" wrote:

> I couldn't get the field titles on the Excel and for the datetime field it
> only shows time not date & time.
> Thanks
>
>

Export sql query result to excel soread sheet

I couldn't get the field titles on the Excel and for the datetime field it
only shows time not date & time.
ThanksCheck the cell format of the Excel spreadsheet to see is it set on "Date >
mm/dd/yy hh:mm" format or not
"Catelin wang" wrote:
> I couldn't get the field titles on the Excel and for the datetime field it
> only shows time not date & time.
> Thanks
>
>