Skip to Content
author's profile photo Former Member
Former Member

Date format for report filter in WEBI

Hi, all.

I have unx universe that connected to Microsoft SQL Server 2008. I want to build report on this universe, but I faced the problem, I need prompt on date field. When I start report with this prompt, appear alert "Query is emty". Then I watch sql script, and saw this

Select...

where [Date field] = "07/23/2013" (format is 'mm/dd/yyyy')

after I change this script to

Select...

where [Date field] = "07/23/2013" (format is 'mm/dd/yyyy')

report show normal data.

Anybody know how change date format in query filter?

Regards, Fanil.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 26, 2013 at 01:01 PM

    Hello Fanil,

    I think PRM is the right place for that modification:

    http://scn.sap.com/message/13818992#13818992

    Did you already test locally with the Rich Client and changed the local PRM file?

    BI4.0 SP02 was released some time ago, do you have any chance to test this in a recent version (e.BI4.0 SP06)?

    Best regards,

    Victor

    PS: I guess you want to change the date format, but both date formats are the same in your original post.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi, thanks for your explanations. But where can i find the parameter for sql server (ODBC) please ?

      <Parameter Name="USER_INPUT_DATE_FORMAT">'dd/mm/yyyy</Parameter>'

      Indeed when i open the file sqlsrv.prm on the BI4 server, i can't see this parameter.

      C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\ODBC\sqlsrv.prm

      <?xml version="1.0" encoding="UTF-8"?><DBParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../dbparameters.xsd">

      <Configuration>

      <Parameter Name="DB_TYPE">MS SQL Server</Parameter>

      <Parameter Name="ORDER_BY_REQUIRES_SELECT">NO</Parameter>

      <Parameter Name="GROUP_BY_SUPPORTS_COLUMN_INDEX">NO</Parameter>

      <Parameter Name="GROUP_BY_SUPPORTS_CONSTANT">NO</Parameter>

      <Parameter Name="JOIN">YES</Parameter>

      <Parameter Name="INNER_JOIN">INNER JOIN</Parameter>

      <Parameter Name="EXT_JOIN">YES</Parameter>

      <Parameter Name="UNION">UNION</Parameter>

      <Parameter Name="UNION_ALL">YES</Parameter>

      <Parameter Name="UNION_IN_SUBQUERY">YES</Parameter>

      <Parameter Name="INTERSECT">INTERSECT</Parameter>

      <Parameter Name="INTERSECT_ALL">NO</Parameter>

      <Parameter Name="INTERSECT_IN_SUBQUERY">YES</Parameter>

      <Parameter Name="MINUS">EXCEPT</Parameter>

      <Parameter Name="MINUS_ALL">NO</Parameter>

      <Parameter Name="MINUS_IN_SUBQUERY">YES</Parameter>

      <Parameter Name="SELECT_SUPPORTS_NULL">YES</Parameter>

      <Parameter Name="RANK_SUPPORTED">YES</Parameter>

      <Parameter Name="PERCENT_RANK_SUPPORTED">NO</Parameter>

      <Parameter Name="CONSTANT_SAMPLING_SUPPORTED">YES</Parameter>

      <Parameter Name="SEED_SAMPLING_SUPPORTED">NO</Parameter>

      <Parameter Name="ANALYTIC_CLAUSE"></Parameter>

      <Parameter Name="ANALYTIC_FUNCTIONS"></Parameter>

      <Parameter Name="FULL_EXT_JOIN">YES</Parameter>

      <Parameter Name="LEFT_EXT_JOIN">YES</Parameter>

      <Parameter Name="LEFT_OUTER"></Parameter>

      <Parameter Name="RIGHT_EXT_JOIN">YES</Parameter>

      <Parameter Name="RIGHT_OUTER"></Parameter>

      <Parameter Name="GROUP_BY_SUPPORTS_COMPLEX">YES</Parameter>

      <Parameter Name="GROUP_BY">YES</Parameter>

      <Parameter Name="HAVING">YES</Parameter>

      <Parameter Name="DISTINCT">YES</Parameter>

      <Parameter Name="ORDER_BY">YES</Parameter>

      <Parameter Name="ORDER_BY_SUPPORTS_COLUMN_INDEX">YES</Parameter>

      <Parameter Name="LIKE_SUPPORTS_ESCAPE_CLAUSE">YES</Parameter>

      <Parameter Name="SUBQUERY_IN_FROM">YES</Parameter>

      <Parameter Name="SUBQUERY_IN_IN">YES</Parameter>

      <Parameter Name="SUBQUERY_IN_WHERE">YES</Parameter>

      <Parameter Name="CALCULATION_FUNCTION">YES</Parameter>

      </Configuration>

      <DateOperations>

      <DateOperation Name="YEAR">{fn year($D)}</DateOperation>

      <DateOperation Name="QUARTER">datepart(qq,$D)</DateOperation>

      <DateOperation Name="MONTH">{fn month($D)}</DateOperation>

      </DateOperations>

      <Operators>

      <Operator Arity="1" ID="ADD" Type="Numeric">+</Operator>

      <Operator Arity="1" ID="SUBSTRACT" Type="Numeric">-</Operator>

      <Operator Arity="1" ID="MULTIPLY" Type="Numeric">*</Operator>

      <Operator Arity="1" ID="DIVIDE" Type="Numeric">/</Operator>

      <Operator Arity="0" ID="NOT" Type="Logical">NOT</Operator>

      <Operator Arity="1" ID="OR" Type="Logical">OR</Operator>

      <Operator Arity="1" ID="AND" Type="Logical">AND</Operator>

      <Operator Arity="0" ID="NOT_NULL" Type="Logical">IS NOT NULL</Operator>

      <Operator Arity="0" ID="NULL" Type="Logical">IS NULL</Operator>

      <Operator Arity="1" ID="SUP" Type="Logical">&gt;=</Operator>

      <Operator Arity="1" ID="INF" Type="Logical">&lt;=</Operator>

      <Operator Arity="1" ID="EQUAL" Type="Logical">=</Operator>

      <Operator Arity="1" ID="DIFF" Type="Logical">&lt;&gt;</Operator>

      <Operator Arity="1" ID="STRICT_SUP" Type="Logical">&gt;</Operator>

      <Operator Arity="1" ID="STRICT_INF" Type="Logical">&lt;</Operator>

      <Operator Arity="1" ID="IN_LIST" Type="Logical">IN</Operator>

      <Operator Arity="1" ID="NOT_IN_LIST" Type="Logical">NOT IN</Operator>

      <Operator Arity="1" ID="MATCH" Type="Logical">LIKE</Operator>

      <Operator Arity="1" ID="NOT_MATCH" Type="Logical">NOT LIKE</Operator>

      <Operator Arity="2" ID="BETWEEN" Type="Logical">BETWEEN AND</Operator>

      <Operator Arity="2" ID="NOT_BETWEEN" Type="Logical">NOT BETWEEN AND</Operator>

      </Operators>

      <Functions>

      <Function Group="False" ID="Substring" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="Numeric"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>substring($1,$2,$3)</SQL>

      </Function>

      <Function Group="False" ID="Uppercase" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>upper($1)</SQL>

      </Function>

      <Function Group="False" ID="Lowercase" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>lower($1)</SQL>

      </Function>

      <Function Group="False" ID="Rightpart" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>right($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="LeftRemove" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>ltrim($1)</SQL>

      </Function>

      <Function Group="False" ID="Absolute" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>abs($1)</SQL>

      </Function>

      <Function Group="False" ID="Round" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>round($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Sqrt" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>sqrt($1)</SQL>

      </Function>

      <Function Distinct="True" Group="True" ID="Minimum" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="All"></Argument>

      </Arguments>

      <SQL>min($1)</SQL>

      </Function>

      <Function Distinct="True" Group="True" ID="Maximum" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="All"></Argument>

      </Arguments>

      <SQL>max($1)</SQL>

      </Function>

      <Function Distinct="True" Group="True" ID="Average" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>avg($1)</SQL>

      </Function>

      <Function Distinct="True" Group="True" ID="Sum" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>sum($1)</SQL>

      </Function>

      <Function Distinct="True" Group="True" ID="Count" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="All"></Argument>

      </Arguments>

      <SQL>count($1)</SQL>

      </Function>

      <Function Group="False" ID="Ceil" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>ceiling($1)</SQL>

      </Function>

      <Function Group="False" ID="Floor" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>floor($1)</SQL>

      </Function>

      <Function Group="False" ID="DateExtract" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>Datename($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Arc_cosine" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>acos($1)</SQL>

      </Function>

      <Function Group="False" ID="Arc_sine" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>asin($1)</SQL>

      </Function>

      <Function Group="False" ID="Arc_Tangent" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>atan($1)</SQL>

      </Function>

      <Function Group="False" ID="Angle_Tangent_2" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>atan2($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Cosine" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>cos($1)</SQL>

      </Function>

      <Function Group="False" ID="Sine" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>sin($1)</SQL>

      </Function>

      <Function Group="False" ID="Tangent" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>tan($1)</SQL>

      </Function>

      <Function Group="False" ID="Degrees" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>degrees($1)</SQL>

      </Function>

      <Function Group="False" ID="Exp" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>exp($1)</SQL>

      </Function>

      <Function Group="False" ID="Log" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>log($1)</SQL>

      </Function>

      <Function Group="False" ID="Log10" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>log10($1)</SQL>

      </Function>

      <Function Group="False" ID="Power" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>power($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Radians" InMacro="False" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>radians($1)</SQL>

      </Function>

      <Function Group="False" ID="Sign" InMacro="False" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>sign($1)</SQL>

      </Function>

      <Function Group="False" ID="Random" InMacro="False" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>rand($1)</SQL>

      </Function>

      <Function Group="False" ID="Pi" InMacro="False" Type="Numeric">

      <SQL>pi()</SQL>

      </Function>

      <Function Group="False" ID="ASCII_code" InMacro="False" Type="String">

      <Arguments>

      <Argument Type="Char"></Argument>

      </Arguments>

      <SQL>ascii($1)</SQL>

      </Function>

      <Function Group="False" ID="Character" InMacro="False" Type="String">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>char($1)</SQL>

      </Function>

      <Function Group="False" ID="Charindex" InMacro="False" Type="Numeric">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>charindex($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Difference" InMacro="False" Type="Numeric">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>difference($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Patindex" InMacro="False" Type="Numeric">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>patindex($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Replicate" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>replicate($1,$2)</SQL>

      </Function>

      <Function Group="False" ID="Reverse" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>reverse($1)</SQL>

      </Function>

      <Function Group="False" ID="Rtrim" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>rtrim($1)</SQL>

      </Function>

      <Function Group="False" ID="Soundex" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>soundex($1)</SQL>

      </Function>

      <Function Group="False" ID="Space" InMacro="False" Type="String">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>space($1)</SQL>

      </Function>

      <Function Group="False" ID="Str" InMacro="False" Type="String">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>str($1)</SQL>

      </Function>

      <Function Group="False" ID="Stuff" InMacro="False" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="Numeric"></Argument>

      <Argument Type="Numeric"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>stuff($1,$2,$3,$4)</SQL>

      </Function>

      <Function Group="False" ID="Conversion" InMacro="False" Type="String">

      <SQL>Convert()</SQL>

      </Function>

      <Function Group="False" ID="Cotangent" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>{fn cot($1)}</SQL>

      </Function>

      <Function Group="False" ID="Mod" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>{fn mod($1,$2)}</SQL>

      </Function>

      <Function Group="False" ID="Concat" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>{fn concat($1,$2)}</SQL>

      </Function>

      <Function Group="False" ID="Left" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>{fn left($1,$2)}</SQL>

      </Function>

      <Function Group="False" ID="Length" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>{fn length($1)}</SQL>

      </Function>

      <Function Group="False" ID="Current_date" InMacro="True" Type="DateTime">

      <SQL>convert (SMALLDATETIME, {fn CURDATE()})</SQL>

      </Function>

      <Function Group="False" ID="Day_of_week" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>{fn dayofweek($1)}</SQL>

      </Function>

      <Function Group="False" ID="Day_of_month" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>{fn dayofmonth($1)}</SQL>

      </Function>

      <Function Group="False" ID="Day_of_year" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>{fn dayofyear($1)}</SQL>

      </Function>

      <Function Group="False" ID="Number_of_the_week" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>{fn week($1)}</SQL>

      </Function>

      <Function Group="False" ID="Number_of_the_month" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>{fn month($1)}</SQL>

      </Function>

      <Function Group="False" ID="Year" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>{fn year($1)}</SQL>

      </Function>

      <Function Group="False" ID="Quarter" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>datepart(qq,$1)</SQL>

      </Function>

      <Function Group="False" ID="Locate" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>{fn locate($1,$2)}</SQL>

      </Function>

      <Function Group="False" ID="IsNull" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="All"></Argument>

      </Arguments>

      <SQL>isnull($1)</SQL>

      </Function>

      <Function Group="False" ID="Character_prompt" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>@prompt($1,'A',,,)</SQL>

      </Function>

      <Function Group="False" ID="Numeric_prompt" InMacro="True" Type="Numeric">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>@prompt($1,'N',,,)</SQL>

      </Function>

      <Function Group="False" ID="Date_prompt" InMacro="True" Type="DateTime">

      <Arguments>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>@prompt($1,'D',,,)</SQL>

      </Function>

      <Function Group="False" ID="Case" InMacro="False" Type="String">

      <Arguments>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      <Argument Type="String"></Argument>

      </Arguments>

      <SQL>CASE $1 WHEN $2 THEN $3 ELSE $4 END</SQL>

      </Function>

      <Function Group="False" ID="NUMBER_TO_CHAR" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="Numeric"></Argument>

      </Arguments>

      <SQL>convert(char,$1)</SQL>

      </Function>

      <Function Group="False" ID="DATE_TO_CHAR" InMacro="True" Type="String">

      <Arguments>

      <Argument Type="DateTime"></Argument>

      </Arguments>

      <SQL>convert(char,$1)</SQL>

      </Function>

      <Function Group="False" ID="IF_NULL" InMacro="False" Type="All">

      <Arguments>

      <Argument Type="All"></Argument>

      <Argument Type="All"></Argument>

      </Arguments>

      <SQL>COALESCE($1,$2)</SQL>

      </Function>

      </Functions>

      </DBParameters>

      Thanks for your response. FYI i'm on BI4.1 SP02 and i work on Webi (not WRC)

  • author's profile photo Former Member
    Former Member
    Posted on Jul 26, 2013 at 12:37 PM

    Also I change informix.prm fail

    <Parameter Name="USER_INPUT_DATE_FORMAT">'dd/mm/yyyy</Parameter>'

    but it did not help

    I work SAP BO 4.0 SP02.

    Regards, Fanil

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.