Mock Cross Tab - Display Help 1

[govmule]

Hello All,

Using Crystal Reports 8.5/Crystal Enterprise 8, hitting a MSSQL 2005 Database. Here are the details:

Mock Cross Tab, grouped by different performance measures down the left side and displaying all 12 months across the top - looks like this:

Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
Measure 1 6 5 7 3 4 2 5 8 3 5 3 2

Measure 2 5 3

Measure 3 5


As you can see, some measure happen monthly, some happen quarterly and some happen bi-annualy. The problem is, some also happen annualy, and I need the measure to show up on the list, even though there is no data in the selection query yet. Is this possible?

The report is grouped by the different measures and I'm summing the data in individual formula's to get the results. There is also a parameter for which year to run the report in. It's referenced in the selection formula.
__________________________________________________________________
//Record Selection Formula
(If {?Reporting Period} = "2009/2010" then
{Measure_Data.MeasureDate} in Date (2009,07,01) to Date (2010,06,30)
else
If {?Reporting Period} = "2010/2011" then
{Measure_Data.MeasureDate} in Date (2010,07,01) to Date (2011,06,30) )
and
{Measures.ItemID} in [1000, 1006, 1010, 1011, 1037, 1038, 1039,
1040, 1041, 1042, 1043, 1044, 1045, 1046, 1047, 1048, 1049, 1050]
and {Measure_Data.Deleted} <> 1
and {Measures.ExportToActuate} = 1
and not isnull({RANGE_COMPUTATIONS_6_GET_RESULTS.MeasureValue})
__________________________________________________________________
//SQL Query
SELECT DISTINCT
Measures."ItemID", Measures."ExportToActuate", Measures."MeasureABR", Measures."Frequency", Measures."Target", Measures."Green", Measures."Yellow", Measures."Red", Measures."CalcType", Measures."CalcDynamicTargetVarianceValue", Measures."CalcCommonGreenRangeValue", Measures."CalcCommonRedRangeValue",
Locations."ItemID",
Measure_Data."MeasureDate", Measure_Data."Deleted",
RANGE_COMPUTATIONS_6_GET_RESULTS."MeasureValue", RANGE_COMPUTATIONS_6_GET_RESULTS."RangeResult", RANGE_COMPUTATIONS_6_GET_RESULTS."RangeResultABR"
FROM
{ oj (("Actuater"."dbo"."Measures" Measures LEFT OUTER JOIN "Actuater"."dbo"."Measure_Data" Measure_Data ON
Measures."ItemID" = Measure_Data."MeasureID")
LEFT OUTER JOIN "Actuater"."dbo"."Locations" Locations ON
Measures."Location" = Locations."ItemID")
LEFT OUTER JOIN "Actuater"."dbo"."RANGE_COMPUTATIONS_6_GET_RESULTS" RANGE_COMPUTATIONS_6_GET_RESULTS ON
Measure_Data."ItemID" = RANGE_COMPUTATIONS_6_GET_RESULTS."DataID"}
WHERE
(Measures."ItemID" = 1050 OR
Measures."ItemID" = 1049 OR
Measures."ItemID" = 1048 OR
Measures."ItemID" = 1047 OR
Measures."ItemID" = 1046 OR
Measures."ItemID" = 1045 OR
Measures."ItemID" = 1044 OR
Measures."ItemID" = 1043 OR
Measures."ItemID" = 1042 OR
Measures."ItemID" = 1041 OR
Measures."ItemID" = 1040 OR
Measures."ItemID" = 1039 OR
Measures."ItemID" = 1038 OR
Measures."ItemID" = 1037 OR
Measures."ItemID" = 1011 OR
Measures."ItemID" = 1010 OR
Measures."ItemID" = 1006 OR
Measures."ItemID" = 1000) AND
Measure_Data."Deleted" <> 1 AND
Measures."ExportToActuate" = 1 AND
RANGE_COMPUTATIONS_6_GET_RESULTS."MeasureValue" IS NOT NULL
__________________________________________________________________

Am I barking up the wrong tree, or is there a way to accomplish this?

Thank you in advance for reading this long post - Peace

 

[lbass]

You can't select on fields from right hand tables and still maintain your left joins, so try eliminating the lines:

Measure_Data."Deleted" <> 1 AND
RANGE_COMPUTATIONS_6_GET_RESULTS."MeasureValue" IS NOT NULL

...from your selection formula, and then all measures should appear, assuming your group is on a fields from Measures. You can then eliminate deleted data in your conditional formulas, e.g.,

if {Measure_Data.Deleted} <> 1 and
{table.date} in ...//etc. then...

-LB

 

[govmule]

Yep - I was reading one of your responses to a different post and gathering the same thing. I'm adjusting the order my tables are linking in and seeing if I can get rid of those statements too.

I'll let you know how it works out - and again, as always, thank you!

 

[lbass]

Your linking looks correct to me (although I might be missing something)--you should just go into report->selection criteria->record and remove those two criteria.

-LB