The subject I chose may not be descriptive, so the best I can do to describe my objective is that I want to look across multiple records belonging to an individual and display them all in the group header section.

I have a SQL query (view), that contains a record for lets say each student, his LEVEL in the class, a test number and the date test was taken. Levels are like a promotion that is affixed to a date also.

The Level file:

TomSmith          Level1          01/15/2013
TomSmith          Level2          02/15/2013
TomSmith          Level3          03/15/2013

the test file

TomSmith                   test1     02/01/2013 
TomSmith                   test2     03/01/2013
TomSmith                   test3     04/01/2013


the query view SQL combined file (done in Crystal matched on name)

TomSmith test1 02/01/2013  Level1 01/15/2013
TomSmith test1 02/01/2013  Level2 02/15/2013
TomSmith test1 02/01/2013  Level3 03/15/2013
TomSmith test2 03/01/2013  Level1 01/15/2013
TomSmith test2 03/01/2013  Level2 02/15/2013
TomSmith test2 03/01/2013  Level3 03/15/2013
TomSmith test3 04/01/2013  Level1 01/15/2013
TomSmith test3 04/01/2013  Level2 02/15/2013
TomSmith test3 04/01/2013  Level3 03/15/2013



I  am not allowed (because of missing data and unlimited # of tests, and because I'm not in control of the view nor is anyone willing to take requests to change it)to drop ahead of time these all into 1 record or view with predetermined fields for test1 test2 test3.

So I am displaying
Tom Smith  in Group header
detail a = test1 2/1/13  Level 1 01/15/2013
detail a = test2 3/1/13  Level 2 02/15/2013
detail a=   test3 4/1/13 Level 3 03/15/2013
detail suppress formula eliminates test dates that do not cross chronilogically with level change dates. (the blue ones above are suppressed)

My situation or objective is to print the 3 level promotions(purple ones) next to Tom Smith's name at the group header level, but when I drag "level date" into the group header, that date becomes FIXED to the first printed detail record's level date. That means all 3 groupheader dates (level dates I'm trying to fill), all say January 15th 2013.

How can I get all 3 squeezed into some type of group header.

How to use shared variables?
each person may or may not have 3 levels.
I thought I could write 3 formulas for each level's date

Shared DateTimeVar levDate1;
if {QueryCombine.Level}=1 then
 levDate1:={QueryCombine.DateBegin};
//then call it out so it will be displayed, when you drag formula to footer
levDate1;

repeat for each level, using same DateBegin on the right side of assignment.
Now I've loaded a date into a variable.
Now the problem is the 2nd person's (2nd group) does not have a level 2 record, so the report prints person 1's level 2 date. I know there is a way to clear out a variable, but changing a date to a blank is a elaborate project in Crystal because you can't "make it null"

If someone answers the problem of dates not being cleared, I can move onto my next question. I may end up putting the 3 dates in a footnote, then I won't have to deal with more groupheader programming logic elaboration.





Edited by mrtj - 19 Apr 2013 at 9:11am

Also, at the beginning of each person you have to have a formula that will reset all of the variables to blank.  Assuming that you change your existing formula so that it will return a string:

I am moving onto the next item to report on.
I have some "tests" that take more than 1 date to complete,
but I only want to count the test as one item. I can do this with "distinct count", this is working fine, when I look at say jan 1- thru mar 31.
But if a test was split between Feb and March, then it gets counted in both months when you add the subtotals separately.

Ie test1 in jan
test2 in feb
test2 in mar
test3 in mar.
The quarter total is 3, but the monthly total is
jan 1
feb 1
march 2
(totals to 4)

I see the distinct count is doing what its supposed to do. Is the only work around to write some extra logic that in March ad "(not count test2)", then add that to the formula?

When this happens, I plan to print an asterisk in the group footnote section, and leave it count as 2 for march.
However, the formula for this footnote is not working on the second student. To test this formula, I temporarily inserted it into the header section (which is suppressed normally), and it works there.

shared stringvar asterisk;
if 
{@ph_txt_num}>1 and ({vw_Start_Date}="03/13/2013" or
{vw_Start_Date}="03/20/2013" or
{vw_Start_Date}="03/27/2013") then asterisk :="*"

then in header i also clear it out
shared stringvar asterisk:=""

One student who has a 3/13/2013 date prints the asterisk, the second does not. I have opened the data in excel to ensure no trailing spaces, and have added the trim function around this string date field but still it does not print * . The asterisk also will print in the detail section when I unsuppress detail and insert asterisk formula there.