Stacked clustered Bar.Series option?

I am working on a report definition with a unique requirement. Our data shows a series of Encounters in one of 3 EStatuses (Missing, Late, On-Time) across Rooms with a DatePeriod (pre-calculated to the Week Start DT) for each Encounter.

I want to end with a bar chart, stacked with EStatus values (so each bar will have no more than 3 segments - Missing, Late, On-Time). However, these stacked bars need to be grouped by DatePeriod, and within each DatePeriod by Room.

Given the sample data I'm including, the X-Axis should show "06/27/2021", "07/04/2021", "07/11/2021", "07/18/2021", and "07/25/2021". Within each of these dates, there should be a single bar for each Room showing that Room's stacked EStatus EncounterNbrCounts.

The best I've been able to do is a separate bar for each Room AND EStatus across each DatePeriod value, but I want to stack the EStatuses by Room by DatePeriod.

Any ideas on how to make this happen? I'm running Info 12.7 SP8

Sample Code:
<Report ID="Scheduling.On-TimeEncounters" Caption="On-Time Encounters">
  <Body>
    <ChartCanvas ID="ChartCanvas1">
      <DataLayer Type="XMLFile" XMLFile="OTEDataSortedByRoom.xml" MaxRows="200" ID="ChartQuery">
        <DataLayerLink ID="dllData" />
      </DataLayer>
      <Series Type="Bar" ID="BarChartOnTime" BarStacking="SideBySide" ChartXDataColumn="DatePeriod" ChartXDataColumnType="DateTime" Color="@Chart.rdCrosstabValue-BarColor~">
        <DataLayer Type="Linked" LinkedDataLayerID="dllData" ID="linkDataOnTime">
          <CompareFilter CompareType="=" DataColumn="EStatus" ID="compOnTime" CompareValue="On-Time" />
          <CrosstabFilter CrosstabColumn="Room" CrosstabLabelColumn="DatePeriod" CrosstabValueColumn="EncounterNbr" CrosstabValueFunction="Count" ID="ctfGrouped">
            <ExtraCrosstabValueColumn CrosstabValueColumn="BarColor" CrosstabValueFunction="Any" ID="BarColor" />
          </CrosstabFilter>
        </DataLayer>
      </Series>
      <Series Type="Bar" ID="BarChartMissing" BarStacking="SideBySide" ChartXDataColumn="DatePeriod" ChartXDataColumnType="DateTime" Color="@Chart.rdCrosstabValue-BarColor~">
        <DataLayer Type="Linked" LinkedDataLayerID="dllData" ID="linkDataMissing">
          <CompareFilter CompareType="=" DataColumn="EStatus" ID="compMissing" CompareValue="Missing" />
          <CrosstabFilter CrosstabColumn="Room" CrosstabLabelColumn="DatePeriod" CrosstabValueColumn="EncounterNbr" CrosstabValueFunction="Count" ID="ctfGrouped">
            <ExtraCrosstabValueColumn CrosstabValueColumn="BarColor" CrosstabValueFunction="Any" ID="BarColor" />
          </CrosstabFilter>
        </DataLayer>
      </Series>
      <Series Type="Bar" ID="BarChartLate" BarStacking="SideBySide" ChartXDataColumn="DatePeriod" ChartXDataColumnType="DateTime" Color="@Chart.rdCrosstabValue-BarColor~">
        <DataLayer Type="Linked" LinkedDataLayerID="dllData" ID="linkDataMissing">
          <CompareFilter CompareType="=" DataColumn="EStatus" ID="compLate" CompareValue="Late" />
          <CrosstabFilter CrosstabColumn="Room" CrosstabLabelColumn="DatePeriod" CrosstabValueColumn="EncounterNbr" CrosstabValueFunction="Count" ID="ctfGrouped">
            <ExtraCrosstabValueColumn CrosstabValueColumn="BarColor" CrosstabValueFunction="Any" ID="BarColor" />
          </CrosstabFilter>
        </DataLayer>
      </Series>
      <ChartXAxis ID="xDates">
        <AxisLabelStyle FontAngle="55" FontSize="12" Format="MM/dd/yyyy" />
      </ChartXAxis>
    </ChartCanvas>
  </Body>
  <ideTestParams />
</Report>

Sample XML Data:

<?xml version="1.0" encoding="utf-8"?>

</rdData>

VISUI

Logi Info Ninja
September 07, 2021 21:44

Johnny,
As far as I know Logi hasn't exposed hierarchical categorization along the x-Axis. I actually think there is a Highcharts plugin that will provide exactly what you want, but it is not easy to implement.

The closest thing you will probably be able to get with Info out-of-the-box is to use a Crosstab chart with a Calculated column returning a concatenation of DatePeriod and Room used as the Label Column in the Crosstab Filter

Your chart would be defined as follows:

<ChartCanvas>
<DataLayer Type="XMLFile" XMLFile="newXMLFile.xml" ID="ChartQuery">
<Remark>
<DataLayerLink ID="dllData" />
</Remark>
<CalculatedColumn Formula="&quot;@Data.Room~&lt;br&gt;@Data.DatePeriod~&quot;" ID="Room-Date" />
<CrosstabFilter CrosstabColumn="EStatus" CrosstabLabelColumn="Room-Date" CrosstabValueColumn="EncounterNbr" CrosstabValueFunction="DistinctCount" />
</DataLayer>
<Series Type="Bar" ChartYDataColumn="rdCrosstabValue" ChartXDataColumn="rdCrosstab" BarStacking="Stacked" />
<ChartXAxis>
<AxisLabelStyle Format="HTML" />
</ChartXAxis>
</ChartCanvas>

I think this is what you are actually looking for: http://blacklabel.github.io/grouped_categories/

Where in this example the Fruit,Vegetable,Fish would be your Date Grouping then within each Date Group you would have your normal stacked bars for each room by EStatus.

We could probably help you out some more if interested.
johns@visuidesign.com

Johnny Stevens

Logi Info Ninja
Logi Forum Guru
September 08, 2021 14:51
Edited

Wow... Another kick-#$s Solution from Sweazen!!
Shout out to VISUI

I'm going with a hybrid of your first recommendation where I'm concatenating DatePeriod+Room (but only showing Room in the Label) and a separate XAxis showing DatePeriod. The issue that I'm seeing is that the secondary XAxis (DatePeriod) is evenly spaced across the available DatePeriod values (width of chart divided by '5' in this case) while my DatePeriod+Room values in the primary XAxis is spaced based on the available data. This leaves the XAxes misaligned as you can see with TicksAndGrids added here. I need the XAxis for DatePeriod to show Gridlines after the last value in each DatePeriod+Room set, so it should fall after "Zeego-Rm1":

Any ideas on how to align these Axes more closely? There will regularly be situations in the data where the Rooms won't be so evenly spaced across DatePeriods; 11, 11, 11, 10, 10 in the above example could easily be 6, 1, 3, 1, 6 which would make the evenly spaced DateOverlay Axis even further off.

Regards,
Johnny

Logi Info Ninja
Logi Forum Guru
September 09, 2021 10:50

Hey John,

It occurred to me that this is a simple matter of getting the XAxis segments to align with the Count of Rooms per DatePeriod. The SeriesDateOverlay doesn't even need the CrosstabFilter... once we group by DatePeriod and get the Ct of Rooms per DatePeriod, we want the 'length' of each DatePeriod 'segment' on the XAxis to correspond to those values.

I have the following Bar.Series and XAxis for this:
<Series BarStacking="Overlay" ChartXDataColumn="DatePeriod" ChartYDataColumn="yval" Color="#FFF" LinkedToXAxisID="DateOverlay" Type="Bar" ID="SeriesDateOverlay" LegendLabel=" ">
<DataLayer ID="ChartQueryOverlay" Type="XMLFile" XMLFile="OTEDataSortedByRoom.xml">
<GroupFilter GroupColumn="DatePeriod" ID="grpDatePer">
<GroupAggregateColumn AggregateColumn="Room" AggregateFunction="DistinctCount" ID="ctRoomsPerPeriod" />
</GroupFilter>
<SortFilter DataType="Date" SortColumn="DoS" ID="sortDoS" />
<CalculatedColumn Formula="0" ID="yval">
<Note Note="set the yAxis val to zero " />
</CalculatedColumn>
</DataLayer>
</Series><ChartXAxis ID="DateOverlay">
<AxisLabelStyle FontAngle="35" FontSize="12" />
<Remark>
<ChartTicksAndGrid />
</Remark>
</ChartXAxis>

How do I get the length of each DatePeriod segment of the XAxis to correspond to the ctRoomsPerPeriod value (using the earlier XML data - 11, 11, 11, 10, and 10 respectively)?

Thanks,
Johnny

Logi Info Ninja
Logi Forum Guru
September 24, 2021 11:38

Just to keep everyone updated... we are contracting with VISUI and John Sweazen for this integration work.

Stacked clustered Bar.Series option?

Comments

Didn't find what you were looking for?