Query/Reporting Tool for SQL Server Tables

Just a general question about any recommendations for a third party tool that would allow setting up a data-dictionary against SQL Server tables and a user could go in and build their own queries, reports. Perhaps they could save their query/report definitions for reuse. Other nice to haves - ability to output to PDF and export to XLS. Would prefer web interface, but can consider desktop as well. Any immediate recommendations?

Thanks.Allowing users direct access to tables is exactly the sort of thing good DBAs AVOID doing.|||Now, Now...

http://www.cognos.com/products/cognos8businessintelligence/reporting.html?lid=//Products//Cognos8BI//Reporting|||Brett - thanks for the product suggestion. That's the exact type of suggestion I'm looking for and welcome others.|||Well, I know when I've been snubbed... :(
Cognos is a good product, and has been around for a while. You might also look into DI Diver: http://www.dimensionalinsight.com/|||Blindman - sorry if you were snubbed.

And great thanks for suggesting another potential tool.

Obviously I have a lot to learn - and you and other experts on this board have been key in me understanding. I'm indebted to you and others and am sending cases of vitual beer and wild women your way.|||I would recommend stylereport http://www.inetsoft.com. Definitely the best reporting tool around.

query with user-define function

I have a table with 3 fields.
In the first field named a there are values i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+3*b (where a,b
supposed to be the contents of the previous fields).
How can I issue a query to get back 5, 9, 32 (5+3*9)'
Many Thanks
HelenNot sure why you want to do this in SQL. Note that if dbo.foo has more than
one row, you will need to limit both queries using a WHERE clause to
identify that single row (unless the expression in c is always the same, in
which case, it shouldn't be in the table at all).
CREATE TABLE dbo.foo
(
a INT,
b INT,
c VARCHAR(32)
)
GO
SET NOCOUNT ON
GO
INSERT dbo.foo SELECT 5,9,'a+3*b'
GO
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b,'+c+' FROM dbo.foo'
FROM dbo.foo
EXEC(@.sql)
GO
DROP TABLE dbo.foo
GO
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen|||Hi,
You can have 3 solutions
1. Direct TSQL . Select a,b,(a+3*b) as c from table_name
2. Create a view. Create view v1 as Select a,b,(a+3*b) as c from table_name
and later use
select * v1
3. Use compute columns while table creation
create table cc(a int, b int, c AS (a + 3 * b))
WHILE INSERTION INSERT DATA ONLY FORM COLUMN a AND b
Thanks
Hari
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen

Query with user-define function

I have a table with 3 fields. In the first field named a there are values
i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+@.q+3*b where a,b
supposed to be the contents of the previous fields, different in each row
and @.q is a variable I wound like to type each time I run the query.
I have typed:
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
FROM dbo.foo where Index=1
EXEC(@.sql)
How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
Many Thanks
HelenDECLARE @.q INT
SET @.q = 7
SELECT a,b, a + @.q + 3 *b AS c
FROM dbo.foo
where Index=1
Jacco Schalkwijk
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||I'm sorry. I didn't explain myself correctly. I mean I have this table in a
SQL Server with many rows and different function in each row. Inside the
function I would like to have a variable (@.q) which I don't know how to writ
e
so as when I query I can put a different value each time.

> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>|||Hi
Maybe
CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
varchar(10) )
INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=1'
FROM dbo.foo where [Index]=1
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||Helen,
The T-SQL infix expression evaluator here might help:
http://users.drew.edu/skass/SQL/Infix.sql.txt
If you first replace the 'a', 'b', and @.q in your expression
with their values, InFixVal should then evaluate the result.
select
a, b,
dbo. InFixVal(replace(replace(replace(c,'a','
('+str(a,19,4)+')'),'b','('+str(
b,19,4)+')'),'@.q,str(@.q,19,4)),1)
from ...
Also look here, for some examples of its use, and comments
about its limitations. It only evaluates a simple set of possible
arithmetic expressions, but it may be enough for you.
http://groups.google.com/groups?hl=...ver&qt_s=Search
Steve Kass
Drew University
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>sql