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

how to create a role in AS 2005 with AMO programmatically

hi All,

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu

Hi,

There is a similar discussion about creating roles with AMO in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

Adrian Dumitrascu

|||

Hi Adrian,

Thanks for u r quick response,I gone through the link and i checked with the code but it is throwing error finding the database it self.

could u please tell me what could be the problem.because i checked with the same code which u provided in the Post.

Thanks in advance.

|||

If you get an exception in this line:

Database db = s.Databases["the database id"];

then you need to specify the ID of your database instead of the string "the database id". To get the ID of your database, use SQL Management Studio to see the database properties (right click on the Database -> Properties).

Or, if you know the Name of the database, but not the ID, you can use the GetByName method:

Database db = s.Databases.GetByName["the Name of your database"];

Adrian Dumitrascu

|||

Hi

I used below code.FYI.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
'Connect to SSAS Instance
'--
Dim MyServer As New Server
Dim MyConnectionString As String = "localhost"
MyServer.Connect(MyConnectionString)

'Get Reference to Database
'--
Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

'Get Reference to Role
'--
Dim MyRole As New Role = MyDatabase.Roles.GetByName("My Role")

'Add Users
'--
Dim MyRoleMember As New RoleMember("TestDomain\TestUser1")
MyRole.Members.Add(MyRoleMember)
MyRole.Update()

'--
MyDatabase.Update() 'This may be optional
MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

In above code am getting the error in Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW") this line.

is the problem with above code or anything i missed out please let me know.

Thanks in advance.

Regards

vishu.

|||

Is the code running under an account that has permissions to read the "Adventure Works DW" database ? Try to temporary add the account as a server admin or as a database admin (you can do that with SQL Management Studio, right click on the server item -> Properties -> Security tab).

If this was the problem (lack of read permissions) you will need to setup a database role for that account, with read metadata and also write permissions.

Adrian Dumitrascu

|||

Hi

Thanks for the reply,i tried with what you said.

But there i can add only my NT Id only i cant see the permissions for read metadata.

still am getting the same problem.

|||

To temporary add the user as a server administrator, you only need to add it as a member in the Security tab of the server properties dialog in SQL Management Studio.

If that doesn't fix the problem, we need to double check 2 things:

1. make sure your code connects to the server you want

2. run SQL Management Studio under the account you run the code ('Run As' works), connect to the server from #1, right click on the Adventure Works DW database -> Properties and verify that the ID of the database is the one you use in the code

If in step #2 you do not see the Adventure Works database, we need to see if it's a permissions problem (although it should not be since you added the user as server admin) or you don't have the database on that server (connect with other users or check the data folder from "%ProgramFiles%\Microsoft SQL Server").

Adrian Dumitrascu

how to create a role in AS 2005 with AMO programmatically

hi All,

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu

Hi,

There is a similar discussion about creating roles with AMO in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

Adrian Dumitrascu

|||

Hi Adrian,

Thanks for u r quick response,I gone through the link and i checked with the code but it is throwing error finding the database it self.

could u please tell me what could be the problem.because i checked with the same code which u provided in the Post.

Thanks in advance.

|||

If you get an exception in this line:

Database db = s.Databases["the database id"];

then you need to specify the ID of your database instead of the string "the database id". To get the ID of your database, use SQL Management Studio to see the database properties (right click on the Database -> Properties).

Or, if you know the Name of the database, but not the ID, you can use the GetByName method:

Database db = s.Databases.GetByName["the Name of your database"];

Adrian Dumitrascu

|||

Hi

I used below code.FYI.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
'Connect to SSAS Instance
'--
Dim MyServer As New Server
Dim MyConnectionString As String = "localhost"
MyServer.Connect(MyConnectionString)

'Get Reference to Database
'--
Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

'Get Reference to Role
'--
Dim MyRole As New Role = MyDatabase.Roles.GetByName("My Role")

'Add Users
'--
Dim MyRoleMember As New RoleMember("TestDomain\TestUser1")
MyRole.Members.Add(MyRoleMember)
MyRole.Update()

'--
MyDatabase.Update() 'This may be optional
MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

In above code am getting the error in Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW") this line.

is the problem with above code or anything i missed out please let me know.

Thanks in advance.

Regards

vishu.

|||

Is the code running under an account that has permissions to read the "Adventure Works DW" database ? Try to temporary add the account as a server admin or as a database admin (you can do that with SQL Management Studio, right click on the server item -> Properties -> Security tab).

If this was the problem (lack of read permissions) you will need to setup a database role for that account, with read metadata and also write permissions.

Adrian Dumitrascu

|||

Hi

Thanks for the reply,i tried with what you said.

But there i can add only my NT Id only i cant see the permissions for read metadata.

still am getting the same problem.

|||

To temporary add the user as a server administrator, you only need to add it as a member in the Security tab of the server properties dialog in SQL Management Studio.

If that doesn't fix the problem, we need to double check 2 things:

1. make sure your code connects to the server you want

2. run SQL Management Studio under the account you run the code ('Run As' works), connect to the server from #1, right click on the Adventure Works DW database -> Properties and verify that the ID of the database is the one you use in the code

If in step #2 you do not see the Adventure Works database, we need to see if it's a permissions problem (although it should not be since you added the user as server admin) or you don't have the database on that server (connect with other users or check the data folder from "%ProgramFiles%\Microsoft SQL Server").

Adrian Dumitrascu

how to create a dynamic Select statements in my C# project

hi, can someone teach me how to create a dynamic Select statements in my C#
project.
i have this sets of select statements that will return the table name and
fields...and that table name and fields are the one im going to use to
create my dynamic select statements...how can i do this'
thanksProbably the best answer is Don't! In a well-designed business process
application Dynamic SQL is rarely necessary and not often a good idea.
Dynamic SQL is bad news for many reasons to do with performance, security an
d
maintainability. The best practice in a tiered architecture is usually to
build data access code in stored procedures and views and then use
parameterized procedures as your application's entry point to the database.
If you are building a general purpose database admin tool then you might
want to consider putting dynamic code in your application, in which case you
would probably want to use the .Net SqlCommand class. Otherwise you may want
to come back with a bit more information about your requirements so that
someone can advise you better.
David Portas
SQL Server MVP
--|||If your question is about generating dynamic SQL statements using
Transact-SQL, see http://www.sommarskog.se/dynamic_sql.html for a
discussion.
To generate dynamic SQL in application code, simply build a string
containing the needed SQL statement and execute using one of the
System.Data.SqlClient objects. Use command parameters as needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"cEciLlE" <cecille.regidor@.gmail.com> wrote in message
news:eHeK54rLFHA.3296@.TK2MSFTNGP15.phx.gbl...
> hi, can someone teach me how to create a dynamic Select statements in my
> C# project.
> i have this sets of select statements that will return the table name and
> fields...and that table name and fields are the one im going to use to
> create my dynamic select statements...how can i do this'
>
> thanks
>