• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

Grid querry results

I have a problem When i insert data into a field of same length, when i select the results in grid view they are not align correctly but when i select results to text they are a well aligned.

what could be causing this and how can i get them corrrectly aligned?

the kind of data am inserting are like 'Personal Account 20,000.00' They are are all of lenght of 55.
see sample below
gridresults.pngSQL 2005 & 2008
0
Peter Kiprop
Asked:
Peter Kiprop
  • 18
  • 16
  • 5
  • +2
1 Solution
 
Ryan ChongCommented:
how's your original data looks like? is it contain with carriage returns, tabs, spaces etc? you may consider to trim them in your query.
0
 
Peter KipropAuthor Commented:
yes there are spaces in between, and i want to maintain them
0
 
Ryan ChongCommented:
you may also consider to put the "label" and "value" into 2 columns, so that they can be aligned easily.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Pawan KumarDatabase ExpertCommented:
Hi Peter,

Seems like you have Char/Varchar data type.

Could you please post the Insert statement and the Table Schema?

Thank You,
Pawan
0
 
Peter KipropAuthor Commented:
here they are

Create Table #rTmpPaySlip (                  
   PaySlipNo char(6), Slno Int,                  
   Desc1 VARCHAR(55), ColOrder INT,                  
   GrpCol INT)

  insert into #rTmpPaySlip(PaySlipNo,slno,Desc1)                                    
     Values(@PaySlipNo,@Slno,'Earnings/Payments'+Replicate(' ',@Left-len('Earnings/Payments')))
0
 
Pawan KumarDatabase ExpertCommented:
While selecting data use below.. this will do it.

LTRIM(RTRIM( Col after splliting ))
0
 
Pawan KumarDatabase ExpertCommented:
Try...

SELECT SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) a , RTRIM(LTRIM(SUBSTRING(VAL,CHARINDEX(' ',VAL),DATALENGTH(VAL)))) b
FROM DAT

--

CREATE TABLE DAT
(
	VAL VARCHAR(100)
)
GO

INSERT INTO DAT VALUES ('PersonalAccount 20,000.00') 
GO

SELECT SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) a , RTRIM(LTRIM(SUBSTRING(VAL,CHARINDEX(' ',VAL),DATALENGTH(VAL)))) b
FROM DAT


SELECT SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) a , SUBSTRING(VAL,CHARINDEX(' ',VAL),DATALENGTH(VAL)) b
FROM DAT

--

Open in new window


O/p for SELECT SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) a , RTRIM(LTRIM(SUBSTRING(VAL,CHARINDEX(' ',VAL),DATALENGTH(VAL)))) b
FROM DAT


a                                 b
PersonalAccount      20,000.00


O/p for SELECT SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) a , SUBSTRING(VAL,CHARINDEX(' ',VAL),DATALENGTH(VAL)) b
FROM DAT

a                                 b
PersonalAccount         20,000.00

EE
0
 
Peter KipropAuthor Commented:
This hasn't worked. Anyone with a different opinion. am using crystal sap 2013 for report. could this also be creating an issue?
0
 
Pawan KumarDatabase ExpertCommented:
So are not getting spaces in the front or at the end from the SQL Query output ..?
0
 
Peter KipropAuthor Commented:
what i expect is a result in grid like below. i get the result i need when i select results to text
results-to-text.JPG
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just to 'check the box' it is a poor programming practice to combine columns of different data types in a single column.  Same goes for leading/trailing spaces when those really do not have any value.  Especially if a user would want math to be performed on one of them.

So ... why are these two values in one column, separated by spaces?

I highly recommend splitting these into two columns, without the leading/trailing spaces, with the amount in a numeric data type.  That way the reporting tool can easily format the amount.
0
 
Pawan KumarDatabase ExpertCommented:
Can you please post the background SQL query you are using ?
0
 
Peter KipropAuthor Commented:
DECLARE Employe_Earns CURSOR FOR  
SELECT EarningsName,Amount
FROM CalcEarnTran A inner join  Earnings B on A.EarningID=B.EarningsID
WHERE  CalcYear=@CalcYear and CalcMonth=@CalcMonth and EmployeeNo=@CEmployeeNo
set @ialign=@ialign + 1
OPEN Employe_Earns
FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @ColWidth=55
      set  @spacbtw=0
        set @spactobeRep=0
        Set @Slno =@Slno + 1
        set @spacbtw=len(@EarningName)  +  len(CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1) )
      Set @spactobeRep=@ColWidth - @spacbtw
       --set @NewDesc = @EarningName  +  Replicate(' ',@spactobeRep) +  CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1)
        ---print @NewDesc
        Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1,Desc2)
            Values ( @PaySlipNo,@Slno,@EarningName,CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1))
       --inser second row
    FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  
END
0
 
Pawan KumarDatabase ExpertCommented:
You are using this temp table to bind to the grid. ?

TRy...replacing

1...

Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1,Desc2)
Values ( @PaySlipNo,@Slno,@EarningName,CAST(@Amount AS DECIMAL(20,8)),1)

2....

Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1,Desc2)
Values ( @PaySlipNo,@Slno,@EarningName,CAST(@Amount AS MONEY),1)
0
 
Peter KipropAuthor Commented:
sorry this should be the correct script
DECLARE Employe_Earns CURSOR FOR  
SELECT EarningsName,Amount
FROM CalcEarnTran A inner join  Earnings B on A.EarningID=B.EarningsID
WHERE  CalcYear=@CalcYear and CalcMonth=@CalcMonth and EmployeeNo=@CEmployeeNo
set @ialign=@ialign + 1
OPEN Employe_Earns
FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @ColWidth=55
      set  @spacbtw=0
        set @spactobeRep=0
        Set @Slno =@Slno + 1
        set @spacbtw=len(@EarningName)  +  len(CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1) )
      Set @spactobeRep=@ColWidth - @spacbtw
        set @NewDesc = @EarningName  +  Replicate(' ',@spactobeRep) +  CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1)
        ---print @NewDesc
        Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1)
            Values ( @PaySlipNo,@Slno,@NewDesc)
       --inser second row
    FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  
END
0
 
Pawan KumarDatabase ExpertCommented:
You are using Varchar thats why it is not working...

Could you please post data(2 -3 rows) from table  #rTmpPaySlip

We can do manipulations here also..
0
 
Peter KipropAuthor Commented:
EarningsName      Amount
Basic Pay             26525.00
Leave Allowance    2500.00
Airtime Allowance  5000.00
0
 
Pawan KumarDatabase ExpertCommented:
Is this the output of #rTmpPaySlip?

If Yes please try below

SELECT  EarningsName    , CAST(Amount AS DECIMAL(10,2)) Amount  FROM  #rTmpPaySlip
0
 
Peter KipropAuthor Commented:
No this is the data am inserting to  #rTmpPaySlip
0
 
Pawan KumarDatabase ExpertCommented:
Ok, and after this you are sending this table to the Grid for binding correct?
0
 
Ryan ChongCommented:
Set @spactobeRep=@ColWidth - @spacbtw

        set @NewDesc = @EarningName  +  Replicate(' ',@spactobeRep) +  CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1)
       

you will Never get the description output to be aligned correctly by just comparing to the character's length. Just imagine the character's width of "W" and "I" is different. as already emphasized by me and Jim, you should create 2 columns instead of putting all into into 1 column.
0
 
Peter KipropAuthor Commented:
very true
0
 
Pawan KumarDatabase ExpertCommented:
Can you please post the SQL Query you are binding to the Grid... We need to modify that to make 2 different columns.
0
 
Peter KipropAuthor Commented:
No am not binding this to a grid. it is script to generate data for a report (crystal report 2013)
0
 
Pawan KumarDatabase ExpertCommented:
Is below the script you are using ?

DECLARE Employe_Earns CURSOR FOR  
SELECT EarningsName,Amount
FROM CalcEarnTran A inner join  Earnings B on A.EarningID=B.EarningsID
WHERE  CalcYear=@CalcYear and CalcMonth=@CalcMonth and EmployeeNo=@CEmployeeNo
set @ialign=@ialign + 1
OPEN Employe_Earns 
FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @ColWidth=55
      set  @spacbtw=0
        set @spactobeRep=0
        Set @Slno =@Slno + 1
        set @spacbtw=len(@EarningName)  +  len(CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1) ) 
      Set @spactobeRep=@ColWidth - @spacbtw 
       --set @NewDesc = @EarningName  +  Replicate(' ',@spactobeRep) +  CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1)
        ---print @NewDesc
        Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1,Desc2)
            Values ( @PaySlipNo,@Slno,@EarningName,CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1))
       --inser second row
    FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  
END

Open in new window

0
 
Peter KipropAuthor Commented:
No. this is the correct one
DECLARE Employe_Earns CURSOR FOR  
SELECT EarningsName,Amount
FROM CalcEarnTran A inner join  Earnings B on A.EarningID=B.EarningsID
WHERE  CalcYear=@CalcYear and CalcMonth=@CalcMonth and EmployeeNo=@CEmployeeNo
set @ialign=@ialign + 1
OPEN Employe_Earns
FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @ColWidth=55
      set  @spacbtw=0
        set @spactobeRep=0
        Set @Slno =@Slno + 1
        set @spacbtw=len(@EarningName)  +  len(CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1) )
      Set @spactobeRep=@ColWidth - @spacbtw
        set @NewDesc = @EarningName  +  Replicate(' ',@spactobeRep) +  CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1)
        ---print @NewDesc
        Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1)
            Values ( @PaySlipNo,@Slno,@NewDesc)
       --inser second row
    FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  
END 

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Okies, Got One idea working on it.
0
 
Peter KipropAuthor Commented:
am waiting
0
 
Pawan KumarDatabase ExpertCommented:
Try this sir...it is working for me.

--

CREATE TABLE DAT
(
	VAL VARCHAR(100)
)
GO

INSERT INTO DAT VALUES ('PersonalAccount 20,000.00')
INSERT INTO DAT VALUES ('Pawan 20,000.00')
GO

DECLARE @M AS INT = 0
SELECT @M = DATALENGTH(MAX(SUBSTRING(VAL,0,CHARINDEX(' ',VAL))))
FROM DAT
SELECT @M

SELECT CASE WHEN DATALENGTH(SUBSTRING(VAL,0,CHARINDEX(' ',VAL))) < @M THEN SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) + SPACE(@M-DATALENGTH(SUBSTRING(VAL,0,CHARINDEX(' ',VAL)))) 
ELSE SUBSTRING(VAL,0,CHARINDEX(' ',VAL)) END + SPACE(5) + RTRIM(LTRIM(SUBSTRING(VAL,CHARINDEX(' ',VAL),DATALENGTH(VAL)))) b
FROM DAT


--

Open in new window


O/P

EE
Hope it helps...
0
 
Ryan ChongCommented:
@Peter,

Any issue using the codes you self-posted in ID: 41868377 ?

Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1,Desc2 , ColOrder)
            Values ( @PaySlipNo,@Slno,@EarningName,CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1))
(with minor amendment)

change your existing codes to:

Create Table #rTmpPaySlip (                  
   PaySlipNo char(6), Slno Int,                  
   Desc1 VARCHAR(55), Desc2 VARCHAR(55), ColOrder INT,                  
   GrpCol INT) 

Open in new window

then it seems will work.
0
 
Pawan KumarDatabase ExpertCommented:
Full Code for you to try.....................Replace your code block with the below one.

DECLARE Employe_Earns CURSOR FOR  
SELECT EarningsName,Amount
FROM CalcEarnTran A inner join  Earnings B on A.EarningID=B.EarningsID
WHERE  CalcYear=@CalcYear and CalcMonth=@CalcMonth and EmployeeNo=@CEmployeeNo
set @ialign=@ialign + 1
OPEN Employe_Earns
FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @ColWidth=55
      set  @spacbtw=0
        set @spactobeRep=0
        Set @Slno =@Slno + 1
        set @spacbtw=len(@EarningName)  +  len(CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1) )
      Set @spactobeRep=@ColWidth - @spacbtw
        set @NewDesc = @EarningName  +  Replicate(' ',@spactobeRep) +  CONVERT(VARCHAR(50),CAST(@Amount AS MONEY),1)
        ---print @NewDesc		
		
        Insert into #rTmpPaySlip(PaySlipNo,slno,Desc1)
        VALUES ( @PaySlipNo,@Slno,@NewDesc)
		
       --inser second row
    FETCH NEXT FROM Employe_Earns INTO @EarningName,@Amount  
END

DECLARE @M AS INT = 0
SELECT @M = DATALENGTH(MAX(SUBSTRING(Desc1,0,CHARINDEX(' ',Desc1))))
FROM #rTmpPaySlip
SELECT @M

SELECT PaySlipNo,slno , CASE WHEN DATALENGTH(SUBSTRING(Desc1,0,CHARINDEX(' ',Desc1))) < @M THEN SUBSTRING(Desc1,0,CHARINDEX(' ',Desc1)) + SPACE(@M-DATALENGTH(SUBSTRING(Desc1,0,CHARINDEX(' ',Desc1)))) 
ELSE SUBSTRING(Desc1,0,CHARINDEX(' ',Desc1)) END + SPACE(5) + RTRIM(LTRIM(SUBSTRING(Desc1,CHARINDEX(' ',Desc1),DATALENGTH(Desc1)))) Desc1
FROM #rTmpPaySlip

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Hi Peter Kiprop,

Have you tried my suggestion.?

Regards,
Pawan
0
 
Peter KipropAuthor Commented:
Yes i did and it didn't help
0
 
Pawan KumarDatabase ExpertCommented:
Are you getting the same issue?
0
 
Peter KipropAuthor Commented:
@Ryan,

this issue i have on that is that am using crosstab on crystal report and becomes difficult to have two fields unless and concatenate on the report thus gives me thesame problem
0
 
Pawan KumarDatabase ExpertCommented:
So whats the plan in mind? May be change in Crystal report.
0
 
Peter KipropAuthor Commented:
may be add two columns as suggested by Ryan and concatenate on cross tab then play around with the width.
0
 
Pawan KumarDatabase ExpertCommented:
Yes two columns will help. Good luck !
0
 
Ryan ChongCommented:
I'm not expert in Crystal Reports but I have added this tag in your question.

and you may request for an attention ("Request Attention" option) so to ask more experts for help. cheers
0
 
mlmccCommented:
Crystal doesn't have a grid format.  What tool are you using?

Have you tried using a fixed space font like Courier?
Proportional fonts can't be aligned as you want.

If this is crystal then the only solution is to use separate fields aligned as desired.

mlmcc
0
 
Peter KipropAuthor Commented:
Thanks mlmcc, I have actually changed the font to Courier New and set horizontal alignment to Justified in crystal reports and it sorted my problem. The only change made on the script is to make the length of the data to be less the field length( in my case i made the colwidth to 45 while the field lenght is 55)

Thanks to all for the contributions. i have learned alot from you guys.

Peter
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 18
  • 16
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now