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

2012-04-11

5237
0
SQL_SERVER

前面我們完成了 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

新增連結

OLE DB Provider請選擇 For AS。

儲存連線字串

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

輸入以下 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]

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

設定ListBox Text 和 Value

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);
        }