Merged Columns In OrderBy

Jul 20, 2005

Lets say I have a table PortDates with 3 columns like this:

PORT ARRIVALDATE SAILINGDATE
NewYork 12-15-2004
Rio 10-12-2004
Hamburg 10-14-2004
etc.

I want to sort the ports on dates from both date columns
SELECT Port FROM PortDates
ORDER BY (ArrivalDate JOIN SailingDate)

Now, that doesn't work of course, but what should I write to get it to work
so I can order by the dates as if they were one merged column?

Join Bytes!

View 7 Replies

Excel Rendering - Merged Columns Etc.

Mar 19, 2007

Hi guys,

i have a client who is dissatisfied with the merged columns and rows produced when SSRS renders to Excel, it prevents them from easily copying and pasting data into another workbook.

Is there going to be any work done in this area of the product in the near future? My understanding is that this 'issue' has been around for quite some time, and shortly after the SSRS2005 RTM release the team were going to look at tidying up the Excel rendering. Can anyone from MS make an official comment on this?

Thanks,

sluggy

View 3 Replies View Related

Help Me With SP OrderBy

Jan 18, 2005

I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB

'ELP B4 I jump out of LondonBridge

**********************************
SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40

ORDER BY NULL
****************************************

SELECT @MYSTATEMENT = ' SELECT * FROM #TempTable

WHERE
ID > '+ convert(varchar(20),@FirstRec) +'
AND
ID < '+convert(varchar(20),@LastRec) +'
AND EmployerID = '+ convert(varchar(20),@EmployerID)+'
AND Job_no = '+convert(varchar(20),@Job_no)+'

ORDER BY ' +
CASE WHEN @WhichColumn= 'name' AND @sortby= 'asc'
THEN 'name'
ELSE 'NULL'
END
+
CASE WHEN @WhichColumn='name' AND @sortby='DESC'
THEN ' desc'
ELSE ''
END

EXEC (@myStatement)

View 5 Replies View Related

OrderBy

Nov 14, 2005

Hi !
I have some table with some "name" field in it which looks like
"Blah blah blah Part 1"
"Blah blah blah Part 2"
"Blah blah blah Part 3"
when I query it
select * from mytable orderby name
it doesn't actually work in a "proper" way.
I keep on getting something like
"Blah blah blah Part 2"
"Blah blah blah Part 1"
"Blah blah blah Part 3"
Do you know what should I do to make it work?

View 5 Replies View Related

ORDERBY Name

Nov 14, 2005

View 3 Replies View Related

How Do You Do ASC And DESC With Dynamic OrderBy

Mar 21, 2006

In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this?
WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND
Thank You,Jason

View 9 Replies View Related

Can We Include Orderby In View?.

May 5, 2008

when i tried to put an orderby in view...i am getting an error...

we can't have a orderby in view?.

View 7 Replies View Related

Sort Table Descending Without Orderby

Jun 24, 2008

can any one help me to sort a table on the basis of one colomn without using ORDER BY Desc.

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

View 6 Replies View Related

Simple Select Statement OrderBy

Feb 4, 2008

Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.

IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.

select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;

But now if I want to grab them in alphabetical order from another table I have problems.

this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;

any help is appreciated. Is there a way to grab each row in alphabetical order?

View 3 Replies View Related

Passing Orderby Clause As Parameter In SP

Jan 24, 2008

Hi,

I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.

Below is my procedure..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),

as

BEGIN

SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END

The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.

I am not able to figure out why.

Any help in this will be appriciated.

Regards,
Salim.

View 16 Replies View Related

Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.

Existing procedure:

ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)

select @Cond=''

if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)

if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow

exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int

select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl

set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;

SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')

RETURN

can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies View Related

Excel Merged Cells

May 15, 2007

Hi

I'm trying to eliminate merged cells when exporting a report to Excel. My problem being my report textbox above my table.

I've searched and found that making the textbox the same width as the first column eliminates the merging. Perfect, it does. But when the report is viewed in the report viewer, the textbox can only expand vertically. So the title looks terrible because column one is not wide.

I've read that expand horizontally is not an option
I don't seem to be able to tell it not to output that text box, which would be an option if there is no other answer
I can't have the text box the size I want it, due it creating merged cells
I don't want to export it as a CSV

Are there any other options available or am missing something?

Cheers

View 5 Replies View Related

Merged Cells And CanGrow

Oct 16, 2007

Is there a reason why a single cell in a table with the CanGrow property set to true will grow down, and merged cells grow right, and is there any way around it?

I have a report that I have to display comments, so I merged 2 cells put the comment field in it, and when it is displayed in the browser, it expands off the page.

When it prints, it seems to be fine, but the majority of my users will be viewing online in a browser.

Any ideas? just an HTML thing I am thinking, and nothing can be done about it.

Thanks

BobP

View 3 Replies View Related

Merged Cell's Problem??

May 16, 2007

Why the cell height(in red) is different when i export to excel?

but above the header(green) show exactly same size..

is it the merges cell cause this problem?

View 5 Replies View Related

Establishing Precedence In ORDERBY Condition Causing Problems.

Jun 11, 2007

Hi.I really need some advice on fine-tuning a stored procedurethat is the meat of the search logic on my site. Customersare allowed to save searches, which dumps the search logicin a table called SavedSearches for later access to the search.My problem started with the ORDERBY condition used for zipcodesearches. The condition did something like:"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHENuserID=102 THEN 3 WHEN userID=81 THEN 4"Of course, this fails when a customer described in the saved searchresults deletes his profile.I have since attempted to brace against this problem by adding aUserPrecendence table with the following columns: email_address,up_order (or, user precedence order), and userID.Since I have made the precedence changes, I have been unsuccessful ingetting any results (data) back from the query. I think it has to dowith the change but am not quite sure what I am doing wrong.I would appreciate it is somebody could take a look at my sproc withparticular attention to how precedence is handled in the ORDERBYcondition. Maybe you can see something I can not?As always, much appreciated.PS - In addition to the UserPrecedence change, I have attempted to addpaging - returning N amount of pages per request based on passed-inparamaters. I'd appreciate it if you could take a quick glance herealso just to make sure my logic is OK.----------------------------------------------------------------------------------------ALTER PROCEDURE [dbo].[sp_PeopleSearch]@pagenum INT = 1,@perpage INT = 10ASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INTSELECT@rows = COUNT(*),@pages = COUNT(*) / @perpageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)IF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum @pages SET @pagenum = @pagesIF @pagenum < 1 SET @pagenum = 1SET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,PageSize = @perpage,TotalPages = @pages,TotalRows = @rows,UpperBoundary = @ubound,LowerBoundary = @lbound-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE@gender VARCHAR(50),@country VARCHAR(50),@orderby INTEGER,@low VARCHAR(50),@high VARCHAR(50),@photo VARCHAR(50),@sort INTEGERSET ROWCOUNT @lboundSELECT@gender = saved_sex,@country = saved_country,@orderby = saved_orderby,@low = saved_fage,@high = saved_tage,@sort = saved_sort,@photo = saved_photo_stringFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)ORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT @perPageSELECT COALESCE(tab1.emailAddress,tab2.user_name,tab3.email_address,tab4.email_address,tab5.email_address,tab6.email_address)id ,tab1.bday_day ,tab1.bday_month ,tab1.bday_year ,tab1.gender ,tab1.zipCode ,tab1.siteId ,tab1.userID ,tab2.photo_location ,tab2.photo_name ,tab2.photo_default ,tab2.no_photo ,tab3.headline ,tab3.about_me ,tab4.login_date ,tab4.login_isonline,tab5.up_order,tab6.saved_orderby,tab6.saved_sort,tab6.saved_fage,tab6.saved_tageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)WHEREtab1.gender = @genderAND tab1.country = @countryAND tab1.bday_year BETWEEN @low AND @highAND tab2.photo_default = 1 + @photo--and not tab2.no_photo = 1--firstName + '~' + lastName-->= @fname + '~' + @lnameORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT 0END

View 7 Replies View Related

Date Format In A Merged Field

Jan 4, 2007

In the footer from a report I want to print the UserID and the Date. I added a textbox with de following code: =User!UserID & " " & Globals!ExecutionTime

Now I want to change the date format in dd-MM-yyy uu:mm. This is not possible in the textbox properties because I added the UserID to the same textbox. Is there a way to change the format?

View 5 Replies View Related

Index Was Out Of Range When Two Cells Are Merged

Jul 10, 2007

I receive this error during rendering when I have two cells merged together:

Error Snippet

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

When I "Split Cells" on the offending cells, it starts to work. This report renders without error when run on the RS server. This error only occurs when running the report locally on the "Preview" tab in the report designer.

I have closed the IDE and deleted the *.data files and restarted with the same results. Is there anyway to get more information about the error to help debug the problem?

Thanks!

~Jon

View 2 Replies View Related

Merged Record Order Wierdness

Sep 14, 2007

I'm using the Merge transformation to merge two sorted tables on four sort fields.

It all looks OK until several records share some of the same sort fields.

For example if table 1 contains

Zip Code(sort field 1) Cust Name(sf2) Cust Number(sf3) Record type(sf4)
1234 Bob 444 A
1234 Bob 555 A

and table 2 contains

Zip Code Cust Name Cust Number Record type
1234 Bob 444 B
1234 Bob 555 B

then the output order is

Zip Code Cust Name Cust Number Record type
1234 Bob 444 A
1234 Bob 555 A
1234 Bob 444 B
1234 Bob 555 B

and I would have expected

Zip Code Cust Name Cust Number Record type
1234 Bob 444 A
1234 Bob 444 B

1234 Bob 555 A
1234 Bob 555 B

Both input table are explicitly sorted just prior to the merge.

Any ideas?

View 6 Replies View Related

Reporting Services :: How To Split Merged Cells

Oct 27, 2015

I am trying to split cells that are merged as part of a grouped report. Am using the right-button menu and pressing "Split Cells" when it appears but it just splits into two parts and not into single cells or rows. When I right-button on one of the parts that came out of the split, I do not get a "Split Cells" to break it down further. Want to be able to split the grouped cells into individual cells or rows and have the rows be different colors. How can I do this?

View 4 Replies View Related

Merged Cells Problem In Excel Export

Dec 3, 2007

Hi all,

I have a basic matrix report but whenever I export this report to Excel, some of the cells end up becoming merged. Is there any way to avoid this using Reporting Services instead of formatting the cells directly in Excel?

Any help is greatly appreciated!

View 12 Replies View Related

Is There A Tool For Verifying Data After Merged Access To Mssqlserver

Dec 24, 2007

hi, all.I want to verify data merged from access in sqlserver.but it has toomany records .so i need a tooldo this.someone maybe has it.thanks

View 1 Replies View Related

Select Boolean Values Merged In A Text Statement

Jul 20, 2005

Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -

View 3 Replies View Related

Single Direction Transfer Of Data In Merged Publication (subscriber To Publisher)

May 28, 2008

Hi,
We have an existing merged replication schema that works well. One of the
tables is named audit. Currently this is a bidirectional transfer. However,
we want a new audit table that will transfer data from the subscriber to the
publisher and not the other way round. I have a script that was generated as a backup script when replication was first created. I have no idea which parameter to change in order to have the merge replication going from subscriber to publisher. Any advise is helpful. Thanks.

View 1 Replies View Related

Reporting Services :: SSRS Report Export With Excel - Textboxes Merged

Jul 2, 2015

I have a report in that report having some Text boxes and below tables.But the problem is when i export the report into excel textboxes are merged, not getting properly.

View 5 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View 1 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

Hiding/Showing Columns Based On The Columns Present In The Dataset

Jun 27, 2007

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that??

Any reply showing me the right way is appricited.

-Thanks,

Digs

View 3 Replies View Related

How Dose It Matter For The Non-clustered Index Key Columns And Included Columns?

Apr 24, 2007

Hi, all experts here,

Thanks a lot for your kind attention.

As I am creating the non-clustered indexes for the tables, I dont quite understand how dose it really matter to put the columns in the index key columns or put them into the included columns of the index?

I am really confused about that and I am looking forward to hearing from you and thank you very much again for your advices and help.

With best regards,

Yours sincerely,

View 4 Replies View Related

A Word About Meta-data, Pass Through Columns And Derived Columns

Oct 13, 2006

Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):

Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.

This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.

Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.

Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.

Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS

View 5 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

Matching A View's Columns To It's Underlying Table's Columns

Jul 20, 2005

Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name

View 2 Replies View Related