Cross Tab Help

[dnfrantum]

I have always had trouble with Cross Tabs and today is no different. I am trying to create a basic P&L with nested rolled natural accounts under title accounts, with summaries at both level and a grand total at the bottom by month. Please see below if this was confusing...

Jan 2009 Feb 2009 Mar 2009

Income (Title)
4111 (Natural) $1,2345.00 $1,5647.00 $1,56878.00
4123 (Natural) $ 25.00 $ 25.00 $ 25.00

Total Income $1,2370.00 $1,5672.00 $1,56873.00

I can't get the nesting/grouping and/or summary to work properly. Does someone have an example or a lead in the right direction?


Thanks in advance,
Donald

 

[lbass]

Please explain what the issue is. How is the display different than you would like it to be?

-LB

 

[dnfrantum]

I am attempting to write a comparative, month to month, Profit and Loss Statement. It would include Income (summarized at Title and Detail account level) with a Gross Income, Expenses (same as above) and then a summary of Income - Expenses for a Net Income. I haven't had much exposure to cross tabs and, while this shouldn't be a difficult task, I am getting lost in cross tabs.

I am currently attempting to do a manual cross tab, as it seems cross tabs may not exactly deliver what I need. I am currently on XI Developer. As an aside, do you know if 2008 has better cross tab functionality?



Thanks in advance,
Donald

 

[lbass]

You didn't really clarify in what way the crosstab wasn't working for you, but a manual crosstab will probably work as easily. What assistance do you need with that?

CR2008 does add functionality for crosstabs, but I'm not familiar with the features.

-LB

 

[dnfrantum]

To clarify, I have the following data:
Period = 2007-01, 2007-02, etc
Title Account = Income, Expenses, etc
Natural Account = 4001, 6001, etc
GL Account = 4001-01-02, 6001-01-02, etc
Debit
Credit
Journal Date

Parameters = StartDate, EndDate or StartPeriod, EndPeriod (either should work)
Formulas = debit - credit for total amount


I am trying to write a report in Crystal XI that use the information above to do a drillable cross tab report that summarizes data by Natural Account and Title Account, with subtotals on Income to equal Gross Profit, Expenses to equal Gross Expenses, and Income - Expenses to equal Net Profit.

I am running into issues at every approach. Currently, I have abandoned the Cross Tab Wizard because I am not that familiar with them and I can't seem to get them to group in the order that I need. I am making an attempt at a manual cross tab, but I am having issues formatting the period to increment by one on with each additional period in the range. I get the correct value returned, but instead of it being in the format of 2007-01, it returns the format $2007.00-$2. Here is the formula I used for that:

ToText(ToNumber(Mid({@FirstPeriod},1,4)) & '-' & (ToNumber(Mid({@FirstPeriod},6,2))+1))

{@FirstPeriod} from above returns the value of the parameter {?StartPeriod}

I feel that if I can get the headers to populate properly, I may have a shot at creating either groups of data or subreports to populate the detail. Because I want to make this a drillable report, I would welcome any suggestions on that front as well.

Please let me know if I can provide any more details.

Thanks in advance,
Donald

 

[lbass]

Why don't you simply use your period field for the column? You would create formulas like this:

if {table.period} = "2007-01" then
{table.amt}

Make one formula for each month. Then insert a group on your title field and the natural field. Place the above formula in the detail section and insert a sum on it at each group level. Then suppress the detail section.

You will be able to drilldown on the group sections, but it wouldn't be specific to the months, so you might have to use subreports for each month.

That should get you started.

-LB

 

[dnfrantum]

How would I use the formula above in conjunction with your formula from thread767-1560377 to reflect a sum of debit-credit? In the formula above, I have to have discrete value, but I am using a parameter and the formula from thread767-1560377 to get my column headers, e.g. it is dynamic. Would you have any advice on how I might handle this?

BTW, the formula from thread767-1560377 worked beautifully and gave me exactly what I wanted.

Thanks in advance,
Donald

 

[dnfrantum]

LB,
I used the above formula against the Period Field in the table to return the value in the same format as the column header, so no more help on this issue needed. I am still having a couple of evaluation problems. I entered two additional threads to try and get answers for them, but again, thanks for your help.

Thanks in advance,
Donald