Automatically Rendering SSRS Reports as PDF

Posted August 3rd, 2011 by Panda

It’s been done before, but worth mentioning. Enjoy! J

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/a09d26ad-776a-447c-ab1a-ab59e1f8f667/

Here’s the syntax I used (all on one line in the URL, of course):

http:// <serverName>/ReportServer/Pages/ReportViewer.aspx?/
<PathToReportOnServer>/<ReportName>
&<ParameterId>=<GUIDforThisParameter>&rs:Command=Render
&rs:Format=PDF&rs:ClearSession=true

Volunteer to be a host family!

Posted August 1st, 2011 by Panda

Here is a little tangent from the usual Tech blogging.

A friend of mine asked me to help spread the word of the need for Host Families for Japanese College Students in Portland, OR.

Here is the “emergency” request:

Currently I am working for a non-profit homestay organization, ANDEO International Homestays.

We are expecting about 45 Japanese college students arriving on August 11th. They will attend PSU special ESL classes and afternoon excursions while they are here. They will return to Japan on Sept. 6th. We received so many host family cancellation last week, and we (my department, and I am the manager..!) still need about 19 more host families for these Japanese college students arriving on August 11th.

When we say “Host family” this means..

  1. single adult who is interested in sharing her/his home to a student and/or
  2. retired couple with/without children at home, and/or
  3. young couple (married or not) with/without children, and/or
  4. international family (some family members may speak Spanish, Chinese, Russian, Korean, etc but speak English at home as a main language – we ask them to speak English as much as they can at home, too) with/without children, and/or
  5. families with specific religion or not

Some families live in apartment, some live in a big house, some parents both work, some are retired. Some families are African American families, some are Asian Americans, some are mix race families, etc.

Is it possible for you to forward my message to as many as people you know? Below is the details of this Japanese college program based at PSU. (We will need to place these students by the end of August 4th…!) Joel is already hosting one of my Chinese students from Beijing…!

PSU, International Special Programs Office and ANDEO International Homestays (non-profit homestay organization) are welcoming 45 Japanese college students from August 11 – September 6th (4 weeks).  Students will attend morning ESL classes and afternoon excursions based at PSU, Monday through Friday, 9am – 5pm daily.  During their PSU program, they will live with host families to learn about American culture, life style and learn about you! 

ANDEO provides students with Trimet tickets, and teach them how to ride the bus/MAX from home to PSU – so families do not need to drive their students to PSU everyday.  A bilingual staff from ANDEO will be on call for 24/7 for any emergencies.  Hosting a student is not as difficult as you think!  PSU will keep the students busy during the day, so your student is looking forward to spend relaxing weekday evenings and fun weekends with your family.   Hosting stipend for hosting a Japanese student for 4 week is $480 in total.  We ask families to provide 3 meals a day (self-serve breakfast and lunch, dinner with family), a comfortable room, and invitations to your daily family activities.

Attached is the program calendar for this group.  If you are open to hosting a Japanese student, please apply online right away!:
Attachment: 2011 America Plus Calendar – Tentative

If you have any questions about this program or other hosting opportunities, please feel free to call Kellie at 503-274-1776 or email her at kellie@andeo.org.

SSL on WSS 3.0 Breaks after IISRESET

Posted June 28th, 2011 by Panda

I’ve recently ran into a peculiarity with getting SSL set up for an existing WSS 3.0 site: They quit working after I run IISRESET. While there have been many different blog posts out there seemingly with the right answer, they haven’t worked 100% in my case. Below is what I have discovered to work along with some caveats and gotchas.

The Environment:

  • WSS 3.0
  • IIS 6.0 (Win2k3 x64)
  • Two different Web Applications with different URLs
  • On Advantage Cert from Entrust (allows registering of two SSL CERTS)
  • Server is in a workgroup. It is a standalone server. No other servers in farm.

The Current Solution:

To the existing SharePoint Web Application in IIS:

This is a fully functional SharePoint site that had been running in BETA w/o SSL. Now that we are moving to production, it makes sense to secure it using SSL… All of the settings below were done through IIS except where otherwise specified.

  • Added a valid CERT to the current web application of the SP Site in IIS (in this case, SharePoint – 80)
  • Changed its default port from 80 to 81
  • There are no host headers on my SharePoint – 80 site
  • On the Cert properties (Directory Security > Edit (the cert):
    • Require Secure Channel
    • Require 128-bit encryption
  • Directory Security Tab > Edit (Authentication and Access Control):
    • Enable Anonymous Access (leave the username/password default)
    • Changed Authenticated Access to Basic Authentication (password sent in clear text)
    • Put the computer name in the “Default Domain” field
  • In SharePoint Central Administration, I’ve set the following Alternate Access Mappings (SCA > Operations > Alternate Access Mappings)

Create a Redirection Website in IIS:

  • Created a new IIS website called Port80Redirect:
    • Added host headers on port 80 to my site
    • Home Directory set as “A Redirection URL”
    • Entered the https:// URL to my site

With the current settings mentioned above, and without running IIS Reset… the SharePoint site works as expected. Any attempts to access the site via http:// will redirect the user to https:// so users don’t get the error message that they must enter the address as HTTPS.

I repeated these steps above for my second web application (I have two different web apps… essentially hosting two different web sites)

Problems:

IISRESET breaks the sites:

  • If I run IISRESET, BOTH sites become inaccessible.
    • IE 8 and 9 report “Internet Explorer cannot display the web page. No error numbers of any sort (useless error!)
    • Chrome reports Error 107 (net::ERR_SSL_PROTOCOL_ERROR): SSL protocol error.

The Workaround:

The only way I’ve found as a workaround is to add a host header to the main site (currently set to port 81), then remove it.

This seems to trick IIS into working again – FOR BOTH SITES!

It is as if making some sort of change to any of the sites in IIS site fixes things (although I’ve only attempted to change the main site, since that is what I was initially troubleshooting).

This is a predictable and repeatable problem and workaround.

Any comments or insights as to why this happens will be greatly appreciated. I am sure there is an obvious answer, especially regarding how to work with SSL Certs and IIS 6.0; but with Google and Bing as my textbooks (no other proper training), and my gut instincts as my tools, this is what I’ve been able to figure out.

SharePoint 2010 – Groups to which a User Belongs

Posted June 14th, 2011 by Panda

Recently, I was asked to “mirror” someone’s access levels, but had not the budget to buy some of the professional-level administration tools out there. After a few trips to Google and Bing, I finally came up with this quickie:

  • Use SharePoint Designer to create a SOAP Service connection to the UserGroup.asmx web service in SharePoint 2010.
  • Use this connection to create a web part web page in your SP Site.
  • Use SharePoint Filter Web Parts to target the specific user

The steps below are just the “quick and dirty” to get the info I need. This is just a starting point, and you can definitely use your favorite customization methods or skills to polish this idea.

Create a SOAP Service connection in SharePoint Designer

  • In SharePoint Designer 2010, I opened up my “lab” sub-site.
  • Click on Data Sources, and then add new SOAP Service Connection.
  • Enter http://YourSharePointSite/_vti_bin/UserGroup.asmx in the location field then click Connect Now.
  • Select GetGroupCollectionFromUser.
  • Modify the userLoginName parameter and…
    • Enter a valid domain\username in the Default Value box.
    • Check “The value of this parameter can be set via a Web Part connection“.
      Note: If you don’t put something there, SPD won’t allow you to add a web part to a page.
  • Click OK.

Add a web part based on the SOAP Service Connection

  • Open an existing page or create a new page on your SharePoint site
  • Edit that site in SPD, and put your cursor in the web part zone you want to place your data.
  • Under the Insert tab, click Data View, then select the new data source just created.

 

Add a filter to the page to allow filtering by login name

It was easier for me to finish this experiment up by editing the page in a web browser

  • Edit your page in a web browser.
  • Add a Query String (URL) Filter, and open the tool pane to edit its settings.
  • Enter a Query String Parameter Name and click OK.
  • Connect this Query String Filter to the Data View Web Part you added in SPD:
    • Click the Query String Web Part’s context menu and choose:
      Connections > Send Filter Values To > your Web Part
    • Select Get Parameters From for the connection type and click Configure.
    • Select userLoginName for the Consumer Field Name and click Finish.
  • Now you can use query parameters to select which user you want to display: http://YourDomain/YourSite/default.aspx?Login=Domain\Username

From here you can take it where you like… Create a form to refresh the page with the username you want to look up or any other type of extra customizations you prefer… or just be lazy and change the Query String manually if you are just using this as a quick reference as I have.

This may seem elementary, however, being an on-going student in the school of Google/Bing, I was unable to find a succinct roadmap to how to set up and successfully run an SSRS report on our SharePoint 2010 environment. We hired outside consultants to deploy SharePoint, and after this engagement, they gave us a SP2010 farm as described below. It was up to me via Google/Bing to figure out how to use it. Rather than go into deep detail, I will just list the key areas I had to configure to get things working.

Hopefully this article will help point people in the right direction.

The Business Case

In our organization, we use many custom lists and logs to track data in our work process. End users usually request either a clean, printer-friendly version of that data, or specialized reporting against that data. To satisfy this requirement, I’ve created SSRS reports based on the SharePoint lists so we can have full control of design of the output (see my article on Connecting Reporting Services to SharePoint on how to create the reports).

The Challenge:

After migrating all of our data from SharePoint 2007, I got stuck with getting the reports to work. I kept running into permissions problems, until I my searches online pointed me to Microsoft Technet’s Security Overview for Reporting Services in SharePoint Integrated Mode. After staring at the comparison table almost at the bottom of this article, it gave me the clues necessary to know what settings to change.

Current Environment:

  • Single SharePoint 2010 Front End
  • Single SQL Server 2008 Back End
  • SharePoint security configured for Windows Authentication using NTLM
  • One instance of Reporting Services installed on the back end in SharePoint Integrated Mode
  • Developing with Visual Studio 2008
  • Environment resides in our company domain.

Specific Settings:

 

Reporting Services Configuration Manager on SQL Server Back End – Settings:

Service Account

Used main SP Service Account. This was a domain account, and I entered the value with domain qualification (domain\username)

Execution Account

Used main SP Service Account. This was a domain account, and I entered the value with domain qualification (domain\username)

Note: I know it is best practice to use a read-only account, but because this is an Intranet and the environment is secured, I use the same. I have attempted to use a separate report viewer service account with no luck.

Web Service URL

Take note of this URL for settings in SCA.

 

SharePoint Central Administration:
> General Application Settings > Reporting Services Integration

Note: Because the consultants who helped us set up SharePoint already configured Integrated Mode, I didn’t have to “Add a Report Server Integration”. I just simply went straight to the first link to configure it.

Report Server Web Service URL

Use the same one as noted in the Reporting Services Configuration Manager. On the RSCM, it had the port number listed as well, and you can omit this.

Authentication Mode

Trusted Account

Credentials

Used main SP Service Account. This was a domain account, and I entered the value with domain qualification (domain\username)

Activate the Reporting Services Feature

I checked “Activate feature in all existing site collections” before clicking OK.

 

Visual Studio 2008: Report Settings

I created a report project off VS2008’s new project wizard with all the default settings. Below are the report settings I configured so I can deploy a report to my SharePoint server.

Once the project is created, to get to the settings, I right-clicked on the Project in the solution Explorer, and chose Properties. From there I configured the values under the General properties.

OverwriteDataSources

True

TargetDataSourceFolder

URL to the folder for storing data sources.
http://mySPsite/Reports/DataSources

TargetReportFolder

URL to the folder for storing my report definitions
http://mySPsite/Reports/ReportDefinitions

TargetServerURL

Simply the root of the SharePoint Site
http://mySPsite

Once I configured these settings, it was as simple as right-clicking on the report I had designed in my solution explorer, and clicking deploy. Don’t forget to deploy the data source, too.

 

Not sure if this is a universal problem, but for TargetDataSourceFolder and TargetReportFolder make sure you do not put the trailing forward-slash at the end of the URL!

 

SharePoint 2010 Report Library: Data Source Settings

 

This part was the not-so-obvious trick that got me over the finish line!!!


I had to actually re-configure the data connection settings from SharePoint in order to get things to work! Before stumbling upon this, I had done a lot of trial and error with different settings in all the categories mentioned above.

In SharePoint, navigate to where the data source was deployed, and simply click on it to configure its settings:

Credentials Settings

Check Stored credentials

Enter the service account with access to view reports

Check Use as Windows credentials

The rest of the settings I left default.

 

The End Result:

After getting these settings configured, I simply navigated to my report via SharePoint, and the report rendered successfully! The beauty of it all is I didn’t have to re-create any of my reports. I just…

  • Added the report definitions previously created in VS2005.
  • Re-created data sources pointing to the SharePoint 2010 Lists.
  • Reconfigured each report to point to the new data sources pointing to SharePoint 2010 lists.

As we develop our SharePoint 2010 environment, we are looking for ways to keep the site alive and relevant. In a sense, to turn SharePoint from the Corporate Intranet stereotype of an online repository of business documents.

One feature that all of our regional office managers really liked was the ability to manage a single calendar in SharePoint for their respective region’s main events. With this request for a central calendar came the need for displaying a short list of upcoming events.

This article covers how I was able to accomplish this requirement without resorting to custom development or code. This was all done completely from the SharePoint web UI.

A Little History on the Business Case

In the beginning this seemed easy:

  • Let’s create a SharePoint 2010 Calendar
  • Let’s create a list view that only shows the next four events.
  • Let’s put that list view web part on the home page

Simple, right?

But there was a catch!

  • We wanted a very specific look and feel.
  • We wanted to have the Date show up as April 29, and not the default Start Time field of full date plus time stamp (4/29/2011 12:00 AM).

So the next step was to turn to a favorite hack of mine to get SharePoint to display what I want without resorting to custom code: Christophe’s HTML Calculated Columns JavaScript trick (see my article on Using Calculated columns to write HTML)

With this trick I proceeded to create a calculated column that converted the Start Time to the desired format of Month ##. I then created a custom view using the Standard View, with Expanded Recurring Events template.

As soon as I did this things looked great at first until I noticed two problems:

  1. For any recurring events, the calculated column only showed the first date of the event, not the current date on the repeating event.
  2. The custom view I created starts from the beginning of the first event date, and not the current date

Solution 1: getting dates to show up correctly

In order to get the correct date to show, I had to use the default Start Date column in my view. To address this column’s default display as full Date and Time, I injected some code into Christophe’s TextToHTML script to convert Date/Time values into the format I wanted.

  • First I added to the Javascript an isDate() function that I found here: http://www.codetoad.com/forum/17_10053.asp
  • I then added an else statement to Christophe’s TextToHTML Javascript as follows:

    function
    TextToHTML(NodeSet, HTMLregexp) {
    var
    CellContent = “”;
    var
    i=0;
    while
    (i < NodeSet.length){
    try
    {
    CellContent = NodeSet[i].innerText || NodeSet[i].textContent;
    if
    (HTMLregexp.test(CellContent)) {NodeSet[i].innerHTML = CellContent;}

    // Adding else if statement to convert regular dates to custom format of MONTHNAME Date (e.g. April 29)
    else if
    (isDate(CellContent)) {
        NodeSet[i].innerHTML = formatDateAsMonthDay(NodeSet[i].innerHTML);
    } //end else if for custome date format

    }
    catch(err){}
    i=i+1;
    }
    }

  • I created my own function to convert the date format (If anyone can help me with a RegEX that would do better than my several lines of REPLACE, that would be nice. I was able to identify that any Date/Time column got surrounded by the <NOBR> tags and since nothing else on my home page uses this tag, it was safe to use this as the unique identifier for my date formatting:

    function
    formatDateAsMonthDay(dateStr) {
        var
    formattedDate;
        var
    m_names = new
    Array(“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”);

        var
    DateString = dateStr;

        //get rid of the <NOBR> tag
        DateString = DateString.replace(“<NOBR>”,””);
        DateString = DateString.replace(“</NOBR>”,””);

        //Date is formatted as 4/29/2011 8:00 AM. This split will just get me the date w/o the time stamp.
        DateString = DateString.split(” “)[0];
        

        var
    d = new
    Date(DateString);
        var
    curr_date = d.getDate();
        var
    curr_month = d.getMonth();

        formattedDate = “<div class=’eeEventWorkaround_Date’>” + m_names[curr_month] + ” ” + curr_date + “</div>”;
        return
    formattedDate;
    }

By putting these three scripts together, I was able to utilize Christophe’s code, which scans the entire page for HTML and add the functionality to find any Date strings and format them accordingly.

Some potential problems with this are that the code is indiscriminant of what kind of content. If the first part of a cell of data in any table on the page has content of Date/Time format, it will get formatted. This worked in my case since the only Date/Time formatted content was in the list view of the event calendar

Solution 2: getting the list to show current events and not from the start.

To accomplish this, I did the following steps:

  • Created an EventTitleHTML Calculated Columns for custom display:
    • EventTitleHTML
      =”<div class=””_Event””>”&Title&”</div>”
      (note: the surrounding DIV tags is what the TextToHTML Javascript finds and causes the page to render the HTML instead of displaying the HTML tags as text)’
  • Created a Yes/No column called Show on Home Page.
  • Created a StartDate Calculated column that pulls the Start Time in Date format only (no time)
  • Created an EndDate Calculated column that pulls the End Time in Date format only (no time)
  • Created a custom view to display upcoming events. Read on for an explanation on this one.

I found a good article on Microsoft’s forum and was able to adapt it to my needs: filter Recurring Event by Start/End Time… By use of some creative filtering, I was able to get the view to show the upcoming 4 events – a good snapshot of what’s to come.

By following the steps in this article, I was able to get my list view to display what I needed. Rather than type out the steps, I’ll give you a screenshot of how my view was built. The only difference between my view and what was suggested in this post is the filter statements, since I wanted to give our office managers the ability to indicate whether a calendar event will display on the home page or not:

The only thing to note on the above image is that when I initially created this view, I used the Standard View, with Expanded Recurring Events, and then unchecked ALL but the Start Time and the Calculated Column. You see the Recurrence column still checked since this type of view cannot have the recurrence removed. It will automatically get re-checked once you save, but will subsequently not show up in the view when used in a web part.

The Result

Apply some CSS and here is the final result!

I hope this helps you or you can glean some tricks off this post!

Converting MOSS 2007 STP files to SharePoint 2010

Posted December 20th, 2010 by Panda

With our organization upgrading to SharePoint 2010, and me to migrate old data w/o much tools or support, here’s a tip for some manual migration:

So if you need to migrate list templates STP’s from SharePoint 2007 to 2010, you could try the steps below!

 

Below is direct quote from Tom’s Random Ranting (http://tom-dw.blogspot.com/2010/06/importing-sharepoint-2007-list.html)

The Gist of Tom’s ranting: Custom List definitions in SP 2007 and SP 2010 are the same! The only difference is a version number inside the file’s XML data that needs to be updated to trick SharePoint 2010 into thinking it is one of its own templates!

  1. Rename the original .STP to .CAB
  2. Extract its manifest.xml to a local folder (lets call it {workingfolder})
  3. Search for the ProductVersion element. This should have a value of 3
  4. Change its value to 4
  5. Repackage the manifest.xml into a .CAB. I’ve done this by using makecab.exe in the C:\Windows\System32 folder
  6. Syntax: makecab.exe {workingfolder}\manifest.xml {workingfolder}\{template-name}.cab
  7. Change the generated cabinet’s extension from .CAB back to .STP and upload it into the _catalogs/lt

Recently my company made the decision to upgrade to SharePoint 2010 and it has fallen on my shoulders to migrate all data. Not being a full time programmer/developer, I’ve made many “hacks” to SharePoint 2007 to make it behave the way our executives wanted. One of them being a SharePoint calendar tied to LOB data, and displayed in a very specific way.

Thanks to Christophe of PathToSharePoint.com, I was able to put together a fantastic win with his HTML Calculated Columns to customize display of list and calendar items!

This post is more to document my experimentation with Christophe’s updated version of the HTML Calculated Columns to work in SharePoint 2010.

My current roadblock is how I might modify the height of each calendar item.

Below is how it looks in SharePoint 2007:

Below is a marked up image of a test calendar in SharePoint 2010:

I’m not well versed on the Master Pages or CSS of a SharePoint 2010 layout, but I believe I just need a few more hacks to get the calendar items to show up appropriately.

Updates to come once I figure this out…

I have a vanilla install of Windows Server 2008 Standard R2, and was attempting to set it up as my Database Back End for SharePoint 2010. Unfortunately, at the end of a lengthy install, I get the error message from SQL Server Install (SQL Server 2008 Standard) that the install of Reporting Services failed and stating “Input string was not in a correct format when installing SQL Server 2008 with Reporting Services”. After many google searches, I decided to just try one solution hinted at by a post on ondrejsv BLOG.

Input string was not in a correct format when installing SQL Server 2008 with Reporting Services – ondrejsv BLOG

Although this post was referring to Windows 7, I was completely out of any more ideas. And since I was building this in a VMWare environment, let’s just take a snapshot and go!

This article pointed at problems with performance counters in the performance monitor causing the SQL install to fail. After trying this fix on my Windows Server system, my SQL Server 2008 with RS Install was successful!

Here are the steps I took, or you can read the above blog post for more detailed info:

  1. Made sure my Windows Server was up to date.
  2. Start > Run > perfmon – just to verify that, indeed, the performance counters were not working.
  3. Start > right-click on Command Prompt, and ran as Admin.
  4. Typed in lodctr /R to manually rebuild the performance counters.

After about 45 seconds, the command prompt window reported a successful rebuild.

Now on to installing SQL Server 2008 w/ RS… (30 minutes later…) and it was a success! I was also successful in getting Service Pack 1 installed as well.

I hope this post helps!

Just recently some end users noted that subsites on our SharePoint (WSS 3.0) simply gave the end user the following error:

Even my Central Admin was down with the same problem!

After modifying my web.config to allow more detailed error, (I did this first on my SCA to avoid showing detailed errors to end users) I get the following when trying to navigate to SharePoint Central Admin:
(Here’s an article with step-by-step on how to accomplish this: http://blog.fpweb.net/troubleshooting-sharepoint-file-not-found-error/)

OK so if you don’t want to read all my documented steps, here’s the gist of it all. Down below you can read what hell I went through to try to fix this problem!

Problem:

Many SharePoint Sites and Sub-sites in WSS 3.0, including SharePoint Central Admin, simply did not work. The error was a generic File Not Found error. Users were not able to see their SharePoint site.

Cause:

When using SharePoint Designer from the server to edit the Master Page in the 12 hive, it performs some auto-correction in the register statements at the top of the file. The root website designator (“~/”) in the file location for some of the ASCX pages was removed.

Solution:

I opened up the default.master in the 12 hive with Notepad and added back in the “~/” into the path for the ASCX pages.

Lesson Learned:

Watch out when using SharePoint designer on local files!

Read more »