MS SQL Server - Supporting user-defined "columns" with eye to SQLRS

Author

Supporting user-defined "columns" with eye to SQLRS

Stan Spotts

2006-03-15, 9:23 am

I'm looking for a best practice for creating user-defined columns that
allows for not-overly complicated methods for reporting. To show general
requirements: a user or manager adds a few columns specific to his
department, including "column" names. He then wants to create a report that
includes these columns along with some of the default columns. Other users
from other departments add other columns spefiic to themselves as well.

I'm thinking that for the novice or regular users, setting up report models
would suffice for column selection, while creating a table for all user
defined types (key would be parent key plus department-key plus column-name
key, maybe) would support holding the data, another for type info, but type
enforcement and other issues are the things that I'm looking for at a best
practice level.

1. Table that has one column of every type (with some max width for varchar,
etc.)
2. Table has one data column and all data is serialized/stored as character
data.
3. other.

I don't want to alter tables because the same database may be used for
multiple departments with different udc requirements, and I also want to
minimize issues with data integrity. All of this has to be done with no DBA
participation - the requirements include the creation and use of the
user-defined data by the end-user.
Any ideas?

Thanks.

Uri Dimant

2006-03-15, 9:23 am

> I don't want to alter tables because the same database may be used for
> multiple departments with different udc requirements, and I also want to
> minimize issues with data integrity. All of this has to be done with no
> DBA participation - the requirements include the creation and use of the
> user-defined data by the end-user.
> Any ideas?

So SELECT just only columns that your manager wants

"Stan Spotts" <sspotts@community.nospam> wrote in message
news:OJ6K75DSGHA.1608@TK2MSFTNGP09.phx.gbl...
> I'm looking for a best practice for creating user-defined columns that
> allows for not-overly complicated methods for reporting. To show general
> requirements: a user or manager adds a few columns specific to his
> department, including "column" names. He then wants to create a report
> that includes these columns along with some of the default columns. Other
> users from other departments add other columns spefiic to themselves as
> well.
>
> I'm thinking that for the novice or regular users, setting up report
> models would suffice for column selection, while creating a table for all
> user defined types (key would be parent key plus department-key plus
> column-name key, maybe) would support holding the data, another for type
> info, but type enforcement and other issues are the things that I'm
> looking for at a best practice level.
>
> 1. Table that has one column of every type (with some max width for
> varchar, etc.)
> 2. Table has one data column and all data is serialized/stored as
> character data.
> 3. other.
>
> I don't want to alter tables because the same database may be used for
> multiple departments with different udc requirements, and I also want to
> minimize issues with data integrity. All of this has to be done with no
> DBA participation - the requirements include the creation and use of the
> user-defined data by the end-user.
> Any ideas?
>
> Thanks.
>

Stan Spotts

2006-03-15, 9:23 am

Either I don't understand the solution in your short response, or you didn't
understand the issues. Do I need to expand with examples? I wasn't sure if
I was clear enough in the requirements.

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:elGEU8DSGHA.1844@TK2MSFTNGP12.phx.gbl...
>
>
> So SELECT just only columns that your manager wants
>
>
>
> "Stan Spotts" <sspotts@community.nospam> wrote in message
> news:OJ6K75DSGHA.1608@TK2MSFTNGP09.phx.gbl...
>
>

Uri Dimant

2006-03-15, 9:23 am

Well, i understood your point, in my opinion it is really bad idea to allow
creating user-defined columns especially by end user
Perhaps you can look at Reporting Services that provides/generates report by
using .NET programming as well

That was exactly my point. Showing to each users only columns that tey
wanted, but again only one person should be responsible for adding/altering
db structure

"Stan Spotts" <sspotts@community.nospam> wrote in message
news:OMA6d$DSGHA.4452@TK2MSFTNGP12.phx.gbl...[color=darkred]
> Either I don't understand the solution in your short response, or you
> didn't understand the issues. Do I need to expand with examples? I
> wasn't sure if I was clear enough in the requirements.
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:elGEU8DSGHA.1844@TK2MSFTNGP12.phx.gbl...
>
>

Stan Spotts

2006-03-15, 11:23 am

I'm well aware of the evils of letting users do this, but as in everything,
context matters. In this case, it has to be done.

I've been looking at SQLRS as well as Report Builder (using report models to
abstract the data), but there are other parts of the system where the data
has to be used that need to be supported. I'll end up having to automate
things like index generation, modification of views, etc., but I'm still
looking for best practices about implementing this, not alternatives. I've
already suggested numerous alternatives that were not accepted. :(

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23hBgZEESGHA.5108@TK2MSFTNGP11.phx.gbl...
> Well, i understood your point, in my opinion it is really bad idea to
> allow creating user-defined columns especially by end user
> Perhaps you can look at Reporting Services that provides/generates report
> by using .NET programming as well
>
>
>
> That was exactly my point. Showing to each users only columns that tey
> wanted, but again only one person should be responsible for
> adding/altering db structure
>
>
>
> "Stan Spotts" <sspotts@community.nospam> wrote in message
> news:OMA6d$DSGHA.4452@TK2MSFTNGP12.phx.gbl...
>
>

Doug

2006-03-15, 8:23 pm

go read about meta data.

let the users add rows, which get reported as columns.

Octavius@gmail.com

2006-03-16, 9:23 am

What Doug said. This functionality of adding "columns" can easily be
supported but you should re-design the db structure so that they are in
essensce adding rows.

Sponsored Links