Developing and deploying SSRS Reports to Pre-Prod/Production environment

Recently I was developing an application for a client that requires SSRS report to view the status. I built the application in SharePoint, .NET (Entity Framework) and SSRS reports. I am not going into the details of the application but I will post about the SSRS reports.

The report is accepting a parameter from query string to load the data but by doing this, we were allowing anyone to see other’s data that we don’t want. To make your report secure you have to add a field saying “Created By” so that report can only show the currently logged-in user report.

I am creating a demo project for this purpose to show the steps you can follow.

Create a project

Add a Shared Data Source and supply your database connection information

Add a report item and write your query to show the data.

Now add “Parameter” called it “CurrentUser” as shown below

In the Default Values section, select “Specify values” and then click “Add” button as shown below:

Click OK.

Add another parameter called “RecordId” to filter record.



Now go back to your Dataset and edit it and update your query to include “CurrentUser” and “ReocrdId” parameter as shown below



Now supply 1 or whatever Id you have got in your database associated with currently logged in user to view data. Below is a sample screenshot:

but as soon as I supply any other record id which is not associate/related to currently logged in user, it just shows blank report. That how I want my report to work.

Deploying report to PRE-PROD/PRODUCTION environment

To deploy a reports in SharePoint integrated environment, you need to follow below steps:

Create a Report Library that will keep your reports and call it “Reports” or whatever you want. Or if you have that library you can use it.


Create a data connection library that will keep your database connection for the reports and call it “Demo DataConnections”

Create a .rsds file in your favourite notepad and name id “Dem-DataConnection.rsds” or whatever you want to call it and write below contents:

<?xml version="1.0" encoding="utf-8"?>
<DataSourceDefinition xmlns="http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource">
  <Extension>SQL</Extension>
  <ConnectString></ConnectString>
  <CredentialRetrieval>Integrated</CredentialRetrieval>
  <Enabled>True</Enabled>
</DataSourceDefinition>
Create a data connection item in above created library (i.e. Demo DataConnections”) by uploading .rsds file and save it. Once it is uploaded, go back to your data connection library and click on link appears in the Name field as shown below:

After clicking on “Demo-DataConnection” link shown in Name field, you will be given another screen to supply connection to your desired database:

Now upload your reports (.rdl files) in “Demo Reports” library.

Now go back to your reports library and select your uploaded report, expand ECB menu and then click on “Manage Data Sources” as shown below:

You will be shown a page, where you can associate Data Source for your reports, like below:

Click on “ReportDataSource” link and associate your data connection to this report so that report can talk to database and fetch records.

Click OK, OK and Close and you are done.

Now click on your report to view the data. So far the report is configured but it will not take any parameter from query string to pass to your report.

Creating Report page that accept values from Query string

To create a report page that can accept values from query string and can pass to your report and can be linked with application please follow below steps:

Create a page in Pages library or Site Pages

Add SQL Server Reporting Services Report Viewer web-part and click on “Edit web part Properties” as shown below:

and select your report.

Now add “Query String (URL) Filter” web-part and give a parameter name that you will be passing in URL. For instance http://www.mmasood.com/Pages/Report.aspx?ReportId=1

Connect Query String Filter web-part to SQL Reporting Service Report web-part and bind the parameter to your report like shown below

Once it is connected, you can supply value in query string (i.e. http://www.mmasood.com/Pages/Report.aspx?ReportId=1 ) and that will be passed to your report.

I will try to package it in wsp and will upload it soon. That’s all.

Creating Master page and Page Layouts in SharePoint 2013

In this post I am going to share how to create Master page and page layouts in SharePoint 2013 using html file you have.

 

When I create Master page and page layout, I use common .css for all pages, that I include in Master page and for each page layout I create separate .css file so that I can manage page layout easily.

1.       Open Design Manager

2.       Click on Edit Master Pages

There are two options, you can either create using an .html file (i.e Converting .html file to SharePoint Master page) or you can start with startermaster page

I have used below .html file (shown as image) to create my master page:

Click on “Convert an HTML file to SharePoint master page” and click add link to upload your or above .html file and then click Insert button as shown below:

Once you have created, it will be displayed in the list of master pages. Click on the master page you just created, it will show something like shown below:

Now go to Master page and Page layouts gallery, and download the generated .html and save it. Come back to “Master Page preview” and click on Snippets.

I am going to add “Search box” from the snippet to the .html master page. Click on “Search box” control, it will show below screen:

Click on “Copy to Clipboard” button, to copy the html of the control to clipboard. Now open the generated .html master page and paste the above copied html source to appropriate location.

Repeat the same process for Top navigation.

Here is the page created using above created master page:

Now I am going to share, how I usually create Page Layout in SharePoint 2013.

1.       Go to Design Manager

2.       Click on “Edit Page Layouts”

3.       Click Create a page layout

For this demo purpose, I am creating a welcome page layout with two columns and one column layout.

When you click “Create a page layout”, it asks you to provide name, master page and content type, I have provide below details:

Once, page layout gets created, go to Master page and page layouts gallery and download .html file of above page layout.

Remove contents between <!--MS:<asp:ContentPlaceHolder ID="PlaceHolderMain" runat="server">--> and <!--ME:</asp:ContentPlaceHolder>-->

And add below line to associate its own .css file

<!-- custom css -->

<link type="text/css" rel="stylesheet" href="/_layouts/15/Jacobs/DemoPortal/Css/Custom_Minimal/Custom_Welcome.css"
ms-design-css-conversion="no" />

Don’t forget to include “ms-design-css-conversion=no” attribute. Go back to Design Manager, and click on “Edit Page Layouts” and then click on newly created page layouts like shown below:

Now click on Snippet to add some web-part zones in it. When you add web-part zone in snippet area, it shows property of that web-part zone. The web-part zone has got a property called “Header Text” that shows the title of the zone.

Below is an image of a page in edit mode created using above page layout:

And finally here is the output page with web-parts:

 

 

That’s it.

 

 

 

Provisioning WebParts to Correct zone in SharePoint 2013

If you have had scratched your head for placing a web-part in correct zone through module or code in SharePoint 2013 and did NOT find answer then this blog will help you overcome.

In SharePoint 2010, you specify zone (i.e title property of <WebPartPages:WebPartZone tag) it works fine but in SharePoint 2013 it does not like it L

After doing some digging that is added web-part to my favourite zone and tried to check what zone SharePoint assign to web-part through code and realised that SharePoint accept Id of <WebPartPages:WebPartZone tag as shown below image:

And here is the sample snippet for Module that adds web-part:

That’s it.  

How I wrote my SharePoint 2013 Bill Tracker App

I am writing this post to help myself and other fellows who are developers like me and are interested in developing SharePoint 2013 Apps.

In this post I am going to explain how I developed my BillTracker App for SharePoint 2013 using Javascript/CSOM for accessing SharePoint List Items using Repository and Knockout JS for displaying bills using MVVM patterns.

I am starting by showing high level architecture so it will make it easier to understand at first glance then project structure and code snippets.

Below is high level architecture:

 

And MVVM with the help of Knockout JS as shown below:

And here is my project structure:

and here is repository implementation in javascript using CSOM:

window.Masood = window.Masood || {};
window.Masood.BillTracker = window.Masood.BillTracker || {};
window.Masood.BillTracker.Repositories = window.Masood.BillTracker.Repositories || {};

window.Masood.BillTracker.Repositories.SharePointRepository = function (context, web, listName) {

    //properties
    this.context = context;
    this.web = web;
    this.listName = listName;
    var listItems;
    var listItem;

    var dateFormat = 'dd-MMM-yyyy';
    //collection for getAllItems
    this.Results = [];
    this.ErrorMessage;

    //result for getBill method
    this.Result;

    var clientSuccessCallback;
    var clientFailureCallback;

    this.getAllItems = function (sucCallback, failCallback) {

        //save the client's callback to variables 
        //that will be used in getAllItemsSuccessCallback event
        clientSuccessCallback = sucCallback;
        clientFailureCallback = failCallback;

        //use CSOM to grab SharePoint items
        listItems = null;
        var oList = web.get_lists().getByTitle(listName);
        var camlQuery = new SP.CamlQuery();
        //ste your CAML to filter items

        listItems = oList.getItems(camlQuery);
        context.load(listItems);
        context.executeQueryAsync(Function.createDelegate(this, getAllItemsSuccessCallback),
                                                                    Function.createDelegate(this, failureCallback));
     
    };

function getAllItemsSuccessCallback() {
        var listItemInfo = '';
        var listItemEnumerator = listItems.getEnumerator();

        this.Result = null;
        this.Results = [];

        paymentDate = '';
        while (listItemEnumerator.moveNext()) {
            var oListItem = listItemEnumerator.get_current();

            //push your Item/Model to Results array
        }

        //call client's success callback    
        clientSuccessCallback();
    }
}

and here is the ViewModel that calls the repository to get all/filtered items:

window.Masood = window.Masood || {};
window.Masood.BillTracker = window.Masood.BillTracker || {};
window.Masood.BillTracker.ViewModels = window.Masood.BillTracker.ViewModels || {};

window.Masood.BillTracker.ViewModels.AllBillsViewModel = function (context, web) {

    //private variables
    var self = this;
    var repository = new window.Masood.BillTracker.Repositories.SharePointRepository(context, web, 'MyBills');
    helper = new Helper();
    var _totalAmount = 0;

    //observable properties those are bind to UI elements
    self.Items = ko.observableArray();
    self.message = ko.observable("");
    self.totalAmount = ko.observable("");


    self.load = function () {
        self.message("Please wait, loading all your bills...");
        repository.getAllItems(onSuccess, onFailure);
    };

    function onSuccess() {
        //clear all items from observable collection
        self.Items.removeAll();
        var allBills = repository.Results;
        
        //populate observable collection
        _totalAmount = 0;
        for (i = 0; i < allBills.length; i++) {
            var item = allBills[i];
            self.Items.push(item);

            _totalAmount += parseFloat(item.Amount);
        }

        self.totalAmount(_totalAmount.toFixed(2));
        self.message("");
    }

    function onFailure() {
        alert('Something went wrong while fetching all your bills. Please contact administrator or  email at mmasood@gmail.com');
        self.message("Something went wrong while fetching all your bills. Please contact administrator or  email at mmasood@gmail.com");
    }
}

 

If you look above code, it does not update UI elements rather it just updates observable collections and property those are bind to UI elements as shown below:

 

 

And each .aspx or .html file has got its codebehind .js file that glue ViewModel instance as shown below:

var context;
var web;

//this function is called when page is loaded and DOM
//is ready
function sharePointReady() {
    context = new SP.ClientContext.get_current();
    web = context.get_web();
    
    //create instance of AllBills ViewModel 
    //and rest is taken care by KnockoutJs

    var viewModel = new window.Masood.BillTracker.ViewModels.AllBillsViewModel(context, web);
  
    //call load to fetch items from SharePoint
    //and populate all observable property and collection
    viewModel.load();
    ko.applyBindings(viewModel);
}

One last thing, before packaging, you need to minify all .js files using Micrsoft Ajax Minifier tool.

That’s it. Hope it would help you to understand and will help you to write better apps.

Firewall port open check

You might need to check whether some ports are open in an environment where Firewall are between servers.

Telnet is the tool to check the connectivity. Recently I was resolving one issue related to People search across multiple domains so I needed a tool to verify connectivity for ports 88, 139 and 445.

I used telnet to check, here is the command

telnet target_server_name_or_ip_address port_to_check

For example, log on to the server where you want to check connectivity from:

C:> telnet AD_SERVER_IP 88

There is another tool called PortQry that checks whether the port is listening or not, please visit http://support.microsoft.com/kb/816103

That's it.

Provisioning WebParts through Module

If you want to provision a web-part (or many) for a Page(s) through Module, here is what I use in my project (especially WCM)

I alway create a Feature (as I love it) and it gives Administrator a control to add or remove functionality and also it gives flexibility for deployment. I will share my WCM project structure in future. So stay tuned to my site please.

Before creating Visual Studio Project, please follow below steps:

1. Create a page using your desired page layout (or the page you want to package)

2. Download a copy of the page you created in step 1.
3. Insert Content Editor or the webpart you want to include in the page using Module. For simplicity I am using Content Editor web-part.
4. Configure above added web-part. For instance add image or add some javascript whatever you want to do.
5. Export web-part and save the file.

Now create a Visual Studio Project for SharePoint and follow below steps:
1. Create Module or SharePoint mapped folder to drop required files
2. Create a Module, name it for instance HomePageModule and copy the file (from step 2 in before section) and configure Pages library of desired site.
3. Copy the contents from exported web-part file. Do NOT copy <webparts> node
4. Check whether the contents has got <!CDATA section, if yes then Encode the ONLY text srrounded by <!CDATA[[ and ]]> section and replace <!CDATA[[<your encoded html>]]> with encoded html
5. Add <AllUserWebPart WebPartZoneID="Zone 1" ZoneOrder="1"><!CDATA[[your copied text goes here]]>
6. Add Feature and include above created Module.
7. Deploy the Feature and you will see the page with configured web-part


Happy coding !!!

SharePoint 2010 Version to Patch Level Description

You might want to know which patch level my SharePoint 2010 Farm is especially when you do some migration stuff from DEV/STAGING to PRODUCTION or creating a new environment based on PRODUCTION.

Go to your SharePoint Central Admin > Manage Servers in this farm

 

Note the Configuration database version as shown in red box. Browse http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=224 and find that configuration database version in above site you will patch level description.

 

Installing Service Pack 1 and Cumulative Updates on SharePoint 2010 Server

Recetly I had to install Service Pack 1 and latest cumulative updates for SharePoint 2010 Server for a client.

The task was very simple but it took a while to get it completed due to some issues with environment. I am writing this blog to help myself and my friends in future.

I did following:

1. Installed SharePoint 2010 Foundation Service Pack 1 (Reboot after installation)
2. You may need to install SharePoint 2010 Foundation Lang Pack Service Pack 1
3. Installed SharePoint 2010 Server Service Pack 1 (Reboot after installation)
4. Ran PSConfig Wizard (or you may need to run PSConfig wizar from PowerShell command with -force switch)

After performing above steps, when I access SharePoint Central Admin and site at port 80, I did not get anything (or white page)

I was wondering what is happening, could not see anything in ULS log or Event viewer. So one of my friend who looked as well after spending couple of hours, we figured out what had happened.

Follow below steps if you want to patch your SharePoint 2010 environment:

1. Take backup of all web.config (all web-apps, layouts and some web-services related config located at 14 hive\Templates\WebServices
2. If you are runing your server in VM, take snapshot so that you can go back to where it was.
3. Install SharePoint 2010 Foundation Service Pack 1
4. Install SharePoint 2010 Foundation Lang Pack Service Pack 1 (if any, do for all languages)
5. Install SharePoint 2010 Server Service Pack 1
6. Install SharePoint 2010 Server Lang Pack Service Pack 1 (if any, do for all languages)
7. Reboot server
8. Run PSConfig Wizar (run as administrator)
9. Check Central Admin and Site are working. If not follow below check list to rectiy your Farm
10. Install Cumulative Update
11. Reboot
12. Run PSConfig Wizard
13. Check Central Admin and Site are working. If not follow below check list to rectiy your Farm

Here is the check list if SharePoint Farm goes wrong:

1. Disable custom error off
•  Open web app and central admin web.config file and find "CustomErrors" and update value to CustomErrors = "Off"
• Find "CallStack" and update value to CallStack="true"
• Open web.config located at _layouts folder and do above steps.
2. If SharePoint is not showing anything, then you must check web.config files for duplicate entries or you may need to compare with previous version. You can use ExamXML or I have developed a tool (to validate or find duplicate elements in web.config), which I will upload soon.
3. After fixing web.config issue, you will see .net or SharePoint specific error and you can fix from there.
4. Check Application Pools are running
5. Check database can be ping from web front end

I will keep updating this list. If you guys have got similar list, you can add to comments.

Hope this will help you and save your time.

My SharePoint 2013 Bill Tracker App got published

I developed an SharePoint 2013 App to manage/track all your bills and it has got published on SharePoint Store that can be downloaded and used in your site.

Head to SharePoint store and search for "bill tracker" as shown below and click add it:

I will blog about this App and soon.

Configuring App Domain in Cloudshare environment

CloudShare is giving pre-configured SharePoint 2013 environment for trial purpose for 14 days. But the environment does not have app domain configuration. It does have VS 2012 installed. For developer especially if you are interested to develop SharePoint 2013 apps, you need to have an environment where you can develop and test.

I am using CloudShare environment to develop and test SharePoint 2013 apps. I want to share the steps I used to configure the environment and also for myself for future reference. Below are the steps: 

1.       Create A record to your DNS as shown below

a.       Open DNS Manager 

                                                               i.      Right click on Forward Lookup Zones shown below

                                                             ii.      Select Primary Zone

                                                            iii.      To all DNS Servers running on domain controller in this domain

                                                           iv.      Enter Zone name. Such as mmasood.com

                                                             v.      Click Finish

b.      Create Host (A) record

                                                               i.      Select newly created Zone from left pane and on right pane right click and select New Host (A) record

                                                             ii.      Provide IP address of your SharePoint Server

                                                            iii.      Create CNAME as shown below

2.       Open command prompt or powershell window and ping abc.apps.mmasood.com as we have configured *.apps.mmasood so just try replace any character or with any word.

 

3.       Now you have configured DNS for the server

4.       Browse Central Admin > Manage Service Applications

5.       Create App Management Service application and provide SPFarm account when creating app pool for this service app.

6.       Run below powershell command

$sa = Get-SPServiceApplication | where-object { $_.displayname -eq "App Management Service" }

$subsservice = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $sa.ApplicationPool –Name "Subscription Settings Service" –DatabaseName "SubscriptionSettingsDB"

$proxySubscriptionService = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $subsservice

Get-SPServiceInstance | Where-Object { $_.typename -eq "App Management Service" } | Start-SPServiceInstance

Get-SPServiceInstance | Where-Object { $_.typename -eq "Subscription Settings Service" } | Start-SPServiceInstance

Set-SPAppDomain "apps.mmasood.com"

Set-SPAppSiteSubscriptionName -Name "app" -Confirm:$false

7.       Now create SharePoint 2013 App project and deploy it.

 

8. When you deploy you might see “Installing …” in visual studio 2012 like shown below

Let the VS deploy your app.      

9. You should see similar result

 

You can see the url of the app with app-prefix and app domain configured above. Hope this will help and save your time.