I want to change the Datetime parameter to DDMMYYYY format.its default value is MMddYYYY.when user will select date from date picker the date should be display in ddmmyyyy format. I tried all expression like
=Format(Parameters!DateFrom.Value, "dd/MM/yy")
=cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))
=cdate(format(DateAdd("d", -61, now),"dd/MM/yyyy"))
but all doesnt work.
Necromancing.
Yes, you can - sort of.
First, notice that SSRS takes the date format from the language that is specified in your browser.
So you could just change the language of the browser. Obviously, you don't wanna tell your users to do that.
So you pass an additional parameter into your report:
I called it in_sprache (Sprache means language in German, with possible values "DE, "FR", "IT", "EN").
Now you need to change the localization process, by overriding the virtual method "InitializeCulture" in ReportViewer.aspx.
You can find ReportViewer in
C:\Program Files\Microsoft SQL Server\MSRS<Version>.MSSQLSERVER
C:\Program Files\Microsoft SQL Server\MSRS<Version>.<Instance>
e.g.
C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER
There you add (in the source-code of the /ReportServer/Pages/ReportViewer.aspx):
<script type="text/C#" runat="server">
protected override void InitializeCulture()
{
string sprache = System.Web.HttpContext.Current.Request.QueryString["in_sprache"];
if(string.IsNullOrEmpty(sprache))
sprache = "";
switch(sprache.ToLowerInvariant())
{
case "de":
sprache = "de-CH";
break;
case "fr":
sprache = "fr-CH";
break;
case "it":
sprache = "it-CH";
break;
case "en":
sprache = "en-US";
break;
default:
sprache = "";
break;
}
// System.Web.HttpContext.Current.Response.Write(sprache);
if(!String.IsNullOrEmpty(sprache))
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture(sprache);
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(sprache);
}
base.InitializeCulture();
}
</script>
This will override the browser-user-language with the one specified in the url-parameter in_sprache (in_sprache must be a parameter of your report).
Now, you must also override context.request.userLanguages for the datepicker to work properly... you can do so only by adding a HTTP-Module (libRequestLanguageChanger.dll) into the web.config of ReportServer
<system.web>
[...]
<httpModules>
[...]
<add name="RequestLanguageChanger" type="libRequestLanguageChanger.RequestLanguageChanger, libRequestLanguageChanger" />
</httpModules>
[...]
</system.web>
. (Requires changing trust-level from rosetta to "Full", unless you can figure out how to change the rosetta-policy to allow this http-module).
Since we can also override InitializeCulture in the HTTP-Module, you don't really have to add the runat="server" script to ReportViewer.aspx.
namespace libRequestLanguageChanger
{
public class RequestLanguageChanger : System.Web.IHttpModule
{
void System.Web.IHttpModule.Dispose()
{
// throw new NotImplementedException();
}
void System.Web.IHttpModule.Init(System.Web.HttpApplication context)
{
// https://stackoverflow.com/questions/441421/httpmodule-event-execution-order
context.BeginRequest += new System.EventHandler(context_BeginRequest);
}
void context_BeginRequest(object sender, System.EventArgs e)
{
System.Web.HttpApplication application = sender as System.Web.HttpApplication;
System.Web.HttpContext context = application.Context;
if (context.Request != null)
{
// string language = context.Request.Headers["Accept-Language"];
string language = null;
// string url = context.Request.RawUrl;
// string referrer = null;
if (context.Request.UrlReferrer != null)
{
// referrer = context.Request.UrlReferrer.OriginalString;
string queryString = context.Request.UrlReferrer.Query;
System.Collections.Specialized.NameValueCollection queryStrings = System.Web.HttpUtility.ParseQueryString(queryString);
language = queryStrings["in_sprache"];
}
if(context.Request.QueryString["in_sprache"] != null)
language = context.Request.QueryString["in_sprache"];
if (!string.IsNullOrEmpty(language))
{
language = language.ToLowerInvariant();
switch (language)
{
case "de":
language = "de-CH";
break;
case "fr":
language = "fr-CH";
break;
case "it":
language = "it-CH";
break;
case "en":
language = "en-US";
break;
default:
language = "";
break;
}
} // End if (!string.IsNullOrEmpty(sprache))
// SQL.Log(url, referrer, sprache);
// Simulate Browser-Language = in_sprache
if (!string.IsNullOrEmpty(language))
{
// context.Request.Headers["Accept-Language"] = language;
System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo(language);
System.Threading.Thread.CurrentThread.CurrentCulture = culture;
System.Threading.Thread.CurrentThread.CurrentUICulture = culture;
if (context.Request.UserLanguages != null)
{
// System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo(context.Request.UserLanguages[0]);
for (int i = 0; i < context.Request.UserLanguages.Length; ++i)
{
// context.Request.UserLanguages[i] = "en-US";
context.Request.UserLanguages[i] = language;
} // Next i
} // End if (context.Request.UserLanguages != null)
} // End if (!string.IsNullOrEmpty(language))
} // End if (context.Request != null)
} // End Sub context_BeginRequest
} // End Class
} // End Namespace
And there you are, ReportServer with a "custom"-culture date-format, without having to tell the user to change the browser-language.
You can't. The closest I can think of is to set an expression on the language property of the report to =User!Language, this will at least display in the users system date format.
You dont even need to set the language to user!Language. SSRS will pick up the DateFormat from the local users regional settings.
But obviously you dont have a lot of control over this and if your customer is screaming for this format, they may not have their PC set with these settings?
good luck!
This report belows' language is set to en-au (which is dd/mm/yyyy)
Following on from the suggestion by Trubs, I set the language in the browser (Chrome this time) and it formatted the values correctly. I changed language from English (US), which uses the mm/dd/yy format to English (Australia), which uses the dd/mm/yy format. Certainly we couldn't have every user changing their browser setting but DevOps could and should definitely set the language for all browsers in their organization to the correct language for their country I would guess?
