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.

Creating YTD Calculation in SSRS

Recently I wrote on a method to create YTD calculations using MDX without making modification to the SSAS cube. However, there will often be scenarios where you need an YTD calculation and you are not using a cube as a data source. In these cases you are at the mercy of making an YTD calculation using SQL. For those that have done this before, you know how difficult that can be and how much code you have to write to get that working.
You can creating this using a combination of a Tablix, Matrix, or a Chart object. The required SSRS Expression needed is RUNNINGVALUE. The example of this expression is as follows…

= RunningValue(Fields!Total_Cost.Value,Sum,"Chart3")

Here is an example of using RunningValue to accumulate YTD values…

 

  Using this example, I specific the following formula in the Tablix for the YTD…

 

 The same could be done for SSRS Matrix and Chart objects as well.

YTD Calculation in Query Designer

One of the most common requested aggregations in MDX is YTD. There are many sources that highlight how to do this in the Cube development environment, but sometimes you don’t have access to the SQL Data Tools / BIDs.

I find using the native YTD calculation problematic when attempting to make YTD calculation outside of the Cube development environment. The YTD function can be extremely slow to process outside of the Cube development environment.  Also, if you’re like me many times you only have access to read the cube but not develop against it. You should then you use the Query Builder to create an YTD calculation.

Basically use the Calculated Member Builder in the Query Designer (whether in Report Builder or Visual Studio).

In my example, I use the following data to create the YTD calculation against the Date Hierarchy PostingPeriod.Fiscal and the Measure.LaborCost.

Aggregate

 (
 PeriodsToDate
 (
[Posting Period].[Fiscal].[Fiscal Year]
 , [Posting Period].[Fiscal].CurrentMember
 )
 ,[Measures].[Labor Cost]
 )

 This how looks in the Calculated Member in Query Designer…

  This is one of the most useful features of the Calculated Member tool – allowing you to create dynamic aggregations without cube authoring..

 

Using SSRS to match colors

There are times when you have to match RGB colors in SSRS to Hex values that are defined by the business requirements. I save myself a lot of time by using websites that do this conversion for you. Here is one site to convert Hex to RGB and vice versa.

http://www.rgbtohex.net/hextorgb/ OR http://www.rgbtohex.net/

Simple but effective.

Getting the exact color

Sometimes you need the exact color codes (RGB or Hex) to match incoming graphics with SSRS colors. I would suggest that if you are not provided with company colors schemes already in this format (RGB/Hex), use the standard colors in SSRS in then work from there.

1.      Select the color you want. Do this by picking any object (text, rectangle, background, etc.) and Select properties. Choose the appropriate object to needs a specific color and then expression. In this example, I’m using Khaki. Select Expression…

2.      Under the Expression Dialog box, you will see the option More Colors in the Value Box. Select More Colors. This will bring you to the Select Color…

 

3.      Select the Color Circle Bar, move it to the top position. And this will give you the Color Sample “Khaki” and another split color for you to manipulate. We can record the RED, GREEN,BLUE color code for Khaki and use that to color object that need to match SSRS objects.

 

NOTE: Where trying to match graphics from incoming applications into SSRS, try using the PNG graphic format instead of JPG. PNG is a good choice for storing iconic graphics in SSRS and often has zero loss of quality regardless of the sizing (which should be small in SSRS). I use this all the time and am never disappointed when importing graphics of this type into SSRS.

Tabular Processing Failures

Tabular cube development is great. But like any other part of MSBI, you 'll run into issues from time to time. Recently I generated the following error while attempting to process a tabular cube....

OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

A connection could not be made to the data source with the DataSourceID of '8e03aac2-05c6-4712-87d4-af620a257aeb', Name of 'SqlServer <Server and/ Connection Name>.

An error occurred while processing the partition 'dimXXXXX_74d208d3-9774-4094-83c1-a7e7615bfcc0' in table 'dimXXXXX_74d208d3-9774-4094-83c1-a7e7615bfcc0'.
The current operation was cancelled because another operation in the transaction failed.

This occurred shortly after making subsequent updates to the cube across several weeks. My initial thought was that something was corrupted with the project files since it was all working just fine a week ago.

To resolve this issue,  I had to first remove any Connections that were not being used. This included two of the five connections I created in the project. You can remove the connection string if there are no tables tied to the connection. To determine if there are tables connected to the connection, you could simply try to delete it.  To do this, try the following…

1. In the Menu, Select Model
2. Select Existing Connections…
3. In the Select Existing Connection Dialog box, Attempt to delete a connection…The Tabular Model Designer will give you can error indicating “One or more tables are still associated with the data source that is being deleted. Remove the associated tables before deleting the data source: ….” It will list the associated tables.
4. If the Connection is allowed to be deleted, it is safe because that connection is not tied to any tables imported

One that was done, I then changed all the connection strings from using the SQL Server Native Client 11.0 (which is fine as long as the SQL Server is on the same machine as the SSIS development / package environment), but to the Microsoft OLE DB Provider for SQL Server which is a bit more flexible (for local and remote servers).

Both of these together resolved my issue.

But the most important thing is how did I get to have some many connections?

 Well this was caused by improperly adding the new tables. When I would add new tables to the existing tabular cube, I would mistakenly start by selecting “Import from Data Source” instead of correctly using  “Existing Connections”. When you specify Import from Data Source  you will then be forced to add a new Connection which will increase the list of Connections. If you do this enough, you have multiple connection strings that are all the same other than the connection name. This gets really annoying when you process the cube and are prompt for a password for EACH connection. This also potentially introduces annoying issues this discussed earlier.