Solved

# Crystal Reports - Using a counter as a group header

Posted on 2014-09-22
188 Views
Hi,
I apologise for not knowing how to even DESCRIBE what I'm looking for! All I can do is give you an example of what I'd like to do.

Imagine I have a table of data called debt_table, showing monies I loaned to various people.
(apologies for the rubbish layout - I don't know how to create tables inside these questions!)

NAME,AMOUNT,DAYS_OVERDUE
Frank,10,1
Bobby,20,2
Michael,25,3
Frank,30, 5
I am trying to create a report that would produce the following as output:

***********start of report**********

Debts

1 or more days overdue
NAME,TOTAL
Frank,40 (as you can see, the report is displaying the total of Frank's two debts)
Bobby,20
Michael,25

2 or more days overdue
NAME,TOTAL
Bobby,20
Michael,25
Frank,30

3 or more days overdue
NAME,TOTAL
Michael,25
Frank,30

4 or more days overdue
NAME,TOTAL
Frank,30

5 or more days overdue
NAME,TOTAL
Frank,30

********end of report********************

My questions are:
1. Is there any way of doing this without using a subreport?
2. How do I create the counter that increments the 'days overdue' and get it to stop when the counter reaches the maximum value of DAYS_OVERDUE in the table (5 in this example) rather than counting on to infinity (and presumably beyond)?

Thanks for looking!
0
Question by:vistamed
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 6
• 6
• +1

LVL 18

Accepted Solution

vasto earned 200 total points
ID: 40336948
You can group by DAYS_OVERDUE. This will show all existing days I the query . If you have a loan overdue one day and another one overdue 3 days you will see 1 and 3 in the list and 2 will be missing. If you want to see all days you need to create another table , which contains all the days from 1 to the max number you want to see in the report and left join this table with your current data. You can go even further and define periods in the new table.  guess at one point you will decide to get loans overdue 1-3 days, 4-5 days 5-10 days and over 10 days. The new table will allow you to handle this too.
0

Author Comment

ID: 40337035
Thanks Vasto, but I'd prefer some way of doing it which is self-contained within the report- I would prefer not to have to make changes to the data source itself such as adding tables etc. I'm interested to see if it can be done :)
0

LVL 100

Expert Comment

ID: 40337069
A record can only be in one group so it will require subreports or an added to able to get FRANK to appear in all groups.

How many records will you typically have?

How many days do you to be able to handle?

DO you want to show up to 5 days? 10 days late? or is there an upper limit?

mlmcc
0

LVL 18

Expert Comment

ID: 40337099
You can create the table on the fly in a command or you can use an excel file. Let me know if this will be an acceptable approach.
0

Author Comment

ID: 40337102
Hi mlmcc,
I suspected I'd have to use a subreport alright. I was just hoping there'd be a way to avoid it :)
The example above is only a simplified analogy of what I'm working on. If I have to, I can settle on an upper limit of 100 days or so, but I'd prefer if the report were smart enough to look at the data it has to show (which may be only a subset of all the debtors) and only display up until the maximum 'Days overdue' that's been returned in the results.
For the sake of an easy life, say I do fix an upper limit of Y. Can you tell me how I'd have the report run the subreport  Y times, passing the counter value to the subreport each time (x=1, x=2, x=3, ...... x=(y-1), x=y)?

Thanks!
0

Author Comment

ID: 40337134
Hi Vasto, yes, creating the table in a command would be fine. I don't know how to automatically fill a table with incrementing values from SQL though.

Thanks!
0

LVL 18

Expert Comment

ID: 40337168
You can do something like this:

``````SELECT * FROM
(
select 1 as Days
UNION
select 2
...
UNION
select 30) as TempData tt
LEFT JOIN  <YourTable > as yt ON yt.DAYS_OVERDUE = tt.Days
``````

You can also define a different TempData table. For example with Min And Max number of days and change the ON clause to
ON yt.DAYS_OVERDUE BETWEEN tt.MinDays and tt.MaxDays
0

LVL 100

Assisted Solution

mlmcc earned 100 total points
ID: 40337216
Actually change the temp table vasto is building to

``````SELECT * FROM
(
select 1 as Days
UNION
select 2
...
UNION
select 30) as TempData tt
LEFT JOIN  <YourTable > as yt ON yt.DAYS_OVERDUE >= tt.Days
``````

Now you only need to group on Days Overdue then Name and show the data.

mlmcc
0

LVL 35

Expert Comment

ID: 40338896
vasto,

You can also define a different TempData table. For example with Min And Max number of days and change the ON clause to
ON yt.DAYS_OVERDUE BETWEEN tt.MinDays and tt.MaxDays
Assuming that you were talking about a table that only has one row with the range, then I don't think that will work, because each row in the original table that was within that range would only be included once.  Or am I missing something?

James
0

LVL 35

Expert Comment

ID: 40338932
You mentioned "SQL".  Assuming that you're using MS SQL, there's a much simpler/shorter way to build a table with a count.  You can use a While loop.  And, because it's a loop, you can easily change how many values you put in the table by changing the ending value for the loop.

Personally, I would use a table variable for this, and that's what I used in my sample code.  You could change it to a temp table if you preferred.

The code below creates a table variable and fills it with 1 - 30, and then outputs the resulting table.

``````DECLARE
@count smallint,
@max_days smallint

DECLARE @days_overdue TABLE
(
days smallint
)

SET @count = 1
SET @max_days = 30

WHILE @count <= @max_days
BEGIN
INSERT INTO @days_overdue
SELECT @count
SET @count = @count + 1
END

select * from @days_overdue
``````

To test that, if you open a connection to your db in a new CR report and then use "Add Command", you can enter that as the Command.  Save that and you should have the days column as an available field (the only field).  Put that in the detail section and you should get the list of values.

For your actual report, you'd use code like that to create the table variable, and then Join that table variable to your main table.

You can change the number of values by changing @max_days.  For example, something like:

SET @max_days = (SELECT MAX(yt.DAYS_OVERDUE) FROM your_table yt WHERE <conditions that select the desired data>)

FYI, if you use a CR Command and you need to use CR parameters to select the data, you need to create those in the Command editor window.  Then you can double-click on the parameter name to insert it into the query.

James

PS: I seem to recall seeing an even easier way to create a table with a list of values like that, but I can't remember any details.  It's not something that I've ever actually needed to do, but I seem to recall seeing something.
0

LVL 18

Expert Comment

ID: 40339419
I am talking about a table , with multiple rows defining periods like 1-5 days, 6-10, 10-30 etc.
0

LVL 35

Expert Comment

ID: 40339504
vasto,

OK.  Just checking.

James
0

Author Comment

ID: 40339581
Hi James,

My apologies - I thought I had mentioned in an earlier post that I'm pulling my data from an Oracle database. Sadly Oracle SQL doesn't like your MSSQL code. I will keep your answer for whenever I need to do a similar thing in MSSQL though! :)

Vasto and mlmcc, thanks for your comments; I haven't had a chance to get back to this report yet but I'll keep you posted.

Don't worry, everybody will get points aplenty cos I've got them to throw around apparently :)
0

LVL 35

Assisted Solution

James0628 earned 200 total points
ID: 40339792
I've never used Oracle, but I did some looking around and the page below has some techniques for creating a table like that:

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques

James
0

Author Comment

ID: 40341027
Hi James,

Now THAT is beautiful! I can use that method for returning exactly the right number of rows for my dataset, as below:

``````SELECT ROWNUM n
FROM ALL_OBJECTS
WHERE ROWNUM <=
(
SELECT MAX(DAYS_OVERDUE) FROM DEBT_TABLE
WHERE
<whatever criteria I want>
)
``````

Thank you so much for that link!
0

LVL 35

Expert Comment

ID: 40341221
You're welcome.  As I recall, the only "catch" to that method was that you needed to have enough objects in ALL_OBJECTS.  If you needed 500 rows, but only had 300 objects, you'd only get 300 rows.  But as long as you will always have enough objects, then, yeah, it does look like a good approach.

James
0

LVL 18

Expert Comment

ID: 40341961
@vistamed, you probably know best how the report should look like. However, consider consolidating the days. I guess there is no big difference if the overdue is 27 or 28 days ( except if you are using this to calculate the interest). You probably can define periods , which will reduce the number of groups and will make the reading easier. Here is an example

With periods:
1-3 days
User 1 -\$100
User 2 - \$150
------------------------
Total \$250

4-5 days
User 3 -\$10
User 4 - \$50
------------------------
Total \$60
-----------------------------------------------------------------------------------------------
Total \$310

The other option will be :

1days
User 1 -\$100
---------
Total \$100

2 days
---------
Total \$0

3 days
User 2 - \$150
---------
Total \$150

4 days
User 3 -\$10
---------
Total \$10

5 days
User 4 - \$50
---------
Total \$50
-----------------------------------------------------------------------------------------------
Total \$310

Imagine how this will look like  for 30 days.
0

Author Comment

ID: 40342064
Hi Vasto,

You are right, it looks like a cumbersome report. The truth is that the situation I described in my original question is a serious simplification of what I actually need to report on - I just cobbled the example together to make it easier to understand.
My actual report is looking at the assembly of a device on a production line. Imagine it was a car being built. I want my report to show me the full list of components which will have been added to the car by the time it reached Assembly Station 1, station 2, station 3..... station 100 and so on.

And the good news is that thanks to you and James and mlmcc, I've got my report working yaay!
Thank you for all your help. I wouldn't have done it alone in a pink fit. :)
0

Author Closing Comment

ID: 40342098
Thanks to all of you, I'm sorry I didn't realise I couldn't give you 500 points each so I had to split them.
Vasto, for suggesting the join to a table of sequential numbers: 200
James, for the sexy SQL command to produce the table: 200
mlmcc, for making me realise I could do a table join using <=  rather than = : 100
Sorry I can't give you more - you all deserve it!
0

LVL 18

Expert Comment

ID: 40342158
Great! Glad I (we) could help.
0

LVL 35

Expert Comment

ID: 40342790
What vasto said.  :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

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â€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Nobody understands Phishing better than an anti-spam company. Thatâ€™s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With companâ€¦
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll