[SOLVED] Field As Columns

This topic is locked

[SOLVED]  Field As Columns		7/11/2013 19:00:53
PHPRunner General questions
G gdude66 author Hi I have a table with scored results over 4 years. The results have a field cycle which has a year and a cycle as 1 or 2 where the cycle is ordered. As this list will keep growing I would like to display the cycle ordered as columns and the person's name or code as the row. The DB has fields person name, year, cycle, score At the end of the row I would like to calculate the difference between the last 2 results. Can this be handled in php runner and if so could I get some pointers.

Mark as Solved Mark as not solved

6 replies


	Sergey Kornilov admin 7/13/2013
	Can you provide some sort of screenshot that would show data in your database along with required report format?


G	gdude66 author 7/14/2013
	Can you provide some sort of screenshot that would show data in your database along with required report format? I can describe the table. In the table have the following fields id, personcode, subject, area, year, cycle, value 45, per0011, english, reading, 2009, s1, 50 46, per0011, english, writing, 2009, s1, 60 47, per0011, english reading, 2009, s2, 55 2018, per0011, english, reading, 2010, s1, 65 etc There are lots of subjects and lots of areas so each person over the years can have multiple entries under a subject and area. I want to produce a table where the data is transposed to Person, Subject English, reading, writing, value with each year or cycle of data coming out with it. I would also like to produce a line chart with progress over 2 years graphed to show improvement in each cycle. How can thta be done using master detail relationship?


	Sergey Kornilov admin 7/14/2013
	I'm still not 100% sure I understand but check crosstab report feature in PHPRunner.


S	Sitaraman 7/15/2013
	Dear Sergey, Even I came across a similar situation where column headers are dynamically generated and wondered if the resultant table could be added to Phprunner. please check this link I am afraid this cannot be achieved using pivot tables since the field values are not numeric.


G	gdude66 author 7/25/2013
	I'm still not 100% sure I understand but check crosstab report feature in PHPRunner. So in my sql query I use this code SELECT student_id, student_name, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb7' AND student_id = t1.student_id and type = 'Numeracy') AS Feb7, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug7' AND student_id = t1.student_id and type = 'Numeracy') AS Aug7, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb8' AND student_id = t1.student_id and type = 'Numeracy') AS Feb8, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug8' AND student_id = t1.student_id and type = 'Numeracy') AS Aug8, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb9' AND student_id = t1.student_id and type = 'Numeracy') AS Feb9, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug9' AND student_id = t1.student_id and type = 'Numeracy') AS Aug9, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb10' AND student_id = t1.student_id and type = 'Numeracy') AS Feb10, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug10' AND student_id = t1.student_id and type = 'Numeracy') AS Aug10 FROM ondemand AS T1 GROUP BY student_id And I lose all use of query designer says "PHPrunner was unable to parse your query.designer tab is not available" but the output works except it does not display student_id or student_name in the web output but displays in the results tab of query designer. Original table has following fields id student_id student_name test_date score type year test_cycle cycle also trying to join to another table but I keep getting errors Any ideas?


G	gdude66 author 7/27/2013
	So in my sql query I use this code SELECT student_id, student_name, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb7' AND student_id = t1.student_id and type = 'Numeracy') AS Feb7, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug7' AND student_id = t1.student_id and type = 'Numeracy') AS Aug7, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb8' AND student_id = t1.student_id and type = 'Numeracy') AS Feb8, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug8' AND student_id = t1.student_id and type = 'Numeracy') AS Aug8, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb9' AND student_id = t1.student_id and type = 'Numeracy') AS Feb9, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug9' AND student_id = t1.student_id and type = 'Numeracy') AS Aug9, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Feb10' AND student_id = t1.student_id and type = 'Numeracy') AS Feb10, (SELECT ondemand.`score` FROM ondemand WHERE cycle='Aug10' AND student_id = t1.student_id and type = 'Numeracy') AS Aug10 FROM ondemand AS T1 GROUP BY student_id And I lose all use of query designer says "PHPrunner was unable to parse your query.designer tab is not available" but the output works except it does not display student_id or student_name in the web output but displays in the results tab of query designer. Original table has following fields id student_id student_name test_date score type year test_cycle cycle also trying to join to another table but I keep getting errors Any ideas? Solved using sum and avg query.

6 replies

Mark as Solved Reply