Solved

# Using a formula as the calculated field in a crosstab

Posted on 2006-06-26

I have a crosstab report based on an MSSQL2000 table. There is a snapshot of our current inventory appended to the table every morning at 6:00am. Currently, I have a couple of crosstabs created based on the product and the weight, which both work beautifully.

I've created a view that includes only yesterday's snapshot and last Sunday's snapshot information. Now, what I'd like to be able to do is a crosstab based on the combined table, which looks at location (columns), product (rows) and a value derived from a formula as the crosstab calculated field. I'll put the theoretic formula I'd like to use below. There could feasibly be weeks where there is a product one week, and the next there isn't a matching product. That should either show the positive or negative of that.

(({vwInvSnapshot.TotWt}*{vwInvShapshot.TotCostLb} where {vwInvSnapshot.InsDate} between CurrentDate()-2 and CurrentDate()-1) MINUS

(({vwInvSnapshot.TotWt}*{vwInvShapshot.TotCostLb} where {vwInvSnapshot.InsDate} between CurrentDate()-9 and CurrentDate()-8)

This report will eventually be automatically scheduled to run every Monday morning. What I want to happen is the immediately previous Sunday's inventory value minus last week's Sunday inventory value...to come up with simply an inventory value difference between this week's end and last week's end. Ultimately, if the current value is less than last week's value, it would give me a negative number which I will then use conditional formatting to turn red.

Can I do this from within Crystal Reports? I experimented some with creating two value field formulas, i.e. ValueYes and Value7 but I couldn't figure out how to limit the value's calculation by date. For instance, {TotWt}*{TotCostLb} WHERE {InsDate} Between CurrDate-2 and CurrDate-1 for VALUE1 and then to get VALUE7 (last week's) {TotWt}*{TotCostLb} WHERE {InsDate} Between CurrDate-8 and CurrDate-8 but I keep getting errors saying there are parentheses missing. If I could get these two values to correctly separate & multiply out as Value1 and Value7, it would be easy to calculated Value1-Value7.

I'm sure this is just as clear as mud so if it's not clear, I'll try to clarify.

Thanks.