Reporting using multiple command objects 1

[RussellO]

Hello,

I created a Crystal 9 report with multiple command objects going against an Oracle 8.1.7 DB. The tables and columns are the same in each of the command objects, the difference is the date selection criteria for the 3 buckets I need, Prior YTD, Prior Year, and Current YTD. The data refreshes fine. I can tell because while I am in design mode, and have put the 3 buckets in the detail section (1 from each command object), I can click on each bucket and go to data base, browse data, and see the distinct values. The problem is in Preview mode, I can't see any data. If I remove the summary buckets from command objects 2 & 3 from the report, I can see the summary bucket from command object 1. If I remove the bucket from Command object 1 and put in data from command object 2, I can't see bucket 2.

Any suggestions? I have attached the SQL statements from one of the command objects if you are interested.

Also, when I edit my command objects and hit OK, I get a message that tells me that no server-side group-by should be performed. I really don't understand that because it does appear to be working. The full text is, "More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed."

Thanks in advance for any assistance you can give me.

SELECT
SHIPPING.SHPTO#,
SHIPPING.SHPTO_CUST_NAME,
SHIPPING.STREET_ADDRESS,
SHIPPING.SHPTO_CITY,
SHIPPING.SHPTO_STATE,
SHIPPING.SHPTO_POSTAL_CODE,
SHIPPING.CONTACT_NAME,
SHIPPING.CONTACT_PHONE,
SHIPPING.EMAIL_ADDRESS,
CURRENT_SALES_REGION.SALES_REGION_NAME,
CURRENT_CUSTOMER_TYPE.CUST_TYPE_NAME,
SHIPPING_TERRITORY.TERRITORY_NAME,
SALESPERSONCI.SALESPRSN_NAME,
SALESPERSONCO.SALESPRSN_NAME,
MATNR.MATNR_CLASS,
MATNR.SET_YN,
TO_NUMBER(TO_CHAR(INVOICED_SALES.INVOICE_DATE,'YYYY')),
sum(INVOICED_SALES.QUANTITY_SHIPPED),
sum(INVOICED_SALES.UC_EXT_NET_SELL_PRICE)
FROM
INVOICED_SALES,
SHIPPING,
MATNR,
SALES_TERRITORY SHIPPING_TERRITORY,
SALESPERSON SALESPERSONCI,
SALESPERSON SALESPERSONCO,
NO_CHARGE,
CUSTOMER_TYPE CURRENT_CUSTOMER_TYPE,
SALES_REGION CURRENT_SALES_REGION
WHERE
( MATNR.MATNR_KEY=INVOICED_SALES.MATNR_KEY )
AND ( INVOICED_SALES.NC_REASON_KEY=NO_CHARGE.NC_REASON_KEY )
AND ( SHIPPING.SHIPPING_KEY=INVOICED_SALES.SHIPPING_KEY )
AND ( SHIPPING.IN_SALESPRSN_KEY=SALESPERSONCI.SALESPRSN_KEY )
AND ( SHIPPING.OUT_SALESPRSN_KEY=SALESPERSONCO.SALESPRSN_KEY )
AND ( SHIPPING.SHPTO_TERRITORY_KEY=SHIPPING_TERRITORY.TERRITORY_KEY )
AND ( SHIPPING.CUSTOMER_TYPE_KEY=CURRENT_CUSTOMER_TYPE.CUSTOMER_TYPE_KEY )
AND ( SHIPPING.SALES_REGION_KEY=CURRENT_SALES_REGION.SALES_REGION_KEY )
AND (
SHIPPING.SHPTO# = {?SHIPTONUMBER}
AND INVOICED_SALES.INVOICE_DATE >= concat ('01-JAN-', to_char(sysdate,'yyyy'))
AND MATNR.MATNR# NOT IN ('373457', '8010', '8011', '8012', '999904', '999910', '999911', '999920', '999945', '999946')
AND NO_CHARGE.NC_CODE NOT IN ('MA', 'MC', 'MD', 'ME', 'MF', 'MO', 'NA', 'NB', 'NC', 'ND', 'NE', 'NF', 'NG', 'NH', 'NI', 'NJ', 'NK', 'NL', 'NN', 'NO', 'NP', 'NQ', 'NR', 'NS', 'NT', 'NU', 'NV', 'NW', 'NX', 'NY')
)
GROUP BY
SHIPPING.SHPTO#,
SHIPPING.SHPTO_CUST_NAME,
SHIPPING.STREET_ADDRESS,
SHIPPING.SHPTO_CITY,
SHIPPING.SHPTO_STATE,
SHIPPING.SHPTO_POSTAL_CODE,
SHIPPING.CONTACT_NAME,
SHIPPING.CONTACT_PHONE,
SHIPPING.EMAIL_ADDRESS,
CURRENT_SALES_REGION.SALES_REGION_NAME,
CURRENT_CUSTOMER_TYPE.CUST_TYPE_NAME,
SHIPPING_TERRITORY.TERRITORY_NAME,
SALESPERSONCI.SALESPRSN_NAME,
SALESPERSONCO.SALESPRSN_NAME,
MATNR.MATNR_CLASS,
MATNR.SET_YN,
TO_NUMBER(TO_CHAR(INVOICED_SALES.INVOICE_DATE,'YYYY'))

 

[vidru]

I would try to combine all 3 into one command object. You could ammend your WHERE to use the whole date range (last year through today), and return column aliases for each of the ranges by using CASE statements.

Been a while since I've used Oracle, so this will be more or less pseudocode:

SELECT SHIPPING.SHPTO#,
PrevYTDSales = SUM(CASE WHEN INVOICED_SALES.INVOICE_DATE BETWEEN '1/1/2003' AND DATEADD(year, -1, CurrentDate) THEN INVOICED_SALES.UC_EXT_NET_SELL_PRICE ELSE 0 END),
PrevYearSales = SUM(CASE WHEN INVOICED_SALES.INVOICE_DATE BETWEEN '1/1/2003' AND '12/31/2003' THEN INVOICED_SALES.UC_EXT_NET_SELL_PRICE ELSE 0 END),
YTDSales = SUM(CASE WHEN INVOICED_SALES.INVOICE_DATE >= concat ('01-JAN-', to_char(sysdate,'yyyy')) THEN INVOICED_SALES.UC_EXT_NET_SELL_PRICE ELSE 0 END)
FROM
Tables....
WHERE
Join conditions and INVOICED_SALES.INVOICE_DATE >= the first day of last year (or whatever), etc.

-dave

 

[RussellO]

Hi Dave,

Thank you for your reponse. I researched using case statements with Oracle SQL and found that case statements are not supported. The closest I could come would be the "decode" function and, in this case, it would be a very gnarly decode statement indeed. I expect I will end up using the Crystal syntax for my SQL so I can build the case statements.

I still don't understand why I can't bring the data in from my multiple command objects, since the command objects all returned the summarized data. Since the command objects return the three result sets, it may be the way I have the data joined. I will play with that again before I give up my efforts in that direction.

Again, thanks for your advice.

Russ