SSRS DateTime Parameters DefaultValue Format

Question

I have an asp.net application that allows users to create subscriptions for SSRS reports.

All my reports have @StartDate and @EndDate parameters which have default values of

=Today.AddDays(-14) 

and

=Today 

respectively.

My c# code takes checks the defaultvalue of the parameters when loading the subscription form, to pre-populate AspxDateEdit controls. This all works fine on my dev environment (NZ).

        ItemParameter[] Parameters = _Rs.GetItemParameters(_ThisReportPath, null, true, null, null);
        if (Parameters.Any())
        {
            foreach (ItemParameter Rp in Parameters)
            {
                if (Rp.ParameterTypeName == "DateTime" && Rp.PromptUser)
                {
                    var MyDtControl = new ASPxDateEdit
                    {
                        ID = "dtParam" + MyTable.Rows.Count
                    };
                    DateTime MyFormattedDisplayDate = Convert.ToDateTime(Rp.DefaultValues[0], CultureInfo.CurrentCulture.DateTimeFormat);
                    MyDtControl.Date = MyFormattedDisplayDate.Date;
                }
            }
        }

The initial website will be residing on a server in Australia, with subsequent rollouts to various countries around the world, so I need to keep the format of DateTimes dynamic (Culture specific).

When loading the subscription form for a report on the live system I get the error “String was not recognized as a valid DateTime.” Dumping the values of the Parameter.DefaultValues[0] shows that the default value on the AU server is in US form (MM/dd/yyyy) which is causing the errors. Logging code:

SQLReportsLogic.Log(MyLog, "(Line 599 - LoadParams)Default Param Value:" + Rp.DefaultValues[0] + "/ ServerDateFormatToday:" + DateTime.Now);

Resulting output:

(Line 599 - LoadParams)Default Param Value:3/24/2015 12:00:00 AM/ ServerDateFormatToday:24/03/2015 7:14:47 AM

The format is expected to align with the culture info of the server which would be dd/MM/yyyy.

-   The report language is set to User!Language
-   The Server System Locale is English(Australia)
-   The Server Location is Australia
-   The Server Datetime format is set to ShortDate=d/MM/yyyy LongDate=dddd, d MMMM yyyy
-   The Server Language is set to English (Australia)
-   The Reporting Services Service is running under Local System Account
-   WWW is running under Local System Account
-   SSRS is running in Native Mode

Where specifically does SSRS get the DateTime Format from when determining the format for =Today()? As I understand my research to date, this should be generating =Today() in the system assigned format of dd/MM/yyyy I have also tried formatting the DefaultValue of the parameters to

-   “yyyy/MM/dd hh:mm:ss” = The DefaultValue expression for the report parameter ‘StartDate’ contains an error: Input string was not in a correct format
-   “yyyy/MM/dd hh:mm” = The DefaultValue expression for the report parameter ‘StartDate’ contains an error: Input string was not in a correct format
-   “yyyy/MM/dd” = The DefaultValue expression for the report parameter ‘StartDate’ contains an error: Input string was not in a correct format
-   DateFormat.ShortDate = The property ‘DefaultValue’ of report parameter ‘StartDate’ doesn’t have the expected type.
-   DateFormat.LongDate = The property ‘DefaultValue’ of report parameter ‘StartDate’ doesn’t have the expected type.
-   DateFormat.GeneralDate = The property ‘DefaultValue’ of report parameter ‘StartDate’ doesn’t have the expected type.

It would seem that unless I can resolve the way SSRS renders =Today() that my hands are tied. As I understand it I need to find the issue with the SSRS installation using the incorrect Culture? Is it looking it up in the registry somewhere?

I am using VS2012 (11.0.61030.00 Update 4) with SSDTBI Plugin (MSSQL Server Reporting Services Designers Version 11.0.3436.0)

Answer 1

Finally found the right search terms to use and found a solution after two days - to override the SSRS GetWebRequest as described here.. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f147c641-d5c2-49e8-97f6-b654bec8366d/datetime-format-for-webservice-api-calls?forum=sqlreportingservices#0c223835-dd5c-4471-b736-1d9dad014144

public partial class ReportingService : DevReportService.ReportingService2010
{

    /// <summary>

    /// Gets or sets the culture that is used when generating the report.

    /// </summary>

    /// <value>The culture that is used when generating the report.</value>

    public CultureInfo Culture { get; set; }

    protected override WebRequest GetWebRequest(Uri uri)
    {

        WebRequest request = base.GetWebRequest(uri);

        CultureInfo culture = this.Culture ?? CultureInfo.CurrentCulture;

        request.Headers.Add(HttpRequestHeader.AcceptLanguage, culture.Name);

        return request;

    }

}