# KReport Bar Chart Sorting Issue **URL:** \ **Category:** KReporter\ **Created:** [August 16, 2021, 5:45am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621 "2021-08-16T05:45:59Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![offshoreevolution](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.spicecrm.io/offshoreevolution/32/409_2.png) [@offshoreevolution](https://community.spicecrm.io/u/offshoreevolution)\ **Post date:** [August 16, 2021, 5:45am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/1 "2021-08-16T05:45:59Z")
Hi, I have one dropdown field of “Office”. It displays the value as text and DB stores the value integer. How it’s possible to sort on DB value instead of display value. ## Drop Down of Office DB Value ::: Display Value 7 ::: Alliance 43 ::: CANNABIS 42 ::: CAAS 35 ::: CHERRY HILL 2 ::: FINANCE ![screenshot-2021.08.16-11_03_47 (1)](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/5ffe381e3cb4fa55cc0b58bbcd753c764c75a372.png) @maretval Thanks Asif Offshore Evolution Pvt Ltd ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [August 19, 2021, 1:41pm UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/2 "2021-08-19T13:41:29Z")
Is your Kreporter a full version? If yes, you should have the query analyzer under tab integration. Turn it on, save, then you will see the queryanalyzer button in “tools”. Please trigger and copy/paste the query built here. I’d like to see it. Is the office column an integer? ---
**Author:** ![offshoreevolution](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.spicecrm.io/offshoreevolution/32/409_2.png) [@offshoreevolution](https://community.spicecrm.io/u/offshoreevolution)\ **Post date:** [August 20, 2021, 5:18am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/3 "2021-08-20T05:18:40Z")
Hi @maretval Yes, we are using Kreporter a full version. Query > SELECT MIN(chytfbdznnc.id) as sugarRecordId, ‘Accounts’ as “sugarRecordModule” , aebpthxehmp.id as “aebpthxehmpid”, ‘root:Accounts::relate:Accounts:client\_partner\_c’ as “aebpthxehmppath”, jbszyrknxpa.id as “jbszyrknxpaid”, ‘root:Accounts:🔗Accounts:assigned\_user\_link’ as “jbszyrknxpapath”, mqjafbmqryr.id as “mqjafbmqryrid”, ‘root:Accounts:🔗Accounts:opportunities’ as “mqjafbmqryrpath”, chytfbdznnc.id as “chytfbdznncid”, ‘root:Accounts’ as “chytfbdznncpath”, MIN(hpfykjaryxe.office\_c) as “k12c9c53ed4d039a3c795eb459bdf”, (LTRIM(RTRIM(CONCAT(IFNULL(aebpthxehmp.first\_name,“”)," “,IFNULL(aebpthxehmp.last\_name,”“))))) as “kb35d0708b84badd9e576870dd176”, (LTRIM(RTRIM(CONCAT(IFNULL(jbszyrknxpa.first\_name,”“),” “,IFNULL(jbszyrknxpa.last\_name,”"))))) as “k7405f674b7aa440a4b15a12ba5c4”, hpfykjaryxe.client\_id\_c as “k14c6da5a90b61eb35cac7d1d1d08”, MIN(chytfbdznnc.name) as “k085f8f1af6b61565a41ca57f2140”, MIN(chytfbdznnc.industry) as “k6d2bd5137a5c314d1c42d8110222”, SUM(erxabmpzjgb.estimated\_revenue\_c) as “k3203962249783675d86a60909455”, mqjafbmqryr.currency\_id as ‘k3203962249783675d86a60909455\_curid’, MIN(erxabmpzjgb.service\_item\_c) as “ke0826fc8cd51eed051f8d1467afc”, MIN(hpfykjaryxe.referred\_by\_c) as “kc3baadd4468b4d0f8aab86b2342c” > > FROM accounts chytfbdznnc > > LEFT JOIN accounts\_cstm as hpfykjaryxe ON chytfbdznnc.id = hpfykjaryxe.id\_c > > LEFT JOIN users AS aebpthxehmp ON aebpthxehmp.id=hpfykjaryxe.user\_id1\_c > > LEFT JOIN users\_cstm as bqsxesmmrmp ON aebpthxehmp.id = bqsxesmmrmp.id\_c > > LEFT JOIN users jbszyrknxpa ON chytfbdznnc.assigned\_user\_id=jbszyrknxpa.id AND jbszyrknxpa.deleted=0 > > LEFT JOIN users\_cstm as gzdryejcrsn ON jbszyrknxpa.id = gzdryejcrsn.id\_c > > INNER JOIN accounts\_opportunities terkgrqwkbt ON chytfbdznnc.id=terkgrqwkbt.account\_id AND terkgrqwkbt.deleted=0 > > INNER JOIN opportunities mqjafbmqryr ON mqjafbmqryr.id=terkgrqwkbt.opportunity\_id AND mqjafbmqryr.deleted=0 > > LEFT JOIN opportunities\_cstm as erxabmpzjgb ON mqjafbmqryr.id = erxabmpzjgb.id\_c > > WHERE chytfbdznnc.deleted = ‘0’ > > GROUP BY hpfykjaryxe.client\_id\_c > > ORDER BY MIN(hpfykjaryxe.office\_c) ASC “Office” is the Account of the custom “DropDown” field. It is DB data type is varchar. ![screenshot-2021.08.20-10_31_52](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/c35e4adc9e516c6ebbfac3b18b3f9118dd4d830b.png) DropDown Option List ‘7’ =\> ‘Alliance’, ‘43’ =\> ‘Cannabis’, ‘42’ =\> ‘CAAS’, ‘35’ =\> ‘Cherry Hill’, ‘2’ =\> ‘Finanace’, ‘10’ =\> ‘NAPLES’, ‘39’ =\> ‘WEST PALM BEACH’ Report of Graph sorting on DB value of varchar 10(NAPPLES), 35(Cherry Hill), 39(West Palm Beach), 42(CAAS), 43(Cannabis), 7(Alliance) Data is displayed on DropDown Display Value ![screenshot-2021.08.20-10_44_21](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/4da6b38bbc41be3b25298e456dfb6880a37eb413.png) I want the toggle option to search by DropDown display value or search by DB value. ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [September 8, 2021, 8:00am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/4 "2021-09-08T08:00:40Z")
if you convert you string values to integers using a customSQL ```auto CAST({t}.{f} AS UNSIGNED) ``` for office\_c you should get sorting as wished but you will loose the label in display. Only solution I see for now would be to have native integers for your values in the dropdown ---
**Author:** ![offshoreevolution](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.spicecrm.io/offshoreevolution/32/409_2.png) [@offshoreevolution](https://community.spicecrm.io/u/offshoreevolution)\ **Post date:** [September 13, 2021, 5:42am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/5 "2021-09-13T05:42:39Z")
> [@maretval](#): > > `CAST({tc}.{f} AS UNSIGNED)` @maretval I am using the CAST in the office\_c field but it is lost the display value and again sorting issues 0, 10, 35, 39, 42, 43, and 7. ![screenshot-2021.09.13-11_08_29](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/a6e518115c23b44bc877d0afffff49714a4ed25e.png) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [September 13, 2021, 6:06am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/6 "2021-09-13T06:06:21Z")
Then you’ll have to make your office\_c an integer field. Not string. ---
**Author:** ![offshoreevolution](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.spicecrm.io/offshoreevolution/32/409_2.png) [@offshoreevolution](https://community.spicecrm.io/u/offshoreevolution)\ **Post date:** [September 13, 2021, 6:56am UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/7 "2021-09-13T06:56:17Z")
But in the chart sorting as string 0, 7, 10, 35, 39, 42, and 43 but in below data as soring string 0, 35, 39, 42, 43, and 7. ![screenshot-2021.09.13-11_08_29](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/a6e518115c23b44bc877d0afffff49714a4ed25e.png) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [September 27, 2021, 2:42pm UTC](https://community.spicecrm.io/t/kreport-bar-chart-sorting-issue/621/8 "2021-09-27T14:42:28Z")
Looks like presentation grouped view will consider the value as a string. What if you go back to string values for your enum but using leading zeros? 000 007 010 035 and so on?