talsnir Posted March 4, 2007 Share Posted March 4, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now