Solved

Query design with two GROUP BY headers

Posted on 2001-08-29
11
237 Views
Last Modified: 2008-02-01
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:
GROUP BY ANALYST
And the inner group should be:
GROUP BY BILLNUM
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;
Analyst>BillNum>Details
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:
CREATE PROCEDURE rptspMysp
(@p1 CHAR(15))
AS
SELECT * FROM rptvwMyview
WHERE rptvwMyView.Sponsor = @p1
ORDER BY rptvwMyview.Analyst, rptvwMyview.BillNum
----------
And here's the view that it calls:
CREATE VIEW rptvwMyview
AS
SELECT TOP 100 PERCENT
Bill.BillNum, Bill.Sponsor, Bill.Title, Bill.OriginalCommittee,
Bill.CommitteeDate, Bill.BAMCode, Bill.BillMemoCode,
CommitteeActions.ActionCode, BillAssignments.Analyst,
Bill.Bill99Code, Bill.SenateNum, Requests.Requester,
 Requests.Comments,BillAssignments.Priority
FROM Bill
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
WHERE
(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)
   Analyst
   Johnson
(GH2: BillNum)
   BillNum    Title  CommDate  BAMCode Analyst(subreport)
   A00100     title  01/01/01   A      Johnson
                                       Smith
                                       Lewis

Thanks in Advance.
John


0
Comment
Question by:jtrapat1
  • 6
  • 5
11 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
John

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
DRRYAN3
0
 

Author Comment

by:jtrapat1
Comment Utility
DRRYAN3,
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)
GH1:
BillNum  Title   BAMCode  CommDate   Analyst(cross-tab)
A0010    desc    90001    01/01/01   Johnson
                                     Lewis
                                     Roberts
-------
Now,when I add the outer group by header:
After:
GH1:
Analyst
Anderson
GH2:
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.

Thanks
John
0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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.

DRRYAN3
0
 

Author Comment

by:jtrapat1
Comment Utility
DRRYAN3,
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.
John

0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
John

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
                               Analyst_B
                               Analyst_C
Bill 200  Bill_Title  BamCode  Analyst_A
                               Analyst_D
                               Analyst_F

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
                               Analyst_B
                               Analyst_C
Bill 200  Bill_Title  BamCode  Analyst_A
                               Analyst_D
                               Analyst_F

Primary Analyst #2
Bill 300  Bill Title  BamCode  Analyst_G
                               Analyst_H
                               Analyst_R
Bill 400  Bill_Title  BamCode  Analyst_A
                               Analyst_B
                               Analyst_C

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.

DRRYAN3
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jtrapat1
Comment Utility
DRRYAN3,
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?

Thanks
John
0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
John

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.

DRRYAN3
0
 

Author Comment

by:jtrapat1
Comment Utility
DRRYAN3,
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.
CREATE TABLE rptTempTable
(BillNum CHAR(6), PrimaryAnalyst CHAR(15))
Insert into rptTempTable (BillNum, PrimaryAnalyst)
(SELECT Bill.BillNum,BillAssignments.Analyst AS PrimaryAnalyst
FROM Bill
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?

Thanks
John
0
 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 250 total points
Comment Utility
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.

DRRYAN3
0
 

Author Comment

by:jtrapat1
Comment Utility
DRRYAN3,
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.
John
0
 

Author Comment

by:jtrapat1
Comment Utility
DRRYAN3,
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.
John
 
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now