SSRS – Join Multiple SharePoint List Columns Using LookupSet Function

Problem:

How to create SSRS reports by joining multiple SharePoint List columns with foreign key relationship

Solution:

There are three lookup functions in SQL Server 2008 R2 Reporting Services:

1. Lookup
2. LookupSet
3. MultiLookup

In this article I will explain the functionality of LookupSet Function and provide a simple report to show how it is used.

LookupSet:

Returns the set of matching values for the specified name from a dataset that contains name/value pairs.

Syntax:

Lookup(source_expression, destination_expression, result_expression, dataset)

Parameters:

source_expression

(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ID.Value.

destination_expression

(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!CustomerID.Value.

result_expression

(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!PhoneNumber.Value.

dataset

A constant that specifies the name of a dataset in the report. For example, “ContactInformation”.

Return:

Returns a VariantArray, or Nothing if there is no match.

Example

• Create two Data Sets as shown below(here “ds_Orders” is the main dataset and “ds_Products” is the master dataset):

• Create a report and drag and drop the necessary columns from the “ds_Orders” dataset as shown below

• In the fourth column of this report, we have to bring the data from the “ds_Products” dataset with lookup option. To do this write the below expression on the fourth column

=Microsoft.VisualBasic.Strings.Join(LookupSet(Fields!ProductCode.Value, Fields!Code.Value, Fields!ProductTitle.Value, "ds_Products"), ",")

• Now your Report will look something similar to this

• That’s it you are done. Run the report you will see the below output

 Reference:

http://msdn.microsoft.com/en-us/library/ee210576(SQL.105).aspx

Adding CSS reference to a SharePoint master page in 2013

There are a couple of techniques for adding a reference to a css file to a master page, but ​in 2013 there is a new element to use.

CSSRegistration.

An example in a standard .master file looks like this:

<SharePoint:CssRegistration Name="<%$SPUrl:~Site/SiteAssets/MetroJs/MetroJs.css% >" runat="server">

If you are using the Design Manager feature and creating a master page in HTML, then you need to encode the < and >

<SharePoint:CssRegistration Name="&lt;%$SPUrl:~Site/SiteAssets/MetroJs/MetroJs.css%&gt;" runat="server">

Enabling PDF preview in search

SharePoint 2013 now crawls and indexes PDF files by default, what it doesn't do is provide the preview (when you hover over the search results and the callout displays the pdf).

It's simple to get going, follow this article.

http://www.wictorwilen.se/sharepoint-2013-enabling-pdf-previews-with-office-web-apps-2013-march-2013-update

Creating blog or team site in Publishing site

    

​By default the Publishing portal limits what site templates you can use to create sub sites.  You can 

override this setting. 

In the site that you want to be the parent site for the Blog

Go to SiteSettings > Page Layouts and Site Template Settings.  In the Subsite Templates section of 

the page add the Blog (All) template from the left hand list to the Right hand list.  Click OK. 

You will now be able to create a Blog as a subsite to the current site.  These settings can be 

configured on each site or you can apply a setting from any upper level site throughout the hierarchy.

Read error on file when saving list as template

Today I’ll talk about a problem that may occur when you try to create a list template.


It may happen that you have this error message


“Read error on file _catalogs/lt/yourtemplatename.stp“


This error occurs if you have exceeded the number of allowed lookup for list views (default 8)


To save your template list, you can change the value of the threshold in the central administration:

• Go to the Central Administration
• Select your web application
• In the “General settings” menu click on “Resource Throttling” and increase the “List View Lookup Threshold” field

Hope this helps

using the exchange web service API from c#

This week I had been looking at the exchange web service API and how we can inspect Emails within Exchange.


If you need to read emails from an inbox then you can very easily and quickly by using the exchange web service API which you can download from here.


I was looking for a way to check unread messages and download its attachment then mark email as read.


In the following example lets assume we will have emails


Create a reference in your project to Microsoft.Exchange.WebServices.dll which you will be able to found in C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll


The following code demonstrates how to achieve this:

using Microsoft.Exchange.WebServices.Data;

 
 
                ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2010_SP1);
                service.Credentials = new WebCredentials("Email Username", "Email Password", "domain name");
                service.Url = new Uri("https://contoso.com.au/ews/exchange.asmx");
                ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };
                Folder inbox = Folder.Bind(service, WellKnownFolderName.Inbox);
 

                // The search filter to get unread email.
                SearchFilter sf = new SearchFilter.SearchFilterCollection(LogicalOperator.And, new SearchFilter.IsEqualTo(EmailMessageSchema.IsRead, false));
                ItemView view = new ItemView(1000);
 
 
                // Fire the query for the unread items.
                // This method call results in a FindItem call to EWS.
                FindItemsResults<Item> unreadMessages = service.FindItems(WellKnownFolderName.Inbox, sf, view);
 
                 foreach (EmailMessage message in unreadMessages)
                {
                    message.Load();
                    Console.Out.WriteLine("*********************  Processing email " + message.Subject + " *********************");
                    foreach (FileAttachment attachment in message.Attachments)
                    {
                        if (attachment is FileAttachment)
                        {
 
                            FileAttachment fileAttachment = attachment as FileAttachment;
                            fileAttachment.Load("c:\\attachements\\" + fileAttachment.Name);
 
                        }
                    }
 
                    message.IsRead = true;
                    message.Update(ConflictResolutionMode.AlwaysOverwrite);
                }

Adding a custom CSS file to your Visual Web Part

If you would like to add a custom CSS file to a Visual Web Part that allows the styling of the web part to remain consistent no matter where it's installed, you can do so via the following steps:

1. Using Visual Studio 2010, open your Visual Web Part solution
2. In Solution Explorer, right-click on the project and select Add -> SharePoint "Layouts" Mapped Folder
3. Right-click on the newly created Layouts folder and select Add -> New Folder
4. Edit the name from "NewFolder1" to "1033"
5. Right-click on the "1033" folder and select Add -> New Folder
6. Edit the name from "NewFolder1" to "Styles"
7. Right-click on the "Styles" folder and select Add -> New Folder
8. Edit the name from "NewFolder1" to the name of your Project (i.e. MyProject in this example)
9. Right-click on the "MyProject" folder and select Add -> New Item...
10. Under the Installed Templates section, expand Visual C# and click on Web
11. Click on the Style Sheet icon, update the name of your style sheet (i.e. Standard.css in this example), and click Add
12. You may now enter the appropriate style sheet rules in this file and save the changes

During the deployment of your Visual Web Part, the custom style sheet will be deployed directly to the SharePoint farm under the following location on the web front end servers:  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\1033\STYLES\MyProject.

Now, to reference the style sheet in your Visual Web Part, you may do so via the following steps:

1. Open your Visual Web Parts .ascx file
2. After the "<%@ Control" tag, add the following line:  <SharePoint:CssLink ID="cssLink1" runat="server" DefaultUrl="MyProject/Standard.css" />
3. Save the change

At this point, you will be able to reference the new style sheet within the controls on your .ascx page.