If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Date logic in 10.2.2 report

Started by chinnucognos, 01 Apr 2016 05:01:18 AM

Previous topic - Next topic

chinnucognos

Hi All,

Can you please suugest how to go with below logic in report level.
I have two date prompt parameters ?FROM_DATE? and ?TO_DATE? , based on below logic the date values should populate.

IF (Maximum([query].[date] > _first_of_month(current_date))
THEN
?FROM_DATE? = first_of_month(current_date)
?TO_DATE? = (Maximum([query].[date])
ELSE
?FROM_DATE? = (Minimum([query].[date])

?TO_DATE? =IF ( _last_of_month(Minimum([query].[date]) < (Maximum([query].[date]) )
           THEN _last_of_month(Minimum([query].[date])
           ELSE (Maximum([query].[date])
ENDIF


thanks in advance
Deal with it!

BigChris

Do you actually want the user to change those dates? If not then they don't need to be prompts...just write a filter statement.

chinnucognos

Thanks for your response..

Yes..User wants to select/change dates accordingly and whatever date logic we applied should populate in Date prompts initially.
Here Date prompts created in Report page
Can you give any work around how to populate values in prompts using Macros or scripts or any conditional blocs?
Deal with it!

BigChris

Ok - that makes sense. It sounds like what you're trying to do is set the default for the date prompts, based on the data in your database...and I'm not sure that that's possible. I'd be tempted (perhaps as a first draft of the report), to calculate those dates and display them a sugested dates in singletons next to your date prompts.

bdbits

You cannot set the value of a prompt macro (e.g. the ?FROM_DATE?), you can only use it in your expressions.

There are many examples here and elsewhere on using javascript to set prompts, but there are a lot of dependencies on your specific report. It is not a drag-and-drop thing, you need some javascript skills. I just googled this example up for you, but remember you will need to understand the basic code here and adapt it for your particular report.

http://www.ibm.com/developerworks/library/ba-pp-reporting-scripting_techniques-page634/

chinnucognos

Thanks bdbits... for ur response.
I tried with procedure what u shared..
I created filter in report level and js as below, but not working as expeceted.Is any thing that i missed in below code.Please anyone let me knw if i did wrong!!

if (([MaxDate]) > [first_of_currentmonth])
then (#prompt('FromDate','date', '([first_of_currentmonth])')#)
else (#prompt('FromDate','date', '([MinDate])')#)

and JAVASCRIPT as
<script type="text/javascript">
var fdf = getFormWarpRequest();
var listDateFrom = fdf.txtDatePDateFrom
var ftp = getFormWarpRequest();
function fromDate()
{
var dtToday = new Date();
var dtlast90Days = new Date (dtToday);
var strlast90Days = [dtlast90Days.getUTCFullYear(), dtlast90Days.getMonth()+1,
  dtlast90Days.getDate()].join("-");
return strlast90Days;
}
pickerControlPDateFrom.setValue(fromDate() );
</script>
Deal with it!

MFGF

Quote from: chinnucognos on 04 Apr 2016 10:40:47 AM
...but not working as expeceted...

What does this mean? Do you get an error? The wrong results? No results? Something else? I'll wager it would be pretty difficult for anyone to tell you what the issue is if they don't know what problem they are trying to solve here? Not that I could help even if I knew - I don't do JavaScript at all, sorry. You need to give as much information about the issue as you can in order to get help, though. "It's not working as expected" doesn't really tell potential posters anything, does it?

Cheers!

MF.
Meep!

chinnucognos

Sorry for not giving proper details..
here it goes
I created filters in List report in which paramters("FromDate" & same for "ToDate")
created using macro as shown below
If (([MaxDate]) > [first_of_currentmonth])
then (#prompt('FromDate','date', '([first_of_currentmonth])')#)
else (#prompt('FromDate','date', '([MinDate])')#)

and later created two Date prompts in report and assigned exisitng parameters(like FromDate & ToDate)
and given "Name" of Date prompts as PDateFrom & PDateTo as given in link provided by bdbits

http://www.ibm.com/developerworks/library/ba-pp-reporting-scripting_techniques-page634/

and JAVASCRIPT i kept along with this and modified as per above criteria
<script type="text/javascript">
var fdf = getFormWarpRequest();
var listDateFrom = fdf.txtDatePDateFrom
var ftp = getFormWarpRequest();
function fromDate()
{
var dtToday = new Date();
var dtlast90Days = new Date (dtToday);
var strlast90Days = [dtlast90Days.getUTCFullYear(), dtlast90Days.getMonth()+1,
  dtlast90Days.getDate()].join("-");
return strlast90Days;
}
pickerControlPDateFrom.setValue(fromDate() );
</script>


Now the expected results are not popullated in Date prompts instead i am getting Current dates in both Prompts.


For suppose,
Date 1 - Beg_of_Cur_Month (03/01)
Date 2 - End_of_Cur_Month (03/31)
Date 3 - Min_of_Data_base_Value (04/20/2014)
Date 4 - Max_of_Data_base_Value (06/20/2014)
Date 5 - End_of_Month_for_Min_Date (04/30/2014)

IF (Max_of_Data_base_Value > Beg_of_Cur_Month)
THEN
FROM_DATE = Beg_of_Cur_Month  - 03/01
TO_DATE = Max_of_Data_base_Value - 03/29
ELSE
FROM_DATE = Min_of_Data_base_Value - 04/20 
TO_DATE =
                              IF (End_of_Month_for_Min_Date < Max_of_Data_base_Value)
                              THEN End_of_Month_for_Min_Date
                              ELSE Max_of_Data_base_Value
ENDIF
Deal with it!