No prompt page and default filter date

[stancho]

Hi all,

I have one report with one Date Filter.
I have a list report which is filtered by this Date.
I would like to have NO prompt page and by default the list will be filtered by today date.
The Date picker will be placed on the report page with Refresh Button.

If I make the filter - required then I will be asked for Date prompt at the beginning.
If I make it optional the list will not be filtered by date on first load and I have to press the refresh button on the report page.

The problem can be solved if I have a prompt page and put javascript auto-submit of the prompt page.. but I would like not to have prompt page.

Is that possible?

BR,
Stancho

[Lynn]

Here is a thought:

Put your date picker on the report page and provide a default value that isn't a date a user would select such as Jan 1 1900.

Create a required filter:

Code Select Expand


(?SelectDate? = 1900-01-01 and [YourDate] = current_date)
OR
(?SelectDate? <> 1900-01-01 and [YourDate] = ?SelectDate?)


This will do what you've described and the report opens initially filtered on the current date. When a user picks a different date and clicks the reprompt button the report will be filtered for the date they choose.

The DOWNSIDE is that the date picker control will open initially set to whatever date you set as the default, so Jan 1 1900 is a bit silly. However the concept works if there is a date you can choose as a default that makes sense (something the user wouldn't filter on for real but doesn't show up on the date picker as absurd).

Of course the JavaScript folks out there will likely have more options for you.

[melee]

Here is a thought:

Put your date picker on the report page and provide a default value that isn't a date a user would select such as Jan 1 1900.

Create a required filter:

Code Select Expand


(?SelectDate? = 1900-01-01 and [YourDate] = current_date)
OR
(?SelectDate? <> 1900-01-01 and [YourDate] = ?SelectDate?)


This will do what you've described and the report opens initially filtered on the current date. When a user picks a different date and clicks the reprompt button the report will be filtered for the date they choose.

The DOWNSIDE is that the date picker control will open initially set to whatever date you set as the default, so Jan 1 1900 is a bit silly. However the concept works if there is a date you can choose as a default that makes sense (something the user wouldn't filter on for real but doesn't show up on the date picker as absurd).

Of course the JavaScript folks out there will likely have more options for you.

The problem is detecting the date you select that ISN'T the default - I've gotten around this by creating a singleton with the prompt value and using Javascript to select the value. So, if the datepicker uses 1/17/2011, there's a singleton in the report that displays that paramvalue. Once you have that, you can set the date on the Datepicker to correspond, no?

[CognosPaul]

This is one of those times where a combination of JavaScript and a macros work well together.

JavaScript will allow you to set the default value of the date picker after the report has been processed. To get around that you need to use a macro prompts Default parameter to pass whichever default value you want.

For example, let's say you want to pass yesterday's date. The prompt macro would look like the following:

Code Select Expand

[YearDate] = #prompt('SelectDate','date','add_days(getDate(),-1)')#
Which would resolve to

Code Select Expand

[YearDate] = add_days(getDate(),-1)

Your JavaScript (places in an HTML item immediately following the picker) would look something like:

Code Select Expand

<script> function Yesterday() { var ndays = 1;
var dtToday = new Date();
var dtYesterday = new Date( dtToday - (86400000*ndays)); 1 Day = 24 Hours = 1440 Minutes = 86400 Seconds = 86400000 Milliseconds
var strYesterday = [dtYesterday.getUTCFullYear(), dtYesterday.getMonth()+1, dtYesterday.getDate()].join("-");
return strYesterday; } pickerControlPromptDate.setValue(Yesterday() ); </script>

The pickerControlPromptDate bit is referencing the prompt name. Change the text in bold to your prompt's name.

[melee]

Paul, that blew my mind. Awesome!

[Arsenal]

Paul, that blew my mind. Awesome!

Paul often has that effect on people with questions about macros  :D

[Arsenal]

Btw Paul, I've got to give your method a try. I've used Lynn's solution before, but never something like you've proposed.

[CognosPaul]

Whoops. The Javascript has an error. Get rid of the explanation of 1 day = 24 hours = etc.

Code Select Expand

<script>
function Yesterday()
{
   var ndays = 1;
   var dtToday = new Date();
   var dtYesterday = new Date( dtToday - (86400000*ndays));
   var strYesterday = [dtYesterday.getUTCFullYear(), dtYesterday.getMonth()+1, dtYesterday.getDate()].join("-");
   return strYesterday;
}
pickerControlPromptDate.setValue(Yesterday() );
</script>

You can also use the same method to set the date prompt to the first of the month, first of the year, or whatever.

[melee]

Paul,

I wasn't aware of pickerControl. How did you discover that function? I see your method as very scalable and effective - would you mind auditing one of mine and telling me where I could improve?

See the following:

http://nicbertino.com/?p=8

I was using hidden text value prompts, inserting the information, and triggering promptAction('finish');

The reason I ask is because I'd like to write something comprehensive about UI, jQuery, and Cognos :)

[stancho]

Hi guys,
thanks for the replies!

My date column type in MySQL is datetime.
I tried a lot of casts but I can only compare [YourDate] with ?SelectDate? which is Date Prompt parameter.
It is datetime but I have only to compare dates. It's working with Date Prompt.
but [YourDate] = current_date is not working (localtimestamp - not working, = cast(..date, timestamp)... not working)

Maybe something in the formats of the dates.. but how can I debug and have this working?

Thanks,
Stancho

[stancho]

Actually the problem is that I have another filter (parameter) which is optional for this query.
If I disable the other optional filter I get it working. In other case I have to refresh the page..

[melee]

Actually the problem is that I have another filter (parameter) which is optional for this query.
If I disable the other optional filter I get it working. In other case I have to refresh the page..

Hmm... Do you have firebug installed?

[CognosPaul]

Hey Melee,

Sorry for taking so long to reply, the past couple of weeks have been somewhat hectic.

The JavaScript is something that I pulled from the Cognos site many many years ago. But you can more interesting functions by going through the JavaScript files. Look under webcontents, you should see a folder called Prompting. Whenever you pull a prompt into the report the generated HTML contains the reference to the correct files. You can use any of the functions found in the JS files. Of course IBM may (and probably will just to watch developers scream) change the function names during upgrades.

Most of the JS files are unencrypted, but there are a few that are impossible to read. The JS in charge of graph generation, for instance.

I've seen your blog before and referred it to some of my friends. I'd think you'd be more capable of auditing my code. ;)

I'm looking forwards to reading that paper  ;D

[melee]

Hey Melee,

Sorry for taking so long to reply, the past couple of weeks have been somewhat hectic.

The JavaScript is something that I pulled from the Cognos site many many years ago. But you can more interesting functions by going through the JavaScript files. Look under webcontents, you should see a folder called Prompting. Whenever you pull a prompt into the report the generated HTML contains the reference to the correct files. You can use any of the functions found in the JS files. Of course IBM may (and probably will just to watch developers scream) change the function names during upgrades.

Most of the JS files are unencrypted, but there are a few that are impossible to read. The JS in charge of graph generation, for instance.

I've seen your blog before and referred it to some of my friends. I'd think you'd be more capable of auditing my code. ;)

I'm looking forwards to reading that paper  ;D

Thank you kindly!

I don't have access to the webserver, but Firebug's console has an autocomplete which I used to explore a little bit. I think it's tough because there's not a ton of documentation out there, just have to insert breaks and try to guess what its doing :)

[CognosPaul]

Even if you don't have access to the folder itself you can still peak at the JS file. No matter how hard you try, you can't stop a user from looking at your JavaScript.

First thing to do is check if directory browsing has been enabled. If it has then life is much simpler. Go to http://server/cognos8/prompting/

Most likely you'll get the FORBIDDEN error. If you don't feel free to poke around and go through all of the JS files there. You'll have more then enough to keep you occupied for a long long time.

If you do get the FORBIDDEN error then you'll need to work with Firebug or IE Developer Toolbar.

Open Firebug and click on the Script tag. Then open the report.

You should see something like the following:

Code Select Expand


<html><head>   
2<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>   
3<meta name="copyright" content="Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved."/>   
4<meta name="trademark" content="Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated)."/>   
5<meta http-equiv="MSThemeCompatible" content="no"/>   
6<title>prompt test - Cognos Viewer</title><script type="text/javascript" src="/cognos8/prompting/prompting.js"></script>   
7<script type="text/javascript" src="/cognos8/rv/GUtil.js"></script>   
8<script type="text/javascript" src="/cognos8/common/framework/notification/CObserver.js"></script>   
9<script type="text/javascript" src="/cognos8/common/framework/util/XMLParser.js"></script> 
10<script type="text/javascript" src="/cognos8/prompting/CDispatcher.js"></script> 
11<script type="text/javascript" src="/cognos8/common/framework/util/requestManager.js"></script> 
12<script type="text/javascript" src="/cognos8/rv/cvServerRequest.js"></script> 
13<script type="text/javascript" src="/cognos8/common/framework/util/CDictionary.js"></script> 
14<script type="text/javascript" src="/cognos8/rv/CCDataManager.js"></script>

Every one of those js files is available for you to look through.  Simply type the following into the url bar:
http://server/cognos8/rv/common.js or http://server/cognos8/prompting/prompting.js

Remember, if your browser can see something, then so can you. And so can the users.