Jump to content
TNG Community
DaveInNE

Can't customize report column names from SQL statement

Recommended Posts

DaveInNE

Hi all,

I just installed TNG to a test server in my home today so that I can play around with it prior to going live. After my usual pains with getting Linux file permissions correct, I'm up and running.

I tried creating a report using a SQL query, but it appears that perhaps not all SQL syntax is supported? I've attached two screenshots which include the SQL queries attempted.

This first report properly displays the names and counts. There are 1634 different last names in this file.

 56a4067e98955_Reportwithnames.png.b153b8

 

However, if I try to name the columns as in the following query, it leaves the rows blank (but notice that it does show the correct number of rows). This query works fine from the MySQL command line.

56a406eb60423_Reportwithoutnames.png.375

 

Just curious, did TNG ever support naming of columns from the SQL statement using "SELECT column AS 'name'" syntax?  I did notice that TNG adds "Limit 50" to the end of the SQL statement for pagination, so I can't really limit it to the top 100 names (at least without some more thought, trial, and error).

None of this is a big deal, I'm just trying to learn as much as I can, including any limitations. I'm loving what I'm seeing thus far! And, this will give me an excuse to learn a little PHP along the way.

Thanks,

Dave

Share this post


Link to post
Share on other sites
beckwith

I've seen this and never figured out why. However, I found the following works:

select lastname as LastName, count(*) as Count from tng_people group by lastname order by count(*) desc, lastname

Share this post


Link to post
Share on other sites
DaveInNE

Got it, thanks for the reply. My guess is that the quotation characters get lost in translation somewhere between JavaScript to PHP to MySQL. I don't have any direct experience in those languages, but I have seen similar hiccups going between other languages (e.g. Python to Java and back). Other than not being able to put whitespace in report column names, it's not a problem for me.

One final note, I ran a few more tests based on your suggestion, and found that the MySQL query in the report config page will treat XXX in the syntax "select column as XXX" as text unless it matches a column name in a table, in which case it will display whatever label is associated with the column. (Apologies if my SQL terminology is incorrect, I'm not a database programmer...)  The behavior can be seen in the following screenshot. If I run the query from the MySQL command line, it always treats XXX as text even without the quotes.

56a4410c347d6_withlabel.png.86a596287fee

Thanks again for the quick response.

Share this post


Link to post
Share on other sites
beckwith

Like you I'm only guessing, but this probably allows better multi-language support which is a TNG strength.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×