Crystal reports grouping time stamps by hour

I made a report in crystal reports and its cross tabbed with Times on top, what i need to do is be able to group the times by hour but I have no clue how to do that

right now it looks like this:

8:01 8:11 8:15 8:45 9:15 9:34 and so on

it needs to be like this
8:00 9:00 and so on having everything in the 8-9 range under 8 and 9-10 under 9 and so on

I am using a Ms sql database and taking info from a number field numbers are in the format 800 for 8 oclock 930 for 9 thirty and so on

how would I group them like thisI'm not sure whether this folg is a right solution. but it'll work.

Try to create a formula and check the numeric value. Form a group using code
Like

If {table.field} in [800 to 830] then
1
else if {table.field} in [831 to 900] then
2
etc

Then group the report using this formula. To display the time interval create one more formula. Refer the previous formula here

i.e
if {@.prev} = 1 then
"8.00 - 8.30"
etc

Hope this will work.|||Thanx I will try that out,

Crystal Report : Cross Tab Formatting(Urgent)

Hi!I am new to cross tab in reports.I am using Seagate Crystal Report 7.Currently developing report with few tables in it. I am having problem grouping it into the fields that i want.I also wish to know how do i add formula in it..as well as writing queries in the access itself.I cant seems to do anything with the column/row on the report.I hope to have some guidelines.Thank You.Hi!I am new to cross tab in reports.I am using Seagate Crystal Report 7.Currently developing report with few tables in it. I am having problem grouping it into the fields that i want.I also wish to know how do i add formula in it..as well as writing queries in the access itself.I cant seems to do anything with the column/row on the report.I hope to have some guidelines.Thank You.

Hi!!!
I'm also new with crystal reports but i have already done a cross tab report using 8.5...anyway, u can use the wizard to guide u in creating ur crosstab...or u can explain further what u want to do so we could help each other in solving ur problem... :)|||I created a cross tab query in Ms Access..hoping to link to Crystal Report 7. But when i tried to arrange the columns n rows..i cant edit anything.eg: There's summarized field to be set in Crystal report 7. I dont want it to count my field..instead displaying just the data. How can i do it?

Cross-tab Row Title

CR XI posted a work-around/solution for inserting a row title in a cross tab report by creating a separate "fake" group, overlaying it and copying the cross tab to the fake group's section. It also discussed horizontal pages.

How do you repeat the label on subsequent/multiple pages though?See if you find answer here

http://support.businessobjects.com/

Crosstab Queries in SQL Server 2005

Hi there,

I'm trying to make a cross tab query in SQL Server 2005 SP2, I have it done in MS Access but I want to switch all my queries that use crosstabs into SQL.

I find this Query on the web:

Code Snippet

CREATE PROCEDURE [sp_CrossTabIntoTable]

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

But when I tried to run it I got this error message:

Msg 156, Level 15, State 1, Procedure sp_CrossTabIntoTable, Line 25

Incorrect syntax near the keyword 'pivot'.

I need it ASAP because I need to create new reports and I don't want to use MS Access no more.

Is there someone who knows what the problem is?

Thanks in advance,

Julien

PIVOT is a reserved keyword in 2005. Either change the column name eg. Pvt or put identifiers around the column name eg 'Pivot'

HTH
|||

Ok PIVOT is the operator/keyword in SQL Server 2005 use the following query...

Code Snippet

CREATE PROCEDURE [sp_CrossTabIntoTable]

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS [pivot] INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

|||THANK YOU BOTH!!

I finally get it working!!

Best regards

Julien

Crosstab Help in Sql Server

I need some assistance, i have this Stored Procedure that will take my table
and create a cross tab. Unfor, I am getting duplicate value from the output,
when checking table1, there are no dups, i dont' know what i am doing wrong.
Please help
below, code that I am using. thaks
=================================
DECLARE @.Month_1_V as varchar(20)
DECLARE @.Create_Date_V as Datetime
DECLARE @.Corp_V as Numeric(13)
DECLARE @.Source_V as Varchar(20)
DECLARE @.Category_V as Varchar(50)
DECLARE @.Description_1_V as Varchar(50)
DECLARE @.Cycle_V as Varchar(1)
DECLARE @.Count_1_V as Numeric(13)
DECLARE @.Month_1_V2 as varchar(20)
DECLARE @.Create_Date_V2 as Datetime
DECLARE @.Corp_V2 as Numeric(13)
DECLARE @.Source_V2 as Varchar(20)
DECLARE @.Category_V2 as Varchar(50)
DECLARE @.Description_1_V2 as Varchar(50)
DECLARE @.Cycle_V2 as Varchar(1)
DECLARE @.Count_1_V2 as Numeric(13)
DECLARE @.V1 as Numeric(13)
DECLARE @.V2 as Numeric(13)
DECLARE @.V3 as Numeric(13)
DECLARE @.V4 as Numeric(13)
DECLARE @.V5 as Numeric(13)
DECLARE @.V6 as Numeric(13)
DECLARE @.V7 as Numeric(13)
DECLARE @.V8 as Numeric(13)
DECLARE @.V9 as Numeric(13)
DECLARE @.V10 as Numeric(13)
DECLARE @.V11 as Numeric(13)
truncate table tbl_Agency
DECLARE t_Noble_Agency CURSOR
FOR
SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
AS Count_1, Description_1, Cycle
FROM phonecol.temp_tblAgency
WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 102)
AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycle
ORDER BY Category, Corp, Source
OPEN t_Noble_Agency
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
IF @.@.FETCH_STATUS = 0
BEGIN
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
@.Source_V)
BEGIN
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
END
--@.V1 = 'Call_Backs'
--@.V2 = '[Left_Msg(Machine)]'
--@.V3 = '[Left_Msg(Live)]'
--@.V4 = 'Promise_to_Pay'
--@.V5 = 'Full_Pay'
--@.V6 = 'Partial_Pay'
--@.V7 = 'Past_Due_Pay'
--@.V8 = 'Wrong_Number'
--@.V9= 'TriTones'
--@.V10 = 'Skip_Trace'
--@.V11 = 'Not_Reported'
IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
Begin
SET @.V1 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
Begin
SET @.V2 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
Begin
SET @.V3 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
Begin
SET @.V4 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
Begin
SET @.V5 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
Begin
SET @.V6 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
Begin
SET @.V7 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
Begin
SET @.V8 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
Begin
SET @.V9 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
Contact'
Begin
SET @.V10 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
Begin
SET @.V11 = @.Count_1_V
END
END
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
END
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
END
CLOSE t_Noble_Agency
DEALLOCATE t_Noble_Agency
GOHi Justin,
I didn't look close into the SP. But from a high level I find that you
have two inserts for 1 fetch within the while loop (one inside the if
condition and one outside in the end). Can you tell why you are doing it.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:

> I need some assistance, i have this Stored Procedure that will take my tab
le
> and create a cross tab. Unfor, I am getting duplicate value from the outpu
t,
> when checking table1, there are no dups, i dont' know what i am doing wron
g.
> Please help
> below, code that I am using. thaks
> =================================
> DECLARE @.Month_1_V as varchar(20)
> DECLARE @.Create_Date_V as Datetime
> DECLARE @.Corp_V as Numeric(13)
> DECLARE @.Source_V as Varchar(20)
> DECLARE @.Category_V as Varchar(50)
> DECLARE @.Description_1_V as Varchar(50)
> DECLARE @.Cycle_V as Varchar(1)
> DECLARE @.Count_1_V as Numeric(13)
> DECLARE @.Month_1_V2 as varchar(20)
> DECLARE @.Create_Date_V2 as Datetime
> DECLARE @.Corp_V2 as Numeric(13)
> DECLARE @.Source_V2 as Varchar(20)
> DECLARE @.Category_V2 as Varchar(50)
> DECLARE @.Description_1_V2 as Varchar(50)
> DECLARE @.Cycle_V2 as Varchar(1)
> DECLARE @.Count_1_V2 as Numeric(13)
> DECLARE @.V1 as Numeric(13)
> DECLARE @.V2 as Numeric(13)
> DECLARE @.V3 as Numeric(13)
> DECLARE @.V4 as Numeric(13)
> DECLARE @.V5 as Numeric(13)
> DECLARE @.V6 as Numeric(13)
> DECLARE @.V7 as Numeric(13)
> DECLARE @.V8 as Numeric(13)
> DECLARE @.V9 as Numeric(13)
> DECLARE @.V10 as Numeric(13)
> DECLARE @.V11 as Numeric(13)
> truncate table tbl_Agency
> DECLARE t_Noble_Agency CURSOR
> FOR
> SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
> AS Count_1, Description_1, Cycle
> FROM phonecol.temp_tblAgency
> WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 10
2)
> AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
> GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycl
e
> ORDER BY Category, Corp, Source
> OPEN t_Noble_Agency
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> IF @.@.FETCH_STATUS = 0
> BEGIN
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
> @.Source_V)
> BEGIN
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> END
> --@.V1 = 'Call_Backs'
> --@.V2 = '[Left_Msg(Machine)]'
> --@.V3 = '[Left_Msg(Live)]'
> --@.V4 = 'Promise_to_Pay'
> --@.V5 = 'Full_Pay'
> --@.V6 = 'Partial_Pay'
> --@.V7 = 'Past_Due_Pay'
> --@.V8 = 'Wrong_Number'
> --@.V9= 'TriTones'
> --@.V10 = 'Skip_Trace'
> --@.V11 = 'Not_Reported'
> IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
> Begin
> SET @.V1 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
> Begin
> SET @.V2 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
> Begin
> SET @.V3 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
> Begin
> SET @.V4 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
> Begin
> SET @.V5 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
> Begin
> SET @.V6 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
> Begin
> SET @.V7 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
> Begin
> SET @.V8 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
> Begin
> SET @.V9 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
> Contact'
> Begin
> SET @.V10 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
> Begin
> SET @.V11 = @.Count_1_V
> END
> END
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> END
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> END
> CLOSE t_Noble_Agency
> DEALLOCATE t_Noble_Agency
> GO|||sure,
what happens first is that data will be imported to a temporary table
from there, the SP should take the data from that table and change it to a
crosstab
inserting output to finalize table
unfor, in the finalize table, i am getting duplicated values
"Omnibuzz" wrote:
> Hi Justin,
> I didn't look close into the SP. But from a high level I find that you
> have two inserts for 1 fetch within the while loop (one inside the if
> condition and one outside in the end). Can you tell why you are doing it.
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Justin" wrote:
>|||Where is the insert script for the finalise table?
I assume that tbl_Agency is the temp table you are referring to and it can
have duplicates. Can you post the cross tab query?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:
> sure,
> what happens first is that data will be imported to a temporary table
> from there, the SP should take the data from that table and change it to a
> crosstab
> inserting output to finalize table
> unfor, in the finalize table, i am getting duplicated values
>
> "Omnibuzz" wrote:
>

CrossTab

Hi Guys!
Is there anything like cross tab of access in sql server?
Thanks.Hafez Rabbani (hafezrabbani@.gmail.com) writes:
> Hi Guys!
> Is there anything like cross tab of access in sql server?

You can do cross-tabs in SQL Server, but it it is not as straightforward
as it is in Access.

There is a section in Books Online that gives some tips about this:
Accessing and Changing Relational Data / Transact-SQL Tips / Cross-Tab
Reports

That section covers static crosstabs. Dynamic crosstabs requires use of
dynamic SQL and requires more work. There is a third-party product, RAC,
which is popular for this, check out http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

'Crosspost' Structure a New App (SQLServer C# - Security & Db Connecctions)

Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
--Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.

> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.

> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.

> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.

> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.

> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.

> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?

> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
--|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.

> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
--
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

'Crosspost' Structure a New App (SQLServer C# - Security & Db Connecctions)

Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.

> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.

> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.

> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.

> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.

> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.

> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?

> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.

> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

CrossJoin of two large sets....

I am looking for the best way to display a date related to a lot.

The problem is when I cross join to a date dimension the performance is terrible. Is there anyway to improve performance?

Code Snippet

WITH

MEMBER x

MEMBER y

SELECT NON EMPTY { [Measures].[X], [Measures].[Y] } ON COLUMNS,

NON EMPTY { ([Lot].[Lot].[Lot].ALLMEMBERS * [Voter Source].[Voter Source Code].[Voter Source Code].ALLMEMBERS)* [Date].ALLMEMBERS ) } ON ROWS

FROM [CUBE]

What do the definitions of X and Y look like - that would affect performance? And how many empty cross-joined rows are to be eliminated?

Cross VPN Domain Authentication

Hi,
We use a cisco point-to-point VPN to allow employees to connect to the
corporate network from their remote computers that are not members of the
corporate Active Directory domain.
When VPN connected from my non-domain member computer, I open the Windows'
"Run" dialog box and type the path of a shared folder on the corporate
network. After a brief delay I get the "Connect to <path>" dialog box that
requests a User name and Password that I would like to use to connect to the
share. I provide suitable credentials, and after another brief delay I am
connected to the share, and I can open documents and copy files to and from
the share (as folder permissions allow).
So far, however, when I write .NET desktop applications that use the
SqlConnection class to connect to SQL Server via domain authentication, I
cannot get an application to connect through the VPN*. Is there any way to
get a database application to pop up a "Connect to <SqlInstance>" dialog box
so that users can provide their domain credentials to get a connection to
SQL Server from a computer that is not a domain member? I'm interested in
both the SQL 2000 and SQL 2005 cases.
* I get "Login failed for user ''. The user is not associated with a trusted
SQL Server connection..."
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel,
I understand that when your .NET application tried to connect to your SQL
Server in your coporate domain from a non-domain member computer via VPN,
it failed with the error "Login failed for user ''".
If I have misunderstood, please let me know.
Please check the authentication mode of your SQL Server in Enterprise
Manager or SSMS (SQL Server 2005 Management Studio). If it is Windows
Authentication mode, please change it to Mixed mode and create a SQL login
for the connection of the non-domain computer.
For the client connection string, I recommend that you refer to this
article:
How To: Connect to SQL Server Using SQL Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:%23d5h3kFqHHA.196@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We use a cisco point-to-point VPN to allow employees to connect to the
> corporate network from their remote computers that are not members of the
> corporate Active Directory domain.
> When VPN connected from my non-domain member computer, I open the Windows'
> "Run" dialog box and type the path of a shared folder on the corporate
> network. After a brief delay I get the "Connect to <path>" dialog box
> that requests a User name and Password that I would like to use to connect
> to the share. I provide suitable credentials, and after another brief
> delay I am connected to the share, and I can open documents and copy files
> to and from the share (as folder permissions allow).
> So far, however, when I write .NET desktop applications that use the
> SqlConnection class to connect to SQL Server via domain authentication, I
> cannot get an application to connect through the VPN*. Is there any way
> to get a database application to pop up a "Connect to <SqlInstance>"
> dialog box so that users can provide their domain credentials to get a
> connection to SQL Server from a computer that is not a domain member? I'm
> interested in both the SQL 2000 and SQL 2005 cases.
Try using Run As to execute the app, with the right-click->Run As... menu,
or by creating a shortcut to the EXE and setting the "Run with different
credentials" checkbox (properties->shortcut [tab]->Advanced [button]
.) The
former is a one-off way to do it; the latter causes a login prompt when the
shortcut is used to run the app.
When prompted, specify domain credentials (in domain\user, or user@.domain
format) for the impersonation context. If that works, a Win32 app could
call LoginAsUser to internally provide a seamless login facility... not sure
what the .net equivilent is -- but if the Run As login prompt is adequate,
it's a moot point.
-Mark

> * I get "Login failed for user ''. The user is not associated with a
> trusted SQL Server connection..."
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
>|||Hi Daniel,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I am very glad to work with you for further research.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thank you. We are currently using an encrypted connection string approach
similar to that described in your MSDN reference. I was hoping to get
around having to maintain those SQL Server logins and the user ambiguity
that comes with using a common login for all users.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:FCq36SQrHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I am very glad to work with you for further research.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Daniel,
Did you mean that you wanted to have each of your desktop .NET application
impersonating a different user which could be used for SQL login?
If so, I recommend that you refer to this KB article for adding the
impersonation code:
How to implement impersonation in an ASP.NET application
(Impersonate a Specific User in Code)
http://support.microsoft.com/kb/306158
Hope this helps. Please feel free to let me know if you have any other
questions or concerns.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Daniel,
What is everything going on? If you have any questions or concerns, please
feel free to post back.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Cross tabs with Drill downs

Hi Guys
I need to generate a report where I need to have drill downs in a matrix(cross tab query) in Business Intelligence.
When I try to build a report using the report wizard, I can either select matrix or Drill downs and subtotals.
Can I have both of these options?

Thanks
Mita

Moving to AS forum.

Cross tabs problem in sql server

I want a query which is used in sql server like access query

TRANSFORM Sum(Q_DayBook.Debit) AS SumOfDebit
SELECT Q_DayBook.Purticular, Sum(Q_DayBook.Debit) AS [Total Of Debit]
FROM Q_DayBook
GROUP BY Q_DayBook.Purticular
PIVOT Q_DayBook.CDate;Unfortunately, SQL Server does not have the same built-in functionality that Access does. The solution is to either do the pivot in the presentation layer, or to use some specialized Transact-SQL code in the database.

See this previous discussion for further details:view post 346479

Terri

Cross Tabs and Groupings

I m trying to struct a query that will give me the impression reports. I want a table that give the page name ,yesterday hits,todays hits, this weeks hits and this month hits.
My Table is like this ..
tblStat----
MID -- key id incrementing with no replication
MURL -- name of page
MIMp -- Counted impression at that day
MDate -- Smalldatetime value
------
returning rows are similar like:
1 index.asp 5 13/12/2003
2 main.asp 2 13/12/2003
3 index.asp 3 14/12/2003
4 main.asp 8 14/12/2003
--
I tried to do a query and it is :
SELECT DISTINCT dbo.tblStats.MURL, INSERT1.MToday, INSERT2.Mpre, INSERT3.MWeek, INSERT4.MMonth
FROM dbo.tblStats LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS Mpre
FROM tblStats
WHERE (Mdate = '3/18/2003')
GROUP BY MURL) INSERT2 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MToday
FROM tblStats
WHERE (Mdate = '3/19/2003')
GROUP BY MURL) INSERT1 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MWeek
FROM tblStats
WHERE (MDate >= '3/15/2003' AND MDate <= '3/21/2003')
GROUP BY MURL) INSERT3 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MMonth
FROM tblStats
WHERE (MDate >= '3/01/2003' AND MDate <= '3/29/2003')
GROUP BY MURL) INSERT4 ON dbo.tblStats.MURL = dbo.tblStats.MURL
------------
but this query returning to me too much records. Because i only have to pages in table that must be two records to return me back. If anyone know how to solve this please response me. Thank you from now on.How About something using the [Case When] & DatePart

SELECT DISTINCT dbo.tblStats.MURL,
SUM(CASE WHEN MDate = Getdate() THEN MIMP ELSE 0 END) AS Today,
SUM(CASE WHEN MDate = (Getdate()-1) THEN MIMP ELSE 0 END) AS YesterDay,
SUM(CASE WHEN MDate = (Getdate()+1) THEN MIMP ELSE 0 END) AS Tommorow,
SUM(CASE WHEN DatePart(wk, MDate) = DatePart(wk, GetDate()) THEN MIMP ELSE 0 END) AS ThisWeek,
FROM tblStats

Or Something like that

PS. Joking about the Tommorow One - lol

GW|||GWilliy,
no distinct, but group by. Do not use abrevs for time parts, it is missguiding.
wk=week

SELECT dbo.tblStats.MURL
...
GROUP BY dbo.tblStats.MURL

hexcode,
be sure you don't store time. Also note that function DATEPART(week,@.D datetime) is nondeterministic (SET DATEFIRST/LANGUAGE dependent).

Good luck!|||The query really worked fine. Just i send the date values from program instead of getdate. Thank you for your help.

Cross table copy working in Query Analyser, but not from code

I'm trying to copy a row from one table to another for audit purposes
using a 'INSERT INTO x SELECT y FROM z' statement. This works
absolutely fine in query analyser, however, when running the exact same
statement from code (.NET via oledb), it fails with the error:
An explicit value for the identity column in table 'x' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
or when specifying all columns:
Cannot insert explicit value for identity column in table 'x' when
IDENTITY_INSERT is set to OFF
Table 'x' has no identity columns, table 'y' has one (integer ID)
identity column.
Anyone have any ideas why this statement would work in the query
analyser, and not in code?
(SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)I would guess you are connected to different databases/instances and the
table schema are different. If needed, you can run a Profiler trace to
verify this.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137432432.481007.178730@.f14g2000cwb.googlegroups.com...
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
> (SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)
>|||That's definately not the issue. I'm fairly experienced with database
applications, despite using .net. I've just never come across an
instance whereby the query analyser gave different results to the oledb
components.
Does either the Query Analyser or oledb .net component do anything
unusual behind the scenes? Or is it a possibility that this is
happening because in my program it is taking place inside a
transaction? (it's the first thing in the transaction, and the
exception is thrown immediately on adding the command to the
transaction)
Thanks for the reply Dan|||A Profiler trace should show all that is going on. I can't think of
anything that would cause different behavior for a single statement like
this. The error clearly indicates the target table has an identity column.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137437890.787728.312550@.o13g2000cwo.googlegroups.com...
> That's definately not the issue. I'm fairly experienced with database
> applications, despite using .net. I've just never come across an
> instance whereby the query analyser gave different results to the oledb
> components.
> Does either the Query Analyser or oledb .net component do anything
> unusual behind the scenes? Or is it a possibility that this is
> happening because in my program it is taking place inside a
> transaction? (it's the first thing in the transaction, and the
> exception is thrown immediately on adding the command to the
> transaction)
> Thanks for the reply Dan
>|||Rory (rory.smith@.gmail.com) writes:
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
One thing to check is that there are not two table x in the database.
Own owned by dbo, which I assume that you run as from Query Analyzer,
and one owned by the user that you connect with from the application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland - you were right - I'm unsure of where the 'phantom' table came
from - I created the database from a script which only has one instance
of the table. Anyway, all sorted now.
Many thanks to you both

Cross table constraint -- possible?

Consider these four tables:
TableA:
Columns: ID (PK), Type (FK-TableA_Types), etc.
TableA_Types:
Columns: ID (PK), Name
Table B:
Columns: ID (PK), Type (FK-TableB_Types), etc.
TableB_Types:
Columns: ID (PK), Name

TableA has a FK column to the TableA_Types table (TableA.Type->TableA_Types.ID), and TableB has a FK column to the TableB_Types table (TableB.Type->TableB_Types.ID).

The problem is that TableA_Types and TableB_Types overlap, and should really be in the same table. The problem is, they're not, and the given the large amount of work that it would involve in consolidating the tables, it would preferable to simply add a constraint to TableA_Types and TableB_Types such that their IDs don't overlap.

In other words, I want to avoid this situation:
TableA_Types:
1 name1
2 name2

TableB_Types:
1 name3
2 name4

Can I add a constraint on TableA_Types such that it prevents new records from having the same IDs as any ID for records in the TableB_Types table, and vice versa?

If so, how? If not, any other suggestions?

Thanks!Are these identity fields? If you can get away with changing the identities for these tables, just re create the identitiy columns so that one table only uses odd numbers and another table only uses even numbers.|||

This is not possible with a constraint.

You could achieve this with a trigger if you really want this situation to be blocked.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

To directly answer you questions -No, not a table constraint; and yes, you have alternatives.

You can use the UNIQUEIDENTIFIER datatype for the primary key in each table -there will never be any overlap. But I don't think that that really makes much sense in this situation. The keys are too large and unwieldy.

Cam's suggestion is interesting, and just 'might' work for you. Set the IDENTITY seed = 1 in one table, and the seed = 2 in the other table, and the increment value = 2 in both tables. As long as the row counts in each table don't approach 1 billion, you 'should' be OK. Set a table constraint in each table that maintains the odd or even scheme.

And Wesley's suggestion to use a TRIGGER is the most 'bullet proof' method -all the while requiring the most server resources.

But the real issue is this: Why do you not take the time and effort and correct a underlaying defect that you know exists in the database?

Leaving it is like having dryrot in your house and just painting over it.

Once I know that dryrot has been painted over, I'm instantly on alert for other signifianct defects waiting like time bombs ...

|||You can "sort of" do it. A constraint can actually access other tables through a function, but it can get messy to do this. So like several other folks have said, a trigger is the thing to do|||

I agree with Arnie though, correcting the database design is actually the best solution.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thanks for the posts, guys. I've implemented a check using triggers. Not the most elegant or best solution, I agree, but it will have to do for now.

cross table

Sir,

My query is

select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/11/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/12/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/13/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/14/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/15/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/16/2006'
group by task_date

My out put is

Date Program Design Upload Testing Meeting Others

2006-09-11 00:00:00.000 42 0 0 8 2 1

2006-09-12 00:00:00.000 77 0 0 4 0 0

2006-09-13 00:00:00.000 56 0 0 8 0 1

2006-09-14 00:00:00.000 63 0 0 6 0 1

2006-09-15 00:00:00.000 63 0 0 6 0 1

Now i want in below format

2006-09-11 2006-09-11 etc

Program 42 77

Design 0 0

Upload 0 0

Testing 8 4

Meeting 2 0

Others 1 0

Total 53 81

How to convert in this format .

--From your output as table:transposetable$:

SELECT ISNULL(cat, 'Total') ,SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',

SUM([9/15/2006]) AS '9/15/2006'

FROM (SELECT cat, MIN(CASE WHEN t.tDate = '9/11/2006' THEN Program END) AS '9/11/2006',

MIN(CASE WHEN t.tDate = '9/12/2006' THEN Program END) AS '9/12/2006',

MIN(CASE WHEN t.tDate = '9/13/2006' THEN Program END) AS '9/13/2006',

MIN(CASE WHEN t.tDate = '9/14/2006' THEN Program END) AS '9/14/2006',

MIN(CASE WHEN t.tDate= '9/15/2006' THEN Program END) AS '9/15/2006'

FROM

(SELECT 'Program' as cat, tDate, Program FROM transposetable$

UNION ALL

SELECT 'Design', tDate, Design FROM transposetable$

UNION ALL

SELECT 'Upload',tDate, Upload FROM transposetable$

UNION ALL

SELECT 'Testing', tDate, Testing FROM transposetable$

UNION ALL

SELECT 'Meeting', tDate, Meeting FROM transposetable$

UNION ALL

SELECT 'Others', tDate, Others FROM transposetable$

) t

GROUP BY cat ) x

GROUP BY cat

WITH ROLLUP

|||

--SQL Server 2005, Louis's idea. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

WITH myCTE as(

select tDate, cat, value

from ( select tDate, Program, Design, Upload, Testing, Meeting,Others

from transposetable$) p

UNPIVOT

(value for cat in ([Program], [Design], [Upload],[Testing],[Meeting],[Others])) as unpvt)

SELECT ISNULL(cat, 'Total'),SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',SUM([9/15/2006]) AS '9/15/2006'

FROM

(select cat, tDate,value

from myCTE) as rotated

PIVOT

(SUM(value) FOR tDate in ([9/11/2006],[9/12/2006],[9/13/2006],[9/14/2006],[9/15/2006])) as pvt

GROUP BY cat

WITH ROLLUP

|||

Hi,

have you looked at the UNPIVOT function?

Sten-Gunnar

Cross Tab?

I need to format the following tables:

tbEmployees
EmployeeID | fName | lName
--------------
jdoe | Joe | Doe
bsmith | Blake | Smith

tbDepartments
DepartmentID | Department
----------
ENG | Engineering
DET | Detailing

tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
----------
ENG | jdoe
DET | bsmith

tbProjects
ProjectID |
-----
1001

tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
--------------
1001 | jdoe | ENG
1001 | bsmith | DET

To the following view :

vProjects
ProjectID | Engineer | Detailer
------------
1001 | Joe Doe | Blake Smith

Any Ideas?
Mike BSQL server does not have built-in crosstab querys as Access does. You can replicate a cross-tab query using CASE statements. It looks intimidating at first, but it actually pretty straight-forward. Look up Crosstab in Books Online for a good explanation and example.

cross tab?

thought my question was misinterpreted.

actual table can be smaller or larger. but,
within a procedure need to turn a created #temptable
similar to(examid is in asc order):
(id, p/f, examid, examname)
1 f xxxx xxxxx
1 f xxxx xxxxx
1 f xxxx xxxxx
2 p
3 p
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx

(note, id = 4 has 7 failed exams).

to a table with up to five columns of failed
examnames:

1 f xxxxx xxxxx xxxxx
2 p
2 p
4 f xxxxx xxxxx xxxxx xxxxx xxxxx

can get rid of exameid if helps.

--
Sent by ricksql from yahoo subpart from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/newso what you need is a crosstab table with a 5 recors limit only ?

Jens Smeyer.|||Dear Jens S|_meyer,

> so what you need is a crosstab table with a 5 recors limit only ?
> Jens S|_meyer.

yes.

within a procedure need to turn a created #temptable
similar to:
(id, p/f, examid, examname)
1 f xxxx xxxxx
1 f xxxx xxxxx
1 f xxxx xxxxx
2 p
3 p
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx

(note, id = 4 has 7 failed exams).

to a table with up to five columns of failed
examnames:

1 f xxxxx xxxxx xxxxx
2 p
2 p
4 f xxxxx xxxxx xxxxx xxxxx xxxxx

can get rid of exameid if helps.

--
Spam protected message from:
Sent by ricksql from yahoo subpart from com
Posted via http://www.usenet-replayer.com/cgi/content/new

Cross Tab/Pivot table/UDF?

I have the following tables:

tbTemplateShapeProperties
fkTemplate | fkProperty | PropertyValue
---------------
1 | 1 | 192
1 | 2 | 36
1 | 3 | 4
1 | 4 | 5
1 | 5 | 2

tbShapeProperties
Property | PropertyName | fkShape
--------------
1 | Width | 1
2 | Height | 1
3 | Flange | 1
4 | Avg. Leg Width | 5
5 | Leg Count | 2

From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF

XSection (Width, Height, Flange, Leg, LegCount)

I tried the following to get a pivot table but it does not give a single row but 5.

SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width,
CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height,
CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange,
CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg,
CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = 1

The following results are returned:

Width | Height | Flange | Leg | LegCount
--------------
192 | 0 | 0 | 0 | 0
0 | 36 | 0 | 0 | 0
0 | 0 | 6 | 0 | 0
0 | 0 | 0 | 5 | 0
0 | 0 | 0 | 0 | 2

The desired result as you could guess is:

Width | Height | Flange | Leg | LegCount
--------------
192 | 36 | 6 | 5 | 2

So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?

Any hints?

Mike BYeah, I did it!!!

Here is the code:

CREATE PROCEDURE usp_GetDoubleTCrossSection

@.iTemplate int,
@.fXSection float OUTPUT

AS

declare @.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float

set @.iTemplate = 1
SELECT @.fWidth = SUM(CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ElSE 0 END),
@.fHeight = SUM(CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ElSE 0 END),
@.fFlange = SUM(CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ElSE 0 END),
@.fLegWidth = SUM(CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ElSE 0 END),
@.fLegs = SUM(CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ElSE 0 END)
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = @.iTemplate

SELECT @.fXSection = [dbo].[TEE_XSECTION](@.fWidth, @.fHeight, @.fFlange, @.fLegWidth, @.fLegs)
GO

//UDF
CREATE FUNCTION TEE_XSECTION
(@.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float)

RETURNS float

AS
BEGIN
RETURN (@.fWidth * @.fFlange + (((@.fHeight - @.fFlange)*@.fLegWidth)*@.fLegs))
END

No cursor!!! Whew!

Mike B

cross tab type query in sql

I have three tables:
tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
tblBookRange has the fields bookRangeID, bookRangeDescription
tblBookSubject has the fields bookSubjectID, bookSubjectDescription
so some typical data in tblBook might be:
1, 1, 1, B1HBSCI
2, 1, 2, B2HBFRE1
3, 1, 3, B3HBGER
4, 2, 1, B4PBSCI
5, 2, 2, B5PBFRE
6, 2, 3, B6PBGER
7, 3, 1, B7CDSCI
8, 3, 2, B8CDFRE
9, 3, 3, B9CDGER1
10, 3, 3, B10CDGER2
11, 1, 2, B11HBFRE2
tblBookRange would be:
1, HardBack
2, PaperBack
3, CD Rom
tblBookSubject would be:
1, Science
2, French
3, German
I'd like to create a query which will return me the subjects along the
top, the book range down the side, and the bookcodes in the cells, a
bit like this:
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
Does that make any sense? So basically I'd like to get some kind of
dynamic SQL working which will do this kind of thing. I don't want to
hard code the subjects in or the book ranges. I get the feeling that
dynamic SQL is the way forward with this and possibly using a cursor or
two too, but it got quite nasty and convoluted when I tried various
attempts to get it working. (one of the ways I tried included working
out each result in a dynamic script, but it ran out of characters as
there were too many "subjects".)
If anyone has any nice but quite dynamic solutions, I'd be delighted to
hear.
(and I know some of you have already told me you don't like tables
beginnig with tbl, but I'm not hear for a lecture on naming
conventions, I'm hear to learn and share ideas :o) )The RAC utility could do this for you quite easily (similiar in
concept to Access crosstab but much more powerful).
But then you wouldn't have the opportunity to write your
own dynamic code and maintain it:)
www.rac4sql.net|||The RAC utility is well worth the relatively inexpensive cost. I think it i
s
$60 per database or $300+ for a site license. If you are doing many crossta
b
queries, you will save this much in time in the first couple of days.
Another thing to note though, the not-yet-officially-released SQL Server 200
5
has a pivot function which will easliy perform crosstab queries.
Archer
"Pike" wrote:

> The RAC utility could do this for you quite easily (similiar in
> concept to Access crosstab but much more powerful).
> But then you wouldn't have the opportunity to write your
> own dynamic code and maintain it:)
> www.rac4sql.net
>
>|||If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL (or
a
report writer that uses dynamic SQL) and all the evil that goes with it or h
ave
your report writer create your crosstab type result. You could also use
something like Excel (or other report writer) to call to a stored proc to ge
t
the data you want and have Excel do the pivoting/crosstabbing.
Thomas
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL stateme
nt
and return the results)?
Thomas
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
> The RAC utility is well worth the relatively inexpensive cost. I think it
is
> $60 per database or $300+ for a site license. If you are doing many cross
tab
> queries, you will save this much in time in the first couple of days.
> Another thing to note though, the not-yet-officially-released SQL Server 2
005
> has a pivot function which will easliy perform crosstab queries.
> Archer
> "Pike" wrote:
>|||You could do something like this:
create table tblBook (
bookID int, bookRangeID int, bookSubjectID int, bookCode varchar(50))
insert into tblBook values(1, 1, 1, 'B1HBSCI')
insert into tblBook values(2, 1, 2, 'B2HBFRE1')
insert into tblBook values(3, 1, 3, 'B3HBGER')
insert into tblBook values(4, 2, 1, 'B4PBSCI')
insert into tblBook values(5, 2, 2, 'B5PBFRE')
insert into tblBook values(6, 2, 3, 'B6PBGER')
insert into tblBook values(7, 3, 1, 'B7CDSCI')
insert into tblBook values(8, 3, 2, 'B8CDFRE')
insert into tblBook values(9, 3, 3, 'B9CDGER1')
insert into tblBook values(10, 3, 3, 'B10CDGER2')
insert into tblBook values(11, 1, 2, 'B11HBFRE2')
create table tblBookRange (bookRangeID int, bookRangeDescription
varchar(50))
insert into tblBookRange values (1, 'HardBack')
insert into tblBookRange values (2, 'PaperBack')
insert into tblBookRange values (3, 'CD Rom')
create table tblBookSubject(bookSubjectID int, bookSubjectDescription
varchar(50))
insert into tblBookSubject values(1, 'Science')
insert into tblBookSubject values(2, 'French')
insert into tblBookSubject values(3, 'German')
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
declare variables
declare @.p char(1000)
declare @.i char(12)
declare @.cntm int
declare @.cntn int
declare @.m int
declare @.n int
set @.p = ''
select @.cntm=count(distinct bookRangeID) from tblBookRange
set @.m = 1
select @.cntn=count(distinct bookSubjectID) from tblBookSubject
set @.n = 1
Process until no more items
while @.m <= @.cntm
begin
while @.n <= @.cntn
begin
string together all items with a comma between
select @.i = bookRangeDescription, @.p = rtrim(@.p) + ' ' + bookCode
from tblBook a join tblbookRange b on a.bookRangeID = b.bookRangeID
where a.bookRangeID = @.m and a.bookSubjectID = @.n
set @.n = @.n + 1
set @.p = rtrim(@.p) + ','
end
print detail row
print @.i + ' ' + rtrim(substring(@.p,1,len(@.p)))
set @.n = 1
set @.m = @.m + 1
set @.p = ''
end
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Yes. Here is the sql to execute a RAC stored procedure. The tool that
comes with RAC is not very straightforward, but manipulating the SQL to get
what you want is.
Execute rac
@.transform = 'max(ltrim(rtrim([qyRac_hits].[x]))) as result))',
@.rows = '[qyRac_hits].[test_name] & [qyRac_hits].[Analyte]',
@.pvtcol = '[qyRac_hits].[ENVIRON_sample_id_mod]',
@.racheck = 'y',
@.from = '[qyRac_hits]',
@.where = 'ENVIRON_sample_id_mod like ~%emw1-%~
and collection_date < ~4/1/2005~',
@.grand_totals='n',
@.defaultexceptions='Funct & Totals'
Archer
"Thomas Coleman" wrote:

> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL state
ment
> and return the results)?
>
> Thomas
>
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
> news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
>
>|||Hello Thomas,
For what it's worth:
Most novice and even experienced sql programmers assume RAC
builds a classic dynamic SELECT statement consisting of gobs
of classic CASE statements as seen in the plethora of sql books
illustrating an sql crosstab.This is NOT what RAC does.Given all
the functionality/features available, this method would be next to
impossible
and even if it could be done it probably would bring the server to a hault.
The basic dynamic query built is a SELECT/GROUP BY query based
on the transform (aggregate(s)), row(s) and the column to be pivoted.
This is the building block from which xtabs,various ranking functionality
and all other basic questions/options start with.The default method that
RAC uses to build the actual result utilitized bcp.So anything you see
in a text book or on a site that shows how to develope a dynamic
crosstab in t-sql bears absolutely no resemblence to RAC:)
The real goal of RAC is to unburdon the user/developer of the messy
details of creating a myraid of dynamic results for various data
manipulation problems without stressing out the query optimizer.
Another example of *What* not *How*:)
Best from,
www.rac4sql.net
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL
> statement and return the results)?|||Understand, but quality of the code was not the reason I asked. My question
related to the permission issues with dynamic SQL. From what I understand, R
AC
would require providing direct table access to the users.
Thomas
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:uCv1hqkYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello Thomas,
> For what it's worth:
> Most novice and even experienced sql programmers assume RAC
> builds a classic dynamic SELECT statement consisting of gobs
> of classic CASE statements as seen in the plethora of sql books
> illustrating an sql crosstab.This is NOT what RAC does.Given all
> the functionality/features available, this method would be next to impossi
ble
> and even if it could be done it probably would bring the server to a hault
.
> The basic dynamic query built is a SELECT/GROUP BY query based
> on the transform (aggregate(s)), row(s) and the column to be pivoted.
> This is the building block from which xtabs,various ranking functionality
> and all other basic questions/options start with.The default method that
> RAC uses to build the actual result utilitized bcp.So anything you see
> in a text book or on a site that shows how to develope a dynamic
> crosstab in t-sql bears absolutely no resemblence to RAC:)
> The real goal of RAC is to unburdon the user/developer of the messy
> details of creating a myraid of dynamic results for various data
> manipulation problems without stressing out the query optimizer.
> Another example of *What* not *How*:)
> Best from,
> www.rac4sql.net
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23g5cFSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL
> (or a report writer that uses dynamic SQL) and all the evil that goes with
> it....
*Evil*...one would think that a professional might pick a more
appropriate term. But since its drummed into the head of every
user so often, it must be so:) Beware of piped pipers with long noses:)