MD-ADHOC.jsp 10.5 KB
Newer Older
Thitichaipun Wutthisak committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302
<%@page contentType="text/html"%>
<%--
// $Id: //open/mondrian/webapp/MD-ADHOC.jsp#11 $
// This software is subject to the terms of the Common Public License
// Agreement, available at the following URL:
// http://www.opensource.org/licenses/cpl.html.
// (C) Copyright 2001-2005 Kana Software, Inc. and others.
// All Rights Reserved.
// You must accept the terms of that agreement to use this software.
//
// jhyde, 6 August, 2001
--%>
<%
    final String nl = System.getProperty("line.separator");
    String[] queries = new String[] {
        // #0
        "select {[Measures].[Unit Sales]} on columns" + nl +
        " from Sales",

        // mdx sample #1
        "select" + nl +
        "    {[Measures].[Unit Sales]} on columns," + nl +
        "    order(except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows" + nl +
        "from Sales",

        // mdx sample #2
        "select" + nl +
        "    { [Measures].[Units Shipped], [Measures].[Units Ordered] } on columns," + nl +
        "    NON EMPTY [Store].[Store Name].members on rows" + nl +
        "from Warehouse",

        // mdx sample #3
        "with member [Measures].[Store Sales Last Period] as '([Measures].[Store Sales], Time.PrevMember)'" + nl +
        "select" + nl +
        "    {[Measures].[Store Sales Last Period]} on columns," + nl +
        "    {TopCount([Product].[Product Department].members,5, [Measures].[Store Sales Last Period])} on rows" + nl +
        "from Sales" + nl +
        "where ([Time].[1998])",

        // mdx sample #4
        "with member [Measures].[Total Store Sales] as 'Sum(YTD(),[Measures].[Store Sales])'" + nl +
        "select" + nl +
        "    {[Measures].[Total Store Sales]} on columns," + nl +
        "    {TopCount([Product].[Product Department].members,5, [Measures].[Total Store Sales])} on rows" + nl +
        "from Sales" + nl +
        "where ([Time].[1997].[Q2].[4])",

        // mdx sample #5
        "with member [Measures].[Store Profit Rate] as '([Measures].[Store Sales]-[Measures].[Store Cost])/[Measures].[Store Cost]', format = '#.00%'" + nl +
        "select" + nl +
        "    {[Measures].[Store Cost],[Measures].[Store Sales],[Measures].[Store Profit Rate]} on columns," + nl +
        "    Order([Product].[Product Department].members, [Measures].[Store Profit Rate], BDESC) on rows" + nl +
        "from Sales" + nl +
        "where ([Time].[1997])",

        // mdx sample #6
        "with" + nl +
        "   member [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] as '[Product].[All Products].[Drink].[Alcoholic Beverages]/[Product].[All Products].[Drink]', format = '#.00%'" + nl +
        "select" + nl +
        "   { [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] } on columns," + nl +
        "   order([Customers].[All Customers].[USA].[WA].Children, [Product].[All Products].[Drink].[Percent of Alcoholic Drinks],BDESC ) on rows" + nl +
        "from Sales" + nl +
        "where ( [Measures].[Unit Sales] )",

        // mdx sample #7
        "with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])'" + nl +
        "select" + nl +
        "    {[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns," + nl +
        "    {Descendants([Time].[1997],[Time].[Month])} on rows" + nl +
        "from Sales",

        // #8
        "select" + nl +
        " {[Measures].[Unit Sales]} on columns," + nl +
        " [Gender].members on rows" + nl +
        "from Sales",

/*
        // #9
        "with" + nl +
        "  member [Product].[Non dairy] as '[Product].[All Products] - [Product].[Food].[Dairy]'" + nl +
        "  member [Measures].[Dairy ever] as 'sum([Time].members, ([Measures].[Unit Sales],[Product].[Food].[Dairy]))'" + nl +
        "  set [Customers who never bought dairy] as 'filter([Customers].members, [Measures].[Dairy ever] = 0)'" + nl +
        "select" + nl +
        " {[Measures].[Unit Sales], [Measures].[Dairy ever]}  on columns," + nl +
        "  [Customers who never bought dairy] on rows" + nl +
        "from Sales\r\n",
*/

        // #10
        "WITH" + nl +
        "   MEMBER [Measures].[StoreType] AS " + nl +
        "   '[Store].CurrentMember.Properties(\"Store Type\")'," + nl +
        "   SOLVE_ORDER = 2" + nl +
        "   MEMBER [Measures].[ProfitPct] AS " + nl +
        "   '(Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales]'," + nl +
        "   SOLVE_ORDER = 1, FORMAT_STRING = 'Percent'" + nl +
        "SELECT" + nl +
        "   { [Store].[Store Name].Members} ON COLUMNS," + nl +
        "   { [Measures].[Store Sales], [Measures].[Store Cost], [Measures].[StoreType]," + nl +
        "   [Measures].[ProfitPct] } ON ROWS" + nl +
        "FROM Sales",

        // #11
        "WITH" + nl +
        "   MEMBER [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[BigSeller] AS" + nl +
        "  'IIf([Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine] > 100, \"Yes\",\"No\")'" + nl +
        "SELECT" + nl +
        "   {[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[BigSeller]} ON COLUMNS," + nl +
        "   {Store.[Store Name].Members} ON ROWS" + nl +
        "FROM Sales",

        // #12
        "WITH" + nl +
        "   MEMBER [Measures].[ProfitPct] AS " + nl +
        "   '([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Sales]'," + nl +
        "   SOLVE_ORDER = 1, FORMAT_STRING = 'Percent'" + nl +
        "   MEMBER [Measures].[ProfitValue] AS " + nl +
        "   '[Measures].[Store Sales] * [Measures].[ProfitPct]'," + nl +
        "   SOLVE_ORDER = 2, FORMAT_STRING = 'Currency'" + nl +
        "SELECT" + nl +
        "   { [Store].[Store Name].Members} ON COLUMNS," + nl +
        "   { [Measures].[Store Sales], [Measures].[Store Cost], [Measures].[ProfitValue]," + nl +
        "   [Measures].[ProfitPct] } ON ROWS" + nl +
        "FROM Sales",

/*
        // #13: cyclical calculated members
        "WITH" + nl +
        "   MEMBER [Product].[X] AS '[Product].[Y]'" + nl +
        "   MEMBER [Product].[Y] AS '[Product].[X]'" + nl +
        "SELECT" + nl +
        "   {[Product].[X]} ON COLUMNS," + nl +
        "   {Store.[Store Name].Members} ON ROWS" + nl +
        "FROM Sales",
*/

        // #14
        "WITH MEMBER MEASURES.ProfitPercent AS" + nl +
        "     '([Measures].[Store Sales]-[Measures].[Store Cost])/([Measures].[Store Cost])'," + nl +
        " FORMAT_STRING = '#.00%', SOLVE_ORDER = 1" + nl +
        " MEMBER [Time].[First Half 97] AS  '[Time].[1997].[Q1] + [Time].[1997].[Q2]'" + nl +
        " MEMBER [Time].[Second Half 97] AS '[Time].[1997].[Q3] + [Time].[1997].[Q4]'" + nl +
        " SELECT {[Time].[First Half 97]," + nl +
        "     [Time].[Second Half 97]," + nl +
        "     [Time].[1997].CHILDREN} ON COLUMNS," + nl +
        " {[Store].[Store Country].[USA].CHILDREN} ON ROWS" + nl +
        " FROM [Sales]" + nl +
        " WHERE ([Measures].[ProfitPercent])",

        // #15 (= mdx sample #7, but uses virtual cube)
        "with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])'" + nl +
        "select" + nl +
        "    {[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns," + nl +
        "    {Descendants([Time].[1997],[Time].[Month])} on rows" + nl +
        "from [Warehouse and Sales]",

        // #16 Virtual cube. Note that Unit Sales is independent of Warehouse.
        "select CrossJoin(\r\n"+
        "  {[Warehouse].DefaultMember, [Warehouse].[USA].children}," + nl +
        "  {[Measures].[Unit Sales], [Measures].[Units Shipped]}) on columns," + nl +
        " [Time].children on rows" + nl +
        "from [Warehouse and Sales]",

        // #17 crossjoins on rows and columns, and a slicer
        "select" + nl +
        "  CrossJoin(" + nl +
        "    {[Measures].[Unit Sales], [Measures].[Store Sales]}," + nl +
        "    {[Time].[1997].[Q2].children}) on columns, " + nl +
        "  CrossJoin(" + nl +
        "    CrossJoin(" + nl +
        "      [Gender].members," + nl +
        "      [Marital Status].members)," + nl +
        "   {[Store], [Store].children}) on rows" + nl +
        "from [Sales]" + nl +
        "where (" + nl +
        " [Product].[Food]," + nl +
        " [Education Level].[High School Degree]," + nl +
        " [Promotions].DefaultMember)",
    };
%>

<html>
<head>
<style>
.{
font-family:"verdana";
}

.resulttable {
background-color:#AAAAAA;
}

.slicer {
background-color:#DDDDDD;
font-size:10pt;
}

.columnheading {
background-color:#DDDDDD;
font-size:10pt;
}

.rowheading {
background-color:#DDDDDD;
font-size:10pt;
}

.cell {
font-family:"courier";
background-color:#FFFFFF;
font-size:10pt;
text-align:right;
}

</style>

<title>JSP Page</title>
</head>
<body>

<a href=".">back to index</a><p/>

    <form action="MD-ADHOC.jsp" method="post">
    <table>
        <tr>
            <td>
                <select name="whichquery">
        <%

        for (int i=0; i<queries.length; i++) {

            %>
            <option
            <%

            if (request.getParameter("whichquery") != null) {
                if (Integer.valueOf(request.getParameter("whichquery")).intValue() == i) {
                    out.print(" selected");
                }
            }
            %>

            value="<% out.print(i);%>">Sample Query #<%out.print(i);

            %>

            </option>

        <%
        }
        %>

                </select>
            </td>
        </tr>

        <tr>
            <td>
                <input type="submit" value="show query">
            </td>
        </tr>
    </table>
    </form>

    <form action="mdxquery">
        <table>
        <tr>
        <td>
        <tr>
            <td>
                <textarea id='queryArea' name="queryString" rows=10 cols=80><%
            if (request.getParameter("whichquery") != null) {
                out.println(queries[Integer.valueOf(request.getParameter("whichquery")).intValue()]);
            }
            if (request.getParameter("queryString") != null) {
                out.println(request.getParameter("queryString"));
            }
        %></textarea>
            </td>
        </tr>
        <tr>
            <td>
                <input type="submit" value="process MDX query">
            </td>
        </tr>

        <% if (request.getAttribute("result") != null) { %>
        <tr>
            <td valign=top>Results:
            <% out.println(request.getAttribute("result")); %>
            </td>
        </tr>
        <% } %>

    </table>
    </form>

<a href=".">back to index</a><p/>

</body>
</html>