Crystal Reports: Working with a Maxtrix

For some reason, 4001 is showing when I have explicitly told it in the view now to show.

If the code that you posted is the view, then no, you did not exclude 4001.  If the account is 4001, you put ME_Actual_Operating_Reve in Sales, and if the account is not 4001, you put TRXAMNT in TransAmt.  So, you do include 4001, but you put the amount in a different field.

 As for trying to put the cross-tab in a detail section, I don't understand why you're trying to do that, but it wouldn't make any sense.  A cross-tab in a detail section would be produced for every record, but the whole point of a cross-tab is summaries.

 James

Good Morning:

OK.  Let me rephrase some things.  I don't want 4001 to show in the cross-tab.  How do I make that happen?

Also, I need my other questions answered, if possible.

Thank you!

TBSupport

Hello, Again:

Below is my revised view, where I removed 4001 as far as "[SALES]" is concerned.  

In this view I do say "CASE WHEN GL00105.ACTNUMBR_1 <> '4001' THEN ME97704.TRXAMNT END AS [TransAmt]".  So, I don't want 4001 in the cross-tab.  How do I make that happen?

Also, I need my other questions answered, if possible.

Thank you!

TBSupport

SELECT     ME97705.ME_Job_ID AS [Project], ME97705.ME_User_Defined AS [Editor], ME97705.DOCDATE AS [DocumentDate], ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company],
                      CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales], GL00105.ACTNUMBR_1 AS [Account],
                      CASE WHEN GL00105.ACTNUMBR_1 <> '4001' THEN ME97705.TRXAMNT END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN ME97708.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' THEN ME97708.ME_Job_Close_Date ELSE NULL
                      END AS [CloseDate]
FROM         ME97705 INNER JOIN
                      ME97708 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID INNER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX INNER JOIN
                      ME97702 ON ME97708.CUSTNMBR = ME97702.MEuserdefined3
/*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
SELECT     ME97704.ME_Job_ID AS [Project], ME97704.ME_User_Defined AS [Editor], ME97704.DOCDATE AS [DocumentDate], ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company],
                      CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales], GL00105.ACTNUMBR_1 AS [Account],
                      CASE WHEN GL00105.ACTNUMBR_1 <> '4001' THEN ME97704.TRXAMNT END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN ME97707.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' THEN ME97707.ME_Job_Close_Date ELSE NULL
                      END AS [Close Date]
FROM         ME97704 INNER JOIN
                      ME97707 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID INNER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX INNER JOIN
                      ME97702 ON ME97707.CUSTNMBR = ME97702.MEuserdefined3

Hi:

You can disregard my question on 4001 in the cross-tab.  I eliminated this account by choosing to suppress empty columns.

Again, I just need my other questions answered.  :)

Thanks!

TBSupport

Hello:

The main thing is that my Crystal report has to resemble the spreadsheet that I placed on this post.

Thanks!

TBSupport

You can't put a cross tab in the details section.  Crosstabs are expected to work on all the data but in the details section you have only 1 record.  What would be the value of a crosstab with only 1 row and column?

Also crosstabs work on SUMMARY data, in the detail section there is no context for the summary.

Are either of the attached versions close to what you want?

mlmcc
CARBON-COMMISSIONrev1.rpt
CARBON-COMMISSIONrev2.rpt

Hi mlmcc:

No, neither of the reports is what I'm after.  Thanks, for trying, though.

Attached is my latest report.  It's not correct, either.

If you look at the spreadsheet that I sent, earlier, you'll see that I want the cross-tab lined up with each sales amount from each project.

I hope that makes sense.  The only way of understanding is to look at the spreadsheet I sent earlier.

Thanks!

TBSupport
CARBON-COMMISSION.rpt

Is the number of categories fixed?

Are new categories added?

mlmcc

Hi mlmcc:

By "categories", I presume you mean the four-digit numbers at the top of the cross-tab report.

The number of categories will be fixed.  But, that's an issue that I will have to tackle, later.  My concern, right now, is to get the cross-tab report lined up with the other data in the report similar to how it is seen in the spreadsheet.  :)

TBSupport

DO you want that spreadsheet recreated or are you fine with seeing only the active Accounts for a particular project/product/company ?

Like this
Proj1     Prod2    Comp3     Sales Amt     1234   2345   3456


Proj2     Prod3    Comp1     Sales Amt     1234   3456  8765

mlmcc

Hi:

I just spoke with the client.  He was OK with the possibility of adding accounts, in the future.  So, I went with your idea of creating a formula for each account and amount.

Thanks, for the help!

TBSupport

FWIW, if accounts will be added later, you could use array variables.  As the report reads records, it would store the account numbers in one array, and accumulate a total for each account in the corresponding element of a second array.  Then formulas would output the contents of those arrays.  That would be more flexible than creating a separate formula for each account, and then having to add a new formula whenever a new account was added.

 Or, if you stick with the idea of having a separate formula for each account, you might want to add an "Other" formula for any accounts that aren't included in the first formulas.  That way, when a new account is added to the data, you can see that on the report (there will be something in the "Other" column), and the amounts in that account will still be included in the report.  Then when you add a new formula for the new account, change the "Other" formula to exclude that account.

 James

Hi James:

Is there any way that you can "layout" both scenarios to me, or do I need to start a separate "question" here in experts-exchange for this?

TBSupport

As accounts are added you will need to change the report to handle them

1.  Add new formulas to get the values
2.  Add new summaries to get the total
3.  Add columns to handle the display


The array method will work except that you can add columns to handle the new accounts dynamically
You could add extra columns to the report  in preparation for new accounts.

The other drawback on the array method is unless you build an array for the other fields you won't have a full account list until the end so the first rows will have only a few accounts listed.

mlmcc

First of all, I wanted to mention one limitation of the whole "manual cross-tab" idea.  You'll only have room for so many columns, depending on your page width.  If a real cross-tab is too wide for the page, CR automatically puts the remaining columns on another page.  You won't get that with a "manual cross-tab", so if you have too many accounts (columns), that could be a problem.  As for the rest ...

 The "Other" idea is pretty simple.  Say the report data currently includes accounts 5001, 5002 and 5003, so you've got 3 formulas like the following (one for each account):

if {Account} = "5001" then
  {TransAmt}


 The "Other" formula would just be something like:

if not ({Account} in [ "5001", "5002", "5003" ]) then
  {TransAmt}

 It only produces an amount when the account is not one of the accounts that are included in the main formulas.  When you add a formula for a new account (eg. 5004), you would also add that account number to the list in [ ] in the "Other" formula, so that it won't be included in that formula anymore .  You'd add a Project group summary for the "Other" formula to the report, same as for the account formulas.


 If you use arrays, you'd have to display the accounts in the Project group footer, because the variables are updated as the records are processed, so you won't have the totals until the group footer.

 Create a formula like the following (call it whatever you like) and put that formula in the report header, to declare the variables, _and_ in the Project group header, to reset the variables for each new Project.

WhilePrintingRecords;
Global StringVar Array accounts;
Global NumberVar Array amounts;
Global NumberVar account_count;

Redim accounts [ 10 ];
Redim amounts [ 10 ];

account_count := 0;

""

Select allOpen in new window

 The "" at the end is just so the formula doesn't produce any visible output on the report.  You could also suppress that field, or the section that the formula is in.

 Create a formula like the following and put it in the detail section:

WhilePrintingRecords;
Global StringVar Array accounts;
Global NumberVar Array amounts;
Global NumberVar account_count;

if account_count > 0 and {Account} in accounts then
// The account is already in the array, so add the amount to the corresponding total
(
  Local NumberVar i;
  for i := 1 to account_count do
    if {Account} = accounts [ i ] then
      exit for;
  amounts [ i ] := amounts [ i ] + {TransAmt}
)
else
// The account is not in the array, so add the account and amount to the arrays
(
  account_count := account_count + 1;
  if account_count > Ubound (accounts) then
  (
    Redim Preserve accounts [ account_count ];
    Redim Preserve amounts [ account_count ];
    ""
  );
  accounts [ account_count ] := {Account};
  amounts [ account_count ] := {TransAmt}
);
""

Select allOpen in new window

 Then, in the simplest case, you'd have separate formulas to output each element in each array.  For example, for the first account number:

WhilePrintingRecords;
Global StringVar Array accounts;
Global NumberVar account_count;

if account_count >= 1 then
  accounts [ 1 ]

Select allOpen in new window

 For the total for the first account:

WhilePrintingRecords;
Global NumberVar Array amounts;
Global NumberVar account_count;

if account_count >= 1 then
  amounts [ 1 ]

Select allOpen in new window

 For the second account:

WhilePrintingRecords;
Global StringVar Array accounts;
Global NumberVar account_count;

if account_count >= 2 then
  accounts [ 2 ]

Select allOpen in new window

 For the total for the second account:

WhilePrintingRecords;
Global NumberVar Array amounts;
Global NumberVar account_count;

if account_count >= 2 then
  amounts [ 2 ]

Select allOpen in new window

 And so on.  You'd create pairs of formulas for as many accounts as you thought you might have, and try to figure out how to fit them all on the report.  :-)  You could use suppression formulas to suppress the fields that you don't need.  For example, to suppress the fields (account and amount) for the third account if there were only 2 accounts:

WhilePrintingRecords;
Global NumberVar account_count;

account_count < 3

Select allOpen in new window

 Simple, eh?  :-)

 FWIW, you could also incorporate the "Other" idea here.  Say you create formulas to output up to 10 different accounts, but one project has 12 accounts.  You could create an "Other" formula that adds together any array elements after the 10th one, so you get one column that includes any "extra" accounts that aren't displayed by the formulas on the report.

 If you also want grand totals for these accounts (not just Project totals), you could add new arrays for the grand total accounts and amounts and update those in the detail formula, along with the Project arrays.

 Oh, and to add another wrinkle, if you're building a list of accounts for each project, then the account columns will be different for each Project, as shown in the last report that you posted, where you had a separate cross-tab for each Project.  Each cross-tab was different.  However, if you wanted every Project to include the same account columns, even if they didn't have transactions for all of those accounts, I think you could do that.  But you've probably got enough to chew on for the moment, so I won't go into that right now.  :-)  Let me know if you want more info.

 James

Hi James:

Please correct me, if I'm wrong.  But, it sounds like the "Other" approach would be the best way to go.  If I understand correctly, even with arrays, you're going to need to make an educated guess as to the number of accounts that you might eventually have in the report.  Am I right?

TBSupport

You are correct since you will need to setup columns with text boxes waiting for data.

The array  method might provide a little more flexibility if you don't preload the array with the existing accounts but rather pick them up as you go along.

mlmcc

So, really, there is no way of creating programming that would anticipate the number of columns is there?  

Even a co-worker of mine suggested using the following T-SQL PIVOT code that does the crosstab without having to hardcode the account number columns.  You would change the IN to be equal to a variable/parameter and put the view in a stored procedure.  

But, even so, there appears to be no way of having programming that would automatically enter a new account number.  Please, let me know if I'm wrong.  Also, if my friend is correct, please let me know of a way of modifying his code to allow for anticipation of new account numbers.  :)


SELECT *
FROM (
    SELECT
        year(DOCDATE) as [year],left(datename(month,DOCDATE),3)as [month],
        DOCAMNT as Amount
    FROM SOP10100
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
)AS P

If I understand correctly, even with arrays, you're going to need to make an educated guess as to the number of accounts that you might eventually have in the report.  Am I right?

Pretty much.  CR arrays can have up to 1000 elements, so, from that POV, arrays can probably handle as many accounts as you're ever likely to need.  The problem is how to display those accounts on the report.

 In the simplest case, you create a separate formula for each array element, but unless you're going to create 1000 formulas for each array (And how would you fit all of those on the report? :-), you have to decide on some smaller number.  But there are probably practical limits, like a single Project is never expected to have more than 20 accounts; or you only have 40 different accounts, total, that are associated with Projects; and you could make an educated guess on how many formulas you need based on those limits.  But it's still just a guess, and things change.

 FWIW, another option would be to use a formula that just concatenates all of the values in an array into one long string.  The advantage there would be that you don't need to create individual formulas (which also means that you don't have to decide how many formulas to create).  But you still have to put that formula (string) in a field on the report and give the field a certain amount of room (another educated guess), and then what happens if the string is too long to fit in that field (too many accounts)?  You can use the "Can Grow" option for the field, to make sure that the entire string will always be visible on the report, but it probably won't be pretty when the values wrap to another line.  Or you could break the string up into pieces, but that kind of brings you back to the original "educated guess" problem -- How many pieces do you need, and how long should they be?


 As for your pivot question, I've never used that feature in SQL, but since, as it stands, your data already has the accounts in separate rows, logically, I'm guessing that the idea of the pivot is to put the accounts in separate columns instead.  If so, that _really_ won't work for CR.  When a new account was added, the data would include a new column, which you'd then have to add to the report.  And if the number of accounts (columns) decreased, the report would get an error on any missing columns, because it would be trying to use fields that didn't exist.

 James