SSRS ReportExecution2005.asmx call with code/ C# / https

As stated in my prior post I have been working on InfoPath Forms Services. During the development of a form I wanted to attach a pdf version of the form in an email during submission. I have researched several ways to accomplish this task and the solution I used was to call reporting services. This call would allow me to return a memorystream and attach it to my email in code. In my case the difficult part was to make sure the call worked from both https and http calls. One key note to take into consideration is to make the web service url call directly from the Forms Collection your are currently in. This will ensure the proper creds are passed and "DefaultCreds" will work. Below I have provided a code sample on how to make the call and return a pdf in a memory stream.

Report Server Web Service Endpoints

Sample of Code:
private MemoryStream FormSubmitGetReportAttachment()
{
try
{

// Render arguments
byte[] result = null;
string reportPath = "http://moss/Reports/Sales/Review.rdl";
string format = "PDF";
string historyID = null;
string devInfo = @"False";
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "ApptKey";
parameters[0].Value = ApptKey.ToString();

//DataSourceCredentials[] credentials = null;
//string showHideToggle = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
//ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;

ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//rs.Credentials = new NetworkCredential(user, pass, domain);
rs.Url = "https://moss/Forms/_vti_bin/ReportServer/ReportExecution2005.asmx";
rs.ExecutionHeaderValue = new ExecutionHeader();

ExecutionInfo execInfo = rs.LoadReport(reportPath, historyID);
String SessionId = rs.ExecutionHeaderValue.ExecutionID;
rs.SetExecutionParameters(parameters, "en-us");

result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

MemoryStream ms = new MemoryStream();
ms.Write(result, 0, result.Length);
ms.Position = 0;
return ms;
}
catch (SoapException e)
{
ErrorMessage(e, true, e.Detail.OuterXml);
return null;
}
catch (Exception ex)
{
ErrorMessage(ex);
return null;
}

}

UserProfileService.asmx in MOSS 2007/ https

Over the past month I have been working with InfoPath Forms Services and managed code. I noticed something on calling a the UserProfileService that I wanted to share. The use of a local DataConnection in the InfoPath works on both https and http calls. But I wanted to strickly use a code solution for this call. For some reason my code solution would only work on http calls. I then came across this article and the recommendation of changing the webservice url from the main site to the site of the form itself.

The solution was to change the url in the web service call from :
https://moss/_vti_bin/userprofileservice.asmx

to:

https://moss/sites/forms/_vti_bin/userprofileservice.asmx

Code Examples of Local and Code only solutions:

Local Data Connection inside Form:
private void GetProfileInfo2()
{
try
{
XPathNavigator Label = Root.SelectSingleNode("/my:myFields/my:Label", NamespaceManager);
XPathNavigator Name = Root.SelectSingleNode("/my:myFields/my:UserName", NamespaceManager);
XPathNavigator ErrorMesageLabel = Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager);

WebServiceConnection UserSvc = (WebServiceConnection)DataConnections["GetUserProfileByName"];
XPathNavigator xnDoc = DataSources["GetUserProfileByName"].CreateNavigator();
UserSvc.Execute();
XPathNavigator xnAcctEmail = xnDoc.SelectSingleNode("dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values[../s0:Name = \"WorkEmail\"]", NamespaceManager);
XPathNavigator xnAcctName = xnDoc.SelectSingleNode("dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values[../s0:Name = \"PreferredName\"]", NamespaceManager);
if (xnAcctEmail != null)
{
Label.SetValue(xnAcctEmail.Value);
Name.SetValue(xnAcctName.Value);
}
}
catch (Exception exception)
{
ErrorMessage(exception);
Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager).SetValue(exception.ToString());
}
}

Code Only Call:
private void GetProfileInfo()
{

try
{
XPathNavigator RemoteEmail = Root.SelectSingleNode("/my:myFields/my:RemoteEmail", NamespaceManager);
XPathNavigator RemoteName = Root.SelectSingleNode("/my:myFields/my:RemoteName", NamespaceManager);


UserProfileService Profile = new UserProfileService();
Profile.Credentials = new System.Net.NetworkCredential("User", "Pass", "Domain");
//Profile.UseDefaultCredentials = true;
//Profile.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
Web_Service_Validation.UserProfile.PropertyData[] properties = Profile.GetUserProfileByName(UserName);

if (properties.Length > 0)
{
RemoteEmail.SetValue(properties[36].Values[0].Value.ToString()); // WorkEmail
RemoteName.SetValue(properties[4].Values[0].Value.ToString()); //name
}


}
catch (Exception exception)
{
ErrorMessage(exception);
Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager).SetValue(exception.ToString());
}
}

SSIS Execute SQL Task into an object variable / For each ADO Enumerator

I ran across the need to dynamically send out 2000+ emails based on a Execute Sql Task in SSIS. I knew I needed to use the For Each Loop Container but wasn't sure how to link multiple rows to a single action in the For Each Loop Container.

I ended up using the Execute Sql Task with a Result Set of "Full result set" and returned the Result Set to a single Variable Object. This allows you to process multiple columns/rows into a single variable. Just need to make sure its a Object variable. Then in the For Each Loop Container Collection I used the Foreach ADO Enumerator. This allowed me to reference the Object variable I setup earlier. Next, I mapped each of the variables in the mapping section based on the order of the columns in the extract proc. Very similar to an array in .net. Finally, I used a script task to fire off an email for each row.

I have used the For Each Loop Containers in the past for processing/imports of data files. This is the first time I have worked with the Object variable in SSIS and agree this is a very power trick to have in your toolbox.

Thanks
Bryan

SSRS 2008 Inscope / Percent to Total

Over the past couple of weeks I have been asked several questions about InScope function in SSRS 2008. It is a very useful function that allows for aggergations at different group levels in a report. This is helpful when trying to calculate a dynamic percent to total by a parent group.

Here is an example of how to use Inscope:
Territory Total of 2500 appts
District Total of 125 appts
what is the percent total?

=iif(iif(InScope("Territory", count(Fields!.Appts.Value,"Territory"),0) = 0 , 0,
count(Fields.Appts.Value) / iif(InScope("Territory"), count(Fields.Appts.Value, "Territory"),0))

Result = 5%

SharePoint Gantt Chart Formatting

The SharePoint default Gantt chart doesn't provide any formatting but with a Content Editor Web Part and a little bit of CSS you can change the layout and formatt.

What this code does:
- change the images width from 16 to 2 pixels.
- remove the display of weekdays
- rotate the dates to display them vertically


Reference: Link

If you are looking for a zoom slider with JQuery:Link

Submit InfoPath 2007 with Code (SharePoint list & Email)

Create a SharePoint Library Submit data connection and an Email Submit data connection and then programmatically execute the data connections in the FormEvents_Submit event handler of the InfoPath form, and perform a Close the form action as part of the Submit Options on the InfoPath form.

1. In InfoPath, design a browser-compatible InfoPath form template
2. On the Data Source task pane, add a Text Field node named formName under the myFields node.
3. On the Tools menu, choose Data Connections.
4. On the Data Connections dialog box, click Add, and create a new Submit data connection As an e-mail message. Fill in any email address in the To field; you will be changing this in code anyway. Accept the default name of Email Submit for the data connection.
5. On the Data Connections dialog box, click Add, and create a new Submit data connection To a document library on a SharePoint site. Fill in a valid URL to a Document Library. Select the formName node from the Main data source to be the File name for the form. Accept the default name of SharePoint Library Submit for the data connection.
6. On the Tools menu, choose Submit Options.
7. On the Submit Options dialog box, select Allow users to submit this form, deselect the Show the Submit menu item and the Submit toolbar button check box, click Advanced and select Close the form from the After submit drop-down list box, select Perform custom action using Code, and click Edit Code.
8. This will add a FormEvents_Submit event handler to the InfoPath form template.
9. In Microsoft Visual Studio Tools for Applications, add the following C# code to the FormEvents_Submit event handler:

// Get a reference to the main data source

XPathNavigator root = MainDataSource.CreateNavigator();

// Generate a name for the form to be saved in SharePoint

string formName = Guid.NewGuid().ToString();

// Set the name of the form on the data connection

root.SelectSingleNode(

"//my:formName", NamespaceManager).SetValue(formName);

// Submit the form to SharePoint

DataConnection spConn =

DataConnections["SharePoint Library Submit"];

spConn.Execute();

// Set the properties for the email

string toAddress = root.SelectSingleNode(

"//my:sendTo", NamespaceManager).Value;

EmailSubmitConnection emailConn =

(EmailSubmitConnection)DataConnections["Email Submit"];

emailConn.To.SetStringValue(toAddress);

emailConn.Subject.SetStringValue("This subject was set from code");

emailConn.Introduction = "This email was generated by code.";

emailConn.EmailAttachmentType = EmailAttachmentType.None;

// Send the email

emailConn.Execute();

// Indicate success

e.CancelableArgs.Cancel = false;

Save your work and build the code.

Reference:

Split Comma Delimited String

Andy has put together a really good video on Comma Delimited String parsing in sql. Personally I am a big fan of the CLR functionality on this issue. Make sure to check out the video he has put together.

Link to video:
http://www.sqlshare.com/SplittingDelimitedStrings_774.aspx

Link to Andy's example:
http://www.sqlshare.com/references.774.zip

Here is another example of the split function:

GO
/****** Object: UserDefinedFunction [dbo].[fn_Split] Script Date: 06/02/2010 17:05:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_Split]
(
@sText VARCHAR(8000) ,
@sDelim VARCHAR(20) = ' '
)
RETURNS @retArray TABLE
(
idx SMALLINT PRIMARY KEY ,
value VARCHAR(8000)
)
AS
BEGIN
DECLARE
@idx SMALLINT ,
@value VARCHAR(8000) ,
@bcontinue BIT ,
@iStrike SMALLINT ,
@iDelimlength TINYINT

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTRIM(RTRIM(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ( ( @iDelimlength = 0 )
OR ( @sDelim = 'Empty' )
)
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText) > 0
BEGIN
SET @value = SUBSTRING(@sText, 1,
CHARINDEX(@sDelim,
@sText) - 1)
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value)
+ @iDelimlength
SET @idx = @idx + 1
SET @sText = LTRIM(RIGHT(@sText,
DATALENGTH(@sText)
- @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue = 1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText) > 1
BEGIN
SET @value = SUBSTRING(@sText, 1, 1)
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END
SET @idx = @idx + 1
SET @sText = SUBSTRING(@sText, 2,
DATALENGTH(@sText) - 1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray
( idx, value )
VALUES
( @idx, @sText )
SET @bcontinue = 0
END
END

END

RETURN
END