Query design with two GROUP BY headers

I'm using VB6, SQL Server 7, and CR8.5.
I think this is more of a SQL design question but a large part of it depends on how I want the data displayed in my Crystal Report.
My report should be grouped twice:
The outer group should be:
And the inner group should be:
I think I need either a temporary table or I think I may be able to get the data from a nested SELECT statement; maybe an INNER QUERY or a SUBQUERY.
This is very complicated but I'll try to explain it the best I can.
In my inner group, since the billnum is the key field, the rest of the data here is correct.
But, when I add my outer group which is based on a different key field, Analyst; the outer group key field takes precedence over what is beneath it and screws up the values of the inner group.
I think I need a subquery because I need to get the value of the outer group first, and then group on that value for the inner group, or vice versa.
It should fall down like a hierarchy;
There's not that much code but the whole thing is that I have a stored procedure which takes a parameter and calls a view, which is a big select statement with all of the values I need for my report.
Here's my stored procedure:
(@p1 CHAR(15))
SELECT * FROM rptvwMyview
WHERE rptvwMyView.Sponsor = @p1
ORDER BY rptvwMyview.Analyst, rptvwMyview.BillNum
And here's the view that it calls:
CREATE VIEW rptvwMyview
Bill.BillNum, Bill.Sponsor, Bill.Title, Bill.OriginalCommittee,
Bill.CommitteeDate, Bill.BAMCode, Bill.BillMemoCode,
CommitteeActions.ActionCode, BillAssignments.Analyst,
Bill.Bill99Code, Bill.SenateNum, Requests.Requester,
LEFT JOIN BillAssignments
ON Bill.BillNum = BillAssignments.BillNum
 LEFT JOIN Requests
ON Bill.BillNum = Requests.BillNum
LEFT JOIN CommitteeActions
ON Bill.BillNum = CommitteeActions.BillNum
LEFT JOIN BillComment
ON Bill.BillNum = BillComment.BillNum
(Requests.BillNum IS NOT NULL) OR
    (Bill.Sponsor <> 'Budget') AND (Bill.Bill99Code = 'Y') AND
    (CommitteeActions.ActionCode IS NULL) OR
    (Bill.Sponsor <> 'Budget') AND
    (CommitteeActions.ActionCode IS NULL) AND
    (Bill.InWAM = 'Y') AND (Bill.BAMCode IN ('R', 'X', 'U'))
ORDER BY BillAssignments.Analyst, Bill.BillNum
Maybe I'm soing too much here (or not enough) or maybe there is a better way.
Anyway, since I'm at a standstill, I'll take any ideas or solutions.
Here's a sample of how I'd like my report to look:
(GH1: Analyst)
(GH2: BillNum)
   BillNum    Title  CommDate  BAMCode Analyst(subreport)
   A00100     title  01/01/01   A      Johnson

Thanks in Advance.

Who is Participating?
DRRYAN3Connect With a Mentor Commented:
Query looks right to me.  Are you using Crystal Reports to link the tables or SQL Server?  It sounds like you are using Crystal.  You may need to add an index to the temp table on billnum and/or make sure you have the link direction in the CRW VLE set properly and that you have defined whether it's one-to-one or one-to-many.


If your view contains the various columns you need for your report and the SP is simply providing you a mechanism to limit the rows returned from the view, then the recordset results from the stored procedure should still be ordered by Analyst and BillNum.

I'm not clear on what the question is here.  Is the view/SP combination returning the right data?  Are you having difficulties in the grouping in the report?  Or do you have overlap in your data such that the analyst/billnum combination is not unique and you have the same information appearing multiple times on your report?

Let me know
jtrapat1Author Commented:
Thanks for the response.
Yes, the sp filters my original view and returns the correct data.
I am having trouble grouping the report.
I thought I might need either a temp table or maybe write the SQL differently to get the correct ordering.
It may be the linking fields between the outer and inner group fields.  Since the same value is included in both the outer and inner groups, this is where it may be getting messed up.
I don't have overlap or multiple values appearing.
But,my analyst/billnum keys are not unique,when I add the second grouping.
The Analyst values in the inner group are included in a cross-tab, since there is a one-to-many between the BillNum and the Analyst.

Here's a Before/After:
Before: (correct values)
BillNum  Title   BAMCode  CommDate   Analyst(cross-tab)
A0010    desc    90001    01/01/01   Johnson
Now,when I add the outer group by header:
BillNum  Title  BAMCode   CommDate   Analyst(cross-tab)
A0010    desc   90001     01/01/01   Johnson
You see how I only get Johnson,one value for the Analyst cross-tab report?  There should be multiples.
I think it may be because of the relationship between the outer analyst and the inner analyst.
Maybe this value should be based on a different datasource.

This is hard to explain so if I'm not clear,let me know.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Why are you using a cross-tab? Where is the crosstab being performed?  I'm assuming you mean CRW's Crosstab wizard or an SQL crosstab.

What's your goal here?  To have the list of analysts be alphabetical?  If so, delete the analyst group and go to the sorting menu and add analyst to the end of the list.  (Sorry, can't remember the menu commands & I'm at home).

If you need to group by analyst to make the overall list come out by analyst alphabetically, you may find that you need to add a column to your recordset for a "primary analyst" which would be a one-to-one for each billnum (pick just one analyst for each billnum to order the report by).

If all the values for BillNum, Title, BAMCode, CommDate are the same but there can be multiple analysts, put your groups in place, and in the detail section (or wherever you are printing the values), check the Suppress Duplicate Values box in each of the columns BillNum, Title, BAMCode, CommDate.  Do not suppress dups in Analyst.

jtrapat1Author Commented:
I'm using a cross-tab since we had a lot of one-to-many relationships between our data and these worked a lot faster than subreports.
The cross-tab is being performed inside the inner GROUP BY since we may have more than one analyst assigned to a certain billnum.
I did use the CRW Cross-Tab wizard to create the cross-tab.
Usually,it's used for numerical data but because of the one-to-many relations,I can use the MAX() function to get a string to show up in the list;i.e. Analyst Names.

I may have the linking field wrong here:
I'm at home,too; I'm on vacation this week so I'll have to explain it from memory.

If you go to Insert>Cross-Tab, there are three tabs.
The first two tabs are for formatting and displaying gridlines.  The third tab is where you choose the linking field and the field you want to shop up as a list.
I used the field in my sp, rptspMysp.Analyst, as the field I want to show up and this may be wrong, since this value is coming from the stored procedure;maybe it should be coming from the view.
The linking field is rptspMysp.billnum since this is the key for this inner group.

Yes,I would like the Analysts to be listed alphabetically, both in the INNER and OUTER groups (if possible).
I'll try what you suggested with the SORT and you are right I do have a "Primary Analyst".
The Primary Analyst would have to be the result of another SELECT statement:
"SELECT BillAssignments.Analyst FROM BillAssignments WHERE Priority =1"
How would I add this column to my VIEW?

Thanks again for all the help.


This query will result in a lot of duplicate data in the fields in the Bill table being returned, which is fine.  You need to define the break points in your report with groups.  If you want the report to list the bills in bill number order, showing each analyst associated with that bill in alphabetical order, like this (paste into notepad with courier):

Bill 100  Bill Title  BamCode  Analyst_A
Bill 200  Bill_Title  BamCode  Analyst_A

then the analyst group has to go and you need to add a sort on analyst (not a group).

If you want to list the bills based on primary analyst, and then in bill number order after that, like this:

Primary Analyst #1
Bill 100  Bill Title  BamCode  Analyst_A
Bill 200  Bill_Title  BamCode  Analyst_A

Primary Analyst #2
Bill 300  Bill Title  BamCode  Analyst_G
Bill 400  Bill_Title  BamCode  Analyst_A

then you will need two columns containing the analyst information.  The first will be the primary analyst associated with the bill number.  The second will contain all the analysts associated with the bill number.  You would create a GROUP on the first and a SORT on the second.  Use the suppress dups checkbox as described and you can format the lines as you wish.

I tend to stay away from the CRW crosstabs because you really lose the ability to format the data the way you want to.

jtrapat1Author Commented:
Thanks for the tips.
I got back to work today and I tried what you suggested but I don't understand it completely.
Can you explain it a little more?
How can I add a column for Primary Analyst to my recordset?
I don't know how to add these two columns to the report:
One for Primary Analyst associated with BillNum and the second being all the Analysts associated with the BillNum.

I know how to write the SQL for Primary Analyst; it would be:
"SELECT BillAssignments.Analyst FROM BillAssignments WHERE Priority =1"
Would this be a nested statement inside my main view?
Or could I save this as a separate view?


The primary analyst column will have to added to your view.  You could create a self-join in your query or create a temp table consisting only of the bill number and primary analyst and then join that temp table to your view.  Leave the query alone otherwise to make sure you get all analysts associated with the bill.

To add the column to your report, open the report and click on verify database from the database menu.  Answer yes when asked to fix up the report.  The new field will now be available to insert.

jtrapat1Author Commented:
Thanks again for the help.
I'm sorry; but I'm still having trouble getting the outer group by field to show up on the report.
I'm almost there but I still need a little help.
I tried what you suggested but I'm getting back an empty field for the outer group by header.
I created a temp table to hold the billnum and primary analyst.
(BillNum CHAR(6), PrimaryAnalyst CHAR(15))
Insert into rptTempTable (BillNum, PrimaryAnalyst)
(SELECT Bill.BillNum,BillAssignments.Analyst AS PrimaryAnalyst
LEFT JOIN BillAssignments
ON Bill.BillNum = BillAssignments.BillNum
WHERE BillAssignments.Priority =1)

When I run this thru query analyzer I get back about 2000 rows so the query is correct but I must be linking it wrong.
I linked this temp table to my view, as described above, BillNum to BillNum, in Visual Linking Expert.
I created a new group by field and dragged the PrimaryAnalyst field in here where I wanted it to show up.

But, I'm getting back a blank field for my outer group by.

Can you think of anything that I may be doing wrong?

jtrapat1Author Commented:
I tried to add in the index but CR would not let me.
When I went into Visual Linking Expert, the Add Index option was grayed out.
So, I tried to add it in manually from SQL Server but CR didn't recognize it.
I read a post from Ken Hamady and he said that "Indexes only apply to PC files,  not to ODBC"
Since I am using ODBC as a connection, I think I have to start over.
I don't mind starting over; there's no time limit on the project- I just want to understand how to get the correct output.

Can you just tell me- (since you know my database pretty well now):
What is the best method to use for getting this type of output, using ODBC as the connection type?
I still need to pass a parameter and I need the double GROUP BYs as I described.
Is is still worthwhile to use a sp to accept a parameter and pass this to a view?
What are my other options?
Could it be done with that nested query or inner query like I suggested before?
I was thinking I could get the value of one query first and then pass this to the outer query.

I'll take any suggestions and don't mind starting over.

Thanks again and I appreciate all of the help.
jtrapat1Author Commented:
I have a question related to this same report with two "group by" headers.

What, in your opinion, is the best way to connect to a report from Visual Basic, to get this desired data?
I tried most of them but found ODBC to be the best and fastest.
If I tried to connect natively thru OLE DB, I found that my users get the login box with password and, as there are only about 50+ users, I'd rather just launch the report.

Maybe there are other ways to connect that give you more flexibility and that won't let you run into the problems that I had.
Can you recommend the best connection method to help me get the results that I need on my report.
I do believe that crystal can do this report; I just don't know how to get the correct data and grouping.

Thanks again and I appreciate all the help.
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.