Extract end_date from date range prompt

[brymack1]

I am trying to extract the start date & end date from a date range prompt. I am dragging in a date prompt and setting range to "yes".

I've searched online at length for this answer. Every answer I can find simply tells of a work-around (don't use a date range, use separate prompts for from and to dates). I get that the work-around works, but there simply MUST be a way to extract the end_date from the date range prompt. For argument's sake, let's just say that I know an answer for this must exist and I want to find the real answer rather than the work-around.

I am on Cognos 10.2.2

For example, if I select Sept 1, 2016 to Sept 30, 2016 in my date range prompt, the parameter outputs the following in my report header:

Expression Definition: ParamValue('ParameterFeedDate')
Returns: Between 2016-09-01T00:00:00.000 and 2016-11-30T23:59:59.999

Note that I am displaying the "value" above, not the "display value".  But the word "Between" isn't really returned. Evidence:

I created a new data item and tried to substring out the start date two ways:
substr(?ParameterFeedDate?,9,10)    =    01 00:00:0   
substr(?ParameterFeedDate?,1,10)    =    2016-09-01

So that's weird, but fine. The word "between" apparently can't count as the starting position of the substr.  The letter "T" disappears also before the time, but regardless, we can definitively extract the start date from a range prompt with the latter of those two in every case.

So using that logic, shouldn't we just need to find where the second date starts within the parameter?  I extended my substring to try and discover where the "end" date would begin:

substr(?ParameterFeedDate?,1,30)   = 2016-09-01 00:00:00.0

This output indicates that the entire string stored in ParameterFeedDate is 2016-09-01 00:00:00.0

So the second date MUST be stored in another parameter that is unnamed. Where is this stored????

[brymack1]

To prove my theory that the prompt only holds the start date, I created the following data item:

Report Expression: #prompt('ParameterFeedDate','date')#
Output: Sep 1, 2016

I am of the understanding that ?ParameterFeedDate? is the same thing as #prompt('ParemeterFeedDate','string')#   ... so I wanted to rule out that switching it to date would simply fix it.  Proof of that:

Report Expression:  #prompt('ParameterFeedDate','string')#
Output: 2016-09-01

[brymack1]

I have found how to extract it using the display value:

Start Date: substring(ParamDisplayValue('ParameterFeedDate'),8,13)   = Sep 1, 2016
End Date:  substring(ParamDisplayValue('ParameterFeedDate'),25,35)  = Sep 30, 2016

Note that it reads 1 instead of 01 for the start day... just to keep in mind

But I'm not sure how to get this into macro format. Hmmmm.....

Now how to convert this to SQL ....

[BigChris]

I can see that this is an interesting topic, but if I need to know or use the end date I'd just have a start date prompt and an end date prompt.

[hespora]

I gotta ask tho - *is* this purely academic?

Run your parameter as well as your date item into a separate query. 2 new calculated data items "minimum([date] for report)" and maximum accordingly. Bam, done.

[BigChris]

@Hespora - that's not strictly true though...the minimum/maximum elements will only bring back the earliest/latest date from the data that matches the filter. If there's no data on the end date, that date won't be calculated by the maximum function.

[hespora]

You're partially right, although I'm far from an expert on this - I am just a report author, I do not model any data sources. But just from that experience, I know that both may be possible.

I have two data sources in my day job that I use regularly. One is exactly like you described, it is linked to transactional data. If I filter on day X, and I have no transactions on day X, then my result set is empty, even if I only have the Date field in the query. The other data source however does not have this limitation. In there, if I filter on a date, that date is returned. Only if I also pull a fact data item into the query does it behave like the first.

[brymack1]

It's not academic, it's just that every "answer" to the question online states the work-around, I want to take the time to find the actual solution, not a work-around.

Aside from being a work-around, there are issues with using the "2 separate prompts" method. Namely that using a start & end date (separate value prompts) as an optional prompt leaves the checkbox for using the prompt marked as checked by default; I have tried every trick out there (html tags, javascript, render variables) and cannot get this prompt to be unused (checkbox = unchecked) by default. That checkbox does not exist on a date-range prompt.

The date range would be perfect if only we can find where IBM stores the second variable. The solution is simple yet undocumented - find where 1 parameter is stored.

[brymack1]

I gotta ask tho - *is* this purely academic?

Run your parameter as well as your date item into a separate query. 2 new calculated data items "minimum([date] for report)" and maximum accordingly. Bam, done.

I can appreciate pulling out the min & max values to default the prompt, so it will effectively select everything, but the table in point has 73 million rows and weeding out the min and max takes way too long to generate a prompt.

[bdbits]

Put an index on that field and it will be nearly instantaneous.

If you are dead set on finding it, the data is almost certainly stored in the prompt page. If you are feeling brave, you can inspect this rather thoroughly with your browsers dev tools (generally accessed with F12) or third party debuggers. I have gone there before and it is not for the faint of heart and you will need to have web dev skills and a thorough understanding of the DOM.

Sometimes it is just not worth it though, and there is nothing saying IBM will not change it in some future release. It could be that the answers you characterize as workarounds are in fact the best 'solution'.