Hi all, I'm creating a report where a user will search for customer records using a date of birth.
Rather than use the Date Prompt and force them to click 3 dozen times to get to the desired DOB, I want them to be able to enter a birth date into a Text Box Prompt in a uniform format: DD/MM/YYYY.
The parameter name is p_dob.
So if the input into the Text Box Prompt is 04/09/1965, the output would be:
YEAR: SUBSTRING(?p_dob?,7,4) = 1965
MONTH: SUBSTRING(?p_dob?,1,2) = 04
DAY: SUBSTRING(?p_dob?,4,2) = 09
I am able to successfully do the following:
1.) Cast these functions to ints, and concatenate them as a proof of concept, like this:
CAST(substring(?p_dob?,7,4) , integer) || CAST(substring(?p_dob?,4,2) , integer) || CAST(substring(?p_dob?,1,2) , integer) = 196549
2.) Create a datetime with _MAKE_TIMESTAMP() by passing in integers as a proof of concept.
What is NOT WORKING is using the _MAKE_TIMESTAMP function with these casted INT values, like this:
_MAKE_TIMESTAMP( CAST(substring(?p_dob?,7,4) , integer) , CAST(substring(?p_dob?,4,2) , integer) , CAST(substring(?p_dob?,1,2) , integer)
OR
_MAKE_TIMESTAMP( [YEAR] , [MONTH] , [DAY] )
If these substrings are successfully being casted as INTs, why aren't they being accepted as the integer arguments in the _MAKE_TIMESTAMP() function?
I have tried using a reference query to pull in the casted integer date fields, but no luck.
The error message is:
XQE-GEN-0002 An unexpected exception occurred. CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2017-10-05-11:13:22.302-#1333
Did you try googling the error code:
http://www-01.ibm.com/support/docview.wss?uid=swg21985963
Yes, it does not seem to be applicable here. I am running the report output to HTML, not Excel.
Hi,
Try the expressions in the attached xml and see if it helps.
<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='ABCD']/package[@name='xyz']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Data Item1"><expression>substring(?dob?,1,4) + '-' + substring(?dob?,5,2) + '-' + substring(?dob?,7,2) + ' ' + '00:00:00.000' </expression></dataItem><dataItem name="Data Item2"><expression>cast([Data Item1],timestamp)</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<list refQuery="Query1" horizontalPagination="true" name="List1">
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Data Item1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Data Item1"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Data Item2"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Data Item2"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2017-08-01T12:34:04.364Z" output="no"/></XMLAttributes></report>
Good luck
New guy
Hi NewGuy, in the interest of readability and understanding your suggestion, allow me to summarize - you're suggesting to do the following:
Data Item1 = substring(?dob?,1,4) + '-' + substring(?dob?,5,2) + '-' + substring(?dob?,7,2) + ' ' + '00:00:00.000'
Data Item2 = cast([Data Item1],timestamp)
So instead of using _MAKE_TIMESTAMP(), you're suggesting creating a string for which you can do: CAST(formatted timestamp string, timestamp).
I will try this and report back.
Hi,
Yes, because the below expression will result in 1978.
CAST(substring(?p_dob?,7,4) , integer) || CAST(substring(?p_dob?,4,2) , integer) || CAST(substring(?p_dob?,1,2) , integer) = 196549
Alternatively you can create 3 dataitems for each substring and you can use _make_timestamp([D1],[D2],[D3])
Good luck
New guy
Hi New_guy, can you clarify what you mean by: "Yes, because the below expression will result in 1978.
CAST(substring(?p_dob?,7,4) , integer) || CAST(substring(?p_dob?,4,2) , integer) || CAST(substring(?p_dob?,1,2) , integer) = 196549"
Also, I tried this, both in my query where the fields are casted as Data Items, and in a reference query. No Luck.
Alternatively you can create 3 dataitems for each substring and you can use _make_timestamp([D1],[D2],[D3])
Hi,
Pls try the with the below approach -
[DOB(char)]
1) cast DoB to varchar - cast([DOB],varchar(10))
[ConcatDOB]
2) After converting DoB to varchar extract day, month & year using substring and concatenate as below
substring([DOB(char)],9,2)||substring([DOB(char)],6,2)||substring([DOB(char)],1,4)
3)Replace all instances of '/' in ?p_dob?
[paramDoB] as replace(?p_dob?,'/','')
5) Add filter to the report
[ConcatDOB]=[paramDoB]
Hope that makes sense
Life gets easy with sql server, just add the filter below -
convert(varchar(10),[Date of Birth],103)=?p_dob?
Regards
Raj