SharePoint Lists as Data Sources

One of the newer methods used as a data source are SharePoint Lists. SharePoint lists as data sources one of the newer methods of data sources is the SharePoint Lists. Although this type of data source removes the need of an available database for you reports, conversely, it removes the available of useful database features as well.  Connecting to a SharePoint lists is very easy. That is one of the advantages of using this type of data source. It’s typically an HTTP URL such as the following...

https://intranet.acmetools.com/_layouts/15/viewlsts.aspx

Though SharePoint lists have one of the simplest methods of connections, it also provides one of the most costly drawbacks in the inability to develop useful queries against the SharePoint lists. This becomes a bigger issue when you are using multiple SharePoint list in the report and data needs to be joined across different lists. Unfortunately, there is no natural JOIN syntax that you can use in the Visual Studio/Report Builder query designer that will allow you to join two different SharePoint Lists into one query/dataset. Therefore you must create multiple datasets and join them using the combination of the SQL Expression Language commands.

For example, if we had three different SharePoint Lists as follows...

In this example if we had to display the Customer with the Sales total, we would have to join the Sales List to the Location List and eventually to the Customer List. This is easy to do in the typical relational database with a simple join command.  Again, since we can join SharePoint lists within the query builder, we have to rely on the SSRS expression language for make these connections. To accomplish this we using the following SSRS functions LookupSet , Join and ReportItem syntax (since we must join a to a third list).

If we simply joining the Sales List to the Location List, all we have to do is use the LookupSet function as follows.

=Join(LookupSet(Fields!LocationID.Value, Fields!LocationID.Value, Fields!CustomerID.Value, “Location”)

If we must connect to the third Sharepoint Customers to get the Customer Name, we have to use the ReportItem expression. This is because you cannot use nested LookupSets in the SSRS Expression language. There use are required to then using the above command to create the CustomerID value to display in a Tablix, Matrix, or List, and then using the ReportItem to use that one of the values in another LookupSet Expression. So if we have already created a ReportItem for the CustomerID using the above expression, then we are using the following to pull in Customer name.

=Join(Lookupset(ReportItems!CustlocID3.Value,Fields!CustomerID.Value,Fields!LastName.Value,"Customers"))

In this expression we use the CustlocID3 that is the ReportItem name in the Tablix (You can find the name of the textbox in the general sections of the Tablix (or Matrix) properties.

Microsoft ability to allow SharePoint Lists as a data source adds some real advantages for enterprise customers. This is particular true is you need some centralized data source but the IT department won’t give out another DB license or access. SharePoint Lists has some great advantages but to get around some of the major drawbacks it will require you to use more of the SSRS expression language .

Becoming an IT professional

The IT industry offers excellent earning potential and presents very few barriers to entry. With average reasoning abilities, sound reading comprehension, and some patience, anyone can enter the field and become employable. Your success in the industry will largely depend on your ambition and area of specialty. As the industry continues to grow, there is a constant need for more IT professionals. While some believe that the market is saturated, my experience and the industry as a whole suggest otherwise. Any credible list of "Top Industries in the Future" should include IT (particularly programming) as a growth industry.

I have worked in the IT industry for over 20 years and have never seen a shortage of work or ideas that need implementing. To enter and thrive in this industry, there are four essential things to consider: get technical, avoid being non-technical, continue to learn, and learn to research.

Getting technical requires choosing a specialty and focusing on a set of technologies. Whether you decide on .Net programming, mobile development, system administration, or database development, your career path must have a focused approach. At this stage in my career, I am primarily focused on Microsoft Business Intelligence (MSBI), a growing and evolving technical area.

Avoid becoming an "administrative IT professional" by working only on IT processes instead of technology. You should understand IT processes, but focus on a specialty where you know the technology well enough to break it down at any level.

Continuing to learn is essential to staying marketable in the industry. Free technical discussion and learning sites, such as Microsoft Virtual Academy, and IT eBooks are excellent resources for continuing education. Reading books in your area of specialty with interest is essential.

Having your lab environment is crucial to practice, experiment and test. This is where your best learning will take place, allowing you to practice strategies, follow labs and exercises, and build the confidence needed to experiment in a safe environment. Virtual environments, such as VMWare or Hyper-V, are useful for creating a lab environment to practice your specialty.

In conclusion, IT is an evolving industry, and staying relevant requires continuous learning and skill improvement. Choosing an area that you are passionate about will make it easier to embrace continued learning. IT is a growth industry, and you can have a long and rewarding career if you are willing to evolve with it.

Using Alternating Row Colors for SSRS

Outside of the making sure that reports reflect the accuracy of the data, it’s also important to make sure the reports are easy to read. One of the most effective ways to make reports easy to ready is to ensure that the visibility of data aligns with the natural way people read data – left to right and top to bottom.

Using alternating row colors not only can make the report more visually pleasing, but also simply easier to read. There are several ways to accomplish this but the most methods would working using to common SSRS expressions: RunningValue and RowNumber. RowNumber is a bit simpler and requires less values but RunningValue is more reliable.

You can use RowNumber when you are using a simply Tablix (Table) where there is no Tablix Grouping defined. Tables are a popular method for displaying data. When you have rows with many columns (8 or more) you can make the tables easier to read by adding alternating color to the rows. Even without a lot of columns, alternative row colors can still make an impactful look on columns. For example, when we look at the following column…

This example using a column where there is no grouping. Therefore, we could simply using a RowNumber SSRS Expression. This requires specifying the textbox background using the following expression to generate alternative row colors… =iif(ROWNUMBER(nothing) MOD 2,"White","Gainsboro").

Again, this method works great when we are using a Tablix that does not use group or aggregations. When using grouping, I suggest you instead try RunningValue to accomplish alternating row colors because the row number are not often sequential when using grouping. In this case I have grouped the items by Conference and then allow drill down to the Institution category.

I would suggest then using RunningValue as the method to establish the alternative columns. You would then use the following method instead

=IIF(RunningValue(Fields!Resource_No_Name.Value,CountDistinct,Nothing) Mod 2,"Silver ", "White")

Whatever type of Tablix configuration that is being used, either RunningValue or RowNumber should be part of the SSRS expression use setting alternate row colors.