How to create cumulative S-curve?

Hi

I need to make two reports: one containing the Raleigh chart, the other containing a cumulative curve.

In the raleigh report I do this:
Per project I can see the amount of mandays on a timebase(weeks).

For the s-curve (cumulative curve) this needs to happen (and I dont know how):
I need to see the sum of the amount of mandays on a timebase(weeks).
An example:

In raleigh curve: week1 has 20md(mandays), week2 has 30md, week3 has 15md, week4 has 5md

In s-curve the same values should give: week1 has 20md, week2 has 50md(30+20), week3 has 65md(50+15), week4 has 70md (65 + 5)

I tried a sum of a sum but thats not allowed in RS
Anyone who knows how to generate an s-curve (cumulative curve)?

Assuming a cumulative s-curve is the sum of a series of data, as it moves through a series of weeks...

eg. Week 1 = 10 S-Curve Value = 10

Week 2 =

8 S-Curve

Value = 18

Week 3 = 15 S-Curve Value = 33

What you need to do is modify the data for the graph to include the

function "RunningValue". For example, if your value is called

"Mandays", the expression would be:

=RunningValue(Fields!Mandays.Value, Sum, Nothing)

This will give you a running total for each week on the graph and

should allow you to draw a cumulative graph (assuming what I think an

s-curve is, is what an s-curve is!).

More info here.

Regards,
Jon|||

Note: the RunningValue function is only available in charts starting in RS 2005. It is not available in RS 2000 charts.

-- Robert

|||

Yes it is RS2005 but it won't work!

I have a field directly retrieved from the sql server called "book_hours"
I make a calculated field on that one to get "book_days" (=book_hours / 7.5)

Then I make another calculated field to go in the data part of the chart called "cumulative_book_days". The expression is RunningValue(Fields!book_days.Value, Sum, Nothing)

From the moment That expression is added (It doesn't even have to be used in the chart), and I build the report, I get the following error:
An internal error occurred on the report server. See the error log for more details.

What is wrong with the expression?
Where can I find the log? (everything runs local and with windows authentication)

|||Your RS logs can normally be found in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles

I also get the same

problem as you when trying to define the running value as a calculated

field in the data set. In fact, VS.NET 2k5 crashes.

However, when I define it on the object by an expression, it runs fine.

What I did was define
RunningValue(Fields!book_days.Value,

Sum, "xxxx") in the Values properties of the data tab in the

chart. xxxx is the Category Group giving the scope in which you

want the running value.

You don't seem to be able to use "Nothing" for the scope parameter of

RunningValue as charts have multiple regions of scope, compared to a

standard table (with no groups) that only has it's own scope.

That seemed to work fine for me.

Robert - could this be a bug?

Regards,

Jon

|||

I keep getting an error, even after setting the scope to the category of the chart. I also used a database field as first parameter of the runningvalue function. (no success :s)

I'm gonna try to build the report from scratch again, maybe I overlooked something.

One other thing: the crashes can be avoided by first building the report. In the solution explorer you right-click the report and select 'Build'. Then if you recieve an error "An internal error occurred on the report server. See the error log for more details.", don't go to the preview tabpage of the report because VS2005 will crash!!

|||

I'm a bit confused:

where should the expression RunningValue(Fields!book_days.Value, Sum, "xxxx") go?
Should I make it in a new calculated field in the datasets pane, and then drag it to the Data Field Area of the chart?
OR
Should I drag the book_days field into the Data Field Area of the chart, then click on it and change it expression over there?

Edit: Ok I tried both, the first thing won't work: Adding a RunningValue Expression in a field in the dataset pane makes the report give that internal error.

The second thing also wont work, the RunningValue expression then just gives the same result as the expression =sum(Fields!book_days.Value)

What's going wrong?

Edit2: Oh another thing ... nothing is happening in those logfiles in the path mentioned above. Are there logging properties to be set? So there will be logged more?

|||Thanks for the advice. What error are you getting now, or is it the same?|||

It's just the same error :s
I even have set up a small example report to to experiment on that so I can minimize the scope of my problem.

I'll put a zipped solution of it online asap, maybe that will help

|||Option 2 is the way that I got it to work. I can only suggest it could be one of two things:

1) The field used in the "Category" (effectively the x axis) section is

causing an issue. Why, I don't know without seeing your report.

2) The scope parameter you have set for the running value doesn't

relate to the category field used. Again, it's a little difficult

for me to comment without seeing the report.

Just to clarify, the book_days field is simply a different result value for

each week?

Assuming you have a dataset like:

Week book_days

1 10

2 8

3 5

4 12

5 2

I'd have thought that specifiying

sum(Fields!book_days.Value) would just give you the same value for each

week (37), where as running value would sum them up for each week across the

x-axis(eg. 10, 18, 23, 35, 37).

Excuse the next post, I posted before I'd realised you'd replied.|||

Ok I made a small testsolution, hopefully somebody can find the time to look at it!
Location: http://users.telenet.be/master/CumulativeChart.zip

The shared datasource is set up with the following connection string: Data Source=localhost;Initial Catalog=master, but no tables are used in the database, the queries of the reports generate their own data.

There are 3 reports in the Solution:

Raleigh.rdl: This report shows a raleigh curve of the data (mandays on time)|||

Btw, if you are looking for a moving average in a chart, you may want to read this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=488875&SiteID=1

At the end of that thread I posted an example of how you can do this with the builtin charts of Reporting Services.

-- Robert

|||

All thanks for your comments, and Robert, thanks for the example ... it showed me what I was doing wrong.

I was grouping on the Category group, but since that each week is another value, the runningValue starts from scratch again each different group.

So the only thing I needed to do was setting the scope parameter to the Series group. Because I am showing the Mandays on a timebase per contract (= the series group), there is only 1 contract, so the group never changes, so the runningValue is never reset.

Now my graph finally looks like an S!

|||Glad you got your problem sorted, hypo.

I thought it had to be scoping issue! I just couldn't work out where.

I must admit, I struggled to get round it without checking Roberts

sample. I ended up "falsifying" a series group by grouping on a

fixed value (1, in this case) and then using that as the scope of the running

value function.

Robert - I've been trying to find a document that details scope within

reporting services. I know it's a common theory, but I'd like one

that's tailored to RS. All the sources I've seen seem a little

flakey. Do you have any suggestions of where I can find one?

Regards,
Jon|||

I'm the author of an upcoming MSDN article that will explain the moving average example (and several other new advanced samples) in detail. Particularly, I will also explain using RunningValues in charts and how to setup the scopes correctly. But I guess the article won't be published until August/Sept or so.

In general a scope is a dataset name, a data region name (list, table, matrix, chart, custom report item), or a group name within a data region.

I think the key here is to understand that in the RunningValue function, the scope is needed to determine when to "reset" the running value. In a list or table there is only one direction for the running value, hence you can use Nothing as a reset scope (meaning: never reset the running value).

In a matrix and a chart you have two running value directions (matrix: horizontal or vertical, chart: categories or series). Hence, you have to specify a grouping scope inside the matrix or the chart to identify the running value direction.

For aggregations however (such as Sum, Avg, etc.), the scope determines how much data you want to aggregate.

-- Robert