Ein typisches Verhalten in Reporting-Services ist es, dass Daten, die mit Parametern herausgefiltert werden, auch im Bericht nicht dargestellt werden. Beispiel: In diesem Chart werden die Umsätze für ein Jahr dargestellt: 

 Wenn nun nur noch die Monate Januar-April im Month-Parameter ausgewählt werden, erscheint folgendes Chart:

Die Daten werden per MDX aus einem Cube gelesen und im Dataset mit einem Subselect oder in der WHERE-Bedingung gefiltert:

SELECT

 {[Measures].[Sales Amount]} ON 0,

 NON EMPTY

 {([Date].[Calendar].[Date].ALLMEMBERS )} ON 1

 FROM

 (SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON 0

 FROM

 (SELECT ( STRTOSET(@DateMonthofYear, CONSTRAINED) ) ON 0

 FROM [Adventure Works]))

Der Kunde wünscht nun, dass die übrigen Monate aber dennoch (mit dem Wert 0) angezeigt werden sollen. Hierfür gibt es zum einen die Möglichkeit, dieses bereits im MDX-Query zu lösen. Dies geschieht über einen String-Vergleich in einem IIF-Block. Leider ist diese Methode recht unperformant.

Ein besseres Ergebnis kann man mit dieser Methode erzielen, vorausgesetzt man verwendet SQL Server ab Version 2008 R2:

Im Dataset werden zunächst alle Daten für das Jahr geholt:

SELECT

 {[Measures].[Sales Amount]} ON 0,

 NON EMPTY

 {([Date].[Calendar].[Date].ALLMEMBERS )} ON 1

 FROM

 (SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON 0

 FROM [Adventure Works])

Dann wird ein zweites Dataset „GueltigeMonate“ erstellt, in dem die Monate anhand des Parameters gefiltert werden:

WITH

MEMBER [Measures].[ParameterCaption] AS [Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS [Date].[Month of Year].CURRENTMEMBER.UNIQUENAME

MEMBER [Measures].[ParameterLevel] AS [Date].[Month of Year].CURRENTMEMBER.LEVEL.ORDINAL

SELECT

{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

[Date].[Month of Year].ALLMEMBERS ON ROWS

FROM

(SELECT STRTOSET(@Monate,CONSTRAINED) ON 0

FROM [Adventure Works])

Nun müssen beide Datasets miteinander verbunden werden. Hierzu werden die Eigenschaften der Serie geöffnet:

Im Feld für die Daten (Value) wird nun der Lookup gegen das Dataset „GueltigeMonate“  hergestellt. Hierfür wird die Funktion „Lookup“ benötigt. Die Syntax sieht so aus:

=IIF(LEFT(Fields!Month.Value,LEN(Fields!Month.Value)-5)=

LOOKUP(LEFT(Fields!Month.Value,LEN(Fields!Month.Value)-5), Fields!ParameterCaption.Value,Fields!ParameterCaption.Value,”GueltigeMonate”),

Sum(Fields!Sales_Amount.Value),0)

Diese Syntax bewirkt folgendes:

Wenn der Monatsname im Haupt-Dataset gleich einem Monatsnamen ist, der im gefilterten Monats-Dataset vorhanden ist, so wird der Wert angezeigt, ansonsten wird die Zahl 0 angezeigt.

Wenn nun im Bericht die gleiche Selektion wie am Anfang beschreiben durchgeführt wird, ergibt sich das gewünschte Bild: 

Die Anzeige-Geschwindigkeit konnte mit der Lookup-Methode gegenüber der Behandlung im Query halbiert werden. In einem der nächsten Blog-einträge werde ich die Lookup-Funktion dann nochmal näher beleuchten.

Die wohl auffälligste Änderung in SharePoint 2010 zu den vorherigen Versionen, ist die Integration der bereits aus den Office Produkten bekannten Ribbon Leiste. Wie so vieles im SharePoint kann auch diese von Entwicklern erweitert und angepasst werden.

Im Folgenden wird ein Beispiel gezeigt, um eine eigene Schaltfläche in die Ribbon Leiste zu integrieren.

Zur Anpassung der Ribbon Leiste wird ein neues Feature mit Visual Studio 2010 erstellt. Dies vereinfacht das spätere Deployment durch die Erstellung einer wsp Datei.

(continue reading…)

Ich bin seit letzter Woche glücklicher Gewinner eines HTC Mozart mir Windows Phone 7. Das Gerät wurde auf der Microsoft Partnerkonferenz an die ersten 800 Teilnehmer verteilt. Aufgespielt war von Anfang an das neue Update auf Windows Phone 7.5 Mango.

Bislang war ich Nutzer eines iPhone 4 und durh das Gerät auch ziemlich verwöhnt. Gerade im privaten Bereich unterstützt mich das iPhone bei vielen Dingen des Alltags.

• Meine Musik verwalte ich über iTunes. Es ist mir möglich die Musik über verschiedene Geräte im Haus abzuspielen (AirPort und Apple TV).
• Die zahlreichen Apps lassen einen immer wieder auf das Gerät schauen, ob Nachrichten, Sportergebnisse oder die neusten Facebook Nachrichten.

Dem neuen HTC Gerät mit Windows Phone 7 wollte ich eine Chance geben, also setzte ich die SIM Karte ein und legte los. Der erste Eindruck war durchaus positiv. Die Bedienung per Touch ist mindestens genauso folgsam wie beim iPhone. Sicher braucht man zunächst eine kurze Zeit, um sich an die geänderte Navigation zu gewöhnen. Die Startseite macht einen sehr guten Eindruck. Die neuen Kacheln des Metros Designs gefallen mir sehr gut. Inhalte werden direkt auf den Kacheln dargestellt, Dokumente oder Internet Seiten können auch direkt als Kachel auf der Startseite angebracht werden.

Interessant war für mich vor allem die Integration der Microsoft Programme. Hier sehe ich für mich die eigentliche Stärke gegenüber dem iPhone. Hier die Vorteile im Überblick:

• Mails lassen sich mit der Kennzeichnung flaggen. Durch diese Kennzeichnung lassen sich Mails im Outlook dann später leichter wiederfinden und nachbearbeiten.
• Es lassen sich Aufgaben darstellen und bearbeiten. Das ist im iPhone leider gar nicht möglich.
• Bei Aufgaben lässt sich ein Erinnerungsdatum hinterlegen. Dann wird die Aufgabe auch im Kalender angezeigt.
• Dokumente (Word, Excel und OneNote) lassen sich direkt im Gerät bearbeiten. Ideal ist das vor allem in Verbindung mit SharePoint oder dem eigenen Live Account. Die Dokumente werden übersichtlich dargestellt und können auch direkt auf einer Kachel dargestellt werden. Bei der Dokumentation der Beiträge der Partnerkonferenz wurden so meine Notizen direkt in unserem SharePoint gespeichert. Zurück am Arbeitsplatz waren so die Inhalte direkt verfügbar.
• Aufgaben lassen sich auch als “Privat” kennzeichnen. Eigentlich selbstverständlich, aber im iPhone leider nicht möglich.
• Abgesagte Termine lassen sich kommentieren. Auch das ist beim iPhone nicht möglich.
• Bei Teilnehmern eines Termins lassen sich auch optionale Teilnehmer hinzunehmen.
• Das zusammenlegen der Kontaktinformationen aus allen verfügbaren Konten (Outlook, Windows Live, Facebook) ist ein tolles Features. Insgesamt ist die Integration der Social Media Daten sehr gelungen.
• Die Kamerataste an der Seite erlaubt das Fotografieren auch bei gesperrtem Gerät.
• Auch die Sync Software Zune macht einen prima Eindruck.
• Apps im Marketplace sind noch nicht ganz in der Fülle verfügbar, können aber die wichtigsten Themen auch sehr gut abdecken.
• Bing Suche hat mich positiv überrascht. Vor allem die Musiksuche und die Möglichkeit Texte einzuscannen und diese unmittelbar zu übersetzen. Das kann sicherlich im nächsten Urlaub von großem Nutzen sein.

Insgesamt macht das Telefon und das Betriebssystem einen sehr guten und gelungenen Eindruck. Ich habe Spaß an dem Gerät und werde es in Verbindung mit meinem iPhone (eher privater Nutzen) weiter nutzen. Bei dem Gerät steht eher der geschäftliche Einsatz im Vordergrund.

SQL Server Danali | PowerPivot

This post combines two ideas

1. The calculation of a moving average (last 30 days) as shown in a previous post
2. Custom aggregations

What I want to show here today, is how we can influence the way our calculation works on higher levels. For this purpose I’m showing three different approaches:

1. The monthly (quarterly, yearly) total is defined as the last day’s moving average of the given date range.
   This corresponds to the original approach from my post about moving averages
2. The monthly (quarterly, yearly) total is defined as the average of all the moving averages on day level.
3. The monthly (quarterly, yearly) total is defined as the average of the sales amount of all days of that period (the moving average is only to be calculated on date level)

Of course, there are many more possibilities but I think the methods shown here, can be used for many other aggregation requirements.

Here are the three aggregations side by side.

Although the values on higher levels of aggregation differ a lot, the daily values are identical (we just wanted to change the rollup method, not the calculation on a detail level).

Let’s start with the first one:

1. The monthly (quarterly, yearly) total is defined as the last day’s moving average of the given date range

This is shown here for our measure “Sales Amount(30 avg)”:

As shown in my previous post, the formula may look like this:

Sales Amount (30d avg):=
AverageX(
  Summarize(
    datesinperiod(
       ‘Date’[Date]
       , LastDate(‘Date’[Date])
       , -30
       , DAY
    )
    ,’Date’[Date]
    , "SalesAmountSum"
    , calculate(Sum(‘Internet Sales’[Sales Amount]), ALLEXCEPT(‘Date’,'Date’[Date]))
  )
  ,[SalesAmountSum]
)

Since we use the expression LastDate(‘Date’[Date]) the last date of each period is used for the moving average – exactly what we wanted.

Average at month level as the average of the moving averages (day level)

In this approach, the monthly aggregate has to be calculated as the average of all the daily moving averages of that month. The picture shows what this means:

This might look pretty difficult. However, for our calculation we simply have to wrap the existing calculation in another Summarize – function.

This is the formula I used:

Sales Amount (30d avg 2):=
AverageX(
  summarize(
    ‘Date’
    , ‘Date’[Date]
    , "DayAvg"
    AverageX(
      Summarize(
        datesinperiod(
          ‘Date’[Date]
          ,LastDate(‘Date’[Date])
          , -30
          , DAY
        )
        ,’Date’[Date]
        , "SalesAmountSum"
        , calculate(Sum(‘Internet Sales’[Sales Amount]), ALLEXCEPT(‘Date’,'Date’[Date]))
      )
      ,[SalesAmountSum]
    )
  )
  ,[DayAvg]
)

The blue part of the formula is exactly the same is in the first calculation. We’re just wrapping this in an additional Average function. Why does this still work on a day-level? Simply because the outer average computes the average of a single value in this case.

So, with just a minor change to the formula, we changed the method of aggregation quite a lot.

3. The monthly (quarterly, yearly) total is defined as the average of the sales amount of all days of that period

This sounds quite simple but in this case we have to distinguish two calculation paths:

• day level
• month level and above

The following picture shows the calculation for the monthly aggregate:

In order to split our computation paths we need to find out if we are on day level or not. Usually the IsFiltered(…) DAX function can be used for this purpose. However, since we have some columns with date granularity (date, day of month, day of year) in our date dimension, we would have to write something like IsFiltererd(‘Date’[Date]) || IsFiltered(‘Date’[Day of Month]) || …

To simplify this, I simple used a count of days in the following code. If we count only one day, we’re on day level. Of course, the count is the more expensive operation, but for this example, I leave it that way (the date table is not really big).

Sales Amount (30d avg 3):=
if (
  Count(‘Date’[Date])=1,
  AverageX(
    Summarize(
      datesinperiod(
        ‘Date’[Date]
        ,LastDate(‘Date’[Date])
        , –30
        , DAY
      )
      , ‘Date’[Date]
      , "SalesAmountSum"
      , calculate(Sum(‘Internet Sales’[Sales Amount]), ALLEXCEPT(‘Date’,'Date’[Date]))
    )
    ,[SalesAmountSum]
  )
  , 
  AverageX(
    Summarize(
      ‘Date’
      ,’Date’[Date]
      ,"SalesAmountAvg"
      , calculate(Sum(‘Internet Sales’[Sales Amount]), ALLEXCEPT(‘Date’,'Date’[Date]))
    )
    ,[SalesAmountAvg]
  )
)

Again the blue part of the formula is exactly the same as in our first approach. This part is taken whenever we’re on a day level. On higher levels, the aggregate is computed as a simple (not moving) average of all daily values.

So, using the concepts of my previous post we were able to change the aggregation method to meet very sophisticated requirements.

Microsoft hat einen Product Guide zum aktuellen CTP3 Release des im kommenden Jahres erscheinenden SQL Server (Denali) veröffentlicht.

Das 456 MB große Paket kann im Microsoft Download Center heruntergeladen werden. 
In diesem Paket sind unterschiedlichste Materialien enthalten, die dem interessierten Anwender die wesentlichen Neuerungen näher bringen.

Die folgenden Materialien sind in dem Paket enthalten:

• 14 Datenblätter zum CTP3 Release
• 8 PowerPoint Präsentationen
• 5 Whitepaper
• 44 Links zu Online-Videos inklusive 26 Präsentationen von der US-TechEd 2011
• 6 durchklickbare Demos
• 26 Links zu Online-Dokumentationen
• 13 Hands-On Lab Preview Dokumente
• 13 selbstlaufende Demos

Nach dem das Paket heruntergeladen und entpackt wurde befinden sich im Zielverzeichnis mehrere Dateien und Unterordner. Um den Product Guide zu starten, muss die Datei STARTHERE.CMD ausgeführt werden. Es öffnet sich dann der definierte Standard-Browser mit der Startseite des Product Guides. Über die Startseite lassen sich dann alle angebotenen Ressourcen auswählen.

Download des Product Guides:
http://www.microsoft.com/download/en/details.aspx?id=27069

Download SQL Server Code Name “Denali” CTP3:
http://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

GUIDs (Globally Unique Identifier) in Hyperlinks zu verwenden ist im Rahmen von Reporting Services nicht ohne weiteres möglich.

Es wird versucht folgenden URL Aufbau https://crmServer/OrganizationName/activities/task/edit.aspx?id={3c046c31-f921-df11-8947-000c29d3e997}, mit Hilfe von einem Parameter und einem GUID Feld herzustellen.

Die einfachste Art der Verknüpfung von einem Parameter mit URL Informationen und einem GUID Feld

Parameters!URLAuftrag.value & Fields!AuftragID.value

führt zu einem Fehler. Die GUID mit Hilfe von Cstr() in einen String zu konvertieren führt auch nicht zum gewünschten Ergebnis.

Parameters!URLAuftrag.value & Cstr(Fields!AuftragID.value)

Die Ctype () Funktion ist hier die gesuchte Funktion. Mit dieser Funktion ist es möglich das GUID Feld in einen String zu konvertieren und mit dem Parameter, welches die URL Informationen beinhaltet, zu verknüpfen.

Parameters!URLAuftrag.value & Ctype(Fields!AuftragID.value, guid).tostring

Der folgende Javascript Aufruf führt dazu, dass der Hyperlink in einem gesonderten Fenster geöffnet wird.

=”javascript:void(window.open(‘” & (Konvertierungsfunktion) & “}’,'_blank’))”

The CeBIT in Hannover, Germany is a great time to learn about new technology, share information and discuss about new topics. As for the last years we (ORAYLIS GmbH) are also present and I would be glad to welcome you at our booth that is located within the Microsoft area: Fair Hall 4, Booth 26 / P30. I will be at the booth from March 2 (Wednesday) to March 5 (Saturday).

Hope to see you at the CeBIT!

Veröffentlicht am 27. Januar 2011

Autoren: Rita L. Sallam, James Richardson, John Hagerty, Bill Hostmann

Der Report kann unter dieser Adresse verfolgt werden:

http://www.gartner.com/technology/media-products/reprints/microsoft/vol2/article15/article15.html

Einige Facts zum Thema:

• Microsoft is the unit leader in Data Warehousing. According to IDC, SQL Server has more units than Oracle and IBM combined.
• Microsoft is a Leader in Gartner’s Magic Quadrant for Business Intelligence (1/2011).  Encourage your customers to assess our offerings across both Business Intelligence and Data Warehousing.
• SQL Server 2008 R2 offers the most comprehensive Data Warehouse solution with integrated relational database engine, industry leading OLAP, Reporting, ETL and MDM tools, and offerings which scale to the needs of all customers today through SQL Server 2008 R2 Fast Track and Parallel Data Warehouse and the HP Business Decision Appliance.
• Parallel Data Warehouse was released and announced in November 2010 and offers customers high scalability and performance at low cost for high end Data Warehouses.  Parallel Data Warehouse is gaining momentum with a strong sales pipeline.  However, it was not considered as a shipping product in the 2011 Data Warehousing Magic Quadrant.  According to Gartner1, “Microsoft released its own MPP data warehouse appliance, the SQL Server 2008 R2 PDW, in November 2010, but the date of its availability did not allow us to consider it when deciding Microsoft’s position in the present Magic Quadrant.”
• As the report confirms, Microsoft offers customers unmatched value and low TCO in Data Warehousing and Business Intelligence.  We achieve this through a range of solutions from industry leading software, Reference Architectures and Appliances.  Fast Track Data Warehouse 2.0 provides great price performance for mid-sized Data Warehouses and data marts, while Parallel Data Warehouse extends this to the high end.  
• In collaboration with HP, Microsoft is innovating on appliances and reference architectures.  As part of the Infrastructure to Applications agreement, Microsoft and HP announced new appliances (HP Enterprise Data Warehouse Appliance and HP Business Decision Appliance) as well a roadmap for future workload specific appliances. We are delivering unmatched flexibility and choice, rapid time to solution and the industry’s leading TCO.