SharePoint & InfoPath: Analyzing Data 

  • By IncWorx Team
  • 16 Aug, 2017
Learn how to analyze data with SharePoint and Infopath using Power BI or Power Query in this step by step instructional post.

System requirements:

  • SharePoint Server; SharePoint Online
  • Excel 2010 and beyond Or Power BI
  • InfoPath Designer

Despite Microsoft's attempts to retire InfoPath from the newest versions of SharePoint, the form designer tool persists and is still widely used across SharePoint sites.

As we all know, its popularity is due to the ease of use and the frequent need to present e-forms with built-in intelligence without code.

Repeating tables are one of the popular features of InfoPath. The challenge however is to extract the content of the tables for reporting and analysis.

This can be achieved using Power Query or Power BI and the form library as data source.

The example InfoPath form below contains a repeating table or repeating section.

When publishing the form to the SharePoint library, using the publishing wizard, the repeating table fields should be published as merged columns of the form library. You should only add the columns that will be required to create the required report. Our goal here is to create a report, graphic or otherwise in Excel or Power BI that will look like this using all 3 columns of our repeating table.
Publish all 3 columns using the setting shown below.
Once published, the data contained in the repeating table will be shown as in the picture below.

In order to analyze the data as single separate entries, we need to create a connection to the document library using Power Query.

Once connected to the SharePoint Library in Power Query, we need to split the merged values in the Item Name, Number and item Classification columns into separate rows.

We add 3 new columns using the following formula in a new column of type table using the Text.Split function:
This formula will create the following custom column:
The next step in the query is to expand the custom column we just created:
After expanding the column, each entry is added as a row:
    The last step is to clean the query by deleting the original columns and any other steps required to obtain the query needed for the report.

      The IncWorx SharePoint Consulting Blog

      By IncWorx Team 04 Dec, 2017
      In an age of limitless information at our fingertips, I’m often frustrated as I try to weed through it all just to find the nuggets I’m looking for.
      By IncWorx Team 04 Dec, 2017

      For the last decade, SharePoint deployments have consisted of on-premise deployments which require many servers, licenses, and ongoing care and feeding. In this model there were typically one or more administrators or developers that were responsible for ensuring the farm was healthy. This typically equated to server farms that had not been patched in years or had any maintenance done whatsoever. It was just easier to leave those servers alone because nobody really knew much about SharePoint.

      With the creation of the 365-application stack and movement of applications like Exchange and SharePoint started occurring, so did the start of the changing support landscape.

      Fast forward to 2017 and cloud adoption has skyrocketed and the 365 stack has been maturing and adding functionality rapidly. As some of our clients have transitioned to Office 365 in varying capacities they have asked the question “Do I really need a support contract since I moved to the cloud?”

      The answer to this is a resounding “YES”.

      A common thought is since I don’t have the servers to be managed and maintained then there is really nothing more to support, I just use the product. Although the back-end administration and management has largely gone away, there Is still a need for product expertise for you to implement the business process, reporting, automation, and other functionality that will provide true ROI for your investment in the platform.

      This is a huge benefit to our clients and can be as well for your organization. All those hours that used to be spent patching, monitoring, and maintaining your SharePoint environment can now be spent working with the business to gather requirements and developing longer term strategy for usage of the 365 platform and applications that will deliver the required business functionality.

      It is exciting to see the tremendous benefits for our clients in this shift to cloud services such as Office 365, Azure, and AWS. If you are contemplating a move to the cloud and have additional questions reach out and we will be happy to assist.

      By IncWorx Team 07 Nov, 2017

      In this DIY Post, learn how to display dynamic information on a SharePoint workflow initiation form with SharePoint Lists, rather than InfoPath.

      Environments: SharePoint 2013 and SharePoint O365

      Scenario: You need to display dynamic information on a SharePoint workflow initiation form.

      Solution: Utilize an OOTB SharePoint list to be used as your workflow initiation form instead of using the InfoPath option to customize the OOTB designer workflow form.

      Description: I’ve ran into situations where the requirements dictated that dynamic information be displayed in SharePoint (not via email) when a workflow is being initiated.  This post will describe how to use a “supporting” custom SharePoint list to display the initiation form information and trigger the workflow against the “target” list.  This solution only uses client side technologies: jQuery and SharePoint’s CSOM.


      Step 1 – Create your SharePoint List That Requires a Workflow

      Create a new SharePoint list that will ultimately have the workflow ran against. Here is the example list that I am using for this post:

      List name = “RandomApproval”

      By IncWorx Team 06 Nov, 2017

      There is a known bug in SharePoint (2013 & 2016) Distributed Cache when using AppFabric pre-CU4. These issues can be fixed by applying the latest AppFabric CU and enabling the background garbage collection feature. It is also recommended to make the changes to the Distributed Cache and STS configs that we have outlined in our process.

      Since Distributed Cache is involved in caching credentials in a SharePoint environment it is not surprising to find out that a misconfiguration of this cache can cause a wide variety of issues that can be difficult to pinpoint. In our experience we have seen this issue manifest itself in the following ways.

      Primarily, in ULS, we see the following errors:

      Product: SharePoint Foundation

      Category: DistributedCache

      Level: Unexpected

      Message:

      Unexpected error occurred in method 'GetObject' , usage 'Distributed Logon Token Cache' - Exception 'Microsoft.ApplicationServer.Caching.DataCacheException: ErrorCode<errca0018>:SubStatus<es0001>:The request timed out..

      Additional Information : The client was trying to communicate with the server : net.tcp://cacheserver.example.com:22233

       at Microsoft.ApplicationServer.Caching.DataCache.ThrowException(ResponseBody respBody, RequestBody reqBody)

       at Microsoft.ApplicationServer.Caching.DataCache.InternalGet(String key, DataCacheItemVersion& version, String region, IMonitoringListener listener)

       at Microsoft.ApplicationServer.Caching.DataCache.<>c__DisplayClass49.<get>b__48()

       at Microsoft.SharePoint.DistributedCaching.SPDistributedCache.GetObject(String key)'.</get></es0001></errca0018>

       

      Product: SharePoint Foundation

      Category: DistributedCache

      Level: Medium

      Message:

      Unexpected error occurred in method 'GetObject' , usage 'Distributed Logon Token Cache' - Exception 'Microsoft.ApplicationServer.Caching.DataCacheException: ErrorCode<errca0017>:SubStatus<es0006>:There is a temporary failure. Please retry later. (One or more specified cache servers are unavailable, which could be caused by busy network or servers. For on-premises cache clusters, also verify the following conditions. Ensure that security permission has been granted for this client account, and check that the AppFabric Caching Service is allowed through the firewall on all cache hosts. Also the MaxBufferSize on the server must be greater than or equal to the serialized object size sent from the client.).

      Additional Information : The client was trying to communicate with the server : net.tcp://cacheserver.example.com:22233  

       at Microsoft.ApplicationServer.Caching.DataCache.ThrowException(ResponseBody respBody, RequestBody reqBody)  

       at Microsoft.ApplicationServer.Caching.DataCache.InternalGet(String key, DataCacheItemVersion& version, String region, IMonitoringListener listener)  

       at Microsoft.ApplicationServer.Caching.DataCache.<>c__DisplayClass49.<get>b__48()  

       at Microsoft.SharePoint.DistributedCaching.SPDistributedCache.GetObject(String key)'.</get></es0006></errca0017>

      Product: SharePoint Foundation

      Category: DistributedCache

      Level: Medium

      Message:

      Token Cache: Failed to get token from distributed cache for '0#.f|provider|username'.(This is expected during the process warm up or if data cache Initialization is getting done by some other thread).

      Exception: 'Microsoft.SharePoint.DistributedCaching.SPDistributedCacheClientRequestTimeOutException: Communications with the cache cluster has experienced a delay past the timeout value,please increase the RequestTimeout of the client. ---> Microsoft.ApplicationServer.Caching.DataCacheException: ErrorCode<errca0018>:SubStatus<es0001>:The request timed out..

      Additional Information : The client was trying to communicate with the server : net.tcp://cacheserver.example.com:22233  

       at Microsoft.ApplicationServer.Caching.DataCache.ThrowException(ResponseBody respBody, RequestBody reqBody)  

       at Microsoft.ApplicationServer.Caching.DataCache.InternalGet(String key, DataCacheItemVersion& version, String region, IMonitoringListener listener)  

       at Microsoft.ApplicationServer.Caching.DataCache.<>c__DisplayClass49.<get>b__48()  

       at Microsoft.SharePoint.DistributedCaching.SPDistributedCache.GetObject(String key)     -

       -- End of inner exception stack trace ---  

       at Microsoft.SharePoint.DistributedCaching.SPDistributedCache.GetObject(String key)  

       at Microsoft.SharePoint.IdentityModel.SPDistributedSecurityTokenCache.GetObject(String key)  

       at Microsoft.SharePoint.IdentityModel.SPTokenCache.TryGetCachedToken(String cacheKey)'.</get></es0001></errca0018>

       

      Product: SharePoint Server Search

      Category: QueryCache

      Level: Unexpected

      Message:

      SearchDistributedCache::PutAction() - Failed due to exception = 'Microsoft.Office.Server.DistributedCaching.SPDistributedCacheClusterDownException: Cache cluster is down, restart the cache cluster and Retry ---> Microsoft.ApplicationServer.Caching.DataCacheException: ErrorCode<errca0017>:SubStatus<es0006>:There is a temporary failure. Please retry later. (One or more specified cache servers are unavailable, which could be caused by busy network or servers. For on-premises cache clusters, also verify the following conditions. Ensure that security permission has been granted for this client account, and check that the AppFabric Caching Service is allowed through the firewall on all cache hosts. Also the MaxBufferSize on the server must be greater than or equal to the serialized object size sent from the client.).

      Additional Information : The client was trying to communicate with the server : net.tcp://cacheserver.example.com:22233

       at Microsoft.ApplicationServer.Caching.DataCache.ThrowException(ResponseBody respBody, RequestBody reqBody)

       at Microsoft.ApplicationServer.Caching.DataCache.InternalPut(String key, Object value, DataCacheItemVersion oldVersion, TimeSpan timeout, DataCacheTag[] tags, String region, IMonitoringListener listener)

       at Microsoft.ApplicationServer.Caching.DataCache.<>c__DisplayClass25.<put>b__24()

       at Microsoft.ApplicationServer.Caching.DataCache.Put(String key, Object value, TimeSpan timeout)

       at Microsoft.Office.Server.DistributedCaching.SPDistributedCache.Put(String key, Object value)

       --- End of inner exception stack trace ---

       at Microsoft.Office.Server.DistributedCaching.SPDistributedCache.Put(String key, Object value)

       at Microsoft.Office.Server.Search.Query.SearchDistributedCache.PutAction(String key, Object value)'</put></es0006></errca0017>

       

      Product: SharePoint Server Search

      Category: QueryCache

      Level: Unexpected

      Message:

      DistributedSearchResultsCache::Get() - Failed due to exception = 'Microsoft.Office.Server.DistributedCaching.SPDistributedCacheClusterDownException: Cache cluster is down, restart the cache cluster and Retry ---> Microsoft.ApplicationServer.Caching.DataCacheException: ErrorCode<errca0017>:SubStatus<es0006>:There is a temporary failure. Please retry later. (One or more specified cache servers are unavailable, which could be caused by busy network or servers. For on-premises cache clusters, also verify the following conditions. Ensure that security permission has been granted for this client account, and check that the AppFabric Caching Service is allowed through the firewall on all cache hosts. Also the MaxBufferSize on the server must be greater than or equal to the serialized object size sent from the client.).

      Additional Information : The client was trying to communicate with the server : net.tcp://cacheserver.example.com:22233

       at Microsoft.ApplicationServer.Caching.DataCache.ThrowException(ResponseBody respBody, RequestBody reqBody)

       at Microsoft.ApplicationServer.Caching.DataCache.InternalGet(String key, DataCacheItemVersion& version, String region, IMonitoringListener listener)

       at Microsoft.ApplicationServer.Caching.DataCache.<>c__DisplayClass49.<get>b__48()

       at Microsoft.Office.Server.DistributedCaching.SPDistributedCache.GetObject(String key)

       --- End of inner exception stack trace ---

       at Microsoft.Office.Server.DistributedCaching.SPDistributedCache.GetObject(String key)

       at Microsoft.Office.Server.Search.Query.SearchResultsDistributedCache.Get(String key)'</get></es0006></errca0017

       

       

      Resolution:

      Step 1

      Modify the Distributed Cache Logon Token Cache settings by using PowerShell to run the following commands:

      Get-SPDistributedCacheClientSetting -ContainerType DistributedLogonTokenCache

      $DLTC = Get-SPDistributedCacheClientSetting -ContainerType DistributedLogonTokenCache

      $DLTC.maxBufferPoolSize = "1073741824"

      $DLTC.maxBufferSize = "33554432"

      $DLTC.requestTimeout = "3000"

      $DLTC.channelOpenTimeOut = "3000"

      $DLTC.MaxConnectionsToServer = "100"

      Set-SPDistributedCacheClientSetting -ContainerType DistributedLogonTokenCache $DLTC

      Restart-Service -Name AppFabricCachingService

      These settings can only be changed using PowerShell and must be run on all the distributed cache servers in the cluster. This will virtually eliminate the cache timeout issues.

      Step 2

      Modify the Security Token Service cache settings by using PowerShell to run the following:

      $sts = Get-SPSecurityTokenServiceConfig

      $sts.MaxServiceTokenCacheItems = "1500"

      $sts.MaxLogonTokenCacheItems = "1500"

      $sts.Update()

      This will also need to be run on all the distributed cache servers.

      Step 3

      Download and install the latest AppFabric CU from Microsoft. This will need to be installed on all the servers in the farm that are hosting Distributed Cache.  

      Step 4

      Modify the DistributedCacheService.exe.config file on all servers to fix the issue with the background garbage collection process. This file can be found in the \Windows\System32\AppFabric folder. Add the following line to the config file between the </configSections> tag and the <dataCacheConfig> tag.

      <appSettings><add key="backgroundGC" value="true"/></appSettings>

      Step 5

      Restart the AppFabric Windows service and the Distributed Cache SharePoint service and run an IISRESET on all machines. I prefer to do a full farm reboot just to be safe and to avoid having to restart the services manually on each server. Depending on the number of servers in the environment, this can be a big time-saver.

      This should fix the issues and clear up the errors filling up ULS.

      I have created a couple basic scripts for making the token cache changes. I would recommend saving the PowerShell commands in step 1 and step 2 as scripts to use in your own environments. These will save a good bit of time compared to making all the changes manually. I am also working on a script to make the changes to the Distributed Cache config file. Once I have this completed I will add it to the instructions

      Conclusion:

      It’s important to keep up with updates to all of the Microsoft technologies that are incorporated within SharePoint Administration. Occasionally, Microsoft will find a bug in a previously released version of one of these technologies and the sooner it is addressed the better. These bugs can be anywhere on the spectrum from simple functionality tweaks to serious security vulnerabilities. Although this particular bug was resolved with AppFabric CU5, it is highly recommended to stay up to date with all of the current MS updates. At the time this article was published, the current AppFabric CU available is CU8. It can be downloaded here: https://www.microsoft.com/en-us/download/details.aspx?id=54440 . Following the instructions in this blog and staying up to date with all of the AppFabric CUs will keep your Distributed Cache implementation functioning at a high level and will eliminate the flood of errors that can show up in ULS and the event logs.

      By IncWorx Team 04 Oct, 2017

      If you’re like many companies looking at migrating some or all of your on-premises SharePoint footprint to SharePoint Online then you undoubtedly have realized it’s not as simple as saying a few magic words and ‘voila’, all your sites and content have been transformed into the cloud.

      There are a several planning steps that should be performed to minimize the challenges and headaches post-migration. Let’s go over ‘4 Planning Steps to a Successful SharePoint Online Migration’.

      In future blog posts, we’ll dive technically deeper into tools, scripts, or processes that can be used to simplify each step.

      
      By IncWorx Team 22 Sep, 2017

      If you are implementing SharePoint in your organization for the first time, or you are planning to upgrade your current SharePoint environment, you have likely questioned whether to use Office 365 and SharePoint Online versus an on-premises installation of SharePoint Server. To make the decision easier, below are five important factors to consider:

      By IncWorx Team 16 Aug, 2017
      Learn how to analyze data with SharePoint and Infopath using Power BI or Power Query in this step by step instructional post.
      By IncWorx Team 27 Jul, 2017

      SharePoint support isn’t only about fixing something that's broken, in fact, it is more about pro-active monitoring, training, development, and demonstrating how to use SharePoint. When you connect with a partner that provides a holistic approach to support, you will see what a big difference it can make. SharePoint Support should be available when you need it most or when you just have a quick question. With the right support your organization can benefit immediately.  Here are 5 major signs you are in need of expert SharePoint support services:

      1. Still sending attachments by email? This very popular way of sharing documents, while familiar, is no longer considered a good practice and with SharePoint sharing and collaborating on documents is a powerful core feature, making sending documents by email a thing of the past.

      2. Feeling like you’re on an island?  In many organizations there is that one person you can count on to answer your technical questions, but what happens when that person isn’t available or doesn’t exist?

      3. Is time really money? when your time is being spent trouble shooting technology or trying to figure out how to use SharePoint what are you gaining? You are valuable for the role you play in your organization, technologies like SharePoint exist to support and increase your value not slow you down.

      4. Are you using SharePoint like a shared drive? Similar to sending emails as attachments, using SharePoint like a shared drive also remains popular. Making folders to organize documents is the way things used to get done, with a little training and support the powerful document management capabilities built into SharePoint will take document management and sharing to the next level.

      5. How do I...? With any new technology, we all feel a little lost. Grasping the concept of SharePoint can be a challenge, but once you figure it out the opportunities to create value for you, your team and your organization are endless but how do you get there?


      By IncWorx Team 27 Jul, 2017

      SharePoint is an incredibly powerful tool within the Microsoft stack, that when implemented correctly, can streamline and solve many business problems. The stumbling block that many people run in to is that working with the platform requires a fairly specialized skillset, and a general ‘IT’ background doesn’t necessarily translate in to being able to pick up and learn SharePoint and begin optimizing your entire company right out of the gate. In fact, given the complexities of the platform and the various other Microsoft technologies that it touches, it can take a very very long time to become an expert. If you have all of the time in the world to set aside and learn SharePoint, stop reading, but if you have many other things to do and are looking to accelerate your organization, here are 3 key ways to leverage a SharePoint Consultancy to take things to the next level.

       

      1)    Business Analysis and Roadmapping

      As mentioned, SharePoint is a massively complex platform that does so many things that can add value. Think of it as a deluxe Swiss Army knife. The majority of people out there only use the knife (document storage), but don’t use many of the other tools because they’re not 100% certain of how/where to use them. A good SharePoint Consultant can come into your organization and meet with key business units and have non-technical discussions to understand what they do, how they do it, and what they struggle with. This is where the “I know SharePoint does this, but I don’t know how” comes in to play. A consultant can bridge that gap for you and identify key areas that you know are pain points and roadmap a solution. Not only that, they can also identify other areas that SharePoint is REALLY good at, that can empower your team even more and help you understand how to get there.

       

      2)    Regular Care and Feeding

      Time is the most valuable thing to most everyone. No one has enough of it, especially not IT people. There are countless things on your plate each and every day and new things that pop up out of nowhere. Given the complexities of SharePoint, it is a machine that will take care of you if you take care of it.  The problem is, taking that daily vitamin is hard to follow through on and remember when you’re running around with your hair on fire every day. Most people get anxiety about wondering when their SharePoint farm is going to break if they don’t have the time to spend on the regular care and feeding.  Unfortunately, this area is really low visibility, and often times falls lower on the priority list than keeping business units happy. Outsource this to someone like an IncWorx SharePoint Consultant, and stop worrying about it - focus on adding value to your business. You will thank us later when you do!

       

      3)    System Consolidation

      I have mentioned a few times that SharePoint is really complex, there isn’t enough time in the day, and IT people run around with their hair on fire. One way to take advantage of SharePoint’s massive set of features and functionalities and alleviate the time/stress issues associated with day-to-day IT life is to reduce the number of systems that you’re running in your business. More moving pieces equals more things that can break and more phone calls that you get while you’re on vacation. SharePoint does so many things, if you work with a knowledgeable SharePoint consultant on a consolidation strategy, you will likely identify a number of various systems that can be replicated and replaced by SharePoint using out-of-the-box features and functionality. This means if you work with someone good, and have a really good handle on proper planning, maintenance, and support, you have a singular item to worry about. Essentially, if you follow step 1, implement step 2, and then move to step 3 – your general quality of life as an IT pro will be ridiculously better! P.S. IncWorx SharePoint consultants can help you with all of the above.

      By IncWorx Team 21 Jul, 2017

      This year, more than any I can remember, we seen an extraordinary amount of headlines like this:

      “World’s biggest cyberattack sends countries into ‘disaster recovery mode’”-5/14/17 CNN

      Or statistics like this from Steve Morgan, Editor-In-Chief at Cybersecurity Ventures, “Cyber crime damage costs to hit $6 trillion annually by 2021."

      If that isn’t enough to send chills up any tech employees spine, I don’t know what is. So what are some best practices?

      Authentication
      Use more than one requirement to log on. Sometimes as simple as 2 factor authentication (something you have and something you know) can go a long way.

      Secure Tools & Networks
      Utilize a tool like SharePoint to share documents. It is much easier and safer to utilize a closed network.
      Engage an expert to manage your settings. As simple as this sounds, there are many companies that I have encountered that have capabilities within SharePoint to better secure themselves that either do not know they have them, or do not know how to properly use them.

      Updates
      Keep up to date with any updates or patches that a company like Microsoft puts out. As hackers change their tactics, company gatekeepers need to know what threats are out there.

      Credentialing 
      Make sure your internal staff has the correct credentials. Often times we see SharePoint environments where too many people have admin access and are making changes that shouldn’t occur, making the environment vulnerable. With the correct provisioning, this can easily be avoided.

      These are just some basic examples that everyone should be looking at. Unfortunately, in my business I hear far too often that the client either doesn’t have the expertise, or time to run specific checks and patches on a daily basis. If that sounds familiar, I would highly recommend reaching out to IncWorx and scheduling a quick call to see how we can help in any given situation. This is what we do all day everyday and we would be happy to discuss how to get your systems secure before the next breach happens. 

      More Posts
      Share by: