Creating Pivot Report using Crystal Reports 9 from a sql server stored procedure

I am using sql server 2000 and crystal reports 9.0.  
I have a stored procedure than generates a table that looks like the following:

Region   User  Type  SummaryField
____________________________
North      Robb   A          10
South     John    B           4


I would like a pivot table in Crystal that does the following:

Creates a header by Region
Then in the details groups all users in that region
Pivots the type as column names
The row data is the summary field listed under each column(type)
Then has a footer with a another total


My problem is I cant seem to get the pivot to allow me to work in both the header and the details section of crystal...its only allowing me to create this functionality in the header region..which is causing this to look very jumbled...
Any ideas on how to accomplish this task would be very appreciated.

Thanks
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?
 
mlmccCommented:
Try letting Crystal do most of the work

Select region,type,user from some tables
where some criteria

mlmcc
0
 
mlmccCommented:
YOu could roup the report by region then show the information on each user in the detail section.

What do you want the report to look like?

mlmcc
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
REGION
------------------------------------

          TYPE TYPE TYPE TYPE
USER  sum   sum   sum   sum   |  tot
USER  sum   sum    sum  sum   |  tot
USER  sum  sum     sum   sum  |  tot
           ---------------------------
            tot     tot        tot     tot

There could be any number of columns created from the pivot for Type for each row user.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Robb HillSenior .Net DeveloperAuthor Commented:
Should I rearange my stored procedure to get the data organized differently before passing it to crystal..if so..here is my sql

Select region,type,user,coung(id) as summary
from some tables
where some criteria
group by region, user, type
order by region, user , type
0
 
mlmccCommented:
Have you tried using a cross-tab in the group header?

mlmcc
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
i made a crosstab...but its not formatting correctly...its putting a single record for user on each page randomly...and then its not allowing me to group all users on one page by region
0
 
mlmccCommented:
Have you tried a simple SQL as

Select region,type,user
from some tables
where some criteria

mlmcc
0
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.