applying sum or count funtions to a group

How can I make reporting services to calculate the sum of a value that is
displayed in a group? When I insert the funtion =Sum(Fields!nuAmount.Value)
in the footer of a group, I get the sum considering all values included in a
detail. I only want the sum to be calculated from the group header values
diaplyeddid you get any answer on this quest?
reg.
Kjell Brandes
"FedericAguirre" <FedericAguirre@.discussions.microsoft.com> wrote in message
news:C518AE1F-FFE3-4F16-A007-165420532AB1@.microsoft.com...
> How can I make reporting services to calculate the sum of a value that is
> displayed in a group? When I insert the funtion
=Sum(Fields!nuAmount.Value)
> in the footer of a group, I get the sum considering all values included in
a
> detail. I only want the sum to be calculated from the group header values
> diaplyed|||do some of the calculations on the query side. If you want a count of orders
group by order# then do a count of them on the report.
"FedericAguirre" wrote:
> How can I make reporting services to calculate the sum of a value that is
> displayed in a group? When I insert the funtion =Sum(Fields!nuAmount.Value)
> in the footer of a group, I get the sum considering all values included in a
> detail. I only want the sum to be calculated from the group header values
> diaplyed

Apply top & Group By

I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.

Thanks

I have done it with the row_number function.

Apply permissions to user, role or group?

Hi guys,

I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.

The database is behind an API of stored procedures to manipulate the data, and views to select the data.

The database needs to be accessed remotely by multiple clients.

How best to keep the database secure?

Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?

There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?

A few pointers would be gratefully received!

What are you trying to protect and from whom? Who should have access to what? What kinds of access do you want to allow? You should start by asking yourself such questions and once you gather the answers, you can start designing your application security to enforce these access restrictions.

There is no best database security model - if you don't have anything to protect, you won't need a security model at all. Best is relative to the needs of a specific application.

If you tell us what you are trying to obtain, we'll try to help you get it.

Thanks
Laurentiu

|||

Thanks Laurentiu,

The database contains billing information and server will be (in some cases) visible on the Internet. Users manage the data using client application software. It is this software that uses the stored proc API.

I created a specific login known to the software and proceeded to grant execute permission to the API on this login.

The API is quite extensive and while doing this I wondered if there was a better way, perhaps using roles or whatever, so that I can grant permissions to the API once and then allow different logins, including SSPI logins to be members of that role, or group or whatever it should be.

Looking for pointers and advice on the best direction to go on this.

|||

I still need more details about your users: are they having diferent roles, which would require different access restrictions to your data? If they all have the same level of access, do you need to distinguish between them, would you want to know, for example, who did what operation and have an auditing system?

There are many ways to achieve security, and it is hard to tell what is the best solution for you. Here are some possibilities, but I can't really recommend one without knowing more about what you are trying to do.

1) You can have all users connecting to your application with the same credentials, and the software will connect to the server as some login. This won't allow you to know who does what, it will only restrict access to those that know how to connect to the application.

2) You can have users connecting to your application with distinct credentials. You would manage these credentials within your application. The connections to the server would be done using the same login, but your application can implement custom auditing because it does the user authentication. Also, any access rights will have to be controlled at the application layer.

3) You can have users connecting again with distinct credentials, but in this case the credentials correspond to SQL Server logins, and for each user, you connect to SQL Server using the corresponding login. You can do auditing in this case either at the application level or at the server level, within the stored procedures that you call. You can manage rights granted to users at SQL Server level and you can use roles for easier management.

If you don't know exactly what you will want to do, it is a good idea to keep your options open. So, use roles and grant permissions on roles; then, if you need to have those permissions accessible to more than one user, you can just add them to the role.

Hope this helps.

Laurentiu

|||

Laurentiu

Been playing around with it and option 3 seems to suit us best. Keeps it flexible, for example, it allows us to split the API into different roles.

Thanks for your ideas!

application service provider architecture

Hi Gang,
Not sure if this is the "right" sql server group to post this question in, but thought I'd give it the "ol'college try". If you feel that I may benefit by posting same question in a different group, please don't hesitate to let me know. Anyhow, below is my question:
Working with a client on developing an Application Service Provider system where users of the system will be affiliated with a parent company, the company will have the contract with my client and each company's data must be separated from all other companies. salesforce.com is a great example of such a structure, but has nothing to do with our application (meaning, we're not building a sales app).
What it comes down to is that there are 2 choices (unless someone knows of a others...)
1. Having the system create a separate database for each company or
2. Managing individual user access rights either through database roles or programatically.
We're looking at other DB providers to see what options we have, and for example, have found that Oracle 9i (which salesforce.com runs on) has a "Virtual Private Database Option". This feature enables developers to build 1 DB to support the ASP model and have Oracle logically (behind the scenes) manage access to the data. There are some disadvantages to doing this, but it is interesting.
I'm looking for information about / examples of SQL Server being used in an ASP environment and what methodology was used and why. Any assistance is greatly appreciated.
Thanks!I prefer separated databases... Each database can be modified, or
recovered, or backed up as the user sees fit... If they are all in a single
database and one user does something nasty and needs to rollback, it would
be a much more difficult task.. Security would be cleaner also. But you
would have lots of backup jobs to deal with...or maybe more complicated
jobs to backup everything.
However if there are going to be lots of customers, which would mean
hundreds or perhaps more databases, then the SQL tools do not work well...
It would take lots of time to open SQL Enterprise Manager for instance, so
you'd have to go command line probably...
The benefit of using a single database, is easier maintenance... But mixing
customer data might be a problem... would you have a separate set of tables
for each customer or simply add a companyid field to each table... IF you
have a companyID field added, you'd need to make sure performance wouldn't
suffer... If I have a small number of rows for my company in one of the
tables, where other companies have millions of rows, if I needed to do a
table scan, I'd have to look through everyone elses rows as well...
Tough choice,
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"appsprov" <anonymous@.discussions.microsoft.com> wrote in message
news:B973EF65-7175-4208-9657-3B8AAAADB2D5@.microsoft.com...
> Hi Gang,
> Not sure if this is the "right" sql server group to post this question in,
but thought I'd give it the "ol'college try". If you feel that I may
benefit by posting same question in a different group, please don't hesitate
to let me know. Anyhow, below is my question:
> Working with a client on developing an Application Service Provider system
where users of the system will be affiliated with a parent company, the
company will have the contract with my client and each company's data must
be separated from all other companies. salesforce.com is a great example of
such a structure, but has nothing to do with our application (meaning, we're
not building a sales app).
> What it comes down to is that there are 2 choices (unless someone knows of
a others...)
> 1. Having the system create a separate database for each company or
> 2. Managing individual user access rights either through database roles
or programatically.
> We're looking at other DB providers to see what options we have, and for
example, have found that Oracle 9i (which salesforce.com runs on) has a
"Virtual Private Database Option". This feature enables developers to build
1 DB to support the ASP model and have Oracle logically (behind the scenes)
manage access to the data. There are some disadvantages to doing this, but
it is interesting.
> I'm looking for information about / examples of SQL Server being used in
an ASP environment and what methodology was used and why. Any assistance is
greatly appreciated.
> Thanks!

API for creating user or group

What's the SOAP API call to create a user or group? It seems that when someone successfully logs into Report Server, a new user is created for that user. However, is it possible to create a list of users on demand?There are no explicit APIs for managing groups or users. In fact, the report server doesn't even know anything about groups - group membership is handled by the Windows APIs. Assuming that users are authenticated and authorized to access resources, the user is created automatically.|||I am using Forms Authentication. Report Manager has the ability to add user/group so there must be an API available. Report Server must know about users/groups because there's a database table that stores user info with relationships to policies and roles.|||

If you are using Forms auth then somebody has written a custom security extension that manages users and groups for you. (Perhaps you are using the sample?) In that case you can see if the security extension provides an API for adding users. If you are using the sample security extension, it does not provide a mechanism (outside of the logon page), you would have to add directly to the table it uses.

You are correct that RS has a table of users and policies, but it does not technically control this list. It always defers to the security extension. When a user comes in RS ask the security extension for the user name and then stores it so that it can keep track of owners. It also stores information on policies, but only to pass back to the security extension to allow the security extension to evaluate if a user has permission to perform the current operation.

|||I wrote the Forms Auth security extension and everything is working fine. I am just confused as to when RS create users and assign roles to them. For example, I want to initially create 5 users and give them permissions to certain folders. Those users are already created in the my own database but not in RS database. So if I use the API to set permissions for them, does RS automatically create the users in its own database? I am also curious as to what actually happens when a new user/group is created via Report Manager. Since Report Manager is built using the same exposed ReportServices2005 SOAP API, there must be a way to create users.|||

RS only adds the users to it's database when it needs to reference the user. If a user creates an item in the catalog or subscribes to a report, the user will be added to our database.

As you said, Report Manager uses the Soap API so it doesn't create any users, it only makes soap calls which may result in a new user row in the RS database.

Is there a reason you feel that the RS database needs to have the user table populated? As long as your security extension knows the users, what RS stores shouldn't be an issue. Perhaps if you explain the issue you are facing we could address that.

|||I want to use Reporting Services to manage reports with several clients. When a new client is added, I will create a folder under the Home folder and create user accounts for their administrators. Then I will assign roles for each user to the folder. So I guess that as soon as I assign roles, that user will be created in the RS database.|||Yep, that is correct.|||

Has anyone tried using the ASP.NET 2.0 Application Services (Membership and Role Management) together with custom extension to authenticate users?

I'm in the process of trying to configure this for a client. We are trying to use as much "out of the box" stuff as possible. Any samples would be usefull.