Archive for June 26, 2013

Dynamic Cell Formatting in SSRS

Early in my Business Intelligence Designer days using other report designer tools, I was all about the data. Get the data onto the report, black and white, never mind fancy fonts or colors. After all, the numbers won’t be any different if you try to make them prettier. A bit like putting lipstick on a pig! Then I came to realize that judicious deviation from the default fonts was not only acceptable, but desirable as evidenced in SQL Server Reporting Services report templates. Sub-total numbers started showing up in a heavier and more prominent font and weight. Grand total number even more so. Such emphasis on the more important numbers on a report help the eyes to quickly identify them as such, leaving the brain to concentrate on other things, such as the numbers themselves. You end up spending more time thinking about the data, and less time finding what you want.

But report cell formatting can go so much further than simply putting more emphasis on numbers at the bottom of the page. With SQL Server Reporting Services, you have the ability to do ‘conditional formatting’, much like can be done in Excel or Access. (If one number is greater than another, format the cell one way, if not, do something else.) To take advantage of the rich formatting options available in SSRS, you need to understand a few basic concepts, and some nuances of Reporting Services.

Know Your Properties

Click on a tablix cell in a report, then click the Properties button.

Note the myriad of properties that can be set just for the cell formatting. Each property, when you go to access its options via the drop-down menu, will have a choice labeled “Expression…”. This is where we will be putting in the ‘conditions’ of the conditional formatting.

Know your Expression Editor, Program Flow expressions, and Property expressions

The Expression Editor in Reporting Services is a powerful tool. For the purposes of our conditional formatting, we’ll be using it to a) examine some value, b) set a Property, and c) supply a default. For the Font Color property of a cell, click the Expression… option below the color palette (see the screen shot above). At this point, the Expression will most likely contain the single word “Black“, assuming you are going with a standard font color. Also note that the work Black is NOT enclosed in double quotes.


In the Category list in the lower left, expand the Common Functions node, then click on Program Flow. Note that there are three functions in the Item list:


Each of these can be used effectively for this task, but we’ll stick with the IIf ( ) function as it is probably the easiest to understand. Basically it works like this: IF a certain condition evaluates to TRUE, THEN do what it says in the second section, OTHERWISE, do what it says in the third section (the default). To see it in action, enter the following formula:

=IIf ( 1 = 1 , “Black” , “Red” )

Click OK, then Preview the report. Because 1 always equals 1, the first section evaluates to TRUE, so the second section is used, in this case “Black”. Note that we need to enclose our color names in double quotes in the Expression Editor for this to work. Now edit the formula and replace the 1 = 1 with 1 = 0. The preview will show the cell contents in a red font. Armed with this knowledge, we can replace the 1 = 0 with a more appropriate business condition. Delete the 1 = 0 text, but leave the cursor in that spot. Click on the Fields (DatasetName) node to see a list of fields available for the Dataset. Double click one to have it inserted in the formula. Add some examination criteria for it that makes sense to your business users. Remember that this part of the expression must evaluate to either True or False. Your final Expression might look like this:

= IIf ( Fields!YTD_Sales.Value >= Fields!PriorYear_YTD_Sales.Value , “Black”, “Red” )

The above conditional expression template can also be used to set any other property available. The only trick is to know what to put in place of the “Black” and “Red” components. The easiest way to determine that is to open the list of choices for the property, and use any set of values you see displayed, as long as you enclose them in double quotes in your expression. Shown below is a list of possible values for the Font Weight property


Using the same True/False comparison from the previous example, the Font Weight property could contain the expression:

= IIf ( Fields!YTD_Sales.Value >= Fields!PriorYear_YTD_Sales.Value , “Bold”, “Normal” )

Font Size is a little trickier as it does not list any valid values. Just know that “8pt” is an 8 point font, “10pt” is 10 point, and so on.

Other Conditional functions

We skipped over the Choose () and Switch () functions, but they operate in a similar manner to IIf(). They return a value based on some comparison. Microsoft’s example of the Choose() function leaves out one simple element: the return value. Sure, it is nice to show the function in use:

=Choose (2, “13”, “15”, “21)

But it would be even better to explain that the function would be ‘choosing’ the second option, which is “15”. How could we use this in a report? Suppose your Salespeople are ranked on performance, and that ranking is available as a report field as an integer. You could select a Font Weight based on the ranking as follows:

= Choose ( Fields!EmployeeRank.Value, “Heavy”, “ExtraBold”, “Bold”, “Semibold”, “Medium”, “Normal”)

The Switch() function lets you list ‘comparison/return’ pairs, all separated by commas, and the function will take the return value that matches the first comparison that evaluates to True. The basic format for this function is:

= Switch ( Comparison 2, Return Value 1, Comparison 2, Return Value 2, … Comparison N, Return Value N )

A Font Size expression could be written as:

= Switch ( Fields!EmployeeLevel.Value = “Manager”, “16pt”,
Fields!AnnualSales.Value <= Fields!SalesGoal.Value, “14pt”,
Fields!AnnualSales.Value > Fields!SalesGoal.Value, “10pt” )

Note here that we have used a different field in the second and third comparisons than is used in the first. There is no rule that says that the comparison elements need to look at the same fields.


It’s easy to see that the Expression editor in SQL Server Reporting Services can be used to build powerful and dynamic expressions that drive various formatting properties. By manipulating multiple Property Expressions for a single cell, the report designer can draw attention to various numbers and report elements if and when the numbers being reported warrant it. It is a much better viewing experience than a simple black on white report where every number is given the same emphasis.



User Defined Actions in Nintex Workflow for Sharepoint

In my last blog post I gave a quick introduction to Nintex Workflow for SharePoint and showed you how to create a very basic workflow with a custom Start Form. I based the fields that are being collected in the Start Form around the idea of a kind of approval workflow that would have 3 types of Approvals that I named Content Approval, Format Approval and Grammar Approval. These approval types could be anything, the idea is just to show that you may have approvals/tasks that are very similar but you would like a few minor differences like with the wording of the Task Description. In order to accomplish this without recreating or copying the same set of Actions multiple times I am going to show you how to create a User Defined Action. A User Defined Action is created in a very similar way to the workflow itself but it allows you to use that set of Actions multiple times in your workflow, or in multiple workflows for that matter, as if it was one action. One of the big advantages to doing this is that if later you need to make a change to that part of the workflow, you can just change the User Defined Action so you don’t have to make the change multiple times.

Now that you get the idea let’s get started. Click on Site Actions. Select Nintex Workflow 2010 and then select Manage User Defined Actions.

Click Create. You will notice that the screen looks the same as when we created the workflow in the previous post.

Go into UDA Settings from the ribbon and enter a Title and Description and optionally change the Category. Click Parameters.

Click New. Enter “ProjectManager” for Name, “Input” for Direction, “Text” as the Type, enter a Description if you want and select Required. Even though you are passing in a person it will accept it as text. Click OK. Click New and create an Input Parameter for “ApprovalText”, “ApprovalType” and “Approver” all with Type of Text. When you are done the User Defined Action Parameter screen should look like this.

Click Close. Click Save on the User Defined Action Settings Screen. Now we are ready to start building the User Defined Action. The process for this is the same as building a workflow. You either select an Action from the left and drag it to the spot you want it or right-click on the spot you want an Action, select Insert Action and select the Action you want. You can then configure the action if necessary.
The first Action we will add will be for a task. There are a few different Actions I could use for this but I am going to use the Flexi task because just like it sounds, it’s a bit more flexible. Right click on the open spot in the Custom Action screen, hover over Insert Action, hover over User interaction and select Assign Flexi task.

Just like in the Workflow you will notice that the Action has an exclamation point indicating it needs to be configured. You will also notice that it has created 2 braches, Approve and Reject. With a Flexi task this can be customized to have different labels and even to add additional outcomes.

Click on the drop down arrow on the Action and select Configure. In the Configure Action screen you can specify who to assign the task to, enter a description of the task, customize the outcomes, etc. You can also customize the task notification that gets sent to the assignee.

To start with we will specify the Assignee by clicking the lookup icon next to the Assignees field. At the bottom of the Select People and Groups dialog expand the “Lookup” section and then scroll down to the Workflow Variables group and click on Approver, click Add and then OK.

This will cause the task to be assigned to the Approver that we setup previously as an input parameter into the User Defined Action. Now we can enter a Task Description. Since we also created an input parameter for Approval Text we will select that for the Description by clicking the Insert Reference button, selecting the Parameters tab, double-clicking ApprovalText and clicking OK. At this point let’s add an additional Outcome. Click Add outcome. Enter “Request Changes” for the name and click OK.

For behavior I am going to just leave it as “First response applies”. The Behavior selection is designed for instances where there are multiple assignees. You can decide if the Outcome is decided by the First response, Majority, or All assignees agreeing. For the purposes of this I am not storing the outcome or outcome achieved. Let’s change the Task Name though. If you remember we created an input parameter of Approval Type. Let’s set the Task name by clearing the default text and then clicking the lookup button next to the field. Click into the “Dynamic text to insert” area at the bottom and type “Review ” (leaving a space at the end). Select the Parameters tab and double-click on ApprovalType and click OK.

I am leaving the rest of the options at their default values so your “Configure Actions” dialog should now look like this.

Now we can customize the notification by clicking the “Task Notification” button in the ribbon. Add the Project Manager to the CC field by click the lookup button next to that field, expanding the Lookup section, scrolling to the Workflow Variables group and adding ProjectManager. Now we can set the Subject to the same value as the Task Name by clearing the default text, clicking the lookup icon, typing “Review ” in the “Dynamic text to insert” area, selecting the Parameters tab, double-clicking ApprovalType and clicking OK just like we did previously. We can now also add the ApprovalText into the notification text. Click on the end of the first line and hit “Enter” twice to add a couple extra line. Put your cursor on the second line and click the “Insert Reference” button. Click on the Parameters tab and double-click ApprovalText and click OK. Your Task Notification should now look like this.

Click on the General tab at the top and click Save. Notice the exclamation point is gone in the Action. Also notice that there is a third branch called “Request Changes”. We are not going to do anything with that branch now but it will be used in another blog post when I discuss looping. Right now all I am going to add is some logging in the Reject and Approve branches. Add the “Log in history list Action” from the Operations group into the “Reject” branch. Click the drop down and select Configure. Click Insert Reference. Go to the “Parameters” tab and double-click ApprovalType. In the Dynamic text to insert box add ” Rejected – ” after the text that was added. Then click the “Common” tab and double-click Approver Comments. Click OK.

Click Save.

Since the only difference between what I want to log for an Approve and a Reject is one word let’s copy by clicking on the drop down icon for the new Log in history list Action under Reject and selecting “Copy”.
Right click on the open space in the Approve branch and select “Paste”. Then Configure the “Log in history list” Action in the Approve branch and change the word Rejected to Approved. Click Save.

We now have a complete User Defined Action so click Publish in the ribbon, add any Change Comments you would like and click Submit. Click Close in the ribbon.
To summarize, in this post we learned how to create a User Defined Action, add and configure the Flexi task Action, customize the notification and add outcomes. In my next blog post I will show you how to add the User Defined Action to the workflow and how to run Actions in parallel.

Introduction to Nintex Workflow for SharePoint

One of the most powerful features in SharePoint that doesn’t get used nearly as often as it should is workflow. One of the reasons for this is that many people are just intimidate by it. They might use some “Out-of-the-Box” approval workflows but beyond that the idea of creating a custom workflow is seen as beyond their capabilities. This is why we have partnered with Nintex. They have a workflow product that integrates seamlessly into the SharePoint user interface so it does not require using Visual Studio or SharePoint Designer. Nintex Workflow provides an intuitive visual interface that makes creating and maintaining workflows much easier.

Over the course of the next few weeks I will be writing a series of articles going through a number of common features that every business can benefit from. The workflow I am going to create during this series of articles is just a simplified example of something you might run into. I am going to have the workflow manually started so it can have a start form to collect data. The workflow is going to assign tasks to 3 different types of approvers in parallel. The approval process will be the same for each but the notifications that will go out to each group will be a little different. This will allow me to show you creating a parallel process and create a user defined action. Additionally I will show how to loop back in case the Project Manager wants to resubmit a Declined tasks to the approver.

For this article I will start from the beginning and show how to customize the workflow start itself by creating variables and customizing the start form.

Let’s get started from the beginning. You will want to start in a list or library, in my case I am starting out in a document library. So we will go to Workflow Settings and select “Create a Workflow in Nintex Workflow”.

When prompted I selected a Blank Template for my workflow. At this point you will see a screen that is similar to flow chart in Visio. The Actions are on the left and the flow chart is in the main part of the screen. At this point I am not going to add any actions I will do that later. In the ribbon click Workflow Settings.

In the Workflow Setting screen, enter a Title for your workflow, a Description of the purpose of the workflow and select the workflow options that you require, in this case I have select Start Manually, and set Start when items are created and Start when items are modified to “No” so that I can have a custom start form.


Now we can add some variables to be populated on the start screen. In the ribbon click “Variables”.


I am going to create a few person selector fields, 3 Approvers and a Project Manager. In the Workflow Variable screen click New and then give the field a Name, select “Person or Group” for the Type, Select “Show on start form”, Select “Required”, and I am choosing “Any User” and “Allow Users” but you can set these however you want.


After doing this for all 3 Approvers and a Project Manager and adding an Approval Text field (Single Line of Text) your Workflow Variable screen should look like this.


At this point, you can Close the Workflow Variables screen. We are now going to customize the Start Form so click the drop down arrow on the Edit Start Form button in the ribbon and select Edit with Nintex Forms.


This will bring up the Nintex Forms – Form Designer. From here you can make whatever changes you would like to the Start Form. For now I just changes some label and made a few other minor changes.


Click Save and then Click Close. Click Save in the Workflow Setting screen. Now we will add just a simple logging action so we can publish our workflow and test the new Start Form.

Log in History List is located in the Operations Group so either open that group and on the left and drag the Log in History List action to the open square in the workflow or right-click on the square and select the action from there.

Notice the exclamation point in the Log in history list item once you have placed it in your workflow. This indicates that you need to do some configuration on that action. Click on the drop down arrow on the action and select Configure.

You can enter any text you want in the text box and insert variables and meta-data values from the associated list item. In this case I just want to log who the Project Manager is. I type the text “The Project Manager for this is: ” and then click the Insert Reference button. Notice the tabs at the top that give you several different options for the source of the information to insert. Click the Workflow Variables tab and then select ProjectManager and click OK.


Click Save and you should now see that the exclamation point has gone away. Now we have a very simple workflow that we can publish. Click the Publish button in the ribbon, enter any Change Comments and click Submit. After the workflow has published you can click the Close button in the ribbon to exit Nintex Workflow. If you now go back into the list/library in which you created the workflow and add an item or upload a file we can test the workflow. Click the drop down arrow on the file name and select Workflows.


Click on the workflow you just created.


You should see the Start screen that you created. Select users for the 3 Approvers and the Project Manager and add some Approval Text and click Start. You can then go into the Workflow Information screen by clicking the drop down arrow next to the file name, selecting Workflows and then Clicking on your workflow in the bottom section “Completed Workflows”.


You should now see in the workflow history section and event with a description that tells you who you selected as a Project Manager.


Just to recap, in this blog post I showed you how to create a Nintex Workflow, Set some basic properties, Add Variables, Edit the Start Form, Add and Configure a basic Action, Publish and run you workflow. In my next blog posts I will continue to build upon this workflow in order to demonstrate some more advanced functionality of Nintex Workflow.

Aggregate All of Your Tasks with SharePoint 2013

Everyone is striving to become a more efficient worker. One theory on how to do that is to clear your mind of any clutter so you can focus all of your attention on your current task. That can be a challenge since throughout the day you might be thinking of what other tasks are due and when. I started by using a scratch pad, and kept constantly updating the list; crossing off tasks, adding new ones. Once it gets too messy, I’d have to start again on a new sheet of paper.

Once we started using SharePoint and Project Server, my list became redundant. Now I was keeping the same list of tasks in multiple different places. Not to mention Outlook has its own tasks list. Which is convenient since you regularly find out about new tasks via email, but it’s inconvenient since Outlook is off by itself. Enter SharePoint 2013.

A great feature introduced in SharePoint 2013 is the Work Management Service Application. This application aggregates every task assigned to you from within SharePoint, Project Server, and Exchange 2013, and displays it in one convenient location: My Sites. In addition to that, you can identify which of those tasks are important and even add your own, which are visible only to you. There is also a “Sync to Outlook”, so that all your tasks can be accessed within Outlook and be available anywhere, including your smart phone.

The following is a screenshot, which shows all the tasks that are currently due, or that I’ve marked as important. The tasks can be grouped by source (SharePoint sites, Project Server projects, personal list, or your Exchange mailbox), and can be viewed as a Gantt chart, which is more descriptive than a list of scattered due dates.

Within the Tasks section of My Sites, you have other default views to get at your tasks.

  • Important and Upcoming: Displays a Gantt chart of all tasks. Lists out only those tasks which are marked as important (by you) or are due 2 weeks prior and 2 weeks after today.
  • Active: List of all tasks which are not marked complete.
  • Completed: List of all completed tasks.
  • Recently Added: List of all active tasks that have been added within the past day.
  • Timesheet: Link to Project Server’s Timesheet page.
  • Task Status Reporting: Link to Project Server’s task status page of all your tasks by project within Project Server.

You can personalize your tasks section, so you can define the number of days to identify what a Recently Active task is, or what defines Upcoming. In addition to other settings like project tasks color and display order.

With that, I’ve found a new home to keep all of my tasks. And the best part is that I’ve finally retired my scratch pad.

For more information, see the TechNet article SharePoint 2013 – Work Management Service Application.