2. PHPRunner General questions
  3. [SOLVED]  Field As Columns
This topic is locked
[SOLVED]

 Field As Columns

 		7/11/2013 7:00:53 PM
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.
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.