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

Posted on 2006-03-22
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 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
    LVL 8

    Expert Comment

    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

    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.
    LVL 8

    Expert Comment

    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... :)
    LVL 17

    Author Comment

    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

    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.

    LVL 8

    Expert Comment


    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 100

    Assisted Solution

    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.

    LVL 8

    Accepted Solution

    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


    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..

    LVL 8

    Expert Comment

    so glad that you got it working :)
    LVL 100

    Expert Comment

    Glad i could help


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    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…
    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…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now