SP Designer 2010 Workflow- Sending Emails to Multiple Users from a Single People Picker Field
SP Designer 2010 has a limitation when trying to implement a workflow action to send an email to multiple users based from lookup from a people picker control that accepts more than one user. By default the email action will only email to the first user and will not detect multiple users. The workaround is to first "activate" the Build Dynamic String workflow action and then set the email To: field to a variable whose value is assigned by the Build Dynamic String action. For some reason SharePoint will only recognized the multiple users from the Build Dynamic String Action pulling the user names from the people picker field.
Step 1 – "Activate" Build Dynamic String Action
When you open SharePoint Designer 2010 and start to build a workflow you’ll notice that under Actions Build Dynamic String is no where to be found. In order to have it appear as an action you will have to edit the WSS.Actions File found in the 14 Hive which contains all the default SDP Action.
1) Navigate to \Program Files\Common Files\Microsoft Shared\web server extensions\14\TEMPLATE\1033\Workflow on your system drive
2) Open the WSS.Actions file (this is where information about all default SPD actions is stored)
3) Find "Build Dynamic String" and change the "AppliesTo" parameter from "DisplayOnly" to "all". Save the file
4) Run IISReset, re-open SPD – you should now have the Build Dynamic String action available
Step 2 – Workflow: Store multiple users in a custom variable built by the Build Dynamic String workflow action
In order for the email action to recognize multiple users, the multiple users must be saved into a custom workflow variable created by the Build Dynamic String action. For some reason SharePoint will only properly record all users picked by the people picker field when the data is saved into a workflow variable with the Build Dynamic String action.
1) In the workflow designer create action "Build Dynamic String"
2) Within the Action click "Dynamic String"
3) Lookup the users that are defined by the people picker control as a string (This example "Approved By" is a people picker field with multiple users)
4) Return the field as a string and savit to a workflow variable with type string
Step 3 – Assign the To: Field to Custom Variable in the Send Email workflow action
1) Create Send Email action
2) Click the address book icon next to the TO: field
3) Set the lookup to the custom workflow variable that was set in the previous step
4) Now the email will contain all users and will be recognized by SharePoint and each user will receive the email
This workaround will now allow multiple emails to be sent by a SharePoint Designer workflow to all users defined by a single people picker field that allows multiple users with out cracking open Visual Studio.
Custom site provisioning provider
Up until a week ago, I thought the only 2 ways to make a template for a site where Site definition and Site Template.
Both ways required that you figure out a lot of XML syntax, feature ids lists ids etc… for any code you want to run on the created site, you need to staple another feature and if you need to run code after the site is provisioned, you need to spawn another thread that will sleep until the site is provisioned. Possible but not very intuitive or easy.
Then when I needed to create a custom publishing site to be used as a template for sub sites, I found custom site provisioning provider while reading this post.
I will probably never code another site template any other way from now on.
The example shows how to do it with run as system but it is not necessary because templates are used to create sites by users with full control so they will have enough rights to run the code. The downloadable code is a good start though.
Using custom site provisioning provider is mainly implementing the SPWebProvisioningProvider interface.
All the tasks are performed in in the: public override void Provision(SPWebProvisioningProperties props)
The first thing to do is to apply a standard template to the new site so we have something to start with.
I find it best to start with "STS#1", the blank template. This gives you an empty provisioned site to add whatever is needed by the requirements. Once a template is implemented with "props.Web.ApplyWebTemplate(SITE_TEMPLATE);" the site is provisioned, so you don’t need to spawn threads and such to work with the created site, it is ready to be modified.
Little XML after all
It will not be SharePoint solution without some xml for good measures right? So here is the minimum.
In a module named "ProvisioningData" add a file named as your project with the features that will be used in the site (Later I will show how to find out the GUIDs for the features)
In the {SharePointRoot}\Template\1033\XML folder , we need a file named webtemp_{your project name}.xml
It will include the name, title, description, image URL, Display category and a reference to the provisioning data.
The Display category and the image URL will allow distinguishing your template in the UI
Image, Category and description
Adding features
The example shows how to iterate the provisioning data for features but they can be added directly in the code too, using the ID.
For the site site.Features.Add(new Guid({the ID of the feature}));
For the sub site web.Features.Add(new Guid({the ID of the feature}));
Make sure you know the scope the feature is for and not to add a feature that exists in the scope alread. (Activated)
Adding Lists
What can be better than figuring IDs of list types? Intellisense off course. Adding lists is as simple as this:
You can add fields to your lists at the same time to further your customization without figuring out the XML syntax like building ONET.XML. Again Intellisense is much easier
Adding pages
Pages can be added as files to a module; text can be changed to customize the particular provisioning (for example, embed in the text, the site title or instructions for the administrator) then added to the root or a folder in the site.
See method "AddPage" in the attached example.
Adding web parts in pages
If we started with a blank site, the web parts are not incorporated in the pages yet.
So we have the opportunity to insert web parts in the zones we want in our pages. We must know the zone IDs of the page and the order in the zone we want the web parts to go in.
There are few types of web parts; the example uses 2 of them:
- WebPart for this type you need the existence of the template to be in the web part gallery of the site collection (activated)
- ListViewWebPart this will show an existing list or library with a view.
Controlling Navigation
We also have the opportunity to customize the top and quick launch links. The example shows the publishing navigation because the template has the publishing feature but the same can be done with none publishing only the classes will be from Microsoft.SharePoint.Navigation Namespace rather than publishing.
We can:
-
Programmatically decide how pages and sub sites will be referenced.
- "Global" refers to the top navigation
- "Current" refers to the quick launch.
- Programmatically remove the headers (Libraries, Lists etc…)
- Programmatically reorder the links and add new custom ones.
Finding GUIDs of features
I found out from a utility that gives you the name by GUID of feature in CodePlex that you can enumerate the Feature Definitions of a farm like so:
SPFarm oFarm = SPFarm.Local;
foreach (var oFeatureDev in oFarm.FeatureDefinitions)
{
System.Diagnostics.Debug.WriteLine(string.Format("Feature {0} Id= {1} scope {2} "
, oFeatureDev.DisplayName, oFeatureDev.Id ,oFeatureDev.Scope));
So you can get a dictionary of name and ID of features that you can use.
You can also use PowerShell
The attached solution includes 2 types of provisioning projects, guess which of them I preferJ.
Restore Strategy – Site Collections, Sites, Libraries, Lists, Documents, and Items
Removing a broken SharePoint 2010 Feature
Great SharePoint Utility: FeatureAdmin
Like most developers I am constantly trying different things on my local development machines to test different approaches and techniques to resolve a variety of programming requirements (no matter what anyone says there is always room for improvement). And like all of those developers I occasionally try something that does not work so well and I end up breaking my development environment (this is why we test on our machines before deploying code for a client to see!).
Recently this happened to me with a SharePoint feature, I was testing something (not sure what) but I ended up getting a feature stuck in my farm that was not actually "deployed" anywhere. I of course found this a month later so all I could remember is this feature was a test of something but I had no clue what. Since it was not deployed in any scope in SharePoint (Farm, WebApplication, Site, or Web) there way no way in Central Admin for me to easily uninstall it, the only reason I found it is by running the Get-SPFeature powershell command and I found the feature was there but the Scope column was blank.
So how did I remove it? I’m sure there is a way to do this in powershell but I wanted to see if there was a utility to visually see what features are activated where because if I broke one feature I may have broken others. I stumbled across a great little tool on CodePlex called FeatureAdmin, see the screen shot below but in essence you open it up you can choose a scope to see what features are activated. You can also load a list of all Features installed in the farm and it will flag broken features with an error, you simply highlight the broken feature and remove it. Solved my problem, if anyone else has any odd Feature issues I highly recommend trying this utility because it can save you a lot of headaches to quickly find a feature problem.
BI Technical Series: Using Images within PowerView
Placeholder
BI Technical Series: Flattening a SSAS Cube Balanced Hierarchy
Suppose we have a balance hierarchy in a SSAS cube that looks as follows:
And you wanted to flatten the hierarchy such that Category, Subcategory and Product appear on rows and the associated measures appear on the columns. The first instinct may be to do the following:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
CROSSJOIN(CROSSJOIN([Product].[Product Categories].[Category].members,[Product].[Product Categories].[Subcategory].members),[Product Categories].[Category].[Product].members]) ON ROWS
FROM [Adventure Works]
However, this typically results in the following error:
Parsing the query …
Query (2, 2) The Product Categories hierarchy is used more than once in the Crossjoin function.
Parsing complete
The issue being that you cannot apply a CROSSJOIN to multiple levels from the same hierarchy. There are several methods for accomplishing this. All of them have varying levels of complexity along with associated advantages and disadvantages. I am just going to provide 1 more method for solving the problem. Besides, you can’t have too many ways to solve a problem.
To begin the flattening process the leaves option of the DESCENDANTS() function will be used to return the lowest level of the hierarchy. Using the DESCENDANTS() function the highest level of the hierarchy is specified along with the leaves option as the last argument. The associated MDX is:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
DESCENDANTS ([Product].[Product Categories].[Category],,LEAVES) ON ROWS
FROM [Adventure Works]
Here is the result:
Now we have the lowest level of the hierarchy. To retrieve the other levels we can use the following MDX:
WITH MEMBER [Measures].[Subcategory Level] as ANCESTOR([Product].[Product Categories].CURRENTMEMBER,[Product].[Product Categories].[Subcategory]).NAME
MEMBER [Measures].[Category Level] as ANCESTOR([Product].[Product Categories].CURRENTMEMBER,[Product].[Product Categories].[Category]).NAME
SELECT {[Measures].[Subcategory Level],
[Measures].[Category Level],
[Measures].[Internet Sales Amount]} ON COLUMNS,
DESCENDANTS([Product].[Product Categories].[Category],,LEAVES) ON ROWS
FROM [Adventure Works]
The calculated members [Measures].[Subcategory Level] and [Measures].[Category] level are using the current member which happens to be a leaf member to navigate up the hierarchy using the ANCESTOR() function and the intrinsic NAME member property to return the name of the member at the specified level.
A couple of items to note; the above approach is a bottom-up approach in that the respective parents of a member are being returned starting at the leaf level. Also, if you have direct access to the attributes that make up the hierarchy you can simply use the CROSSJOIN() function because the attribute hierarchies are different but, if you must use the user defined hierarchy, then the issue arises on how to flatten the hierarchy. This approach can also be used to flatten recursive hierarchies.
So, does anyone have a different approach to flattening hierarchies; if so, drop me a line.
Ray Johnson | Director – Intelligent Business | ray.johnson@mpspartners.com
MPS Partners, LLC. an SPR Company
http://www.mpspartners.com
BI Technical Series: Flattening a SSAS Cube Balanced Hierarchy
Suppose we have a balance hierarchy in a SSAS cube that looks as follows:
And you wanted to flatten the hierarchy such that Category, Subcategory and Product appear on rows and the associated measures appear on the columns. The first instinct may be to do the following:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
CROSSJOIN(CROSSJOIN([Product].[Product Categories].[Category].members,[Product].[Product Categories].[Subcategory].members),[Product Categories].[Category].[Product].members]) ON ROWS
FROM [Adventure Works]
However, this typically results in the following error:
Parsing the query …
Query (2, 2) The Product Categories hierarchy is used more than once in the Crossjoin function.
Parsing complete
The issue being that you cannot apply a CROSSJOIN to multiple levels from the same hierarchy. There are several methods for accomplishing this. All of them have varying levels of complexity along with associated advantages and disadvantages. I am just going to provide 1 more method for solving the problem. Besides, you can’t have too many ways to solve a problem.
To begin the flattening process the leaves option of the DESCENDANTS() function will be used to return the lowest level of the hierarchy. Using the DESCENDANTS() function the highest level of the hierarchy is specified along with the leaves option as the last argument. The associated MDX is:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
DESCENDANTS ([Product].[Product Categories].[Category],,LEAVES) ON ROWS
FROM [Adventure Works]
Here is the result:
Now we have the lowest level of the hierarchy. To retrieve the other levels we can use the following MDX:
WITH MEMBER [Measures].[Subcategory Level] as ANCESTOR([Product].[Product Categories].CURRENTMEMBER,[Product].[Product Categories].[Subcategory]).NAME
MEMBER [Measures].[Category Level] as ANCESTOR([Product].[Product Categories].CURRENTMEMBER,[Product].[Product Categories].[Category]).NAME
SELECT {[Measures].[Subcategory Level],
[Measures].[Category Level],
[Measures].[Internet Sales Amount]} ON COLUMNS,
DESCENDANTS([Product].[Product Categories].[Category],,LEAVES) ON ROWS
FROM [Adventure Works]
The calculated members [Measures].[Subcategory Level] and [Measures].[Category] level are using the current member which happens to be a leaf member to navigate up the hierarchy using the ANCESTOR() function and the intrinsic NAME member property to return the name of the member at the specified level.
A couple of items to note; the above approach is a bottom-up approach in that the respective parents of a member are being returned starting at the leaf level. Also, if you have direct access to the attributes that make up the hierarchy you can simply use the CROSSJOIN() function because the attribute hierarchies are different but, if you must use the user defined hierarchy, then the issue arises on how to flatten the hierarchy. This approach can also be used to flatten recursive hierarchies.
So, does anyone have a different approach to flattening hierarchies; if so, drop me a line.
Ray Johnson | Director – Intelligent Business | ray.johnson@mpspartners.com
MPS Partners, LLC. an SPR Company
http://www.mpspartners.com
BI Technical Series: Flattening a SSAS Cube Balanced Hierarchy
Suppose we have a balance hierarchy in a SSAS cube that looks as follows:
Your first instinct may be to do the following:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
CROSSJOIN([Product].[Product Categories].[Category].members,[Product].[Product Categories].[Subcategory].members) ON ROWS
FROM [Adventure Works]
This typically results in the following error:
Parsing the query …
Query (2, 2) The Product Categories hierarchy is used more than once in the Crossjoin function.
Parsing complete
The issue being that you cannot apply a CROSSJOIN to multiple levels from the same hierarchy. There are several methods for accomplishing this. All of them have varying levels of complexity along with associated advantages and disadvantages. I am just going to provide 1 more method for solving the problem. Besides, you can’t have too many ways to solve a problem.
To being the flattening process the leaves option of the DESCENDANTS() function will return the lowest level of the hierarchy. Using the DESCENDANTS() function the highest level of the hierarchy is specified along with the leaves option as the last argument. The associated MDX is:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
DESCENDANTS ([Product].[Product Categories].[Category],,LEAVES) ON ROWS
FROM [Adventure Works]
Here are the results:
Now we have the lowest level of the hierarchy. To retrieve the other levels we can use the following MDX:
WITH MEMBER [Measures].[Subcategory Level] as ANCESTOR([Product].[Product Categories].CURRENTMEMBER,[Product].[Product Categories].[Subcategory]).NAME
MEMBER [Measures].[Category Level] as ANCESTOR([Product].[Product Categories].CURRENTMEMBER,[Product].[Product Categories].[Category]).NAME
SELECT {[Measures].[Subcategory Level],
[Measures].[Category Level],
[Measures].[Internet Sales Amount]} ON COLUMNS,
DESCENDANTS([Product].[Product Categories].[Category],,LEAVES) ON ROWS
FROM [Adventure Works]
Incorporating SQL 2012 Columnstore Indexes into your data retrieval strategy
A traditional BI architectural practice is to build a data warehouse with a data cube on top of it. One of the reasons you would do this is that data cubes contain pre aggregated data, thus your reports and queries that use the cube will render much faster than if you were querying directly off of your data warehouse. That being said, there can be infrastructure, support, and resourcing related concerns that need to be addressed if you chose to move your BI strategy in that direction.
Managing Metadata with Master Data Services – Part 1
This is the first in a series of blog entries that will discuss how to manage metadata as master data using Microsoft’s Master Data Services. In this first entry we discuss metadata as master data provide an Introduction to Master Data Services capabilities and what are its key components.
Meta data is data about data. Organizations employ many methods for managing their metadata (file systems, databases, spreadsheets, enterprise content management applications, etc.). There are also different types of metadata:
-
Technical metadata
- Line of Business applications
- Database schemas
- Development standards and conventions
-
Business metadata
- Business concepts
- Business entities
- Business processes
For both types of aforementioned metadata, related processes change, organizations evolve and technologies come and go. The question is how to manage these changes with respect to organizational metadata.
Typically master data would consist of business entities, their associated attributes and members; for instance, a customer master consisting of a list of customer attributes and customers. But what defines a customer. What is its context and how does it evolve over time. I submit that organizational metadata should be treated as master data and a formal tool and process should be used to manage it.
One such tool is Microsoft’s Master Data Services. Master Data Services provides the following capabilities:
- Centralized hub for storing authoritative and certified data
- Supports implementation of business rules and workflows for maintaining data quality
- Supports hierarchies and attribute level domain constraints
- Role based security
- Multiple user interfaces (Web, Excel 2010)
- Model for batch imports and exports to/from external systems
The following figure defines the basic steps in defining, developing and maintaining our master data services solution. In this post we will only address master data structures as they relate to metadata.
The following data structures are available and need to be defined within Master Data Services:
Models – Models organize the structure of your master data. You can have one or many models. In the case of metadata the model will be a container for concepts. For example, the model would be the metadata model defining the concepts of Business Entity Metadata, Attribute Entity Metadata, etc.
Entities – An entity is a container for the attributes, attribute groups, hierarchies and collections that define a specific concept. There can be as many entities as required to fully define the concept. In the case of metadata there can be an entity defined as follows from a membership perspective:
Where
Business Entity Metadata – The name of the entity
Name – The entity being defined (Customer, Product, Account, etc.)
Description – Definition of the entity (Customer is an individual or organization that purchases our products)
Owner – The name of the individual or organization responsible the definition of the entity
Attributes – Attributes can be viewed as columns within an entity. In the case of our Metadata model and our entity Business Metadata Entity we would have the following:
|
Business Entity Metadata |
||
|
Name |
Description |
Owner |
|
Customer |
An individual or organization that purchases our goods or services |
Customer Service |
|
Account |
A cost collection point defining cash flow into and out of the organization |
Finance |
|
Product |
A manufactured good or a service sold to a Customer |
Operations |
Attribute Groups – An Attribute Group provides the ability to manage attributes by grouping them into tabs. From a usability perspective this becomes important when there are a large number of attributes need to be viewed. Attributes can be logically group based on their relationship to one another.
Domain Based Attributes - A domain-based attribute is an attribute with values that are populated by members from another entity. You can think of a domain-based attribute as a constrained list; domain-based attributes prevent users from entering attribute values that are not valid. To select an attribute value, the user must pick from a list. The domain-based attribute can be used in multiple entities.
Once the structures have been built, it is now time to populate them with data. In the next post we will fully define all the metadata entities and discuss their relationships to one another and the types of data that will populate them.
