vb.net barcode reader sdk Result in Microsoft Office

Encoding Code 128C in Microsoft Office Result

The query uses KPIValue, KPIGoal, and KPIStatus There is also KPITrend, which is not used in this query The results show the KPI by Category for FY 2003 The KPIStatus

indicates how close the KPIValue is to the KPIGoal Note the column header for KPIValue reflects the original measure used in the KPI design in BIDS and looks slightly different from the KPIGoal and KPIStatus headers Also, the KPIValue and the KPIGoal are not in the same format

Here the KPIs are redone as measures This fixes a number of problems in the previous query The KPITrend has been added and two years are shown to illustrate how KPITrend works

-- formatting existing KPIs with member [Measures][Actual] as KPIValue("Product Gross Profit Margin"),format_string = "Percent" member [Measures][Target] as KPIGoal("Product Gross Profit Margin"),format_string="Percent" member [Measures][Status] as KPIStatus("Product Gross Profit Margin") member [Measures][Trend] as KPITrend("Product Gross Profit Margin") select crossjoin({[Date][Fiscal][Fiscal Year][FY 2003], [Date][Fiscal][Fiscal Year][FY 2004]},{[Measures][Actual], [Measures][Target],[Measures][Status],[Measures][Trend]}) on columns, [Product][Product Categories][Category] on rows from [Adventure Works]

The KPITrend (renamed as Trend) for this particular KPI shows the change in the KPIValue from year to year The column headers for the KPI are now more readable and the KPIGoal (renamed as Target) is formatted the same as KPIValue (renamed as Actual) to make the KPIStatus (renamed as Status) figure easier to understand

Sometimes you may want to temporarily override the original KPI definition created back in BIDS In this query, both the KPIGoal and the KPIStatus for the Accessories category have been overridden

-- changing existing KPIGoal (target) and KPIStatus with member [Measures][Actual] as KPIValue("Product Gross Profit Margin"),format_string = "Percent" member [Measures][Target] as iif([Product][Product Categories]currentmember is [Product][Product Categories][Accessories],60, KPIGoal("Product Gross Profit Margin")),format_string="Percent" member [Measures][Status] as iif([Product][Product Categories]currentmember is [Product][Product Categories][Accessories], Case When [Measures][Actual]/ [Measures][Target] >= 95 Then 1 When [Measures][Actual]/ [Measures][Target] < 95 And [Measures][Actual]/ [Measures][Target] >= 70 Then 0 Else -1 End ,KPIStatus("Product Gross Profit Margin")) select crossjoin({[Date][Fiscal][Fiscal Year][FY 2003], [Date][Fiscal][Fiscal Year][FY 2004]}, {[Measures][Actual],[Measures][Target],[Measures][Status]}) on columns, [Product][Product Categories][Category] on rows from [Adventure Works]

The KPIGoal (target) has been set to 60% instead of the original 40% a little more demanding! The KPIStatus (status) is also a more challenging figure It has been changed, for the lifetime of the query only, to qualify as a status of 1 only when the KPIValue is more than 95% of the KPIGoal the original threshold was 90% Thus, the KPIValue (actual) for Accessories for FY 2004 results in a status of 0, even though profits were pretty good at 5219%

SSAS 2008 introduces the new Create KPI syntax Unfortunately, if you are using SSAS 2005, this is not available The workaround in SSAS 2005 is to create your own measures If you do have SSAS 2008, run the two Creates together (the Go statement allows you to do this without a syntax error) before you try the Select

-- creating a new KPI create member [Adventure Works][Measures][Profit Margin Value] as [Measures][Gross Profit Margin] go create KPI [Adventure Works][Profit Margin] as [Measures][Profit Margin Value], goal= case when [Product][Category]CurrentMember Is [Product][Category] [Accessories] then vba!format(40,"Percent") when [Product][Category]CurrentMember Is [Product][Category][Bikes] then vba!format(12,"Percent") when [Product][Category]CurrentMember Is [Product][Category][Clothing] then vba!format(20,"Percent") when [Product][Category]CurrentMember Is [Product][Category]

[Components] then vba!format(10,"Percent") else vba!format(12,"Percent") end, status= case when vba!cDBL(left(KPIValue("Profit Margin"),len(KPIValue("Profit Margin"))-1)) * 100/vba!cDBL(left(KPIGoal("Profit Margin"), len(KPIGoal("Profit Margin"))-1)) >= 90 then 1 when vba!cDBL(left(KPIValue("Profit Margin"), len(KPIValue("Profit Margin"))-1)) * 100/vba!cDBL(left(KPIGoal("Profit Margin"), len(KPIGoal("Profit Margin"))-1)) < 90 and vba!cDBL(left(KPIValue("Profit Margin"), len(KPIValue("Profit Margin"))-1)) * 100/ vba!cDBL(left(KPIGoal("Profit Margin"), len(KPIGoal("Profit Margin"))-1)) >= 80 then 0 else -1 end -- selecting the new KPI select crossjoin({[Date][Fiscal][Fiscal Year][FY 2003], [Date][Fiscal][Fiscal Year][FY 2004]}, {KPIValue("Profit Margin"),KPIGoal("Profit Margin"), KPIStatus("Profit Margin")}) on columns, [Product][Product Categories][Category] on rows from [Adventure Works]

The Select is reasonably straightforward The Create Member is there so the KPI column headers have consistent names The Create KPI is rather more complex Note the Goal= and Status= entries; there is no corresponding Value= as you might expect VBA Format is used to show KPIValue and KPIGoal as percentages In addition, there is heavy use of other VBA functions to enable comparison of KPIValue and KPIGoal in the Status= section for defining KPIStatus It is necessary to remove the % symbol introduced in the previous formatting The KPIStatus for Bikes for FY 2004 is 1 because the KPIValue is more than 90% of the KPIGoal You can also include a Trend= section, if you wish, in the Create KPI syntax There is often more than one way to accomplish the same result in MDX The following syntax shows an alternative you may find easier to read (it eliminates the VBA functions):

create member [Adventure Works][Measures][Profit Margin Value] as [Measures][Gross Profit Margin] go create member [Adventure Works][Measures][Profit Margin Goal] as case when [Product][Category]CurrentMember Is [Product][Category] [Accessories] then 040 when [Product][Category]CurrentMember Is [Product][Category][Bikes] then 012 when [Product][Category]CurrentMember Is [Product][Category][Clothing] then 020 when [Product][Category]CurrentMember Is [Product][Category] [Components] then 010 else 012 end, FORMAT_STRING = "Percent" go create member [Adventure Works][Measures][Profit Margin Status] as case when KPIValue("Profit Margin")/KPIGoal("Profit Margin") >= 90 then 1 when KPIValue("Profit Margin")/KPIGoal("Profit Margin") < 90 and KPIValue("Profit Margin")/KPIGoal("Profit Margin") >= 80