Recent content by wanzek

Thank you for the suggestion. I tried that on one of the fields with this formula: If IsNull({HRRM.udJobLocation}) or {HRRM.udJobLocation} = "" then '999' else {HRRM.udJobLocation} I think updated my sort formula to have: if {?Sort}='N' then {udDriverDatabase.FullName} else if...

I have a very strange thing occurring on my report. I have a parameter formula that is supposed to allow you to choose what you want to sort by: if {?Sort}='N' then {udDriverDatabase.FullName} else if {?Sort}='D' then {udDriverDatabase.DriverStatus} else if...

Which hid the values but now I have a large white space. Is there a way around the large gap?

You are correct. I was putting it in the surpress. I put this formula in the Display string.

I didn't get an error on the first formula but when I try the second I get: A boolean is required here.

Yes, it seems to be working correctly and it is much faster than it was. As for the top N I don't even see that as an option in the cross tab. I see Nth Largest, Nth Smallest and Nth most frequent.

What is a vertical display by week? The problem with a manual crosstab is I need it to be able to run for the life of a job and there isn't a maximum number of weeks that I could determine.

I was able to get this working but I am still unsure how to handle values when there are ties. You mentioned you might have a solution to that.

That didn't work but I switched it back to the left and it did: SELECT "JCCD"."Phase", "JCCD"."ActualHours", "JCCD"."ActualDate", "JCCD"."Employee", "JCCD"."CostType", "JCCD"."Job", "JCCD"."JCCo", ( select sum(a."ActualHours") from "JCCD" a where a."Employee" ="JCCD"."Employee" and a."Phase"...

I received an error: Failed to retrieve data from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Also, something I was wondering if what happens if something ties for first place as the max? How does it pick the one it shows? Is there a way to get all of the tops values?

( select sum("ActualHours") from "JCCD" a where a.Employee ="JCCD"."Employee" and a.Phase = Left("JCCD"."Phase",2 ) and {fn WEEK(a.ActualDate)}={fn WEEK("JCCD"."ActualDate")} and {fn YEAR(a.ActualDate)}={fn YEAR("JCCD"."ActualDate")} )

I also forgot to mention that I am using Crystal Reports 2016

Here is the SQL Query: SELECT "HQCO"."Name", "JCCD"."JCCo", "JCCD"."Job", "JCCD"."PhaseGroup", "JCCD"."Phase", "JCCD"."CostType", "JCJP"."Description", "JCCD"."UM", "JCCD"."EstUnits", "JCCD"."ActualUnits", "JCCD"."EstHours", "JCCD"."ActualHours", "JCCD"."ActualDate", "JCJM"."Description"...

I did get it to work but it takes a really long time to run. I am trying to figure out what I can do to get it to speed up. It takes several hours to complete.