KACE Product Support

Hi,

I'm trying to make a graph of the different Mac OSX operating systems we have. I've gotten as far as the sql code but I'm getting SC_Connect errors on the report. Help ??

SELECT MACHINE.NAME AS SYSTEM_NAME,
(if( OS_NAME
regexp '.*Mac.*10\.4.*' , 'Mac OS X Tiger',  if( OS_NAME
regexp '.*Mac.*10\.5.*' , 'Mac OS X Leopard',  if( OS_NAME
regexp '.*Mac.*10\.6.*' , 'Mac OS X Snow Leopard', if( OS_NAME
regexp '.*Mac.*10\.7.*' , 'Mac OS X Lion', OS_NAME)))))
as 'OS_NAME'
FROM MACHINE

XML

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport name="landscape" columnCount="1" printOrder="Vertical" orientation="Portrait" pageWidth="842" pageHeight="595" columnWidth="782" columnSpacing="0" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20" whenNoDataType="AllSectionsNoDetail" isTitleNewPage="false" isSummaryNewPage="false">
 <property name="ireport.scriptlethandling" value="0"/>
 <property name="ireport.encoding" value="UTF-8"/>
 <import value="java.util.*"/>
 <import value="net.sf.jasperreports.engine.*"/>
 <import value="net.sf.jasperreports.engine.data.*"/>
 <style name="GroupLabel" mode="Transparent" fontName="Arial" fontSize="10" forecolor="#FFFFFF" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 <style name="GroupHeader" mode="Transparent" fontName="Arial" fontSize="10" forecolor="#FFFFFF" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"> 
  <box leftPadding="3"/>
 </style>
 <style name="GroupFooter" mode="Transparent" fontName="Arial" fontSize="10" forecolor="#e5e5e5" backcolor="#e5e5e5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
  <box leftPadding="3"/>
 </style> 
 <style name="ColumnHeaderFooter" mode="Transparent" isDefault="false" fontName="Arial" fontSize="10" forecolor="#FFFFFF" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
  <box leftPadding="3"/>
 </style>
 <style name="ColumnHeaderPanel" isDefault="false" mode="Opaque" forecolor="#FFFFFF" backcolor="#006699" fill="Solid" fontName="Arial" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 <style name="GroupHeaderPanel" isDefault="false" mode="Opaque" forecolor="#FFFFFF" backcolor="#999999" fill="Solid" fontName="Arial" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 <style name="ReportHeader" mode="Transparent" forecolor="#000000" isDefault="false" fontName="Arial" fontSize="16" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
  <box/>
 </style>
 <style name="Detail" mode="Transparent" forecolor="#000000" isDefault="false" fontName="Arial" fontSize="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false">
  <box/>
 </style> 
 <style name="detail_small" isDefault="false" fontName="Arial" fontSize="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 <style name="detail_large" isDefault="false" fontName="Arial" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" isPdfEmbedded="false"/>
 <style name="columnLabel" isDefault="false" forecolor="#000000" fontName="Arial" fontSize="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" isPdfEmbedded="false"/>
 
 <queryString><![CDATA[SELECT MACHINE.NAME AS SYSTEM_NAME,
(if( OS_NAME
regexp '.*Mac.*10\.4.*' , 'Mac OS X Tiger',  if( OS_NAME
regexp '.*Mac.*10\.5.*' , 'Mac OS X Leopard',  if( OS_NAME
regexp '.*Mac.*10\.6.*' , 'Mac OS X Snow Leopard', if( OS_NAME
regexp '.*Mac.*10\.7.*' , 'Mac OS X Lion', OS_NAME)))))
as 'OS_NAME'
FROM MACHINE ]]></queryString>

 
        <field name="SYSTEM_NAME" class="java.lang.String"/>
                <field name="OS_NAME" class="java.lang.String"/>
                <field name="SC_CONNECT_TIME" class="java.lang.String"/>
       
 
 
<group name="SC_CONNECT_TIME">
            <groupExpression><![CDATA[$F{SC_CONNECT_TIME}]]></groupExpression>
            <groupHeader>
            <band height="25">
                <frame>   
                <reportElement style="GroupHeaderPanel" mode="Opaque" x="0" y="0" width="782" height="25" key="frame-1"/>
                <staticText>
                    <reportElement x="0" y="0" width="96" height="25" forecolor="#FFFFFF" key="staticText"/>
      <box>     
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textElement textAlignment="Left">
                        <font size="9" isBold="false"/>
                    </textElement>
                <text><![CDATA[SC_CONNECT_TIME]]></text>
                </staticText>
                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
                    <reportElement x="102" y="0" width="548" height="25" forecolor="#FFFFFF" key="textField"/>
                    <box>
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textElement textAlignment="Left">
                        <font size="9" isBold="false"/>
                    </textElement>
                <textFieldExpression class="java.lang.String"><![CDATA[$F{SC_CONNECT_TIME}]]></textFieldExpression>
                </textField>
                </frame>
            </band>
            </groupHeader>
            <groupFooter>
            <band height="16">
                <frame>                   
     <reportElement style="GroupFooter" mode="Opaque" x="0" y="0" width="782" height="16" key="frame-2"/>
     <box leftPadding="3"/>
                <line direction="BottomUp">
                    <reportElement x="0" y="15" width="779" height="0" forecolor="#000000" key="line"/>
                    <graphicElement stretchType="NoStretch">
      <pen lineWidth="0.25" lineStyle="Solid"/>
     </graphicElement>
                </line>
                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
                    <reportElement x="0" y="1" width="548" height="15" forecolor="#000000" key="textField"/>
     <box>
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textElement textAlignment="Left">
                        <font size="9" isBold="false"/>
                    </textElement>
                <textFieldExpression class="java.lang.String"><![CDATA[$V{SC_CONNECT_TIME_COUNT} + " records with category of " + $F{SC_CONNECT_TIME}]]></textFieldExpression>
                </textField>
                </frame>
            </band>
            </groupFooter>
        </group>
 
 <background>
  <band height="0">
  </band>
 </background>
 
 <title>
  <band height="100">
   <line direction="TopDown">
    <reportElement x="0" y="98" width="781" height="0" forecolor="#808080" key="line"/>
    <graphicElement stretchType="NoStretch">
      <pen lineWidth="2.0" lineStyle="Solid"/>
    </graphicElement>
   </line>
   <line direction="TopDown">
    <reportElement x="0" y="3" width="781" height="0" forecolor="#808080" key="line"/>
    <graphicElement stretchType="NoStretch">
      <pen lineWidth="2.0" lineStyle="Solid"/>
    </graphicElement>
   </line>
   <staticText>
    <reportElement style="ReportHeader" x="99" y="5" width="465" height="27" key="staticText-1"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <text><![CDATA[Copy of Graph of Platform OS]]></text>
   </staticText>
   
   <textField isStretchWithOverflow="false" pattern="'Generated :' EEEEE dd MMMMM yyyy 'at' HH:mm:ss" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
    <reportElement style="detail_large" x="99" y="38" width="465" height="20" key="staticText-1"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <textFieldExpression class="java.util.Date"><![CDATA[new Date()]]></textFieldExpression>
   </textField>

   <staticText>
    <reportElement style="detail_large" x="99" y="58" width="465" height="20" key="staticText-4"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <text><![CDATA[Category :Software]]></text>
   </staticText>
   
   <staticText>
    <reportElement style="detail_large" x="3" y="78" width="777" height="20" key="staticText-5"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <text><![CDATA[Description:]]></text>
   </staticText>
   
   <image evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
    <reportElement x="3" y="5" width="85" height="30" key="image-1"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>    
    <graphicElement stretchType="NoStretch"/>
    <imageExpression class="java.lang.String"><![CDATA["/kbox/kboxwww//adminui/reports/KBOX_logo.jpg"]]></imageExpression>
   </image>
  </band>
 </title>

 <pageHeader>
  <band height="0">
  </band>
 </pageHeader>
 
 <columnHeader>
  <band height="20">
   <frame>
    <reportElement style="ColumnHeaderPanel" mode="Opaque" x="0" y="0" width="782" height="20" key="frame-2"/>
   
   <line direction="BottomUp">
    <reportElement x="0" y="0" width="782" height="0" forecolor="#000000" key="line"/>
    <graphicElement stretchType="NoStretch">
      <pen lineWidth="0.25" lineStyle="Solid"/>
    </graphicElement>
   </line>
   
   <line direction="BottomUp">
    <reportElement x="0" y="19" width="782" height="0" forecolor="#000000" key="line"/>
    <graphicElement stretchType="NoStretch">
      <pen lineWidth="0.25" lineStyle="Solid"/>
    </graphicElement>
   </line>
   
   
                <staticText>
                    <reportElement style="ColumnHeaderFooter" x="0" y="0" width="391" height="15" key="staticText "/>
                    <box>
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textElement textAlignment="Left">
                        <font pdfEncoding="Cp1250"/>
                    </textElement>
                    <text><![CDATA[SYSTEM_NAME]]></text>
                </staticText>                <staticText>
                    <reportElement style="ColumnHeaderFooter" x="391" y="0" width="261" height="15" key="staticText "/>
                    <box>
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textElement textAlignment="Left">
                        <font pdfEncoding="Cp1250"/>
                    </textElement>
                    <text><![CDATA[OS_NAME]]></text>
                </staticText>
   </frame>
  </band>
 </columnHeader>
 
 <detail>
  <band height="17">
   <line direction="TopDown">
    <reportElement x="0" y="0" width="782" height="0" forecolor="#808080" backcolor="#FFFFFF" key="line"/>
    <graphicElement stretchType="NoStretch"/>
   </line>
   
                <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
                    <reportElement style="Detail" x="0" y="0" width="391" height="15" key="textField"/>
     <box>
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textFieldExpression class="java.lang.String"><![CDATA[$F{SYSTEM_NAME}]]></textFieldExpression>
                </textField>
                                <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
                    <reportElement style="Detail" x="391" y="0" width="261" height="15" key="textField"/>
     <box>
      <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
     </box>
                    <textFieldExpression class="java.lang.String"><![CDATA[$F{OS_NAME}]]></textFieldExpression>
                </textField>
               
  </band>
 </detail>
 
 <columnFooter>
  <band height="0">
  </band>
 </columnFooter>
 <pageFooter>
  <band height="27">
   <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
    <reportElement x="572" y="6" width="170" height="19" key="textField"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <textElement textAlignment="Right">
     <font size="10" isBold="false"/>
    </textElement>
   <textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]></textFieldExpression>
   </textField>
   <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Report" hyperlinkType="None" hyperlinkTarget="Self">
    <reportElement x="746" y="6" width="36" height="19" key="textField"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <textElement textAlignment="Left">
     <font size="10" isBold="false"/>
    </textElement>
   <textFieldExpression class="java.lang.String"><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
   </textField>
   <line direction="TopDown">
    <reportElement x="0" y="3" width="782" height="0" forecolor="#000000" key="line"/>
    <graphicElement stretchType="NoStretch">
      <pen lineWidth="2.0" lineStyle="Solid"/>
    </graphicElement>
   </line>
   <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
    <reportElement x="1" y="6" width="209" height="19" key="textField"/>
    <box>
     <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
      <bottomPen lineWidth="0.0" lineColor="#000000"/>
      <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
    </box>
    <textElement>
     <font size="10" isBold="false"/>
    </textElement>
   <textFieldExpression class="java.util.Date"><![CDATA[new Date()]]></textFieldExpression>
   </textField>
  </band>
 </pageFooter>
 <summary>
  <band height="0">
  </band>
 </summary>
</jasperReport>