Fixed columns and headers with crosstab for vertical / horizontal scrolling
Good Day
I have a large crosstab which I have managed to get working quite well including extra columns. To be fair, I'm quite impressed how well the crosstab accommodated the complexity and calculations including the calculations for the row totals.
The problem is that due to the number of columns and rows scrolling becomes an issue. Scrolling right you lose the label column so not sure which row you are viewing - in my case the branch name. Scrolling down you lose the column headers so you don't know which pivot you are looking at.
Any ideas or alternatives? Every where I turn I run into problems.
TIA
-
This can be achieved strictly in CSS.
- First you must wrap your Table or Crosstab within a division container.
- The first header cell is set to position sticky, with a left and top position set to 0 - this allows this cell to be sticky when scrolling horizontal or vertical
- The table header is then set to position sticky with a top position set to 0 - this allows these th to be sticky at the top when scrolling vertical
- The table body first column cells are set to position sticky with a left position set to 0 - this allows these td cells to be sticky at the left when scrolling horizontally
Video Demo:
https://watch.screencastify.com/v/zaW6Whc0DFBBCbdsGf67
<Division ID="divCrosstab">
<CrosstabTable ID="CrosstabTable1" AjaxPaging="True" DraggableColumns="False" ResizableColumns="False" AccessibleSummary="AnalysisGrid CrossTabTable" Caption="OrderDate by CustomerID on Sum of OrderID" SortArrows="True">
<CrosstabTableLabelColumn ID="colAxLabel" Header="CustomerID">
<Label ID="lblAxLabl" Caption="@Data.CustomerID~" />
<DataColumnSort DataColumn="CustomerID" DataType="Text" />
</CrosstabTableLabelColumn>
<CrosstabTableValueColumns ID="colAxValue" Header="@Data.rdCrosstabColumn~" Format="M/d/yyyy HH:mm">
<Label ID="lblAxValue" Caption="=iif(@Data.rdCrosstabValCount~=0,"","@Data.rdCrosstabValue~")" Format="#,###.#" />
<CrosstabValueColumnSort DataType="Number" />
</CrosstabTableValueColumns>
<DataLayer Type="XMLFile" ID="XMLFileDataLayer1" XMLFile="C:\inetpub\wwwroot\VISUIClients\_SupportFiles\NW_Orders.xml">
<TimePeriodColumn ID="rdHdrGrpOp_OrderDate_FirstMinuteOfHour" DataColumn="OrderDate" TimePeriod="FirstMinuteOfHour" DateGroupByOperator="FirstMinuteOfHour" DataType="Date" Format="M/d/yyyy HH:mm" />
<SortFilter SortColumn="rdHdrGrpOp_OrderDate_FirstMinuteOfHour" DataType="Date" />
<CrosstabFilter CrosstabColumn="rdHdrGrpOp_OrderDate_FirstMinuteOfHour" CrosstabLabelColumn="CustomerID" CrosstabValueColumn="OrderID" CrosstabValueFunction="Sum" />
</DataLayer>
<InteractivePaging ShowPageNumber="True" PageRowCount="20" HideWhenOnePage="True" Location="Top" CaptionType="Image" rdIdeIdx="17" />
</CrosstabTable>
</Division><style>
#divCrosstabWrapper {
width: 500px;
height: 300px;
position: relative;
overflow: auto;
border: 1px solid black;
}
#CrosstabTable1 th,
#CrosstabTable1 td {
padding: 20px;
height: 50px;
width: 100px;
}
#CrosstabTable1 > thead > tr > th:first-of-type {
position: sticky;
left: 0;
top: 0;
border-right: 2px solid black;
border-bottom: 2px solid black;
background-color: Blue !important;
z-index: 2;
}
#CrosstabTable1 > thead > tr > th {
position: sticky;
top: 0;
border-bottom: 2px solid black;
}
#CrosstabTable1 > tbody > tr > td:first-of-type {
position: sticky;
left: 0;
border-right: 2px solid black;
}
</style>0 -
Hi
Thank you for the response which is greatly appreciated. However, I have copied you code above and put into a new report and it does not work! I'm really not sure what I am missing but no scrollbars appear and the headers and label column do not stay fixed.
What am I missing?
TIA
0 -
Do not cut and paste the CSS directly into Logi. Paste it into Notepad++ first. I think this forum code section adds some sort of encoding to it. The same thing happened to me yesterday when I went to test the code and for the life of me couldn't get a simple bit of the CSS to work.
The code does work.
1. Make sure the ID for your division is the same as mine.
2. Make sure the Crosstab is named the same.
3. Turn off both Resizable and Movable Columns. I think having these turned on is possible, it will just make the CSS selectors much more complicated because with these turned on, Logi builds the Table Header with a nested table.0 -
Hi
Where does the css Style code go? That seems to be the issue - I have copied your exact report and css and nothing works! Clearly I am doing something wrong. Do I put the Style into HTML and insert it before the div?
0 -
Best thing to do is to copy all the css classes (do not include the style tag and end tag). Save that into a css file under _SupportFiles and reference that from the Crosstab report. However, whenever I make examples like this I use an IncludeHTML element and copy the entire <Style> block. It should apply whether it comes before or after the container it applies to.
It is truly frustrating that we can't simply attach the files to the post and/or provide a Dropbox link or Google Drive link to download the files.
In regards to the css not being recognized. I had the same exact issue last night when trying to use the code copied from the forum. The css was not getting applied no matter what I tried. It wasn't even showing at being applied to elements within the browser dev console. I ended up having to retype all the css in order to get it to work. Copying and pasting into Notepad++ fixed that for me.0 -
The issue seems to be within the indentation of the css code. I am not seeing any special characters, but something within the copied code's indent is causing the css styles and classes to be ignored in the browser.
0 -
<Report ID="Default">
<Body>
<Division ID="divCrosstab" HtmlDiv="True">
<CrosstabTable ID="CrosstabTable1" AjaxPaging="True" DraggableColumns="False" ResizableColumns="False" AccessibleSummary="AnalysisGrid CrossTabTable" Caption="OrderDate by CustomerID on Sum of OrderID" SortArrows="True">
<CrosstabTableLabelColumn ID="colAxLabel" Header="CustomerID">
<Label ID="lblAxLabl" Caption="@Data.CustomerID~" />
<DataColumnSort DataColumn="CustomerID" DataType="Text" />
</CrosstabTableLabelColumn>
<CrosstabTableValueColumns ID="colAxValue" Header="@Data.rdCrosstabColumn~" Format="M/d/yyyy HH:mm">
<Label ID="lblAxValue" Caption="=iif(@Data.rdCrosstabValCount~=0,"","@Data.rdCrosstabValue~")" Format="#,###.#" />
<CrosstabValueColumnSort DataType="Number" />
</CrosstabTableValueColumns>
<DataLayer Type="XMLFile" ID="XMLFileDataLayer1" XMLFile="C:\inetpub\wwwroot\VISUIClients\_SupportFiles\NW_Orders.xml">
<TimePeriodColumn ID="rdHdrGrpOp_OrderDate_FirstMinuteOfHour" DataColumn="OrderDate" TimePeriod="FirstMinuteOfHour" DateGroupByOperator="FirstMinuteOfHour" DataType="Date" Format="M/d/yyyy HH:mm" />
<SortFilter SortColumn="rdHdrGrpOp_OrderDate_FirstMinuteOfHour" DataType="Date" />
<CrosstabFilter CrosstabColumn="rdHdrGrpOp_OrderDate_FirstMinuteOfHour" CrosstabLabelColumn="CustomerID" CrosstabValueColumn="OrderID" CrosstabValueFunction="Sum" />
</DataLayer>
<InteractivePaging ShowPageNumber="True" PageRowCount="20" HideWhenOnePage="True" Location="Top" CaptionType="Image" rdIdeIdx="17" />
</CrosstabTable>
</Division>
<IncludeHtml Html="<style>
#divCrosstab {
 width: 500px;
 height: 300px;
 position: relative;
 overflow: auto;
 border: 1px solid black;
}
#CrosstabTable1 th,
#CrosstabTable1 td {
 padding: 20px;
 height: 50px;
 width: 100px;
}
#CrosstabTable1 > thead > tr > th:first-of-type {
 position: sticky;
 left: 0;
 top: 0;
 border-right: 2px solid black;
 border-bottom: 2px solid black;
 background-color: Blue !important;
 z-index: 2;
}
#CrosstabTable1 > thead > tr > th {
 position: sticky;
 top: 0;
 border-bottom: 2px solid black;
}

#CrosstabTable1 > tbody > tr > td:first-of-type {
 position: sticky;
 left: 0;
 border-right: 2px solid black;
}
</style>" />
</Body>
<ideTestParams />
</Report>0 -
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Row>
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<EmployeeID>5</EmployeeID>
<OrderDate>2010-07-04 00:00:00.000</OrderDate>
<RequiredDate>2010-08-01 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-16 00:00:00.000</ShippedDate>
<ShipVia>3</ShipVia>
<Freight>32.38</Freight>
<ShipName>Vins et alcools Chevalier</ShipName>
<ShipAddress>59 rue de l'Abbaye</ShipAddress>
<ShipCity>Reims</ShipCity>
<ShipPostalCode>51100</ShipPostalCode>
<ShipCountry>France</ShipCountry>
</Row>
<Row>
<OrderID>10249</OrderID>
<CustomerID>TOMSP</CustomerID>
<EmployeeID>6</EmployeeID>
<OrderDate>2010-07-05 00:00:00.000</OrderDate>
<RequiredDate>2010-08-16 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-10 00:00:00.000</ShippedDate>
<ShipVia>1</ShipVia>
<Freight>11.61</Freight>
<ShipName>Toms Spezialitäten</ShipName>
<ShipAddress>Luisenstr. 48</ShipAddress>
<ShipCity>Münster</ShipCity>
<ShipPostalCode>44087</ShipPostalCode>
<ShipCountry>Germany</ShipCountry>
</Row>
<Row>
<OrderID>10250</OrderID>
<CustomerID>HANAR</CustomerID>
<EmployeeID>4</EmployeeID>
<OrderDate>2010-07-08 00:00:00.000</OrderDate>
<RequiredDate>2010-08-05 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-12 00:00:00.000</ShippedDate>
<ShipVia>2</ShipVia>
<Freight>65.83</Freight>
<ShipName>Hanari Carnes</ShipName>
<ShipAddress>Rua do Paço #67</ShipAddress>
<ShipCity>Rio de Janeiro</ShipCity>
<ShipRegion>RJ</ShipRegion>
<ShipPostalCode>05454-876</ShipPostalCode>
<ShipCountry>Brazil</ShipCountry>
</Row>
<Row>
<OrderID>10251</OrderID>
<CustomerID>VICTE</CustomerID>
<EmployeeID>3</EmployeeID>
<OrderDate>2010-07-08 00:00:00.000</OrderDate>
<RequiredDate>2010-08-05 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-15 00:00:00.000</ShippedDate>
<ShipVia>1</ShipVia>
<Freight>41.34</Freight>
<ShipName>Victuailles en stock</ShipName>
<ShipAddress>2 rue du Commerce</ShipAddress>
<ShipCity>Lyon</ShipCity>
<ShipPostalCode>69004</ShipPostalCode>
<ShipCountry>France</ShipCountry>
</Row>
<Row>
<OrderID>10252</OrderID>
<CustomerID>SUPRD</CustomerID>
<EmployeeID>4</EmployeeID>
<OrderDate>2010-07-09 00:00:00.000</OrderDate>
<RequiredDate>2010-08-06 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-11 00:00:00.000</ShippedDate>
<ShipVia>2</ShipVia>
<Freight>51.3</Freight>
<ShipName>Suprêmes délices</ShipName>
<ShipAddress>Boulevard Tirou #255</ShipAddress>
<ShipCity>Charleroi</ShipCity>
<ShipPostalCode>B-6000</ShipPostalCode>
<ShipCountry>Belgium</ShipCountry>
</Row>
<Row>
<OrderID>10253</OrderID>
<CustomerID>HANAR</CustomerID>
<EmployeeID>3</EmployeeID>
<OrderDate>2010-07-10 00:00:00.000</OrderDate>
<RequiredDate>2010-07-24 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-16 00:00:00.000</ShippedDate>
<ShipVia>2</ShipVia>
<Freight>58.17</Freight>
<ShipName>Hanari Carnes</ShipName>
<ShipAddress>Rua do Paço #67</ShipAddress>
<ShipCity> Rio de Janeiro</ShipCity>
<ShipRegion>RJ</ShipRegion>
<ShipPostalCode>05454-876</ShipPostalCode>
<ShipCountry>Brazil</ShipCountry>
</Row>
<Row>
<OrderID>10254</OrderID>
<CustomerID>CHOPS</CustomerID>
<EmployeeID>5</EmployeeID>
<OrderDate>2010-07-11 00:00:00.000</OrderDate>
<RequiredDate>2010-08-08 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-23 00:00:00.000</ShippedDate>
<ShipVia>2</ShipVia>
<Freight>22.98</Freight>
<ShipName>Chop-suey Chinese</ShipName>
<ShipAddress>Hauptstr #31</ShipAddress>
<ShipCity>Bern</ShipCity>
<ShipPostalCode>3012</ShipPostalCode>
<ShipCountry>Switzerland</ShipCountry>
</Row>
<Row>
<OrderID>10255</OrderID>
<CustomerID>RICSU</CustomerID>
<EmployeeID>9</EmployeeID>
<OrderDate>2010-07-12 00:00:00.000</OrderDate>
<RequiredDate>2010-08-09 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-15 00:00:00.000</ShippedDate>
<ShipVia>3</ShipVia>
<Freight>148.33</Freight>
<ShipName>Richter Supermarkt</ShipName>
<ShipAddress>Starenweg 5</ShipAddress>
<ShipCity>Genève</ShipCity>
<ShipPostalCode>1204</ShipPostalCode>
<ShipCountry>Switzerland</ShipCountry>
</Row>
<Row>
<OrderID>10256</OrderID>
<CustomerID>WELLI</CustomerID>
<EmployeeID>3</EmployeeID>
<OrderDate>2010-07-15 00:00:00.000</OrderDate>
<RequiredDate>2010-08-12 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-17 00:00:00.000</ShippedDate>
<ShipVia>2</ShipVia>
<Freight>13.97</Freight>
<ShipName>Wellington Importadora</ShipName>
<ShipAddress>Rua do Mercado #12</ShipAddress>
<ShipCity>Resende</ShipCity>
<ShipRegion>SP</ShipRegion>
<ShipPostalCode>08737-363</ShipPostalCode>
<ShipCountry>Brazil</ShipCountry>
</Row>
<Row>
<OrderID>10257</OrderID>
<CustomerID>HILAA</CustomerID>
<EmployeeID>4</EmployeeID>
<OrderDate>2010-07-16 00:00:00.000</OrderDate>
<RequiredDate>2010-08-13 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-22 00:00:00.000</ShippedDate>
<ShipVia>3</ShipVia>
<Freight>81.91</Freight>
<ShipName>HILARION-Abastos</ShipName>
<ShipAddress>Carrera 22 con Ave. Carlos Soublette #8-35</ShipAddress>
<ShipCity>San Cristóbal</ShipCity>
<ShipRegion>Táchira</ShipRegion>
<ShipPostalCode>5022</ShipPostalCode>
<ShipCountry>Venezuela</ShipCountry>
</Row>
<Row>
<OrderID>10258</OrderID>
<CustomerID>ERNSH</CustomerID>
<EmployeeID>1</EmployeeID>
<OrderDate>2010-07-17 00:00:00.000</OrderDate>
<RequiredDate>2010-08-14 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-23 00:00:00.000</ShippedDate>
<ShipVia>1</ShipVia>
<Freight>140.51</Freight>
<ShipName>Ernst Handel</ShipName>
<ShipAddress>Kirchgasse 6</ShipAddress>
<ShipCity>Graz</ShipCity>
<ShipPostalCode>8010</ShipPostalCode>
<ShipCountry>Austria</ShipCountry>
</Row>
<Row>
<OrderID>10259</OrderID>
<CustomerID>CENTC</CustomerID>
<EmployeeID>4</EmployeeID>
<OrderDate>2010-07-18 00:00:00.000</OrderDate>
<RequiredDate>2010-08-15 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-25 00:00:00.000</ShippedDate>
<ShipVia>3</ShipVia>
<Freight>3.25</Freight>
<ShipName>Centro comercial Moctezuma</ShipName>
<ShipAddress>Sierras de Granada 9993</ShipAddress>
<ShipCity>México D.F.</ShipCity>
<ShipPostalCode>5022</ShipPostalCode>
<ShipCountry>Mexico</ShipCountry>
</Row>
<Row>
<OrderID>10260</OrderID>
<CustomerID>OTTIK</CustomerID>
<EmployeeID>4</EmployeeID>
<OrderDate>2010-07-19 00:00:00.000</OrderDate>
<RequiredDate>2010-08-16 00:00:00.000</RequiredDate>
<ShippedDate>2010-07-29 00:00:00.000</ShippedDate>
<ShipVia>1</ShipVia>
<Freight>55.09</Freight>
<ShipName>Ottilies Käseladen</ShipName>
<ShipAddress>Mehrheimerstr. 369</ShipAddress>
<ShipCity>Köln</ShipCity>
<ShipPostalCode>50739</ShipPostalCode>
<ShipCountry>Germany</ShipCountry>
</Row>
</Root>0 -
Try these last bits of code. The first one is the XML for the report. The second is the XML data for my example.
My original post had an issue with the css selector id. That may have been the issue with your attempt with my code, but I had the correct division id within my code and it still didn't work. Hopefully this last code block will work. It works in my environment.0
Please sign in to leave a comment.
Comments
9 comments