Cross Tab Report need summary and detail

[Former Member]

Hi, I am using cross tab to generate an open sales report. the required dates are the columns and the report looks very good except for i need to summarize the qty late but keep the qty that is due in the future. because i have not been able to do this, my report is approx 60 pages long. if i only use the current date out to the last sale date the report is only 10 pages long. I beleive i need some sort of formula but i am clueless on how to do this. any help would be greatly appreciated.

[Former Member · Accepted solution]

Hi Jeff,

Can you give us a report template like how the current report looks and what is your expectation(how you want your report to be)

Thanks,

Sastry

[Former Member · Accepted solution]

Thank you for your interest Sastry. It would be nice to upload the file so you can see but since I cant, I will attempt to explain the report and my ideal expectations. We are a manufacturing company and each week we review the current status of our orders (both Late, Current and Future). The company works within weekly buckets meaning that if the order is due within the week, as long as it ships during the week thats ok (meaning, they don't care about specific days the orders are due). At the current time, we have orders that are late and once the order is late, we don't care about what week it was due only that the order is late. Having said that, I will now attempt to explain the report. The rows start with the part number, then the order number, customer and finally the customer PO number. The column is as follows: The first column is the Grand Total for all the dates that are set in the report. The subsequent columns are in weekly buckets from this week (11/2/08) out to March of 09. This date has been selected only because that's what will fit on one page in landscape mode. If we never had any late orders this report would be perfect for our needs, but since we have many late orders, I either need to open up the date fields out to sometime in 2007 which then makes the report extremely long and cumbersome or have a separate report that only shows the late orders (currently that's what I am doing but gets confusing bouncing between two reports. My idea which is why I posted this comment is this: I would like to keep the report as is reporting all current and future sales in weekly columns and add in a separate column that sums up all the late orders in one column and reports only just the number late (all dates to yesterday). I am somewhat new to using the cross tab reports and cant seem to make a formula that even comes close to my intention. Thank you for your interest. If you need to see the actual report I would be happy to email you a copy of it in PDF format or I can send you the actual Crystal Report file. Thank you again

Jeff

[Former Member · Accepted solution]

Dear Jeff

If I understand this, you want to see all order in the week buckets colums, and all late orders in one column

How about this....

Creat a formula which is:

if Order date<currentdate then currentdate-7 else totext(order date)

This will change all late orders to a date 7 days before today.

In the crosstab the colum by date, grouped by week. \

Conditionally format all dates (and information in the columns to show red for late orders and black if not late with the formula

if {@date}<currentdate then crred else crblack

Does this work?

Kevin

[Former Member · Accepted solution]

Thank you Kevin,

I believe this will work. After studying up on formulas further and trial and error, I actually remade the report without the cross-tab format and used several formulas to accomplish what I needed. Thank you again for your response.

Jeff