ReportPortal
ReportPortal
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 ReportPortal General Forum
 Report Portal General Issues
 Hourly averages of a month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waechtler

Germany
3 Posts

Posted - 01/28/2014 :  04:44:13  Show Profile  Reply with Quote
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

admin

1637 Posts

Posted - 01/28/2014 :  23:55:50  Show Profile  Reply with Quote
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?
Go to Top of Page

waechtler

Germany
3 Posts

Posted - 01/29/2014 :  05:40:53  Show Profile  Reply with Quote
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
Go to Top of Page

admin

1637 Posts

Posted - 01/29/2014 :  06:31:05  Show Profile  Reply with Quote
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?
Go to Top of Page

waechtler

Germany
3 Posts

Posted - 01/29/2014 :  07:12:43  Show Profile  Reply with Quote
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
Go to Top of Page

admin

1637 Posts

Posted - 01/29/2014 :  14:35:36  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
ReportPortal © 2000-2002 Snitz Communications Go To Top Of Page