InfoPath 2007 Bind Drop Down List with C#.Net

I have seen several ariticles on how to bind a list in InfoPath 2007 so this isn't anything new but I wanted to share the method that worked best for me. I found it best to make a generic method to bind the list so you can hook it into multiple event handlers. This prevented me from writing the same bit of code twice. If you notice prior to binding the list I clear the list. On a prior post I have posted the code for the ClearListBox method. If you don't clear the list prior to you may end up just appending your values.

private void BindDistrictList(string username, string HomeDistrict)
{
string xpListBox = "/my:myFields/my:listDistrict/my:listDistrictItem";
ClearListBox(xpListBox, Root);

try
{
string data = string.Empty;
using (SqlConnection conn = new SqlConnection(_SqlCon))
{
SqlCommand command = new SqlCommand("IPGetDistrictbyUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@UserName", SqlDbType.Char).Value = UserName;
command.Parameters.Add("@hd", SqlDbType.Char).Value = HomeDistrict;
conn.Open();

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
XmlDocument doc = new XmlDocument();
XmlNode group = doc.CreateElement("listDistrictItem", NamespaceManager.LookupNamespace("my"));
XmlNode field = doc.CreateElement("listDistrictItemName", NamespaceManager.LookupNamespace("my"));
XmlNode node = group.AppendChild(field);
node.InnerText = reader["DistrictName"].ToString();

field = doc.CreateElement("listDistrictItemValue", NamespaceManager.LookupNamespace("my"));
node = group.AppendChild(field);
node.InnerText = reader["DistrictID"].ToString();

doc.AppendChild(group);

MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:listDistrict", NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator());

}
conn.Close();
}
}
catch (Exception ex)
{
ErrorMessage(ex);
}
}

InfoPath 2007 Clear Databound Drop Down List with C#.Net

Over the past couple weeks I have been working with InfoPath 2007 and cascading drop down list. The issue is once a user decides to go out of the normal flow selection you need to clear the drop down list and re-bind the list. I couldn't find good examples on how to clear a list so I decided to share my code.

Call from your method:
ClearListBox("/my"myFields/my:listLocations/my:listLocationsItem", Root);

Here is the ClearListBox method:
public void ClearListBox(string xp_ListBox, XPathNavigator xNav)
{
//clear list box of items
XPathNodeIterator lstClear = xNav.Select(xp_ListBox, NamespaceManager);
if (lstClear.Count > 0)
{
for (int i = lstClear.Count; i > 0; i--)
{
XPathNavigator reList = MainDataSource.CreateNavigator();
XPathNavigator reListItems = reList.SelectSingleNode(xp_ListBox + "[" + i + "]", NamespaceManager);
reListItems.DeleteSelf();
}
}
}

I am using a Property for Root:
private XPathNavigator Root
{
get
{
return MainDataSource.CreateNavigator();
}
}

InfoPath - Get the current user without writing code

Recently I started working on InfoPath forms in Moss2007. One common item that is needed on each of the forms is the current user. I came accross a method of getting the current user in InfoPath 2007 via the UserProfileService web service. Below is a really good article that provides a step by step example.

http://blogs.microsoft.co.il/blogs/itaysk/archive/2007/04/05/InfoPath-_2D00_-Get-the-current-user-without-writing-code.aspx

Active Directory using SSIS, LDAP, ADHelper.dll and C#.Net

Recently I have been task with a project to get up to date info from Active Directory. My goal was to stay within the scope of SSIS, MOSS, SSRS and SQL. The problem I faced was two very specific fields in LDAP, description and memberof. Since these two fields are arrays you can't use LDAP or SSIS Active Directory Services. I did come across the ADHelper dll that would allow SSIS to return the memberof field. The proplem is that the query is focused on users and not group. In order to get group description I had to write a console app that was kicked off by a SSIS package.

Here is a good article on the basic fields that can be queried using SSIS Active Directory Services.

http://agilebi.com/cs/blogs/jwelch/archive/2007/04/07/retrieving-information-from-active-directory-with-ssis.aspx

Here is a good article on the ADHelper.dll that can be referenced in SSIS
http://www.mssqltips.com/tip.asp?tip=1657

I will post the code to query the Group Description infomation.

Week of Business Intelligence with Pragmatic Works Webinars

This week Pragmatic Works put togther a "Week of Business Intelligence" webinars. It was a lot of material to cover in such a short amount of time but each session is a must. If you didn't get a chance to view them make sure to check out the recorded sessions at the below link. Most of the webinars have code samples you can download.

Some of my favorite:

1. Designing a Data Warehouse
   
2. Loading a Data Warehouse with SQL Server Integration Services (SSIS)
   
3. Report Design Recipes for BI Solutions

http://www.pragmaticworks.com/resources/webinars/March2010Webinar.aspx

SSAS 2008 Time Calculation Dimension (Calculated Members)

Over the past couple of weeks I have been working on setting up a PPS 2007 dashboard and came across a Time Calculation Dimensions in SSAS 2008. At first I didn't truly understand the true purpose or functionality but after time I gained respect as I created new Time Calculation Calculated Members. This article will explain the steps needed to setup a Time Calculation dimension and add multiple Calculated Members that are stored in the Time Dimension.

1. Go to your dsv of the cube and add a new Named Calculation field to your time dimension, Make sure to call it PeriodID and the expression should be " N'Current Period' " (make sure to drop the double quotes
   
2. Create a new Dimension with the Dimension Wizard
3. Select use an existing table
4. tap into your current time dimension, in my case I am using DimWeek
5. Make sure to have the primary key of the dimension selected and no other fields
6. Name the new dimension "Time Calculation"
7. Now that the dimension is created bring it up
8. Drag over the PeriodID (the Name Calculation you created on step 1) to the Attributes section of the Dimension Structure tab
9. Rename the field to Time Calculation and you should have something like this
10. Now open the cube up and go to the Calculation
11. Create a new Calculated Member
12. Name: [Prior Year]
13. Parent hierarchy: Time Calculation. Time Calculation
14. Expression: (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period])
15. 
16. Now you need to setup your Dimension Usage
17. Setup the Measure Group, Referenced, Intermediate Dimension (Week in this case), Reference Dimension attribute (Time Calculation), Intermediate Dimension attribute (PeriodID)
18. 
19. Build and deploy cube
20. Now in your cube browser you will see a new dimension call Time Calculation
21. Add the new dimension and drag a metric and the time dimension and you will see the power of the Time Calculation Dimension

Here is a list of some other Time Calculations:
CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior Year]
AS (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period]),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date]
AS aggregate(crossjoin([Time Calculation].[Time Calculation].&[Current Period], PeriodsToDate([Week].[FiscalCalendar].[Fiscal Year], [Week].[FiscalCalendar].CurrentMember))),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change]
AS [Time Calculation].[Current Period] - [Time Calculation].[Prior Year],
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change %]
AS Case
When ISEMPTY([Time Calculation].[Prior Year]) Or [Time Calculation].[Prior Year] = 0
Then IIF([Time Calculation].[Current Period] > 0, 1, 0)
Else
[Time Calculation].[Year to Year Change] / [Time Calculation].[Prior Year]
End,
FORMAT_STRING = "Percent",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 3 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(3) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 6 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(7) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date %]
AS [Time Calculation].[Time Calculation].&[Current Period] /[Time Calculation].[Time Calculation].[Year to Date],
FORMAT_STRING = "Percent",
VISIBLE = 1;

Cross Site List RSS Feed with SharePoint DataView (No need for Anonymous Access)

1. Lets start out by pulling up SharePoint Desinger 2007


2. Open up your site (in my case I am using MySite)


3. On the Data Source Library tab on the right portion of the designer
   


4. Connect another library in the lower right
   


5. Now that you have created a connection to another library you need to "Refresh library" to get the objects to appear
   


6. Now your Data Source Library should look like this (image)


7. We now move on to the list you are trying to hook into. On the Date Source Library select "Connect to a script or RSS feed.."


8. On the General tab give it a name, in my case "BI Calendar"


9. On the Source tab the default values are good


10. Just add the URL for the List RSS feed you want to view
11. Go to the list, select the action, select "View RSS Feed"
    


12. Take the URL and drop it into the "Enter the URL to a server-side script" field
    
13. At this
    point if you a specific view that you want to reference you are going to need to
    add the GUID to the view in the above step as well. This was difficult step to figure
    out and I hope I can help save someone else the time I spent on it. Open up the
    view, modify the view and pull out the "&View=" from the URL. This parameter will be used in the server-side script connection to the List RSS Feed. The next step is to add a parameter to the DataView properties. A URL with out the View will look like this: "http://MyServer/_layouts/listfeed.aspx?List=a4a88bd0%2Ddf29%2D4f54%2Da924%2D195be47346ed" and a URL with the View will look like this: "http://MyServer/_layouts/listfeed.aspx?List=a4a88bd0%2Ddf29%2D4f54%2Da924%2D195be47346ed&View=%7B9F3ACEAB%2D75DF%2D4C04%2D83C2%2D3A5733A6CA31%7D"
14. 
    Go to the the Login tab, in my case we are using the Kerberos so "Use Windows Auth"
    works, not sure what other cases this will work. You may have to play this depending
    on the type of Auth you have setup on your farm. Keep in mind if you enter your
    creds they will be stored in plain text. This is the point in which you get around
    the Anonymous Access part of a List RSS Web Part .


15. Now we need to add a SharePoint DateView to the page. Insert, SharePoint Controls,DateView
    


16. The next step is to link the Server-Side Scripts to the SharePoint DataView web part we just
    added. Right click on the server side scrip you just created in the Data Source
    Library and select "Show Data". This will allow you to do two things, validate the
    the connection is working correctly and view the list of RSS fields that come over
    in the feed
    


17. Now scroll down on the Data Source Details tab to the "Item" section. Then select "title &
    pubDate" by holding down your CRTL key.
18. Then select the "Insert Selected Fields
    as.." drop down at the top of the Date Source Details tab
    
    This step binds the fields from the RSS feed to the DataView



19. Go to the DataView and right click edit properties. We want to edit the title and
    convert it into a hyper link.
    
    Make sure the "Text to display" is {title} and the "Address" is {link}.
    


20. Next I clean up the headers on the webpart and the columns
21. Finally save and deploy.
    You may first see an error page but just select the "refresh page" link and you
    are done (image)