How do I use a parameter in a custom report?

You have to use the specific parameter in the SQL query for the drilldown report. If a parameter is passed to a report, Harvest will add the parameter @Param (and also @Param2 if there is a second parameter) to the SqlCommand.Parameters before executing. Similarly, it adds @StartDate and @EndDate as well, so you can use these in your query to properly reflect what is selected by the date control.

For example, in our Blog Posts by Section report, the section number is passed in as a parameter. Here is the query used by this DrillDown report that uses @StartDate, @EndDate, and @Param:

Query = @"SELECT $top$
    PostId,
    PostTitle AS [Post],
    PublishDt AS [Date]
FROM csr_BlogPostData p
WHERE PublishDt >= @StartDate AND PublishDt < @EndDate
AND p.SectionId = @Param
ORDER BY PublishDt DESC";

Hope this helps,

- Andrew

To accomplish these things, you actually have to override some specific functions of the report.

Here's an example of overriding the title to turn a number parameter into its corresponding record name. You will need a similar query to turn the id into the record name:

public override string FormatTitle()
        {
            if (ReportingContext.Current == null) //i.e: an Email or Embedded report
            {
                return base.FormatTitle();
            }

            string param = Library.Helper.FormatForTitle("param");

            SerializableDictionary<string, object> p = new SerializableDictionary<string, object>();
            int paramInt;
            bool isInt = Int32.TryParse(param, out paramInt);

            if (isInt)
            {
                string query = string.Format(@"EXECUTE AS USER = '{0}'
SELECT BlogTitle FROM csr_BlogPostData WHERE SectionId = @ParamInt
REVERT", ReportingContext.Current.ReportParameters.ReportSite.DatabaseUser);

                p.Add("@ParamInt", paramInt);

                System.Data.DataTable dt = ReportManager.ExecuteReportWithAdhocQuery(this, query, p);

                if (dt.Rows.Count > 0)
                {
                    param = dt.Rows[0]["BlogTitle"].ToString();
                }
            }

            return string.Format("{0} for {1}", DisplayName, param);
        }

...basically execute an additional query using ReportManager.ExecuteReportWithAdhocQuery, then return a new Title string using the value returned. Most of the other things in this method are checks to catch any potential problems the title override might cause.

I'll write another post soon to address creating a scorecard report.

- Andrew

Building a Scorecard Report:

Tables are formatted by passing a StringBuilder through several formatting functions for the various steps. You can override the formatting at any of these steps:

BeginTableRow
FormatReportTableHeader
FormatReportTableRow
FormatReportTableFooter
EndTableRow

Most of the time you can accomplish whatever table manipulation you need to by just using FormatReportTableHeader and FormatReportTableRow. To create a scorecard report, you will need to override FormatReportTableRow. For each row returned in the data table for this report, Harvest will call this method to apply any formatting changes to it.

In the case of scorecard reports like the one you mentioned, it is accomplished by writing the SQL query to return EXACTLY 1 ROW, then applying html formatting inside the table row with FormatReportTableRow.

Here's the formatting for the BlogPostSummary report you mentioned. Notice that the FormatReportTableHeader is overridden to return nothing, so that the table can be declared and styled inside the FormatReportTableRow:

public override void FormatReportTableHeader(object dataSource, StringBuilder sb)
        {
        }

public override void FormatReportTableRow(DataRow currentRow, bool isAlternateItem, int counter, StringBuilder sb)
        {
            int commentCount = Int32.Parse(currentRow["Comments"].ToString());
            int views = Int32.Parse(currentRow["Views"].ToString());
            int RSSviews = Int32.Parse(currentRow["RSS Views"].ToString());
            int totalViews = views + RSSviews;
            int trackbacks = Int32.Parse(currentRow["Trackbacks"].ToString());

            sb.AppendFormat(
                    @"
<table class=""ReportingGrid"" border=""1"" cellpadding=""3"" cellspacing=""0"" rules=""all"">
<tr>
<td>
<div style=""width: 48%; float: left; padding:3px 5px 3px;"">
<b class=""formatted"">{8}</b><br />
<b class=""formatted"">{6}</b><br />
<b class=""formatted"">{1}</b> web views <br />
<b class=""formatted"">{2}</b> RSS views<br />
<b class=""formatted"">{5}</b> total views<br />
</div>
<div style=""width: 48%; float: right; padding:3px 5px 3px;"">
Posted by <b class=""formatted"">{9}</b><br />
Posted on <b class=""formatted"">{7}</b><br />
<b class=""formatted"">{0}</b> comment(s)<br />
<b class=""formatted"">{3}</b> trackbacks <br />
<span style=""line-height: 2.5em;"">Rating: </span><b class=""formatted"">{4}</b>
</div>
</td>
</tr>
</table>"
                    , Library.Helper.FormatObject(commentCount)
                    , Library.Helper.FormatObject(views)
                    , Library.Helper.FormatObject(RSSviews)
                    , Library.Helper.FormatObject(trackbacks)
                    , Library.Helper.FormatObject(currentRow["Rating"])
                    , Library.Helper.FormatObject(totalViews)
                    , currentRow["Post Title"]
                    , Library.Helper.FormatObject(currentRow["Publish Date"])
                    , currentRow["Blog Title"]
                    , currentRow["User"]);
                return;
        }

...there are a number of style classes you can make use of, or you can add your own to screen.css. You might want to poke around with view source on some of the formatted reports to see the specific styles they are using.

- Andrew