Business Intelligence capabilities in SharePoint 2010


Here is a good picture showing all Business Intelligence capabilities in SharePoint 2010. It explains the area of BI, SharePoint BI services such as Excel & PowerPivot, Excel Services, Visio Services, Performance Point Services, Reporting Services and Report Builder. IT also layout application services architecture and type of data sources supported for each services.

Business Intelligence in SharePoint 2010

Business Intelligence in SharePoint 2010

Feel free to download it here:

http://www.bronios.com/wp-content/uploads/choose-a-tool-for-business-intelligence-in-sharepoint-2010.pdf

or

http://www.microsoft.com/downloads/details.aspx?FamilyID=fc97d587-ffa4-4b43-b77d-958f3f8a87b9&displaylang=en<-->

SQL Server Reporting Services 2005 report parameter auto Postback Issue!


I am sure some of you might experience the annoying auto postback on SSRS report parameters. It will postback every time you change the value of the parameter even there do not have any dependencies at all. I am having this problem for several reports with quite a number of parameters. I tried googling but doesn’t help much. After some try and error, I manage to fix some of it (not 100%). This is cause by how we input the default value on the report parameters and dataset parameters. For example, you need to set the default value “100” to a parameter. Some of you might put in “=100”. Try to change it to “100” without the “=” sign. This stupid “=” sign is causing my report to keep on post back for every change. You might need to apply the same rules at your dataset parameters. I am not sure it is 100% works but at least it solves most of my reports. Hope this can help! Do let me know if you got better solution!

An error occurred during printing. (0x8007F303) – MS Reporting Services


Yes, it’s another bug in SSRS. One of my colleagues building a MOSS portal that integrated with SSRS. We are facing a problem when user clicks on print from ‘Action’ button. The first dialog prompted to choose the printer but when press print, the following error message pop up indicate “An error occurred during printing. (0x8007F303)”. If click preview button rather than print, a preview of blank report with the words: “An error occurred trying to render the report (0x8007f303)”. Guess what is the problem???

After hours of searching, debugging and googling, he manage to find out the root cause. This is because we did some customization on the reporting services web.config file. We remove the export “tif” file type in the reporting services web.config file. We do know why this little change is causing the big problem during printing.  Hope this helps!

Reporting Services Navigation – Jump to URL error


I come across some problems in Reporting Services 2005 in SharePoint integration mode when using Navigation – Jump to URL. Here are some strange behaviors on the problem:

1. The link won’t work if you link to a page/item inside a SharePoint List.

2. The link is OK if you link to invalid page/item in a SharePoint List (invalid item name but valid path)

3. The link is OK if you link to other site like http://www.microsoft.com or http://www.ajaxworkspace.com

I tried googling around but can’t find any solid solution. But I found out that we can use JavaScript as a workaround to solve this issue. Replace your Jump to URL format from:

= “http://servername/sitename/documentlist/pagename.aspx”

With

= “javascript:window.location.href= ‘http://servername/sitename/documentlist/pagename.aspx ‘;”

It works for me!

P/s: Does anybody know where to find the restriction rules for error “Other restrictions apply. (rsInvalidItemPath)” in order to construct a valid URL path for reporting services?

SSRS Performances – Matrix control VS Table control


SSRS provide table and matrix control to let user populate data in different scenario. Table control let user to populate simple format of data where fixed columns applied and rows grow dynamically. Matrix provide more powerful features where it columns and rows can be dynamically grow. In that case, writing a store procedure to support a matrix become easier in some scenarios.

E.g. you have a source table Sales with data below:

ClientID

Product

SalesAmount

Customer A

X

1000

Customer B

Y

2000

Customer A

Y

1000

Customer C

Z

3000

Customer C

W

4000

Customer A

Y

2000

You need to generate a sales report with the following format:

ClientID

W

X

Y

Z

Customer A

0

1000

3000

0

Customer B

0

0

2000

0

Customer C

4000

0

0

3000

You can simply write a SP as below and bind the columns to products and rows to ClientID:

SELECT ClientID, Product, Sum(SalesAmount) FROM Sales GROUP BY ClientID, Product

This solution seems pretty simple and straight forward. However, this will only work fines if you have small set of record in your tables. If you have hundred thousand of records with >20 type of products in your table, this solution no longer perfect. You will be facing performance issues. Yes, Matrix control very slow when deal with large amount of data.

Read the rest of this entry »

Page break, Blank Space, subreports and Continuous Printing in SSRS


If you have a main report that calls subreports.  When a subreport has enough data such that it will not fit into the remaining area of the current page, the renderer inserts a page break leaving alot of white space befere the subreport output. Please do not waste your time to find out how to solve it. Just go a head to remove the sub report and rebuild in the main reports. This is a known bug reported in MS KB. Here are the details:

http://support.microsoft.com/kb/938943
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2374376&SiteID=1

Posted in SSRS. No Comments »

Last mile of Business Intelligence


Here is an interesting article, “Last mile of Business Inttelligence”. You may face the same problem in the BI world these days.

http://www1.juiceanalytics.com/writing/2007/11/last-mile-business-intelligence/

Posted in SSAS, SSRS. No Comments »

Problem with web part Target Audience


I am working on a SharePoint dashboard project recently. We have a lot of pie chart and graph integrated using Reporting Services Add-In web part. We have set Target Audience in each web part to offer multiple views to different users. It works fine in the past until this Monday. All the web part with target audience tie to AD group disappear. After spending half day on it, we found that the password for search service and content access crawler account expired. This effect the user profile and cause the target audience failed. Do check out these accounts if you have similar problem.

Disable right click on SQL reporting services report in SharePoint 2007


We are working on SharePoint Workspace with Business Intelligence solution. There some report build with reporting services show in SharePoint page. Then, we come across a special requirement that the client do not allow user to “Right click” on the entire SharePoint page including the reports. If you are using page viewer or report web part to view the report in native mode, you will never able to include your JavaScript to disable the right click. This is because it using iFrame to link to the report. To overcome this problem, there are 3 steps need to be done.

  1. Install and configure Reporting Services Add-in for SharePoint
    First, we need to install the “Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies” add-in and change reporting services to SharePoint integration mode. You can download the add-in in https://www.microsoft.com/downloads/details.aspx?familyid=1E53F882-0C16-4847-B331-132274AE8C84&displaylang=en  and get some introduction on its benefit here http://blogs.msdn.com/SharePoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx . You need to configure the reporting services after installed the add-in. You can easily find some help via google. Once done, you have to upload some report to SharePoint document library.
  2. Add JavaScript to disable the right click on SharePoint.
    To simplify the story, we use Content Editor Web Part to ad in the JavaScript. Enter code below in “Source Editor”:
    <SCRIPT language=”JavaScript” >
    function queryString(parameter) {
      var loc = location.search.substring(1, location.search.length);
      var param_value = false;
      var params = loc.split(“&”);
      for (i=0; i<params.length;i++) {
          param_name = params[i].substring(0,params[i].indexOf(‘=’));
          if (param_name == parameter) {
              param_value = params[i].substring(params[i].indexOf(‘=’)+1)
          }
      }
      if (param_value) {
          return param_value;
      }
      else {
          return false; //Here determine return if no parameter is found
      }
    }
    function right(e) {
    if (navigator.appName == ‘Netscape’ &&
    (e.which == 3 || e.which == 2))
    return false;
    else if (navigator.appName == ‘Microsoft Internet Explorer’ &&
    (event.button == 2 || event.button == 3)) {
    alert(“Sorry, you do not have permission to right click.”);
    return false;
    }
    return true;
    }
    document.onmousedown=right;
    document.onmouseup=right;
    if (document.layers) window.captureEvents(Event.MOUSEDOWN);
    if (document.layers) window.captureEvents(Event.MOUSEUP);
    window.onmousedown=right;
    window.onmouseup=right;
    </script>
    addin01.jpg
    The right click will disable on the specify page now.
    Read the rest of this entry »

Change color on MS SQL Reporting services charts/Pie


Since I am still new to SQL reporting services, I thought changing charts/pie color is impossible. The VS editor seems doesn’t have any more setting for me to do so. As you all know, the default palette color is very ugly. Luckily, I manage to found one article describe how to get more out of these limitations.
http://msdn2.microsoft.com/en-us/library/aa964128.aspx

This document describes how to customize your charts/pie. Here is my before-after outcome:

Before
before1.jpg

After
after1.jpg
It is not hard, just extra some codes. Do make it to make your client happier! Cheers!

Posted in .Net, SSRS. 1 Comment »