Showing multiple columns from the same source item

[fkli222]

I am trying to create a report that lists 2 different columns from different dimensions but from the same source item. For example, I want to create a variance analysis report that compares actual vs forecasted data for a given month. Each of the columns would be coming from my versions dimension but comparing the same month, one representing actual and the other forecast. What is the best way to approach this and how would I define each one of these columns on my page prompt when trying to filter on the different versions and month to compare?



Any help would be greatly appreciated.

Thank you!

[fkli222]

My main issue that I cannot figure out is when I specify the current month I would like to analyze.

I know I can select the month, and then what 2 versions I want to compare but I want this to be automatic in the back end.

I tried doing a IF/THEN as a filter saying if month = 'Dec 2013', then version in ('Dec 2013', 'Nov 2013')

I get a parsing error when trying to run the report. Is there a better way to tell the report when I select a specific month it knows what 2 versions to compare?

Thanks!

[katdbc]

Buddy you explain more clearly.

You want a cross tab with month on the rows and Actual and forecast (goal ) in you column ?

is my understanding right .

Do you have all your data in the backend ? why dont you create a cross tab and get the data for all the month before you use the prompt page

[katdbc]

buddy can you explain more clearly ?

[fkli222]

Sorry if it was a little confusing.

My data is in the back end but I have a couple of problems.

My actuals and forecasted data sits between 2 different versions. For example, if I wanted to analyze December, I would need to compare December version (Actuals) and November version (Forecast). I already created a crosstab, with my elist item on the rows and versions on the columns. The reason for the prompt page was so the user is able to select the month that they would like to analyze.

My intent is basically for the user to pick a month before running the report and it would automatically spit out a actual vs forecast (2 versions) analysis. So my question is, how do I make the report know what 2 versions to pick based on the month the user picks?

Thank you!