A question about group structure?

hi, all
I have one report like this:
Students Scores For Class 1
StudentID StudentName Age Score......
......
Average:......
Students Scores For Class 2
StudentID StudentName Age Score......
......
Average:......
...
For this report, I need to show StudentID StudentName..... on every
page as header, but Students Scores For Class only show once for each
class.
So, in this case, the structure would be:
GroupHeader --> Students Scores For Class 2
--> StudentID StudentName Age Score......
TableDetail ......
GroupFooter -- >Average:......
What would the StudentID, StudentName.... be in order to show it on
each page? Do I need to create one more group?
Thanks.Hi, Nick
When you go to 'Edit Group..." have you select 'Repeat group header on each
page'?
"Nick" <nick_1394@.yahoo.com.cn> wrote in message
news:1137454724.899667.184760@.g49g2000cwa.googlegroups.com...
> hi, all
> I have one report like this:
> Students Scores For Class 1
> StudentID StudentName Age Score......
> ......
> Average:......
> Students Scores For Class 2
> StudentID StudentName Age Score......
> ......
> Average:......
> ...
>
> For this report, I need to show StudentID StudentName..... on every
> page as header, but Students Scores For Class only show once for each
> class.
> So, in this case, the structure would be:
> GroupHeader --> Students Scores For Class 2
> --> StudentID StudentName Age Score......
> TableDetail ......
> GroupFooter -- >Average:......
> What would the StudentID, StudentName.... be in order to show it on
> each page? Do I need to create one more group?
> Thanks.
>|||hi, thanks for your reply.
I think if I select the repeat header, it will repeat "Students Scores
For Class 2" too on each page, right? I only want to repeat "StudentID,
StudentName... " this header on each page. The "Students Scores For
Class 2" is above this header, and I only want it to display once in
the whole report.|||Nick,
I'm not sure of the syntax (I'm a SQL guy, not VB) but it seems like
you need to set the visibilty property for your first group heading
line to be False (i.e. Hidden = False) only when then value in your
group field changes. I know this is pretty vague and incomplete but
maybe somebody better in VB can pick it up from here...

A few compilations/sec

Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
times. But the recompilations/sec could average around 0 or 1..
How can i find out what sprocs are triggering the compiles ?And why would
they ?
I tried to look at sysprocesses at the waitresource column for values that
have a TAB%[COMPILE] in them and found some sprocs but they look very si
mple
and straighforward. So whats causing a compile ? Could it be the code ?A compile is when you execute a statement / procedure and there is no plan
for it in the procedure cache. This usually occurs when you have adhoc sql
or poorly formatted batches. Take a look at sysproccache table and order by
USECOUNTS DESC. You will see all the ones at the top that are reused and
the ones near the bottom are not.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
> times. But the recompilations/sec could average around 0 or 1..
> How can i find out what sprocs are triggering the compiles ?And why would
> they ?
> I tried to look at sysprocesses at the waitresource column for values that
> have a TAB%[COMPILE] in them and found some sprocs but they look very
> simple and straighforward. So whats causing a compile ? Could it be the
> code ?
>|||There are so many sprocs with usecounts of 1.. There must be a better way to
narrow it down , is there not ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>A compile is when you execute a statement / procedure and there is no plan
>for it in the procedure cache. This usually occurs when you have adhoc sql
>or poorly formatted batches. Take a look at sysproccache table and order
>by USECOUNTS DESC. You will see all the ones at the top that are reused
>and the ones near the bottom are not.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||Well then you have a lot that don't get reused. You can use trace along with
the SP events that track cache hits and such.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Use the plan_handle to get the T-SQL batches that are use donly once. For
SQL 2005 the query looks like:
select *, (select [text] from sys.dm_exec_sql_text(p.plan_handle))
from sys.dm_exec_cached_plans p
where usecounts = 1
order by size_in_bytes desc
Also, go ahead and read the 'Execution Plan Caching and Reuse' topic in BOL
http://msdn2.microsoft.com/en-us/library/ms181055(en-US,SQL.90).aspx .
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>

A few compilations/sec

Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
times. But the recompilations/sec could average around 0 or 1..
How can i find out what sprocs are triggering the compiles ?And why would
they ?
I tried to look at sysprocesses at the waitresource column for values that
have a TAB%[COMPILE] in them and found some sprocs but they look very simple
and straighforward. So whats causing a compile ? Could it be the code ?
A compile is when you execute a statement / procedure and there is no plan
for it in the procedure cache. This usually occurs when you have adhoc sql
or poorly formatted batches. Take a look at sysproccache table and order by
USECOUNTS DESC. You will see all the ones at the top that are reused and
the ones near the bottom are not.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
> times. But the recompilations/sec could average around 0 or 1..
> How can i find out what sprocs are triggering the compiles ?And why would
> they ?
> I tried to look at sysprocesses at the waitresource column for values that
> have a TAB%[COMPILE] in them and found some sprocs but they look very
> simple and straighforward. So whats causing a compile ? Could it be the
> code ?
>
|||There are so many sprocs with usecounts of 1.. There must be a better way to
narrow it down , is there not ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>A compile is when you execute a statement / procedure and there is no plan
>for it in the procedure cache. This usually occurs when you have adhoc sql
>or poorly formatted batches. Take a look at sysproccache table and order
>by USECOUNTS DESC. You will see all the ones at the top that are reused
>and the ones near the bottom are not.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
|||Well then you have a lot that don't get reused. You can use trace along with
the SP events that track cache hits and such.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
|||Use the plan_handle to get the T-SQL batches that are use donly once. For
SQL 2005 the query looks like:
select *, (select [text] from sys.dm_exec_sql_text(p.plan_handle))
from sys.dm_exec_cached_plans p
where usecounts = 1
order by size_in_bytes desc
Also, go ahead and read the 'Execution Plan Caching and Reuse' topic in BOL
http://msdn2.microsoft.com/en-us/library/ms181055(en-US,SQL.90).aspx .
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>

A few compilations/sec

Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
times. But the recompilations/sec could average around 0 or 1..
How can i find out what sprocs are triggering the compiles ?And why would
they ?
I tried to look at sysprocesses at the waitresource column for values that
have a TAB%[COMPILE] in them and found some sprocs but they look very simple
and straighforward. So whats causing a compile ? Could it be the code ?A compile is when you execute a statement / procedure and there is no plan
for it in the procedure cache. This usually occurs when you have adhoc sql
or poorly formatted batches. Take a look at sysproccache table and order by
USECOUNTS DESC. You will see all the ones at the top that are reused and
the ones near the bottom are not.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
> times. But the recompilations/sec could average around 0 or 1..
> How can i find out what sprocs are triggering the compiles ?And why would
> they ?
> I tried to look at sysprocesses at the waitresource column for values that
> have a TAB%[COMPILE] in them and found some sprocs but they look very
> simple and straighforward. So whats causing a compile ? Could it be the
> code ?
>|||There are so many sprocs with usecounts of 1.. There must be a better way to
narrow it down , is there not ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>A compile is when you execute a statement / procedure and there is no plan
>for it in the procedure cache. This usually occurs when you have adhoc sql
>or poorly formatted batches. Take a look at sysproccache table and order
>by USECOUNTS DESC. You will see all the ones at the top that are reused
>and the ones near the bottom are not.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
>> times. But the recompilations/sec could average around 0 or 1..
>> How can i find out what sprocs are triggering the compiles ?And why would
>> they ?
>> I tried to look at sysprocesses at the waitresource column for values
>> that have a TAB%[COMPILE] in them and found some sprocs but they look
>> very simple and straighforward. So whats causing a compile ? Could it be
>> the code ?
>|||Well then you have a lot that don't get reused. You can use trace along with
the SP events that track cache hits and such.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>>A compile is when you execute a statement / procedure and there is no plan
>>for it in the procedure cache. This usually occurs when you have adhoc sql
>>or poorly formatted batches. Take a look at sysproccache table and order
>>by USECOUNTS DESC. You will see all the ones at the top that are reused
>>and the ones near the bottom are not.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
>> times. But the recompilations/sec could average around 0 or 1..
>> How can i find out what sprocs are triggering the compiles ?And why
>> would they ?
>> I tried to look at sysprocesses at the waitresource column for values
>> that have a TAB%[COMPILE] in them and found some sprocs but they look
>> very simple and straighforward. So whats causing a compile ? Could it be
>> the code ?
>>
>|||Use the plan_handle to get the T-SQL batches that are use donly once. For
SQL 2005 the query looks like:
select *, (select [text] from sys.dm_exec_sql_text(p.plan_handle))
from sys.dm_exec_cached_plans p
where usecounts = 1
order by size_in_bytes desc
Also, go ahead and read the 'Execution Plan Caching and Reuse' topic in BOL
http://msdn2.microsoft.com/en-us/library/ms181055(en-US,SQL.90).aspx .
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>>A compile is when you execute a statement / procedure and there is no plan
>>for it in the procedure cache. This usually occurs when you have adhoc sql
>>or poorly formatted batches. Take a look at sysproccache table and order
>>by USECOUNTS DESC. You will see all the ones at the top that are reused
>>and the ones near the bottom are not.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
>> times. But the recompilations/sec could average around 0 or 1..
>> How can i find out what sprocs are triggering the compiles ?And why
>> would they ?
>> I tried to look at sysprocesses at the waitresource column for values
>> that have a TAB%[COMPILE] in them and found some sprocs but they look
>> very simple and straighforward. So whats causing a compile ? Could it be
>> the code ?
>>
>