[SQL SERVER][BI] 多維度報表(3)

[SQL SERVER][BI] 多維度報表(3)

前面我們完成了 Cube 和 報表設計,

現在就要使用Asp.net 簡單開發一個查詢頁面,

可以讓業務人員操作並匯出報表。

 

.aspx

頁面拉2個ListBox(SelectionMode="Multiple")、1個Button和ReportViewer,

並設定2個ListBox DataSource。

<form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="ProductsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyOLAP %>"
            ProviderName="<%$ ConnectionStrings:MyOLAP.ProviderName %>" SelectCommand="WITH&#13;&#10;MEMBER [Measures].[ParameterCaption]&#13;&#10;AS '[Dim Product].[English Product Name].CURRENTMEMBER.MEMBER_CAPTION'&#13;&#10;MEMBER [Measures].[ParameterValue]&#13;&#10;AS '[Dim Product].[English Product Name].CURRENTMEMBER.UNIQUENAME'&#13;&#10;SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}&#13;&#10;ON COLUMNS , [Dim Product].[English Product Name].ALLMEMBERS&#13;&#10;ON ROWS&#13;&#10;FROM [Adventure Works DW Cube]">
        </asp:SqlDataSource>
          <asp:SqlDataSource ID="PromotionDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyOLAP %>"
            ProviderName="<%$ ConnectionStrings:MyOLAP.ProviderName %>" SelectCommand="WITH&#13;&#10;MEMBER [Measures].[ParameterCaption]&#13;&#10;AS '[Dim Promotion].[English Promotion Name].CURRENTMEMBER.MEMBER_CAPTION'&#13;&#10;MEMBER [Measures].[ParameterValue]&#13;&#10;AS '[Dim Promotion].[English Promotion Name].CURRENTMEMBER.UNIQUENAME'&#13;&#10;SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}&#13;&#10;ON COLUMNS , [Dim Promotion].[English Promotion Name].ALLMEMBERS&#13;&#10;ON ROWS&#13;&#10;FROM [Adventure Works DW Cube]">
        </asp:SqlDataSource>      
        EnglishProductName<asp:ListBox ID="ProductsList" runat="server" DataSourceID="ProductsDataSource"
            DataTextField="[Measures].[ParameterCaption]" DataValueField="[Measures].[ParameterValue]"
            SelectionMode="Multiple" Width="239px"></asp:ListBox><asp:SqlDataSource ID="ProductDS"
                runat="server" ConnectionString="<%$ ConnectionStrings:MyCubeConnectionString %>"
                ProviderName="<%$ ConnectionStrings:MyCubeConnectionString.ProviderName %>" SelectCommand="WITH&#13;&#10;MEMBER [Measures].[ParameterCaption]&#13;&#10;AS '[Dim Product].[English Product Name].CURRENTMEMBER.MEMBER_CAPTION'&#13;&#10;MEMBER [Measures].[ParameterValue]&#13;&#10;AS '[Dim Product].[English Product Name].CURRENTMEMBER.UNIQUENAME'&#13;&#10;SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}&#13;&#10;ON COLUMNS , [Dim Product].[English Product Name].ALLMEMBERS&#13;&#10;ON ROWS&#13;&#10;FROM [Adventure Works DW Cube]">
            </asp:SqlDataSource>
        EnglishPromotionName<asp:ListBox ID="PromotionList" runat="server" SelectionMode="Multiple" Width="239px" DataSourceID="PromotionDataSource" DataTextField="[Measures].[ParameterCaption]" DataValueField="[Measures].[ParameterValue]"></asp:ListBox>
            <asp:Button ID="Button1" runat="server" Text="查詢報表" OnClick="Button1_Click" />
      
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt"
            Height="400px" ProcessingMode="Remote" Width="758px">
            <ServerReport ReportServerUrl="http://servername:port/ReportServer_SQL2008" />
        </rsweb:ReportViewer>  
    </div>      
    </form>

 

 

設定ListBox DataSource

image

 

新增連結

image

OLE DB Provider請選擇 For AS。

 

儲存連線字串

image

 

選擇自訂SQL陳述式或預存程序

image

 

輸入以下 MDX 語法

WITH
MEMBER [Measures].[ParameterCaption]
AS '[Dim Product].[English Product Name].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[Dim Product].[English Product Name].CURRENTMEMBER.UNIQUENAME'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}
ON COLUMNS , [Dim Product].[English Product Name].ALLMEMBERS
ON ROWS
FROM [Adventure Works DW Cube]

 

image

 

語法沒錯誤就能看到 Cube 中相關資料

image

 

設定ListBox Text 和 Value

image

 

note:另一個 ListBox請重複操作,只需更改 MDX語法部分。

WITH
MEMBER [Measures].[ParameterCaption]
AS '[Dim Promotion].[English Promotion Name].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[Dim Promotion].[English Promotion Name].CURRENTMEMBER.UNIQUENAME'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}
ON COLUMNS , [Dim Promotion].[English Promotion Name].ALLMEMBERS
ON ROWS
FROM [Adventure Works DW Cube]

 

 

 

.aspx.cs

 protected void Button1_Click(object sender, EventArgs e)
        {
            ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
            ReportViewer1.ShowParameterPrompts = false;
            ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://yourservername:yourport/ReportServer_SQL2008");//請依環境更改
            ReportViewer1.ServerReport.ReportPath = "/OLAPSSRS2k8/MyReport";//勿加副檔名.rdl
            List<ReportParameter> parametersList = new List<ReportParameter>();
            parametersList.Add(GetParaValue(PromotionList, "DimPromotionEnglishPromotionName"));//參數1
            parametersList.Add(GetParaValue(ProductsList, "DimProductEnglishProductName"));//參數2 
            ReportViewer1.ServerReport.SetParameters(parametersList);//加入相關報表參數
            ReportViewer1.ServerReport.Refresh();
        }

        ReportParameter GetParaValue(ListBox checkListBox, string parameterName)
        {
            List<string> parameterValues = new List<string>();

            foreach (ListItem li in checkListBox.Items)
            {
                if (li.Selected)
                {
                    if (li.Text == "All")
                    {
                        parameterValues.Add(li.Value);
                        break;
                    }
                    else
                        parameterValues.Add(li.Value);
                }
            }

            return new ReportParameter(parameterName, parameterValues.ToArray(), true);
        }

 

 

執行結果

image

 

image

 

或許你可能會和我ㄧ樣覺得建立多維度報表怎麼會那麼麻煩,

但請千萬不要輕言放棄BI,因為 SQL2012 PowerView 將讓你驚嘆,

原來建立高可互動、高視覺和高效能報表可以這麼簡單,

所以....升級 SQL2012 吧...XD,讓我們把寶貴時間花在有價值的地方吧。

 

下面SQL2012 PowerView影片來源取自Youtube