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.
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MIKEConnect With a Mentor Software Solutions ConsultantCommented:
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
 
mlmccCommented:
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
 
Carla RomereDirector of Information TechnologyAuthor Commented:
The data is already restricted in the select expert. I've got only the two days selected that I want to compare.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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

mlmcc
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
mlmccCommented:
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
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
mlmccCommented:
You could UNION the views.

Select * from vsInvSnapShotYest
UNION
Select * from vsInvSnapShot7

How similar do they need to be?  Whicbh fields?

mlmcc
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
mlmccCommented:
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
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
mlmccCommented:
Not sure.  You can set the report to treat NULLs as zero.  I think it is in the report options

mlmcc
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
Carla RomereDirector of Information TechnologyAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
Glad I could be of service.

MikeV
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.