Empower your BI using Powerpivot with the datasets of the Windows Azure Marketplace.

To empower you BI solutions, you want to merge data from different data providers. In most companies data is merged from databases within the company. As of today a lot more information is available which can be useful to empower your BI solution. I will explain this with an example.
Assume your company is making ice creams. The sales revenue of ice cream depends on the weather. The higher the tempature, the more ice creams will be consumed. So your revenue of ice creams should be compared with the tempature. By comparing your revenue with the tempature you can see if the increase in revenue is caused by a good job of your sales people or by extreem hot temparture of the last period. How do you know what the tempature has been in the last year? This kind of data will come available in the Windows Azure Market Place for premium data. It will enable information workers to integrate premium datasets available on DataMarket with their on-premise business data to perform analytics.

How to use premium Windows Azure DataMarket data?
In this example I will make use of a dataset which is for free: Datestream. Datestream is a date table feed designed for import into an Excel PowerPivot model. The table contains columns particularly suitable for time business intelligence applications.

1. Sign on to the Windows Azure market Place.
2. Select the Datestream dataset.
3. Select the hyperlink: Explorer this dataset.
   
   
4. Specify YearKey 2010. In my example I will analyze only the year 2010. Press on the Run Query button.
5. To generate a DataFeed. Select Export and press on the Export to Excel Powerpivot Download button.
   
   
6. Specify a name for your data feed and save it to a location on your PC.
7. Open Excel PowerPivot and import your on premise data you want to analyze. For instance your ice cream sales per day.
8. Press the From Azure DataMarket button to import the Datestream datafeed.
   
   
9. Specify the Azure DataMarket dataset URL. Browse to the datafeed file you made in step 6.
10. Specify your Account key. This can be found in the Windows Azure market place under the tab My account. Select account keys.
    
    
11. Refresh your datasets in the PowerPivot window. You will get 365 rows to import the date details for the year 2010.
    
    
12. Create a relationship between the date from the DateStream and your ice scream sales dates. This can be done in the design tab of the PowerPivot window.
13. Now you can start analyzing your data. For instance, which day of the week do you sell the most ice creams.

Enjoy it to use the Azure DataMarket to empower your BI experience.

Running totals per page in SSRS to display cumulative totals.

In Crystal Reports, you can easily make use of running totals on every page of your report. SQL Server Reporting Service does not have a standard functionality for this. Especially for companies who want to migrate from CRW to SSRS, running totals can be important to use. This blogpost will explain how you can implement running totals in your report.

1. Add a tablix to your report and add a dataset to it. In my example I have 3 columns:
   Debtor code, Account Name and Outstanding amount.
2. Add a 4th column to the tablix. Set the visibility of this column to Hide.
3. Select in the 4th column the detail row. Select Text Box Properties and change the name of the textbox to 'RunningTotal'.
4. Select Value and press the expression button. In this expression we will define the running total of the column. In my example, I have a columns Outstanding from the dataset 'CreditLimitDetails'
   
   
   
   =RunningValue(Fields!Outstanding.Value,SUM,"CreditLimitDetails")
   Now we have a running value of the outstanding amount in the 4th column.
5. To display the running value on every page, we will use the page footer.
6. Add a page footer to your report. In BIDS you can select, Report, Add Page footer.
7. In this example I will add a page total and a running total to the report.
8. Insert a 2 text boxes to the page footer.
9. Open the expression definition of the first text box. In this text box we will define the totaal outstanding amount of the current page.
   ="Page Total: " + cstr(Sum(ReportItems!Outstanding.Value))
10. Open the expression definition of the second text box. In this text box we will define the Running total of the outstanding column of the current page and all previous pages.
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))

Now your report will print running totals at the bottom of every page.

Localization of your SSRS reports. Translations of labels in your reports.

Localization is not a build in feature in SQL Server Reporting Service (SSRS). The user experience is very important for the success of your reports. In a lot of organizations you will see people who are using their applications in different languages. A user can install\configure his windows operating system and MS Office version in the langauage he or she wants. When using SSRS reports a user can't select the language of his reports. The reports will be generated in the language they are made. This is not a good user experience. This blog will explain what you can do to support multiple languages in your SSRS reports.

1) We need a table which will hold all translations.
CREATE TABLE [dbo].[Translations](
     [ID] [int] NOT NULL,
     [Language] [nvarchar] (10) NOT NULL,
     [Description] [nvarchar] (255) NULL,
     CONSTRAINT [PK_Translations] PRIMARY KEY CLUSTERED
     ([ID] ASC,[Language] ASC)
     ) ON [PRIMARY]

2) We need to add translations to this tabe which are needed in the reports.
INSERT INTO Translations (ID,Language,Description) VALUES (1000000,'en-US','Management')
INSERT INTO Translations (ID,Language,Description) VALUES (1000000,'nl-NL','Management')
INSERT INTO Translations (ID,Language,Description) VALUES (1000001,'en-US','Dashboard')
INSERT INTO Translations (ID,Language,Description) VALUES (1000001,'nl-NL','Dashboard')
INSERT INTO Translations (ID,Language,Description) VALUES (1000002,'en-US','Revenue')
INSERT INTO Translations (ID,Language,Description) VALUES (1000002,'nl-NL','Opbrengst')
INSERT INTO Translations (ID,Language,Description) VALUES (1000003,'en-US','Cost')
INSERT INTO Translations (ID,Language,Description) VALUES (1000003,'nl-NL','Kosten')
INSERT INTO Translations (ID,Language,Description) VALUES (1000004,'en-US','Result')
INSERT INTO Translations (ID,Language,Description) VALUES (1000004,'nl-NL','Resultaat')
INSERT INTO Translations (ID,Language,Description) VALUES (1000005,'en-US','Cash')
INSERT INTO Translations (ID,Language,Description) VALUES (1000005,'nl-NL','Liquiditeit')
INSERT INTO Translations (ID,Language,Description) VALUES (1000006,'en-US','Risk')
INSERT INTO Translations (ID,Language,Description) VALUES (1000006,'nl-NL','Risico')
INSERT INTO Translations (ID,Language,Description) VALUES (1000007,'en-US','Figures are expressed in ')
INSERT INTO Translations (ID,Language,Description) VALUES (1000007,'nl-NL','Cijfers worden weergegeven in ')
INSERT INTO Translations (ID,Language,Description) VALUES (1000008,'en-US','up to')
INSERT INTO Translations (ID,Language,Description) VALUES (1000008,'nl-NL','t/m')
3) Open a new SSRS report in Business Intelligence Development Studio. (BIDS)
4) Create a dataset which will return the set of labels for a given language. Add dataset named 'Labels' using next query:
SELECT ID, Language, Description
FROM Translations
WHERE Language = @Language

5) After adding the dataset 'Labels' a report parameter named 'Language' is added to the report. Configure the default value of the parameter 'Language' to the language ID of the client running the report.

The language ID of the client is configured by the user in Internet Explorer, Internet Options, Tab General, Button Language

6) For testing purposes it is easier to make the parameter 'Language' visible.

7) Create a second dataset with all available languages. This dataset will be used as available values for the parameter. You can use next query:
SELECT DISTINCT(Language) FROM Translations

8) Create a hidden, multi-valued parameter called 'Labels'.
Set the available values to the Labels dataset, value field: ID, label field: Description

Set the Default values to the Labels dataset, value field: ID This is important as you don't have access to tha available values from within the report. When the report is started by the user, the 'Labels' parameter will contain all translation for the selected language.

9) Add a function to the report. This function will find the translation for the selected language based on the specified ID in the report. To add a function to a report, right click on the yellow part of the report. Choose Report Properties, Code

Use next code:

Public Function GetLabel(P as Parameter, Label as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If (P.Value(i) = Label) Then Return P.Label(i)
Next i
Return Label
End Function

10) Now we are ready to configure the labels in your report to use this function.
As example for translation of the label: 'Revenue' use next expresion in your label:
=Code.GetLabel(Parameters!Labels,1000002)

11) Next step is to configure the report to use the date and number format to the selected language.
Open the report properties window and configure language with the parameter value of the selected language.
=Parameters!Language.Value

12) Now execute your report. The report will be generated in the configured language of your Internet Explorer.

13) Select another language if you want, in this case nl_NL\

You will see that all labels are translated and the thousand seperator is changed from , to .

Enjoy it to make your reports in the localization of your users to improve the user experience.