This week I was working on generating some reports using Excel 2013, Power Query, PowerPivot and then I wanted to publish them to SharePoint. Unfortunately our server wasn’t ready for it so I went through the process of installing PowerPivot for SharePoint (existing SharePoint) so my data connections in Excel workbooks (Data Models and PowerPivots) could be refreshed correctly.

Through the process of installing and configuring PowerPivot I encountered some problems and finally solved them all. Once again here are my founds as some keynotes and summary:

 

  1. Your SQL server (2012 in my case) must be at least SP1. Otherwise your DataModels when refreshing data connections could not be loaded and you’ll receive error prompt. More information
  2. A seperate instance of SQL server should be installed for PowerPivot. More information
  3. I’ve also installed Microsoft SQL Server 2012 SP1 PowerPivot for Microsoft SharePoint and followed the configuration as recommended.
  4. The service account for Excel Service Application pool should have execute permission on the Admin Content database. This could be acheived via PowerShell. Otherwise you’ll get error when loading dashboards in PowerPivot service application. Use the following PowerShell: More information

$url = “http://centraladmin“
$webApp = Get-SPWebApplication -Identity $url
$webApp.GrantAccessToProcessIdentity(“DOMAIN\serviceaccount”)5

  1. Also the Excel Services service account should be administrator of the PowerPivot Analysis service instance. More information

Hope this could be helpful.

Thanks

Navid

Advertisements

Hi

I was trying to configure send to connections in CA (SharePoint Server 2013) to be able to use content organizer to send documents to different site collections.

And encountered this error prompt each time I tested the link saying: Verification Failed: URL is not a valid routing URL

http://server/site Url/_vti_bin/officialfile.asmx

I was using the intranet public address for server part of “Send To URL” box (http://intranet.mydomain.co.uk/site Url/_vti_bin/officialfile.asmx) which was causing the error. So just using the internal server name (http://sharepointserver/site Url/_vti_bin/officialfile.asmx) solved the problem and also content organizer in my scenario started working fine.

Maybe in same scenario this may solve your problem.

Regards

Navid

Hello everyone

After investigating for about a week on an issue, I’m here to summerize and give you information about it.

Our platfrom is SharePoint 2013 and using office 2013 client applications. We have a root site collection and couple of host named site collections also.

If you need to use people and group fields in Document Infomation Panel or as people picker in InfoPath forms, maybe you have seen that your InfoPath form or Word document stops working and it may hang or carsh.

By searching internet you will come up with some ideas that you should modify the web.config file and set attribute MultipleSiteBindingEnabled to True.

Something like this:


<system.serviceModel>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />
</system.serviceModel>

However I found that this modification could have lots of annoying side effects and is not working properly. Although depends on your configuration. I recommend don’t do it!  It can cause the drag and drop functionality on document libraries stop working, it can cause the internal people and group fields (for example for lists) stop searching the directory and it can cause SharePoint designer to not be able to connect to your sites.

What I found instead was quite simple suprisingly. We had used manully binding into IIS which I found is not correct. To summerize and cut a long story short, I found:

  1. There is no need to modify the web.config file for adding multiple site bindings attribute
  2. And more importantly never ever manually add site binding into the IIS console

 

Just following this two instructions everything works like a charm in our farm.

Hope this helps.

Navid 🙂

 

 

 

Showing multiple columns in content query web part was what I was working on this week in our SharePoint 2013 environment.

This post is a quick summery of what I learned through the whole work.

  1. My platform is SharePoint 2013
  2. I’m using content type syndication, so the hub is another site collection and in a second site collection I’m going to use CQWP based on a specific content type with its own site columns
  3. Step1, to know what itemstyle.xsl is and how it works. then customize it
  4. step2, to know what ContentQueryMain.xsl is and how it works, then customize it
  5. A trick to insert CQWP on your SharePoint web page

An Important note:

Unlike SharePoint 2007, there’s no need to export your web part and modify it when your SharePoint is 2010 or 2013. You just need to custmize itemstyle.xsl and in some cases (like showing as a table) the ContentQueryMain.xsl

Cheers to Waldek Mastykarz based on this resource.

 

to customize itemstyle.xsl, better to first try it simply and add your fields and check if it works and then customize it to view fields as you want. So create a template:


<xsl:template name="ProjectsReport" match="Row[@Style='ProjectsReport']" mode="itemstyle">
</xsl:template>

 

To add fields that should be set in the webpart:


<xsl:value-of select="@ProjectName" />

<xsl:value-of select="@ProjectClient" />

<xsl:value-of select="@StatusSummary" disable-output-escaping="yes" />
...

Note: I found that you should not use space in your template name or any of xsl select values. In my case my column names was same as these xsl fields.

In this point you should be able to add content query webpart to a simple wiki page and set the required fileds.

CQWP after customizing itemstyle.xsl

CQWP after customizing itemstyle.xsl

You are almost done. In this case you just need to set the style of viewing your data in itemstyle as you want.

But if you need to show them as a table there a good article here about it. I won’t copy it here so have a look there.

and my final custom code is like this. I tried to use SharePoint light banded rows style to have a better view. Don’t forget there’s a little customize for ContentMainQuery.xsl as mentioned above.


<xsl:template name="ProjectsReport" match="Row[@Style='ProjectsReport']" mode="itemstyle">
    <xsl:param name="CurPos" />
    <xsl:param name="Last" />
   
 <xsl:variable name="tableStart">
      <xsl:if test="$CurPos = 1">
       <![CDATA[
        <table width="100%" class="ms-rteTable-6" cellpadding="1" cellspacing="0">
          <tr class="">
            <th class="">Name</th>
            <th class="">Client</th>
            <th class="">Status Summary</th>
            <th class="">Project Phase</th>
            <th class="">This Reporting Period</th>
            <th class="">Next Reporting Period</th>
            <th class="">Key Issues and Risks</th>
          </tr>]]>  
      </xsl:if>
    </xsl:variable>
 
    <xsl:variable name="tableEnd">
      <xsl:if test="$CurPos = $Last">
        <![CDATA[ </table> ]]>
      </xsl:if>
    </xsl:variable>

    <xsl:variable name="rowStart">
        <xsl:if test="$CurPos mod 2 = 0">
            <![CDATA[
              <tr class="ms-rteTableEvenRow-6">
            ]]>
        </xsl:if>
        <xsl:if test="$CurPos mod 2 != 0">
            <![CDATA[
              <tr class="ms-rteTableOddRow-6">
            ]]>
        </xsl:if>
    </xsl:variable>
    <xsl:variable name="rowEnd">
               <![CDATA[
              </tr>
            ]]>
    </xsl:variable>
    <xsl:value-of select="$tableStart" disable-output-escaping="yes"/>
    <xsl:value-of select="$rowStart" disable-output-escaping="yes"/>
        <td>
            <xsl:value-of select="@ProjectName" />
        </td>
        <td>
            <xsl:value-of select="@ProjectClient" />
        </td>
        <td>
            <xsl:value-of select="@StatusSummary" disable-output-escaping="yes" />
        </td>
        <td>
            <xsl:value-of select="@ProjectPhase" />
        </td>
        <td>
            <xsl:value-of select="@ThisReportingPeriod" disable-output-escaping="yes" />
        </td>
        <td>
        <xsl:value-of select="@NextReportingPeriod" disable-output-escaping="yes" />
        </td>
        <td>
            <xsl:value-of select="@KeyIssuesRisks" disable-output-escaping="yes" />
        </td>
    <xsl:value-of select="rowEnd"/>
    <xsl:value-of select="$tableEnd" disable-output-escaping="yes"/>

</xsl:template>

 

1 more trick!

I found that in case of using site content types and site columns (maybe content type syndication) and then querying from multiple sub-sites in the subscriber site collection, at first I should connect the CQWP to one the lists directly in any of sub-sites using third option as the “Source”. Then when it connected succefully and “fields to display” were set correctly, I changed the source to the whole site collection. Setting the whole site collection at first was not working for me and the webpart was giving me an error and fields couldn’t be set.

CQWP2

And here is what I acheived finally:

Querying multiple sub-sites using content query web part

Querying multiple sub-sites using content query web part

 

Some other useful blogs that I reviewed are also this and this.

Hope this could be helpful.

Navid

I was designing a content page based on a page layout in a publishing site.

Usually I won’t direct users to the NewItem.aspx page or the List or Library directly. The way I provide them to create a new item in my current project is usually by customizing the list from with InfoPath and then using an InfoPath form web part in a publishing page. To be honest that’s an interesting and cool way and I’m happy with that. I do like the publishing feature of SharePoint !!!

However what if you like to open a NewItem form as a dialog directly from a content page. I found some articles like this and a good one here and thanks Baris Bikmaz about the post. You can read them for more information. Here is the summary for my case:

I used a HTML web part and customize the code like this.

<a href="javascript:NewItem2(null, '/<strong>YourSiteAddress</strong>/_layouts/listform.aspx?PageType=8&amp;ListId={<strong>YourListID</strong>}&amp;RootFolder=')">
<img alt="" src="Simple Image Address Which I used Instead of a Text Link" />
</a>

Page type 8 means New form Dialog.

Image tag is optional and you can use a simple text instead.

The good thing about this was that when I customized the list new item form with InfoPath, I wasn’t sure if it would open customized or not. and after testing It worked ! the dialog not only opened but also was based on customized infoPath ! Great! 🙂

Yesterday we needed to have full screen background on our SharePoint sites and their pages. After lots of searching and testing different approaches, I found the following solution which is based on jQuery, the most suitable one for us. We couldn’t use the CSS 3 techniques since in my opinion using CSS3 in SharePoint 2010 is not recommended and has a lot of side effects. especially for internal use like Intranet and portal which main objectives are collaboration. I have previously published a post here about CSS3 and SharePoint 2010 which usually case some parts like ribbon or etc start function correctly.

I found my solution here but I changed it a little. Instead of using “$(window).load” , I used “$(document).ready” , so it will help that the image to be re-sized much sooner. According to this blog, if you want to hookup your events for certain elements before the window loads, then $(document).ready is the right place.

Here is the summary of my solution:

  • Needed styles to be attached to the master page:

#bg {
position: fixed;
top: 45px; /* image to start after ribbon */
left: 0;
z-index:-9999
}
.bgwidth {
width: 100%;
}
.bgheight{
height: 100%;
}
  • Needed jQuery (Don’t forget to link to a proper jQuery library!):

$(document).ready(function() {

var theWindow = $(window),
    $bg = $("#bg"),
    aspectRatio = $bg.width() / $bg.height();

function resizeBg() {

if ( (theWindow.width() / theWindow.height()) < aspectRatio )
   {
    $bg
    .removeClass()
    .addClass('bgheight');
   }
   else
   {
    $bg
    .removeClass()
    .addClass('bgwidth');
   }
}
theWindow.resize(resizeBg).trigger("resize");
});

  • Also this tag should be added right after “<div id=”s4-bodyContainer”>” tag in the master page

<img src="../PublishingImages/Background.jpg" id="bg" alt="">

src” is the relative address of the background file for each site. so you can place your background file there.

Good luck ! 🙂

This request was came up to us when we needed a picture library that every time users should be able to upload their own images to it without overwriting the others images. The problem was “overwrite existing files” checkbox.

There is a solutions for this issue (not useful in my opinion) which is about modifying the “upload.aspx” page on the server (_layouts folder). However it will affect farm scope. we need it just for a specific library.

I found a solution based on jQuery. I’m not very expert in terms of coding and JavaScript, however I tried my own way and made a simple code after some hours! 😀 This workaround helped us. So I’m gonna share it with you here and hope that would be helpful for you too. Here is the summery of my workaround:

  1. Find if the prompt or page (upload) is related to our list (line 4)
  2. Find the check box and un-check it (line 6-7)
  3. Hide the section and also its description (line 8)

Here is the code:

$(document).ready(function()
	{
	var form = $('form');
 	if ( form.attr('action').indexOf('YOUR 36 character List ID - Without Braces') > -1 )
	 	{
	 	var checkbox = $('#ctl00_PlaceHolderMain_UploadDocumentSection_ctl03_OverwriteSingle');
	 	checkbox.prop('checked',false);
	 	checkbox.parent().hide();
	 	var multiple = $('#ctl00_PlaceHolderMain_UploadDocumentSection_ctl03_UploadMultipleLink');
	 	multiple.hide();
	 	}
	}
);

Note:

  • on line 4 you must replace the ListID with you own Picture library ID or document library ID
  • Put this script in your default master page also link to jQuery file