Solved

Grid querry results

Posted on 2016-11-01
41
54 Views
Last Modified: 2016-11-04
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
Comment
Question by:Peter Kiprop
  • 18
  • 16
  • 5
  • +2
41 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41868099
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868102
yes there are spaces in between, and i want to maintain them
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41868106
you may also consider to put the "label" and "value" into 2 columns, so that they can be aligned easily.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868107
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868110
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868124
While selecting data use below.. this will do it.

LTRIM(RTRIM( Col after splliting ))
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868126
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868219
This hasn't worked. Anyone with a different opinion. am using crystal sap 2013 for report. could this also be creating an issue?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868225
So are not getting spaces in the front or at the end from the SQL Query output ..?
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868234
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41868343
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868352
Can you please post the background SQL query you are using ?
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868377
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868386
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868409
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868428
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41868783
EarningsName      Amount
Basic Pay             26525.00
Leave Allowance    2500.00
Airtime Allowance  5000.00
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41868823
Is this the output of #rTmpPaySlip?

If Yes please try below

SELECT  EarningsName    , CAST(Amount AS DECIMAL(10,2)) Amount  FROM  #rTmpPaySlip
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41869597
No this is the data am inserting to  #rTmpPaySlip
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41869601
Ok, and after this you are sending this table to the Grid for binding correct?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41869610
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41869612
very true
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41869613
Can you please post the SQL Query you are binding to the Grid... We need to modify that to make 2 different columns.
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41869779
No am not binding this to a grid. it is script to generate data for a report (crystal report 2013)
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41869780
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41869875
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41869892
Okies, Got One idea working on it.
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41869898
am waiting
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41869899
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
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41869904
@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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41869906
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871601
Hi Peter Kiprop,

Have you tried my suggestion.?

Regards,
Pawan
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41871912
Yes i did and it didn't help
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871914
Are you getting the same issue?
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41871915
@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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871940
So whats the plan in mind? May be change in Crystal report.
0
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41871972
may be add two columns as suggested by Ryan and concatenate on cross tab then play around with the width.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871973
Yes two columns will help. Good luck !
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41873282
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 41873325
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
 
LVL 6

Author Comment

by:Peter Kiprop
ID: 41875158
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now