Link to home
Start Free TrialLog in
Avatar of Datakat
DatakatFlag for United States of America

asked on

Grouping by Weeks when some have no data

I have a report that uses a table with week beginning and ending dates, one record for each week, to group report by week.  I OUTER joined it and it works fine.  Also had to set the report options to convert database NULL values to default.  Now I get a record with 0's in it, which I want.  Have to do a calculation using a rolling 4 weeks total.  

Now I need to divide this report into departments.  When I add this extra group, I don't get a "week" record for each department.  Only get records that have data.  I do get records with 0's for each week, but only one per company, not one per department group. Any suggestions to create records for each department for each week, even if some weeks/department combinations have no data?
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

You don't really have any choices in the matter.  Access will only report on data, not on 'No data'
You have to create records for every group you wish to show in the report.


It sounds like you might need a table of departments and and a table of weeks which you first use to create a cross-joined query (Cartesian joined query if that means more to you) and so get a record for every week for every department.

You can then use this query to left join to your real data on department and week and so produce the full range of records you require for your report.
If you then do any date or department selection, you MUST do it using fields from the crossjoin query- not fields from your actual data; otherwise you will destroy the left join.
Sorry - just realised this a CR zone, not Access.

However the same solution applies.  In your database, or using say, Excel worksheets, you need to create the cross join using tables of depts and weeks.
I agree on above comment. unless you put a crosstab report, normal "grouped" reports will need some data for what needs to be displayed.
Avatar of Datakat

ASKER

My Command for record selection:
SELECT d.fmd_date,
 d.fmd_prft_ctr,
dd.fmdd_prodlnk,
dd.fmdd_gross,
 dd.fmdd_cost,
dd.fmdd_freight,
dd.fmdd_price,
dd.fmdd_serial,
inv_header.ivh_prodtcl,
inv_header.ivh_desc,
pc.prft_name,
dd.fmdd_frgt_inv,
name.an_name,
name.an_cust,
w.rsbw_wk_lnk,
w.rsbw_end_date,
YEAR({?Week Ending Date})-1 lyear
 FROM ssfactor.rs_b_period w, OUTER  
      (ssfactor.fm_delivery d,
      ssfactor.fm_delvr_d dd,
      ssfactor.sys_prft_ctr pc,
      ssfactor.si_invoice i,
      ssfactor.inv_header inv_header,
      ssfactor.ar_altname name)
 WHERE ((w.rsbw_start_date<=d.fmd_date)
AND (w.rsbw_end_date>=d.fmd_date))
AND ((d.fmd_movement=dd.fmdd_movement)
AND (d.fmd_invoice=dd.fmdd_invoice))
AND (d.fmd_prft_ctr=pc.prft_ctr)
AND (d.fmd_invoice=i.ih_nbr)
AND (dd.fmdd_prodlnk=inv_header.ivh_link)
AND (i.ih_cust=name.an_cust)
AND (d.fmd_prft_ctr=52)
AND YEAR( w.rsbw_end_date)
BETWEEN Year({?Week Ending Date})-1 and YEAR({?Week Ending Date})

I cannot use a cross-tab, as the format of the report is being requested a certain way, there are other things happening in the report after record selection.
Too many tables here for Cartesian query.  Good idea, but I don't think it will work out for Crystal Reports.  I do not have ability to create a procedure or database on the Informix server.  
Any other thoughts?
ASKER CERTIFIED SOLUTION
Avatar of Datakat
Datakat
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Darinwc
Darinwc

if you have a tbale with all the weeks and a table with all the departments but no joining index, the application will spit out a table with all the possible combinations of the two. Then you can link that to your data table with a left join and end up with a table with all the weeks and departments and all the data.

Alternatively if you add 'default records' for each week and department combination.. that is probably the easier solution.

week 1 - deparment 1 - profit: $100
week 3 - deparment 1 - profit: $200

add a ficticious record to your data with a $0 value to week 2 department 1

week 1 - deparment 1 - profit: $100
week 2 - deparment 1 - profit: $0
week 3 - deparment 1 - profit: $300
Can you please post the solution you used.
Avatar of Datakat

ASKER

I used my own solution.  Did three queries with UNION ALL to get the data.
Sorry- but I don't understand where you got the 'missing' data from so it would help all CR users if you posted the solution.
Avatar of Datakat

ASKER

Figured it out myself.  No-one posted an answer I could use.