ssrs report using a varchar(max) column

Question

ssrs report using a varchar(max) column

wendy elizabeth

SSCoach

Points: 16915
More actions
December 11, 2016 at 8:44 pm

#315803

In an existing ssrs 2010 report , I will like to create multiple groupings within the report so the report will print on different pages. The multiple groupings would come from 1 (one) column that is contained within a sql server database where the field is defined as varchar(max).
I would like the groupings to be based upon specific literals. However I do not know how to create multiple groupings based on different parts of the same sql server 2010 column containing different literals.
In the example I am showing you, I would like to create a page break where the literal . is located. The literal could be anything.
I am just using the literal so you can show me and/or point me to links that will tell me how to accomplish my goal of page breaks? Would I somehow be able to use some page break property like disable or one of the other properties?
12/9/2016
 
 
 
 
To the Parent/Guardian of Joe Smith
&n
bsp; &nbs
p;
447782
 4 VERNON AVE CLER, NE
68111 
 
 
 
Dear Parent/Guardian and Joe Smith: 
 
 
This letter is to inform you that Jarious has the equivalent of 5 or more
unexcused absences this school year. We believe student attendance in school is a key
component to school success, so it is very important for all children to develop
habits of good attendance. Poor attendance contributes to failing grades,
decreased learning opportunities, lower academic achievement and may limit your
child's opportunities to be involved with school activities. 
 
 
(Option
1)
Please call me at
PHONE NUMBER to discuss these absences. During this phone conference we
will address concerns and issues that may be contributing to the absences, and
develop a collaborative plan to try to improve attendance. 
 
(Option
2)
I have scheduled an attendance review on DATE at
TIME. We will meet at PLACE located at
ADDRESS. Please check in at the main office upon your arrival.
During this meeting we will address concerns and issues that may be contributing
to the absences, and develop a collaborative plan to try to improve attendance.
The student and parent should attend this review. If you cannot attend
this meeting please contact me at PHONE #. 
 
 
We are notifying you so that together we can address all issues that may be
contributing to these absences. Communication between the home and school is
very important and we request that parents call the school each and every day
that a student is going to be absent. Let us work as partners to make your child
successful in school.
 
Sincerely, 
 
 
Tracy smithk 
 School Support Liaison/ Attendance Designee
TECH School
12/9/2016
 
 
 
 
To the Parent/Guardian of Joe Smith
&n
bsp; &nbs
p;
447782
 4841 CLEAR ST #205VET, NJ 68104 
 
 
 
Dear Parent/Guardian and Joe Smith: 
 
 
This letter is to inform you that Jarious has the equivalent of 5 or more
unexcused absences this school year. We believe student attendance in school is a key
component to school success, so it is very important for all children to develop
habits of good attendance. Poor attendance contributes to failing grades,
decreased learning opportunities, lower academic achievement and may limit your
child's opportunities to be involved with school activities. 
 
 
(Option
1)
Please call me at
PHONE NUMBER to discuss these absences. During this phone conference we
will address concerns and issues that may be contributing to the absences, and
develop a collaborative plan to try to improve attendance. 
 
(Option
2)
I have scheduled an attendance review on DATE at
TIME. We will meet at PLACE located at
ADDRESS. Please check in at the main office upon your arrival.
During this meeting we will address concerns and issues that may be contributing
to the absences, and develop a collaborative plan to try to improve attendance.
The student and parent should attend this review. If you cannot attend
this meeting please contact me at PHONE #. 
 
 
We are notifying you so that together we can address all issues that may be
contributing to these absences. Communication between the home and school is
very important and we request that parents call the school each and every day
that a student is going to be absent. Let us work as partners to make your child
successful in school.
 
Sincerely, 
 
 
Tracy smithk 
 School Support Liaison/ Attendance Designee
TECH School

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

Is the above HTML being returned in one field (as in you have one row and column returned). If so, then inserting a new page will be impossible.

If each letter is a separate instance, then this is much simpler. Do you already have your grouping set up on your report? is so, click the Group in your Row Groups pane, and press F4. Expand the Group section in the properties pane, and then expand Page Break. Finally Change The BreakLocation property to Between.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

You are saying that I can not create page breaks whenever the literal called '' is located? How about if I use tablixes and/or rectangles and embed item within each other?

Thom A SSC Guru Points: 99143 More actions · Answer 3

SSRS won't see as a page break no.

How is your HTML being returned to you? Can you have it so that each row is one html page?

For example (using the second dataset):

USE DevTestDB;

GO

CREATE TABLE #Sample (LetterID INT IDENTITY(1,1),

ChildName VARCHAR(50),

LetterDate DATE,

Addr1 VARCHAR(50),

Addr2 VARCHAR(50),

Addr3 VARCHAR(50),

Postcode VARCHAR(10));

INSERT INTO #Sample (ChildName, LetterDate, Addr1, Addr2, Addr3, Postcode)

VALUES ('Joe Smith',GETDATE(), '123 Road', 'Towny Town','Big County','AA12 1AB'),

('Jane Doe', GETDATE(), '7 Street Lane', 'Little Big Village', 'Back Water Coast', 'SS12 1AS'),

('Steve Bloggs', GETDATE(), '55 Cresent Drive', 'Huge City', 'Tiny little County', 'L1 1GG');

SELECT *

FROM #Sample S;

SELECT '' + FORMAT(S.LetterDate, 'dd-MMM-yyyy') + '

To the Parent/Guardian of ' + S.ChildName + '

 

' + S.Addr1 + ' 

' + S.Addr2 + ' 

' + S.Addr3 + ' 

' + S.Postcode + '

 

I regret to inform you that ,' + S.ChildName + ' Has been very naughty!

 

Kind regards, 

The Head Master

(Jane Oscar)' AS LetterHTML

FROM #Sample S;

DROP TABLE #Sample;

SSRS can easily then create a new page, as each letter has it's own row.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk