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

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

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.

https://www.experts-exchange.com/questions/21760347/CR-XI-Trying-to-count-using-stringvar-in-group-footer.html

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

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
   etc

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

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

Mike

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
TR, RR, RC in ACTCODE and PF in STATCODE, then this is LITIGATION COMPLETED.

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.

THANKS

Hello,

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

Experts:

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

Mike

so glad that you got it working :)

Glad i could help

mlmcc