Freeze tablix header while scrolling and repeat on each page

There could be a frustrating situation when someone would try to freeze the tablix headers and enable the option to repeat header on each page after selecting related Tablix properties in SSRS 2008 versions.

The options are there but unfortunately these options won’t work. This seems to be a bug that will be fixed very soon hopefully. But meanwhile if someone finds himself in the same situation and enabling of related checkboxes to freeze rows or column headers and to repeat headers on each page as shown in followig diagram is not working then configure these options as shonw in second diagram.

To enable these options perform following steps:

1. Click on the small arrow on right top of the grouping panel and selec Advanced Mode

2. The static groups will be visible. Select the top most static group

3. Note that related header is now selected in the tablix

4. In properties panel set the FixedData, KeepTogether and RepeatOnNewPage properties to True and KeepWithGroup propert to After. This will freeze the header and repeat it on every new page.

Logon failed for the unattended execution account

Following are steps that need to be performed if you start getting login failed for unattended execution account error.

“An error occurred during client rendering.
The report server has encountered a configuration error. Logon failed for the unattended execution account
Logon failed (rsLogonFailed) Logon failure: unknown user name or bad password.”

 If this happens then it means there are unattended execution account was configured and there is some problem with the account mentioned in the configurations. Either it has changed in Active Directory setup and the password has been expired or changed. This is optional setting but if you have configured it then it must be updated when there is change in the account information or password. Otherwise you may start getting the aforementioned error message.

 To configure the account:

 Launch the reporting services configuration manager and connect to the report server.

1. Go to Execution Account and select the Specify an execution account checkbox.

2. Enter the account information and click on Apply button. This must be the domain account.

 Go to the report manager and access the reports and it should now run properly.

PowerPivot Virtual Lab

Need some hands on PowerPivot? Following lab is for you.

https://cmg.vlabcenter.com/default.aspx?moduleid=dbe037bc-9004-4a38-883f-696d507a1b66

Karachi International Book Fair 2010

Karachi International Book Fair was organized at Expo Centre Karachi from 24th to 28th December 2010. Although I am not among book lovers but I visited the exhibition thrice. One of my friend commented that you’re not interested in books (Kitaab) but in Kitaabi Chehray). This is not true.. Infact under tense city situation when people get a chance to refresh their minds, they prefer to visit such venues and this is the reason that people belonging to all age groups attended this exhibition.

Anyway, the exhibition had wide range of books. Islamic collection, books from Urdu and English literatures, science and technologies books, etc.

Another good aspect of the exhibition was that publishers from other countries were also there including Iran, India and Singapore.

Hope to observe it next year as well. Thanks organizers for such a wonderful event.

SSIS Packages and Triggers (SQL Server 2005)

Got this problem few days back. I created a trigger on TableA that populates TableB when a new record is inserted or existing record is updated in TableA and then there is series of actions after populating TableB that is not relevant for this post. TableA is populated through SSIS package. The package uses fast load option to do this job.

Everything worked fine, SSIS package executed successfully populating TableA but there was no data in TableB. Trigger was working fine because when I inserted data into TableA from query analyzer, it fired and populated data into TableB.

Finally after some research I came to know that fast load option doesn’t execute TABLOCK, CHECK_CONSTRAINTS and also doesn’t fire TRIGGERS. So to make these things enable in SSIS Package fast load component right click on data flow task and select Show Advanced Editor. It will show the dialog box, select the Component Properties tab and edit FastLoadOptions property to enable firing of triggers and other options as shown in below image.

MSDN Virtual Labs

If you want to experience and evaluate a Microsoft product and you have no resources available to work then you can try Hyper V Edition of MSDN Virtual labs. These labs are available on internet free of cost and the good thing is that you don’t need to install any extra setup on you local machine if your system meets few basic requirements.

Operating System: WinXP, Win2000, Win 2003 server, Win 2008 server, Vista of Windows 7

Internet Explorer: 6 or later

ActiveX Control: Remote Desktop Terminal Services ActiveX Control that you’ll most probably found installed on your system.

And if you are behind firewall then the port 443 should be open. Following is the screenshot from Business Intelligence Virtual Lab. Main area of the window will show you the remote desktop. On the left you can find connections list and on the right you can see the show/hide bar having lab manual and end session option.

Manual is available in three different format.

1. The instructions are available as Bar content with next previous option

2. Lab instructions are available in PDF to download

3. Lab instructions are also available as webpage

Any one of the format could be used. It is timed session and on top of the bar you’ll see the time remaining for the lab session.

It seems there are some problem with the lab as I was kicked out of the lab many times and I am sure I had no network problem at that time but overall it is good piece of work and you can get a good start on the techology you wish to work.

For complete lsit of available labs visit the following page:

http://msdn.microsoft.com/en-us/aa570323.aspx

A business that floats over oceans

Among all business domains the one I liked most is the Shipping industry. I have spent 3 initial years of my career in this domain, developing number of windows and web applications, for one of the top name in this industry and still developing certain things for it (Unfortunately all these are in immature stages that I cannot showcase them and hopefully will be able to exhibit sooner or later)

Anyway to keep the business in mind decided to write the sort of refresher write-up. This business has many other businesses/domains attached like import and export, freight forwarding, accounting and finance, customer services and also has links with government agencies like customs department.

The main entity is known to be as B/L or BOL (Bill of lading). This is the document that issued by a carrier (Shipping Line Company) to shippers, consignees and/or freight forwarders acknowledging the loading, unloading and delivery of goods aka commodities. This document also describes the types, weight and dimensions of commodities, details of containers. Obviously the commodities are placed inside containers of some types and sizes, for instance refrigerating containers of 20 ft or normal container of 40 ft, etc. B/L also logs the information of ports from where the container is loaded on board and the destination port where it is unloaded, the methods of delivery on both ports, i.e. the goods are brought to port and then loaded into container or pre loaded container is brought to port and vice versa on the delivery port. This is referred by standard global key terms CY and CFS. The possible combinations are CY/CY, CY/CFS, CFS/CFS and CFS/CY where first part shows the loading port and the second one show the destination or delivery port.

Some key stakeholders are Shipper, Forwarder, Consignee, Notify Party, Tally Contractors, Stevedores, Trucker, etc.

And some frequent terms include Vessel, Voyage, Mother Vessel, Loading Port, Origin Port, Destination Port, Delivery Port, Gate Pass, Delivery Order, House B/Ls, EID Fact File, Yards, Terminals, Freight, etc.

Software Development Trends

In Pakistan, either a carrier company operates solely as Maersk, APL, etc or there are agencies who has contract for certain career companies as UMA (United Marine Agencies). But the software development trends are more or less similar. These companies usually have online database system that is provided by their parent companies. I have seen online legacy DOS bases system usually that are provided to them. This system is the main source of information and operators download and upload data by using such system. But unfortunately this doesn’t fulfill the country specific needs at least this is true for Pakistan. For instance there are certain requirements by Customs, KPT (Karachi Port Trust), PRAL (Pakistan Revenue Automation Ltd.) etc that these online systems are not in compliance. So to cater such needs local software is developed. Such software gets the source data from online system in the form of text files (CSV, EDI Fact File, RAW form flat file, etc). From these files data is usually uploaded into local databases to manipulate rather than manual input.

Will try to keep posting my progress on pending items on which I am working with turtle speed. 

A short overview of Excel Services

Excel services are one of the technology item introduced in Microsoft Office SharePoint Server 2007 and has more refined and advanced capabilities in SharePoint 2010. Excel services can be used to view the excel workbook within the SharePoint 2010 in a webpage.

Users can share the workbooks prepared by them with other users via SharePoint 2010 without letting them to change the original contents. In some cases you can change the cell values but this is obvious that you cannot use excel services to create new workbooks. Users can make these workbooks available on SharePoint portals or can make them a part of dashboard items.

You can use these workbooks just in similar way as you use in excel environment. You can explore data in pivot table, sort data and can apply filters on it. And also if you have workbook open rights you can open that workbook in excel and save it on your local machine to view it offline.

There are four main components of excel services:

1. Excel Web Access Web Part: This is a web part just like other web parts to interact with excel workbooks in browser. This web part could be connected to other web part on the page.
2. Excel Web Services: These are the services that developers can use to programmatically access the excel objects.
3. REST API: This is called representational state transfer API to access the parts of excel workbooks by specifying them in URL
4. ECMAScript: JavaScript to use with Excel Web Access control

How do I start BI in SharePoint2010?

SharePoint 2010 comes with more power BI features including Performance Point dashboards, KPIs, Scorecards and excel services. You can build interactive analytical reports from variety of data sources including legacy OLTP databases including Oracle, SAP data sources etc, SSAS databases, flat files and other OLEDB sources. Detailed blogs on each of these report type will be published later.

 

To start building BI pages within SharePoint2010, we first need a BI site that will be created by selecting Business Intelligence Center  site template. Once the BI center site has been created following page is available to move forward.

 

 This page has three actions label, Monitor Key Performance, Build and Share Reports and Create Dashboards. To immediately start developing BI contents, hover mouse on any of the label, by doing so a DIV will be appeared on the left of the label. Click on Start using PerformancePoint services and it will display the following page with the button to run dashboard designer. Also there are some pre designed samples that could be viewed by clicking on links.

 

 By clicking on Run Dashboard Designer button, an exe (DashboardDesigner.exe) will be downloaded and installed in the client machine. This is windows based component that is needed to design and deploy the contents. After installing the exe file, the designer will be launched as shown below.

 

 This designer has two tabs. SharePoint and Workspace. The SharePoint tab display all the contents that are saved on server and Workspace tab displays those contents that are newly created or selected from server to edit in this designer. In the Workspace Browser window there are two folders Data Connections and PerformancePoint Content. In Data Connections folder all connections to any data source will be displayed where as in PerformancePoint Content folder following content types are displayed.

 

KPI

Key Performance Indicators could be designed to display the goal and current status of any organizational performance measure. Another good feature added is KPI details report. This report could be added and on mouse click the details related to selected KPI will be displayed.

 Filter

Filters are used to filter ay of the PerformancePoint content. Filters are created and then connections are established among filter and other items.

Report

There are various types of reports that could be created using PerformancePoint services. Including Analytic Grid, Analytic Charts, Excel files through excel services, KPI detail reports, SSRS Report, Web pages and few more.

 

Indicator

Indicators are used to show whether or not the given measure is on the target. This information is displayed in many ways, e.g. signal lights, trend bars, cylinders, green/red/yellow flags/arrows, meter gauges and more.

 

 Scorecard

These are the items that show performance for given measures. It compares the actual values of measures, compare them with the goal and displays visually by using graphical indicators. The following image is taken from scorecard help page in SharePoint 2010. It displays the actual and target values of sales performance and sales margins.

 

 Dashboard

Dashboard is the consolidated view of all BI items created by using PerformancePoint and excel services. A typical dashboard might include all the above items. It provides a good view of overall organization’s performance at a glance. The information displayed on dashboard can further be analyzed by using drill down and drill up options. One more powerful tool added is Decomposition Tree. It provides categorized view for meaningful analysis. I’ll describe this tool in detail in my next posts.

Hence, Microsoft has provided variety of individual items and it is up to the developers and business users how they join all these parts to make a powerful engine that will drive the organization to the highest levels in short time.

Starting Exploring Power Pivot

I have a little complain from Microsoft not to provide cube browser as in SSAS and BIDS. And to exress this I wrote this blog post and this forum post.
And I believe this complain will no longer exists after exploring the Power Pivot as I read and viewed some text based and video tutorials on Power Pivot and hope that it will give me the desired cube browsing and analysis experience when I’ll make some reports using this in next few day.