I have a report that has 10 columns. While viewing, users should see only 8 columns and 2 will be hidden.
But is there any way we can export those 2 hidden columns to excel?
Any help is greatly appreciated.
Thanks,
I have a matrix table with a rectangle in the data cell. The rectangle has an image and textbox. The textbox has an expression in it's Hidden property based on the column name. The report renders fine on screen. When the report is exported to Excel, CSV, XML the textbox contents are not output (the images display as expected). I've tried setting the DataElementOutput to Output/Yes with no success. Exporting to TIFF, PDF, Web Archive/MTHML is fine.
Here is a sample RDL which exhibits the issue:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Gemini50DataSource">
<DataSourceReference>Gemini50DataSource</DataSourceReference>
<rdataSourceID>bb03313c-48a4-4e40-af99-ed584847ca20</rdataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="ImagePath">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>c:</Value>
</Values>
</DefaultValue>
<Prompt>Image Path</Prompt>
</ReportParameter>
</ReportParameters>
<rdrawGrid>true</rdrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rdnapToGrid>true</rdnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>3.75in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.75in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="PicIndex">
<rdefaultName>PicIndex</rdefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PicIndex.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_PicIndex">
<GroupExpressions>
<GroupExpression>=Fields!PicIndex.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ColumnName">
<rdefaultName>ColumnName</rdefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ColumnName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ColumnName">
<GroupExpressions>
<GroupExpression>=Fields!ColumnName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Width>5.5in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rdefaultName>textbox1</rdefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.55208in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.30208in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Image Name="image1">
<Sizing>AutoSize</Sizing>
<Left>0.25in</Left>
<MIMEType />
<ZIndex>1</ZIndex>
<Visibility>
<Hidden>=IIF(First(Fields!ColumnName.Value = "image"), Len(Fields!CellValue.Value)=0, true)</Hidden>
</Visibility>
<Width>0.3in</Width>
<Source>External</Source>
<Style />
<Value>="file:" + Parameters!ImagePath.Value + Fields!CellValue.Value</Value>
</Image>
<Textbox Name="textbox2">
<Left>1.625in</Left>
<DataElementOutput>Output</DataElementOutput>
<rdefaultName>textbox2</rdefaultName>
<Visibility>
<Hidden>=IIF(Fields!ColumnName.Value <> "image", False, True)</Hidden>
</Visibility>
<Width>1.875in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>PeachPuff</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Fields!CellValue.Value</Value>
</Textbox>
</ReportItems>
<Visibility>
<Hidden>=IIF(True, False, True)</Hidden>
</Visibility>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</Rectangle>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.625in</Height>
</Body>
<rd:ReportID>a944d20c-558a-4805-9d4c-aecc9757f678</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT 1 as PicIndex, 'image' as ColumnName, 'image1.jpg' as CellValue
union
SELECT 2,'image','image2.jpg'
union
SELECT 3,'image','image3.jpg'
union
SELECT 4,'image',null
union
SELECT 5,'something else',null
union
SELECT 6,'another column', 'display my text!'</CommandText>
<DataSourceName>Gemini50DataSource</DataSourceName>
</Query>
<Fields>
<Field Name="PicIndex">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>PicIndex</DataField>
</Field>
<Field Name="ColumnName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ColumnName</DataField>
</Field>
<Field Name="CellValue">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CellValue</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Nice topic, Hidden columns!!
I read several threads about this topic.
This is what I understood:
when you hide a COLUMN based on an Expression when you render the report all the hidden columns takes space at the end of the report because the body doesn't rezise.
It seems that there is no workaround, this is how RS works (any correction is appreciated) and I can leave with it because I don't have so much hidden columns.
My problem is that the background color of the columns of the table is RED and when I export the report to PDF, at the end of the table with the visible columns I have some columns red..
If these extra columns would be white it could be acceptable, but these red columns are really annoyng!
Someone can advice me how to solve this issue?
Thank you
Marina B.
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
Hi,
I hide some fields in the Report based on the user's query selection.
However, when the user exports to excel all the report fields appear.
How do I show only the relevant fields in the excel export?
Thanks.
Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.
Thanks
Hello,
Can someone please tell me why there's ALWAYS a hidden cell in Excel when a report has a more than one page?
for example, a report has 10 page breaks and after exported to Excel it has the first row hidden in each sheet except the first sheet.
Why this happens always? is there any workaround to avoid this?
thanks
I have a need to import only non-hidden rows from an excel spreadsheet. When I create the package in SSIS, it imports everything. Due to the use of the data on the spreadsheet, we cannot simply delete the data.
Is there a special setting in the Excel Source or Connection manager that can be set to "only import non-hidden rows"?
Also, how do I go about setting the sheet with an index instead of the actual Sheet name? The user changes the sheet name at random, but I know I only need the first two sheets on the file.
Thanks!
Matt Michuta
Given a model in Power Pivot with a number of KPIs that are hidden with the 'hide from client tools' option. After restoring the model in SSAS and connecting to the model from Excel, the KPIs are visible under a separate category/table "KPIs".
Other hidden items in the model like columns and measures remain invisible.
Is this normal behavior and what can I do about this?
When I open the spreadsheet in Excel 2000, it works fine. When I try to print, it crashes Excel. In testing, I narrowed it down to the Header/Footer, because it also crashes when I go to Page Setup and click on the header/footer tab.
However, I can print the same spreasheet from Excel 2007.
Am I just dealing with a "you need to upgrade all your clients" situation, or is there a known issue with certian formatting that is passed out with reports that is not supported by older versions of Excel?
I am using Reporting Services 2005 SP2 to serve up the report that is exported to Excel.
Any assistance is appreciated.
Hi All
I've been googling this for a while now and can't seem to find any elegant answers.
I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.
Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?
His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).
In an ideal world an individual would send an email to the Server with two formated parameters (@FromDate & @ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.
Thanks
GW
I have a query where I am trying to export a column that has around2000 characters and it is truncated. What is not setup in MS SQLcorrectly so that DTS will export the csv correctly.Thanks,John
View 1 Replies View RelatedHello,
I have a group I'll call G4.
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
=IIF(ReportItems!Textbox66.Hidden = False, True, False)
...the report chokes on it.
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
How can i print or export all fields for all tables in my sql 2005 db?
View 3 Replies View RelatedHi
I need to export data to CSV file from Sql Reporting Services.
I am including single quotations in my view to display in SRS for string fields.
After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Please let me know if anybody knows.
Thx
Vijji
I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.
Any help would be appreciated.
Jerry
I've built a fairly straight forward report in RS that looks normal in preview mode and in PDF format with out any issues.But when I export it to Excel report header is not appearing in each page.Any ideas as to why this is occurring?thanks in advance,Ramesh KS
View 1 Replies View RelatedIs there any nice way when saving a result set grid to CSV (right click, save to CSV) to properly delimit text fields that contain commas? As it is, text data that includes commas cannot be safely exported to CSV.
Example:
SELECT 'Hi there, friend!', 'Hi' UNION ALL
SELECT 'Bye now', 'Bye'
Anyone know a way to pull specific fields from an excel spreadsheet.
I'm using SQL7 DTS to pull data into a SQL table. I can pull the entire
sheet but I just want certain fields.
thanks, archie
I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?The code is as follows:Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn) Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader() Dim row As Integer = 0 While excelreader.Read() NameValsBuffer.AddRow() NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0))) NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1))) NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2))) NameValsBuffer.CompanyId = Me.Variables.CompanyId NameValsBuffer.UserId = Me.Variables.UserId End While
View 5 Replies View RelatedHello All, I'm simply exporting data from SQL to Excel via the Export Data feature in SQL Management Studio.My problem is I have a column aliased as [ID #] and when it exports it changes the Pound Sign into a decimal -> ID .SPACE(25) As [ID#], It's not a major problem but does anyone have any suggestions? Thanks, Kyle
View 3 Replies View RelatedHi there I know its possible to export data from a gridview to excel. I'm looking to export data directly from a stored procedure at the click of a button.Somebody suggested using the following:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C: esting.xls;', 'SELECT * FROM [SheetName$]') select * from table-nameWhen I tried executing the above lines of codes I got the following error message:"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." If anyone has any idea whats wrong ... plssssssssssssssssssss ... let me know .... Thanks in advance.
View 4 Replies View RelatedI have a job which produces a daily report and exports it to excel. However, instead of overwriting the contents of the file the data is being appended to the file. Any suggestions welcome.
Thanks in advance
Vince.
I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)
what is the best way to do this? DTS, BCP
thanks
FOCEL
I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.
In the Transform Data Task Properties:
EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'
It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)
When I perform the same task using DTS Export utility, i get the following error:
Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS
Please Help
Thanks
Hello All
First time setting up A SSIS
Trying to import a excel that creates a new table called lockbox.
Does not import any records from Columns-Contract Id, Owner ID,
that have a null value.
Then in the Site Id -that is a number value- change to a text value
and update it to it text value.
for example: Site Id Column
2 = Ell
3 = CSI
12 = Ell2
Can someone get me started in the right direction?
Thanks for your time and help
JK
I have written a query which dumps the output to a table, ideally I would like to select everything from this table in to an Excel file.
Looking @ http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/
Made me think this would be quite easy... so
1. I created a spreadsheet named test.xls with the column headings from my temp table
2. Saved and closed this xls
3. Tried to run the following:
USE [PEI];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:DELL est.xls;',
'SELECT * FROM [Sheet1$]')
SELECT *
FROM tblCFPooled
GO
Where C:DELL est.xls is where I saved test.xls, tblCFPooled is the table I have populated in the firstplace and PEI is the database name.
When I run this the following error crops up:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I then found something on a different site:
http://www.mssqltips.com/tip.asp?tip=1202
So based on this I tried:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:DELL est.xls;',
'SELECT * FROM [T$]') select * from tblCFPooled
And obtained the following error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'T$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
(I renamed the sheet1 to T if you are wondering where T$ comes in!)
Before running either of these 'export' queries I did as instructed in the first link:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Which produced results:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.
I assumed from that I had successfully enabled Ad Hoc Distributed Queries - Is that correct?
Both methods seem to throw up a similar error, an error which makes me think its some kind of SQL authentication issue.
Has anyone successfully exported from SQL to Excel - if so, any tips?!
(For the record I am using Management Studio, we are running SQL Server 2005, I am doing all this on a Vista Business machine, the SQL server is on a local server on our network here. )
Hi All,
While exporting my reports to Excel , I want them to be exported to a single excel book in multiple sheet.
i.e. single report in single sheet and all in a single excel workbook.
Does anyone have any idea how to do that?
Regards
Saurabh
Hi,
I have a report when I run that report if the result is span for multiple page the table header is displaying for each page.When exported to PDf it is displaying the table header for each page.but when we export to Excel the table headers are not displaying.Any work around for this.
Thanks
Hi ,
I have a simple report which i want to export to excel format. the are no groups in the report. I need each page of the report to appear as a seperate page in excel. currently when i export the report i get a single continuous page. is there any way in which i can do this ????
I have some reports that I used vbcrlf in. Whenever they are exported to Excel only the first line appears.
Ex: Name (&vbcrlf)
Address 1 (& vbcrlf)
Address 2 (& vbcrlf)
City, State, Zip
This exports to Excel and displays the name line only.
However, if I close the file that was exported and the extra blank screen, and hit export to excel again for a second time, all of the information is displayed.
Have you tried exporting your report with subreports to Excel? How does it work?
Thanks!