SQL Reporting Service – Create An Application

Well, here I am again. I seem to be getting better at posting these days, and here's a fun one!

A few years ago, I had a customer that had a report that listed items in a catalog. At times, they would print out the report and manually scratch out items that needed to be removed from the catalog. I recognized what they were doing one day and suggested we build a web page for them to use to edit the items. Since they were so familiar with the report, I decided to try to embed a link that would remove the items from the database.
So, we added a parameter to the default page that accepted input and passed in the ProductID, which was then fed to a SQL Stored Procedure which deleted the item from the catalog. Below is the link we used to make the call to the we page.

="javascript:void(window.open('http://www.mywebsite.com/default.aspx?param=Delete&prodid=" & Fields!ProductID.Value & "', '_new'))"


Where does this go?

Right click on top of the text or field you want to link to the trigger the event, in our case a delete event. Select the Text Box Properties link, and goto the Action dialog. Select Go to URL from the radio button list and then click the function button next to the Select URL text box.











In the Expression text box, type in the javascript call and click Ok.

  











That's it. Have Fun!

Add Carrier Tracking Link in SQL Reporting Services

I have run into this before. I need to add a link to an outside website and feed in a parameter. This time it was FedEx, UPS, USPS and DHL.

Instead of hard coding the URL’s into the report, and using a series of “If Thens” or “Case Selects” I decided to create a lookup table with all of the carriers and their URLs. From this table, I associated it with an order id and pulled the shipping type.

Orderid - Int32

Tracking Number - varchar(75)

Tracking Carrier - varchar(50)

URL - varchar(150)

Now, I also wanted this to open in a new window. Javascript seemed the obvious solution here. So, I right click on top of the field I want hyperlinked and choose Text Box Properties.

Now choose Action, select Go To URL and click the Function key next to the Select URL text box. This is where we will add the javascript.

The Expression box will be displayed. Make sure the fields are available from your query.

The Javascript used is:

="javascript:void(window.open('" &Fields!URL.Value & Fields!TrackingNumber.Value & "', '_new'))"

This will build the URL + Tracking Number and when clicked, present the user with a new browser window with the tracking information.

Here are the carriers and their URLs:

FedEx:
http://fedex.com/Tracking?action=track&tracknumber_list=%num%&cntry_code=us
DHL: :
http://www.dhl.com/content/g0/en/express/tracking.shtml?brand=DHL&AWB={tracking_number}
UPS: :
http://wwwapps.ups.com/etracking/tracking.cgi?TypeOfInquiryNumber=T&InquiryNumber1={tracking_number}
USPS: :
http://trkcnfrm1.smi.usps.com/PTSInternetWeb/InterLabelInquiry.do?origTrackNum={Tracking Number}


Have fun!

SharePoint - Active Directory Change Password Web Part

Our company, Efficience, setup Microsoft SharePoint 2010 Server for our internal website. We use this for our corporate communications, project and product management. We rely heavily on SharePoint. We also have employees that are in Pondicherry, India who connect to the SharePoint server. As the defacto Systems Administrator in the office, I try to leverage RDP for administering user connections to servers, etc. However, as most admins will attest, I do not want anyone having remote desktop access to Active Directory. For this reason, we created the AD Change Password web part.
I spent a few days combing through the web to find a reasonably priced web part to install that met our needs. I found them to range between $495 and $1200. Granted, some of them had more whistles and bells than was needed, but none fit our simple requirement: allow users to log in to SharePoint and change their AD password.
So, we finished up with a web part that meets are requirement.
The web part is simple:
1) a three field form

2) a label control wired to the current user to display their name and

3) a submit button
To make the connections to the LDAP provider (Active Directory), we added the tool part pane which can be changed based on the environment variable inputs. Simply, edit the web part once it's on the page, provide the:

• LDAP Address (example: servername.domain.local)
• Service Account Name : needs to have privileges to talk to the LDAP provider.
• Service Account Password : not "Secret"!

And that's it. so please go download it and enjoy. You can see our other web parts at http://products.efficience.us/ , Microsoft Office MarketPlace, cnet downloads or TuCows .

Twitter Web Part

The web has rapidly turned into a social medium. When I started using and developing for the “world wide web”, I connected using Prodigy , a dialup service in 1993. In those days Google was still an unknown experiment in a closet at Stanford. Who could have imagined that it would one day host the opinions of nearly 200 million Twitter users spitting out 90 million Tweets per day? Some Twitter users have millions of followers. Lady Gaga currently has 10,433,026 followers. Now imagine that you own a business and Lady Gaga tweets poorly about your product or service. That’s like running a negative ad about you on the final night of American Idol. How many more units do you think you would sell?

Our company wanted to see what the Twitterverse had to say about our company’s products and services on a daily basis. We leverage SharePoint 2010 for our internal needs and it made sense to stick a web part on the home page where we communicate the company’s activities and news. After searching the web for a Twitter web part, we decided to build our own, mainly due to the cost.

Thanks to a few websites we found, we had the core understanding of where we wanted to go and how to get there. Jan Tielen’s blog was the first stop. If you do not have him bookmarked, you should. His article pointed me to the Twitter Search API, which as he mentions in his post, turns out to be easy to use.

Once the code was written and tested, we loaded the web part on our corporate intranet and loaded it with the search text. Since we only wanted to search Twitter and not allow users to submit Tweets, it was fairly straight forward. We also wanted to load the web part with predetermined search values that could not be changed by everyone in the organization. For this reason, the search criteria were set in the Tool Part Pane, which can only be changed by the administrator (in our environment). To setup the query, you can use a number of methods for submitting and returning searches.

Some of the ways are listed below:

• Microsoft + SharePoint – returns results for both “Microsoft” and “SharePoint”
• “web part” – returns results for the exact phrase “web part"
• walk OR run - returns results for either “walk” or “run” (or both)
• explorer -Ford - returns results for “explorer” but not “Ford”
• from:gregdamico – sent from person “gregdamico”
• to:microsoft – sent to person “microsoft”

The web part is free so please go download it and enjoy. You can see our other web parts at http://products.efficience.us/ , Microsoft Office MarketPlace, cnet downloads or TuCows .

Remote Desktop Web Part for SharePoint 2010

Remote Desktop Web Part Available

While trying to figure out how to allow users into our network, without giving them the keys to the castle, I enabled Remote Desktop Access for a few members of my team. Thinking more about this, I quickly remembered a project several years ago while working for the Department of Defense. That project required us to "lock down" access to only necessary personnel and limited access to the programs on the servers. After spending some time on Microsoft's KnowledgeBase, we realized that we could create desktop "shortcuts" for those employees that made the RDP connection, logged them in and limited their access to one specific application on that server.


Recently the Efficience development team decided to build a SharePoint 2010 web part that does the same thing. Leveraging the remote desktop protocol, we created a SharePoint 2010 web part that allows a SharePoint administrator to drop the part on a web part page and configure it for use. It was not developed with the intention to allow users to "pick" their server, but that is coming in the next release.


Here's how it's used. Edit the web part page and select the RDP web part. Once the web part is on the page, choose the tool menu to edit the web part. In the Tool Part Pane, you will find the remote access settings.

Server = IP or URL of server with Remote Access enabled
Username = Account name for the server
Password = password for the Username
Domain = if necessary, provide the domain name
Program = the program name including extension, if you want the user to only have access to that program. Once they close the program, the session ends and the user is logged out and the RDP window closes.

That's it. Now you only have to go download it. The RDP web part is completely free and is available at the Efficience, LLC website @

http://www.efficience.us/products-0/apps---solutions-0/. For support, send an email to support@efficience.us and we will answer your questions.


Thanks! Until next time...

SharePoint 2010 Email Enabled Document Library

I have been working with SharePoint for nine years and love it. Amazingly, I finally had a reason to use the SharePoint e-mailed enabled library, which I have wanted to learn since 2007.

So here’s the scenario. We use a SaaS timesheet application to record time for our employees. We wanted to import that daily report into SharePoint and display the data in a page along with other daily metrics. This report could be sent in Excel, HTML and CSV format. I also decided to use SSIS to import the data into SQL to populate a Performance Point Dashboard. Easy right?

Well, the SaaS application is only able to email reports to us and have no future plans to provide web services to query the data. Oh well. So we setup the reports to be emailed to ourselves and scheduled delivery as daily. Now, since our email server is 2003 SBS Exchange, there was little that could be done to re-reroute the emails.

After a number of searches on Google I found Jeff Widmer’s Blog with a part of the solution: http://weblogs.asp.net/jeffwids/archive/2010/08/16/configuring-incoming-email-for-sharepoint-2010-foundations.aspx . However I only wanted to use an internal account and did not want to expose the STMP server to the web.

So, here’s how we handled it.

1 – Setup the SharePoint SMTP
Login to Central Administration for either Foundation or SharePoint 2010 portal. Choose System Settings > Configure Incoming Email.

Now since I had already setup the outgoing email settings to use the SMTP server on my SharePoint server, I decided to use that for the incoming email as well. For more information on setting up the outgoing SharePoint E-mail click here, http://technet.microsoft.com/en-us/library/cc263462.aspx.

Be sure and remember the Domain Name you used to setup the SharePoint SMTP server because you will need this when you setup the Incoming E-mail settings.

Clicking on the Configure Incoming E-mail link will display this page. -->

Use the SharePoint server name + domain name + .local (ie. SharePoint.Company.local). Put that in the Incoming Email Address field.

You will also want to select Yes for Enable Sites on this server to retrieve email?

2- Setup the Document Library
Now, find the document library you want to email your attachments to and under Library Settings, click Incoming e-mail settings under Communications.

Click “Yes” under Allow this document library to receive e-mail? Under E-mail Address field add the address you want to use for this library.

Since I want this to update daily, I chose: Save all attachments in root folder and Overwrite files with same name to “Yes”.

3 –Test the Library
To test this out, open your email client and send a message with an attachment.

After a few secs, the email attachment is now in the document library:

So, I will add the SSIS import in my next post. Happy Coding!

Increasing SharePoint 2010 List Maximum

I have been developing on SharePoint since 2001 and it is beginning to amaze me that "mystery errors" remain. Ok, so recently I was setting up a SharePoint 2010 custom list pulling data from Bugtracker using the data connector. When I opened the page, I got the "unable to ... blah, blah" - mystery error. Anyway, I searched in Google, Bing, Yahoo...and nothing. Then after digging around in Google Groups, I found an interesting lead.

"Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator. "

Buried in the log
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

The maximum number of rows that can be read through Database Connector is '2000'. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

Searching this message led me to this article:
http://blogs.technet.com/b/speschka/archive/2009/11/13/large-list-throttling-for-external-lists-in-sharepoint-2010.aspx

Below is the script I needed to run in Powershell to increase the maximum limit. This allowed my 2500 bugs list from Bugtracker to be displayed. When the CEO is happy, I'm happy. Happy Coding.

Set-SPBusinessDataCatalogThrottleConfig
$proxy = Get-SPServiceApplicationProxy where {$ -match “Business Data Connectivity Service”}
Get-SPBusinessDataCatalogThrottleConfig –Scope database –ThrottleType items –ServiceApplicationProxy $proxy

$defaultThrottleConfig = Get-SPBusinessDataCatalogThrottleConfig –Scope database –ThrottleType items –ServiceApplicationProxy $proxy
$defaultThrottleConfig

Set-SPBusinessDataCatalogThrottleConfig –Default 10000 –Identity $defaultThrottleConfig –Maximum 20000
$customThrottleConfig = Get-SPBusinessDataCatalogThrottleConfig –Scope database –ThrottleType items –ServiceApplicationProxy $proxy
$customThrottleConfig