Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2890
  • Last Modified:

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
0
Robb Hill
Asked:
Robb Hill
  • 5
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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:
Try letting Crystal do most of the work

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

mlmcc
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now