Date format for report filter in WEBI

[Former Member]

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.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Hello, Victor.

Now I cannot check this in SP06 You think SP06 can to solve this problem?

Ooops, in the second script must be 07/23/2013 (format mm/dd/yyyy)

[Former Member · Accepted solution]

Hello Fanil,

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

There has been quite many enhancements and fixes since BI4.0 SP02, so if there is something wrong here, then there is a fair chance that it has been fixed in the meantime.

Best regards,

Victor

[Former Member · Accepted solution]

Hello, Victor.

I solved part of problem. I change in informix.prm paramter to 'MM-dd-yyyy HH:mm:ss'. After it query in IDT work fine, but then I try to use this universe in Webi the problem appear again. Maybe I need change date input format in another .prm file?

Regards, Fanil.

[Former Member · Accepted solution]

Hello Fanil,

which version of Web Intelligence are you using for your test (e.g. RIA, Web, Rich Client)?

Best regards,

Victor

[Former Member · Accepted solution]

Hello, Victor.

I'm test all on Web Intelligence Rich Client.

Regards, Fanil

[Former Member · Accepted solution]

Hello Fanil,

If you are using Rich Client, my understanding is that changing the local informix.prm file and connecting in 2-tier mode should be enough.

Maybe you can now also change the informix.prm file on the server (make always a backup), and have it restart it.

Best regards,

Victor

[Former Member · Accepted solution]

Hello, Victor.

I change informix.prm and sqlsrv.prm files on local machine and all work fine in Webi Rich Client. But now I have problem in BI Launch Pad, I get error convert varchar to smalldatetime is out-of-range. I think is maybe some bug.

Regards, Fanil

[former_member272253 · Accepted solution]

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)

[Former Member]

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