Inserting a blank line betwen groupings in my matrix report

I have a matrix report with 3 column groups. My main group is called
"Location" and then I dump out a bunch of data related to that location.
I want to insert a blank line before each new "Location" in my report but I
can't figure out how to do this with my matrix report.
Any ideas?Try putting this into expression for the location:
=(Fields!Location.Value+Environment.newline())
Good luck!
Peace,
Dan
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:6B279444-7502-43B3-B238-9C282A3C8858@.microsoft.com...
>I have a matrix report with 3 column groups. My main group is called
> "Location" and then I dump out a bunch of data related to that location.
> I want to insert a blank line before each new "Location" in my report but
> I
> can't figure out how to do this with my matrix report.
> Any ideas?

Insert, update does not work , until SQL server restart.

Hi,
I am not sure if this is the right place to post my problem. If not so, pls
do advise me of the right group.
Here is my problem:
I have this problem with SQL server. All of a sudden the insert, updates does
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I restarted the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
Sam
You may want to check for blocking, locking issue when you
can't do the inserts/updates. You can use query analyzer and
query sysprocesses, execute sp_who2, sp_lock to see what's
going on when the inserts, updates aren't successful.
-Sue
On Thu, 4 Aug 2005 10:15:11 -0700, sam
<sam@.discussions.microsoft.com> wrote:

>Hi,
>I am not sure if this is the right place to post my problem. If not so, pls
>do advise me of the right group.
>Here is my problem:
>I have this problem with SQL server. All of a sudden the insert, updates does
> not work on SQL server based application written in ASP. The select commands
> are returning fine, its only the inserts and updates. They dont appera to
>make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I restarted the SQL server and things get to be working again. This happened
> a couple of times. It can't be the memory issue, coz there is ample of space
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database.
>Any clues to the problem would be appreciated.
>Thanks,
>Sam
|||Thanks for the reply Sue,
I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
didnt find any in the BlkBy field of the results nor any thing from sp_lock.
all the processes are Granted. And i checked it at the time the problem
arised. It looks like SQL server is sorting itself after a while. The Lock
Type for al lthe requests is DB. Does that mean anything and the Status shows
as GRANT. Mode is 'S'.
Is there anything else that I should look for?
"Sue Hoegemeier" wrote:

> You may want to check for blocking, locking issue when you
> can't do the inserts/updates. You can use query analyzer and
> query sysprocesses, execute sp_who2, sp_lock to see what's
> going on when the inserts, updates aren't successful.
> -Sue
> On Thu, 4 Aug 2005 10:15:11 -0700, sam
> <sam@.discussions.microsoft.com> wrote:
>
>
|||Then next you may want to try running profiler to monitor
what's going across when you do an insert, update and
monitor the server itself with Perf Mon.
When you trace the database activity, check for statements
starting with no corresponding statement completed. It could
be a timeout issue or could be related to resource
constraints on the server itself.
-Sue
On Tue, 9 Aug 2005 15:19:03 -0700, sam
<sam@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply Sue,
>I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
>didnt find any in the BlkBy field of the results nor any thing from sp_lock.
>all the processes are Granted. And i checked it at the time the problem
>arised. It looks like SQL server is sorting itself after a while. The Lock
>Type for al lthe requests is DB. Does that mean anything and the Status shows
>as GRANT. Mode is 'S'.
>Is there anything else that I should look for?
>"Sue Hoegemeier" wrote:

Insert, update does not work , until SQL server restart.

Hi,
I am not sure if this is the right place to post my problem. If not so, pls
do advise me of the right group.
Here is my problem:
I have this problem with SQL server. All of a sudden the insert, updates doe
s
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I restarted the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
SamYou may want to check for blocking, locking issue when you
can't do the inserts/updates. You can use query analyzer and
query sysprocesses, execute sp_who2, sp_lock to see what's
going on when the inserts, updates aren't successful.
-Sue
On Thu, 4 Aug 2005 10:15:11 -0700, sam
<sam@.discussions.microsoft.com> wrote:

>Hi,
>I am not sure if this is the right place to post my problem. If not so, pls
>do advise me of the right group.
>Here is my problem:
>I have this problem with SQL server. All of a sudden the insert, updates do
es
> not work on SQL server based application written in ASP. The select comman
ds
> are returning fine, its only the inserts and updates. They dont appera to
>make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I restarted the SQL server and things get to be working again. This happen
ed
> a couple of times. It can't be the memory issue, coz there is ample of spa
ce
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database
.
>Any clues to the problem would be appreciated.
>Thanks,
>Sam|||Thanks for the reply Sue,
I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
didnt find any in the BlkBy field of the results nor any thing from sp_lock.
all the processes are Granted. And i checked it at the time the problem
arised. It looks like SQL server is sorting itself after a while. The Lock
Type for al lthe requests is DB. Does that mean anything and the Status show
s
as GRANT. Mode is 'S'.
Is there anything else that I should look for?
"Sue Hoegemeier" wrote:

> You may want to check for blocking, locking issue when you
> can't do the inserts/updates. You can use query analyzer and
> query sysprocesses, execute sp_who2, sp_lock to see what's
> going on when the inserts, updates aren't successful.
> -Sue
> On Thu, 4 Aug 2005 10:15:11 -0700, sam
> <sam@.discussions.microsoft.com> wrote:
>
>|||Then next you may want to try running profiler to monitor
what's going across when you do an insert, update and
monitor the server itself with Perf Mon.
When you trace the database activity, check for statements
starting with no corresponding statement completed. It could
be a timeout issue or could be related to resource
constraints on the server itself.
-Sue
On Tue, 9 Aug 2005 15:19:03 -0700, sam
<sam@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply Sue,
>I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
>didnt find any in the BlkBy field of the results nor any thing from sp_lock
.
>all the processes are Granted. And i checked it at the time the problem
>arised. It looks like SQL server is sorting itself after a while. The Lock
>Type for al lthe requests is DB. Does that mean anything and the Status sho
ws
>as GRANT. Mode is 'S'.
>Is there anything else that I should look for?
>"Sue Hoegemeier" wrote:
>

Insert record into temporary table from a select statement

Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" ... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chuai believe you can do it in a simpler way. can you post some sample data and the output you are looking for?|||here is the sample data. i want to group them to be able to use in 3 record in this case.

weekgroup mark updatedate
Week 1 100.000000 2006-01-03 09:37:15.000
Week 1 100.000000 2006-01-06 12:18:09.000
Week 1 71.600000 2006-01-06 12:59:46.000
Week 1 100.000000 2006-01-06 13:03:52.000

Week 2 95.000000 2006-01-09 11:49:17.000
Week 2 100.000000 2006-01-09 12:19:19.000

Week 3 100.000000 2006-01-16 15:03:24.000
Week 3 71.600000 2006-01-16 15:05:31.000
Week 3 100.000000 2006-01-17 15:59:43.000
Week 3 100.000000 2006-01-17 16:57:38.000

--------
here is the code i did. i set the @.dtstart = 1st day of the month
@.dtWeekEnd = 1 week after the 1st day of the month
@.dtEnd = end of the month

i try to group by the weekgroup but it fail. and it only allow to group by the last update date(Answer.dtAnswer).

SELECT CASE
WHEN day(@.dtStart) <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) THEN 'Week 1'
WHEN day(@.dtStart)+7 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 7 THEN 'Week 2'
WHEN day(@.dtStart)+14 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 14 THEN 'Week 3'
WHEN day(@.dtStart)+21 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 21 THEN 'Week 4'
else 'Week 5'
END as weekgroup, AVG(Answer.bScore) AS tScore, dtAnswer -- CONVERT(INT, AnswerKey.bRowId) AS bRowId-- day(Answer.dtAnswer) as days
FROM Answer INNER JOIN
AnswerKey ON Answer.lAnswerId = AnswerKey.lAnswerId INNER JOIN
QuestionDef ON AnswerKey.iQuestionDefId = QuestionDef.iQuestionDefId INNER JOIN
QuestionAnswerDef ON QuestionDef.iQuestionDefId = QuestionAnswerDef.iQuestionDefId AND AnswerKey.bKeyId = QuestionAnswerDef.bKeyId
WHERE (Answer.iMinutes BETWEEN 570 AND 1020) and (Answer.dtAnswer >= @.dtStart) AND (Answer.dtAnswer < @.dtEnd) and (Answer.iTemplateId = 1)
GROUP BY Answer.iTemplateId, AnswerKey.bRowId, QuestionDef.sQuestion, Answer.fDiscard,
QuestionAnswerDef.sAnswer, Answer.fIncomplete, AnswerKey.bScore, AnswerKey.bRowId, dtAnswer--, WeekGroup--, NoOfWeek
having (Answer.fDiscard = 0) AND (Answer.fIncomplete = 0)
order by dtAnswer--);|||what i understand, from the long sql, is you have a table with a numeric col and a date col and you want to have a AVG of the numeric col on at an interval of every 7 days, staring from the first day of the month...

an example to do the above for 1st half and 2nd half of the month could be

select avg(<Numeric Col>),
case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end
from table2
group by case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end

here the first date of the month is '20060101'|||thank for your advice, i found a solution from your sample.

i dun know using the case also can put at group by so problem solve easily.

thanks a lot:D
regards
terence chua