Solved

Crystal Reports - Using a counter as a group header

Posted on 2014-09-22
21
177 Views
Last Modified: 2014-09-24
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
Comment
Question by:vistamed
  • 7
  • 6
  • 6
  • +1
21 Comments
 
LVL 18

Accepted Solution

by:
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

by:vistamed
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

by:mlmcc
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

by:vasto
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

by:vistamed
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

by:vistamed
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

by:vasto
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

Open in new window



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

by:mlmcc
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

Open in new window


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

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
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 34

Expert Comment

by:James0628
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

Open in new window


 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 18

Expert Comment

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

Expert Comment

by:James0628
ID: 40339504
vasto,

 OK.  Just checking.

 James
0
 

Author Comment

by:vistamed
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 34

Assisted Solution

by:James0628
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

by:vistamed
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>
)

Open in new window


Thank you so much for that link!
0
 
LVL 34

Expert Comment

by:James0628
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

by:vasto
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

by:vistamed
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

by:vistamed
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

by:vasto
ID: 40342158
Great! Glad I (we) could help.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40342790
What vasto said.  :-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

12 Experts available now in Live!

Get 1:1 Help Now