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

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.

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}*{v

(({vwInvSnapshot.TotWt}*{v

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.

mlmcc

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...

mlmcc

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],[

vsInvSnapShot7 Fields:

[CurrBldg],[ProductCode],[

[CurrBldg],[ProductCode],[

How could I combine the two views to get rows of data similar to the following?

[CurrBldg],[ProductCode],[

Lowell, HDPE, Densified, 20000, 32000, .35, .06

Lowell South, LLDPE, Densified, 0, 22000, .234, .002

Select * from vsInvSnapShotYest

UNION

Select * from vsInvSnapShot7

How similar do they need to be? Whicbh fields?

mlmcc

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?

Try this

Select vsInvSnapShotYest.*, vsInvSnapShot7.* from vsInvSnapShotYest FULL OUTER JOIN vsInvSnapShot7

ON vsInvSnapShotYest.ProductC

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

mlmcc

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.

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

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)*TotWe

(CostLb+TranscostLb)*TotWe

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?

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

Thanks MikeV

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

MikeV

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

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}+{Sh

//@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_

ELSE

IF {Sheet1_.INSDATE} IN LastFullWeek

THEN ({Sheet1_.COSTLB}+{Sheet1_

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

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

M

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.

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