| T O P I C R E V I E W |
| waechtler |
Posted - 01/28/2014 : 04:44:13
Hi,
I am a newbie to Reportportal and have problems understanding how to create this report:
The report should show a variable's average for each hour of the day, covering a hole month.
That is it should display 24 values for 0 - 24 hours, each one representing the average value for this hour derived from all 30 days of the reporting period
Can this be done?
Thx
Jan |
| 5 L A T E S T R E P L I E S (Newest First) |
| admin |
Posted - 01/29/2014 : 14:35:36
You need to create an MDX expression using Avg and ParallelPeriod MDX functions. The formula will look something like:
AVG({
[CalendarHierarchy].[Hour].CurrentMember,
ParallelPeriod ([CalendarHierarchy].[Day],1, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],2, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],3, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],4, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],5, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],6, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],7, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],8, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],9, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],10, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],11, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],12, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],13, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],14, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],15, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],16, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],17, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],18, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],19, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],20, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],21, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],22, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],23, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],24, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],25, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],26, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],27, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],28, [CalendarHierarchy].[Hour].CurrentMember),
ParallelPeriod ([CalendarHierarchy].[Day],29, [CalendarHierarchy].[Hour].CurrentMember)
},[Measures].[MyMeasure]
)
Note that:
ParallelPeriod ([CalendarHierarchy].[Day],1, [CalendarHierarchy].[Hour].CurrentMember) will give you the same hour the previous day.
ParallelPeriod ([CalendarHierarchy].[Day],2, [CalendarHierarchy].[Hour].CurrentMember) will give you the same hour two days ago. |
| waechtler |
Posted - 01/29/2014 : 07:12:43
quote:
[i]Originally posted by admin[/i]
[br]Do you want a single value for the entire month? Will this value give you the average of 720 (24*30) hours in that month?
No, but 24 single values, one for _each_ hour of the day, for the entire month, i.e. 24 times the average value of 30 hours in that month |
| admin |
Posted - 01/29/2014 : 06:31:05
Do you want a single value for the entire month? Will this value give you the average of 720 (24*30) hours in that month? |
| waechtler |
Posted - 01/29/2014 : 05:40:53
From my answers you will see, that I miss some basic understanding, hope I can make it clear anyway:
>1. Is the data available in SSAS cube?
Its a cube, the connection type is SQLOLEDB, is this SSAS?
>2. Does your Cube has Hours and Date attribute?
Not sure if this is an attribute, but there is a Calendar hierarchie, it looks like this:
http://ftp.ics.de/outgoing/jw/reportportal001.jpg
>3. Will you report have: Hours (0-24) on Columns and Date (Last 30 days) on Rows?
In the screenshot above, you can see that both days and hours are in the first row, the second contains the measure
From this data, I would like to have a x-Axis with hours (not: day and hour), and a y-Axis with the Availability values, like this:
http://ftp.ics.de/outgoing/jw/reportportal002.jpg
>4. For example, if the current Date is 1/30/2014 and the current Hour is 11 the calculation will give you the average of Hour 11 for the last 30 day including 1/30/2014?
Yes, but I do not need to run the reports for specific hours, I run it once a month
Then it should be for all hours 0-24, not only 11
|
| admin |
Posted - 01/28/2014 : 23:55:50
1. Is the data available in SSAS cube?
2. Does your Cube has Hours and Date attribute?
3. Will you report have: Hours (0-24) on Columns and Date (Last 30 days) on Rows?
4. For example, if the current Date is 1/30/2014 and the current Hour is 11 the calculation will give you the average of Hour 11 for the last 30 day including 1/30/2014?
|
|
|
Report Portal
