asked on

Using a formula as the calculated field in a crosstab

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.

Mike McCracken

You cannot do what you want in a formula. You will need to restrict the data in the select expert then do the calculations

mlmcc

Carla Romere

ASKER

The data is already restricted in the select expert. I've got only the two days selected that I want to compare.

Mike McCracken

Can you show some sample data or records you get and the result you want?

mlmcc

Carla Romere

ASKER

http://cmr-ftp.com/samplepivot.xls

Here is a small sampling of data in excel that shows what I'm trying to accomplish. My current setup is that I take a "snapshot" of my inventory every morning at 6am. I have two views set up in SQL Server - one that pulls yesterday's inventory and the other one pulls from one week ago yesterday. I can create a Crosstab in Crystal for this week and one for last week using these views. The end result is I want to have ONE crosstab that shows only the DIFFERENCE in value of the inventory by using this week minus last week. It just doesn't seem that difficult, but it has me pulling my hair out. The problem has something to do with the fact that in the snapshot inventory, if the inventory on any line item is "zero" that line item doesn't show up in the snapshot at all. Maybe there's a way to force a view to count zero line items also?

If there is another way to set up the views, or whatever, I don't mind. But, I really need to be able to put this together in Crystal Reports because I can generate and send out reports from Crystal Reports unattended on a schedule.

Any light shed on this will be greatly appreciated...

Mike McCracken

How are you pulling the data? I don't think you can build what you want from 2 cross tabs but you may be able to combine the views so the information can be pulled.

mlmcc

Carla Romere

ASKER

I have two views set up on the SQL server - vwInvSnapShotYest (yesterday's inventory) and vwInvSnapShot7 (one week ago inventory).
I tried combining those into another view, but I don't remember if I tried it as a full outer join - including all records from both views.

vsInvSnapShotYest Fields:
[CurrBldg],[ProductCode],[Form],[TotWt],[CostLb],[TransCostLb]

vsInvSnapShot7 Fields:
[CurrBldg],[ProductCode],[Form],[TotWt],[CostLb],[TransCostLb]

[CurrBldg],[ProductCode],[Form] can be different combinations for each week.
How could I combine the two views to get rows of data similar to the following?

[CurrBldg],[ProductCode],[Form],[TotWtYest],[TotWt7],[CostLb],[TransCostLb]
Lowell, HDPE, Densified, 20000, 32000, .35, .06
Lowell South, LLDPE, Densified, 0, 22000, .234, .002

Mike McCracken

You could UNION the views.

Select * from vsInvSnapShotYest
UNION
Select * from vsInvSnapShot7

How similar do they need to be? Whicbh fields?

mlmcc

Carla Romere

ASKER

I actually tried that, but what I got in the crosstab from that one was something similar to this:

Alex Junction Lowell
HDPE Densified 10000 15000 200000 (yesterday)
HDPE Densified 12000 8000 267000 (today)

I couldn't for the life of me figure out how to get only ONE "HDPE Densified" and the values of +2000, -7000, +67000 in a crosstab. I tried setting up a formula to subtract yesterday's from today's, but then the value came out twice under the same column & row headings. Is there a way to get around that hurdle?

Mike McCracken

I assume you want them linked on the ProductCode and Form fields.

Try this
Select vsInvSnapShotYest.*, vsInvSnapShot7.* from vsInvSnapShotYest FULL OUTER JOIN vsInvSnapShot7
ON vsInvSnapShotYest.ProductCode = vsInvSnapShot7.ProductCode AND vsInvSnapShotYest.Form = vsInvSnapShot7.Form

You should now get 1 record for each and be able to do the math for the columns

mlmcc

Carla Romere

ASKER

What will happen when we had a product code/form combo one week, but didn't in the other? Will it count one week as a zero while keeping the other value & still do the calculation?

Mike McCracken

Not sure. You can set the report to treat NULLs as zero. I think it is in the report options

mlmcc

Carla Romere

ASKER

Well, that didn't work either. I've tried about every combination of views set up in sql, as well as joins set up in Crystal Reports. The only way I've been able to get the desired results was to import yesterdays data into sheet1 of a spreadsheet, last weeks' data into sheet2 of the same spreadsheet and on sheet3 manually listed each product code/form combo and used multi-criterion sumproducts to subtract last week's from this week.

I would still love to get this set up in Crystal Reports automatically. But the problem is that the data isn't consistent from last week to this week. We may have some combos last week that we don't have this week, and vice versa. i.e....

productcode form lastweek thisweek difference (thisweek-lstweek)
hdpe scrap <null> 2000 2000
ldpe repro 184000 62000 -122000
lldpe dens. 20000 32000 12000
ldpe hydro 15000 <null> -15000

I need all combos that exist in EITHER week to be included, but to only be included once - every combination I've tried I end up with the same combos twice - one for last week and once for this week.

Marcus Aurelius

Why do you need to use a VIEW? Why can't you link the TABLES and let Crystal handle the rest? I don't understand why you are limiting your data with a VIEW?

It seems like you are summarizing your data FIRST in the VIEW....and then expecting Crystal to summarize it again?

Can you have Crystal link the actual TABLES then pull the detail...and then....summarize the data...for this week or last week...or whatever week.....by using formulas....and Crosstab?

MikeV

Carla Romere

ASKER

Well I don't have a problem using the tables. The only reason I was using a view is I was tring to limit the dataset by limiting to just the two weeks I need. So assuming I switch to using the table itself...how do I call just the two weeks' data?

Field names in the table that I need are:
CurrBldg, ProductCode, Form, TotWt, CostLb, TransCostLb, InsDate

Columns would be CurrBldg
Rows would be ProductCode and Form
CrossTab summary needs to be a formula like this:

(CostLb+TranscostLb)*TotWeight where InsDate between Today and Tomorrow MINUS
(CostLb+TranscostLb)*TotWeight where InsDate between Today-7 and Tomorrow-7

Each week the ProductCode/Form combinations vary...i.e. last week we may have had HDPE/Repro but this week that combination is null, and vice versa. What I want is the amount of change (+/-) for every ProductCode/Form combination that appears in either last or this week. How can I calculate the formula to use as the crosstab summary?

Marcus Aurelius

Please give me a SAMPLE of 3 or so rows of DATA from your TABLE for these columns:

CurrBldg, ProductCode, Form, TotWt, CostLb, TransCostLb, InsDate

Thanks MikeV

Marcus Aurelius

What you need to do is get ALL of your data into the DETAILS...then using that data....begin planning your strategy to summarize that data within Crosstabs. Sometimes...regular Crosstabs are not able to handle certain types of summaries, but from what I've seen in your post so far, I don't see using a regular crosstab to summarize as being a problem.

Once I get the sample data, I should be able to fix you up pretty quick.

MikeV

Carla Romere

ASKER

http://cmr-ftp.com/samplepivot.xls

Here is a sample of what I'm trying to accomplish.
Raw data would look like this:

Junction, HDPE, Densified, 234545, 07-17-2006 8:00:00 am
Lowell, HDPE, Pulverized, 43245, 07-17-2006 8:00:00 am
Junction, HDPE, Densified, 432677, 07-10-2006 8:00:00 am
Lowell, HDPE, Pulverized, 234267, 07-10-2006 8:00:00 am
Junction, HDPE, Pulverized, 42678, 07-17-2006 8:00:00 am
Junction, HDPE, Pulverized, 31567, 07-10-2006 8:00:00 am
Porter, LDPE, Hydropulp Scrap, 1265478, 07-17-2006 8:00:00 am
Porter, LDPE, Hdropulp Scrap, 0, 07-10-2006 8:00:00 am (zero values don't show up at all, but I need to assume 0 values if the line item isn't there)

So with this data I'd want:

=====================================================
Sample of formula I want to use...
Junction Lowell Porter
HDPE Densified 234545-432677 0 0
HDPE Pulverized 42678-31567 43245-234267 0
LDPE Hydropulp Scrap 0 0 1265478-0
=====================================================
END RESULT - AMOUNT OF CHANGE
Junction Lowell Porter
HDPE Densified -198132 0 0
HDPE Pulverized -11111 -191022 0
LDPE Hydropulp Scrap 0 0 1265478

Marcus Aurelius

Right, I've build a sample in CRXI and it works with the exception of the FINAL variance.

What I did was...

First ADD the columns "CurrBldg, ProductCode, Form, TotWt, CostLb, TransCostLb, InsDate" into the DETAILS section.

Then, create these formulas for use in crosstab:

//@CalcAmt
({Sheet1_.TRANSCOSTLB}+{Sheet1_.COSTLB})*{Sheet1_.TOTWT}

//@Weeks
IF {Sheet1_.INSDATE} IN WeekToDateFromSun
THEN "THIS WEEK"
ELSE
IF {Sheet1_.INSDATE} IN LastFullWeek
THEN "LAST WEEK"
ELSE ""

//@Variance
IF {Sheet1_.INSDATE} IN WeekToDateFromSun
THEN ({Sheet1_.COSTLB}+{Sheet1_.TRANSCOSTLB})*{Sheet1_.TOTWT}
ELSE
IF {Sheet1_.INSDATE} IN LastFullWeek
THEN ({Sheet1_.COSTLB}+{Sheet1_.TRANSCOSTLB})*{Sheet1_.TOTWT} *-1
ELSE 0

Then in CREATE a CROSSTAB as follows:

COLUMN = @THISWEEK then CURRBLDG
on @THISWEEK change sort to SPECIFIED ORDER and set to THISWEEK and LASTWEEK, omit OTHERS

ROWS = PRODUCTCODE then FORM

SUMMARIZED FIELD = SUM of @Variance

I'm tweaking this format...but feel free to try it out..and tell me if it is close to what you want.

This will provide LAST WEEK'S TOTALS and THIS WEEKS TOTALS...and give a variace as the GRAND TOTAL

Hope it helps.

MikeV

Marcus Aurelius

The exception ..that I'm tweaking is the FINAL VARIANCE is NOT separating BY BLDG.

I'll let you know if I get it to work.

M

Carla Romere

ASKER

Okay - the live data is in an sql server and connecting through odbc. So once I connect to the odbc db, then i'd omit the Sheet1 reference, correct?

ASKER CERTIFIED SOLUTION

Marcus Aurelius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

Glad I could be of service.

MikeV