Crosstab report!

crosstab.xml

<?xml version="1.0" encoding="utf-8" ?>
<Report xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	guid="26785108-8124-4350-BAFA-1A20EE32762A">
	<ReportInfo>
		<ReportName>Report Card Crosstab Report</ReportName>
		<Description />
		<ReportType>XSL</ReportType>
		<ReportExportFormat>PDF</ReportExportFormat>
		<ReportFile>/Reports/XSLReportFiles/CUSTOM/Crosstab.xrp</ReportFile>
		<RemoveDuplicates>true</RemoveDuplicates>
		<HasCachedInfo>false</HasCachedInfo>
		<ReportPermissions>
			<PersonType>A</PersonType>
			<PersonType>T</PersonType>
			<AdministratorAccessCode>NONE</AdministratorAccessCode>
		</ReportPermissions>
	</ReportInfo>
	<Parameters>
	</Parameters>
	<Selections>
		<RequiredSelections>
			<Selection>
				<SelectionEntity>TRACK</SelectionEntity>
				<IsSingleSelect>true</IsSingleSelect>
			</Selection>
			<Selection>
				<SelectionEntity>REPORTING_PERIOD</SelectionEntity>
				<IsSingleSelect>true</IsSingleSelect>
			</Selection>
			<Selection>
				<SelectionEntity>SCHOOL</SelectionEntity>
				<IsSingleSelect>true</IsSingleSelect>
			</Selection>
			<Selection>
				<SelectionEntity>SCHOOLBOARD</SelectionEntity>
				<IsSingleSelect>true</IsSingleSelect>
			</Selection>
		</RequiredSelections>
		<BasicSelections>
			<Selection>
				<SelectionEntity>TEACHER</SelectionEntity>
				<IsSingleSelect>false</IsSingleSelect>
			</Selection>
			<Selection>
				<SelectionEntity>TEACHER_CLASS</SelectionEntity>
				<IsSingleSelect>false</IsSingleSelect>
			</Selection>
		</BasicSelections>
	</Selections>
	<Query>
		<Sql>
			CREATE TABLE #temp_TeacherClass
			(
			VALUE		INT,
			REPEATER	INT
			)
			EXEC sasel_sp_entityselection_Fill_Selected_Target_Entity_Ids
			@IN_SELECTION_ID,
			'TEACHER_CLASS',
			NULL,
			'#temp_TeacherClass',
			0


			CREATE TABLE #temp_ReportingPeriod
			(
			VALUE		INT,
			REPEATER	INT
			)
			EXEC sasel_sp_entityselection_Fill_Selected_Target_Entity_Ids
			@IN_SELECTION_ID,
			'REPORTING_PERIOD',
			NULL,
			'#temp_ReportingPeriod',
			0



			--TEACHER CLASS PAIR
			select
			SA_TCP.TEACHER_CLASS_ID as 'TCID',
			ISNULL(SA_TCP.LABEL,(SA_TCP.CLASS_ID + '-' + SA_TCP.CLASS_SECTION)) as 'LABEL',
			SA_VT.PROPER_NAME as 'TNAME',
			SA_SRP.SCHOOL_REPORTING_PERIOD_NAME as 'RPNAME'
			from SA_TEACHER_CLASS_PR SA_TCP
			join SA_VW_TEACHER SA_VT
			on SA_TCP.TEACHER_ID = SA_VT.TEACHER_ID
			join #temp_TeacherClass TEMP_TC
			on SA_TCP.TEACHER_CLASS_ID = TEMP_TC.VALUE
			join #temp_ReportingPeriod TEMP_RP	--look up cross join
			on TEMP_RP.VALUE = TEMP_RP.VALUE
			join SA_SCHOOL_REPORTING_PERIOD SA_SRP
			on 	SA_SRP.SCHOOL_REPORTING_PERIOD_ID = TEMP_RP.VALUE


			--STUDENT
			select
			SA_SCP.TEACHER_CLASS_ID as 'TCID',
			SA_S.STUDENT_UNIQUE_ID as 'SUID',
			SA_P.LAST_NAME + ', ' + SA_P.FIRST_NAME as 'STUDENT'
			from SA_STUDENT_CLASS_PR SA_SCP
			join SA_STUDENT SA_S
			on SA_S.STUDENT_ID = SA_SCP.STUDENT_ID
			join SA_PERSON SA_P
			on SA_P.PERSON_ID = SA_S.PERSON_ID
			join #temp_TeacherClass TEMP_TC
			on SA_SCP.TEACHER_CLASS_ID = TEMP_TC.VALUE
			where SA_SCP.ACTIVE = 1
			order by SA_P.LAST_NAME, SA_P.FIRST_NAME


			--STUDENT ASSESSMENT OBJECT
			select
			SA_SCP.TEACHER_CLASS_ID as 'TCID',
			SA_S.STUDENT_UNIQUE_ID as 'SUID',
			SAAO_V_ATR.ASSESSMENT_OBJECT_ID as 'AOID',
			SAAO_AO.ASSESSMENT_OBJECT_NAME as 'AO_NAME'
			from SA_STUDENT_CLASS_PR SA_SCP
			join saao_vw_assessmentobject_teacherclass_reportingperiod SAAO_V_ATR
			on SA_SCP.TEACHER_CLASS_ID = SAAO_V_ATR.TEACHER_CLASS_ID
			join SAAO_ASSESSMENT_OBJECT SAAO_AO
			on SAAO_AO.ASSESSMENT_OBJECT_ID = SAAO_V_ATR.ASSESSMENT_OBJECT_ID
			join SA_STUDENT SA_S
			on SA_S.STUDENT_ID = SA_SCP.STUDENT_ID
			join #temp_TeacherClass TEMP_TC
			on SA_SCP.TEACHER_CLASS_ID = TEMP_TC.VALUE
			join #temp_ReportingPeriod TEMP_RP	--look up cross join
			on TEMP_RP.VALUE = SAAO_V_ATR.SCHOOL_REPORTING_PERIOD_ID
			where saao_v_atr.ACTIVE = 1
			order by SAAO_AO.DISPLAY_GROUP_ID, SAAO_AO.SORT_ORDER, SAAO_AO.ASSESSMENT_OBJECT_NAME


			--STUDENT ASSESEMENT ITEMS
			select
			SAAO_V_ATR.TEACHER_CLASS_ID as 'TCID',
			SA_S.STUDENT_UNIQUE_ID as 'SUID',
			SAAO_V_ATR.ASSESSMENT_OBJECT_ID as 'AOID',
			SAAO_AI.ASSESSMENT_ITEM_ID as 'AIID',
			AIICP.TEXT_COMMENT_BANK AS IS_COMMENT
			from saao_vw_assessmentobject_teacherclass_reportingperiod SAAO_V_ATR
			join SAAO_ASSESSMENT_ITEM SAAO_AI
			on SAAO_AI.ASSESSMENT_OBJECT_ID = SAAO_V_ATR.ASSESSMENT_OBJECT_ID
			join SAAO_ASSESSMENT_ITEM_INPUT_CONFIG AIIC
			ON AIIC.ASSESSMENT_ITEM_ID = SAAO_AI.ASSESSMENT_ITEM_ID
			join SAAO_ASSESSMENT_ITEM_INPUT_CONFIG_PRESETS AIICP
			ON AIICP.PRESET_ID = AIIC.PRESET_ID
			join SA_STUDENT_CLASS_PR SA_SCP
			on SA_SCP.TEACHER_CLASS_ID = SAAO_V_ATR.TEACHER_CLASS_ID
			join SA_STUDENT SA_S
			on SA_S.STUDENT_ID = SA_SCP.STUDENT_ID
			join #temp_TeacherClass TEMP_TC
			on SAAO_V_ATR.TEACHER_CLASS_ID = TEMP_TC.VALUE
			join #temp_ReportingPeriod TEMP_RP	--look up cross join
			on SAAO_V_ATR.SCHOOL_REPORTING_PERIOD_ID = TEMP_RP.VALUE
			where saao_v_atr.ACTIVE = 1
			and SAAO_AI.ASSESSMENT_ITEM_TYPE IS NULL
			order by SAAO_AI.SORTORDER


			--STUDENT MARKS
			select
			SAAO_V_ATR.TEACHER_CLASS_ID as 'TCID',
			SA_S.STUDENT_UNIQUE_ID as 'SUID',
			SAAO_V_ATR.ASSESSMENT_OBJECT_ID as 'AOID',
			SAAO_AI.ASSESSMENT_ITEM_ID as 'AIID',
			SAAO_V_ASR.RESULT as 'VALUE'
			from saao_vw_assessmentobject_teacherclass_reportingperiod SAAO_V_ATR
			join SA_STUDENT_CLASS_PR SA_SCP
			on SA_SCP.TEACHER_CLASS_ID = SAAO_V_ATR.TEACHER_CLASS_ID
			join SA_STUDENT SA_S
			on SA_SCP.STUDENT_ID = SA_S.STUDENT_ID
			join saao_vw_assessmentobject_student_results SAAO_V_ASR
			on SA_S.STUDENT_UNIQUE_ID = SAAO_V_ASR.STUDENT_UNIQUE_ID
			and SAAO_V_ATR.LINK_ID = SAAO_V_ASR.LINK_ID
			join SAAO_ASSESSMENT_ITEM SAAO_AI
			on SAAO_AI.ASSESSMENT_ITEM_ID = SAAO_V_ASR.ASSESSMENT_ITEM_ID
			join #temp_TeacherClass TEMP_TC
			on SAAO_V_ATR.TEACHER_CLASS_ID = TEMP_TC.VALUE
			join #temp_ReportingPeriod TEMP_RP	--look up cross join
			on SAAO_V_ATR.SCHOOL_REPORTING_PERIOD_ID = TEMP_RP.VALUE
			where saao_v_atr.ACTIVE = 1


			--class assessment object
			select
			SAAO_V_ATR.TEACHER_CLASS_ID as 'TCID',
			SAAO_V_ATR.ASSESSMENT_OBJECT_ID as 'AOID',
			SAAO_AO.ASSESSMENT_OBJECT_NAME as 'NAME'
			from saao_vw_assessmentobject_teacherclass_reportingperiod SAAO_V_ATR
			join SAAO_ASSESSMENT_OBJECT SAAO_AO
			on SAAO_AO.ASSESSMENT_OBJECT_ID = SAAO_V_ATR.ASSESSMENT_OBJECT_ID
			join #temp_TeacherClass TEMP_TC
			on SAAO_V_ATR.TEACHER_CLASS_ID = TEMP_TC.VALUE
			join #temp_ReportingPeriod TEMP_RP	--look up cross join
			on SAAO_V_ATR.SCHOOL_REPORTING_PERIOD_ID = TEMP_RP.VALUE
			where saao_v_atr.ACTIVE = 1
			order by SAAO_AO.DISPLAY_GROUP_ID, SAAO_AO.SORT_ORDER, SAAO_AO.ASSESSMENT_OBJECT_NAME


			--class assessment item
			select
			SAAO_V_ATR.TEACHER_CLASS_ID as 'TCID',
			SAAO_V_ATR.ASSESSMENT_OBJECT_ID as 'AOID',
			SAAO_AI.ASSESSMENT_ITEM_ID as 'AIID',
			SAAO_AI.ABREV as 'NAME',
			AIICP.TEXT_COMMENT_BANK AS 'IS_COMMENT'
			from saao_vw_assessmentobject_teacherclass_reportingperiod SAAO_V_ATR
			join SAAO_ASSESSMENT_ITEM SAAO_AI
			on SAAO_AI.ASSESSMENT_OBJECT_ID = SAAO_V_ATR.ASSESSMENT_OBJECT_ID
			join SAAO_ASSESSMENT_ITEM_INPUT_CONFIG AIIC
			ON AIIC.ASSESSMENT_ITEM_ID = SAAO_AI.ASSESSMENT_ITEM_ID
			join SAAO_ASSESSMENT_ITEM_INPUT_CONFIG_PRESETS AIICP
			ON AIICP.PRESET_ID = AIIC.PRESET_ID
			join #temp_TeacherClass TEMP_TC
			on SAAO_V_ATR.TEACHER_CLASS_ID = TEMP_TC.VALUE
			join #temp_ReportingPeriod TEMP_RP	--look up cross join
			on SAAO_V_ATR.SCHOOL_REPORTING_PERIOD_ID = TEMP_RP.VALUE
			where saao_v_atr.ACTIVE = 1
			and SAAO_AI.ASSESSMENT_ITEM_TYPE IS NULL
			order by SAAO_AI.SORTORDER


			DROP TABLE #temp_TeacherClass
			DROP TABLE #temp_ReportingPeriod
		</Sql>
		<DataTables>
			<DataTable>
				<TableName>TC</TableName>
				<ContainsRawData>false</ContainsRawData>
				<RawDataIdColumn />
				<RawDataValueColumn />
			</DataTable>
				<DataTable>
					<TableName>S</TableName>
					<ContainsRawData>false</ContainsRawData>
					<RawDataIdColumn />
					<RawDataValueColumn />
					<ForeignKeys>
						<ForeignKey>
							<PrimaryKeyTable>TC</PrimaryKeyTable>
							<PrimaryKeyColumn ForeignKeyColumn="TCID">TCID</PrimaryKeyColumn>
						</ForeignKey>
					</ForeignKeys>
				</DataTable>
					<DataTable>
						<TableName>SAO</TableName>
						<ContainsRawData>false</ContainsRawData>
						<RawDataIdColumn />
						<RawDataValueColumn />
						<ForeignKeys>
							<ForeignKey>
								<PrimaryKeyTable>S</PrimaryKeyTable>
								<PrimaryKeyColumn ForeignKeyColumn="TCID">TCID</PrimaryKeyColumn>
								<PrimaryKeyColumn ForeignKeyColumn="SUID">SUID</PrimaryKeyColumn>
							</ForeignKey>
						</ForeignKeys>
					</DataTable>
						<DataTable>
							<TableName>SAI</TableName>
							<ContainsRawData>false</ContainsRawData>
							<RawDataIdColumn />
							<RawDataValueColumn />
							<ForeignKeys>
								<ForeignKey>
									<PrimaryKeyTable>SAO</PrimaryKeyTable>
									<PrimaryKeyColumn ForeignKeyColumn="TCID">TCID</PrimaryKeyColumn>
									<PrimaryKeyColumn ForeignKeyColumn="SUID">SUID</PrimaryKeyColumn>
									<PrimaryKeyColumn ForeignKeyColumn="AOID">AOID</PrimaryKeyColumn>
								</ForeignKey>
							</ForeignKeys>
						</DataTable>
							<DataTable>
								<TableName>SM</TableName>
								<ContainsRawData>false</ContainsRawData>
								<RawDataIdColumn></RawDataIdColumn>
								<RawDataValueColumn></RawDataValueColumn>
								<ForeignKeys>
									<ForeignKey>
										<PrimaryKeyTable>SAI</PrimaryKeyTable>
										<PrimaryKeyColumn ForeignKeyColumn="SUID">SUID</PrimaryKeyColumn>
										<PrimaryKeyColumn ForeignKeyColumn="TCID">TCID</PrimaryKeyColumn>
										<PrimaryKeyColumn ForeignKeyColumn="AIID">AIID</PrimaryKeyColumn>
									</ForeignKey>
								</ForeignKeys>
							</DataTable>
			
			<DataTable>
				<TableName>CAO</TableName>
				<ContainsRawData>false</ContainsRawData>
				<RawDataIdColumn></RawDataIdColumn>
				<RawDataValueColumn></RawDataValueColumn>
				<ForeignKeys>
					<ForeignKey>
						<PrimaryKeyTable>TC</PrimaryKeyTable>
						<PrimaryKeyColumn ForeignKeyColumn="TCID">TCID</PrimaryKeyColumn>
					</ForeignKey>
				</ForeignKeys>
			</DataTable>
				<DataTable>
					<TableName>CAI</TableName>
					<ContainsRawData>false</ContainsRawData>
					<RawDataIdColumn></RawDataIdColumn>
					<RawDataValueColumn></RawDataValueColumn>
					<ForeignKeys>
						<ForeignKey>
							<PrimaryKeyTable>CAO</PrimaryKeyTable>
							<PrimaryKeyColumn ForeignKeyColumn="TCID">TCID</PrimaryKeyColumn>
							<PrimaryKeyColumn ForeignKeyColumn="AOID">AOID</PrimaryKeyColumn>
						</ForeignKey>
					</ForeignKeys>
				</DataTable>

		</DataTables>
	</Query>
</Report>

crosstab.xrp

<?xml version="1.0" encoding="UTF-8"?>
<report repeats-on="TC" data-root="//report/data">
  <parameters>

  </parameters>
  <layout>
		<page-master name="report" margin="0.5in" >
			<page-master-header name="header" extent="0.25in"/>
			<page-master-body name="body" margin-top="0.25in"/>
		</page-master>
  </layout>
	
  <body>
   <page reference="report">
	   
	   <page-header reference="header">
		   test
	   </page-header>
	   
	<page-body reference="body" font-size="10pt">

		<variable name="iTCID">
			<datavalue field="TCID"/>
		</variable>
		
		<variable name="NonCommentCount">
			<datavalue field="count(CAO/CAI)"/>
		</variable>

		<table>
			<table-column column-width="proportional-column-width(1)"/>
			<table-column column-width="proportional-column-width(1)"/>
			<table-column column-width="proportional-column-width(1)"/>
			<table-header>
			</table-header>
			<table-body>
			<table-row>
				<table-cell text-align="left">
					<block>
						<datavalue field="LABEL"/> 
					</block>
				</table-cell>
				<table-cell text-align="center">
					<block>
						<datavalue field="RPNAME"/>
					</block>
				</table-cell>
				<table-cell text-align="right">
					<block>
						<datavalue field="TNAME"/>
					</block>
				</table-cell>
			</table-row>
			</table-body>
		</table>
		
		<table>
			
			<table-column column-width="50mm"/>
			<for-each select="CAO/CAI">
				<table-column>
					<attribute name="column-width">
						<choose>
							<when test="IS_COMMENT=1 or IS_COMMENT=true or IS_COMMENT='true'">proportional-column-width(<datavalue field="$NonCommentCount"/>)</when>
							<otherwise>proportional-column-width(1)</otherwise>
						</choose>
					</attribute>
				</table-column>
			</for-each>
			
			<table-header>
				
				<table-row>
					<table-cell border='1pt solid black' padding-left='10pt' number-rows-spanned='2'>
						<block>
							STUDENT
						</block>
					</table-cell>
					<for-each select="CAO">
						<variable name="iAOID">
							<datavalue field="AOID"/>
						</variable>
						<table-cell border="1pt solid black" text-align="center">
							<attribute name="number-columns-spanned">
								<datavalue field="count(CAI)"/>
							</attribute>
							<block>
								<datavalue field="NAME"/>
							</block>
						</table-cell>
					</for-each>
				</table-row>

				<table-row keep-together="always">
					<for-each select="CAO/CAI">
						<table-cell border="1pt solid black" text-align="left">
							<block-container 
								reference-orientation="90" 
								inline-progression-dimension.minimum="5mm"
								inline-progression-dimension.optimum="15mm"
								inline-progression-dimension.maximum="70mm">
							<block>
								<datavalue field="NAME"/>
							</block>
							</block-container>
							
						</table-cell>
					</for-each>
				</table-row>

			</table-header>
			
			<table-body>
				<for-each select="S">

					<variable name="iSUID">
						<datavalue field="SUID"/>
					</variable>

					 <table-row keep-together="always">

						<table-cell border='1pt solid black' padding-left='10pt'>
							<block font-weight="bold">
								<datavalue field="STUDENT"/>
							</block>
						</table-cell>
						 
						<for-each select="SAO/SAI">

							 <table-cell border="1pt solid black" text-align="center">
								 <block>
									 <choose>
										 <when test="string(number(SM/VALUE)) != 'NaN'">
											 <datavalue field="SM/VALUE" format="#.###" />
										 </when>
										 <otherwise>
											 <datavalue field="SM/VALUE" />
										 </otherwise>
									 </choose>
									
								 </block>
							 </table-cell>

						 </for-each>

					 </table-row>
					
				</for-each>
			</table-body>
			
		</table>
		
	 </page-body>
   </page>
  </body>
</report>