Report group by and sort by

[junction123]

Hi

I am using CE 9.0 and SQL. The report is generated by a stored procedure.

I am grouping by:

dept name
full name
user name

The report should return 10 pages. I only get 2 pages. Is it filtering som of the records based on the dept name?

I need to do this report ASAP

Thanks in advance!
junction123

 

[Turkbear]

Hi,
What database?
What tables are involved in the SP?
What is the SP's code? Any where clauses?
How much data is returned when the SP is executed stand-alone, if possible?

 

[junction123]

The database is SQL server 7.x


Both the report and the proc return 4896 records. But the report does not display all the records. The count though is correct.

Below is the procedure code....



CREATE PROC ASP_GetUsers
@applicationName varchar(12) = NULL
, @accessLevel float = -1
AS

/*--- Create a temp table to hold all of the permissions ---*/
CREATE TABLE #OSMPermission
(
ID INTEGER NOT NULL
, Name VARCHAR(50) NOT NULL
, Code INTEGER NOT NULL
)

/*--- Load all of the users that we are interested in into a temporary table ---*/
SELECT ID = usr.ID
, UserName = RTRIM(usr.Lastname) + ', ' + RTRIM(usr.Firstname)
, DomainLogin = usr.DomainLogin
, DeptName = hr.DeptName

INTO #KM_RW_Users
FROM OSMUserGrpXUserUsers grp_usr

JOIN OSMUser usr
ON grp_usr.UserID = usr.ID
AND usr.Active = 1
AND usr.DomainLogin IS NOT NULL

LEFT JOIN xRawHR411Feed hr
ON usr.HRGUID = hr.HRGUID

/*--- Load all of the permissions associated with ResearchWire ---*/
IF UPPER(ISNULL(NULLIF(@applicationName, ''), 'RW')) = 'RW'
BEGIN
INSERT #OSMPermission
(
ID
, Name
, Code
)
SELECT ID
, Name
, Code

FROM OSMPermission perms
WHERE ISNULL(perms.ExtraDouble, 0) = ISNULL(NULLIF(@accessLevel, -1), ISNULL(perms.ExtraDouble, 0))
AND perms.Code BETWEEN 70000 AND 79999
END

/*--- Load all of the permissions associated with KM ---*/
IF UPPER(ISNULL(NULLIF(@applicationName, ''), 'KM')) = 'KM'
BEGIN
INSERT #OSMPermission
(
ID
, Name
, Code
)
SELECT ID
, Name
, Code

FROM OSMPermission perms
WHERE ISNULL(perms.ExtraDouble, 0) = ISNULL(NULLIF(@accessLevel, -1), ISNULL(perms.ExtraDouble, 0))
AND perms.Code BETWEEN 50000 AND 59999
END

/*--- Retrieve all of the Users that have permissions granted to them individually ---*/
SELECT UserID = usr.ID
, UserName = usr.UserName
, Login = usr.DomainLogin
, DeptName = usr.DeptName
, PermissionID = perms.ID
, PermissionName = perms.Name
, UserGroupID = -1
, UserGroupName = 'Permission granted to individual'

FROM #OSMPermission perms

JOIN OSMPermXUserGranted perms_usr
ON perms.ID = perms_usr.PermissionID

JOIN #KM_RW_Users usr
ON perms_usr.UserID = usr.ID

LEFT JOIN OSMPermXUserRevoked perms_usr_rev
ON perms.ID = perms_usr_rev.PermissionID
AND usr.ID = perms_usr_rev.UserID

WHERE perms_usr_rev.PermissionID IS NULL

UNION

/*--- Retrieve all of the Users that have permissions granted to them via groups ---*/
SELECT UserID = usr.ID
, UserName = usr.UserName
, Login = usr.DomainLogin
, DeptName = usr.DeptName
, PermissionID = perms.ID
, PermissionName = perms.Name
, UserGroupID = grp.ID
, UserGroupName = grp.Name

FROM #OSMPermission perms

JOIN OSMPermXrGrpGranted perms_grp
ON perms.ID = perms_grp.PermissionID

JOIN OSMUserGroup grp
ON perms_grp.UserGroupID = grp.ID

JOIN OSMUserGrpXUserUsers grp_usr
ON grp.ID = grp_usr.UserGroupID

JOIN #KM_RW_Users usr
ON grp_usr.UserID = usr.ID

LEFT JOIN OSMPermXGroupRevoked perms_grp_rev
ON perms.ID = perms_grp_rev.PermissionID
AND grp.ID = perms_grp_rev.UserGroupID

WHERE perms_grp_rev.PermissionID IS NULL

ORDER BY UserName, UserID, PermissionName, PermissionID, UserGroupName, UserGroupID

RETURN 0
go


Thanks!
juntion123

 

[Turkbear]

Odd..Any sections of the report being supressed?
Is there a Group selection formula involved?
Anything in common about what records are and what records are not shown?

 

[junction123]

No group selection formula is involved. All I am doing is having three groups

Group 1 by Dept Name ASC

Group 2 by Full Name ASC

Group 3 by User Name ASC

I did not check what are missing but I think it is showing only one entry per group.

Thanks!
junction123

 

[junction123]

Turkbear

The report is missing the domain "NYC". We have several domain names and I noticed that records with NYC domain are not being displayed though the count at the bottom shows Record: 4896 which is correct.

Thanks!
junction123

 

[Turkbear]

Hi,
Odd, if no supression -

When you run the Proc by itself, do those records appear?
Not just in the count,but in the data?



P.S.: can you create a variant of the SP to return ONLY the NYC domain- if that works, then something in the report design or perhaps ths user's permissions are at work.

 

[synapsevampire]

If you're getting the right number of rows (as indicated by the row count), yet the report isn't showing some data, then there's some sort of suppression at work.

What count are you using to determine that the record count is correct?

Crystal shows 2 fields while bringing data in, rowsused/rows queried

The report->Edit Selection Formula might be further eliminating rows.

And each field could have suppression on it if the detqail section itself doesn't.

-k

 

[junction123]

This has been resolved since. The grouping order for the columns was incorrect.

Thanks
junction123