Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: FRED QUAYE
  Where is FRED QUAYE?
 
 Ghana
 FRED QUAYE
 To: Paul Gibson
  Where is Paul Gibson?
 Glasgow
 United Kingdom
 Paul Gibson
 Tags
Subject: RE: problem print group rpt with header
Thread ID: 415590 Message ID: 416253 # Views: 25 # Ratings: 0
Version: Not Applicable Category: C#.NET
Date: Wednesday, December 24, 2014 11:25:14 PM         
   


> > Coming from a typical foxpro background.
> >
> >
> > What should I group on ?
> >
> > Please help
>
>
> OK so let's break this down into manageable pieces to see if we can get somewhere a bit quicker than we are at the moment.
>
> When reporting there are at least two kinds of grouping: 1) How you group the data for the report 2) How you group the output of the report. Those might sound the same but they are subtly different but very important that people understand the difference.
>
> For #1 this is all done inside your query or queries you should structure your queries so that you get the correct number of records, the correct number of columns and the correct data within those columns on those rows. For some they might not actually "group" their data in the strictest use of the word, they might get all their data together and the closest thing they do to grouping it is to order it to aid the next bit of grouping. I would find it extremely difficult to tell you exactly how to extract the data you want from your database, I don't know your data structures and the way you are firing queries at me is confusing given that I don't know your data.
>
> I see from your initial post that you have clarified that there are 5 customers and each customer has 20 records. You indicate that you want each customers record on a new page so for #1 I suggest you write a query that will extract the 100 records (5 x 20) and you can help things along by ordering the results of your query by the customer first and then anything else after that. That should ensure that you have the correct number of records and they are in an order that helps you if you need to debug anything. Then all you need to do is make sure that you have the correct columns in the results.
>
> In my previous reply I showed a technique I use when I want to debug the data sets that I get back from my stored procs, you should use that when learning to make sure you are getting the data back as you want it or you should use a technique of writing test-bed scripts to run in your SQL engine to see if you are getting the data back as you want it. What I mean by that is to write a .SQL file that DECLAREs and SETs all the parameters at the top and then goes into your query, you can run the test-bed script in SQL Management Studio over and over again, testing different parameters just by changing the SET lines and re-running the test-bed script.
>
> An example of a test-bed would be something like this:
>
>
DECLARE @SiteOptions int, @CountryList nvarchar(max)
> 
> SET @SiteOptions = 3 -- Incremental Options parameter to say how the site is to behave.  Add 1 = Items Must Be Fated, 2 = Real Live Data (examine back-office fields)
> SET @CountryList = '' -- Comma Delimited list of countries to report on
> 
> SELECT .......

>
> ...please note I have very specifically obfuscated the query because non-disclosure agreements say I can't reveal the details. What I can do with that is alter the SET lines run the full query and test lots of parameters very quickly. The results pane of the management studio shows me the data that will be returned in each case so I can see if the correct data is being collected, if it looks the way I want it to, if it is being ordered correctly etc and adjust as required.
>
> That is job #1 done so move onto job #2.
>
> Once you know that the correct data is going to be sent then have a look at your RDLC and work out how you are going to group your output. In your case you indicate that you want to highlight the differences between customers and have a new page per customer that means that you want to group the report output by customer. Note that does not dictate that you need to group the SQL query by customer, quite the contrary because if you did that you would only get one record per customer. So just make sure you've got that clear, don't necessarily group the SQL query by customer, just write that so it returns the data as you want it. When grouping by customer I would recommend grouping by the field which is the customers unique identifier in the results set, not the customers name. The reason I do this is because many systems don't force uniqueness of customer names - for good reason therefore you could have data in your database where two different customers do actually have the same name. In that occurrence if you grouped by customer name then you would incorrectly merge the data from two different customers into one section so don't do that as it would be wrong to do so. If the customers are the same then you should have a procedure for merging the data in the database - don't cater for this by merging the data in the reports - do it properly. So you set the group by expression in your RDLC to be the customers unique identifier and set all the options to make sure you get a new page per group.
>
> That should be you sorted. I've typed a lot there but it is fairly simple, once you know how to do this it becomes automatic.


The issue I have is with the report viewer and not the stored procedure.


I test the data from the stored procedure by running the Executable.


The test output from the stored procedure gave 7 records.

However when you issue the grouping in the report viewer all the 7 records reduce to 2 only,

I must admit there are repetitions in the records.

This is the output from the stored procedure when executed


IDNO LOANUMBER PERIOD YEAR1 PAYMENT
----------------------------------------------------
001 1 12 2014 400
001 1 12 2014 400
001 1 1 2015 400
001 1 2 2015 400

002 1 12 2014 1400
002 1 12 2014 1400
002 1 1 2015 1400



However when I move the data to the report viewer, instead of seeing 7 records.

it gives me only 2 (Two).


I have ok now

Thanks a million



Thanks

ENTIRE THREAD

problm print grp rpt with header (sos) Posted by FRED QUAYE @ 12/8/2014 6:52:32 AM
RE: problem print group rpt with header Posted by Paul Gibson @ 12/8/2014 10:28:43 AM
RE: problem print group rpt with header Posted by FRED QUAYE @ 12/8/2014 11:40:07 AM
RE: problem print group rpt with header Posted by Paul Gibson @ 12/8/2014 12:17:32 PM
RE: problem print group rpt with header Posted by FRED QUAYE @ 12/22/2014 2:29:12 PM
RE: problem print group rpt with header Posted by Paul Gibson @ 12/22/2014 5:03:55 PM
RE: problem print group rpt with header Posted by FRED QUAYE @ 12/22/2014 5:50:38 PM
RE: problem print group rpt with header Posted by Paul Gibson @ 12/23/2014 10:19:05 AM
RE: problem print group rpt with header Posted by FRED QUAYE @ 12/24/2014 11:25:14 PM