Sub-report values in Crosstabs

[Former Member]

I hope that there is an easy answer for this.

I have report that uses a cross tab to summarize quantity shipped from various locations by item. I need to also summarize the quantity ordered for each item. I have created a summary report that tallies the orders up and passes back a totalamount ordered for each item. I have used shared variable to pass this information from the subreport. I now need to place this item into the row listing for the crosstab, possible?

I am working with Crystal Report XI and running on MS SQL server as a backend DB.

Thanks in advance for the help.

[Former Member · Accepted solution]

Hi Carl,

Thanks for the great information and the SQL to solve the problem. My issue is how do I get Crystal to generate this SQL. I do have have the ability to create a view on the Database. I am looking at SQL expression, but I'm not completely sure how that would be used (more research is needed). If you could point me in the right direction, I would be grateful.

Cheers,

Wager

[Former Member · Accepted solution]

In the database expert, under the database connections, there is an option to Add Command. This allows you to enter an SQL command (full stored-procedure syntax of your database) that the report can be based off of (instead of selecting tables or stored procedures already in the database). Parameters can be added to the command so you can push as much of the selection, aggregation, sorting, etc. to the database as possible, which will result in much faster run times.

HTH,

Carl

[Former Member]

I'm not sure why you're using a subreport... Have the data return location, item, qty ordered, qty shipped, which in SQL might look something like this:

select isnull(od.location, sd.location) as location,
  isnull(od.item, sd.item) as item,
  isnull(od.order_qty, 0) as order_qty,
  isnull(sd.ship_qty, 0) as ship_qty
from (
  select location, item, sum(order_qty) as order_qty
  from order_detail
  where order_date between {?Start Date} and {?End Date}
) od
full outer join (
  select location, item, sum(ship_qty) as ship_qty
  from ship_detail
  where ship_date  between {?Start Date} and {?End Date}
) sd
on od.location = sd.location 
and od.item = sd.item 

Then use this as the basis of the crosstab.

HTH,

Carl