Jump to content
Sign in to follow this  
talsnir

XML Validation problem

Recommended Posts

Hello All,As I am stepping my first steps in XSD world I am encountering some difficulties. I guess that for a more experienced programmer my problem will look very easy and will be solved in no time but I can't see what I am doing wrong, so please help!I am trying to establish a standartd validation process for my XML file (ReportsConfig.xml). I've built in a corresponding schema (ReportsConfig.xsd), I've followed the instructions described in the w3schools tutorial but I still have problems.The reportsConfig XML file should contain a root element reports. The reports element should contain at least one report element that should contain at least one column element and a set of specific attibutes.Here is the schema:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">	<xs:attribute name="id" type="xs:string"/>	<xs:attribute name="label" type="xs:string"/>	<xs:attribute name="description" type="xs:string"/>	<xs:attribute name="type">		<xs:simpleType>			<xs:restriction base="xs:string">				<xs:enumeration value="query"/>				<xs:enumeration value="query_keywords"/>				<xs:enumeration value="query_no_results"/>				<xs:enumeration value="products"/>				<xs:enumeration value="biz_scoring"/>				<xs:enumeration value="report_mvt"/>				<xs:enumeration value="report_rules_summary"/>				<xs:enumeration value="report_rules_templates"/>				<xs:enumeration value="report_rules_triggered"/>				<xs:enumeration value="report_builiding_blocks"/>				<xs:enumeration value="report_metric_policy"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="pageSize">		<xs:simpleType>			<xs:restriction base="xs:integer">				<xs:minInclusive value="0"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="topRecords">		<xs:simpleType>			<xs:restriction base="xs:integer">				<xs:minInclusive value="0"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="sortOrder">		<xs:simpleType>			<xs:restriction base="xs:integer">				<xs:enumeration value="1"/>				<xs:enumeration value="2"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="exportable" type="xs:boolean"/>	<xs:attribute name="exportSize">		<xs:simpleType>			<xs:restriction base="xs:integer">				<xs:minInclusive value="0"/>				<xs:maxInclusive value="65535"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="alertable" type="xs:boolean"/>	<xs:attribute name="alertSize">		<xs:simpleType>			<xs:restriction base="xs:integer">				<xs:minInclusive value="0"/>				<xs:maxInclusive value="65535"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="searchable" type="xs:boolean"/>	<xs:attribute name="hidden" type="xs:boolean"/>	<xs:attribute name="pixelWidth">		<xs:simpleType>			<xs:restriction base="xs:integer">				<xs:minInclusive value="0"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="dataType">		<xs:simpleType>			<xs:restriction base="xs:string">				<xs:enumeration value="number"/>				<xs:enumeration value="percent"/>				<xs:enumeration value="rank"/>				<xs:enumeration value="string"/>				<xs:enumeration value="date"/>				<xs:enumeration value="bar"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="filterType">		<xs:simpleType>			<xs:restriction base="xs:string">				<xs:enumeration value="number"/>				<xs:enumeration value="refine"/>				<xs:enumeration value="category"/>				<xs:enumeration value="text"/>				<xs:enumeration value="date"/>				<xs:enumeration value="radio"/>				<xs:enumeration value="enum"/>			</xs:restriction>		</xs:simpleType>	</xs:attribute>	<xs:attribute name="format" type="xs:string"/>	<!--Modification is Required !-->	<xs:attribute name="sortColumn" type="xs:string"/>    	<xs:element name="reports">		<xs:complexType>			<xs:sequence>				<xs:element name="report" minOccurs="1" maxOccurs="unbounded">					<xs:complexType>						<xs:sequence>							<xs:element name="column" minOccurs="1" maxOccurs="unbounded">								<xs:complexType>									<xs:attribute ref="id" use="required"/>									<xs:attribute ref="label" use="required"/>									<xs:attribute ref="description" use="optional"/>									<xs:attribute ref="hidden" use="optional"/>									<xs:attribute ref="searchable" use="optional"/>									<xs:attribute ref="pixelWidth" use="optional"/>									<xs:attribute ref="exportable" use="optional"/>									<xs:attribute ref="alertable" use="optional"/>								</xs:complexType>							</xs:element>						</xs:sequence>						<xs:attribute ref="id" use="required"/>						<xs:attribute ref="label" use="required"/>						<xs:attribute ref="description" use="optional"/>						<xs:attribute ref="hidden" use="optional"/>						<xs:attribute ref="pixelWidth" use="optional"/>						<xs:attribute ref="searchable" use="optional"/>						<xs:attribute ref="exportable" use="optional"/>						<xs:attribute ref="alertable" use="optional"/>					</xs:complexType>				</xs:element>			</xs:sequence>		</xs:complexType>	</xs:element>	</xs:schema>

and here is the XML file:

<?xml version="1.0" encoding="UTF-8"?><reports>	<report id="products" label="Products" type="products" pageSize="40" topRecords="200" sortOrder="2" sortColumn="profit_margin" exportable="true" exportSize="1000" alertable="true" alertSize="200">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="80"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="250"/>			<column id="Price" label="Price" description="Price of this product" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number"/>			<column id="inventory_level" label="Inventory" description="Number of items of this product in inventory" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="profit_margin" label="% Profit" description="% Profit Margin for this product" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="percent" format="#0.0"/>			<column id="freshness" label="Freshness" description="Score indicating how new the product is" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="revenue" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="conversion" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="refinements" label="Refinements" hidden="true" exportable="false" searchable="false" pixelWidth="50" filterType="refine"/>			<column id="free_text" label="Free Search" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="text"/>		</columns>	</report>	<report id="productByInventoryLevel" label="High Inventory Products" type="products" navBar="true" pageSize="40" topRecords="200" sortOrder="2" sortColumn="inventory_level" exportable="true" exportSize="1000" alertable="true" alertSize="200">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="80"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="250"/>			<column id="Price" label="Price" description="Price of this product" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number" format="#0.00"/>			<column id="inventory_level" label="Inventory" description="Number of items of this product in inventory" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="profit_margin" label="% Profit" description="% Profit Margin for this product" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="percent" format="#0.0"/>			<column id="freshness" label="Freshness" description="Score indicating how new the product is" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.0"/>			<column id="revenue" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="conversion" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="refinements" label="Refinements" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="refine"/>			<column id="free_text" label="Free Search" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="text"/>		</columns>	</report>	<report id="productByFreshness" label="New Products" type="products" navBar="true" pageSize="40" topRecords="200" sortOrder="2" sortColumn="freshness"  exportable="true" exportSize="1000" alertable="true" alertSize="200">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="80"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="250"/>			<column id="Price" label="Price" description="Price of this product" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number" format="#0.00"/>			<column id="inventory_level" label="Inventory" description="Number of items of this product in inventory" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="profit_margin" label="% Profit" description="% Profit Margin for this product" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="percent" format="#0.0"/>			<column id="freshness" label="Freshness" description="Score indicating how new the product is" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.0"/>			<column id="revenue" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="conversion" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="refinements" label="Refinements" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="refine"/>			<column id="free_text" label="Free Search" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="text"/>		</columns>	</report>	<report id="productByRevenue" label="Top Selling Products (Revenue)" type="products" navBar="true" pageSize="40" topRecords="200" sortOrder="2" sortColumn="revenue"  exportable="true" exportSize="1000" alertable="true" alertSize="200">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="80"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="250"/>			<column id="Price" label="Price" description="Price of this product" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number"/>			<column id="inventory_level" label="Inventory" description="Number of items of this product in inventory" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="profit_margin" label="% Profit " description="% Profit Margin for this product" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="percent" format="#0.0"/>			<column id="freshness" label="Freshness" description="Score indicating how new the product is" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="revenue" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="conversion" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="refinements" label="Refinements" hidden="true" exportable="false" searchable="false" pixelWidth="50" filterType="refine"/>			<column id="free_text" label="Free Search" hidden="true" exportable="false" searchable="false" pixelWidth="50" filterType="text"/>		</columns>	</report>	<report id="productBySold" label="Top Selling Products (Conversion)" type="products" navBar="true" pageSize="40" topRecords="200" sortOrder="2" sortColumn="conversion"  exportable="true" exportSize="1000" alertable="true" alertSize="200">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="80"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="250"/>			<column id="Price" label="Price" description="Price of this product" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number" format="#0.00"/>			<column id="inventory_level" label="Inventory" description="Number of items of this product in inventory" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="profit_margin" label="% Profit" description="% Profit Margin for this product" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="percent" format="#0.0"/>			<column id="freshness" label="Freshness" description="Score indicating how new the product is" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.0"/>			<column id="revenue" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="conversion" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="refinements" label="Refinements" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="refine"/>			<column id="free_text" label="Free Search" hidden="true" exportable="false" searchable="false"  pixelWidth="50" filterType="text"/>		</columns>	</report>	<report id="bizScoring" type="biz_scoring" pageSize="40" topRecords="200" sortOrder="2" sortColumn="$score" tableName=""  exportable="true" exportSize="1000" alertable="true" alertSize="200">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="60"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="300"/>			<column id="$score" label="Rank" description="Rank" hidden="false" searchable="false" pixelWidth="80" dataType="rank"/>			<column id="conversion" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="freshness" label="Freshness" description="Score indicating how new the product is" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.0"/>			<column id="inventory_level" label="Inventory" description="Number of items of this product in inventory" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="revenue" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" format="#0.00"/>			<column id="Price" label="Price" description="Price of this product" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number" format="#0.00"/>			<column id="profit_margin" label="% Profit" description="% Profit Margin for this product" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="percent" format="#0.0"/>			<column id="refinements" label="Refinements" hidden="true" exportable="false" searchable="false" pixelWidth="50" filterType="refine"/>			<column id="free_text" label="Free Search" hidden="true" exportable="false" searchable="false" pixelWidth="50" filterType="text"/>		</columns>	</report>	<report id="query" type="query" navBar="true" pageSize="12" topRecords="1000" sortOrder="2" sortColumn="SEARCH_COUNT" tableName="DAILY_TOP_SEARCHES" label="Keyword and navigation searches"  exportable="true" exportSize="5000" alertable="true" alertSize="200">		<query>			<daily>                        select * from                            (select                                CriteriaDictionaryHashKey,                                KEYWORDS,                                TIME_FRAME,                                CATEGORY_ID,                                CATEGORY_NAME,                                CATEGORY_PATH,                                OTHER_CRITERIA,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                ITEMS_VIEWED,                               NUMBER_OF_ORDERS,                              TOTAL_SALES,                               NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from DAILY_TOP_SEARCHES as searches {0} and page_type=''Search+Nav'') as results                             where true=true                                                  </daily>			<weekly>                         				select * from                            (select                                CriteriaDictionaryHashKey,                                KEYWORDS,                                TIME_FRAME,                                CATEGORY_ID,                                CATEGORY_NAME,                                CATEGORY_PATH,                                OTHER_CRITERIA,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                ITEMS_VIEWED,                               NUMBER_OF_ORDERS,                              TOTAL_SALES,                               NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from WEEKLY_TOP_SEARCHES as searches {0} and page_type=''Search+Nav'') as results                             where true=true                                                                                                    </weekly>			<monthly>                         select * from                            (select                                CriteriaDictionaryHashKey,                                KEYWORDS,                                TIME_FRAME,                                CATEGORY_ID,                                CATEGORY_NAME,                                CATEGORY_PATH,                                OTHER_CRITERIA,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                ITEMS_VIEWED,                               	NUMBER_OF_ORDERS,                              	TOTAL_SALES,                               	NUMBER_OF_SEARCHES AS SEARCH_COUNT                             from MONTHLY_TOP_SEARCHES as searches {0} and page_type=''Search+Nav'') as results                             where true=true                                                                           </monthly>			<custom>				select * from                            (select                                CriteriaDictionaryHashKey,                                KEYWORDS,                                TIME_FRAME,                                CATEGORY_ID,                                CATEGORY_NAME,                                CATEGORY_PATH,                                OTHER_CRITERIA,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                CAST(sum(ITEMS_VIEWED) as unsigned) AS 'ITEMS_VIEWED',                                CAST(sum(NUMBER_OF_ORDERS) as unsigned) AS 'NUMBER_OF_ORDERS',                                CAST(sum(TOTAL_SALES) as decimal) AS 'TOTAL_SALES',                                CAST(sum(NUMBER_OF_SEARCHES) as unsigned) AS 'SEARCH_COUNT'                            from DAILY_TOP_SEARCHES as searches {0} and page_type=''Search+Nav''                            group by CriteriaDictionaryHashKey) as results                            where true=true                                                                           </custom>		</query>		<columns>			<column id="CriteriaDictionaryHashKey" label="CriteriaDictionaryHashKey" hidden="true" exportable="false" searchable="false" pixelWidth="50"/>			<column id="TIME_FRAME" label="TIME_FRAME" hidden="true" exportable="false" searchable="false" pixelWidth="50"/>			<column id="KEYWORDS" label="Keywords" hidden="false" searchable="true" pixelWidth="120" filterType="text"/>			<column id="CATEGORY_PATH" label="Navigation Category" hidden="true" exportable="false" searchable="true" pixelWidth="250" filterType="category"/>			<column id="CATEGORY_ID" label="CATEGORY_ID" hidden="true" exportable="false" searchable="false" pixelWidth="50"/>			<column id="CATEGORY_NAME" label="Navigation Category" hidden="false" searchable="false" pixelWidth="180" tooltipColumnId="CATEGORY_PATH"/>			<column id="OTHER_CRITERIA" label="Refinements" hidden="false" searchable="true" pixelWidth="180" filterType="text"/>			<column id="SEARCH_COUNT" label="Count" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number"/>			<column id="NUMBER_OF_RESULTS" label="Results (#)" hidden="false" searchable="false" pixelWidth="85" filterType="number" dataType="number"/>			<column id="RESULTS_TYPE" label="Result Type" hidden="true" exportable="false" searchable="false" pixelWidth="105" filterType="enum"/>			<column id="PAGE_TYPE" label="Page Type" hidden="true" exportable="false" searchable="false" pixelWidth="110" filterType="enum"/>			<column id="IS_FREE" label="Is Free" hidden="true" exportable="false" searchable="false" pixelWidth="110" filterType="enum"/>			<column id="IS_NAV" label="Is Nav" hidden="true" exportable="false" searchable="false" pixelWidth="110" filterType="enum"/>			<column id="HAS_SUGGESTIONS" label="Alternative" hidden="true" exportable="false" searchable="false" pixelWidth="130" filterType="enum"/>			<column id="RBT_USED" label="RBT Used" hidden="true" exportable="false" searchable="false" pixelWidth="120" filterType="enum"/>			<column id="DOMINANT_BU" label="Dominant Business Unit" hidden="true" exportable="false" searchable="false" pixelWidth="120" filterType="enum"/>			<column id="DOMINANT_CATEGORY_PATH" label="Result Categories" hidden="true" exportable="false" searchable="false" pixelWidth="160" filterType="category"/>			<column id="DOMINANT_BRAND" label="Result Brands" hidden="true" exportable="false" searchable="false" pixelWidth="140" filterType="enum"/>			<column id="TOTAL_SALES" label="Revenue" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="NUMBER_OF_ORDERS" label="Items Sold" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number" filterValue=""/>			<column id="ITEMS_VIEWED" label="Clicks (#)" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number"/>		</columns>	</report>		<report id="loadStats" searchable="false" filterable="false" saveViewEnabled="false" pageable="false" type="custom" navBar="true" pageSize="12" topRecords="1000" sortOrder="2" sortColumn="SEARCH_COUNT" tableName="TOP_SEARCHES" label="Load Statistics"  exportable="true" exportSize="5000" alertable="true" alertSize="200">		<query>			<daily>								select *			from(				select distinct				TIME_FRAME,				DATE_FORMAT(TIME_FRAME, ''%H:%i'') as TIME_FRAME_STR,				SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT,				SUM(NUMBER_OF_SEARCHES) * 100 / 				(select max(SEARCH_COUNT)				from(					select distinct					SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT					from HOURLY_TOP_SEARCHES as searches  {0}					and page_type=''Search+Nav''					GROUP BY TIME_FRAME) as results			 	limit 1000) as SEARCH_COUNT_BAR				from HOURLY_TOP_SEARCHES as searches  {0}				and page_type=''Search+Nav''				GROUP BY TIME_FRAME) as results				where true=true								</daily>					<weekly>				select *			from(				select distinct				TIME_FRAME,				DATE_FORMAT(TIME_FRAME, ''%m/%d/%Y'') as TIME_FRAME_STR,				SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT,				SUM(NUMBER_OF_SEARCHES) * 100 / 				(select max(SEARCH_COUNT)				from(					select distinct					SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT					from DAILY_TOP_SEARCHES as searches  {0}					and page_type=''Search+Nav''					GROUP BY TIME_FRAME) as results			 	limit 1000) as SEARCH_COUNT_BAR				from DAILY_TOP_SEARCHES as searches  {0}				and page_type=''Search+Nav''				GROUP BY TIME_FRAME) as results				where true=true							</weekly>							<monthly>			select *			from(				select distinct				TIME_FRAME,				DATE_FORMAT(TIME_FRAME, ''%m/%d/%Y'') as TIME_FRAME_STR,				SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT,				SUM(NUMBER_OF_SEARCHES) * 100 / 				(select max(SEARCH_COUNT)				from(					select distinct					SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT					from DAILY_TOP_SEARCHES as searches  {0}					and page_type=''Search+Nav''					GROUP BY TIME_FRAME) as results			 	limit 1000) as SEARCH_COUNT_BAR				from DAILY_TOP_SEARCHES as searches  {0}				and page_type=''Search+Nav''				GROUP BY TIME_FRAME) as results				where true=true			</monthly>											<custom>				select *			from(				select distinct				TIME_FRAME,				DATE_FORMAT(TIME_FRAME, ''%m/%d/%Y'') as TIME_FRAME_STR,				SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT,				SUM(NUMBER_OF_SEARCHES) * 100 / 				(select max(SEARCH_COUNT)				from(					select distinct					SUM(NUMBER_OF_SEARCHES)  as SEARCH_COUNT					from DAILY_TOP_SEARCHES as searches  {0}					and page_type=''Search+Nav''					GROUP BY TIME_FRAME) as results			 	limit 1000) as SEARCH_COUNT_BAR				from DAILY_TOP_SEARCHES as searches  {0}				and page_type=''Search+Nav''				GROUP BY TIME_FRAME) as results				where true=true						</custom>			                                                                        </query>		<columns>			<column id="TIME_FRAME" label="Date" hidden="true" searchable="false" pixelWidth="150" dataType="date"/>			<column id="TIME_FRAME_STR" label="Date" hidden="false" searchable="false" exportable="false" pixelWidth="100" filterType="date" dataColumnId="TIME_FRAME"/>			<column id="SEARCH_COUNT" label="Count" hidden="false" searchable="false" pixelWidth="70" filterType="number" dataType="number"/>			<column id="SEARCH_COUNT_BAR" label="" hidden="false" searchable="false" exportable="false" pixelWidth="220" dataType="bar" tooltipColumnId="SEARCH_COUNT"/>		</columns>	</report>						<report id="queryKeywords" type="query_keywords" navBar="true" pageSize="12" topRecords="1000" sortOrder="2" sortColumn="SEARCH_COUNT" tableName="DAILY_KEYWORD_SEARCHES"  exportable="true" exportSize="5000" alertable="true" alertSize="200">		<query>			<custom>                        select * from (                            select                                KEYWORDS,                                TIME_FRAME,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                CAST(sum(ITEMS_VIEWED) as unsigned) AS 'ITEMS_VIEWED',                                CAST(sum(NUMBER_OF_ORDERS) as unsigned) AS 'NUMBER_OF_ORDERS',                                CAST(sum(TOTAL_SALES) as decimal) AS 'TOTAL_SALES',                                CAST(sum(NUMBER_OF_SEARCHES) as unsigned) AS 'SEARCH_COUNT'                            from DAILY_KEYWORD_SEARCHES searches {0} and page_type=''Search+Nav''                            group by KEYWORDS) as results                        where true=true                     </custom>			<daily>			select * from                            (select                                KEYWORDS,                                TIME_FRAME,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                ITEMS_VIEWED,                                NUMBER_OF_ORDERS,                                TOTAL_SALES,                                NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from DAILY_KEYWORD_SEARCHES searches {0} and page_type=''Search+Nav'') as results                            where true=true                                                                        </daily>			<weekly>			select * from                            (select                                KEYWORDS,                                TIME_FRAME,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                ITEMS_VIEWED,                                NUMBER_OF_ORDERS,                                TOTAL_SALES,                                NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from WEEKLY_KEYWORD_SEARCHES searches {0} and page_type=''Search+Nav'') as results                            where true=true                                                                                              </weekly>			<monthly>                        select * from  				(select                                KEYWORDS,                                TIME_FRAME,                                NUMBER_OF_RESULTS,                                RESULTS_TYPE,                                DOMINANT_BU,                                DOMINANT_CATEGORY_PATH,                                DOMINANT_BRAND,                                PAGE_TYPE,                                IS_FREE,                                IS_NAV,                                HAS_SUGGESTIONS,                                RBT_USED,                                ITEMS_VIEWED,                                NUMBER_OF_ORDERS,                                TOTAL_SALES,                                NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from MONTHLY_KEYWORD_SEARCHES searches {0} and page_type=''Search+Nav'') as results                            where true=true                                                                                               </monthly>		</query>		<columns>			<column id="TIME_FRAME" label="TIME_FRAME" hidden="true" exportable="false" searchable="false" pixelWidth="50"/>			<column id="KEYWORDS" label="Keywords" hidden="false" searchable="true" pixelWidth="160" filterType="text"/>			<column id="SEARCH_COUNT" label="Count" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number"/>			<column id="NUMBER_OF_RESULTS" label="Results (#)" hidden="false" searchable="false" pixelWidth="85" filterType="number" dataType="number"/>			<column id="RESULTS_TYPE" label="Result Type" hidden="true" exportable="false" searchable="true" pixelWidth="105" filterType="enum"/>			<column id="PAGE_TYPE" label="Page Type" hidden="true" exportable="false" searchable="false" pixelWidth="110" filterType="enum"/>			<column id="IS_FREE" label="Is Free" hidden="true" exportable="false" searchable="false" pixelWidth="110" filterType="enum"/>			<column id="IS_NAV" label="Is Nav" hidden="true" exportable="false" searchable="false" pixelWidth="110" filterType="enum"/>			<column id="HAS_SUGGESTIONS" label="Alternative" hidden="true" exportable="false" searchable="false" pixelWidth="130" filterType="enum"/>			<column id="RBT_USED" label="RBT Used" hidden="true" exportable="false" searchable="false" pixelWidth="120" filterType="enum"/>			<column id="DOMINANT_BU" label="Dominant BU" hidden="true" exportable="false" searchable="false" pixelWidth="120" filterType="enum"/>			<column id="DOMINANT_CATEGORY_PATH" label="Result Categories" hidden="true" exportable="false" searchable="false" pixelWidth="160" filterType="category"/>			<column id="DOMINANT_BRAND" label="Result Brands" hidden="true" exportable="false" searchable="false" pixelWidth="140" filterType="enum"/>			<column id="TOTAL_SALES" label="Revenue" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>			<column id="NUMBER_OF_ORDERS" label="Items Sold" hidden="false" searchable="false" pixelWidth="80" filterType="number" dataType="number" filterValue=""/>			<column id="ITEMS_VIEWED" label="Clicks (#)" hidden="false" searchable="false" pixelWidth="70" filterType="number" dataType="number"/>		</columns>	</report>	<report id="queryNoResults" type="query_no_results" navBar="true" pageSize="12" topRecords="1000" sortOrder="2" sortColumn="SEARCH_COUNT" tableName="DAILY_TOP_SEARCHES"  exportable="true" exportSize="5000" alertable="true" alertSize="200">		<query>			<custom>                        select * from                            (select                                TIME_FRAME,                                criteriaDictionaryHashKey,                                NUMBER_OF_RESULTS,                                KEYWORDS,                                HAS_SUGGESTIONS,                                CAST(sum(NUMBER_OF_SEARCHES) as unsigned) AS 'SEARCH_COUNT'                            from DAILY_TOP_SEARCHES as searches {0}                            group by CriteriaDictionaryHashKey) as results                        where NUMBER_OF_RESULTS=0                        </custom>                        <daily>                        select * from                            (select                                TIME_FRAME,                                criteriaDictionaryHashKey,                                NUMBER_OF_RESULTS,                                KEYWORDS,                                HAS_SUGGESTIONS,                                NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from DAILY_TOP_SEARCHES as searches {0}                        and NUMBER_OF_RESULTS=0) as results                        where true=true                        </daily>                        <weekly> select * from                            (select                                TIME_FRAME,                                criteriaDictionaryHashKey,                                NUMBER_OF_RESULTS,                                KEYWORDS,                                HAS_SUGGESTIONS,                                NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from WEEKLY_TOP_SEARCHES as searches {0}                        and NUMBER_OF_RESULTS=0) as results                        where true=true                        </weekly>                        <monthly>                        select * from                            (select                                TIME_FRAME,                                criteriaDictionaryHashKey,                                NUMBER_OF_RESULTS,                                KEYWORDS,                                HAS_SUGGESTIONS,                                NUMBER_OF_SEARCHES AS SEARCH_COUNT                            from MONTHLY_TOP_SEARCHES as searches {0}                        and NUMBER_OF_RESULTS=0) as results                        where true=true                        </monthly>                    </query>		<columns>			<column id="TIME_FRAME" label="TIME_FRAME" hidden="true" exportable="false" searchable="false" pixelWidth="50"/>			<column id="KEYWORDS" label="Keywords" hidden="false" searchable="true" pixelWidth="200" filterType="text"/>			<column id="SEARCH_COUNT" label="Count" hidden="false" searchable="false" pixelWidth="120" filterType="number" dataType="number"/>			<column id="CriteriaDictionaryHashKey" label="CriteriaDictionaryHashKey" hidden="true" exportable="false" searchable="false" pixelWidth="50"/>			<column id="HAS_SUGGESTIONS" label="Alternative" hidden="false" pixelWidth="200" filterType="enum"/>		</columns>	</report>			<report id="Revenue" type="report_mvt" pageSize="40" topRecords="100" sortOrder="1" sortColumn="RECIPENAME" tableName="hourly_mvt_impressions" exportable="true" exportSize="5000" alertable="true" alertSize="200">		<query>                        Select * from                            (SELECT                                RULEID,                                RECIPENAME,                                SUM(IMPRESSIONS) AS IMPRESSIONS,                                SUM(TOTAL_SALES) as TOTAL_SALES,                                (100*SUM(TOTAL_SALES))/(SUM(IMPRESSIONS)) as TOTAL_SALES_PERCENT,                                MAX(TOTAL_SALES) as RECIPE_BAR                           FROM DAILY_MVT_IMPRESSIONS {0}                           GROUP BY RULEID,RECIPENAME) as results                         WHERE RULEID={1}                    </query>		<columns>			<column id="RECIPENAME" label="Strategy Name" hidden="false" searchable="true" pixelWidth="250"/>			<column id="IMPRESSIONS" label="Impressions" hidden="false" searchable="true" pixelWidth="100"/>			<column id="RULEID" label="Rule Id" hidden="true" exportable="false" searchable="true" pixelWidth="110"/>			<column id="TOTAL_SALES" label="Revenue" hidden="false" searchable="true" pixelWidth="100" dataType="number"/>			<column id="TOTAL_SALES_PERCENT" label="" hidden="true" exportable="false" searchable="true" pixelWidth="60"/>			<column id="RECIPE_BAR" label="" hidden="false" exportable="false" searchable="true" pixelWidth="130"/>		</columns>	</report>	<report id="Items Sold" type="report_mvt" pageSize="40" topRecords="100" sortOrder="1" sortColumn="RECIPENAME" tableName="hourly_mvt_impressions"  exportable="true" exportSize="5000" alertable="true" alertSize="200">		<query>                        Select * from                            (SELECT                                RULEID,                                RECIPENAME,                                SUM(IMPRESSIONS) AS IMPRESSIONS,                                SUM(ADD_TO_CART_COUNT) as ADD_TO_CART_COUNT,                                (100*SUM(ADD_TO_CART_COUNT))/(SUM(IMPRESSIONS)) as ADD_TO_CART_PERCENT,                                MAX(ADD_TO_CART_COUNT) as RECIPE_BAR                           FROM DAILY_MVT_IMPRESSIONS {0}                           GROUP BY RULEID,RECIPENAME) as results                         WHERE RULEID={1}                    </query>		<columns>			<column id="RECIPENAME" label="Strategy Name" hidden="false" searchable="true" pixelWidth="250"/>			<column id="IMPRESSIONS" label="Impressions" hidden="false" searchable="true" pixelWidth="100"/>			<column id="RULEID" label="Rule Id" hidden="true" exportable="false" searchable="true" pixelWidth="110"/>			<column id="ADD_TO_CART_COUNT" label="Items Sold" hidden="false" searchable="true" pixelWidth="100"/>			<column id="ADD_TO_CART_PERCENT" label="" hidden="false" searchable="true" pixelWidth="60" dataType="percent"/>			<column id="RECIPE_BAR" label="" hidden="false" exportable="false" searchable="true" pixelWidth="130"/>		</columns>	</report>	<report id="Items Clicked" type="report_mvt" pageSize="40" topRecords="100" sortOrder="1" sortColumn="RECIPENAME" tableName="hourly_mvt_impressions"  exportable="true" exportSize="1000" alertable="true" alertSize="200">		<query>                        Select * from                            (SELECT                                RULEID,                                RECIPENAME,                                SUM(IMPRESSIONS) AS IMPRESSIONS,                                SUM(CLICK_THROUGH_COUNT) as CLICK_THROUGH_COUNT,                                (100*SUM(CLICK_THROUGH_COUNT))/(SUM(IMPRESSIONS)) as CLICK_THROUGH_PERCENT,                                MAX(CLICK_THROUGH_COUNT) as RECIPE_BAR                           FROM DAILY_MVT_IMPRESSIONS {0}                           GROUP BY RULEID,RECIPENAME) as results                         WHERE RULEID={1}                    </query>		<columns>			<column id="RECIPENAME" label="Strategy Name" hidden="false" searchable="true" pixelWidth="250"/>			<column id="IMPRESSIONS" label="Impressions" hidden="false" searchable="true" pixelWidth="100"/>			<column id="RULEID" label="Rule Id" hidden="true" exportable="false" searchable="true" pixelWidth="110"/>			<column id="CLICK_THROUGH_COUNT" label="Items Clicked" hidden="false" searchable="true" pixelWidth="100"/>			<column id="CLICK_THROUGH_PERCENT" label="" hidden="false" searchable="true" pixelWidth="60" dataType="percent"/>			<column id="RECIPE_BAR" label="" hidden="false" exportable="false" searchable="true" pixelWidth="130"/>		</columns>	</report>	<!--Home Page Reports -->	<report id="homePageReport1" type="products" pageSize="10" topRecords="10" sortOrder="2" sortColumn="revenue" label="Top Selling Products (Revenue)" linkToReport="productByRevenue">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="55"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="170"/>			<column id="revenue" format="#0.00" label="Revenue" description="Total Revenue ($) for this product during last week" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>		</columns>	</report>	<report id="homePageReport2" type="products" pageSize="10" topRecords="10" sortOrder="2" sortColumn="conversion" tableName="" label="Top Selling Products (Conversion)" linkToReport="productBySold">		<columns>			<column id="prod_id" label="SKU" description="Product Id" hidden="false" searchable="false" pixelWidth="55"/>			<column id="title" label="Title" description="Product Name" hidden="false" searchable="false" pixelWidth="170"/>			<column id="conversion" format="#0.00" label="Conversion" description="Item's average conversion rate" hidden="false" searchable="false" pixelWidth="90" filterType="number" dataType="number"/>		</columns>	</report>	<report id="homePageReport3" type="query_keywords" pageSize="10" topRecords="10" sortOrder="2" sortColumn="SEARCH_COUNT" tableName="DAILY_KEYWORD_SEARCHES" label="Keyword Searches" linkToReport="keywords">		<query>                        select * from (                            select                                KEYWORDS,                                TIME_FRAME,                                NUMBER_OF_RESULTS,                                RESUL

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...