Usage of Workflow Variables using SharePoint Designer 2007

At one of my client's place, I have created a couple of workflows that sends out an email to a printing press automatically when employees requests business cards on the SharePoint Intranet Portal. Created a simple list with following fields - Data Type:
1. Employee Name - String
2. Title - Choice (List of Choices)
3. Qty - Number
4. Cell # - Single Line of text
5. Office # - Choice (Drop down choice list)
6. Extn. # - Number
7. Fax # - Choice (Drop down choice List)
8. Email - Single line of text

Simple requirement, eh!
So created a workflow using SharePoint Designer 2007 for this list.
1. Created one workflow step with one action that is 'Send an Email'. Set the appropriate properties in the email body by selecting the values from the 'Current Item'.
2. Looks good so far, however, upon submission of new item, workflow generates an email and then I noticed that some fields that were empty are printed as "?????". This looks weird.

To fix the issue:
1. I created Workflow Variables and set the default values to 'x' for all of them.
2. Created workflow step for each variable and set the condition as:
a. For Cell #, for example,
Condition: If List:Cell # does not starts with x
Action: Set Variable: CellNumber to List: Cell#

Else IF - Click link 'Add 'ElseIf' Condition Branch'.
Condition:
Action: Set Variable: CellNumber to " " (without double quotes). Basically click spacebar once to put single space in the value.

3. Do it for all the variables. Note. If the Field data type is number, for example, extension number, the condition will be:
If List: Ext # is greater than 0,
4. In case of Choice type field, because choice type field values starts with a numeric value, then condition mentioned in step 2 is still valid.

5. Upon setting all workflow steps and values for conditions, the final step is to modify the 'Send an Email' step. The only change here is that instead of using List values, user variables using 'Add Lookup to body' button.

Reason of doing this drama:
When I create a variable in SharePoint Designer, the default value is set to "?????". I dont know where to change this default value. Even if the list field value is empty in the list, when workflow runs, the default value shows up as "?????" and not empty.
So it is required initialize the values by 'x', and then later resetting the value to " " (without double quotes and one space).

And if you try to check the value of the variable to "?????" then the condition will not be true, which is surprising to me. So to fix the problem, I am setting the value to 'x' first and then later if the List: field Value is empty, then resetting the variable to ' ' so that in the email body the field value should display as ' ' (without quotes) which is what customer wants to display.

Initial Workflow Email Output:
Following request submitted for printing

Employee Name : Test User
Title : Client Relationship Manager
Division : Los Angeles
Quantity : 500
Cell # : ?????
Office # : 323.923.3000
Ext. # : ?????
Fax # : 323.923.3020
Fax # (Res) : ?????
Email : ?????

After Fixing the issue, it was:
Following request submitted for printing

Employee Name : Test User
Title : Client Relationship Manager
Division : Los Angeles
Quantity : 500
Cell # :
Office # : 323.923.3000
Ext. # :
Fax # : 323.923.3020
Fax # (Res) :
Email :

Hope this helps somebody!! Send me email at Atul Shukla for questions or suggestions to improve the code.
Thanks!

The user does not exist or is not unique.

Past one week, I was struggling to get the solution of this problem related to SharePoint.
Problem #1: Whenever I try to add a user to a SharePoint group, if the user exist in the site collection, then I get this specific error.
Problem #2: Whenever I try to add a user to a SharePoint group, and if the user DOES NOT exist in the site collection, then I am unable to add the user at all because SharePoint in unable to communicate with Active Directory for name resolution.

These two specific problems were giving me very hard time. Let me give a brief about the environment here:
Web Front End (WFE) Server: 64-bit machine with 64-bit software. Windows Server 2008 R2, Office SharePoint Server 2007 Standard Edition with SP2 (version 6421).
Database + Reporting Server: 64-bit machine with 32-bit software: Windows Server 2003 Enterprise Edition, SQL Server 2005 with SP2, Reporting Services (with SharePoint Integration Mode), Reporting Services SP2 is applied.
Authentication Mechanism: Kerberos. [Very important here, because in windows NTLM authentication, AD link is working]

After struggling for 10 days, I finally open up the ticket with Microsoft to get the solution, and it is fixed after spending more than 24 hours. I interacted with SharePoint Team, IIS Team, Directory Services team, and but this problem is not related to any of these, it is a feature of Windows Server 2008 (and R2 too) which does not work with SharePoint Server 2007 OR the applications that are built on Windows Server 2003 based application. It is a local policy in 2008 R2 that is required to be disabled (and machine restart is required after reset).
This policy name is "Domain member: Digitally encrypt or sign secure channel data (always)". It is Enabled by default when you configure Windows Server 2008 R2.
How to reach to this policy: Start -> Run -> type gpedit.msc.
Browse to Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options. Under Security Options on the right hand side, you will find this policy.
Double click to open the policy, Click 'Disable' radio option. Save the changes and restart the machine.

Earlier to this solution, did following checks though:
1. people picker property is correctly set.
2. Domain Controller is receiving request and responding it back using Network Monitoring Tool.
3. Port Qry UI - A tool that helps to check what ports are responding for kerberos Authentication mechanism.

One very good article that is worth reading: http://support.microsoft.com/kb/823659.

Hope this will help somebody who have Windows Server 2008 R2.

Reporting Server 2005 Add-in for SharePoint Technologies Not setting up correctly on SharePoint Server 2007

I was trying to setup Reporting Services 2005 Add-In for SharePoint Technologies. Existing Production Environment is
(1) Server1 - Web Server (64-bit) - Windows Server 2008 R2, Office SharePoint Server 2007, IIS 7.0 - 64-bit softwares are used in this box
(2) Server2 - Database Server (32-bit) - Windows Server 2003 Ent Edition, SQL Server 2005 Ent Edition, Analysis, Integration & Reporting Services - are installed.

I thought of creating Reporting Services on Web Server (Server1) and with this thought, we installed SQL Server Reporting Services 2005 (32-bit) on Web Server. Initially configuration check was failing because 'Enable 32-bit Applications' setting was 'False' in IIS. I changed it to True and validation succeeded. Installation also completed without any further problems. Ran Reporting Services COnfiguration wizard to set the configuration settings, we gave Database Server name as Server2 and Instance name as MSSQLSERVER. It is done without any problem. Before setting up the SharePoint integration mode, we checked the SharePoint Portal server, and found that neither the portal nor the central admin page is accessible. Panic created because it was production portal web site. Checked all configurations, everything looks good, but still the problem persist. As soon as I disabled 'Enable 32-bit application' and set its value to 'False', Central Admin and Portal Web Site started working again. So conclusion came out that we can't install SSRS on Web Server because web server is running on 64-bit hardware and software, however, SQL Server database is running on 32-bit OS and softwares. So we decided to install SSRS on database server (as there is no separate box available for Reporting services). So we changed 'Enable 32-bit application' to true and then un-installed SSRS from Web Server. Restarted the web server, and changed 'Enable 32-bit application' back to 'False'. Portal web sites and central admin web site was running fine.

Now we installed SSRS on Database server (Server2), and configured reporting services in there. We also installed WSS 3.0 (32-bit) on database server and add it to existing web farm. We configured reporting services into SharePoint Integration Mode, without any problem using domain service account. In fact, we logged to these machines using this service account. All configuration is done and accessible.

Further we need to install Reporting Services 2005 Add-in for SharePoint Technologies on the web server. We installed (64-bit) version on Web Server (Server1). Double click installation was failing so adapted another suggested way. Downloaded SharePointRS_x64.msi locally, and then from command prompt, executed this command SharePointRS_x64.msi SKIPCA=1. This extract a file named rsCustomAction.exe to the %temp% location. From Command Prompt, executed command cd %temp% to go to Temp directory, and then executed rsCustomAction.exe -i. Installation completed without any problem. Given the log details:

User: svcSharepoint
Installing Report Server feature.
Calling copyappbincontents command.

copyappbincontents command completed successfully.
Adding Report Server feature to farm.
Installed Report Server feature.
Activating Report Server feature to root level site collections.
Activating feature in web app 'SharePoint - 90'
Activating feature to root site collection: http://Server1
Feature already activated in site collection http://Server1
Activateing Report Server feature in all admin site collections.
Activating feature in web app ''
Activating feature to root site collection: http://Server1:24920
Feature already activated in site collection http://Server1:24920
Updating DocIcon.xml file
DocIcon.xml file updated.
Beginning install of cab files.
Calling HCInstal for lcid '1033'
Outcome code is: 4
Calling HCInstal for lcid '3082'
Outcome code is: 4
Calling HCInstal for lcid '2070'
Outcome code is: 4
Calling HCInstal for lcid '2052'
Outcome code is: 4
Calling HCInstal for lcid '1055'
Outcome code is: 4
Calling HCInstal for lcid '1053'
Outcome code is: 4
Calling HCInstal for lcid '1049'
Outcome code is: 4
Calling HCInstal for lcid '1046'
Outcome code is: 4
Calling HCInstal for lcid '1045'
Outcome code is: 4
Calling HCInstal for lcid '1044'
Outcome code is: 4
Calling HCInstal for lcid '1043'
Outcome code is: 4
Calling HCInstal for lcid '1042'
Outcome code is: 4
Calling HCInstal for lcid '1041'
Outcome code is: 4
Calling HCInstal for lcid '1040'
Outcome code is: 4
Calling HCInstal for lcid '1038'
Outcome code is: 4
Calling HCInstal for lcid '1036'
Outcome code is: 4
Calling HCInstal for lcid '1035'
Outcome code is: 4
Calling HCInstal for lcid '1032'
Outcome code is: 4
Calling HCInstal for lcid '1031'
Outcome code is: 4
Calling HCInstal for lcid '1030'
Outcome code is: 4
Calling HCInstal for lcid '1029'
Outcome code is: 4
Calling HCInstal for lcid '1028'
Outcome code is: 4
Cab files installed successfully.
Stopping W3SVC service.
Starting W3SVC service.

After installing, followed the instructions to configure the settings in Central Administration - Application Management Tab. I found 'Reportion Services' section. Clicked Manage integration settings and gave values for Report Server Web Service URL as http://server2:8080/reportserver. Set the value of Authentication Mode as Trusted Account. In Grant Database Access section, I noticed that Report Server name is appearing as Server1 (which is my webserver and not report server). Changed this name to Server2. Click Ok pops up a dialog box to ask about the reporting services service account details. I inputed the correct service account details and click Ok. In Set server defaults, I did not make any changes.

With these settings, I thought that I am done with my configuration settings. Further I created a simple SSRS report using Business Development Studio from Database Server (Server2) and published the reports directly to SharePoint Portal under Reports -> Reports Library. Publishing succeeded without any problems. When I clicked the report RDL file, it gives me the following error:

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

Enabled the tracing on the web server, and access the portal directly from web server, i found this error:

File not found.

Both webserver and database server are running under NTLM authentication. I ran the following script from command prompt:

cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

So now the question I am asking to myself, where exactly it is searching for the report? When I further checked the configuration values set under 'Grant database access', I found that Server Name is still pointing to Web Server (Server1) instead of correct reporting services database that is running on Server2. So now I am stuck as none of the reports are accessible. And I have no clue what else I can check.

---------------------------
I posted this description on SQL Server Reporting Services Forum, but didn't receive any response in few days. Finally opened a ticket with Microsoft and they resolved it. Here is the resolution.
1. Checked if both Server1 and Server2 are running with same version of SharePoint. It was different. Server1 installed with Office SharePoint Search Server 2007 Enterprise Edition 64-bit, and Server2 installed with WSS3.0 (32-bit). So un-installed WSS3.0 from Server2 and installed Office SharePoint Search Server 2007 Enterprise Edition (32-bit as machine is 32-bit). Since reporting services add-in needs SharePoint Object Model to be installed/present on the machine, I configured this installation as 'Web Front-end Only' and attached it to existing web farm. Definitely I dont want this server to host any web application so opted the choice accordingly.
2. Checking "NTLM" Authentication set on Report Server (Server2). He checked this option using this script from command prompt:

C:\inetpub\AdminScripts>cscript adsutil.vbs get w3svc/NTAuthenticationProviders

With this command it is identified that IIS is configured with NTLM authentication. I had executed the check earlier and set it to NTLM using command:

C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

Further, check if Report Server IIS Web Site authentication is NTLM. To check that, use following command:

C:\inetpub\AdminScripts>cscript adsutil.vbs get w3svc/812193046/root/NTAuthenticationProviders Where 812193046 is website identifier related to my machine.

Yours could be different so use it accordingly. If the value is not set or is not NTLM, use the following to set appropriately:

C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/812193046/root/NTAuthenticationProviders "NTLM"

3. Checking Disable Loop Back Check registry entry on Web & Report Server (Franky speaking, I do not know much about disabling Loop back check, however, it is required to set on web & report server) - As I said, it is required to be set in Web front end & Report Server machines. Here are the instructions. On the web front end, open registry using regedit from Start->Run.

(b) Navigate to the path: Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa and on the right side, if you do not find the name as DisableLoopbackCheck, then create one by:
(a) Right Click white space, click New-> DWORD (32-bit) Value (My WFE is 64-bit machine). Give the name as DisableLoopbackCheck (case-sensitive). The value should be 1 (Decimal).
Do this change on Report Server machine too.

4. Final changes are required in SharePoint Central administration -> Operations -> Global Configuration -> Alternate Access Mapping.
It is important to note that Reporting Services 2005 Add-in works ONLY with the website defined in Default Zone.
In my situation there were two zones (a) Default zone, the internal url was http://server1 and (b) Intranet zone, it was http://portal.company.com/.
I was trying to access the reports using http://portal.company.com/ and getting file not found error because of the said reason. I swapped default zone and intranet zone Internal URL. It is important to note that http://server1 internal URL must exist in any of the zone (other than default).

With these changes, republished a SSRS report on to the portal and accessed it correctly.

So one milestone achieved where SSRS reports are accessible directly from SharePoint Portal. Now the next task was to publish the Analysis Services based SSRS reports to Portal. Publishing is easy, however, those reports will not be accessible and you may notice this error:

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

And if you try to access the report directly from Web Server (assuming that trace is on), you will see the following error:

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.

With the error description, it is clear that current logged on user is not authorized to access cube data. It is by design that Analysis Services Cubes run under 'Windows Integrated Security'. So how to get pass by this problem, when NTLM security is set for Reporting Services 2005 with SharePoint. There is a solution for that. Follow the steps to check and set some settings:

1. On Database server where report server database exist (in my case, report server is configured on database server {Server2}). Start SQL Server Surface Area Configuration. This option is available here: Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

2. Click Surface Area Configuration for Features. Expand Reporting Services. This option is available under default instance of MSSQLSERVER. Click 'Windows Integrated Security' and clear the check box on the right with the option 'Enable Windows Integrated Security for report data source connections' (if selected).

All set here with a change in the Cube based data source that you must create a new Report Data Source, say DataSource1. Other values are:
a. Name (Required): DataSource1 (.rsds)
b. Data Source Type: Microsoft SQL Server Analysis Services
c. Connection String: Data Source=;Initial Catalog=. For example, if Analysis Services Database Name is AdventureWorks 2008 DW, then this value must be in double quotes while defining in Initial Catalog. Initial Catalog="AdventureWorks 2008 DW"
d. Credentials: Click 'Stored credentials' and then define domain service account in user name as DomainName\DomainUserName, in password box, type valid domain user password. Make sure to select check boxes 'Use as Windows Credential'. Also, this domain service account user should have access to the cubes appropriately. If not it will not work.
e. Availability: Make sure that 'enable this data source' check box is checked. If not, then this data source can't be used at run time for reports.
f. Click Ok to save the changes.

Appropriately set the Report data source as this newly created data source and you are all set.