Solved

# Using a formula as the calculated field in a crosstab

Posted on 2006-06-26
1,160 Views
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.
0
Question by:Hers2keep
• 9
• 7
• 6

LVL 100

Expert Comment

ID: 16989742
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
0

Author Comment

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

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

ID: 17001016
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...
0

LVL 100

Expert Comment

ID: 17006841
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
0

Author Comment

ID: 17009445
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
0

LVL 100

Expert Comment

ID: 17055378
You could UNION the views.

Select * from vsInvSnapShotYest
UNION
Select * from vsInvSnapShot7

How similar do they need to be?  Whicbh fields?

mlmcc
0

Author Comment

ID: 17057969
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?
0

LVL 100

Expert Comment

ID: 17062932
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
0

Author Comment

ID: 17063696
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?
0

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

ID: 17087449
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.
0

LVL 17

Expert Comment

ID: 17151593
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
0

Author Comment

ID: 17153536
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?
0

LVL 17

Expert Comment

ID: 17153859
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

0

LVL 17

Expert Comment

ID: 17153909
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
0

Author Comment

ID: 17154975
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
0

LVL 17

Expert Comment

ID: 17155112
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

0

LVL 17

Expert Comment

ID: 17155120
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
0

Author Comment

ID: 17155154
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?
0

LVL 17

Accepted Solution

MIKE earned 500 total points
ID: 17157819
Well, more accurately...I would just REPLACE each of the data elements (columns) that are enclosed in brackets like this { }.

You need to make sure that YOUR DATABASE Data Elements are there instead of my SAMPLES.

Remember these are FORMULAS....not SQL COMMANDS....so you need to create the formulas WITHIN your FORMULA WORKSHOP.

Hope it makes sense..

MikeV
0

LVL 17

Expert Comment

ID: 17314596
Glad I could be of service.

MikeV
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.