Dynamic YTD Calculation

[pmartins]

Hi guys,

I'm currently converting a standard Cognos report to Active Report.

One of the functionaily is to select a month in a value prompt (I've converted this to a drop down list) and then to use that month in the periodsToDate function, and generate a set that gives me the ability to calculate YTD dinamycally.

Of course I need the ability to, when the report is open, change the month selection, and have the YTD re-calculate on the fly.

This is easy with parameters, which in Active Reports do not exist :-) I tried the currentMember function as a substitute, but without good results in practice.

Has anyone faced this challenge? And solved it successfully?

Thank you so much!
Pedro Martins
Lisbon, Portugal

[MFGF]

One of the key points to remember when building an Active Report is that it has no connection to the datasource when users are consumng it, so the concept of dynamically recalculating things disappears. Instead, you need to pre-calculate all possible desired results in different cards, and simply allow the user to select the appropriate card in the deck using a control of some sort. It looks to the user like things are being recalculated, but in fact all they are doing is selecting a different pre-rendered result set on a different card.

In your case, you will need to have a separate card for each month (probably in a data deck based on month) and on each card you will need to have calculated and displayed the YTD from that month to the date of whenever the report is run and the MHT file is rendered. You can then drive selection of the relevant card from a drop down list based on month.

Hope that helps!

MF.

[CognAsh]

I couldn't have put it better myself!

Could perhaps use a slider to control the months - would look swish...

Ash ;0)

[MFGF]

I couldn't have put it better myself!

Could perhaps use a slider to control the months - would look swish...

Ash ;0)

Weird, I'm getting a funny sense of deja vu, Ash! I have a nagging feeling I have discussed this topic with someone recently. Maybe I'm dreaming it? ;)

Good idea on the Slider. It would need to be configured as a single-value slider rather than a range, though. Oh my, there goes that deja vu again... :)

MF.

[pmartins]

Hi guys,

Thanks for the input, I've made progress since the last post, but I'm still not quite there yet...

Ok, I have a drop down with months, but I cannot filter my data deck with that query, if I do that I cannot get YTD as it is a group of months basically. I cannot use it in the master-detail relationship either as this is also a filter.

So all I can do is to use the month variable in the container select option.

Then I'm using the currentMember MDX function (in the Crosstab query) to "fetch" the selected month, and based on this calculate YTD. But this isn't working at all :( The query doesn't recognize the month selected in the drop-down...

This should be rather straightforward, but I have to confess I'm a bit lost here... Please one more tip? :)

Pedro

[Francis aka khayman]

The culprit is the Master Detail Relationship between your Month Data Deck and your YTD Data (be it in a singleton, list, crosstab, etc). Master Detail only has an "equal to" relationship.

Hence when you select for example March from your prompt, your Master Detail Relationship will have
[Month From Data Deck] = [Month from your YTD Query] or March = March which will include data from March only...


instead of [Month From Data Deck] <= [Month from your YTD Query] or March >= [Month from your YTD Query] which will include Jan, Feb and Mar (if they are present in your data).