Combine Rows in Search Result

In Sql Server 2005 Express I have this table:

CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)

Each paragraph of text (SectionText) is in its own row(SectionNo) Each primary document has a DocumentTypeID of 1 withthree subdocument types (2=Index, 3=Background, 4=Report).

I run this query and return a collection of single rows from various documents grouped together by DocumentNo:

SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo

For each row that contains the search term, I would like toreturn the full document (all rows as parapraphs within one row ofreturned data). In other words, I want to reconstitute the fulldocument as it existed prior to being inserted into the database withparagraph separation.

For exampe, if the search term is in row 3of DocumentNo=5, DocumentTypeID=2, I want to return all the rows ofthat document in one block of text that retains paragraph format(preferablly with a line break and carriage return betweenparagraphs). How can this be done?

You can do this trick which will lead you to solve the problem.

Okay, let say you need to group each page's paragraph in one record insted of many records (as in your current case).

Step#1:

So, Create another table with following columns :
1) BookID: Int or smallint
2) PageID: Int or smallint
3) PageText: Text or NText

Step#2:

1) Do acursor that will loop throug all of theparagraphs related to aspecific page.
2) DoINSERT thefirst record into thePageText field of thenew created table, while you doUPDATEfor therest of recordsafter concatenatingthem with value already exists in thePageText field.

Step#3:

Do this for each page in each book.

Result:

At the end you will have one table from which you can query and seach about any word/paragraph in any page in any book!!

Good luck.

|||

Thanks for the suggestion. I will give it a try.

Column Grand Totals

Is there a slick way to add grand totals to my report? so far, in my report table I have the following sections

TableHeader1

TableHeader2

Group1

Footer1

I added one of the same fields from my Group1 to Footer1....but it's not summing correctly. I have this in my footer right below one of my Group Fields:

=SUM(Round(((Fields!FeeGoal_AZ.Value) / Fields!FeeSchedule.Value) * 100))

From the expression it looks like you are summing up percentage values? Is this really what you want?

Maybe you are actually looking for the average percentage value?
E.g.
=Avg(Fields!FeeGoal_AZ.Value / Fields!FeeSchedule.Value * 100)

-- Robert

|||

sorry, just ignore that one. Try this...this is actually numbers:

=SUM(Fields!PostedAmount.Value)

when I put that in my footer, it isn't summing my Group field above it in my Group1 correctly all the way down that colum...

|||

Rob, can you help me!! need your input since you're the only one who seems to have any knowledge on this. I am very desperate here! This can't be that hard:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=235554&SiteID=1

|||totals are accurate...I just needed to add SUM() to my group row fields and then I saw the light...My grand total was acting fine, it was just my row items that weren't showing currect Sums. I assumed that since the SSRS 2005 report had a group, that it would sum up it's rows automatically but that's not the case, you still need to use SUM() around your fields in your group.