Blog Post: ?????????? ????????? ?????????? SQL Server Reporting Services 2008R2. ????? 1.

? ???? ?????????? ?????? "?????? ? ????????? ???????????? ????????????? ? SQL Server Analysis Services", ????? 1 ? 2, ?? ??????????? ?????????? KPI ? Analysis Services. ? ?????? ????? ????? ????????, ??? ????????? ????? ?? ?????? ???? ???????????.

??????, ???? ????? ?????????? Microsoft, ??????? ????????? ? KPI ????????????? ? ???????? PerformancePoint Server 2007, ?? ?? PerformancePoint Services ? SharePoint 2010. ?????? ??????, ?? ?????? ?? ??, ?? ?????????????? ?????? ????????.  PerformancePoint ????????? ?????????????? ????? KPI, ???????? ?? ?? ????????? ??????????. ??????, ??? ????? ? ??????? ???????, ?? ??????????? ?????? ??????? ?????? ????????????? ?????????, ? ??????? ??????? ??????? ??????????? ?????????, ? KPI, ?? ???, ?????? ????????? ?? ?????? ???????????????? ???????. ? ???? ?????? SQL Server ??????????????. ??????, ??????, ????? ?????? ???? ?????????? ? ???????? ??????, ? ????? ?? ????????????????? ????????? ?????????? ?????-?????? Access, ???? ????????????? ????? ???????? ?????? ??????? ???????? KPI ?? ?????? ????? ?????? ??????, ??????? ? ????????? ? ???????????? Excele?. ????????? ???????? ??????, PerformancePoint ???????? ?????? ????????? ?????? KPI, ? ??? ?????????, ?? ?? ?????????? ?????????? ?? ?????. ???? ?? ????? ??????????? ??????????? ???? ???????????????? ???????? ? ?????????? ????????? ??????????? ? Visio, SQL Server ???????? ????????????? ?? ??????? KPI ? Analysis Services, ??????? ?? ??????????? ????, ? ?????????? ?? ??????????? ? ???? ??????? Reporting Services. ? ???????? ??????? ?????? ?????? ?? ?????? ????????????? ????? ??????, ? ??????????? ???????? ?? ???????? ?????? ????? ????????? ??????????? ????????, ? ?????? - ?????????? ?? ?????? ??????????? ???????: ????? ????? ??????, ????????? ? ????????? ???????? ?? ???????????? ?????? ??????? ? ?????? ???????? ??? KPI Product Gross Profit Margin (?????????????? ??????? ??????? ?? ??????? ????????). ????????????? ??????????? ?????????? MDX ???????? ???????? ????????? ???????

with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])

select

{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0

, nonempty(Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER), Measures.[Sales Amount]) on 1

from [Adventure Works]

where [Date].Calendar.[Calendar Year].[CY 2008]

?????? 1

???.1

 

??-?????, ????????????. ?? ????? ????????? ???????????? ?????????.

????????? Report Builder 3.0 (Start -> All Programs -> Microsoft SQL Server 2008 R2 Report Builder 3.0). ???? ?? ? ??? ??? ?? ?????, ????????? ??? ?????? ??????. ????????????? ?? ????????, ??????? ?? ????? ?????????? ??? ?????. ????????? ????? ???????????? ????? ??? ????????? ????????, ??????? ?????????? ??????? ????. ??????? ???????? ? ???????? ?????????, ?? ???? ??????????, ? ???????? ????? ??????? ???????????? ? ?????? ??????. ????? ?????? ?????????? ?????? ??? ????????? Report Data. ???? ?? ?????-???? ???????? ??? ?? ????????????, ? ?????? ???? View ???????? ????? ? ???????? Report Data. ? ?????? Report Data ???????? ?????? ??????? ?? ????? Data Sources ? ??????? Add Data Source:

 

???.2

 

???????? ??????? ?????????? ? ??????? Analysis Services, ?? ??????? ? ??????? ? ??????????? ?????? ??????????????? KPI. Shared connection ?????????????, ??? ??? ??? ??????? ?? ??????? Reporting Services. ???????? ??????????, ??????? ????? ???????? ? ?????? ?????:

 

???.3

???????? ???????? Build ? ?????? ???????? ???????????? ??????????:

 

???.3

 

?????? ????? ??????????, ????? ?????? ?????? ????? ???????????? ?? ????????? ? ?????. ?????????? ???????? ????? Dataset (??????) ? Report Data.

 

???.4

 

??????? ?? ???????? Query Designer. ????????? ???? ??????????? ????????, ? ??????? ????? ??????????? ????????? ? ???? ?? ??????? ? ????????, ???????? ????????? ?????????? ? ?.?., ??? ? ??????? ??????? Excel.

 

???.5

 

????????? ?????? ???????????? ???????? ?? ??????????. ???????? ?????? Design Mode, ????? ??????? ? ????? ??????? ????? ?????? ???????. ? ??? ??? ??????? ??????????? ??????? ?????? (?????? 1), ???????????, ??? ????????? ??????? ???????? ??? ????????????? ? Reporting Serices:

 

with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])

select

{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0

, nonempty(Product.[Product Categories].Product.Members, Measures.[Sales Amount]) on 1

from [Adventure Works]

where [Date].Calendar.[Calendar Year].[CY 2008]

?????? 2

 

???.6

 

?????? ??????? Descendants, ??????? ???????? ?????????, ???????????? ? ???? ????????, ? ?????? ??????? ??? ???????? ? ????????? ?????? Product. ?? ??????? ????? OLE DB, ??????????? ?????? ?????????? ? ????????? ?????????????. Reporting Serices - ????? ????? ????? ? ????? ???? ???????????? ?? ????????????? ? ??????????. ? ?????? Descendants ???????????? ?????? ????? ????????? ?????? ??????? ? ?????? ????????.

?????? ??????? ??????? ???????? [Date].Calendar.[Calendar Year].[CY 2008] ?? ????????. ??? ??????? ?????? ?????? ??????? ????? ????????? ??? ????????? ?????? ? ???????????? ?????????? ? ??????. ??????? ?? ?????? ????????? ??????? ? ??????? ???????? ?? ????? TimePeriod, ??????? ????? ??????? ?? ?????? ????????? Date, ???????? Calendar. ???????? ?? ????????? ???????? ???? [All Periods]:

 

???.7

 

??????? ?????????? ????? ??? ??. ? ?????? ???????? ???????? ?????? TimePeriod, ???????, ??? ????? ?????????? ? ??? ?????????, ??????? ?? ?????????? ???????? TimePeriod1, ?????? ???? ??????? ????????, ??? ???????, ????? ??????: ParameterValue, ?? ???????? ????? ????? ???????? ?????????, ? ParameterCaptionIndented, ??????? ????? ???????????? ??? ?????? ???????? ?????????.

 

 

???.8

 

????? ??????????, ??????, ???????.  ?????, ??? Report Builder ??? ?? ??????????. ?? ??????. ????????? ????? ? ???? ????? (?? ?? ?????? ??????????), ???????? ?????????????? rdl ????? ????????? ?????????? ? ??????? ? ??? ??????? TimePeriod1

 

???.9

 

?? ???????? ?? ?????? MDX-???????, ??????? ???????? ?????? ????????? Date, ? ????????????? ????? ??????????? ???? ParameterCaptionIndented, ? ??????? ????????? ?????? ????????? ?? ??????. ????? ?????? ????, ??????? ?????? ????? ?????. ?????? ??????? ???? ??????? ?? rdl, ???? ????? ??? ?? ????? ? ????????? ? ??????????? rdl:

 

???.10

 

???????? ????? ??????? DataSet2 ?????? ???? ?? ????????? DataSource1. ?????? ??? ???????? ????????? MDX:

 

with member [Measures].[ParameterCaption] as space([Measures].[ParameterLevel] * 2) + [Date].Calendar.CurrentMember.NAME

     member [Measures].[ParameterValue] as [Date].Calendar.CurrentMember.UNIQUENAME

     member [Measures].[ParameterLevel] as [Date].Calendar.CurrentMember.LEVEL.ORDINAL

select

{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} on 0,

except([Date].Calendar.members, [Date].Calendar.[Date].Members) on 1

from [Adventure Works]

?????? 3

 

???.11

?????? ??? ?? ?? ?????, ??? ?? ?????? ?? ???.9, ?????? ? ????? ??????????? ? ??????? ?????? ? ?????????? ?????? MDX. ???????? ?????????? ????? ?????? (CurrentMember.LEVEL.ORDINAL) ???????????? ??? ??????? ??????? ???????. ???????? CurrentMember.NAME ??? ???????? ??????????? ???????????? ??? ??????????? ????? ????????? ??? ?????? ??????? ? ?????????. ???????? CurrentMember.UNIQUENAME ???????? ????????? ????????? ? ????? ????????????? ? ?????? DataSet1 ? ??????? where. ? ?????? ??????? ??? ???????? ????????????, ??? ??????????? ?????????? ??????? ?????????????? ???????. ??? ?????? ??? ????? ???? ?? ???????????? ?????????? ??????????, ???? ??????? ?????? ????????? ???????? ???? Date/Time ? ??????? ?? ???? ???? ????????? Date ?? ?????????? ????????. ??????? ????? ???????? ? ??????????? ??????????? ?????? ??????? ? ???, ??? ??????? ???????? ?? ??????????? ??????????? ??? ?????? ????? ??? ?????? ??????, ??? ?? ????? ??? ????? ??? ????????, ????????, ? ??????? ??????, ????? ?? ???????? ????? ????? GET-??????. ?????????? ???????? ?????? ?? ????????????? ? ???????????? ??? ??????????? ???? ??????, ??? ? ?????? ??????. ??? ?????? ??????? ????? ?????? ???? ASP.NET ??? WinForms-??????????, ?????? ?? ???? ??????? ReportViewer, ? ????? ??? ?????? ??????? ??????? ????????????? ???????, ??????? ?? ????.

???????? ? ???????? ????????? ?????? TimePeriod, ??? ???????? ?? ???.8, ??????, ??? ???????? ????? ???????????? ?? ???????, ??? ???????? ??? ???? ????? DataSet2 (??. ?????? 3), ??? ???????? ????????? ????? ?????????? ?? ???? ParameterValue ??????? ????????, ? ??????????????? ???????, ??????? ????? ???????????? ??? ??????, ????? ??????? ?? ???? ParameterCaption:

 

 

???.12

 

???????? ?????? ???????? ?? ????????? ??? ??????? ?????????, ?.?. ? ??????? ????? ??????????? ?????, ???? ???????? ???????? ????????? ??? ??? ????????? ?? ????????????. ?? ???.7 ???????? ?? ????????? ?????????? ?????????? - [Date].Calendar.[Calendar Year].[CY 2008]. ??????? ????-????  ???????? ? ??????? ??? ?????? ?????????? ????????? ??????, ?.?. ?????????? ??????, ?? ???????  ???? ????????? ???? Measures.[Sales Amount]. ??????? ??? ???? ?? ?????? ? ????? "?????? ? ????????? ???????????? ????????????? ? SQL Server Analysis Services. ????? 1", ?????? 7:

 

Tail(nonempty([Date].[Calendar].[Month].Members, Measures.[Sales Amount]), 1).Item(0).Item(0).UNIQUENAME

?????? 4

? ?????? ??????? ?????? ??????? UNIQUENAME ?? ???????? ?? ???????? 3, ?.?. ???????? TimePeriod ????? ??? Text (???.12, ???????? General). ???? ?? ???.12 Default Values ????? ? ?????, ??? ???????? ?? ????????? ??? ????????? ?????? ????? ???? ????? ?????????? (Specify values), ???? ???? ??????? ?? ??????? (Get values from a query). ? ?????????, ??? ?????????? ?????????? ????????????? ?????????. ??? ?????? Specify values ????? ??????? ?????????? ???? ????????? ??? ??????? ? ??? ???-?????? ?? ????? ?????? ??????????, ?? ???????????? MDX-????????? (?????? 4) ??? ???????????? ? ?????? ???, ??? ???????? ???????? ?? ????????? ? ????????? TimePeriod ???. ???????? ???????? ? ????? ??? ???? ??????? DataSet3

 

with member Measures.aaa as Tail(nonempty([Date].[Calendar].[Month].Members, Measures.[Sales Amount]), 1).Item(0).Item(0).UNIQUENAME

select Measures.aaa on 0 from [Adventure Works]

?????? 5

 

???.13

 

?? ?????????? ?????? ?? ??????. ???????? ???? Adventure Works ????????, ??? ????????? ???????? (?? Sales Amount) ????? ????????????? ???? 2008 ?.

 

???.14

 

??????? DataSet3 ? ???????? Default Values ??? ????????? TimePeriod:

 

???.15

 

? ??? ???????? ???????? ???????? TimePeriod ? ??????? where ??????? DataSet1 (?????? 2, ???.6) ?????? ???????? [Date].Calendar.[Calendar Year].[CY 2008]:

 

with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])

select

{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0

, nonempty(Product.[Product Categories].Product.Members, Measures.[Sales Amount]) on 1

from [Adventure Works]

where StrToMember(@TimePeriod)

?????? 6

 

????????? MDX-??????? StrToMember, ?????? ??? Reporting Services ?? ????? ???? ????????? Member. ???????? ?????? TimePeriod ????? ??? Text; ????? ?????????? ?????? ? ????????? ????? ? ????, ???????????? ??? ???????.

 

???.16

 

???? ?? ?????? Run ????? ?????? ? ?????, ??? ??? ????????. ?? ????????? ?????????? ???? 2008 ?.

 

???.17

 

??-?????, ???????? ?????. ???? ??? ?? ?????? ?? ??????? ????? ??????????.

?????? ??????? ????????? ?????????, ??-???????? ???????? ????? ??? ? ?????????. ??????, ?? ????? ???????????? ?????????????? ?????????? ??????????? ????????????????? Microsoft ???? ? ????? ?????.

Pamela Anderson Amber Arbucci Christina Aguilera Kate Hudson Adriana Lima