Wednesday, November 23, 2016

Hiding extra export options in SSRS 2012 Reportviewer for SharePoint

In SSRS 2012, reportviewer provides multiple export options including pdf, word, excel, mhtml etc.
Different customer may have different requirement to have the specific export options.
Our client wanted to have only excel and pdf to be visible in the available options.
Its simple but somewhat changed from the way we do in SSRS 2008.

Use below powershell to hide all the options except pdf and excel.

$SSRSApp = Get-SPRSServiceApplication
if ($SSRSApp -ne $null)
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "XML" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "MHTML" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "CSV" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "IMAGE" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "HTML4.0" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "RPL" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "WORD" -ExtensionAttributes "<Visible>False</Visible>"
   Set-SPRSExtension -identity $SSRSApp -ExtensionType "Render" -name "WORDOPENXML" -ExtensionAttributes "<Visible>False</Visible>"

update the above script based on what options needs to be hidden and what options needs to be kept visible to user.

Monday, October 10, 2016

Apply filter on calendar view

Whether we use Dataview or List view webpart, we can filter the data according to our need. Filtering will be based on value of other column of the list. But what if we want to apply filter in the calendar view of the list? If we will use dataview using sharepoint designer, filter option will not come. We will have to write some code in .Net to achieve that. An alternate way is a bit simple. Suppose we want to display the calendar Item and appointments which are scheduled on current day. It can be achieved using ListView webpart as below-

1. Take a List view Calendar webpart.

2. Create a calculated column say 'StDate' whose value will be determine from the column 'start time'.

3. open the page in edit mode and edit the webpart. edit toolpane will be opened.

4. Click on modify this view.

5. In filter section select the column StDate and give its value [Today].

6. Click Ok.

Thats It. Now it will filter according to the value of the calculated column 'StDate'.

Ofcourse the filtering condition will be based on other column value of the calendar. Means we can apply filter on Calendar view in sharepoint upto a defined level only. But what if we want to filter according to some other parameter say querystring. In that case we will have to write some code in .Net. In code we may create a custom calculated column and assign the value of querystring to that column. that column may be used for filtering .

Hopes it will help.

Upgrading from SQL 2008 R2 to SQL 2012 in SharePoint 2010 farm

We have recently upgraded to SQL 2012 in SharePoint 2010 farm for our client.
Below are the recommended steps for upgrading-

1. we can't directly upgrade from SQL 2008 to SQL 2012. we first need to install SP1 of SQL 2008 R2 and then upgrade to SQL 2012.
2.Side by side upgrade is recommended approach wherein we create a new DB server with upgraded version and then replace the new server with the old DB server.
3. Disconnect the existing DB server from the farm by running the SharePoint config wizard. Ensure to retrieve the pass-phrase before disconnecting the farm.
4. Connect to new DB server by re-running the config wizard. Here config wizard may fail on third step and may give below error (as happened in my case)-
"Configuration wizard cannot connect to configuration database. Object reference not set to an instance of an object."
To resolve this issue we shall use SQL alias on SharePoint server before connecting with new DB server. below are the steps-
  • run cliconfig.exe by double clicking it under C:\windows\system32 (C:\windows\sysWOW64 for 64 bit system)
  • On General tab -> Enable TCP/IP
  • On Alias tab > Click Add
  • Choose TCP/IP
  • On Server Alias > Give your Old SQL Server Name
  • Server Name > Give your New Server Name
  • Uncheck Dynamic > Choose the default Port Number 1433
  • Now run the config wizard to connect with new DB server
The config wizard should run successfully.
5. On Central admin site, navigate to servers on the farm page. This page might still be showing old DB server. The reason being, SharePoint retains reference of old server in its config DB. To resolve this issue, follow below steps in the config DB of the new server.

  •  select * from objects where name = ‘Sharepoint_Config’
    1. - You might get two rows. Check if the parent ID of both the entries are same.
    2. - One of the row will also have the parent ID equals to the ID of that row.
    3. - Note down the ID the other row where parent ID is not equal to the it’s own ID (ID1)
  •  Get ID of the new sql server (ID2)
    1.  - Select ID from objects where name =’newsqlservername’
  • Select ID from objects where parentid= ID2 (note down the ID = ID3)
  • Update objects set parentid=ID3 where ID=ID1. Try visiting Server in the farm page (FarmServers.aspx). you might still see the old server name.
  • Now restart your server and open the FarmServers.aspx page and it should now display the new server name. Refer this-  
6. Possibly the central admin content DB or may be your web application content DB might also be showing the old DB server. To resolve this, detach the content DB using PowerShell and then attach again with same content DB from new server.
Thats all. it should now be okay and the upgrade should work correctly.

Note- It should be noted that upgrading to SSRS 2012 will take away the Reporting services configuration manager and instead the configuration will be done by SharePoint service application for SSRS which automatically gets created once we upgrade to SSRS 2012.

Sunday, July 14, 2013

Custom Treeview using JavaScript- SharePoint 2010

we can find a lots of options for creating treeview. one option may be to use .NET treeview control itself. another option may be to write your own treeview using C#, Jquery etc. or get a JQuery treeview from google.
I looked for all the options. The .Net treeview has post back problem with each expand collapse.
The JQery treeview which I found on JQuery site was good but it has very large JQuery written for it and was more complicated to customize it. Also, with two level expand-collapse functionality with dynamically populated data made it very slow. To overcome from this problem, I decided to write my own TreeView for our own specific requirement.

We have a set of countries and for each countries we have set of categories and for each category there were a set of  subcategories. There were FAQs corresponding to each question. We had to show the countries, categories and subcategories in the form of treeview. I implemented it using javascript. The data I fetched using client object model from SharePoint list.

Below is the code-

<script type="text/javascript">

    ExecuteOrDelayUntilScriptLoaded(getData, "sp.js");   //this is necessary to ensure the library is loaded before function triggered
    var namedListItem;
    var countryItems;
    var CatItems;
    var subcatItems;
    var country;
    var category;
    var subCategory;
    var clientContext;
    function getData() {

        clientContext = SP.ClientContext.get_current();
         var region = clientContext.get_web().get_lists().getByTitle('Region'); 
        country = clientContext.get_web().get_lists().getByTitle('Country');
        category = clientContext.get_web().get_lists().getByTitle('Category');
        subCategory = clientContext.get_web().get_lists().getByTitle('SubCategory');
        var camlQuery = new SP.CamlQuery();
        camlQuery.set_viewXml(''); //caml statement goes here between the single quotes
        namedListItem = region.getItems(camlQuery);
        clientContext.load(namedListItem, 'Include(ID, Region)');
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onRegionQuerySucceeded), Function.createDelegate(this, this.onRegionQueryFailed));

    function onRegionQuerySucceeded(sender, args) {
        var listItemInfo = '';
        var listItemEnumerator = namedListItem.getEnumerator();
        while (listItemEnumerator.moveNext()) {
            var oListItem = listItemEnumerator.get_current();
            document.forms[0]["ddlRegion"].options.add(new Option(oListItem.get_item('Region'), oListItem.get_item('Region')));
        var countryCaml = new SP.CamlQuery();
        var selectedRegion = document.getElementById('ddlRegion');
        if (selectedRegion.selectedIndex != -1) {
            countryCaml.set_viewXml('' + selectedRegion.options[selectedRegion.selectedIndex].value + '
        else {

        var catCaml = new SP.CamlQuery();

        var subcatCaml = new SP.CamlQuery();

        countryItems = country.getItems(countryCaml);
        CatItems = category.getItems(catCaml);
        subcatItems = subCategory.getItems(subcatCaml);

        clientContext.load(countryItems, 'Include(ID, Country, RegionLookup)');
        clientContext.load(CatItems, 'Include(ID, Category)');
        clientContext.load(subcatItems, 'Include(ID, SubCategory, CategoryLookup)');

        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

    function onQuerySucceeded(sender, args) {

        var countryEnumerator = countryItems.getEnumerator();
        var catEnumerator = CatItems.getEnumerator();
        var subCatEnumerator = subcatItems.getEnumerator();
        mainDiv.innerHTML = '';
        var dv = document.createElement('div');
        dv.innerHTML = '';
        var i = 1;
        var j = 1;
        var k = 1;
        while (countryEnumerator.moveNext()) {
            var countries = countryEnumerator.get_current();
//below inner HTML will be generated dynamically on which treeview script will work.
//generated HTML should strictly be in the same format for treeview to work

            dv.innerHTML += '
 + i +
'"& gt;' + '+i+'"& gt;' + countries.get_item('Country') + '
'" style="display:none"& gt;
            while (catEnumerator.moveNext()) {
                var categories = catEnumerator.get_current();
                document.getElementById('categoryContainer' + i).innerHTML += '
 + i + j +
'" style="padding-left:10px"& gt;' + ' + i + j + '"& gt;' + categories.get_item('Category') + '
 + i + j +
'" style="display:none"& gt;';
                //method ExpandCollapse() will be responsible for expand collapse to work               while (subCatEnumerator.moveNext()) {
                    var subcategories = subCatEnumerator.get_current();
                    document.getElementById('subCategoryContainer' + i + j).innerHTML += '
 + i + j + k +
'" style="padding-left:10px" & gt;' + '::' + subcategories.get_item('SubCategory') + '
        }//treeview HTML ends

    function onQueryFailed(sender, args) {

        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    function onRegionQueryFailed(sender, args) {

        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
//countries will be retrieved based on region selected from dropdown.
    function getCountryCategory(ddl) {
        clientContext = SP.ClientContext.get_current();
        country = clientContext.get_web().get_lists().getByTitle('Country');
        countryCaml = new SP.CamlQuery();
        if (ddl.selectedIndex != -1) {
            countryCaml.set_viewXml('' + ddl.options[ddl.selectedIndex].value + '');
        else {
        countryItems = country.getItems(countryCaml);
        clientContext.load(countryItems, 'Include(ID, Country, RegionLookup)');
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

    //treeview javascript starts here
    function ExpandCollapse(obj) {
            var catContainer = obj.nextSibling.nextSibling;
            var imgArr = catContainer.getElementsByTagName('img');
            if (imgArr.length > 0) {
                for (var c = 0; c < imgArr.length; c++) {
                    if (imgArr[c].src.indexOf('minus.gif') != -1) {

    function setCountryCategoryVisibility(obj) {
        if (obj.src.indexOf('minus.gif') != -1) {
            obj.src = "/_layouts/Grail/Images/plus.gif";
            var subCatContainer = obj.nextSibling.nextSibling;
   = 'none';
        else {
            obj.src = "/_layouts/Grail/Images/minus.gif";
            var subCatContainer = obj.nextSibling.nextSibling;
   = 'inline';
            toggleNodes(obj);//toggle other nodes

    function toggleNodes(obj) {
    var currentImage;
    var divArr = mainDiv.getElementsByTagName('div');
    if (divArr.length > 0) {
        for (var c = 0; c < divArr.length; c++) {
            if (divArr[c].id != '') {
                var currentDiv = document.getElementById(divArr[c].id);
                var currentImgArr = currentDiv.getElementsByTagName('img');
                for (var currImgCount = 0; currImgCount < currentImgArr.length; currImgCount++) {
                    if (currentImgArr[currImgCount].id == {
                        currentImage = 'nottobecollapsed';
                    else {
                        currentImage = 'tobecollapsed';
                if (currentImage != 'nottobecollapsed') {
                    var divToBeToggled = document.getElementById(divArr[c].id);
                    var otherImages = divToBeToggled.getElementsByTagName('img');
                    for (var imagecount = 0; imagecount < otherImages.length; imagecount++) {
                        otherImages[imagecount].src = "/_layouts/Grail/Images/plus.gif";
                        var catSubCatContainer = otherImages[imagecount].nextSibling.nextSibling;
               = 'none';
    }//    //treeview javascript ends here
<asp:Panel ID="clientTreeviewContainer" runat="server" >
<asp:Label ID="lblRegions" runat="server" Text="Select your Region" ></asp:Label><br /><br />

<select id="ddlRegion" onchange="javascript:getCountryCategory(this);"></select>
<br /><hr class="Grail-hrLine" />
<div id="mainDiv" >


That's it.
This implementation requires very small javascript and will be applicable for n level expand-collapse in the treeview but the condition is- the generated HTML should strictly be in the same format as generated here.

Sunday, April 24, 2011

Deleting layout from layout gallery

When we want to delete a particular layout from layout gallery and it is being referred by pages (pages created by this layout), we will not be able to delete the layout. It will redirect us to error page stating-"This item cannot be deleted because it is still referenced by other pages." So, to remove the layout, we will have to remove all the pages which are referring the layout and then we will be able to remove the layout. Alternatively, we can move the layout to a temporary folder by opening the master page gallery in window explorer and then remove the temporary folder. Moving a layout to a different folder will break all the page references and therefore removing the layout will not create any problem.
This we can also do through code as below-

private static void DeleteLayoutFromGallery(SPWeb web, string layoutName)
SPFile layout = web.GetFile(web.Site.Url.ToString() + "/_catalogs/masterpage/" + layoutName);
SPFolder layoutFolder = layout.ParentFolder;
SPFolder tempFolder = layoutFolder.SubFolders.Add("tempFolder");
layout.MoveTo(tempFolder.Url + "/" + layout.Name);

The important point to note is- All the pages refering the deleted layout will not get the layout reference and hence will get corrupted. Even if we re-add those layouts through feature, broken references will still be broken and pages will remain be corrupted. If we will try to open those pages, It will display error message reading- "Resource can not be found."

Show only required layouts in available layout gallery - PublishingWeb.SetAvailablePageLayouts method

SharePoint have few out of the box layouts available with publishing site template. When we create a new publishing layout, that layout need to be added into the available layout gallery in order to make it available to user for creating the publishing page. This can be done through mannual steps as well as through code. SharePoint object model have PublishingWeb.SetAvailablePageLayouts() method which can be use to set available layouts. Here is how-

using(SPSite site=new SPSite(url))
PublishingSite pubSite=new PublishingSite(site);
PublishingWeb pubWeb = PublishingWeb.GetPublishingWeb(site.OpenWeb());
SPContentType associatedCType = pubSite.ContentTypes["My ContentType"];
if (associatedCType != null)
PageLayoutCollection allLayouts = pubSite.GetPageLayouts(associatedCType, true);
pubWeb.SetAvailablePageLayouts(allLayouts.ToArray(), true);

Thats it. The above piece of code will add all the layouts inheriting from content type associatedCType and remove all other layouts from the available layout gallery. Only added layouts will be available for creating pages

Friday, December 10, 2010

Creating webpart instance from assembly name and adding it into a webpart zone- SharePoint

We had an requirement to replace an existing webpart with a new webpart from a webpart zone on a publishing page in sharepoint portal. To accomplish this in a generic way we decided to create an utility which will use the new and old webpart DLL name. The DLL name will be passed as string. The utility will delete the existing webpart and create an instance of the webpart to be added from the DLL name.

We used Assembly class to create an instance of the webpart.
Here is the code-

using System.Reflection;

private static void GetWebsRecursively(SPWeb web, string pageName, string RemovedWebPartDll, string RemovedWebParttitle, string newWebPart, string IsPageOrLayout,string newWebPartTitle)
//pageName= name of the page/layout from where webpar has to be replaced
//RemoveWebPartDll= name of the DLL which has to be removed
//RemovedWebParttitle= title of the webpart to be removed
//newWebPart= DLL name of the webpart to be added
//IsPagOrLayout= value to indicate whether the page is publishing page
// or a layout. value would either be page or layout.
SPFile objFile = null;
string strCheckInMsg = "";
AppDomain domain = AppDomain.CurrentDomain;
AppDomain newDomain = AppDomain.CreateDomain("wpDomain");
DirectoryInfo dirInfo = new DirectoryInfo(@"C:\WINNT\assembly\gac_msil\" + newWebPart.Substring(0, newWebPart.LastIndexOf('.')));
DirectoryInfo[] dir = dirInfo.GetDirectories();
FileInfo[] file = dir[0].GetFiles();
string filePath = file[0].FullName;
Assembly asm = newDomain.Load(File.ReadAllBytes(filePath));
System.Web.UI.WebControls.WebParts.WebPart newWP = null;
newWP = (System.Web.UI.WebControls.WebParts.WebPart)asm.CreateInstance(newWebPart);
strCheckInMsg = "Modified by webpart delete console app";
if (PublishingWeb.IsPublishingWeb(web))
PublishingWeb pWeb = PublishingWeb.GetPublishingWeb(web);
if (IsPageOrLayout.ToUpper() == "PAGE")
string path = web.Url.ToString() + "/Pages/" + pageName;
objFile = web.GetFile(path);
string path = web.Url.ToString() + "/_catalogs/masterpage/" + pageName;
objFile = web.GetFile(path);

// If file is already in checked out state then first checkin that and approve
if (objFile.CheckOutStatus != SPFile.SPCheckOutStatus.None)


if (objFile.CheckOutStatus == SPFile.SPCheckOutStatus.None)

if (HttpContext.Current == null)
HttpRequest request = new HttpRequest("", web.Url, "");
HttpContext.Current = new HttpContext(request, new HttpResponse(new StringWriter()));
HttpContext.Current.Items["HttpHandlerSPWeb"] = web;

SPLimitedWebPartManager wpMgr = SPContext.Current.Web.GetLimitedWebPartManager(objFile.Url, System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared);
for (int i = 0; i < wpMgr.WebParts.Count; i++)
if (wpMgr.WebParts[i].GetType().FullName.ToUpper() == RemovedWebPartDll.ToUpper() && wpMgr.WebParts[i].Title.ToUpper() == RemovedWebParttitle.ToUpper())
string s = wpMgr.GetZoneID(wpMgr.WebParts[i]);
wpMgr.DeleteWebPart(wpMgr.WebParts[i] as System.Web.UI.WebControls.WebParts.WebPart);
newWP.Title = newWebPartTitle;
wpMgr.AddWebPart(newWP, s, 1);

catch (Exception ex)
Console.WriteLine("Error Occoured Message : " + ex.InnerException);

This code will replace the existing webpart from new webpart provided the new webpart is present in the GAC.
sounds very good :)

Tuesday, November 17, 2009

Caching with SPList-Sharepoint

In big sharepoint projects, it may be possible that performance of our application get reduce because of large amount of data stored in sharepoint list.In such scenario, calling Sharepoint list through sharepoint object model will harm the performance. caching can be useful in such scenario.
To implement caching we can use PortalSiteMapProvider class. The GetCachedListItemsByQuery() method of PortalSiteMapProvider Class to get the cached data.Below is the code snippet-
string strVal;
SPWeb web=SPControl.GetContextWeb(context);
SPQuery query = new SPQuery();
query.Query = "CAML Query";
PortalSiteMapProvider provider = PortalSiteMapProvider.CurrentNavSiteMapProviderNoEncodePortalWebSiteMapNode node = provider.FindSiteMapNode(web.ServerRelativeUrl.ToString()) as PortalWebSiteMapNode;
SiteMapNodeCollection items = provider.GetCachedListItemsByQuery(node, "List Name", query, web);
if (items != null)
foreach (PortalListItemSiteMapNode item in items) {
strVal = item["column name"].ToString();
Now strVal will get the cached data.
Alternatevely we can use HttpRunTime.cache, provided by ASP.Net, to implement caching in following way-
SPList lstStore;
SPListItemCollection items;
if (HttpRuntime.Cache["configstorelist"] == null)
lstStore = web.GetList(strlistPath);
HttpRuntime.Cache.Add("configstorelist", lstStore, null, DateTime.MaxValue, TimeSpan.FromMinutes(10),System.Web.Caching.CacheItemPriority.Default, null);
lstStore = (SPList)HttpRuntime.Cache["configstorelist"];
Here we will check if the Cache object is null or not. if it is null, it will call getList() method, else it will take the data from Cache object.One more point is to use GetList() method instead of SPList.Lists["name"] as SPList.Lists iterate through all the list so it may reduce the performance in large projects. So it is advisable to use GetList().

Monday, November 16, 2009

Accessing SPList-cross web application, sitecollection and site

There might be some scenario where we might have to access list across site collection. We might have a situation that we have to search for a list across a site collection. We may have to check whether the list is present in any of the site collection or not and if present we may have to do some operation on the list. Below is the code snippet that may be useful in this scenario-
private string GetListvalue()
string strVal=string.Empty;
Uri uri = new Uri(SPContext.Current.Web.Url.ToString());
SPWebApplication webApp = SPWebApplication.Lookup(new Uri("http://" + uri.Host));
if (webApp != null && webApp.Sites != null && webApp.Sites.Count > 0)
using (SPWeb web = SPContext.Current.Site.RootWeb)
foreach (SPSite site in webApp.Sites)
foreach (SPWeb web in site.AllWebs)
if (IfListExists(web, "List Name"))
SPList lstStore;
SPListItemCollection items;
lstStore = web.GetList(list path);
SPQuery query = new SPQuery();
query.Query = "CAML query";
items = lstStore.GetItems(query);
strVal = items[0]["Column name"].ToString();
return strVal;
Here I am accessing a sigle column from the first row that will be returned by the query.
The IfListExists() method will use Linq to check whether the list exists or not. Sharepoint don't have any method or properties to check the existance of the list. Another alternative is to use the Extension Methods. Below is the definition of the IfListExists() method-
private static bool IfListExists(SPWeb web, string listName)
return web.Lists.Cast().Any(list => string.Equals(list.Title, listName));

Thats it.
Hopes it will help. :)

Wednesday, July 29, 2009

Accessing multiple lists across multiple sites - SPSiteDataQuery

Sometimes, we may require to access multiple lists in multiple websites using a single query. Sharepoint provides two ways to achieve this-
1. joined subview using sharepoint designer ( click here to see)
2. Using SPSiteDataQuery class
SPSiteDataQuery class can be used to run CAML query across multiple lists in multiple websites within a site collection. It can be defined as-
SPSiteDataQuery query = new SPSiteDataQuery();
SPSiteDataQuery class returns a DataTable Class which can be used to access the data.
SPSiteDataQuery class has few properties which need to be set in order to use it. These properties are-

a. Lists- Can be used to specify list type, lists which has to be used in query through ListID, Maximum number of lists can be used in query etc. List Type is specified by BaseType attribute. The value of BaseType attribute will be
0 for a generic list
1 for a document library
3 for a discussion forum
4 for a survey
5 for a issues list.
The Lists property can be set as
query.Lists="<Lists BaseType="'0'/">";
If we want to query some specific lists only then we must specify their list ID as below-
<List ID=\"E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23\"/>
to get the list ID go to list setting page take the value of 'List' querystring from page URL. It will be like-

replace %7B with '{', %7D with '}' and %2D with a '-' and we will have the list ID as
The maximum number of lists that the query can use will be specified by MaxListsLimit attribute as below-
<Lists BaseType='0' MaxListsLimit='10'>

b. Webs Property- This will specify the scope of the query. It may have any of the three values-

1. SiteCollection- query will run through the whole site collection.
query.Webs = "<Webs Scope="'SiteCollection'/">";

2. Recursive- query will run within website and its child site.
query.Webs = "<Webs Scope="Recursive">";

3. Blank- query will run through current web only.
query.Webs = "<Webs Scope="'">";

c. ViewFields property- It can be used to specify the column we are going to display. It can be used a follow-
query.ViewFields = "<FieldRef Name='Department' Nullable='TRUE' /><FieldRef Name='desc' Nullable='TRUE' />"
If we set the value Nullable='TRUE' then the query will fetch the data even if the column has null value in the corresponding row.

d. Query property- It is used to specify the CAML query as below-
query.Query="<Where><Eq><FieldRef Name="'ID'"><Value Type=" 'Counter'"> 1</Value></Eq></Where>";

e. RowLimit- This property will limit the number of rows which has to be fetched.
It can be used as-
query.RowLimit=5; ( To limit the number of rows to 5)

Below is the sample code to use SPSiteDataQuery-

SPWeb web = SPControl.GetContextWeb(Context);

SPSiteDataQuery q = new SPSiteDataQuery();

query.Query = "<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Eq></Where>";

query.ViewFields = "<FieldRef Name='Department' Nullable='TRUE' /><FieldRef Name='desc' Nullable='TRUE' /><FieldRef Name='DepartmentLink' Nullable='TRUE' />";

query.Lists = "<Lists BaseType='0'><List ID=\"E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23\"/><List ID=\"B59FC80A-B586-4F31-A376-2FE20896641D\"/><List ID=\"3482EBF1-A2A7-4420%2DA920-09EA2635C557\"/></Lists>";

query.Webs = "<Webs Scope='SiteCollection'/>";

query.RowLimit = 10;

DataTable t = web.GetSiteData(query);
The DataTable will be populated by the data fetched from the list. It can be used to display the contents on the web page.