47030^Kreuztabelle Studierende nach Geschlecht und Nationalität^--freemarker template\
-- Maskenfelder -Leere Zeilen ausblenden, leere SPalten ausblenden?\
<sqlvars>\
<sqlvar name="table_name">select table_name from xcube where maskeninfo_id=${Maskennummer}</sqlvar>\
<sqlvar name="aggrfunction">select aggrfunction from xcube_kennzahl where maskeninfo_id=${Maskennummer} and tid=<<Kennzahl>></sqlvar>\
<sqlvar name="aggrfunction_restriction">select nvl(restriction,''::char(1)) from xcube_kennzahl \
where maskeninfo_id=${Maskennummer} and tid=<<Kennzahl>>\
</sqlvar>\
<sqlvar name="datatype">select nvl(datatype,'decimal(17,2)'::varchar(30)) from xcube_kennzahl \
where maskeninfo_id=${Maskennummer} and tid=<<Kennzahl>></sqlvar>\
--benoetigt falls Kennzahlen in Zeilen oder Spalten\
<sqlvar name="kennzahlen" type="hashsequence">select apnr,aggrfunction,\
nvl(restriction,''::char(1)) as restriction \
from xcube_kennzahl where maskeninfo_id=${Maskennummer}</sqlvar>\
<sqlvar name="restriction" type="string"><![CDATA[<sximport>select restriction from xcube where maskeninfo_id=<<Maskennummer>></sximport>]]></sqlvar>\
<sqlvar name="coldef" type="hash"><![CDATA[select name,\
caption,\
foreignkey_tab,\
foreignkey_col,\
foreignkey_cap,\
nvl(foreignkey_cond,''::varchar(1))\
as foreignkey_cond,\
--nvl(C.specific_restriction,''::varchar(1)) as specific_restriction,\
sortfield,\
is_sicht,\
calc_gesamt,\
is_virtual,\
vcsql,\
nvl(attrib_nachbearbeitung,''::char(1)) as attrib_nachbearbeitung\
from xcube_dimconfig  where  \
maskeninfo_id=${Maskennummer} \
and name=<<Ansicht in Ergebnisspalten>>]]>\
</sqlvar>\
<sqlvar name="nichtsicht_ergspalten" type='hashsequence'><![CDATA[\
<#if coldef.is_sicht=0>\
select distinct ${coldef.foreignkey_col}::varchar(255) as apnr,\
${coldef.foreignkey_cap} as name,\
1::smallint as detailgesamtsort,\
${coldef.sortfield} as tmp_sortfield\
from ${coldef.foreignkey_tab} where \
1=1 \
<#if coldef.foreignkey_cond!=''> and ${coldef.foreignkey_cond} </#if>\
 --{coldef.specific_restriction} \
--ausblenden von Spalten, die gar nicht in konkretem Teilergebnis vorkommen, geht derzeit nicht für virtuelle Spalten und nachbearbeitung, -> Kernmodul\
<#if coldef.is_virtual?number=0&&coldef.attrib_nachbearbeitung=''&&"<<Leere Spalten ausblenden>>"="'ja'">\
and ${coldef.foreignkey_col}::varchar(255) in (select distinct ${coldef.name}::varchar(255) from ${table_name} where   \
 ${restriction} \
)\
</#if>\
<#if coldef.calc_gesamt?number=1> \
union\
select 'gesamt','Gesamt',\
2::smallint as detailgesamtsort,\
null as tmp_sortfield from xdummy\
 </#if>\
order by detailgesamtsort,\
tmp_sortfield,\
name;\
<#else>\
select 'Sichtspalten aktiv' from xdummy;\
</#if>\
]]>\
</sqlvar>\
\
\
\
<sqlvar name="rowdef" type="hash"><![CDATA[select name,caption,is_sicht,calc_gesamt,foreignkey_tab,foreignkey_col,foreignkey_cap,\
nvl(foreignkey_cond,''::varchar(1)) as foreignkey_cond,\
--nvl(C.specific_restriction,''::varchar(1)) as specific_restriction,\
sortfield,is_virtual,vcsql,nvl(attrib_nachbearbeitung,''::char(1)) as attrib_nachbearbeitung\
from xcube_dimconfig C where maskeninfo_id=${Maskennummer} and name=<<Ansicht in Ergebniszeilen>>]]>\
</sqlvar>\
--nur bei wenn Ergzeilen_def nicht vom Typ Sicht relevant\
<sqlvar name="nichtsicht_ergzeilen" type='hashsequence'><![CDATA[\
<#if rowdef.is_sicht=0>\
select 1::smallint as level, \
${rowdef.foreignkey_col}::varchar(255) as key,--hier wird zur Vereinheitlichung mit Sichten Bezeichung "key" für eigentlichen Schluessel benoetigt\
${rowdef.foreignkey_cap} as name,\
${rowdef.sortfield} as tmp_sortfield \
from ${rowdef.foreignkey_tab}\
where 1=1\
<#if rowdef.foreignkey_cond!=''> and ${rowdef.foreignkey_cond} </#if>\
 --{rowdef.specific_restriction} \
<#if rowdef.calc_gesamt?number=1>\
union\
select 2,'gesamt','Gesamt',null as ${rowdef.sortfield} from xdummy\
</#if>\
order by level,tmp_sortfield; \
<#else>\
select 'nicht relevant' from xdummy;</#if>\
]]>\
</sqlvar>\
\
<sqlvar name="zweiteRowDef" type="hash"><![CDATA[\
<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">\
select name,caption,\
is_sicht,\
calc_gesamt,\
foreignkey_tab,\
foreignkey_col,\
foreignkey_cap,\
nvl(foreignkey_cond,''::varchar(1)) as foreignkey_cond,\
--nvl(C.specific_restriction,''::varchar(1)) as specific_restriction,\
C.sortfield\
from xcube_dimconfig where maskeninfo_id=${Maskennummer} and name=<<2.Ansicht in Zeilen>>\
<#else>\
select 'keine zweite RowDef' from xdummy;\
</#if>\
]]>\
</sqlvar>\
--nur bei wenn Ergzeilen_def nicht vom Typ Sicht relevant\
<sqlvar name="zweiteRow_nichtsicht_ergzeilen" type='hashsequence'><![CDATA[\
<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>"&&zweiteRowDef.is_sicht=0>\
select 1::smallint as level, \
${zweiteRowDef.foreignkey_col}::varchar(255) as key,--hier wird zur Vereinheitlichung mit Sichten Bezeichung "key" für eigentlichen Schluessel benoetigt\
${zweiteRowDef.foreignkey_cap} as name,\
${zweiteRowDef.sortfield} as tmp_sortfield \
from ${zweiteRowDef.foreignkey_tab}\
where 1=1\
<#if zweiteRowDef.foreignkey_cond!=''> and ${zweiteRowDef.foreignkey_cond} </#if>\
 --{zweiteRowDef.specific_restriction} \
 <#if zweiteRowDef.calc_gesamt?number=1>\
union\
select 2,'gesamt','Gesamt',null as ${zweiteRowDef.sortfield} from xdummy\
</#if>\
order by level,tmp_sortfield; \
<#else>\
select 'nicht relevant' from xdummy;</#if>\
]]>\
</sqlvar>\
\
\
</sqlvars>\
\
<#function kennzahlInRowOrCol>\
--TODO auch 2. Dimension in Zeilen/Spalten\
<#if coldef.name="xckennzahl"||rowdef.name="xckennzahl"> <#return 1><#else> return <#return 0> </#if>\
</#function>\
\
<#function hasVirtualColumns>\
--TODO auch 2. Dimension in Zeilen/Spalten\
<#if coldef.is_virtual?number=1||rowdef.is_virtual?number=1> <#return 1><#else> return <#return 0> </#if>\
</#function>\
\
<#assign graph_col= "col" />\
/* <#assign graph_col=<<Spaltenvisualisierung>> /> */\
\
create temp table tmp_cellmax (maxvalue ${datatype} );\
insert into tmp_cellmax values(0);\
\
<#if hasVirtualColumns()=1||kennzahlInRowOrCol()=1>\
select * into temp tmp_rohdaten from \
${table_name}\
where \
 ${restriction};\
 <#if coldef.is_virtual?number=1> alter table tmp_rohdaten add column ${coldef.name} varchar(255); ${coldef.vcsql}; </#if>\
 <#if rowdef.is_virtual?number=1> alter table tmp_rohdaten add column ${rowdef.name} varchar(255); ${rowdef.vcsql}; </#if>\
 --create index ix_tmp_r1 on tmp_rohdaten(${coldef.name},${rowdef.name}); --Todo nicht xckennzahl ggfs. für 2. Dimension in Spalten\
</#if>\
\
\
<#if kennzahlInRowOrCol()=0>\
create temp table tmp_cache (\
colattrib varchar(255), <@addcomment comment=coldef.caption/>\
rowattrib varchar(255), <@addcomment comment=rowdef.caption/>\
row2attrib varchar(255),\
value decimal(17,5));\
insert into tmp_cache (colattrib,rowattrib,<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">row2attrib, </#if> value)\
select ${coldef.name}::varchar(255), \
${rowdef.name}::varchar(255),\
<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">${zweiteRowDef.name}::varchar(255),</#if>\
${aggrfunction}\
 \
<#if hasVirtualColumns()=0> \
from ${table_name}  where \
 ${restriction}\
 <#if (aggrfunction_restriction?length>0)> and ${aggrfunction_restriction} </#if>\
 <#else>\
from tmp_rohdaten\
</#if>\
\
 group by 1,2 <#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">,${zweiteRowDef.name}</#if> ;\
create index ix_tmp_roh1 on tmp_cache (colattrib,rowattrib,row2attrib);\
</#if>\
\
--moegliche attrib_nachbearbeitung\
<#if kennzahlInRowOrCol()=0> \
<#assign tmp_table="tmp_cache"/> <#assign rowfield="rowattrib"/> <#assign colfield="colattrib"/> \
<#else>\
<#assign tmp_table="tmp_rohdaten"/> <#assign rowfield=rowdef.name/> <#assign colfield=coldef.name/> \
</#if>\
\
  <#if (rowdef.attrib_nachbearbeitung?length>1)>\
  ${rowdef.attrib_nachbearbeitung?replace("<<tmp_table>>",tmp_table)?replace("<<col>>",rowfield)};\
  -- z.B. update tmp_cache R set rowattrib='s_'||rowattrib;\
  </#if>\
  <#if (coldef.attrib_nachbearbeitung?length>1)>\
  ${coldef.attrib_nachbearbeitung?replace("<<tmp_table>>",tmp_table)?replace("<<col>>",colfield)};\
  -- z.B. update tmp_cache R set rowattrib='s_'||rowattrib;\
  </#if>\
 \
  \
  \
\
<#if coldef.is_sicht&gt;0>\
<#assign ergspalten=.vars[coldef.caption].elements/>\
<#else>\
<#assign ergspalten=nichtsicht_ergspalten/>\
</#if>\
create temp table tmp_erg (\
ebene integer,\
col1 varchar(255),\
name varchar(255),\
<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">col2 varchar(255),</#if>\
<#assign i=0/>\
<#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
  c${i} decimal(17,5) default 0, <@addcomment comment=spalte.name/>\
 \
</#foreach>\
sortnr integer\
) <@informixnolog/>;\
\
create index ix_tmp_erg on tmp_erg (sortnr);\
  \
<#if rowdef.is_sicht&gt;0> \
  <#assign zeilendef=.vars[rowdef.caption].elements/> --ermittelt des SichtObjekts anhand des Feldnames (aus felderinfo)\
<#else>\
  <#assign zeilendef=nichtsicht_ergzeilen/>\
</#if>\
<#assign sortnr=0/>\
<#foreach zeile in zeilendef>\
<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">\
<#foreach zeile2 in zweiteRow_nichtsicht_ergzeilen>\
<#assign sortnr=sortnr+1/>\
     insert into tmp_erg (ebene,col1,name,col2,sortnr)\
      values (${zeile.level},'${zeile.key}','${zeile.name}','${zeile2.name}',${sortnr});\
     <#assign i=0/>\
<#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
         <@updateval i=i col=spalte row=zeile row2=zeile2 sortnr=sortnr/>\
     </#foreach>\
</#foreach>\
<#else>\
/* <#if zeile.level &lt;= <<Zeilenfilter bis Ebene>> > */\
   <#assign sortnr=sortnr+1/>\
     insert into tmp_erg (ebene,col1,name,sortnr)\
      values (${zeile.level},'${zeile.key}','${zeile.name}',${sortnr});\
     <#assign i=0/>\
<#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
         <@updateval i=i col=spalte row=zeile row2='' sortnr=sortnr/>\
     </#foreach>\
/* </#if> --<<Zeilenfilter bis Ebene>> */\
</#if>     \
</#foreach>\
\
<#macro updateval i col row row2 sortnr>\
update tmp_erg T set c${i}=\
<#if kennzahlInRowOrCol()=0>\
  (select nvl(sum(value),0) from tmp_cache R where 1=1 \
  <@colrestriction col=col/>\
  <@rowrestriction row=row row2=row2/>\
  ) \
  <#else>  -- Kennzahl ist in Zeile oder Spalte\
  (select <@kennzahlSelect row=row col=col/>\
   from tmp_rohdaten R where 1=1 \
  <#if rowdef.name="xckennzahl"> <@kennzahlrestrict row=row col=col/>  <#else> <@rowrestriction row=row row2=row2 rowattribname=rowdef.name/></#if>\
  <#if coldef.name="xckennzahl"> <@kennzahlrestrict row=row col=col/> <#else> <@colrestriction col=col colattribname=coldef.name/></#if>\
  \
  )\
  </#if>\
  where T.sortnr='${sortnr}'\
  ;\
 </#macro>\
 \
 <#macro kennzahlrestrict row col>\
 <#if coldef.name="xckennzahl">\
   <#foreach k in kennzahlen>\
     <#if (col.apnr=k.apnr&&k.restriction?length>0)> and ${k.restriction} </#if>\
   </#foreach>\
 </#if>\
 <#if rowdef.name="xckennzahl">\
   <#foreach k in kennzahlen>\
     <#if (row.key=k.apnr&&k.restriction?length>0)> and ${k.restriction} </#if>\
   </#foreach>\
 </#if>\
 </#macro>\
 \
 <#macro kennzahlSelect row col>\
 <#if coldef.name="xckennzahl">\
   <#foreach k in kennzahlen>\
     <#if col.apnr=k.apnr> ${k.aggrfunction} </#if>\
   </#foreach>\
 </#if>\
 <#if rowdef.name="xckennzahl">\
   <#foreach k in kennzahlen>\
     <#if row.key=k.apnr> ${k.aggrfunction} </#if>\
   </#foreach>\
 </#if>\
 \
 \
 </#macro>\
 \
 \
 \
 <#macro colrestriction col colattribname='R.colattrib'>\
 <#if coldef.is_sicht==1>\
 and ${colattribname}::varchar(255) in ${col.subkeys}\
<#elseif coldef.is_sicht=2>\
 and (${col.strukturStr?replace('$1',' '+colattribname+"::varchar(255)")}) \
<#else>\
 <#if col.apnr!='gesamt'>\
  and ${colattribname}::varchar(255)='${col.apnr}' \
  </#if>\
  </#if>\
 </#macro>\
 \
 <#macro rowrestriction row row2 rowattribname='R.rowattrib' >\
 <#if rowdef.is_sicht==1>\
    and ${rowattribname}::varchar(255) in ${row.subkeys}\
  <#elseif rowdef.is_sicht==2>\
 and ( ${row.strukturStr?replace('$1',' '+rowattribname+"::varchar(255)")} ) \
  <#else>\
    <#if row.key!='gesamt'> and ${rowattribname}::varchar(255)='${row.key}' </#if>\
  </#if>\
  <#if row2?is_hash> and R.row2attrib='${row2.key}' </#if> \
 </#macro>\
 \
drop table if exists tmp_cache;\
\
drop table if exists tmp_rohdaten;\
\
-- Kennzahl in Spalten/Zeilen ggfs. null\
<#assign i=0/>\
 <#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
 update tmp_erg set c${i}=0 where c${i} is null; \
</#foreach>\
<#assign lastcol=i />\
\
--Wenn Grafikfunktion eingeschaltet ist, wird Maximalwert ermittelt:\
<#if i != 0 && graph_col != "col" >\
\
<#assign i=0/>\
 <#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
 <#if i == lastcol>\
 update tmp_cellmax set maxvalue = (select max(c${i}) from tmp_erg) \
 where tmp_cellmax.maxvalue < (select max(c${i}) from tmp_erg) ;\
 </#if> \
</#foreach>\
\
</#if>\
\
<#if "<<Leere Zeilen ausblenden>>"="'ja'">\
delete from tmp_erg where 1=1\
<#assign i=0/>\
<#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
and  c${i}=0\
</#foreach>;\
 </#if>\
<#if "<<Ansicht in Ergebniszeilen>>"="'institution'">\
update tmp_erg set name=replace(name,col1||' - ','');\
update tmp_erg set col1=(select max(uniquename) from organigramm where col1=trim(key_apnr))\
where substring(col1 from 1 for 1)='_';\
--vorher war ggfs _3 zu O005030, auch O005030 in Namen ersetzen\
update tmp_erg set name=replace(name,col1||' - ','');\
</#if> \
\
\
\
\
select \
<#if rowdef.is_sicht&gt;0>\
 <#if graph_col=="col">\
  ebene,\
 <#else>\
  ebene as level, --bei Grafiken keine Ebenenanzeige\
 </#if>\
</#if>\
<#if graph_col?starts_with("_spark")>\
name as _categorySpark\
 <#else>\
name\
</#if>\
--TODO: wenn <<2.Ansicht in Zeilen>>"!="" dann ,col2\
<#assign i=0/>\
<#foreach spalte in ergspalten>\
 <#assign i=i+1/>\
<#if i != lastcol && graph_col != "col" >\
<#if graph_col == "_graphbaralternating">\
--bei symmetr. Balken jeder 2. Balken rechtsbündig\
	<#if i % 2 == 0>\
	, '' || c${i}::${datatype} || '|' || tmp_cellmax.maxvalue as _graph${i}\
	<#else>\
	, '' || c${i}::${datatype} || '|' || tmp_cellmax.maxvalue as _graphbarright${i}\
	</#if>\
 <#elseif graph_col?starts_with("_spark")>\
, c${i} as ${graph_col}${i}\
 <#else>\
, '' || c${i}::${datatype} || '|' || tmp_cellmax.maxvalue as ${graph_col}${i}\
</#if>\
\
<#else>\
--letzte Spalte:\
<#if graph_col?starts_with("_spark")>\
, c${i} as ${graph_col}${i}\
<#else>\
, c${i}::${datatype} as col${i}\
</#if>\
\
</#if>\
\
</#foreach>\
\
from tmp_erg, tmp_cellmax order by sortnr;^--freemarker Template\
XIL List\
   sizable_columns horizontal_scrolling\
   drop_and_delete movable_columns\
   white_space_color=COLOR_WHITE fixed_columns=1\
   min_heading_height=35\
<#if rowdef.is_sicht&gt;0>\
Column CID=0 heading_text="Ebene" center_heading\
   row_selectable heading_platform readonly explanation="Hierarchieebene"\
   width=5 text_size=8\
</#if>\
Column CID=20 heading_text="${.vars["Ansicht in ErgebniszeilenObject"].selectedItems[0].name}" center_heading\
   row_selectable heading_platform readonly explanation="Schlüssel: ${.vars["Ansicht in ErgebniszeilenObject"].selectedItems[0].name}Nr"\
   width=30 text_size=30\
<#if "<<2.Ansicht in Zeilen>>"!=""&&"<<2.Ansicht in Zeilen>>"!="<<2.Ansicht in Zeilen>>">\
Column CID=20 heading_text="${.vars["2.Ansicht in ZeilenObject"].selectedItems[0].name}" center_heading\
   row_selectable heading_platform readonly\
   width=20 text_size=10 explanation="${.vars["2.Ansicht in ZeilenObject"].selectedItems[0].name}"\
</#if>\
<#if coldef.is_sicht&gt;0>\
<#assign ergspalten=.vars[coldef.caption].elements/>\
<#else>\
<#assign ergspalten=nichtsicht_ergspalten/>\
</#if>\
<#foreach spalte in ergspalten>\
Column CID=1 heading_text="${spalte.name}" center_heading\
   row_selectable heading_platform readonly explanation="Ausprägung ${spalte.name}"\
   width=12\
</#foreach>\
@@@^Altersgruppe^Anzahl^Kreuztabellen zu Studierendenzahlen^drop table tmp_erg;drop table tmp_cellmax;^ ^1^700^360^0^1^ ^