RESOLVED - Report studio chart using data items for x-axis

[AJAYC]

Hello All,

I'm writing a report which contains a stacked area chart.

The y-axis is simply the measure of "amount" and my x-axis is a rolling time period.

The requirement I have is to prepare a report with moving x-axis labels, the prompted month will always appear as the furtherest right column and all others to the left of it, as prevMembers of the prompted value.

What I have done in the query of the chart is to create 12 dataitems. Dataitem1 is simply defined with an expression "?PromptedMonth?, with DataItem2 being defined as prevMember(DataItem1). This cumbersome arrangment allows me to pull back 11 previous members based on the prompt month.

I've then placed these 12 dataitems into the category x-axis row part of the chart.

The problem I am getting, despite the existence of data in every month, is that the first six months are not displayed in the chart.

I've adjusted every possible property to see if there is some restriction, but i think there must be something fundamentally wrong in my approach.

I'm using RS version 10.1.1 with TM1 9.5.2 as my datasource.

Any help, gladly appreciated.

Ajay

[AJAYC]

This is now resolved.....................so for anyone wondering.

First, create a slice member set (as source data is dimensional) in the query for the chart using the "set" and "lag" functions, ie:

Set ( ?PromptedMonth?,
lag (?PromptedMonth?,1),
lag (?PromptedMonth?,2),
lag (?PromptedMonth?,3),
lag (?PromptedMonth?,4),
lag (?PromptedMonth?,5),
lag (?PromptedMonth?,6),
lag (?PromptedMonth?,7),
lag (?PromptedMonth?,8),
lag (?PromptedMonth?,9),
lag (?PromptedMonth?,10),
lag (?PromptedMonth?,11) )

This will bring through only the data for the prompted month and 11 months prior.

Next, to ensure that the x-axis in the chart refers to the same range of months, placing the prompted month to the far right, create a new dataitem in the same query, in my case called it "TimeXAxis", and insert the following definition:

Set ( lag (?PromptedMonth?,11),
lag (?PromptedMonth?,10),
lag (?PromptedMonth?,9),
lag (?PromptedMonth?,8),
lag (?PromptedMonth?,7),
lag (?PromptedMonth?,6),
lag (?PromptedMonth?,5),
lag (?PromptedMonth?,4),
lag (?PromptedMonth?,3),
lag (?PromptedMonth?,2),
lag (?PromptedMonth?,1),
?PromptedMonth? )

This dataitem, "TimeXAxis" is then dragged ontp the category x-axis of the chart to ensure that the chart displays exactly the same range of months per the query applied to the data.

Slowly figuring out new things especially with the Report Studio integration with TM1.

Regards
Ajay