Re-Grouping

[Pregius]

I'm using Crystal Reports 9.2.0.448

Hoping this is a very basic question but I don't know enough about the program and terminology to look in the right area to find the answer.

I've written a report that has three hierarchical layers of groups and in the footer for the innermost grouping I've distilled the data I really want (mostly the results of what where for me complex formulas comparing records within each group and between groups).

Is there a way to either start a new report with the individual records being these sets of data from the footers of the inner group of the current report (the catch being that I would need to not select some of them per a flag I've already set in each footer) or to suppress all the rest of the data in the current report and then re-group these footers differently than the groups that where required to calculate their data in the first place? Basically the current grouping was only a tool to help me calculate the records (stored in the footer of the 3rd level grouping) and now I need to make a report with just those calculated records grouped in a totally different way for the end user.

I can give more detail about the project and how I have the current report grouped but I don’t want to put everyone to sleep if there is a simple answer on how to group one way to calculate a sort of summary data set and then group that new data set completely differently.

Thanks!

 

[synapsevampire]

The short answer to your lengthy post is No.

There are subreports, but design should precede development, you're requesting the reverse.

I suggest that you'll keep more people "awake" by supplying technical posts, lengthy text posts are usually much less informative than the poster intended.

Try supplying the following:

Database used
Connectivity used
Capable of building Views/Stored Procedures/Querys?
Example data
Expected output

We know that you have CR 9, and formulas in a Group.

It may be that we can even use a crosstab to handle your summary requirements, but it's best to give an example of what it is that you want.

-k

 

[Pregius]

After viewing a previous thread I had the idea that groups for formula purposes could somehow be suppressed so as not to affect the final report. Since that apparently isn't the case I went ahead with the very inelegant solution of exporting the refined data that was created with the original report's three levels of groups to Excel and then importing it into Access and writing a 2nd report to group the first report's data the way the client needs it.

I have very little information on the database. I believe that it's Oracle but don't know the version. Not sure of the connection type either. It is unlikely I could get permission to make any changes/additions to the db and if a query can do what I need it would be a rather complex one I think.

Basically each data set is an event (charge) with a start and end time. The charges are grouped together in a higher-level event (booking). Each also has a person's ID associated (jail ID) and an agency (agency id) that is responsible for the bill.

My job was to sort out which one of ten agencies was responsible for each portion of each individual’s booking period(s). While one person my have multiple bookings in a report period they should not overlap at all. However, there are almost always multiple charges in a booking period and the charges start and disposition datetimes almost always overlap in an unpredictable way and often come from different agencies. I use a hierarchy of agencies to decide which should be billed for time when multiple charges are pending from multiple agencies.

I did this by grouping first by individual and then by booking and then by agency. I forced the agencies to be listed in order of hierarchy (did this manually as I wasn't able to find a way to automatically sort by a calculated field that wasn't the field they where grouped on). Then using maximum and minimum functions to find the full period that includes all the charges for a particular agency and stored this in the group footer. Then I used the “three formula method” (since I couldn't get the previousvalue function to work on a calculated field in a footer) to adjust the effective time period after comparing to the previous higher priority agency group period (i.e. the lower priority agency doesn't get billed until all higher priority agency’s charges are dispositioned).

So basically I found grouping by individual and then agency necessary to calculate the period for which each agency should be billed for that individual.

However the final report is a bill for the agency and needs to be grouped by agency first and list each individual and the time period for which that agency is liable for that individual’s bill (the data that was in the footer for the 3rd level group in the first report).

What I've read about a subreport didn't sound like it could gather these footers together from the entire report and regroup them. I should probably do some research on what a crosstab is before spending time writing a program to automate the very tedious task of exporting to excel, importing to access and then running the 2nd crystal report.

This whole project would have been much easier if I could have used previosvalue on a calculated field in a group footer and if there was a way to feed rows from one report into another for re-grouping.

 

[synapsevampire]

Sure sounds like you went the long way home to me.

Real Databases are very fast with complex capabilities, that's why you'll NEVER see a big kid database in Excel or Access.

If you decide to post example data and expected output I'm sure someone here will be able to assist.

-k

 

[Pregius]

I'd much rather keep it all in crystal and not have to port out of one report and into another via a table in order to re-group. However since I can only hope to read the real database I have to make do with what I have available that I can write to (access). After I do my considerable calculations via the many lines of formula code in the first report there are only a few thousand sets of distilled data to put in the table for the 2nd monthly report.

Is the export “odbc” option actually designed to export data to a db? I had thought the export to excel option was just a way to view and move the report and I was doing something sneaky by then moving it to a db (all be it a wimpy one - access) that could then be read by crystal for another report.

So is output to a table and then a new report on that table the way people normally get around the re-grouping problem?

I’ll see if I can scare up some real data if that’s easier to follow than the description.

 

[synapsevampire]

No, outputting to Excel and then another table is NOT common (I hope).

ODBC can export to a NEW table in a database.

SQL can generate temporary files on the fly and do all sorts of wonderful things in a Stored Procedure.

You don't need real data, but I need example data and expected output here.

You've provided a tome, yet very little technical information, take a couple of minutes and provide some example data and expected output, and then illustrate the dilema without going into your preference for Excel or external tables.

A subreport may make short work of this.

-k

 

[Pregius]

I'll look into the export to ODBC option as it sounds like it can cut Excel out of this process. However, I'll still be exporting out of Crystal to a table and then back into a new report unless a way can be found around my regrouping problem. I would much rather not leave Crystal at all but it looks like the export ODBC option was designed to export directly to a table, something I didn't know when I was going through Excel solely as a way to Access. Will the ODBC export option support Access?

Here is simplified/privatized data on an individual during the November report period (the source table contains similar records on many other individuals over a wide range of time).

BookN BookA ChargeD ChargeT DispD DispT
1 X 11/21/2003 0714 11/25/2003 2009
2 X 11/29/2003 2319 12/12/2003 1713
2 Z 11/29/2003 2319 12/12/2003 1713
2 Z 11/29/2003 2320 12/12/2003 1713
2 X 11/29/2003 2343 12/12/2003 1714
2 Z 11/29/2003 2344 12/12/2003 1714
2 Z 11/29/2003 2345 12/12/2003 1714

Each record shows the start (Charge Date and Charge Time to Disposition Date and Disposition Time) of a charge. Why they split date and time into two fields and recorded the time as a string I don't know but I've worked around this in formulas which handle bad/missing data and convert to a start datetime and end datetime. BookN is a booking number and correlates to a stay in jail so no individual can have two concurrent booking numbers (must be released before being re-booked). BookA is the agency that laid the charge and will be responsible for the billing for any part of the charge period where a higher priority agency isn't responsible (there are about 10 agencies total with a priority hierarchy and differing rules regarding uncharged time at the beginning of their responsible periods and different full and partial day rates).

My end report is grouped by BookA and lists all BookN for the report period and the adjusted start and stop times for which that BookA was responsible for the BookN bill (which it then calculates out). The line for this inmate would appear like this in part:

Agency “X” Bill
BookN ChargeD ChargeT DispD DispT
1 11-21-2003 0714 11-25-2003 2009
2 11-29-2003 2319 11-30-2003 2343

Agency “Y” Bill
<none for this BookN>

The end time of the 2nd entry is calculated to be within the report period and will be the same time that the next month's report will pick up billing from. BookA = Z doesn't get billed for either BookN because BookA = X has priority over it in the hierarchy and none of BookA = Z's BookN = 2 charges happen to extend outside of BookA = X's charge period.

In order to make the calculations necessary to compare the charge periods for an individual's BookN between BookA I found it necessary to first group on BookN (I actually grouped on the individual ID first but see now that BookN for the same individual don't overlap so I could do away with that highest grouping) and then on BookA. I force the BookA to be sorted by priority and then compare the start and end datetimes for a BookA’s total charge period against the previous BookA group (after checking to see that the previous is the same BookN and not a different BookN). After making all the calculations I need I store the calculated row (adjusted start and end times per the complex rules) in the footer of the inside group, BookA.

The problem is that the final report must be grouped by BookA (first and only group) with a row for each BookN as calculated in the first report’s inner group footers. The solution I found for this re-grouping problem was to export the footers from the BookA grouping of the first report and import them into a second report that groups on BookA only. The first report's outside grouping by BookN was just for purposes of making the needed complex calculations comparing start and end datetimes between BookA’s for a single BookN. I believe the complexity of the calculations and the number of BookN’s and BookA’s in a report preclude using a query to perform the work I now perform in the first report (at least without the luxury of being able to write something to that db such as a new view and even then I would have a two step process involving creating a new table to report off of which is where I am now with the 2nd report).