cross-tab summary fields

[itasueb]

Have 3 cross tabs in this reports
1st gives sales total by division and qtr for first year

ex division 1st qtr 2nd qtr etc.
year company1 2,200,355 1,955,000
2004 company2 1,300,000 1,456,665
etc.

2nd gives sales total by division and qtr for prior year

ex division 1st qtr 2nd qtr etc.
year company1 1,400,355 1,955,000
2003 company2 950,000 1,256,665
etc.

3rd give change in sales by division and qtr
ex division 1st qtr 2nd qtr etc.
company1 800,000 0
company2 350,000 200,000
etc.

4th is suppose to give you percentage of change
any suggestions????

my percents using a weighted average are way off

 

[lbass]

If you are able to use SQL expressions, you can create the percentage in the following way:

Create two SQL expressions:

[{%Yr2-Yr1}]:

(select sum(AKA.`SalesAmt`) from Table AKA where
AKA.`Division` = Table.`Division` and
{fn QUARTER(AKA.`SalesDate`)} = {fn QUARTER(Table.`SalesDate`)} and
{fn YEAR(AKA.`SalesDate`)} = 2004) -
(select sum(AKA.`SalesAmt`) from Table AKA where
AKA.`Division` = Table.`Division` and
{fn QUARTER(AKA.`SalesDate`)} = {fn QUARTER(Table.`SalesDate`)} and
{fn YEAR(AKA.`SalesDate`)} = 2003)

[{%Yr1}]:

(select sum(AKA.`SalesAmt`) from Table AKA where
AKA.`Division` = Table.`Division` and
{fn QUARTER(AKA.`SalesDate`)} = {fn QUARTER(Table.`SalesDate`)} and
{fn YEAR(AKA.`SalesDate`)} = 2003)

In each of these expressions, replace "Table" with your table name, and replace each field name (SalesAmt, Division, SalesDate) with your exact field name. Leave "AKA" as is, since it is an alias table name.

Then go to the formula editor and create a formula {@cellpercent}:

if {%Yr1} <> 0 then
{%Yr2-Yr1} % {Yr1}

Place {@cellpercent} in the crosstab as a summary field. Do not change the type of summary (to avoid crashing). After exiting the crosstab expert, right click on the field and change the summary to maximum. If you are using column totals and row totals, you will need to suppress both of these in the percent crosstab. To get row and column total percentages, you would create a separate crosstab for each. For the row total percentage, create two SQL expressions:

[{%rowtotal2003}]:

(select sum(AKA.`SalesAmt`) from Table AKA where
AKA.`Division` = Table.`Division` and
{fn YEAR(AKA.`SalesDate`)} = 2003)

[{%rowtotaldiff}]:

(select sum(AKA.`SalesAmt`) from Table AKA where
AKA.`Division` = Table.`Division` and
{fn YEAR(AKA.`SalesDate`)} = 2004) -
(select sum(AKA.`SalesAmt`) from Table AKA where
AKA.`Division` = Table.`Division` and
{fn YEAR(AKA.`SalesDate`)} = 2003)

Then create a formula {@rowtotalpercent}:

if {%rowtotal2003} <> 0 then
{%rowtotaldiff} % {rowtotal2003}

Again, use this as your summary, with {table.Division} as your row field. Change the summary to a maximum after exiting the crosstab expert.

For the column crosstab, you would use the quarter clauses in two SQL expressions, but eliminate the clauses referring to division.

For these two crosstabs, you would suppress the row and column labels and the grand total value, and also go to the customize style tab->format grid lines and uncheck "show grid lines". Then position these crosstabs on top of the original crosstab.

Finally, you would need to create a formula reflecting the grand total percent. Again you would create two SQL expressions, one for the difference, one for the previous year, and then eliminate both division and quarter clauses. You do not need to create a crosstab for this, but instead, simply place this in the grand total position on the original crosstab.

Right click on each of the values and format them by clicking on the % icon in the toolbar.

-LB

 

[itasueb]

Not TOO familiar with SQL expressions. I'll see how far I can get.
Thanks

 

[itasueb]

WHEW!!!

Okey, was able to get formulas in, created cross tab, put in the cellpercent formula and I got some results. Only problem is the percents are wrong. First percent for 1st company for 1st qtr should have been 16.80, this formula returned the value of 38.43. Second, third and fourth company all had the same vaules in all the qtrs. Not sure where I went wrong.

 

[lbass]

Please copy your SQL expression into the post so we can troubleshoot it. Can you verify that you changed the summary to "maximum"? Finally, if you have any record selection criteria, please post your formula here as well (not the show SQL query, please), since there may be elements of that which need to be built into the expression. Go to report->edit selection formula->record and copy that and paste it here.

-LB

 

[itasueb]

yr2-yr1:

(select sum(AKA.pyperdblnc) from crystal_glsumm_view_REV AKA where
AKA.pydbname = allglsumm.PYDBNAME and
(AKA.pyperiodid) = (allglsumm.pyperiodid) and
(AKA.pyyear1) = 2004)-
(select sum(AKA.pyperdblnc) from crystal_glsumm_view_REV AKA where
AKA.pydbname = allglsumm.PYDBNAME and
(AKA.pyperiodid) = (allglsumm.pyperiodid) and
(AKA.pyyear1) = 2003)

yr1:

(select sum(AKA.pyperdblnc) from crystal_glsumm_view_REV AKA where
AKA.pydbname = allglsumm.PYDBNAME and
(AKA.pyperiodid) = (allglsumm.pyperiodid) and
(AKA.pyyear1) = 2003)

Summary was changed to maximum.

Record selection is
{allglsumm.PYPERIODID} in 1 to 12
selects which qtrs are to be used

cross tab row is on @Division
column is on pyperiodid
summary is on Max of @cellpercent

Hope that helps
Thanks
Sue

 

[itasueb]

This is the formula for the Division field

if {allglsumm.PYDBNAME}='co1' and {allglsumm.ACTNUMBR_3}='20' then 'Company 1' else
if {allglsumm.PYDBNAME}='co2' and {allglsumm.ACTNUMBR_3}='21' then 'Company 2' else
if {allglsumm.PYDBNAME}='co3' and {allglsumm.ACTNUMBR_3}='25' then 'Company 3' else
if {allglsumm.PYDBNAME}='co4' then 'Company 4' else
if {allglsumm.PYDBNAME}='co5' then 'Company 5' else
if {allglsumm.PYDBNAME}='co6' then 'Company 6'

Also the report has to groupings
1st Division
2nd Pyperiodid

All cross tabs are in the Footer a-d

Also have a paramater field to pick cy and py

Let me know if you need anything else.

Thanks
Sue

 

[lbass]

There are several issues with your expression. First you are using multiple tables, so the expression isn't written correctly to accommodate that. Also, you can't substitute {allglsumm.PYDBNAME} for {@division}. You also cannot build in parameters into a SQL expression. Finally, you would have to build in the period selection condition which would be easy enough to do. However, I think your need to use {@division} as a row field makes your report probably too complex to use the SQL expression approach. Sorry.

-LB

 

[itasueb]

Thanks for your insight.

Is there any other way I could approach this?

Have the view that I use create a new database with the necessary fields???

Sue