Replace column value with MAX() query in dataflow before insert

Hi,

We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.

Just to be clear:
column id (int)
column name (string)

Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table

Which transformation do I use to replace the value of the id column with this new id?

thanks,

Stephane

Hi Stephane,

You would first need to get the maximum value of the id column into another column (called maxid, for the purposes of this example). You could do this with a script component, or by splitting the flow and using an Aggregate transform,and merging it back. Then, you can then use a derived column transform to replace the value of the column by setting a replace expression such as the following:

ISNULL(id) ? maxid + 1 : id

Mark

|||

Hi Mark,

Thanks for your reply.

Your solution would work if all the data was in the dataflow, which unfortunately it isn't for me (we iterate over excel files with one row per file). We now do a select max() ... at the start of the control flow and store it in a variable. When we detect a new row, we increment the value and use a derived column transform like you suggested as well to put the value in the column.

Thanks,

Stephane

sql

Repeating Rows

Is there any way by which we can force to repeat the column headers to repeat
if the page changes. Like in EXCEL where we can mention those rows that need
to be repeat with the new pages.
ThanksClick on the header of the table (far left), there is a property called
repeatonnewpage that you set to true.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:7DD34123-91DC-44CD-BB57-9A2C018292AC@.microsoft.com...
> Is there any way by which we can force to repeat the column headers to
repeat
> if the page changes. Like in EXCEL where we can mention those rows that
need
> to be repeat with the new pages.
> Thanks
>|||Thanks! This is a matrix and when i go in properties of the particular FIELD
(Months), there is an option (ADVANCED TAB) which says
"REPEAT REPORT ITEM WITH DATA REGION ON EVERY PAGE" and then you can pick up
the specific data region from drop down. The problem here is that this option
is not available for me and couldnt be chosen.
"Bruce L-C [MVP]" wrote:
> Click on the header of the table (far left), there is a property called
> repeatonnewpage that you set to true.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Asim" <Asim@.discussions.microsoft.com> wrote in message
> news:7DD34123-91DC-44CD-BB57-9A2C018292AC@.microsoft.com...
> > Is there any way by which we can force to repeat the column headers to
> repeat
> > if the page changes. Like in EXCEL where we can mention those rows that
> need
> > to be repeat with the new pages.
> > Thanks
> >
>
>|||Sorry, can't help you with the matrix problem you are having.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:AF07B84C-4D5A-4032-9BD5-014A795A6F6B@.microsoft.com...
> Thanks! This is a matrix and when i go in properties of the particular
FIELD
> (Months), there is an option (ADVANCED TAB) which says
> "REPEAT REPORT ITEM WITH DATA REGION ON EVERY PAGE" and then you can pick
up
> the specific data region from drop down. The problem here is that this
option
> is not available for me and couldnt be chosen.
>
> "Bruce L-C [MVP]" wrote:
> > Click on the header of the table (far left), there is a property called
> > repeatonnewpage that you set to true.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Asim" <Asim@.discussions.microsoft.com> wrote in message
> > news:7DD34123-91DC-44CD-BB57-9A2C018292AC@.microsoft.com...
> > > Is there any way by which we can force to repeat the column headers to
> > repeat
> > > if the page changes. Like in EXCEL where we can mention those rows
that
> > need
> > > to be repeat with the new pages.
> > > Thanks
> > >
> >
> >
> >

Repeating attribute member in Excel 2007

I'm accessing SSAS 2005 cube with Excel 2007. In the pivot table, let's say that I pull premium by policy year, policy month.

The display in the pivot table would be like the following.

policy year policy month premium

2007 Jan 2,000,000

Feb 1,000,000

Mar 1,500,000

How can I repeat 2007 in the pivot table so that it would look like the following?

policy year policy month premium

2007 Jan 2,000,000

2007 Feb 1,000,000

2007 Mar 1,500,000

By repeating the attribute member in Excel 2007, my user can easily export the pivot table into a data set.

Mitch

One way to solve this is to make a SSRS2005 report with the SSAS2005 cube as a data source.

In Reporting Service you can control if a group member name should be repeated for each child or not.

Users can export this report to Excel.

I have not seen a solution in Excel 2007 pivot tables, more than allowing drill-through actions on cells, and let users get the leaf transactions behind that cell value.

HTH

Thomas Ivarsson

|||

Thomas,

Thanks for the feedback. For me, SSRS is not an option because my users want to create these data sets on the fly within Pivot table. I'll just let them know the feature is not there yet for now.

Mitch

Rendering URL in EXCEL format

Hi Everyone,
I am trying to diaply my report on the web form by pasting the URL
in ReportViewer1.ReportPath = " " inside the codes. My URL is
/web
Test/FirstReport&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false
It is working fine and diaplying the report, but when I change the
Format to Excel. It does not display the report properly. It gives me
an error that report cannot be found. Same thing with PDF, when I
change the format to PDF. IT gives me the same error.
"There was error opening the file. File does not exist". In excel
format if I save the file. The file is saved but when I want to open
the file from save/open dialog box it does not display properly.
Can anyone please let me know what the problem is.
Thanks,

Hi,

Excel rendering has been a problem since the Reporting Services is used.

With the retail version some versions was not supported. SP1 added some new supported versions. And SP2 is adding more.

You may be running into such a problem.

SP2 will support Excel 97 and later versions.

SP1 supports Excel 10 (Office 2002) or later

Eralper

http://www.kodyaz.com

Rendering true Excel

My users are complaining that the Excel files they are getting back from
the web application's rendering of reports in Excel mode are not true
Excel, but rather still retain HTML. Has anyone else experienced this?
I have included my rendering code snippet below:
--
try
{
data = render.RunReport(ConfigurationSettings.AppSettings[ePledgeConstants.REPORT_SERVER_URL_PROPERTY],
fullReportName, reportParameters, format, out
encoding,
out mimeType, out parametersUsed, out warnings,
out streamIds);
Response.Clear();
Response.ContentType = mimeType;
string fileName = report.Name +
GetFileExtension(mimeType);
if (mimeType != "text/html")
{
Response.AddHeader("Content-Disposition",
"attachment; filename=" + fileName);
}
switch(encoding)
{
case "Unicode (UTF-8)":
Response.ContentEncoding = new UTF8Encoding();
break;
case "Unicode (UTF-7)":
Response.ContentEncoding = new UTF7Encoding();
break;
default:
Response.ContentEncoding = new UTF8Encoding();
break;
}
Response.BinaryWrite(data);
}
catch (Exception exception)
{
Console.Out.Write(exception.ToString());
}
---
The run report method in turn is like this:
---
public byte[] RunReport(string ServerUrl, string ReportName,
ParameterValue[] parameters, string Format, out string encoding, out
string mimeType, out ParameterValue[] parametersUsed, out Warning[]
warnings, out string[] streamIds)
{
ReportingService rs = new ReportingService();
rs.Timeout = -1;
rs.Url = ServerUrl;
rs.Credentials = new NetworkCredential(UserName, Password);
return rs.Render(ReportName, Format, null, null, parameters, null,
null, out encoding, out mimeType, out parametersUsed, out warnings,
out streamIds);
}Have you deployed Reporting Services SP1. In SP1, we output native XLS.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryon" <blape@.whittmanhart.com> wrote in message
news:OVl$MrGGFHA.2932@.TK2MSFTNGP15.phx.gbl...
> My users are complaining that the Excel files they are getting back from
> the web application's rendering of reports in Excel mode are not true
> Excel, but rather still retain HTML. Has anyone else experienced this? I
> have included my rendering code snippet below:
> --
> try
> {
> data => render.RunReport(ConfigurationSettings.AppSettings[ePledgeConstants.REPORT_SERVER_URL_PROPERTY],
> fullReportName, reportParameters, format, out
> encoding,
> out mimeType, out parametersUsed, out warnings, out
> streamIds);
> Response.Clear();
> Response.ContentType = mimeType;
> string fileName = report.Name +
> GetFileExtension(mimeType);
> if (mimeType != "text/html")
> {
> Response.AddHeader("Content-Disposition",
> "attachment; filename=" + fileName);
> }
> switch(encoding)
> {
> case "Unicode (UTF-8)":
> Response.ContentEncoding = new UTF8Encoding();
> break;
> case "Unicode (UTF-7)":
> Response.ContentEncoding = new UTF7Encoding();
> break;
> default:
> Response.ContentEncoding = new UTF8Encoding();
> break;
> }
> Response.BinaryWrite(data);
> }
> catch (Exception exception)
> {
> Console.Out.Write(exception.ToString());
> }
> ---
> The run report method in turn is like this:
> ---
> public byte[] RunReport(string ServerUrl, string ReportName,
> ParameterValue[] parameters, string Format, out string encoding, out
> string mimeType, out ParameterValue[] parametersUsed, out Warning[]
> warnings, out string[] streamIds)
> {
> ReportingService rs = new ReportingService();
> rs.Timeout = -1;
> rs.Url = ServerUrl;
> rs.Credentials = new NetworkCredential(UserName, Password);
> return rs.Render(ReportName, Format, null, null, parameters, null,
> null, out encoding, out mimeType, out parametersUsed, out warnings, out
> streamIds);
> }|||Lukasz Pawlowski [MSFT] wrote:
> Have you deployed Reporting Services SP1. In SP1, we output native XLS.
> -Lukasz
>
Yes I have.
An interesting side note to this problem is that on a Mac, it only sees
the HTML, never the Excel information. In Windows, the file looks like
Excel, but does not save as Excel.
Also, is there a way to display gridlines? The Excel comes back as
Excel for the web and does not have gridlines by default.|||Grid lines are based on how you designed your report. If you created for
example a table with inner boarders, you should see grid lines.
Are you supplying any device info parameters to your excel rendering?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryon" <blape@.whittmanhart.com> wrote in message
news:%23ynRKTHGFHA.128@.TK2MSFTNGP14.phx.gbl...
> Lukasz Pawlowski [MSFT] wrote:
>> Have you deployed Reporting Services SP1. In SP1, we output native XLS.
>> -Lukasz
>>
> Yes I have.
> An interesting side note to this problem is that on a Mac, it only sees
> the HTML, never the Excel information. In Windows, the file looks like
> Excel, but does not save as Excel.
> Also, is there a way to display gridlines? The Excel comes back as Excel
> for the web and does not have gridlines by default.|||Lukasz Pawlowski [MSFT] wrote:
> Grid lines are based on how you designed your report. If you created for
> example a table with inner boarders, you should see grid lines.
> Are you supplying any device info parameters to your excel rendering?
> -Lukasz
>
I'm not sure what you mean by device parameters.

rendering to multiple Excel Workbooks from one Report in Reporting Services 2005

Hi Folks,

Is there a way in Reporting Services 2005 to render 1 report into multiple Excel Workbooks?

The report we have already renders to multiple Excel "pages" from the Report, but we need to find out if we can also break and render into a NEW Workbook, based on Groups, for example.

Thanks in advance for any help.

Glen Plantz

Sorry, it's not possible to export one report to multiple workbooks.

You could possibly achieve this by requesting multiple exports with each one receiving different parameter values.

-chris

rendering to multiple Excel Workbooks from one Report in Reporting Services 2005

Hi Folks,

Is there a way in Reporting Services 2005 to render 1 report into multiple Excel Workbooks?

The report we have already renders to multiple Excel "pages" from the Report, but we need to find out if we can also break and render into a NEW Workbook, based on Groups, for example.

Thanks in advance for any help.

Glen Plantz

Sorry, it's not possible to export one report to multiple workbooks.

You could possibly achieve this by requesting multiple exports with each one receiving different parameter values.

-chris

Rendering to Excel: numbers stored as text

Some of my reports are rendering to Excel with numbers stored as text (the
little green triangle in the upper corner of each cell alerts the viewer of
the problem).
While this seems innocuous, it means that a user can't do typical Excel
operations, like highlighting cells and creating a graph, or highlighing and
viewing the totals on the status bar. Although there's a number in the cell,
it acts like text.
Not all of my reports are doing this, but I'm having major trouble figuring
out the differences between those which are, and those which are not. Also,
I can't find any documentation about how the rendering makes this
determination.
Thank you!It appears I've finally found the solution. Since I knew I wasn't *really*
aggregating any values, I was using First() as my aggregation formula. When
I switched to Sum() as my aggregation, the result was forced to be numeric,
even through the Excel rendering.
How was anyone supposed to figure this out without documentation!? I mean,
if the fundamental data is numeric, then why should First( ) change it to
text? That seems SO arbitrary...!
I hope this helps someone to NOT pull their hair out like I did.
"mr_bert" wrote:
> Some of my reports are rendering to Excel with numbers stored as text (the
> little green triangle in the upper corner of each cell alerts the viewer of
> the problem).
> While this seems innocuous, it means that a user can't do typical Excel
> operations, like highlighting cells and creating a graph, or highlighing and
> viewing the totals on the status bar. Although there's a number in the cell,
> it acts like text.
> Not all of my reports are doing this, but I'm having major trouble figuring
> out the differences between those which are, and those which are not. Also,
> I can't find any documentation about how the rendering makes this
> determination.
> Thank you!|||Thanks
This just saved me a day of pulling my hair out

Rendering to Excel, multiple sheets

I am rendering a report from RS that has multiple tables. After each table I
have it setup to have a page break. This causes each table to be written to
a different sheet within the excel workbook. This is fine. What I would
like to know is if there is a way to write the table name to the excel
workbook tab when the report is rendered instead of having the tabs show
sheet1, sheet2, etc. DOes anyone know how or if this can be done?
Thanks for your help,
sOn Oct 11, 10:24 am, Susan R <Sus...@.discussions.microsoft.com> wrote:
> I am rendering a report from RS that has multiple tables. After each table I
> have it setup to have a page break. This causes each table to be written to
> a different sheet within the excel workbook. This is fine. What I would
> like to know is if there is a way to write the table name to the excel
> workbook tab when the report is rendered instead of having the tabs show
> sheet1, sheet2, etc. DOes anyone know how or if this can be done?
> Thanks for your help,
> s
Several people have asked this same question on these forums w/pretty
much the same response: this is not currently possible in SSRS. Sorry
that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
I never tried it my myself, but facing the same problem I found
http://www.spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx
Seems to do the job, but is a little tricky to setup and works only for
excel 2003 and newer.
Simon
"Susan R" wrote:
> I am rendering a report from RS that has multiple tables. After each table I
> have it setup to have a page break. This causes each table to be written to
> a different sheet within the excel workbook. This is fine. What I would
> like to know is if there is a way to write the table name to the excel
> workbook tab when the report is rendered instead of having the tabs show
> sheet1, sheet2, etc. DOes anyone know how or if this can be done?
> Thanks for your help,
> s

Rendering to Excel - Crash - Server Application Unavailable - Out of RAM

I have a very simple report which generates a large amount or rows -
63,000. When I run the report all goes to plan and the data it
brought back and I can page through its 1336 returned pages. But I
need to be able to export all of this data ro excel, but when I try it
works away for a bit before eventually "crashing" and returning -
"
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web
browser to retry your request.
Administrator Note: An error message detailing the cause of this
specific request failure can be found in the application event log of
the web server. Please review this log entry to discover what caused
this error to occur.
"
In the loogs it says -
aspnet_wp.exe (PID: 2352) was recycled because memory consumption
exceeded the 306 MB (60 percent of available RAM).
yet I can go to Excel and go Data - Import External Data - New
Database Query and run the sql from there and export it to excel and
it works fine, and pretty quickly.
Is there anyway around this' I need to find someway to be able to
render my report from my reporting application in Excel for clients...
ThanksUnfortunately Reporting Services was designed to handle very big reports
since it renders 'in memory'. As you've seen from your error the server is
running out of memory. You could try to redesign the report into smaller
reports or try adding more memory on the server.
--
Adrian M.
MCP
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:3d6ebe80.0503300120.1b92ac6f@.posting.google.com...
>I have a very simple report which generates a large amount or rows -
> 63,000. When I run the report all goes to plan and the data it
> brought back and I can page through its 1336 returned pages. But I
> need to be able to export all of this data ro excel, but when I try it
> works away for a bit before eventually "crashing" and returning -
> "
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this
> specific request failure can be found in the application event log of
> the web server. Please review this log entry to discover what caused
> this error to occur.
> "
> In the loogs it says -
> aspnet_wp.exe (PID: 2352) was recycled because memory consumption
> exceeded the 306 MB (60 percent of available RAM).
> yet I can go to Excel and go Data - Import External Data - New
> Database Query and run the sql from there and export it to excel and
> it works fine, and pretty quickly.
> Is there anyway around this' I need to find someway to be able to
> render my report from my reporting application in Excel for clients...
> Thanks|||> Unfortunately Reporting Services wasn't designed to handle very big
> reports since it renders 'in memory'.
--
Adrian M.
MCP
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uClX4mSNFHA.2468@.tk2msftngp13.phx.gbl...
> Unfortunately Reporting Services was designed to handle very big reports
> since it renders 'in memory'. As you've seen from your error the server
> is running out of memory. You could try to redesign the report into
> smaller reports or try adding more memory on the server.
> --
> Adrian M.
> MCP
> "Gearoid" <gearoid_healy@.yahoo.com> wrote in message
> news:3d6ebe80.0503300120.1b92ac6f@.posting.google.com...
>>I have a very simple report which generates a large amount or rows -
>> 63,000. When I run the report all goes to plan and the data it
>> brought back and I can page through its 1336 returned pages. But I
>> need to be able to export all of this data ro excel, but when I try it
>> works away for a bit before eventually "crashing" and returning -
>> "
>> Server Application Unavailable
>> The web application you are attempting to access on this web server is
>> currently unavailable. Please hit the "Refresh" button in your web
>> browser to retry your request.
>> Administrator Note: An error message detailing the cause of this
>> specific request failure can be found in the application event log of
>> the web server. Please review this log entry to discover what caused
>> this error to occur.
>> "
>> In the loogs it says -
>> aspnet_wp.exe (PID: 2352) was recycled because memory consumption
>> exceeded the 306 MB (60 percent of available RAM).
>> yet I can go to Excel and go Data - Import External Data - New
>> Database Query and run the sql from there and export it to excel and
>> it works fine, and pretty quickly.
>> Is there anyway around this' I need to find someway to be able to
>> render my report from my reporting application in Excel for clients...
>> Thanks
>|||Adrian M. wrote:
> Unfortunately Reporting Services was designed to handle very big reports
> since it renders 'in memory'. As you've seen from your error the server is
> running out of memory. You could try to redesign the report into smaller
> reports or try adding more memory on the server.
>
"try adding more memory on the server" - COOL! Worst recomendation ever
seen! How about to redesign RS? Why I don't have such problems in "old
fashion" developing (pre-dotnet)?
For example, I have some data. If I'll export this data as CSV then it
tools 10 MB. But then I'm truing to export same data as Excel - server
dies and tooks 400MB of RAM. My personal "reporter" tooks much less
resources! I this normal? Is this problem with my server?
I think - not. This is problem of R$ and M$.
How I can use RS for ENTERPRISE reporting if it cannot work with large
amounts of data? Only with tiny data (1000 records or less).
Also RS has huge memory management issues. How about to give away unused
memory back to system? Where is magical GC?|||I actually have some suggestions for you to try, but after your rant I have
decided not to share them with you.
--
Adrian M.
MCP
"Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> Adrian M. wrote:
>> Unfortunately Reporting Services was designed to handle very big reports
>> since it renders 'in memory'. As you've seen from your error the server
>> is running out of memory. You could try to redesign the report into
>> smaller reports or try adding more memory on the server.
> "try adding more memory on the server" - COOL! Worst recomendation ever
> seen! How about to redesign RS? Why I don't have such problems in "old
> fashion" developing (pre-dotnet)?
> For example, I have some data. If I'll export this data as CSV then it
> tools 10 MB. But then I'm truing to export same data as Excel - server
> dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> resources! I this normal? Is this problem with my server?
> I think - not. This is problem of R$ and M$.
> How I can use RS for ENTERPRISE reporting if it cannot work with large
> amounts of data? Only with tiny data (1000 records or less).
> Also RS has huge memory management issues. How about to give away unused
> memory back to system? Where is magical GC?|||Adrian,
I have encountered the same issues with Reporting Services when over 90,000
records are being returned to a report. I am using a dedicated server with
2gb of RAM. We are currently getting an extra 2gb to assist in the load, but
I was wondering what your other options were other than reducing the criteria
of the report.
Any help you may be able to provide would be greatly appreciated.
"Adrian M." wrote:
> I actually have some suggestions for you to try, but after your rant I have
> decided not to share them with you.
> --
> Adrian M.
> MCP
>
> "Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
> news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> > Adrian M. wrote:
> >> Unfortunately Reporting Services was designed to handle very big reports
> >> since it renders 'in memory'. As you've seen from your error the server
> >> is running out of memory. You could try to redesign the report into
> >> smaller reports or try adding more memory on the server.
> >>
> >
> > "try adding more memory on the server" - COOL! Worst recomendation ever
> > seen! How about to redesign RS? Why I don't have such problems in "old
> > fashion" developing (pre-dotnet)?
> >
> > For example, I have some data. If I'll export this data as CSV then it
> > tools 10 MB. But then I'm truing to export same data as Excel - server
> > dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> > resources! I this normal? Is this problem with my server?
> > I think - not. This is problem of R$ and M$.
> >
> > How I can use RS for ENTERPRISE reporting if it cannot work with large
> > amounts of data? Only with tiny data (1000 records or less).
> >
> > Also RS has huge memory management issues. How about to give away unused
> > memory back to system? Where is magical GC?
>
>|||See my response to your other post.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:63F7E982-CCE1-43E7-A077-DB8B4240D35C@.microsoft.com...
> Adrian,
> I have encountered the same issues with Reporting Services when over
90,000
> records are being returned to a report. I am using a dedicated server
with
> 2gb of RAM. We are currently getting an extra 2gb to assist in the load,
but
> I was wondering what your other options were other than reducing the
criteria
> of the report.
> Any help you may be able to provide would be greatly appreciated.
> "Adrian M." wrote:
> > I actually have some suggestions for you to try, but after your rant I
have
> > decided not to share them with you.
> >
> > --
> > Adrian M.
> > MCP
> >
> >
> > "Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
> > news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> > > Adrian M. wrote:
> > >> Unfortunately Reporting Services was designed to handle very big
reports
> > >> since it renders 'in memory'. As you've seen from your error the
server
> > >> is running out of memory. You could try to redesign the report into
> > >> smaller reports or try adding more memory on the server.
> > >>
> > >
> > > "try adding more memory on the server" - COOL! Worst recomendation
ever
> > > seen! How about to redesign RS? Why I don't have such problems in "old
> > > fashion" developing (pre-dotnet)?
> > >
> > > For example, I have some data. If I'll export this data as CSV then it
> > > tools 10 MB. But then I'm truing to export same data as Excel - server
> > > dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> > > resources! I this normal? Is this problem with my server?
> > > I think - not. This is problem of R$ and M$.
> > >
> > > How I can use RS for ENTERPRISE reporting if it cannot work with large
> > > amounts of data? Only with tiny data (1000 records or less).
> > >
> > > Also RS has huge memory management issues. How about to give away
unused
> > > memory back to system? Where is magical GC?
> >
> >
> >

rendering to browser instead to file

Did anyone have rendered a report in excel format directly to browser
instead to a file? I tried this code below wich works fine for pdf
rendering:
result = rs.Render("/MyReports/SVREL00104", "Excel", Nothing, _
Nothing, params, Nothing, _
Nothing, encoding, mime, history, warnings, streamsId)
Page.Response.ClearContent()
Page.Response.ClearHeaders()
Page.Response.ContentType = mime
Page.Response.BinaryWrite(result)
Page.Response.Flush()
Page.Response.Close()
This code should load the excel into the web browser window, but I'm getting
this errors:
1. A message box saying: The Microsoft Excel can not access the file:
'http://localhost/MyReports/test.aspx'.
2. An input box asking for an alternate name for 'Titulos_de_Impressao'
(Print Title) because this is already an internal name.
Any ideas?
[]s
Renato
--
----
Renato Aloi
Analista Programador
J&W Informática Ltda.
+55 11 30406675If you can tolerate a dialog box asking if the user wants to open or save,
this will work:
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition",
"attachment;filename=\"MyReport.xls\"");
Response.BinaryWrite(result);
Response.End();
It works for PDF as well. There may be other ways, but this worked for us
so we stuck with it.
"Renato Aloi" wrote:
> Did anyone have rendered a report in excel format directly to browser
> instead to a file? I tried this code below wich works fine for pdf
> rendering:
> result = rs.Render("/MyReports/SVREL00104", "Excel", Nothing, _
> Nothing, params, Nothing, _
> Nothing, encoding, mime, history, warnings, streamsId)
> Page.Response.ClearContent()
> Page.Response.ClearHeaders()
> Page.Response.ContentType = mime
> Page.Response.BinaryWrite(result)
> Page.Response.Flush()
> Page.Response.Close()
> This code should load the excel into the web browser window, but I'm getting
> this errors:
> 1. A message box saying: The Microsoft Excel can not access the file:
> 'http://localhost/MyReports/test.aspx'.
> 2. An input box asking for an alternate name for 'Titulos_de_Impressao'
> (Print Title) because this is already an internal name.
> Any ideas?
> []s
> Renato
> --
> ----
> Renato Aloi
> Analista Programador
> J&W Informática Ltda.
> +55 11 30406675
>
>|||Thanks for your idea, Debra. Though I was really trying to avoid these
dialog boxes. In fact I am 'translating' all my systems' reports from
Crystal Reports to Reporting Services. The problem is my clients are already
using my systems with this export option to excel, without needing to save
any file. If I barelly whisper that they will need to take more clicks to do
something that is already working well, I will be exterminated.
However, I think the approach i will adopt is save a file to disk then use
this code:
Page.Response.ClearContent()
Page.Response.ClearHeaders()
Page.Response.ContentType = mime
Page.Response.WriteFile(savedFile)
Page.Response.Flush()
Page.Response.Close()
This approach works fine without any dialog boxes. The problem is at my
clients using web farms with load balance. I am afraid of exceptions, like
file not found.
Any other ideas, let me know, please.
[]s
Renato
"debra doty" <debradoty@.discussions.microsoft.com> escreveu na mensagem
news:22D7671D-13F2-413F-84B8-022E222F3A4C@.microsoft.com...
> If you can tolerate a dialog box asking if the user wants to open or save,
> this will work:
> Response.ClearContent();
> Response.ClearHeaders();
> Response.AppendHeader("Content-Disposition",
> "attachment;filename=\"MyReport.xls\"");
> Response.BinaryWrite(result);
> Response.End();
> It works for PDF as well. There may be other ways, but this worked for us
> so we stuck with it.
>
> "Renato Aloi" wrote:
> > Did anyone have rendered a report in excel format directly to browser
> > instead to a file? I tried this code below wich works fine for pdf
> > rendering:
> >
> > result = rs.Render("/MyReports/SVREL00104", "Excel", Nothing, _
> > Nothing, params, Nothing, _
> > Nothing, encoding, mime, history, warnings, streamsId)
> >
> > Page.Response.ClearContent()
> > Page.Response.ClearHeaders()
> > Page.Response.ContentType = mime
> > Page.Response.BinaryWrite(result)
> > Page.Response.Flush()
> > Page.Response.Close()
> >
> > This code should load the excel into the web browser window, but I'm
getting
> > this errors:
> >
> > 1. A message box saying: The Microsoft Excel can not access the file:
> > 'http://localhost/MyReports/test.aspx'.
> >
> > 2. An input box asking for an alternate name for 'Titulos_de_Impressao'
> > (Print Title) because this is already an internal name.
> >
> > Any ideas?
> >
> > []s
> > Renato
> >
> > --
> > ----
> > Renato Aloi
> > Analista Programador
> > J&W Informática Ltda.
> > +55 11 30406675
> >
> >
> >

Rendering reports to Word format

Currently my company inserts graphs from Excel into a Word document. They
then update the Word document with explanations about their analysis.
The requirement is for the graphs in this Word document to come from
Reporting Services 2000. Analysts then need to be able to update the text in
the document.
The SoftArtisans product appears to provide the required functionality,
however, my company has very strict guidelines about introducing additional
software into the organisation.
Do you have any ideas about how to render the Word document, including
graphs and sub-reports using Reporting Services 2000?
My thanks in advanceI understand your dilemma, but I strongly suggest you attempt to leverage
commercial software that already does the job before trying to roll your
own.
If you have no other choice, get your hands on Office 2003 Interop
Assemblies. You can then render the report(s) through web service calls
(take a look at the Render() method). There are other methods that allow
you to get a list of the image streams for the report. You'll need to get
to the image streams and pull the chart image from the web service. Then
you can write the image(s) to the word document. This is one way to do it.
-Tim
"SuzyM" <SuzyM@.discussions.microsoft.com> wrote in message
news:5FA762D2-BAE6-4471-A28A-F01B3D0FA44E@.microsoft.com...
> Currently my company inserts graphs from Excel into a Word document. They
> then update the Word document with explanations about their analysis.
> The requirement is for the graphs in this Word document to come from
> Reporting Services 2000. Analysts then need to be able to update the text
> in
> the document.
> The SoftArtisans product appears to provide the required functionality,
> however, my company has very strict guidelines about introducing
> additional
> software into the organisation.
> Do you have any ideas about how to render the Word document, including
> graphs and sub-reports using Reporting Services 2000?
> My thanks in advance

Rendering Report directly in the MS Excel

Hi,
We have multiple reports created in the SRS 2005. What is the best approach
to directly exporting (rendering) the created reports in Excel without using
any report viewer control or report manager for exporting on click of the
button.
Thanx in advance.You can call the report using the url access, as:
http://<server>/ReportServer?/<report
path>&rs:Command=Render&rs:Format=EXCEL
Jeronimo Vogt
"Parimal" <Parimal@.discussions.microsoft.com> wrote in message
news:3761A0CF-EFA4-4266-8E15-4832D6AF3814@.microsoft.com...
> Hi,
> We have multiple reports created in the SRS 2005. What is the best
approach
> to directly exporting (rendering) the created reports in Excel without
using
> any report viewer control or report manager for exporting on click of the
> button.
> Thanx in advance.|||The new MS Reports tool (an addition to Excel 2003) is worth looking at - it
might meet your requirements. I think it is supposed to be released sometime
in Dec 2005 - anyone has any confirmed information on the release date?
For details on MS Reports:
http://download.microsoft.com/download/d/6/f/d6f66e9b-1b72-4192-b4ae-cecbad1a4903/MSReportsTCS.doc
-K
"Jeronimo Vogt" wrote:
> You can call the report using the url access, as:
> http://<server>/ReportServer?/<report
> path>&rs:Command=Render&rs:Format=EXCEL
> Jeronimo Vogt
> "Parimal" <Parimal@.discussions.microsoft.com> wrote in message
> news:3761A0CF-EFA4-4266-8E15-4832D6AF3814@.microsoft.com...
> > Hi,
> > We have multiple reports created in the SRS 2005. What is the best
> approach
> > to directly exporting (rendering) the created reports in Excel without
> using
> > any report viewer control or report manager for exporting on click of the
> > button.
> > Thanx in advance.
>
>

Rendering PDF missing pages

i had rendered a report and created a excel file and each page of the 2-page
report generate a sheet in the workbook, which is great now i rendered the
same report in PDF, the PDF only generates one page instead of the expected 2
pages. What am I doing wrong?
Please help.
FYI. Using C# in a window form.Hi Nkem
I hade the same problem, but solved with service pack1 or SP2
"Nkem" wrote:
> i had rendered a report and created a excel file and each page of the 2-page
> report generate a sheet in the workbook, which is great now i rendered the
> same report in PDF, the PDF only generates one page instead of the expected 2
> pages. What am I doing wrong?
> Please help.
> FYI. Using C# in a window form.