SSRS REPORT Invisible columns shown when exported

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

SSRS REPORT Invisible columns shown when exported

Author

Topic

raj vardhan43
Starting Member

20 Posts

Posted - 2012-11-12 : 14:15:47

Hi All,

I have a SSRS report.I had used an expression in the hidden property in the tablix member. My intention is to show only those records which evaluates to 'false' for the expression.

When i export this report to excel, everything is working is fine, i.e hidden records are not shown, but when i export this same report to .csv then hidden records are also shown.

I had tried using =iif(Globals!RenderFormat.Name="CSV", True, False) in the hidden property and also tried changing the "DATA ELEMENT OUTPUT" from auto to output, but they didn't worked.

Does anyone solved this problem earlier ??

raj

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 15:03:55

Did you mean DataElementOutput from Auto to NoOutput? If you do, it will be excluded in CSV output.

raj vardhan43
Starting Member

20 Posts

Posted - 2012-11-12 : 15:42:04

Hi!!

I tried using DataElementOutput=NoOutput, then all the records are excluded, i don't want all the records to be excluded. I want to exclude only those records which evaluate to true in the hidden property expression. Seems, this is bug in SSRS. Have you faced similar problem earlier ??

Thanks for your time!!

quote:
Originally posted by sunitabeck

Did you mean DataElementOutput from Auto to NoOutput? If you do, it will be excluded in CSV output.

raj

navya krishna
Starting Member

39 Posts

Posted - 2013-01-23 : 00:35:30

Dear all,

i have one problem in ssrs.i need to display sales report customer wise [sales and budget and variance and variance percentage] from different databases but in single server.it will be there detailes in multiple databases.i need to display detailes in single report.could you please anyone help me.for example we are maintaining 2010,2011 databases so i need to display customer no,sales,budget,variance,variance percentage in single report ...

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 01:40:10

quote:
Originally posted by navya krishna

Dear all,

i have one problem in ssrs.i need to display sales report customer wise [sales and budget and variance and variance percentage] from different databases but in single server.it will be there detailes in multiple databases.i need to display detailes in single report.could you please anyone help me.for example we are maintaining 2010,2011 databases so i need to display customer no,sales,budget,variance,variance percentage in single report ...

navya krishna katta

you can a create view which includes the details from all the database tables like


CREATE View YourViewName
AS
SELECT columns
FROM DB1.dbo.table
UNION ALL
SELECT columns
FROM DB2.dbo.table
UNION ALL
SELECT columns
FROM DB3,dbo.table
...

i would have opted to keep them in same database with just a column indicating date or year of transaction

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 03:15:36

HELLO Visakh,
if i write above query i am getting below error

"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict".....

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 03:30:58

add COLLATE clause to convert all varchar data to default collation


CREATE View YourViewName
AS
SELECT columns COLLATE database_default
FROM DB1.dbo.table
UNION ALL
SELECT columns COLLATE database_default
FROM DB2.dbo.table
UNION ALL
SELECT columns COLLATE database_default
FROM DB3,dbo.table
...

do this only for character based fields (varchar,char,nvarchar etc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 06:55:16

CREATE View MDB
AS
SELECT [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer].Address COLLATE database_default
FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer]
UNION ALL
SELECT CCIL12.dbo.[CCIL-2012$Customer].Address COLLATE database_default
FROM CCIL12.dbo.[CCIL-2012$Customer]

i am getting error "Create View or Function failed because no column name was specified for column 1" means where no column name specified.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 07:09:26

[code]
CREATE View MDB
AS
SELECT [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer].Address COLLATE database_default AS Address
FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer]
UNION ALL
SELECT CCIL12.dbo.[CCIL-2012$Customer].Address COLLATE database_default
FROM CCIL12.dbo.[CCIL-2012$Customer]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-02-05 : 03:07:14

Hello Visakh,
CREATE View MDB
AS
SELECT [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer].Address COLLATE database_default AS Address
FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer]
UNION ALL
SELECT CCIL12.dbo.[CCIL-2012$Customer].Address COLLATE database_default
FROM CCIL12.dbo.[CCIL-2012$Customer]

it's working in sqlserver management studio but it's not working in ssrs how can i use it in ssrs.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 03:18:08

why? whats the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-02-05 : 06:46:16

Hello Visakh,

If i write above query it will be displaying only ccil 12 dataset fields .how can i display from both table.

navya krishna katta

navya krishna
Starting Member

39 Posts

Posted - 2013-02-05 : 06:50:04

Hello Vishak,
see i created that query in sql server management studio in cronus db and then in ssrs i created two dbs and two datasets then i have taken address field from both tables .but in cronus table i have selected that created view .it will be displaying only second ccil 12 address but not showing two dbs addresses.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:13:08

i dont understand what you're talking about here

in ssrs i created two dbs and two datasets

you cant create dbs in ssrs. I hope you meant datasources

why do you need two datasets? have you applied any filters in report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-02-06 : 13:02:09

Hello visakh,

see i created query in sqlserver management studio it will be working perfectly there but it will be getting error in ssrs .

SELECT CCIL12.dbo.[CCIL-2012$G_L Account].No_,
(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN '2012-01-01 00:00:00.000' AND '2012-10-31 00:00:00.000')),
(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test1
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN '2012-10-01 00:00:00.000' AND '2012-10-31 00:00:00.000'))
FROM CCIL12.dbo.[CCIL-2012$G_L Account]
WHERE CCIL12.dbo.[CCIL-2012$G_L Account].No_ IN('41120','41130','41140','41150')

I am getting error

"[rsErrorReadingDataSetField] The dataset ‘DSCCIL’ contains a definition for the Field ‘EXPR1’. The data extension returned an error during reading the field. System.OverflowException: Conversion overflows. at System.Data.SqlClient.SqlBuffer.get_Decimal() at System.Data.SqlClient.SqlBuffer.get_Value() at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Microsoft.ReportingServices.DataExtensions.SqlDataReaderWrapper.GetValue(Int32 fieldIndex) at Microsoft.ReportingServices.DataExtensions.MappingDataReader.GetFieldValue(Int32 aliasIndex) F:\SSRS\COA REPORT\COA REPORT\chart of account.rdl"

Please can you help me.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 22:45:09

Whats EXPR1? I cant see such a field in your resultset. You need to post the definition for that as i think issue is with EXPR1 calculation causing data conversion overflow

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-02-07 : 01:26:28

Hi visakh ,
if we paste that query in ssrs it will show that fields like expr1,expr2 .yes exactly your doubt is right.data conversion overflow.it will be getting problem to display.query is right it's showing in ssrs error conversion overflow.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 03:11:43

whats the datatype of Amount field? are you doing any explicit casting at ssrs end? please post expression you used in SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources