Default Date passing issue

[PRIT AMRIT]

Hi all,

I have a customized date dimension and it has a 'Date' filter i.e. Date=?p_date?. This is to force the User to select a date to derive some other business date culumns. Everything is working fiine when users use this in Query Studio.

I have the Filter in FM

But the user would also like to develope a Report in Report studio and Schedule the report. When they do scheduling, it prompts them to select a date value. Instead user would like to schedule the report based on the 'sysdate'.

I have tried with assigning a default value to the Calender Prompt and then subsituting the same with a Case statement, but it doesn't work.

[Date]=
(
CASE when ?p_date?='2000-01-01'
THEN to_char ({sysdate} ,'YYYY-MM-DD')
ELSE ?p_date?
end
)

Also tried passing with the default calender value to the parameter with Java Script. It does work, but failing when I do the scheduling.

Could anybody pls suggest if I am missing something or any other good ideas? Thanks a lot

Br
Prit

[CognosPaul]

Make the prompt object on the prompt page optional.

Use a macro prompt (instead of the alias) to give the default value to the filter:

[Date] = #prompt('p_date','date',sq(timestampMask($current_timestamp,'yyyy-mm-dd')))#

When the report is scheduled, the users will need to uncheck the date prompt.

When you set up a schedule, Cognos will store the params. It won't run the prompt page, so you can't rely on any JavaScript to modify params.

[PRIT AMRIT]

it worked like champ.....

PaulM-- You deserve a salute  :)

Will come back to you again in case I face any issues with this... HE HE

Br,
Prit

[PRIT AMRIT]

PaulM, i have a question, whether the report would schdule based on the 'Sysdate'? I have noticed if I don't define a default value to the date prompt in the prompt page, then the report schedule is failing.

If I define a default value then schedule does work, but it seems not to be considering the 'Sysdate'. It considers the default date in the Calender Prompt.

My rquirment is the report should consider the 'Sysdate' by default anytime user would schedule a report.

Please bear with me if I am missing something. Attached the XML spec of the report.

Thanks a lot for your help indeed.

Br,
Prit

[CognosPaul]

If I understand, the issue is that when the user tries to schedule the report, it defaults to using the date prompt? By default the checkbox next to the date prompt is checked, meaning that Cognos would use the default date on the date prompt.

You can uncheck the date prompt automatically with JavaScript by wrapping the prompt with a div id="datePrompt" and using the following script:

Code Select Expand

<script>document.getElementById('dateprompt').getElementsByTagName('INPUT')[2].click();</script>

The problem with that is the user would need to remember to check the box if they want to change the date when running the report interactively. I haven't found a way to identify whether the prompt page is being run from the scheduler.

Alternatively you could create another static choice checkbox prompt for them to indicate they want to always use the sysdate. The use value would be to_char ({sysdate} ,'YYYY-MM-DD') and display would be something like: "Check this if scheduling the report"

The filter would then be:

[Date] = #promptmany('sysdate','token',prompt('p_date','date'))#

[PRIT AMRIT]

Alternatively you could create another static choice checkbox prompt for them to indicate they want to always use the sysdate. The use value would be to_char ({sysdate} ,'YYYY-MM-DD') and display would be something like: "Check this if scheduling the report"

The filter would then be:

[Date] = #promptmany('sysdate','token',prompt('p_date','date'))#

Tried this solution as well. Even if you force to schedule the report on 'Sysdate' with the Checkbox prompt, it still considers the date prompt value, meaning for schedule it still considers the date prompt value. I have attached the screenshots of the same. Please have a look at it.

Alos tried setting the default value as  "Check this if scheduling the report" for the Checkbox prompt. When I scheduled the report it failed saying 'unable to find a value for 'p_date'(which the date prompt parameter)'. this means it is only validating the date prompt parameter not checkbox prompt parameter.

Have attached the XML spec too. Please have a look at it and suggest me if i am missing something.

Thanks a lot.

Br,
Prit

[CognosPaul]

I think it's fine here.

In the scenario I gave before, it's perfectly fine for the scheduler to get the date parameter, since it ignores it completely.  If the checkbox is checked then it ignores the p_date.

As an experiment, schedule the report by checking the checkbox and set the date to august 1.

[PRIT AMRIT]

As an experiment, schedule the report by checking the checkbox and set the date to august 1

Then the report is scheduled on 'Aug-01' date. it doesn't ignore the p_date.   :(

If you have time, would you mind to create a sample and share the XML spec with me? Because one of the other thing I have noticed in the below solution that use value 'to_char ({sysdate} ,'YYYY-MM-DD')' returns a TEXT instead of 'sysdate' value.

Alternatively you could create another static choice checkbox prompt for them to indicate they want to always use the sysdate. The use value would be to_char ({sysdate} ,'YYYY-MM-DD') and display would be something like: "Check this if scheduling the report"

The filter would then be:

[Date] = #promptmany('sysdate','token',prompt('p_date','date'))#

[CognosPaul]

Try this:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Date" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="filter date"><expression>#promptmany('sysdate','token',prompt('p_date','date'))#</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Date]=[filter date]</filterExpression></detailFilter></detailFilters></query>
<query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Date" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="filter date"><expression>#promptmany('sysdate','token',prompt('p_date','date'))#</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="Date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Date"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="filter date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="filter date"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
<list refQuery="Query2" horizontalPagination="true" name="List2">



<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="Date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Date"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="filter date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="filter date"/></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>
<promptPages><page name="Prompt Page1">
<pageHeader>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue/>
</dataSource>
<style>
<defaultStyles>
<defaultStyle refStyle="tt"/>
</defaultStyles>
</style>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ta"/>
</defaultStyles>
</style>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="hp"/>
</defaultStyles>
</style>
</pageHeader>
<pageBody>
<contents><selectDate parameter="p_date" multiSelect="false" range="false" required="true"/><selectValue parameter="sysdate" multiSelect="true" range="false" required="false" selectValueUI="checkboxGroup"><selectOptions><selectOption useValue="trunc({sysdate},'dd')"><displayValue>Check me when scheduling</displayValue></selectOption></selectOptions></selectValue></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>
<pageFooter>
<contents>
<promptButton type="cancel">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="back">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="fp"/>
</defaultStyles>
</style>
</pageFooter>
<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages></layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2010-06-14T19:53:55.577Z" output="no"/></XMLAttributes><reportName>test2</reportName></report>

[PRIT AMRIT]

Unfortunately I am using 8.4 and unable to open the XML spec since it is 10v  :(

[CognosPaul]

Try this

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>

<promptPages><page name="Prompt Page1">
<pageHeader>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue/>
</dataSource>
<style>
<defaultStyles>
<defaultStyle refStyle="tt"/>
</defaultStyles>
</style>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ta"/>
</defaultStyles>
</style>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="hp"/>
</defaultStyles>
</style>
</pageHeader>
<pageBody>
<contents><selectDate parameter="p_date" multiSelect="false" range="false" required="true"/><selectValue parameter="sysdate" multiSelect="true" range="false" required="false" selectValueUI="checkboxGroup"><selectOptions><selectOption useValue="trunc({sysdate},'dd')"><displayValue>Check me when scheduling</displayValue></selectOption></selectOptions></selectValue></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>
<pageFooter>
<contents>
<promptButton type="cancel">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="back">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="fp"/>
</defaultStyles>
</style>
</pageFooter>
<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages><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="Date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Date"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="filter date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="filter date"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
<list refQuery="Query2" horizontalPagination="true" name="List2">



<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="Date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Date"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="filter date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="filter date"/></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"/></XMLAttributes><queries><query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Date" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="filter date"><expression>#promptmany('sysdate','token',prompt('p_date','date'))#</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Date]=[filter date]</filterExpression></detailFilter></detailFilters></query><query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Date" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="filter date"><expression>#promptmany('sysdate','token',prompt('p_date','date'))#</expression></dataItem></selection>
</query></queries></report>

We really need spoiler tags here.

[PRIT AMRIT]

Hi PaulM,

Extremly sorry for bothering you so much. I am unable to open the 'XML' spec and getting the bellow error. Tried to replace with my data items but still no luck. Tomorrow will try to open this XML in office. Thanks

XML Error

Reason:
Element content is invalid according to the DTD/Schema.

URL:


Line:
4515

Character:
149

Source:

      <selection><dataItem name="Date" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="filter date"><expression>xx</expression></dataItem></selection>

[CognosPaul]

any luck opening the XML? The error you're getting is that it doesn't like

Code Select Expand

<XMLAttribute name="RS_dataType" value="4" output="no"/> that.
The way I found that is by pasting the error into notepad and finding the 149'th column, which started that
xml tag. Delete that line, and any other XMLAttributes named RS_dataType and try again. You may need to
go a few iterations of trying to open, then deleting stuff that isn't in the 8.4 xml schema.

[PRIT AMRIT]

No luck PaulM  :(

If you get some free time, you may send the screenshots of the techniques and expressions you have implimented in the Report. I may follow the steps and see if it helps? Right now I have kept this Issue ON HOLD :)

Thanks PaulM

Regards,
Prit