I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?
View 5 Replies
I am trying to export a report from sql server reporting services in a csv format, but I do not want the text qualified with quotations. Is there any way to do this?
Hi,
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
MiraJ
Hi,
I am trying to remove rows of data when the value in a textbox in the row is 0.
My expression for a value of a textbox in my report layout is:
=Fields!salary_FTE.Value+Fields!Leave_FTE.Value
Could you please show me how I edit this expression so a row of data does not show when the value in this textbox is 0, or, should I create this filter in my query rather than in the report layout?
Thanks
Hi, everyone
This is my data comming from Long StroredProcedure.In this Procedure will have 5 CTE's. I want to do with other function. i.e Removing Repetetive data form ACTION column. This ACTION column consist with BUY/SELL. Whenever two or More BUY/SELL i want to remove that one.
INPUT:
TABLE1
TIME ACTION
2008-05-09 19:33:07.657 SELL
2008-05-09 19:33:08.017 SELL
2008-05-09 19:33:08.360 BUY
2008-05-09 19:33:08.703 SELL
2008-05-09 19:33:09.063 SELL
2008-05-09 19:33:09.423 BUY
2008-05-09 19:33:10.093 BUY
2008-05-09 19:33:10.437 SELL
2008-05-09 19:33:10.797 SELL
OUTPUT:
TIME ACTION
2008-05-09 19:33:07.657 SELL
2008-05-09 19:33:08.360 BUY
2008-05-09 19:33:08.703 SELL
2008-05-09 19:33:09.423 BUY
2008-05-09 19:33:10.437 SELL
Please Help ME anyone..
Hi,
Could you please tell me removing table columns with null value will reduce the datafile size.
Data file size needs to be reduced to make free space on the disk, Could please suggest what data type can be choosen to be removed from the existing table so that more space can be made(Char,varhchar,int,date etc )
Thanks
John Jayaseelan
I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,
[code]......
I apologise if this question has been asked before but I have spent ages searching these forums and the internet for an answer and I am yet to find one.
My problem is that I have a package which imports a column, lets call it 'Column A'. Column A is used to create other columns, lets say Columns B, C & D. This is done in a script using asychronous input and once completed column A is no longer used. Other tranformations occur to B, C & D including a split and then finally a merge together again, but all the time A seems to remain a valid input on all processes yet I never choose to use or output it. When I come to the merge process I am required to merge columns B, C & D but also A, surely this is inefficient. Furthermore when the package has run I get a warning telling me that Column A is not required and should be removed, but I can not seem to find anywhere to remove it from the pipe.
I am hoping that I am just missing something obvious here but I have been tearing my hair out so any help would be much appreciated!
Graham.
Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.
I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.
Cheers, Al
Hi,
I have results from a survey in a table, every entry is assigned a unique ID. I want to remove duplicate entries based on the survey data but not on the unique ID (obviously).
So far I have...SELECT DISTINCT RespondantID, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13
FROM Results
But that gives...
1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1
2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2
3 - Martin
2
2
2
2
2
2
2
2
2
2
2
2
2
I.e. in the above example, it would seem that 'Martin' submitted his data once and then submitted it again with his name.
How can I remove the '2 - Anonymous' frrom the data set?
Thanks!
Anyone know how to check the length of a varchar data in a table? I want to delete rows that contains an entry that exceeds 100 characters.
I should be able to write a program to do it, but I am wondering if I can do it in SQL.
i've to generate a notepad using this query in vb.net :
strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _
[Code]....
In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.
Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.
how to group the "d.amount" without having repetition in the "d.shareholderid" ??
I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?
Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310
So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310
I have a sort component which is (wrongly) reducing the number of rows* in my dataset.
NB "remove duplicates" is NOT ticked!
Why is this occuring?
*number displayed on the flow arrows at runtime
I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this?
(if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data)
Thanks.
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005.
I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
INNER JOIN Threads ON Topics.topicid = Threads.topicid
INNER JOIN Messages ON Threads.threadid=Messages.threadid
WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as below:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
I want the result set as follows:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above.
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
INNER JOIN Topics ON Topics.topicid = Threads.topicid
WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
) AS LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as:-
ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads
1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
Developers
2
JSP
NULL
NULL
0
1
Developers
3
EJB
NULL
NULL
0
1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
Developers
5
AWT
NULL
NULL
0
1
Developers
6
Web Services
NULL
NULL
0
1
Developers
7
JMS
NULL
NULL
0
1
Developers
8
XML,HTML
NULL
NULL
0
1
Developers
9
Javascript
NULL
NULL
0
2
Database
10
Oracle
NULL
NULL
0
2
Database
11
Sql Server
NULL
NULL
0
2
Database
12
MySQL
NULL
NULL
0
3
Desginers
13
CSS
NULL
NULL
0
3
Desginers
14
FLASH/DHTLML
NULL
NULL
0
4
Architects
15
Best Practices
NULL
NULL
0
4
Architects
16
Longue
NULL
NULL
0
5
General
17
General
NULL
NULL
0
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Messages] (
[msgid] [int] IDENTITY (1, 1) NOT NULL ,
[threadid] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[date] [datetime] NULL ,
[message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD
PRIMARY KEY CLUSTERED
(
[msgid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Messages] ADD
FOREIGN KEY
(
[threadid]
) REFERENCES [dbo].[Threads] (
[threadid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
,DischDate
,AdmDiagCode
,ID
,AdmDiagCodeDesc
,AdmittingDiagnosis
,AnyDx
,DischDx
,ECode
,IntOOS
,PrinDxAnesDesc
,PrinDxAnesInd
,PrinDxCode
,PrinDxCondDesc
,PrinDxCondInd
,PrinDxDesc
,PrinDxEqualsAdmDxYnu
,SecDx10AnesDesc
,SecDx10AnesInd
,SecDx10Code
,SecDx10CondDesc
,SecDx10CondInd
,SecDx10Description
,SecDx11AnesDesc
,SecDx11AnesInd
,SecDx11Code
,SecDx11CondDesc
,SecDx11CondInd
,SecDx11Description
,SecDx12AnesDesc
,SecDx12AnesInd
,SecDx12Code
,SecDx12CondDesc
,SecDx12CondInd
,SecDx12Description
,SecDx13AnesDesc
,SecDx13AnesInd
,SecDx13Code
,SecDx13CondDesc
,SecDx13CondInd
,SecDx13Description
,SecDx14AnesDesc
,SecDx14AnesInd
,SecDx14Code
,SecDx14CondDesc
,SecDx14CondInd
,SecDx14Description
,SecDx15Code
,SecDx15Description
,SecDx1AnesDesc
,SecDx1AnesInd
,SecDx1Code
,SecDx1CondDesc
,SecDx1CondInd
,SecDx1Description
,SecDx2AnesDesc
,SecDx2AnesInd
,SecDx2Code
,SecDx2CondDesc
,SecDx2CondInd
,SecDx2Description
,SecDx3AnesDesc
,SecDx3AnesInd
,SecDx3Code
,SecDx3CondDesc
,SecDx3CondInd
,SecDx3Description
,SecDx4AnesDesc
,SecDx4AnesInd
,SecDx4Code
,SecDx4CondDesc
,SecDx4CondInd
,SecDx4Description
,SecDx5AnesDesc
,SecDx5AnesInd
,SecDx5Code
,SecDx5CondDesc
,SecDx5CondInd
,SecDx5Description
,SecDx6AnesDesc
,SecDx6AnesInd
,SecDx6Code
,SecDx6CondDesc
,SecDx6CondInd
,SecDx6Description
,SecDx7AnesDesc
,SecDx7AnesInd
,SecDx7Code
,SecDx7CondDesc
,SecDx7CondInd
,SecDx7Description
,SecDx8AnesDesc
,SecDx8AnesInd
,SecDx8Code
,SecDx8CondDesc
,SecDx8CondInd
,SecDx8Description
,SecDx9AnesDesc
,SecDx9AnesInd
,SecDx9Code
,SecDx9CondDesc
,SecDx9CondInd
,SecDx9Description
,VisitTypeCode
,accountnumber
,DischVisitTypeCode
FROM ampfm.rpt_AdtVisitDiag
)
--Select * From CTE
Delete From CTE Where ROWID > 1
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data.
Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount
123, Grenadine, 100.00, 75.00, 15.50, 2.00
456, Lime Juice, 120.00, 81.00, 17.25, 2.00
There could be 1 item or 4000 items.
What I want to see is.
Item # - 123, 456
Description - Grenadine, Lime Juice
CaseSalePrice - 100.00, 120.00
Cost - 75.00, 81.00
BottleSalePrice - 15.50, 17.25
Discount - 2.00, 2.00
What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.
I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.
The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.
I am all out of creative ideas, any help would be appreciated.
I have a drill down report which need to be exported to excel. But, when exporting to excel it shows many unwanted blank rows for a particular record due to the drill down option.
Is it possible to remove it?
I need to exclude the rows in yellow color when exporting to excel.
I have a requirement to export data from a SQL Server 2012 table, to a CSV file. This needs to be done either every hour, or ideally if it is possible, whenever a new record is created or an existing record is updated/deleted.
The data to be extracted from SQL is:
Mxmservsite.siteid as Marker_ID, mxmservsite.name as Name, 'SITE' as Group, '3' as Status, '' as Notes, mxmservsite.zipcode as Post_Code, 'GB' as Country, '' as Latitude, '' as Longitude, '' as Delete
Where dataareaid='ansa'
How I can go about doing this?
When exporting a report to excel is there any way of marking certain columns as being readonly in the excel file?
Thanks
Niall
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?
Thanks
someone recently told me that their bcp export of a table with over MAX_INT rows failed because BCP hit an overflow in the internal counter that bcp uses to tell you how many rows it's exported. You know how it write "rows copied to host file: 40000" to stdout? That number got to MAX_INT, then went negative, then BCP crashed. So they had to start all over and break things up with the -F, -L flags.
I was rather surprised that a program that claims to handle "bulk" data would use a 32 bit int to accumulate the count. I suppose the original sybase dev figured nobody would ever want to export more than 2b rows.
just wondering, has anyone seen this before?
I am about to kick off a similar large export and am planning to break it up so as not to hit this, but that also means I won't be able to verify that it is indeed a problem.
elsasoft.org
Hi All,
Few days back we faced a problem when we were doing an Export to Excel of a report which was using sub-reports. After going thro. the knowledge base articles we came to know that SSRS currently doesn't suppot this option, an alternate is to use use List instead of tables, so we used List and did all the formatting with the list and the data was displayed correctly and it was Exporting to Excel also properly. But now we found out that after doing an Export to Excel, the Excel File is leaving blank rows between the data (i.e.,) If the report consists of two rows, then it displays the first row in the 10th row of excel and the second row in the 12th row of excel. The 11th row is blank and it appears as a small blank row between 10 and 12. The actual problem because of this is we are not able to do Auto filter in Excel, because Excel by default considers the values only until it encounters a blank row when we are doing auto filter. So is there a way to avoid this blank row while exporting to Excel, we have tried to remove the borders and all other stuffs but nothing seems to work. Have anyone encountered the same problem or is there any work around for this problem. Thanks in advance.
Hello Folks,
This is my first real exposure to using SSIS's Import Export Tool.
I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.
When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.
Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?
Any help would be appreciated.
Thanks, Mark
Hi...
I am new to MS SQL 2000. I need to generate reports using the SQL2000 database and exporting the data to Excel. Currently i started using the DTS services and was fine..But i have constraints that Excel can hold only 65K records..My records can go up to 80K +.Is there a way i can split the sheet depending the number of records..or is there a better way to do this...I do not have reporting services as we r using SQL 2000...Could anyone please help...
If there is any other way of doing this please guide me as to how to go about or any reference websites...
Thanks in advance
Banu
Hello:
I have an OLEDB source that uses a stored procedure which pivots records and returns me data with columns which are dynamic (Changing every time). How can I export this data with dynamic number of columns to excel destination?
Thanks
Jatin
We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies View RelatedNice 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.
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".
Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.
What am I missing here? I installed SP1 of SQL server 2005, but that did not help.
Thanks in advance.