Combining Groups In Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Combining Groups In Report

Author

Topic

thehollis
Starting Member

4 Posts

Posted - 2008-06-24 : 15:26:32

Is there a way to combine groups that you've set up in a report?

For example:

I am pulling information that is entered by a form. The 4 possibilities are (blank), Y, N, and P, which has been made into a group to uniformly sort these options with a sum of how many users have each answer:

(blank) 10
P 20
Y 35
N 100

However, (blank) and N are pretty much the same thing...so I'd like to combine those results like this:

N 110
P 20
Y 35

Any help is appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-24 : 15:39:11

Do it in the query. Could you post what you have so far so that we can add to it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thehollis
Starting Member

4 Posts

Posted - 2008-06-24 : 15:50:17

SELECT EMPLOYEE, LAST_NAME, FIRST_NAME, DEPARTMENT,
PROCESS_LEVEL, TERM_DATE, SCHEDULE, EMP_STATUS,
UNION_CODE, AUTO_DEPOSIT
FROM table.EMPLOYEE
WHERE (TERM_DATE = '01-JAN-1700')

This is the query I'm using to create a report of 6 demographic views (who is using direct deposit and who isn't). The WHERE statement is to ensure the employee is still active.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 01:39:44

quote:
Originally posted by thehollis

SELECT EMPLOYEE, LAST_NAME, FIRST_NAME, DEPARTMENT,
PROCESS_LEVEL, TERM_DATE, SCHEDULE, EMP_STATUS,
UNION_CODE, AUTO_DEPOSIT
FROM table.EMPLOYEE
WHERE (TERM_DATE = '01-JAN-1700')

This is the query I'm using to create a report of 6 demographic views (who is using direct deposit and who isn't). The WHERE statement is to ensure the employee is still active.

Thanks!

What is the field on which you're groupinng on? i.e fields that contains (blank),P,N...

thehollis
Starting Member

4 Posts

Posted - 2008-06-25 : 09:49:53

quote:
Originally posted by visakh16

quote:
Originally posted by thehollis

SELECT EMPLOYEE, LAST_NAME, FIRST_NAME, DEPARTMENT,
PROCESS_LEVEL, TERM_DATE, SCHEDULE, EMP_STATUS,
UNION_CODE, AUTO_DEPOSIT
FROM table.EMPLOYEE
WHERE (TERM_DATE = '01-JAN-1700')

This is the query I'm using to create a report of 6 demographic views (who is using direct deposit and who isn't). The WHERE statement is to ensure the employee is still active.

Thanks!

What is the field on which you're groupinng on? i.e fields that contains (blank),P,N...

AUTO_DEPOSIT contains the values (blank), N, Y, and P. Sorry.

chrpeter
Starting Member

31 Posts

Posted - 2008-06-25 : 10:20:29

You could create a calculated variable in the dataset and if AUTO_DEPOSIT is (blank) or N set have it return N, otherwise return the value.
=IIF(AUTO_DEPOSIT = "" OR AUTO_DEPOSIT = "N", "N", AUTO_DEPOSIT)
Then group on this new variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 13:46:45

or created the calaculated member in query itself.like:-

SELECT EMPLOYEE, LAST_NAME, FIRST_NAME, DEPARTMENT, 
PROCESS_LEVEL, TERM_DATE, SCHEDULE, EMP_STATUS, 
UNION_CODE, AUTO_DEPOSIT,
CASE WHEN AUTO_DEPOSIT IN('N','(blank)') THEN 'N'
     ELSE AUTO_DEPOSIT
END AS groupfield
FROM table.EMPLOYEE
WHERE (TERM_DATE = '01-JAN-1700')

and use this in place where you currently group by AUTO_DEPOSIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources