Charts Reports mssql Select Top Only shows the first 10 items in the database table

Applications Grid / Reports
#1

In the chart application, i have a sql script that selects the top 10 based on quantities. This script woks just fine in the mssql management studio and other sql involvements, The problem is when the charts application runs it will only pick the first 10 items in the database. So i have also tried asc order just to see what would happen. It also takes the first 10 items of the database and only sorts from them. I have tried doing the function in the sql and with in the sorting function of the charts. I have also tried limits which will give me a fatal error which i figure is because that is a mysql function. If i create in a grid i get the same issues.

My 2 questions would be.

  1. Has any one had this issue if so any thoughts?
  2. Or two would this be bug?
    Any help would be grateful.

Thank you

#2

post your code you use to select the data plz

#3

Hi Mike,

here is my mssql script. I believe this is what you are asking for.
Thank you for your help!

SELECT TOP 10
IM_TRANSDETAILS.ITEM AS ITEM,
IM_ITEMFILE.DESCRIPTION,
IM_TRANSDETAILS.TRANSACTION_TYPE AS U,
Left(IM_TRANSDETAILS.GL_PERIOD, 4) AS YEAR,
Sum(IM_TRANSDETAILS.QUANTITY) AS QUANTITY,
IM_ITEMFILE.MATERIAL AS M
FROM
IM_ITEMFILE
INNER JOIN IM_TRANSDETAILS ON IM_ITEMFILE.ITEM = IM_TRANSDETAILS.ITEM
INNER JOIN IM_ITEMFILE_UDF ON IM_ITEMFILE_UDF.ITEM = IM_TRANSDETAILS.ITEM
WHERE
IM_TRANSDETAILS.TRANSACTION_TYPE = ‘U’
GROUP BY
IM_TRANSDETAILS.ITEM,
IM_ITEMFILE.DESCRIPTION,
IM_TRANSDETAILS.TRANSACTION_TYPE,
Left(IM_TRANSDETAILS.GL_PERIOD, 4),
IM_ITEMFILE.DESCRIPTION2,
IM_ITEMFILE_UDF.UDF_PART_NUMBER_CHANGE,
IM_ITEMFILE_UDF.UDF_PART_NUMBER_CHANGE1,
IM_ITEMFILE.MATERIAL
ORDER BY
QUANTITY desc

#4

are you running mySQL?

in the summary section you can set the limit. if you set it to 10 it should give you the top 10 results in the group by.

It’s working for me!

#5

Hi Mike, for this connection we are running msSQL since that is where the data is at.

#6

it helped applying the LIMIT in the summary?