Crystal formulae

[teddysnow12]

Hi,

I have to create a table in crystal like,


| current | prior |change(cu-pr)
| | |
Employees | | |
Members | | |
average age | | |
%females | | |
%males | | |

I wrote a query for calculating the columns.
so i get the data for count of employees , count of memebers , average age and so on for given parameters.

Among the parameters , i have year(rptg_yyyymm)
depending in the year entered , i have to calculate all these.
current is the count of current employees of the year given
prior is also count of proir employees of 1year proir to given year.

so i need the formula for proir year.


and also any ideas for this report to be done.

 

[MCuthill]

TeddySnow,

Can you provide an example of the raw data used for this? What does the date field identify? A better explanation of your data would greatly help.

As a quick note using "[ tt ]" and "[ /tt ]" tags (without the spaces) is handy for lining up tables.

Example:
[tt]Column1 ... Column2 ... Column3
First ..... Middle .... Last
John ...... James ..... Smith[/tt]

Cheers!


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[teddysnow12]

rptg_yyyymm is teh one of the parameter dependign on which the counts of the employees and members are retrieved.

 

[teddysnow12]

for example if we enter 2009-10 for rptg_yyyymm
then the count of employees and memebers are retrived based on 2009-10.

 

[MCuthill]

TeddySnow,

Is all the information in one table? Perhaps an "Employee" table? Is there a record created each month for a person in this table? I had asked for sample data as it is hard to understand how you derive the summary (and how one would determine last year) originally posted without seeing what makes up these summaries.

I suppose what I am asking is what would differentiate a person in 2009-10 from the persons you would see in the 2008-10 list? I also assume the same person can exist in both subsets?

Would the data look like this?
[tt]Person ... Gender ... rptg_yyyymm
Mike ..... M ........ 2009-10
Mike ..... M ........ 2009-11
Sally .... F ........ 2009-11
Mike ..... M ........ 2009-12[/tt]
(basically one row for each month a person is "active". Mike being active 2009-10 through 2009-12 and Sally only an employee for 2009-11)

If you could please better explain your dataset, it would be greatly appreciated.

Thanks TeddySnow,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[teddysnow12]

current ------ prior------- change
-----------------------------------------------
employees
-----------------------------------------------
members
-----------------------------------------------
average age
-----------------------------------------------
females
-----------------------------------------------
males
-----------------------------------------------


select sum(mms.plcy_holdr_mnth_cnt) employees ,
to_date(mms.rptg_yyyymm,'yyyy-mm') ,
add_months(to_date(mms.rptg_yyyymm,'yyyy-mm'), -12) ,
count(mms.mbr_mnth_cnt) members ,
count(mms.mbr_mnth_cnt)-sum(mms.plcy_holdr_mnth_cnt) change,
round(sum(mms.as_of_age_yrs)/count(mms.mbr_mnth_cnt),2) average_age,
round(sum( decode (mb.dw_gender_cd,'F',1,'M',0))/count(mms.mbr_mnth_cnt),2) percent_females,
round(sum( decode (mb.dw_gender_cd,'M',1,'F',0))/count(mms.mbr_mnth_cnt) ,2) percent_males,
pd.dw_funding_type_cd,
PD.DW_BUS_SEG_CD ,
PD.DW_MKT_SEG_CD,
gd.ftn_nbr,
gd.ftn_grp_nme,
gd.mbr_grp_nbr
from mbr.mbr_mnth_fact mms , mbr.mbr_dim mb,MBR.PRDCT_DIM pd , mbr.grp_structr_dim gsd , cmrcl_wlns.grp_dim@HM_LINK gd
where mms.mbr_hist_seq_nbr = mb.mbr_hist_seq_nbr
and MMS.PRIMRY_MEDCL_PRDCT_CD_HSN = PD.PRDCT_CD_HIST_SEQ_NBR
and mb.actv_indc = PD.ACTV_INDC
and GSD.MBR_GRP_NBR = gd.mbr_grp_nbr
and gd.ftn_grp_nme = &v_name
and gd.ftn_nbr = &v_number
and MMS.RPTG_YYYYMM = &rptg_yyyymm
and pd.dw_funding_type_cd = &dw_funding_type_cd
and pd.dw_bus_seg_cd = &dw_bus_seg_cd
and gd.mbr_grp_nbr = &mbr_grp_nbr
group by mms.rptg_yyyymm,
PD.dw_funding_type_cd,
PD.DW_BUS_SEG_CD,
PD.DW_MKT_SEG_CD,
gd.ftn_grp_nme,
gd.ftn_nbr
gd.mbr_grp_nbr




this is the code written and there are list of parameters within the code based on which teh metrics are calculated.

what is need is how to calculate the formula for prior year in crystal.

this query returns the result for only one particular year given in parameter that gives teh data for current .
but I should write teh formula for prior also to get teh data related to proir year that is 1 year behind the year which is given in the parameter.

 

[teddysnow12]

I need to fill in the values as,

current employees
prior employees
change(cur-pro) employees

current members
proir members
change menbers

and so on..

these values get generated based on given parameters like rptg_yyyymm(year)
ftn_nbr_nme(customer name)

and so on


prior basically depends on rptg_yyyymm that is 1year behind

 

[lbass]

Mike is asking you to show how your current fields display if placed in the detail section of a report.

-LB