I am using RGB HEX #s in my report for some colors. It shows up fine when rendering to a browser, however when exporting to Excel my custom colors don't come over. I've tried creating a report.xlt file with my custom colors in it, but when I export to Excel it uses the standard Excel palette instead of the custom palette in my report.xlt.
Any ideas on how to retain my custom colors when exporting to Excel?
Exporting a report to Excel has a lot of issues with appearance. As a result, I created an Excel template that I would like to use when the user selects to export the report to Excel, using the reportViewer toolbar. I haven't found any documentation thus far on how to accomplish this. Does anyone know whether this is possible with SSRS and if so can you please provide information as to how it is accomplished. I have created a similar post where my application code can be reviewed: http://forums.asp.net/t/1183977.aspx
Any help is appreciated.
I need some help regarding Custom Colors implementation for Charts in the Reporting Services.
What we are trying to do is to make every color for every Series(see example below) to be a customized Corporate color.
For example each of the series on the Chart should be in specific Custom Color:
Series 1 - "LightBlue"
Series 2 - "LightYellow"
Series 3 - "DarkBrown"
All I found from MSDN is how to do it in the Chart Properties in a Code section (right click on the report outside the chart -> Properties -> Code tab -> paste the Custom Code ).
And then to use it in the Chart color definition as:
Code Snippet=Code.GetColor( Series# )
But we are trying to do it in a separate C# class that would be updated only in one place, instead of changing 30 reports each time something is changed. So that it could be generic.
The C# class:
Code Snippet
namespace CustomColorPalette
{
public class CustomPalette
{
public CustomPalette()
{
}
public static string GetColor(string GroupingValue)
{
int groupingValue = int.Parse(GroupingValue);
string[] colorPalette = {"Green", "Blue", "Red"};
if (groupingValue < colorPalette.Length)
{
return colorPalette.GetValue(groupingValue).ToString();
}
else
{
return "Black"; // if # of Series is more than defined - all undefined will be = Black color
}
}
}
}
I tried 2 different ways:
1.
Then I put a code into a Custom Code section of a report:
Code Snippet
Public Function GetColor(SeriesNumber as String)
Return CustomColorPalette.CustomPalette.GetColor(SeriesNumber)
End Function
And in the Chart Custom Color settings I put:
Code Snippet=Code.GetColor( Fields!SeriesNumber.Value )
And after compilation I get an error:
Error 303 [rsCompilerErrorInCode] There is an error on line 2 of custom code: [BC30451] Name 'CustomColorPalette' is not declared. c:developmentvoyagerreportsvoyager convert reports.rootvoyager convert reportsReport_007D.rdl 0 0
2.
Second way I tried:
Then I put a code into a Custom Code section of a report:
Code Snippet
Dim X As CustomColorPalette.CustomPalette
Protected Overrides Sub OnInit()
X = new CustomColorPalette.CustomPalette()
End Sub
And in the Chart Custom Color I put:
Code Snippet=Code.X.GetColor( Fields!SeriesNumber.Value )
And after compilation I am getting an error like this:
Error 302 [rsCompilerErrorInExpression] The BackgroundColor expression for the chart €˜chart2€™ contains an error: [BC30456] 'X' is not a member of 'ReportExprHostImpl.CustomCodeProxy'. c:developmentvoyagerreportsvoyager convert reports.rootvoyager convert reportsReport_007D.rdl 0 0
Is there any workaround? Or all I can do is to make the Custom Colors manually in each report, and if something need to be changed - I will need to change it in each report separately
Thanks,
Alex
Hi All,
I want to get the Font,Background colors of the report item(Textbox) changed in Runtime,According to the value in the Textbox. The idea is to get this done by using the Same Custom code for getting both the Foreground,Background colors, I thought to implement this with flag, But I dont know how to do this..
Reporting experts ..please help me with this issue.Thanks in Advance
-Mahendra
I need to print in RED if there's atleast 1 or more consistency error and need a heading color of the table (Caption) how to do it.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>DB Corruption Report:</H1>' +
N'<table border="1">' +
N'<tr><th>DBName</th><th>MessageText</th>' +
[code]....
I am facing some issue in rendering my SSRS report(2012 version) to excel sheet with color codes. i am using conditional formatting to display the colors. So what is happening is ,take the below expression as an example, for the id  = 0, we are not getting white back ground, we are getting some other colors. But the same thing is working fine in SSRS 2008R2.Â
=IIF(MAX(Fields!id.Value=0,
= 0, "WHITE","BLUE")
We mark cells in  a report using color names like red, pink, and transparent in the fill expression. In the browser it works correctly. When exported to Excel the colors go to the wrong cells. This worked correctly in SSRS 2008.Â
View 9 Replies View RelatedI have DTS package which exports the data from myTable to myXLSheet - works fine, except this appends data to the existing myXLSheet.
Is there any way of creating a new sheet or clearing out the existing one first???
Thanks
Hi,
Ive installed and imported a db into SQL2005 Express. I now need to export the db to an excel file but I cannot find the import/export feature. Ive tried navigating to the db within the SMSE inteface and right clicking but there is no options available to carry out the task.
Any ideas guys, stuck on this one
DW
Does any one know how to name the tabs in reporting services when you export the date to excel. example I want sheet1 to be Summary. Can this be done in rporting services?
View 2 Replies View RelatedHiI am using the DTS package with SQL SERVER 2000. I have a table, and in oneof the columns are links to websites. I am trying to export this to Excel2002 SP3a typical text string stored in the table is=HYPERLINK("www.asite.com","Click")I had hoped the field would be transferred exactly as is and so would appearas a hyper link in the Excel document it creates. Unfortunateld it seems toautomatically put a ' in front of it so if I click on the cell, in the barat the top I get'=HYPERLINK("www.asite.com","Click") - Notice the apostropheso the full text is displayed in the spreadsheet (rather than the wordClick)Does anyone know of a way to get rid of the ' thats being added onthanks in advanceAndy
View 2 Replies View Related
Hi,
I have 5 subreports, where each is wrapped in its own rectangle, in one report. The report renders successfully. However, when I try to export the results to Excel, I receive the following error
An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Item has already been added. Ket in dictionary: '_41_201' Key being added: '_41_201'
Any ideas?
The reports defined in the subreports contain drill through links to each other. These reports were originally designed to be drill through reports. However, my user will like to export them into one workbook.
Hi All,
I am creating reports using SSRS 2005.
After deploying my reports to the reporting server i want my reports to be exported to excel.
But when i perform this the formatting of the report gets changed in excel.
The cell in excel gets merged.thus the structure gets distorted.
Does anyone have any idea in this regrard?
Why is it so and what is the solution for the same?
Thanks
Regards
Saurabh
Hi,
Sorry if this is a stupid question, RS isn't my area of expertise, a customer has asked if it was possible to export all the data in an RS report onto one Excel sheet.
She has created a report off an AS2005 cube, there are 71 pages, is there a way of increasing the number of rows per page or even putting them all on one page? Having some 30 sheets in a spreadsheet seems a little excess.
Thanks in advance
Matt
I'm trying to export a table from SQL7 to Excel. Only thing is that I'd like to specify the Excel spreadsheet filename at run time. The name of the spreadsheet needs to contain the current date e.g. "table1_20011005.xls", "table1_20011006.xls". Is there any way I can do this ? I've looked at DTS but it seems you need to specify the spreadsheet file name and cannot alter it.
View 2 Replies View RelatedHi,
I am having some problems exporting to Excel using SQL 7.
I have a DTS package which runs a query that returns one value and pumps the data to an Excel spreadsheet. Everything is working except the data is not populating the correct cell in the spreadsheet. How can I control which cell SQL pumps the data to?
Thanks.
The date in sql appears like this '07/25/2013 00:00:00' but when I export to excel the date shows like this '22-JUL-81 12.00.00.000000000 AM'. When I change format in excel nothing happens.
View 6 Replies View RelatedKevin writes "Please forgive me if this is a stupid question. I am working in an environment where we are using SQL Server 2005 and Excel 2003. We have a custom .NET 2005 application which runs against the SQL Server. Users frequently create ad hoc queries in Management Studio to pluck out certain info. They would like to save the results down into an Excel spreadsheet for further analysis; However, we haven't come up with an elegant solution to save these results in Excel and keep the column headings and data types for each column. I have seen transact-sql code to do this in the past using the OPENROWSET command to write to an Excel file; However, I am not confident that this is the best way to skin this particular cat so I thought I would run it by you. So, my question is, What is the BEST way to save ad hoc query results from SQL Server to an Excel spreadsheet so that I preserve the column headings and data type of each column?"
View 2 Replies View RelatedHi,
I am Sree Raj. New to SQL Server.
I am trying to export data to excel and it is giving an error.
Here is the query and error:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:SQL ServerEmp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM Emp_Info
error is:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
Can anybody please help me what is this error about & how to fix this.
Thanks in advance
Sree Raj
Hi all,I've seen this noted in many posts, but nothing I've checked out givesme any clue on how to do this.Basically as my topic says, I have a DTS and I simply need to exportsome data from a table in MS SQL 2000 to an Excel spreadsheet. I alsoneed to automate this process so it can run nightly and each new day anew spreadsheet will be on a network share for us to pick-up.Can someone point me to the right direction? This needs to be donetotally through the DTS script, so no ImportExport wizard or anythingmanual.Thanks --Sam
View 1 Replies View RelatedFirst time here so please bear with me.Set up a DTS package to export data to an excel sheet on an hourlybasis. Problem is, it keeps appending to the same excel sheet.Any idea how to prevent that. All I want to accomplish is that everyhour, the latest data is in the excel sheet and the previous data isdeleted.Thanks in advance!
View 5 Replies View RelatedMy users have an excel sheet I'm trying to migrate to RS...by and large it's just staic data, which is GREAT for me. But the sheet has one "Total" column, where they locally enter three values then print it off. Is there a way to get a =SUM(X:X,X:X) to migrate to excel so it defaults to 0? If I put it in quotes those come over to excel, if I dont put it in quotes I cant even preview...
Suggestions?
Thanks,
Steve
Hello,
Im using SSRS and i made a report that has a huge quantity of data, and when i want to export it to excel i got the following error.
Server Error in '/Reports' Application.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors]
[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +959
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
I can export other reports to excel, but not this one. Can anyone help me here?
Thank you
I get an error when I export my report to excel. But I do not get this error if I export the report to any other format. The error is:
An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Object reference not set to an instance of an object.
Any ideas what could be causing this error?
This report does contain a couple of sub-reports.
Hi,
I have a report returning about 50000 rows, when i export this into excel it takes a few minutes and the file size is about 13MB, When i try to open up a 13mb file it is so slow...it is better for me to execute the dataset in SQL analyser and copy the results directly into excel whereby the file is 8mb and opens up also instantly...
My exported version is just data and no graphics however the page appears to be ''white'' although i set the fill in excel to transparent...maybe this is making the file hard to open...
Anybody have problems with exporting to excel and actually able to use it without running into long delays due to the file size...what can i do to fix this
thanks
When i export data from SSRS to excel , if the textbox contains data with more than one line, in excel it comes only as a single line with same width as given in SSRS .Inside it contains data but not displaying
View 4 Replies View RelatedHi,
I have a windows form in visual studio and am trying to export a table from the local sql database into a excel worlbook. I followed this example: (http://support.microsoft.com/kb/307029/en-us) that uses the example northwind mdb database and it worked ,but when I try to change the connection string I get an error
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
Could anyone let me know the correct way to go about this. My ultimate aim is to export a report.
I'm using RS2000 and I have a report that when I exported to the Excel, all the groups were automatically converted to outline format in Excel. After a year, I decided to update the report a bit and now when I export the report to Excel and it only export the top group (group1). The rest of the data doesn't even get transferred to Excel. Is there a choice in tables were you can select whether or not to export groups? I basically added more groups and mergered a few columns, but I can't pin point what's causing this to happen. Any help on this would be appreciated very much.
Thanks in advance!!!
Hi
I'm experiencing the following problem:
When I export my report to excel then open up the Print Preview in Excel, the table headers are not printed on each page, only on the first. I have ticked the "Repeat header rows on each page" in the table properties, but to no effect. For some reason this works for PDF but not for Excel.
Is this some kinda bug or am I doing something incorrectly? I've tried putting the report in in a table as a sub-report, but that doesn't work either.
Is there a work-around (besides putting the table header row in the page header)?
I'm really at my wits' end and quite frustrated.
Thanks in advance
Exporting to Excel; timeout
I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying €œThe underlying connection was closed: An unexpected error occurred on a receive.€?
Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service
Hi,
I am trying to export a report that generates around 32,000 records in excel but it throws an error page €œServer Application unavailable€?.Exporting it in pdf format works fine.When I filtered the data selecting 'ALL' option for all the inputs I got this error meggase but When I view the report selecting a particular detail for the required data,it exports to excel normally.Is this because the number of records have been reduced?Is there anyway to resolve this issue?
Thanks,
annej.
I am new to SQL and can do queries OK on SQLTalk. I need to know if there is a script to retrieve data and then export to an Excel spreadsheet for internal company use. Is there such a beast and is this the right place to look???
View 1 Replies View RelatedScenario :
SQL table has 2 columns SQ1, SQ2
Excel sheet has three columns : EX1, EX2, EX3
SQ1 matches with EX2
SQ2 matches with EX3
I would like to insert the rows where EX1='X'. EX1 is the left most column in th Excel sheet.
Here's what I am trying. Could you suggest any changes/suggestions?
-- Link server logic
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Config_spreadsheet_load')EXEC master.dbo.sp_dropserver @server=N'Config_spreadsheet_load', @droplogins='droplogins'
GO
--This is the link logic to connect the spreadsheet to the database
sys.sp_addlinkedserver 'Config_spreadsheet_load',
Excel',
'Microsoft.Jet.OLEDB.4.0',
In : @ExcelfileName,
'excel 8.0;
IMEX=1;
GO
-- Insert Plan Codes, for the column (PLC_NEW_COL) marked as ‘X’
insert into Plan_code (SQ1,
SQ2)
-- **** How can I have the logic for checking EX1='X'
where PLC_NEW_COL like ‘X’
-- Once the data has been created, clean-up