Creating custom rollup reports with Date Range filters using Data View Web Part

On a recent MOSS 2007 engagement, we were asked to deliver an accounting report. The report had to aggregate data from multiple form libraries and provide the ability to the user to specify a date range for filtering. This would have been easy to build using a specialized tool like SSRS but not a lot of information is available around filtering rollup data in Data View Web part . This article will describe generating the report using OOB Data View Web part.

As part of this solution, we will learn how to:

• Use a DataView web part to display data from a list
• Use multiple data sources to provide an aggregated (roll up ) report
• Provide the ability to filter results for a specific data range by using SharePoint DateTime controls and Data View parameters.

Let’s start by creating 2 custom lists on your site: SourceList1 and SourceList2.

For this exercise, I will keep the structure simple and create the lists with the following 3 columns:

1. Title
2. Approved Date
3. Amount

  Populate the lists with some dummy values, here is what my lists look like.

Step 1: Using a Data View Web Part in SharePoint Designer to display roll up list data (from multiple lists)

1. Add a new page to your site.
2. Open the new page in SPD.
3. Click on “Click to insert a web part”
4. From the Top Menu, select Data View –> Insert Data View…
5. In the Data Source Library on the right pane, click on the drop down next to SourceList1 and select “Link to another Data Source”. (Note: to display data from a single list, select Show Data from the menu) 
   
   
6. Select “Configure Linked Source… and in the Link Data Sources Wizard, select SourceList2. 
   
   
7. Click Next and select Merge on the next screen and Click Finish.
8. Back in the Data Source Properties, navigate to the General Tab and give the Linked Data Source a Name (For e.g. Merged Source)
9. The “Merged Source” Data source should show up on the Data Source pane at the bottom. Select “Show Data” from the actions menu for the data source.
10. Select the columns you want in the report and drop them in the DataFormWebPart on the page.
11. All the rows from both lists should be displayed. 
    
12. Click on the icon to bring up the Common Data View Tasks. You can make changes to the Data View from here like adding / removing fields, applying a sort or adding filtering. We will revisit the Filter option in the next step. 
    
    
13. Save your page and navigate to it from your browser. You should see your roll-up report from the 2 lists. 
    
    
14. We need to get rid of the time that shows up with the Date as we are only interested in the data part.
15. Select the field in the Data View Web Part and bring up the Common xsl tasks menu by clicking on the button. Select “DataTime formatting options…” link and uncheck the “Show Time” check box. 
    
     

Step 2: Adding Date Range filters to the report using Data View Web Part parameters.

Background: SharePoint provides a lot of ways to sort / filter list data and the same functionality is available in views created with Data View Web part. As you probably noticed in the report, this functionality is not turned on by default. To turn on the filtering capability, bring up the Common Data View Tasks menu and in the Data View Properties, check the checkbox for “Enable sorting and filtering on column headers (basic table layout only)”.

This doesn’t fulfill our requirement though. We need the ability to filter the report based on a user accepted date range. If we wanted to filter the report for a specific value, we could have used one of the OOB filter web parts (For e.g Date Filter, Text Filter or SharePoint List Filter). Here are the filters available OOB in SharePoint.

Let’s see the steps to add the Date Range filtering to the report. We will be using the SharePoint DateTime server control to accept the date value from the user. (Note: We can use the standard HTML text box control as well but using the DateTime server control gives us the nice calendar to select the date from instead of manually typing the date in.)

1. Switch to Split view in SPD
2. Locate the DateTimeControl in the toolbox and drag drop the control in the code view before the opening “WebPartPages:WebPartZone” tag. Change to id to StartDateCtl. 
    
                                                   
3. Select the Control in the Design view, and set the DateOnly property to true in the Tag Properties pane.
4. Add another DateTime control and change the name to EndDateCtl and set the DateOnly property to true.

5. Add a HTML Input (Submit) form control

6. Save the form and preview. Here is what the form should look like (I used some table layouts here, your form might look a little different) 
   

7. Now that we have the Date Range fields created, we just have to wire them up with our Date View Web part.

8. Before we make the changes to the DataView web part, check the source for the aspx page by selecting “View Source” and locate the DateTime control in the HTML. We need to make a note of the control names; we will need them when we create the parameters. For my form they are set to: 
   ctl00$PlaceHolderMain$StartDateCtl$StartDateCtlDate and
   ctl00$PlaceHolderMain$EndDateCtl$EndDateCtlDate

 

1. In your SPD, bring up the Common Data View Tasks, click on Parameters… and add 2 new parameters called StartDate and EndDate with parameter source set to Form. Paste the control name in the Form Field text box. 
   
   

2. Add the Filter condition to: ApprovedDate <Greater Than or Equal> StartDate AND ApprovedDate <Less Than or Equal> EndDate 
   
   

3. For a simple string filter, that would be the last step. However the dates work differently. The date control saves the date in mm/dd/yyyy format whereas the xslt filter is expecting the date in YYYY-MM-DDTHH:MM:SS format. (For more information about this issue, check out Andy Lewis’ msdn blog entry here)

4. Luckily for us, Andy Lewis also provides us with an xsl file (date_templates.xsl) that we can use to convert the date to the correct format. I have also copied the file here in case the referenced blog entry is no longer available. This xsl provides many capabilities including the capability to convert a date to an ISO format (convertCalcDateValue)

5. Copy the convertCalcDateValue xsl template from the xsl file and paste it before the first xsl:template tag in your page.

6. Locate the following lines in your code:
   <xsl:variable name="dvt_StyleName">Table</xsl:variable>
   <xsl:variable name="Rows" select="/dsQueryResponse/Rows…

7. Add the following code between the 2 lines:
   <xsl:variable name="StartDate_ISO">
      <xsl:call-template name="convertCalcDateValue">
      <xsl:with-param name="paramDate" select="$StartDate"/>
      </xsl:call-template>
   </xsl:variable> 
   <xsl:variable name="EndDate_ISO">
      <xsl:call-template name="convertCalcDateValue">
      <xsl:with-param name="paramDate" select="$EndDate"/>
      </xsl:call-template>
   </xsl:variable> 
   
   These lines will create 2 new variables StartDate_ISO and EndDate_ISO and save the converted StartDate and EndDate values respectively. Next we have to change the filter condition to use these new variables.

8. Change the reference in the <xsl:variable name=”Rows” select=”dsQueryResponse/Rows… to StartDate_ISO and EndDate_ISO, so the line looks like:
   
   <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[number(translate(substring-before(@ApprovedDate,'T'),'-','')) &gt;= number(translate(substring-before($StartDate_ISO,'T'),'-','')) and number(translate(substring-before(@ApprovedDate,'T'),'-','')) &lt;= number(translate(substring-before($EndDate_ISO,'T'),'-',''))]"/>

9. Save the page and test out your page in the browser.