Is it possible to have more than one table footer rows ?

Well, we can add another table footer row by right-clicking on the footer row of the table and select, Inser Row Above, or, Insert Row Below.

But, I want to set different values to same properties of rows. Such as;

Row3.RepeatOnNewPage = False

Row4.RepeatOnNewPage = True

What do you say ?

-SMang the Frustrated Developer

SMang,

You can't hide your table rows independently with the RepeatOnNewPage, but you can set the rows "hidden" property to true or false. The catch here is that you will need to tie it to a toggle item.

Ham

sql

Is it possible to create thread & start from CLR Stored Proc

My simple CLR Stored procedure is as below:

[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyParallelStoredProc(string name1, string name2)
{
Thread t = null;
Worker wth = null;
int parallel = 2;
Object[] obj = new object [parallel];
SqlPipe p;
p = SqlContext.Pipe;

for (int i = 0; i < parallel; i++)
{
if (i == 0)
wth = new Worker(name1);
else
wth = new Worker(name2);
t = new Thread(new System.Threading.ThreadStart(wth.WorkerProc));
t.Name = "Thread -" + i.ToString() + ":";
t.Start();
p.Send(t.Name + ":Started");
obj[ i] = t;
}
for (int i = 0; i < parallel; i++)
{
t = (System.Threading.Thread)obj[ i];
t.Join();
p.Send(t.Name + ":Finished");
}
return 0;
}

The worker class implementing Thread Proc:

public class Worker
{
private string Name;

public Worker(string name)
{
SqlPipe p;
p = SqlContext.Pipe;
Name = name;
p.Send("In Constructor:" + Name);
}

public void WorkerProc()
{
SqlPipe p;
p = SqlContext.Pipe;
for (int i = 0; i < 10; i++)
p.Send(i.ToString()+":"+Name);
}
}

The assembly is registered with UNSAFE permission set.

CREATE ASSEMBLY
ThreadTest
FROM
'C:\\ThreadTest\bin\Debug\ThreadTest.dll'
WITH
permission_set = unsafe;
GO

CREATE PROC ParallelStoredProc
@.Name1 NVARCHAR(1024),
@.Name2 NVARCHAR(1024)
AS
EXTERNAL NAME ThreadTest.[MyTest.ThreadTest].MyParallelStoredProc

When I invoke the the stored procedure from T-SQL script as below,

EXEC ParallelStoredProc @.Name1, @.Name2

the thread class constructor gets called; but the 'WorkerProc' does not execute ?

Whether an UNSAFE assembly is allowed to spawn threads

inside SQL Server ?

Your code works correctly to start and run threads under unsafe. The reason you think it doesn't work is because the SqlContext connection is not available on new threads, so you can't use it to Pipe.Send information back.

If you try/catch for exceptions in your WorkerProc, you should see an error like the following:

"The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code."

Steven

|||

Thanks steve. Your input was very useful.

If I use SqlConnection in WorkerProc, the thread gets aborted

and goes into "Stopped" state.

It means the main CLR Stored proc can only execute the T-SQL commands ?

WorkerProc's are restricted to computations.

Is it possible not to keep together textfields?

Hi,

This all appears when exporting a report to PDF:

I've a report with a label and textfield in the upper third, below the textfield there are some other controls. My problem is, that all works fine as long as the text for the textfield is short enough to fit the page. If the text gets to long I get a nearly blank page with only the label on it and the textfield on the next page. I found no property to change this behaviour. I want my textfield to split up to multiple pages, just as Access does it!

I want to see the first part of the content on the first page and then page footer, page header and then the second part on the next page.

I tried a list-control and a table-control with a rect in it -> same problem.

I found a very ....uncommon... way to do it, I made a subreport with just a textfield on it which has the value "Parameters!Text" (add the report parameter "Text") and put this subreport into my report and passing the long text as a parameter to the subreport ;)

But I think that's not exactly brilliant..

Any other hints?

Benni

|||

Okay,
very similar problem, if I have a report header(rectangle with some labels in it) with a list below it. As long as the record in the list contains few data all looks fine. If the content reaches a specific length the whole record is keept together on the next page (there is no report header, so a bit more space). If the content gets even larger its okay again (some parts on the first page, the rest on the next page)!

I've NOT set KeepTogether vor any control. I also tried the subreport trick, but it doesn't work ;(

Why does Microsoft add so many rarely needed extra features (e.g. multiple datasets side by side) and loose sight of the basic features? If I don't get this running I'll have to choose a different product, because no user wants to have this strange reports.. Even Access 2000 (I've no older version available) has better keeptogether options ;(

|||

Benni,

Did really the textfield "nokeeptogether" work using a subreport?

It doesn't work for me, perhaps your text was more than one page long? Did you do any other settings to make it work?

/Dan

|||The official MS workaround is to make a few Textfields under each other and split the text right into them. We've made a service request at microsoft and they told us, that they could rise a DCR (Design Change Request). We'll see if they fix it..

Is it possible not to keep together textfields?

Hi,

This all appears when exporting a report to PDF:

I've a report with a label and textfield in the upper third, below the textfield there are some other controls. My problem is, that all works fine as long as the text for the textfield is short enough to fit the page. If the text gets to long I get a nearly blank page with only the label on it and the textfield on the next page. I found no property to change this behaviour. I want my textfield to split up to multiple pages, just as Access does it!

I want to see the first part of the content on the first page and then page footer, page header and then the second part on the next page.

I tried a list-control and a table-control with a rect in it -> same problem.

I found a very ....uncommon... way to do it, I made a subreport with just a textfield on it which has the value "Parameters!Text" (add the report parameter "Text") and put this subreport into my report and passing the long text as a parameter to the subreport ;)

But I think that's not exactly brilliant..

Any other hints?

Benni

|||

Okay,
very similar problem, if I have a report header(rectangle with some labels in it) with a list below it. As long as the record in the list contains few data all looks fine. If the content reaches a specific length the whole record is keept together on the next page (there is no report header, so a bit more space). If the content gets even larger its okay again (some parts on the first page, the rest on the next page)!

I've NOT set KeepTogether vor any control. I also tried the subreport trick, but it doesn't work ;(

Why does Microsoft add so many rarely needed extra features (e.g. multiple datasets side by side) and loose sight of the basic features? If I don't get this running I'll have to choose a different product, because no user wants to have this strange reports.. Even Access 2000 (I've no older version available) has better keeptogether options ;(

|||

Benni,

Did really the textfield "nokeeptogether" work using a subreport?

It doesn't work for me, perhaps your text was more than one page long? Did you do any other settings to make it work?

/Dan

|||The official MS workaround is to make a few Textfields under each other and split the text right into them. We've made a service request at microsoft and they told us, that they could rise a DCR (Design Change Request). We'll see if they fix it..

Is it possible in single SQL statement

Does anyone know how should I write the sql for getting the following result?

Original Table like below.
----------
[WorkDay] [AgentCode]
06/12/01 3
06/12/02 2
06/12/02 3
06/12/03 2
06/12/03 3
----------

Curernt SQL:

When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.

Example,
SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2
----------
[WorkDay] [AgentCode]
06/12/02 2
06/12/03 2
----------

I would like to know the agent is in the specified date.
The expected result like below.
----------
[WorkDay] [AgentCode]
06/12/01 NULL
06/12/02 2
06/12/03 2
----------

Please help its urgentwhy would the result have a row for 06/12/01 when you are telling it to get the rows for agentcode=2... it will discard all the rows having agentcode <> 2.
I think you need to use a self outer join to get the expected result.|||SELECT DISTINCT
Workday,
CASE WHEN AgentCode = 2 THEN
2
ELSE
NULL
END AS AgentCodeIfTwo
FROM OriginalTable|||or when your table is called justanotherday

SELECT DISTINCT
a.workday, b.agentcode
FROM
justanotherday a
LEFT JOIN
justanotherday b
ON
(a.workday = b.workday AND b.agentcode = 2)|||I suppose if you are trying to kill time and want to scan the table twice...|||:D

Well, that shouldn't be too hard... in five years are only 1825 date records... so...|||...assuming only 1 record per day, in which case why select DISTINCT? There is no way to tell how many records are in the table.|||no... i mean.. it's 'only' 1825 records against x records in 5 years... if x is not extremely large, no SQL interpreter should have problems with it in my opinion... or am i totally wrong here?|||The issue isn't that there would be a noticable difference in performance against small record sets. The issue is that the solution you proposed is sub-optimal, and might encourage someone to use the same method against larger databases. We try to propose (and frequently argue about) "best practices" on this forum. If you think that the algorithm you proposed is more efficient than Pootle's method, then take the opportunity to justify your opinion.|||ok... you are right about that... my solution is not really an optimal one :)

Is it possible

Is It Possible to use Inner Join -- to retrieve data from a different Database? Or is it limited to the same Database? The Example below throws me an error. Thank You.
--------------------------------------------------------------
Function GetNames(ByVal uid As Integer) As System.Data.DataSet Dim connectionString As String = (ConfigurationSettings.AppSettings("ConnectionString"))
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Products].[uid], [Products].[ManufacturerId], [Products].[Name], [Manufacturers].[uid], [Manufacturers].[AddressID], [Test.dbo].[uid], [Test.dbo].[Company] FROM [Products] INNER JOIN [Manufacturers] ON [Products].[uid] = [Manufacturers].[uid] INNER JOIN [Test.dbo] ON [Manufacturers].[uid] = [Test.dbo].[uid]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_uid As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_uid.ParameterName = "@.uid"
dbParam_uid.Value = uid
dbParam_uid.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_uid)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End FunctionYour syntax is incorrect. You are missing ] and [ when referencing the other database.
Dim queryString As String = "SELECT [Products].[uid], [Products].[ManufacturerId], [Products].[Name], [Manufacturers].[uid], [Manufacturers].[AddressID], [Test].[dbo].[uid], [Test].[dbo].[Company] FROM [Products] INNER JOIN [Manufacturers] ON [Products].[uid] = [Manufacturers].[uid] INNER JOIN [Test].[dbo] ON [Manufacturers].[uid] = [Test].[dbo].[uid]"
|||Thank You Douglas,

I did try it that way, and it threw me this error:

Invalid object name 'Test.dbo'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'Test.dbo'.
Source Error:

Line 74: dataAdapter.SelectCommand = dbCommandLine 75: Dim dataSet As System.Data.DataSet = New System.Data.DataSetLine 76: dataAdapter.Fill(dataSet)Line 77: Line 78: Return dataSet

|||asp.netcat, you don't have a fully qualified table name there. You should be using this format when referring to your table:

database.owner.tablename

and this format when referring to a column in your table

database.owner.tablename.columnname

|||

Thank you Terri & Douglas,

Yes Indeed I overlooked & forgot all about adding the Table Name. I do have it working now. Big Thanks!! As always... You Guys are the Best!!!

Is it better work with date/time in same field or separated?

Hi,
Is it better work with date/time in same field or separated as below:
varX = '12/12/2003' + '12:45' »» in same field in the table
OR
varX = '12/12/2003' + '12:45' »» in separated fields in the table
varY = '12:45'
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SPUse a DATETIME column. This will make the most out of indexing, date range
queries, date comparisons, built in functions like datepart(), dateadd() and
datediff(), and easier legibility.
Since SQL Server doesn't have separate DATE and TIME datatypes, moving them
to separate columns would either mean (a) having useless date information in
the "time" column and useless time information in the "date" column, or (b)
converting the columns to a different datatype. For more information, see
http://www.aspfaq.com/2206
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:#60t$RZxDHA.1704@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it better work with date/time in same field or separated as below:
> varX = '12/12/2003' + '12:45' »» in same field in the table
> OR
> varX = '12/12/2003' + '12:45' »» in separated fields in the table
> varY = '12:45'
> Thanks,
> VILMAR
> BRAZIL
> PRAIA GRANDE/SP
>

Is Full Text Struggling...

H there,
We have a query that is taking too long to run, which uses Full Text
(MSSQL2000).
The query below, when using this clause takes between 11 seconds and 10
minutes! QA thnks that the full text search will cost 70% of the query cost.
CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR
("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food Safety")
OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age card")
OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT ("Ahold")
AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
("European stocks may decline") AND NOT ("European stocks may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
The query below, when using this clause, takes only between 0.02 secs and 2
secs. QA thnks that the full text search will cost 50% of the query cost.
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR ("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
Why is the second query instant and the first taking ages?
Sometimes, the longer queries such as the first one here can take *much*
longer to run (like, 5 minutes). We're hoping that we can get even the
queries with more expressions to run inside a few seconds. In fact, they did
when we had < 400,000 rows.
Is it possible that our full text indexing is just set up wrong, or that our
hardware isn't sufficient?
Some additional facts that may help...
The table (and FT index) only have 800,000 rows
We run 116 of these queries in a row, directly after each other.
We've cleared out all stop words, since we want to index on anything.
The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
disks.
Whilst the queries are running, I'm not seeing massive memory use.
Any help much appreciated. Please let me know if you need more info.
Tobes
I would suspect its all the search arguments and Boolean logic you have
which is causing the problems especially the AND NOTs.
Note that this
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
is equivalent to the simpler
CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
"designated driver initiative" OR "drink driving" OR "legislation" OR
"price" OR "pricing" OR "underage driving")')
Revisting the AND NOTs, basically the way this is processed is all matches
are returned for the first part
("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli") OR
("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
and then you trim rows which contain
AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
preview") AND NOT ("European stocks may decline") AND NOT ("European stocks
may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
This trimming is very expensive.
One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
might help slightly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> H there,
> We have a query that is taking too long to run, which uses Full Text
> (MSSQL2000).
> The query below, when using this clause takes between 11 seconds and 10
> minutes! QA thnks that the full text search will cost 70% of the query
cost.
> CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD")
OR
> ("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food
Safety")
> OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age
card")
> OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT
("Ahold")
> AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
> ("European stocks may decline") AND NOT ("European stocks may rise") AND
NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> The query below, when using this clause, takes only between 0.02 secs and
2
> secs. QA thnks that the full text search will cost 50% of the query cost.
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> Why is the second query instant and the first taking ages?
> Sometimes, the longer queries such as the first one here can take *much*
> longer to run (like, 5 minutes). We're hoping that we can get even the
> queries with more expressions to run inside a few seconds. In fact, they
did
> when we had < 400,000 rows.
> Is it possible that our full text indexing is just set up wrong, or that
our
> hardware isn't sufficient?
> Some additional facts that may help...
> The table (and FT index) only have 800,000 rows
> We run 116 of these queries in a row, directly after each other.
> We've cleared out all stop words, since we want to index on anything.
> The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
> disks.
> Whilst the queries are running, I'm not seeing massive memory use.
> Any help much appreciated. Please let me know if you need more info.
> Tobes
>
>
|||Hi Hilary,
Thank you for the reply. Yes, the queries run significantly quicker without
the NOTs. I had tried the other boolen expressions in a more compact form,
but that made little difference unfortunately. I guess the full text search
engine may do it's own optimising to clean up our verbose queries!
In two weeks we'll be throwing more hardware at the program (doubling ram,
increasing disk capacity, and introducing dual Xeon processors), so
hopefully that will make the situation better.
Our main problem is that we want scalability. At the moment we have 116
"projects", each with their own queries that run one by one. These are
taking hours to run (some queries quick, some looooong!). Do you think we
may benefit from running more than one query in parrallel? For example, have
two processes executing 58 queries each?
Thanks again for your help.
Tobes
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ex7e8bQcFHA.720@.TK2MSFTNGP15.phx.gbl...
>I would suspect its all the search arguments and Boolean logic you have
> which is causing the problems especially the AND NOTs.
> Note that this
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
> ("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> is equivalent to the simpler
> CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
> "designated driver initiative" OR "drink driving" OR "legislation" OR
> "price" OR "pricing" OR "underage driving")')
> Revisting the AND NOTs, basically the way this is processed is all matches
> are returned for the first part
> ("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli")
> OR
> ("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
> Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
> ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
> and then you trim rows which contain
> AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
> preview") AND NOT ("European stocks may decline") AND NOT ("European
> stocks
> may rise") AND NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> This trimming is very expensive.
> One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
> might help slightly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
> news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> cost.
> OR
> Safety")
> card")
> ("Ahold")
> NOT
> 2
> ("alcopops"
> did
> our
>

is case statement the only way

Hi

I need to generate a SQL report like below,its basically calculating the count of students

For

District Level

then for

Region Level

then for

Each School under a Region

Like the Display Below

District summary

Total

Male

Femal

Indian

White

Asian

--

--

--

Type1

22

33

22

11

11

11

23

11

13

Type2

2

…6

…7

;;;;

13

14

Region 1

Region 2...................Region 8

Each School

School1....School 15

Do I have to have

case for each Type for each Race

and then

for Each Levels

of

District

Region

And 500 Schools?

Please Help

Thanks

Could you post your DDL statements and some sample data? This will help us understand your problem and hopefully provide a solution....
|||

From what I understand you may be looking for the PIVOT operator which is available in SQL 2005.

http://technet.microsoft.com/en-us/library/ms177410.aspx

|||

SELECT distinct SCHOOL_REGION,SCHOOL_NUMBER,ETHNICITY,SCH.S_SCHL_NAME,

INTV.Intervention_ ID,

case

INTV.Intervention_ ID when '1' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

case

INTV.Intervention_ ID when '2' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

........

-

-

case

INTV.Intervention_ ID when....... '14' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

FROM STUDENT_DEMOGRAPHIC STDM

left join school_location SCH on STDM.school_number = SCH.S_SCHOOL_NUM

left join Meeting MTNG on STDM.student_id = MTNG.student_id

left join Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID

left join Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID

where INTV.Intervention_ID not in ('')

group by SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

ORDER BY SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

P.S ...here the inner case statement is for the horizontal column names in the report

and I need the totals for the Type 1 ..type2 ...type 14 which is my vertical column in my report ( the INTV.Intervention_ID is this field) which i use in outer case statement above