"Dynamically Changing" Variable SORT for Manual Cross-tab?

Posted on 2006-03-22
Medium Priority
Last Modified: 2012-06-27

I'm using variables to pass summary counts down to Group Footers.

My groupings are:

>>>>>>>>>>FILE NUMBER

My summary at these levels looks like this:

                      NO.LIT.NEEDED      LIT.STARTED       LIT.COMPLETED
01/2005                    1                           1                             2
02/2005                    1                           1                             2
03/2005                    1                           1                             2
04/2005                    1                           1                             2
05/2005                    1                           1                             2

Client #: 1000           5                          5                              10

What I would like to do is to SUPPRESS the VERTICAL display of the counts and show them HORIZONTALLY at the CLIENT ID goup level. The main reason is for EXPORTING purposes to EXCEL.  Here is what I would like to achieve:

                          01/2005                    02/2005                    03/2005                04/2005               05/2005
CLIENT #        NLN   LS   LC              NLN   LS   LC                NLN   LS   LC           NLN   LS   LC       NLN   LS   LC                                                                          
1000                1      1     2                1     1      2                    1    1    2               1     1     2           1    1     2
2000             ETC...ETC..

I know that I can accomplish this by manually building a separate Variable that will take the MONTHLY summary counts and move them to a HORIZONTAL position at the CLIENT ID group summary level.

But my main concern is the ONGOING need to be creating NEW variables for EACH new month that is to be reported on. My secondary concern is the actual LAYOUT of the report.


Is there a way to us VARIABLES so that they are used "dynamically?"

An example would be if I created a variable for EACH month for EACH COUNT.  ie. one variable is "Jan05-NoLitNeeded Cnt", another is "Jan05-LitStart Cnt" and so on.

Is there a way to set the variables so that regardless of what REPORTING DATES I use...that the Variables would print in the correct alignment, instead of ME having to manually code the alignment? Perhaps just setting the FIRST COUNT column to JANXX?

Example: The above data layout example is hard coded to print the Jan05 totals, then the Feb05 totals etc. But if I run the report for Jan06-Mar06...my layout would be totally thrown off....not only that I would need to create variables for the 2006 months that I don't have created.

So, I'm hoping someone might have some thoughts on how to accomplish this. I'm thinking maybe I need to somehow create my VARIABLES by MONTH (JAN) and not take into account the YEAR. AND only run my report for a CALENDER year at a time???

Sorry if this sounds confusing, but your help is much appreciated.

Question by:MIKE
  • 5
  • 4
  • 2

Expert Comment

ID: 16259494
several thoughts to get started & get the dates/varialbes working first, and then we can look further at the formatting specifics...

some questions that will help us to assist you further:
Q1 = the various status of "Lit completed", "Lit started", etc - how are these determined? is this based on a singel status field? or is it based upon a date field (or absence of a date field), etc...
Q2 = if you want to go to columns by month (and yes, we can certainly vary the date ranges so that they "roll" from some starting point) - do you have a maximum number of columns constraint that we can live within? (e.g. 18 months? 24 months?) the reason that I ask is that using rolling columns will give you more formula flexibility, etc. but you need to create all of the maximum possible columns in advance (though we can easily make them disappear if form the report if there's no data) vs. a cross tab will automatically expand for the required number of columns, though a bit less flexible, especially if you're true intention is to export to Excel
Q3 = must all of this be done directly within Crystal? e.g. if you're only using Crystal to summarize & then export the data, you may want to think about using a SQL view (or the Microsoft query tool) whereby you could actually embed the query results & related parameters directly into an excel spreadsheet and by-pass an entire step...

Meanwhils, assuming we're going to proceed via Crystal:
1) the various status fields = are they actually the same field? if so, you can start by grouping on that field instead of the month to begin to reverse the columns/rows (my gues is that it's not, but that still wont't stop us - we'll just need to write a formul & group on that instead- perhaps you can provide furhter information so that we can help you more...)
1) I'm guessing that perhaps you shouldn't need to use "variables" - it looks like what you're actually doing is a conditional count? i.e. if certain criteria are met for each case, then a count of 1, otherwise a count of 0 - is this correct? and then you are summarizing this information at various levels (client/month, etc) - again correct?

if so, you have various choices for how to best accomplish this:
a) within Crystal = create "If then" formulas something like:
if {type} = "case completed" then 1 else 0 (we'll add the information about the dates a little farther down - let's first be sure that you see what we're doing here) what this will do is then display in the detail section (for each column) either 1's or zeros (Don't worry - we'll later suppress this detail... this is simply so that you can see how everything works!) now, for each formual filed, you would <right><click> on it, and insert a summary (group subtotals and/or grand totals, respectively...NOTE: be sure that you choose "sum", not count... we do understand that you're actually "counting" but we're accomplishing this via "summing" all of the ones...)
b) within SQL - can write a query to create the columns, etc...

2) now for the dates... brief overview, and then you can give us more to go on... yes, you can "start" with a month (e.g. Jan 2005) and then automatically create "rolling" dates based on that "base" date... what you will want to do is:
a) for example, if you want to create 12 columns for months "based" from the column 1 month, you would create 12 formulas something like:
column 1 month formula = date(year({?date parameter}),month({?date parameter}),1)
// you can also just use the date parameter itself, but converting the date parameter to the first day of the month insures that you will get consistent results even if the users select a different date - e.g. jan 15 ,2005 would still give you Jan 1, 2005 - is that what you want? (for the MONTH/YEAR only... we'll see how to use this in a moment

column 2 month formula = if month({@column 1 date formula}) = 12
then date(year({@column 1 date formula})+1,1,1)
else date(year({@column 1 date formula}), month({@column 1 date formula}))+1,1)
// this automatically will adjust from December to january, plus otherwise "roll"

column 3 month formula = if month({@column 2 date formula}) = 12
then date(year({@column 2 date formula})+1,1,1)
else date(year({@column 2 date formula}), month({@column 2 date formula}))+1,1)
and so on...

3) finally, once we understand how the dates work, and where we're headed with the "if then" formulas, we will bring it all together into formulas = 1 for each column...

colum 1 count formula =  if {type} = "case completed"  and year({case date} = year({@column 1 month fomula}) and month({case date} = month({@column 1 month formula}) then 1 else 0
// what this formual does is: 1) test the sace status criteria and 2) confirm that the month/year agree to your monthly column formula requirement... if so, it counts 1
since we've made the date columns "flexible" (all sourced from the relationship to the singld date parameter, the data will easliy "flow" for rolling date ranges...

is this basically what you're looking for? (and we can work on hiding columns that you don't use, if you wish...)

so, if you wnat to give us further sample details to work with, we'd be happy to help you get this done :)

LVL 17

Author Comment

ID: 16259738
Thank you for input:

I think it would be best to direct you to my previous thread on this. The obtaining of the counts for LIT COMPLETED, LIT STARTED etc..was it's own beast. I  hope I'm posting this correctly, but if you cannot find it let me know.


I'm reading through your questions and will reply asap.

Expert Comment

ID: 16261434
no problem... but some thoughts:

1) you should be able to accomplish the same thing without variables, since if I'm understanding the prior post correctly, you're confirming that certain, multiple conditions are met, and are only passing the variable values across to other formulas for purposes of summarizing them withing the various grouping levels, is this correct? (e.g. each result set stands on its own rather than being dependent upon a previous/next value, similar to a running total)...

eliminating the variables will cause you to have more individual formulas, but can be much easier to maintain and accomplish what you truly want in this case...
2) what I'm not clear about (from the other question) is why you need to have it as a sting label? (which, you would require a loop, etc to build the string array) is this purely for formatting? are you actually exporting to Excel (which will put things in the correct columns if you use some formatting tricks - more on that after we know what you need) or perhaps to something else?

for example, from mlmcc's suggestion of 03/10 to the other question, the following formual could be run without variables (and could incorporate the compound criteria of months, multiple filed values, etc...)
if (instr(LitStatus,"RC") > 0) then
   "No Lit Needed"
else if (instr(LitStatus), "  ") then

the problem with using variables in a report like this (as you've probably already discovered) is that yes, if you're going to use a global "scope" the yes you must create all of the necessary individual variables to accomodate every value you're seeking...

looking forward to hearing more... :)
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

LVL 17

Author Comment

ID: 16261592
In reply:

Q1 = above link

Q2 = I think that I need a previous CALENDER year plus Current YTD. (ie. For today, I would need to see 01/01/2005 - 03/31/2006. Going across the page would be ok...If I could somehow get a SUBTOTAL by year that would be good. I don't think I'm interested in a "rolling" 24 months...etc. I need a set LAST CALENDER YEAR and the CURRENT CALENDER YEAR to compare it to. The Comparison will more than likely be done in EXCEL, but I would at least like to have a printable clean version straight from Crystal XI.

Here is an example of what I would like to see:

CLIENTID LitTYPE      01/05     02/05     03/05     04/05     05/05     06/05     07/05     08/05     09/05     10/05     11/05     12/05     TTL2005  01/06   02/06   03/06
1000         NoLit         1             1           1             1           1           1           1             1            1            1          1           1              12        1          1         1
1000         LitStart      1             1            1            1           1           1           1             1            1            1          1           1              12        1          1         1
1000         LitComp     1             0            0            0           2           1           1             1            1            1          2           1              11        1          0         0
2000       etc...etc...


LVL 17

Author Comment

ID: 16261687
In response to your latest question #1, I think is YES. I'm only using variable to pass totals to group levels? If I can get away from doing that, then I may try, but can't see a way yet.

For question #2 above, I could try the formula from mlmcc again, but for some reason I don't think it was working for me. I had to use dylanyees advice on assigning a booleanvar of TRUE to the line of it contained the CODE and then..base on a set list of certain codes,....the TYPE OF LITIGATION could be assigned.

ie. if ACTCODE has TR, RR AND RC are all present and no PF in STATUS CODE, then this would be classified as NO LIT NEEDED.

TR, RR but no RC then this is LIT STARTED

I have to assign a TYPE OF LIT, based on a combination of ACTCODES AND STATUS CODES.

Do you still see someway of doing this without variables?

As it stands, I've used a combination of mlmcc and dylanyee's advice and created a pretty good summary report, but I'm needing to fine tune the export and my latest post here is basically what I'm trying to achieve now.


Expert Comment

ID: 16262990

Q#1 = above - I think I'll need to print out the prior question plus this one to be able to see more clearly what's up with your conditional formulas... maybe tonight ?

Q#2 = layout - no problem & yes, you could certainly have a 12 month column total, etc... (if what you actually want is 12 monthly columns + total = 13 columns across, and then Year by year below that, we could likely do that also (as an accountant, this is exactly the type of report that I create all the time) - the key will obviously be to be sure that we can get the correct underlying data first, and hence the need to return to Q#1...

I'll see if I can find a bit of free time tonight... :)
LVL 101

Assisted Solution

mlmcc earned 750 total points
ID: 16265371
How about this?

In the main report show the column headers in the page header and the row header in the detail section.

Add a subreport in the detail section to that uses multi-column format to produce the columns as required.
Link the subreport on the client ID to ensure only one client data is pulled.


Accepted Solution

PATKIRSCH earned 750 total points
ID: 16271169
spent some time looking at the other related question, and it would appear that the difficuly of your reporting requirement (whether it's presented as a sting, count, etc) is actually that you are looking to create a multi-tiered grouping, which normally does not present a problem, but in your data structure it does because there is no single "static" element within a record that can allow us to use it for furhter manipulation at various levels... rather, you data essentially works in reverse...

in other words, your higher level grouping (NO.LIT.NEEDED, LIT.STARTED, LIT.COMPLETED) is based upon the status across multiple records within the lower level grouping (FILE NUMBER) which requires that we first "aggregate" the records in the FILE NUMBER as to whether ther is at least one count of a record for each of various status types, and then use the results of that grouping to determine the Higher level grouping of STATUS (before we even start cross-tabulating by month & year). Unfortunately, this is the opposite of how Crystal (and/or most reporting tools for that matter) normally work, and thus the need for variables/running totals, etc...

so, it would seem that the obvious choices are likely to be:
1) see if there's a way to "push" the STATUS (NO. LIT NEEDED, etc) determination aggregate portion of the work for each FILE back into the query so that the report recordset can return a single record for each file that shows 1) status message (this is the hard part since you'd need to translate the logic currently being used in the report to the equivalent groups within sql, and then perform equivalent tests , 2) date (which I'm assuming is the date from the first record for each file? (so it would be the minimum date within the File group), 3) and other information you need
2) leave the report "as is" for formatting, and let us help you with the rolling dates
3) consider subreports, as per mlmcc's suggestion

so, please let us know what your thoughts are at this point so we can think about the next steps.
best wishes :) Pat
LVL 17

Author Comment

ID: 16307930

I went ahead and created the separate variables BY MONTH and manually created all of the formulas to accomplish, what turns out to be a manual crosstab.

I had to created variables for each month Jan05-Dec06. There ended up being about 60 formulas or so. Wasn't too bad as I could copy and past alot of them...and I also used WORD to EDIT/REPLACE variable names to speed things up.

So, I now have a report that I can export in the format needed.

Thanks for all of your help, especially from the other thread related to TR,RR,RC,PF..code assignments..etc..


Expert Comment

ID: 16314283
so glad that you got it working :)
LVL 101

Expert Comment

ID: 16317291
Glad i could help


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses

809 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