Solved

Grid querry results

Posted on 2016-11-01
41
77 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 50

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 50

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

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

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

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
ID: 41869601
Ok, and after this you are sending this table to the Grid for binding correct?
0
 
LVL 50

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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

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

Author Comment

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

Expert Comment

by:Pawan Kumar
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 50

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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

775 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