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
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
ASKER
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.
--------------------------
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.
ASKER
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)
from some tables
where some criteria
group by region, user, type
order by region, user , type
Select region,type,user,coung(id)
from some tables
where some criteria
group by region, user, type
order by region, user , type
Have you tried using a cross-tab in the group header?
mlmcc
mlmcc
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you tried a simple SQL as
Select region,type,user
from some tables
where some criteria
mlmcc
Select region,type,user
from some tables
where some criteria
mlmcc
Glad i could help
mlmcc
mlmcc
What do you want the report to look like?
mlmcc