Hi all,

I have a question that has been bugging me for a couple of days now, and honestly I don't know how to fix this.

I have to create a report that shows the amount of money we collected over a certain period of time. This has to be split over the different actions that we take (sending out letters, making calls etc). However, the actions aren't being taken in a fixed order. It could happen that for 1 case, the order is Letter 1, Call 1, Call 2, Letter 2, and for the next case it is Letter 1, Call 1, Letter 2, Call 2.

The data is saved into 2 tables, 1 holding the payments, and 1 holding the different actions. However, not all actions are in the report, only the main actions.

I ended up making different views for the actions, to make sure there are no duplicates.

I know how to get the start date for every action. I created a formula like this for every action type in the report:
if not isnull({V_CON01.FECHA}) then CDateTime ( val(left({V_CON01.FECHA},4)),val(mid({V_CON01.FECHA},5,2)),val(mid({V_CON01.FECHA},7,2)),val(mid({V_CON01.FECHA},9,2)),val(mid({V_CON01.FECHA},11,2)),val(right({V_CON01.FECHA},2)))
(in the actions table, the date is set as a Char, that's why the conversion)

However, I am not sure how to get the next action date. This could serve as an end date for the different sections. I assume I will have to use some sort of loop, but I really suck at those [IMG]smileys/smiley5.gif" align="middle" />


The data should come out something like this in the end:

Month assigned:   Letter 1, Call 1, Call 2, Letter 2
September 2012    5K        2K      3K      1.5K
October 2012        4K        2.5K    2K      3K


I really hope someone can help me with this.

Thanks in advance.

Kind regards

Gerben

Edited by Gurbs - 24 Sep 2013 at 12:21am

Hi Sastry,

Yes, {V_CON01.FECHA} is the action date for the first action. Different actions have different views ({V_CALL1.FECHA}, {V_CALL2.FECHA} etc). So it only applies for a certain action.

The amount for field is a formula called {@Cobro}

Our database is in Spanish, so everyone here is used to use the Spanish terms :)

Just looking at the original post...

Are you trying to normalize the values so that the order is always the same? I'm a little confused about what you are trying to accomplish...or where the difficulty lies.

ok, I think I understand...
Typically the way that you would achieve this would be to use variables (shared or global) in the detail section, suppress the detail section and display the results in a group footer.

The reason to do this is that CR will not loop through the data. The best that it offers is the Next() and Previous() function, but these have the issue that they will cross group boundaries, so use with care.

The other alternative is to use a subreport, as a new report will loop through its data...which can cause a performance hit. The classic example is if your report has 100 lines of detail, and each detail line hits a subreport, your report will hit the database 101 times to complete the report. Start factoring in time for connections, network traffic, amount of data being sent across the wire...it can add up. That is not to say that subreports don't have there place and uses....

The another alternative that comes to mind, is if your report is being called from a application that you can modify, you can have the application get the data, then have the application modify the data to your liking, and finally pass the modified data to the report for display.

And the last alternative, is to write a stored procedure that again processes the data in such a way that all the is left for the report to do is mostly, just display it.

I realize that the last 2 alternatives may not be available to many, if not most, report writers, but I thought I would mention them.

HTH

I like shared variables (as they will cross boundaries to subreports while global won't...not that I use subreports too often)

variables tend to run in groups of 3 formulas: reset, increment, and display.

reset: typically in a group header
shared numbervar x := 0;
"" //will hide a 0 displayed on the report

increment: typically in details
shared nubmervar x;

if {table.field} = someCondition then
x:= x + something; //can really be anything you want, and you don't need to increment x, you could just assign
""//again, hides the the value or running total. Can be removed during report development as a way to 'see' what is happening

display: typically in group footer
shared numbervar x;


there are about 6 different types of variables, check help for them all. You can call your variable whatever you like, just keep the spelling the same and CR will access it correctly.

HTH