September 13, 2017 at 6:27 am
Hello,
I'm new to this forum and it seems awesome, so here's my first post/topic to test the waters. 😉
I’m working on a report that has details(single row) in a table with first and last name columns ordered by LastName.
I’d like the report to add/display 3 empty rows between groups of last names(by their first letter).
So groups of “A” LastNames would end with 3 blank rows. Groups of “B” LastNames would end with 3 blank rows and so on all the way through “Z”
Example Report:
Made up LastName column:
Abbe
Adams
Arlyn
New Row
New Row
New Row
Bird
Bryn
Burn
Bzork
New Row
New Row
New Row
C LastNames begin and so on. . .
Does anyone have a best practice or an expression they could share to do this?
I’ve come close with using a hidden expression =IIF(Left(Fields!LastName.Value,1)>(Previous(Left(Fields!LastName.Value.1))),False,True), but I almost need a “Next” or a "Between" instead of a “Previous” to get that to work correctly, but I don’t think there is a Next function available.
Any advice would be greatly appreciated. Thanks!
September 13, 2017 at 8:17 am
Couple of ways I can see.
One, is at the dataset level, and insert your extra rows in there. Provided that the query is only for your report set, it's not a bad way to go, but it does look a little odd (at least to me). One way of achieve this is as follows (using LAG and a couple CTEs):CREATE TABLE #Name (LastName varchar(20));
INSERT INTO #Name
VALUES
('Abbe'),
('Adams'),
('Arlyn'),
('Bird'),
('Bryn'),
('Burn'),
('Bzork'),
('Charlie');
GO
WITH Spaces AS
(SELECT 1 AS BlankRow UNION ALL
SELECT 2 AS BlankRow UNION ALL
SELECT 3 AS BlankRow UNION ALL
SELECT 4 AS BlankRow),
Lags AS (
SELECT LastName,
LAG(LEFT(LastName,1)) OVER (ORDER BY LastName) AS LastPersonInitial
FROM #Name)
SELECT CASE WHEN Spaces.BlankRow = 4 OR Spaces.BlankRow IS NULL THEN LastName ELSE NULL END AS LastName
FROM Lags
LEFT JOIN Spaces ON LEFT(LastName, 1) != LastPersonInitial
ORDER BY Lags.LastName, BlankRow;
GO
DROP TABLE #Name;
Another method would be to use grouping on your tablix on the report. I'm explaining this from memory right now, but if you know SSRS well enough, it should set you on the right path.
Firstly, add a group to your tablix on the LEFT most character of your Lastname Field. This will likely add a new column to your tablix on the left hand side. Leave it there for the moment. Right click the row in your tablix where your data is, and there will be an option to insert a new row inside the group. Do this 3 times (so you have 3 blank rows under your data row in your tablix). Then, select the new column that was made when you created the group and then right click it. Select Delete Column; this should present you with a dialogue window asking if you want to delete the column or the column and the group. Select just the column and click ok.
Now, when you Previous the report, it should put 3 blank rows between each group (the left most character). It will, also add 3 lines after the last group (if you have one, the group z), so you'll need to add some logic to hide those rows.
If you try the latter option, and you get stuck, let me know. I should be at a PC tomorrow with SSRS, so I'll be able to create a sample rdl and provide a copy (as well as post some more detailed steps). However, see how far you get first.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 13, 2017 at 6:48 pm
Thank you!! I was able to get it to work by using grouping on my tablix as you suggested. Took me a couple tries, but it is now working exactly as I wanted. 🙂
Much appreciated!
September 22, 2017 at 9:07 am
Thanks again Thom A!
So currently on my SSRS report I have the grouping(by the first letter of the persons last name) setup and my 3 lines inserting at the end of each group(A, B, C, and so on) and that is working (thanks to Thom A above).
Another request has come up to insert 3 rows prior to the A grouping only(if there are no last name's that begin with A).
Is there an expression/grouping option I can add to the SSRS report to get 3 rows before the A group(if there are no last names that begin with A)?
If there is no way to do that....in a worst case scenario, can I display 3 initial blank rows(below my header), prior to any groups/grouping and show only on the first page of the report?
Thanks!!
September 22, 2017 at 9:46 am
Instead of putting the blank rows after your data, put them before, then hide them if the group value is "A", rather that before, when you were hiding for Z. That'll place them there for every letter, apart from A, meaming that if B or a letter is the first group (alphabetically), they will be displayed.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 22, 2017 at 11:45 am
Hmm...that sounds almost like it would work, but won't I lose my blank rows after group Z then(they'll appear before Z it seems like)?
Maybe I could just have 3 blank rows on the first page underneath the header(no matter if a group A is present or not)? I just don't want them to repeat on every page.
September 22, 2017 at 11:56 am
Just one other clarification. I was not hiding for Z before. I wanted the 3 blank rows to appear after each group(A, B, C, etc), including Z at the very end of the report
Was working beautifully until, I received this new request to "add 3 blank rows" prior to A group if no A's.....and what I thought was an easy fix has turned into a bit of a pain for me, LOL!
September 22, 2017 at 12:15 pm
Ahh, so you want them at both ends? Ok, not a problem; Switch the the solution I gave last, as this solves the "problem" with A (as you don't want 3 rows at the top if there is an A). Then, select your last row and instead of adding a new row in the current group, insert a new row below OUTSIDE of the group. Then add 2 more rows. That'll put the last 3 at the bottom regardless.
I'm working from a little from memory on the dialogue menu, as I don't have SSDT at home.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 22, 2017 at 1:33 pm
Thanks again for all the advice Thom A.
I did land up getting this to work a round about way...I added 3 rows inside the group(at the top) and I added a visibility expression to those 3 rows =IIF(RowNumber("DSet_Name")>1,True,False) and that got me the 3 rows to show only on the first page. It does show if there are A groups, but luckily that was not a deal breaker for the users(at least not yet anyways).
Have a great weekend!
September 25, 2017 at 8:56 am
Sometimes, the easiest way to solve that kind of problem is to have the dataset return rows with grouping values but no data rows, and that makes the grouping and sorting tasks, including the inclusion of extra lines or hiding extra lines a piece of cake. It's often far easier than spending most of the day experimenting trying to find a way to solve that particular problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 11:27 am
Ok, so I'd like to try and insert these blank rows via the dataset/stored procedure I have setup to see if that is easier(and this also gives me some practice posting in the correct format on this site).
I've inserted my code below(hope that displays correctly).
Basically, this query runs and extracts patient names among other items and I have it ordered by ShowPatientName.
I'd like to insert 3 rows before last names that begin with A's(if there are any that begin with A) and 3 blank rows after the A's come to an end.
If there are no A's, there would be only 3 blank rows(and not 6) within the group. It would then move to the B's and only insert 3 blank rows if there are no B's or insert 3 blank rows at the end of the group.
Then the C's would get 3 blank rows(if no C names) or would insert 3 blank rows at the end of the C's and this would continue through the Z's.
Would you have any ideas on how I could work that into the code below?
Thanks!
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @StartDt DATE = '01/01/2016', @EndDt DATE = '09/29/2017'
IF Object_id('tempdb..#AllCases') IS NOT NULL
DROP TABLE #AllCases;
CREATE TABLE #AllCases (
CaseID BIGINT,
ClientGUID NUMERIC(16,0),
LocationGUID NUMERIC(16,0),
RoomDisplayName VARCHAR(255),
ScheduledStartDateTimeUTC DATETIME2
)
INSERT INTO #AllCases
SELECT DISTINCT
a.CaseID,
a.ClientGUID,
l.guid AS LocationGUID,
l.Name AS RoomDisplayName,
a.PlannedStartDateTimeUTC as ScheduledStartDateTimeUTC
FROM dbo.SXASRGCase a
INNER JOIN SXASRGCaseStatus cs ON cs.CaseStatusID = a.CaseStatusID
INNER JOIN CV3Location l ON a.LocationGUID = l.GUID
INNER JOIN [SXASRGCaseProcedure] c ON c.CaseID = a.CaseID
INNER JOIN CV3Client ON a.ClientGUID = CV3Client.GUID
INNER JOIN CV3LocnFacility f ON f.FacilityGUID = l.FacilityGUID
WHERE (
((a.ActualStartDate BETWEEN @StartDt AND @EndDt) AND a.ActualStartDate IS NOT NULL)
OR
((a.PlannedStartDate BETWEEN @StartDt AND @EndDt) AND a.ActualStartDate IS NULL)
)
;WITH FirstCases AS
(
SELECT
DISTINCT
AllCases.CaseID,
AllCases.LocationGUID AS SurgeryRoom,
AllCases.RoomDisplayName AS Room,
CONVERT(DATE, AllCases.ScheduledStartDateTimeUTC) AS StartDate,
CONVERT(VARCHAR(5), CONVERT(DATETIME2, AllCases.ScheduledStartDateTimeUTC), 114) AS StartTime,
CONVERT(DATE, ISNULL([SXASRGCase].ActualEndDateTimeUTC, DATEADD(minute,[SXASRGCase].TotalDurationMinutes, CONVERT(DATETIME2, AllCases.ScheduledStartDateTimeUTC)))) AS EndDate,
CONVERT(VARCHAR(5), CONVERT(DATETIME2, ISNULL([SXASRGCase].ActualEndDateTimeUTC, DATEADD(minute,[SXASRGCase].TotalDurationMinutes, CONVERT(DATETIME2, AllCases.ScheduledStartDateTimeUTC)))), 114) AS EndTime,
[SXASRGCase].TotalDurationMinutes AS Duration,
ROW_NUMBER() OVER (PARTITION BY AllCases.RoomDisplayName, CONVERT(DATE, AllCases.ScheduledStartDateTimeUTC) ORDER BY AllCases.ScheduledStartDateTimeUTC) AS Row,
CV3Client.DisplayName AS ShowPatientName, /*This is the column/field I sort by that has the LastName, FirstName.
Would like to insert 3 blank rows for each group of the first letter of the last name(So for LastNames begginning with A's at the end of the A's group
there will be 3 blank rows(even if there are no A's)also only for A's would there be 3 rows before any A's(if there are A's), then it would move on to the B's and only insert 3 blank rows at the end of the group
or if no B's then also include 3 blank rows and then it would proceed until the Z's with 3 blank rows at the end of each group(even if there are
no names in the group). */
CV3Client.GenderCode AS Gender,
RIGHT('00' + CAST(CV3Client.BirthMonthNum AS VARCHAR(2)), 2)
+ '-' + RIGHT('00' + CAST(CV3Client.BirthDayNum AS VARCHAR(2)),2)
+ '-' + CAST(CV3Client.BirthYearNum AS VARCHAR(4)) AS DOB,
ISNULL(CV3ClientVisit.IDCode, dbo.SXAAMFormatClientIDCodeFn(CID.ClientIDCode, CID.TypeCode, NULL)) AS ClientVisitMRN,
ISNULL(CV3ClientVisit.VisitIDCode, 'No Visit ID') AS ClientVisitIDCode,
[SXASRGCase].[CaseIdentifier] AS CaseNumber,
ISNULL(CV3ClientVisit.[TypeCode], '') AS VisitType,
ISNULL([SXASRGCase].BookingComments1, 'No Booking Comments #1') AS BookingComments1,
ISNULL([SXASRGCase].BookingComments2, 'No Booking Comments #2') AS BookingComments2,
ISNULL([SXASRGCasePreOpInfo].Notes, 'No PreOp Notes') AS PreOpNotes,
ISNULL([CV3ClientVisit].CurrentLocation, 'Location N/A') AS CurrentLocation,
pp.PrimarySurgeonDisplayName,
pp.PrimaryProcedureName,
pp.PreferenceCardName
FROM [SXASRGCase]
INNER JOIN #AllCases AllCases ON AllCases.CaseID = [SXASRGCase].CaseID
INNER JOIN CV3Client ON SXASRGCase.ClientGUID = CV3Client.GUID
INNER JOIN SXASRGCasePreOpInfo ON SXASRGCase.CaseID = SXASRGCasePreOpInfo.CaseID
LEFT JOIN CV3ClientVisit ON SXASRGCase.ClientVisitGUID = CV3ClientVisit.GUID
-- adds Surgeon, Procedure, PreferenceCardName:
CROSS APPLY (
SELECTTOP 1
--p.Name AS PrimaryProcedureName,
cp.Description AS PrimaryProcedureName,
u.DisplayName AS PrimarySurgeonDisplayName,
ISNULL(c.Name, '') AS PreferenceCardName
FROM SXASRGCaseProcedure cp
INNER JOIN SXASRGProcedure p ON cp.ProcedureID = p.ProcedureID
INNER JOIN SXASRGSurgeryStaff s ON cp.SurgeryStaffID = s.SurgeryStaffID
INNER JOIN CV3User u ON s.UserGUID = u.GUID
LEFT join SXASRGCasePreferenceCard b ON (b.CaseID = SXASRGCase.CaseID and b.CaseProcedureID = cp.CaseProcedureID)
LEFT join SXASRGPreferenceCard c ON (c.preferenceCardID = b.PreferenceCardID)
WHERE cp.CaseID = SXASRGCase.CaseID
AND cp.Active = 1
AND cp.IsPrimary = 1
ORDER BY cp.HasBeenPerformed DESC, cp.AssociatedCaseProcedureID ASC
) pp
-- Get MRN number for No Visit workflow
INNER JOIN CV3Location L ON L.GUID = SXASRGCase.LocationGUID
INNER JOIN CV3Location F ON F.GUID = CASE WHEN L.IsFacility = 1 THEN L.GUID ELSE L.FacilityGUID END
--=================================================================================================
LEFT JOIN CV3ClientID CID ON CID.ClientGUID = SXASRGCase.ClientGUID AND CID.TypeCode = F.ClientIDType AND CID.Active = 1 and CID.IDStatus = 'ACT'
)
SELECT * FROM FirstCases
ORDER BY ShowPatientName
September 29, 2017 at 12:05 pm
In the end I get results like this:
Blank Row
Blank Row
Blank Row
Abbe
Adams
Arlyn
Blank Row
Blank Row
Blank Row
Bird
Bryn
Burn
Bzork
Blank Row
Blank Row
Blank Row
C's-with no C names
Blank Row
Blank Row
Blank Row
Davis
Digger
Dizzy
Duegger
Blank Row
Blank Row
Blank Row
OR
A's-with no A lastnames
Blank Row
Blank Row
Blank Row
Bird
Bryn
Burn
Bzork
Blank Row
Blank Row
Blank Row
C's-with no C names
Blank Row
Blank Row
Blank Row
Davis
Digger
Dizzy
Duegger
Blank Row
Blank Row
Blank Row
September 29, 2017 at 12:14 pm
Try this on for size:SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @StartDt AS date = '01/01/2016', @EndDt AS date = '09/29/2017';
IF OBJECT_ID('tempdb..#AllCases', N'U') IS NOT NULL
BEGIN
DROP TABLE #AllCases;
END;
CREATE TABLE #AllCases (
CaseID BIGINT,
ClientGUID NUMERIC(16,0),
LocationGUID NUMERIC(16,0),
RoomDisplayName VARCHAR(255),
ScheduledStartDateTimeUTC DATETIME2
);
INSERT INTO #AllCases
SELECT DISTINCT
a.CaseID,
a.ClientGUID,
l.[guid] AS LocationGUID,
l.Name AS RoomDisplayName,
a.PlannedStartDateTimeUTC AS ScheduledStartDateTimeUTC
FROM dbo.SXASRGCase AS a
INNER JOIN SXASRGCaseStatus AS cs
ON cs.CaseStatusID = a.CaseStatusID
INNER JOIN CV3Location AS l
ON a.LocationGUID = l.GUID
INNER JOIN [SXASRGCaseProcedure] AS c
ON c.CaseID = a.CaseID
INNER JOIN CV3Client
ON a.ClientGUID = CV3Client.[GUID]
INNER JOIN CV3LocnFacility AS f
ON f.FacilityGUID = l.FacilityGUID
WHERE
(
a.ActualStartDate BETWEEN @StartDt AND @EndDt
AND
a.ActualStartDate IS NOT NULL
)
OR
(
a.PlannedStartDate BETWEEN @StartDt AND @EndDt
AND
a.ActualStartDate IS NULL
);
WITH LETTERS AS (
SELECT TOP (26) SUBSTRING(X.LETTER_LIST, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1) AS LETTER
FROM (
VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
) AS X (LETTER_LIST),
sys.all_objects
),
FirstCases AS (
SELECT DISTINCT
AllCases.CaseID,
AllCases.LocationGUID AS SurgeryRoom,
AllCases.RoomDisplayName AS Room,
CONVERT(DATE, AllCases.ScheduledStartDateTimeUTC) AS StartDate,
CONVERT(VARCHAR(5), CONVERT(DATETIME2, AllCases.ScheduledStartDateTimeUTC), 114) AS StartTime,
CONVERT(DATE, ISNULL([SXASRGCase].ActualEndDateTimeUTC, DATEADD(minute,[SXASRGCase].TotalDurationMinutes, CONVERT(DATETIME2, AllCases.ScheduledStartDateTimeUTC)))) AS EndDate,
CONVERT(VARCHAR(5), CONVERT(DATETIME2, ISNULL([SXASRGCase].ActualEndDateTimeUTC, DATEADD(minute,[SXASRGCase].TotalDurationMinutes, CONVERT(DATETIME2, AllCases.ScheduledStartDateTimeUTC)))), 114) AS EndTime,
[SXASRGCase].TotalDurationMinutes AS Duration,
ROW_NUMBER() OVER (PARTITION BY AllCases.RoomDisplayName, CONVERT(DATE, AllCases.ScheduledStartDateTimeUTC) ORDER BY AllCases.ScheduledStartDateTimeUTC) AS [Row],
CV3Client.DisplayName AS ShowPatientName, /*This is the column/field I sort by that has the LastName, FirstName.
Would like to insert 3 blank rows for each group of the first letter of the last name(So for LastNames begginning with A's at the end of the A's group
there will be 3 blank rows(even if there are no A's)also only for A's would there be 3 rows before any A's(if there are A's), then it would move on to
the B's and only insert 3 blank rows at the end of the group or if no B's then also include 3 blank rows and then it would proceed until the Z's with
3 blank rows at the end of each group(even if there are no names in the group). */
LEFT(CV3Client.DisplayName, 1) AS FIRST_LETTER, -- Key to letter scheme
CV3Client.GenderCode AS Gender,
RIGHT('00' + CAST(CV3Client.BirthMonthNum AS VARCHAR(2)), 2)
+ '-' + RIGHT('00' + CAST(CV3Client.BirthDayNum AS VARCHAR(2)),2)
+ '-' + CAST(CV3Client.BirthYearNum AS VARCHAR(4)) AS DOB,
ISNULL(CV3ClientVisit.IDCode, dbo.SXAAMFormatClientIDCodeFn(CID.ClientIDCode, CID.TypeCode, NULL)) AS ClientVisitMRN,
ISNULL(CV3ClientVisit.VisitIDCode, 'No Visit ID') AS ClientVisitIDCode,
[SXASRGCase].[CaseIdentifier] AS CaseNumber,
ISNULL(CV3ClientVisit.[TypeCode], '') AS VisitType,
ISNULL([SXASRGCase].BookingComments1, 'No Booking Comments #1') AS BookingComments1,
ISNULL([SXASRGCase].BookingComments2, 'No Booking Comments #2') AS BookingComments2,
ISNULL([SXASRGCasePreOpInfo].Notes, 'No PreOp Notes') AS PreOpNotes,
ISNULL([CV3ClientVisit].CurrentLocation, 'Location N/A') AS CurrentLocation,
pp.PrimarySurgeonDisplayName,
pp.PrimaryProcedureName,
pp.PreferenceCardName
FROM [SXASRGCase]
INNER JOIN #AllCases AS AllCases
ON AllCases.CaseID = [SXASRGCase].CaseID
INNER JOIN CV3Client
ON SXASRGCase.ClientGUID = CV3Client.[GUID]
INNER JOIN SXASRGCasePreOpInfo
ON SXASRGCase.CaseID = SXASRGCasePreOpInfo.CaseID
LEFT JOIN CV3ClientVisit
ON SXASRGCase.ClientVisitGUID = CV3ClientVisit.[GUID]
-- adds Surgeon, Procedure, PreferenceCardName:
CROSS APPLY (
SELECT TOP 1
--p.Name AS PrimaryProcedureName,
cp.[Description] AS PrimaryProcedureName,
u.DisplayName AS PrimarySurgeonDisplayName,
ISNULL(c.Name, '') AS PreferenceCardName
FROM SXASRGCaseProcedure AS cp
INNER JOIN SXASRGProcedure AS p
ON cp.ProcedureID = p.ProcedureID
INNER JOIN SXASRGSurgeryStaff AS s
ON cp.SurgeryStaffID = s.SurgeryStaffID
INNER JOIN CV3User AS u
ON s.UserGUID = u.[GUID]
LEFT join SXASRGCasePreferenceCard AS b
ON b.CaseID = SXASRGCase.CaseID
AND b.CaseProcedureID = cp.CaseProcedureID
LEFT JOIN SXASRGPreferenceCard AS c
ON c.preferenceCardID = b.PreferenceCardID
WHERE cp.CaseID = SXASRGCase.CaseID
AND cp.Active = 1
AND cp.IsPrimary = 1
ORDER BY cp.HasBeenPerformed DESC, cp.AssociatedCaseProcedureID ASC
) AS pp
-- Get MRN number for No Visit workflow
INNER JOIN CV3Location AS L
ON L.[GUID] = SXASRGCase.LocationGUID
INNER JOIN CV3Location AS F
ON F.[GUID] = CASE WHEN L.IsFacility = 1 THEN L.[GUID] ELSE L.FacilityGUID END
--=================================================================================================
LEFT JOIN CV3ClientID AS CID
ON CID.ClientGUID = SXASRGCase.ClientGUID
AND CID.TypeCode = F.ClientIDType
AND CID.Active = 1
AND CID.IDStatus = 'ACT'
)
SELECT L.LETTER, FC.*
FROM LETTERS AS L
LEFT OUTER JOIN FirstCases AS FC
ON L.LETTER = FC.FIRST_LETTER
ORDER BY L.LETTER, FC.ShowPatientName;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 2:45 pm
Amazing!! Worked exactly as I needed on the first try!
Thank you very much sgmunson for the super fast reply and incredible results! I think it is in fact much easier to control the data via the dataset as you suggested.
I really appreciate the help. Have a great weekend!
October 2, 2017 at 9:10 am
cor_dog2 - Friday, September 29, 2017 2:45 PMAmazing!! Worked exactly as I needed on the first try!
Thank you very much sgmunson for the super fast reply and incredible results! I think it is in fact much easier to control the data via the dataset as you suggested.
I really appreciate the help. Have a great weekend!
Glad I could help. It's almost always easier to control grouping by making the data fit the grouping requirements, rather than trying to fiddle with the settings in the reporting tool. I've found this to be true for both SSRS and Crystal Reports, and I would expect to see similar results with pretty much every other reporting tool that exists.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply