Issue with Repeated Columns.

[#1]

I have a table like this

ContentID,Grade,noofattempts,matherror1,matherror2,matherror3,scienceerror1,scienceerror2,scienceerror3,date_created

Data will be as follows

222,1stgrade,25,0,8,2,0,0,0,'12/13/2012'

240,1stgrade,20,2,5,2,0,0,0,'12/13/2012'

253,1stgrade,30,0,8,2,0,0,0,'12/13/2012'

333,2ndgrade,96,0,8,2,0,4,2,'12/13/2012'

696,2ndgrade,22,88,8,2,59,0,0,'12/13/2012'

888,4thgrade,0,15,8,2,0,0,69,'12/13/2012'

256,4thgrade,25,0,8,2,0,0,0,'12/13/2012'

The users want a pivot table the following way.

In addition the user wants to show the descriptions for each error at the bottom

matherror1 - Error in Maths during first quarter

matherror2 - Error in Maths during second quarter

Also on the left I have to show all the errorcodes as a column.

ErrorCode

matherror1

matherror2

matherror3

The main problem I am facing

with all this is that these are not values in the table. They are defined as columns.I tried to Unpivot the columns into rows. Then the data is wrong as it is summing multiple rows of the same data.

Have you run into scenario like this before? Please let me know if I was able to explain my issue here.

[fosuzuki · Accepted solution]

Check the attached sample.

Hope this helps you

Fernando

[#3]

Thanks for your response.

I modifed the table like this

ContentID,Grade,noofattempts,ErrorCode,ErrorCount,datecreated

matherror1,matherror2,matherror3,scienceerror1,scienceerror2,scienceerror3,date_created

Data will be as follows

222,1stgrade,25,matherror1,0,'12/13/2012'

222,1stgrade,25,matherror2,8,'12/13/2012'

222,1stgrade,15,matherror3,2,'12/13/2012'

222,1stgrade,20,scienceerror1,0,'12/13/2012'

222,1stgrade,25,scienceerror2,2,'12/13/2012'

222,2ndgrade,6,matherror1,3,'12/13/2012'

222,2ndgrade,25,matherror2,0,'12/13/2012'

222,2ndgrade,25,matherror3,2,'12/13/2012'

222,2ndgrade,25,scienceerror1,5,'12/13/2012'

222,2ndgrade,7,scienceerror2,2,'12/13/2012'

240,1stgrade,25,matherror1,0,'12/13/2012'

240,1stgrade,25,matherror2,8,'12/13/2012'

240,1stgrade,15,matherror3,2,'12/13/2012'

240,1stgrade,20,scienceerror1,0,'12/13/2012'

240,1stgrade,25,scienceerror2,2,'12/13/2012'

240,2ndgrade,6,matherror1,3,'12/13/2012'

240,2ndgrade,25,matherror2,0,'12/13/2012'

240,2ndgrade,25,matherror3,2,'12/13/2012'

240,2ndgrade,25,scienceerror1,5,'12/13/2012'

240,2ndgrade,7,scienceerror2,2,'12/13/2012'

The way I implemented the above problem is by first using a pivot table. I defined Grade as the Dimension and the rest as expressions.

Ex:

sum of MathError1

sum of MathError1 is defined as

({$<[ErrorCode]={'MathError1'}>} ErrorCount) 

sum of MathError2

sum of MathError2 is defined as

({$<[ErrorCode]={'MathError2'}>} ErrorCount

All the calculations are correct. But the problem I am running into is that I am not able to drill down to the particular Error Code when the users clicks on the data.

For ex: when the user clicks on the sum of sum of Science error1 it is not knowing to drill down to just that particular error. I can understand ithat it is not able to figure out how to get to this calculation. Do you have any better idea as to how I can implement this.

[fosuzuki]

if you want to drill down on the ErrorCode, you must use the ErrorCode as a Dimension in your pivot table.