Search results for query: *

Thank you -k!

I hve a report that is printing detail records, including address information. I would like to suppress a field if duplicated ONLY while the customer name remains the same (and is supressed. Example: Customer Sales Zipcode Phone B. J. Gift Shop $150 19302...

I tried using a formula in CrystalReports and it worked - to some degree. The SQL looks like this: SELECT sdslsm , sum(sdaexp)/100 netsales , sdivd invDate FROM proddta.f4211 WHERE sdivd>={?BeginDateJDE} and sdivd<={?EndDateJDE} and sddct='RI' and sdktln=0 and sdsocn=0 and not sdlnty in...

I finally am getting back to this. Thanks for the info on the whitepaper, I have already put this to use on several reports. It's a good solution when you have results back and are formatting for display. However, I am at a loss as to how to convert the JDE date during the SQL query such that...

Thank you for your help. I can't get to this now but will tomorrow. -D

I need to create a crosstab table that will take input parameters of {?BeginDate} and {?EndDate} for a 13 month period and display colum values for each month starting with the first column representing the month including the BeginDate. The date values coming out of the database are hybrid...

I've run into some problem creating a report that previews and saves fine but when I close and try to repoen it, the program quits after a few seconds without any error message. The same query is used for other reports without a problem. I'm just swaping some column data and a group select...

The subquery appproach mentioned above seems to work OK without undue delay in processing time (~10 min for a year's worth of data). Using runing totals for the for the group footers and a sum for the report footer works well. Thanks for your help.

When I use the formula, CR9 wont let me group by the formula. It's not available on the list of fields I can group by. It seems as though the field must be valid at the record level. To get around this, I created a subquery in the SELECT statement which returns the total period sales for the...

I want to group report results by a category code related to the sum of a field. Specifically, the query returns sales at a detail level. GF2 grouping is by Customer (with sum[sales]) I would like to have a GF1 grouping by volume category (0-100, 100-500, etc.). I have a formula which associates...

I am constructing a query for a form (and report) which has input parameters. One of the parameters is for the item number and the users want to enter partial data and return all matching items. The problem I am having is in scripting the criteria. I am using : For field: IBLITM criteria...

That did the trick... a little in reverse. I used the following: PARAMETERS startDate DateTime; SELECT PRODDTA_F0101.ABUPMJ , PRODDTA_F0101.ABAN8 , PRODDTA_F0101.ABALPH FROM PRODDTA_F0101 WHERE (((PRODDTA_F0101.ABUPMJ)=CLng([startDate])+65379)); The application is for a JD Edwards One...

Thank you. I'll try to work with this. I noticed the same discrepency in the conversion. If I format an Excel date as a number I get 37725. If I use the CAST({?DATE} as int) I get 37723. Why??? dunno. Either way I can compensate with a constant. Where would I put Dim vJulian as long vJulian =...

Is there a formula function available that will convert a date (say, 4/14/2003) to it's Julian number (37723)? I am using an unput parameter of Date as a condition for a Julian date field. In SQL I can use CAST({?Date} as int). Does Access have an equivalent function? I am using Access 2000.

CR9 and SQL Server 2000

I have created a report that displays sales by Sales Rep By Category. I have been requested to modify it such that it contains rows for null sales data (i.e. a complete listing of categories for each sales rep showing actual or null sales). The SQL I am working with is below: SELECT dr.drdl01...

That doesn't do it. What I am looking for as a result would look something like this Sales Rep Promo Category Sales Jim Stickers Cat1 0 Cat2 100 Cat3 50...

Thanks swampBoogie. I am a little confused. In the example I provided above, all the search conditions for f4211 and f0005 are in the FROM clause. Should they be in the WHERE clause? I'll make the change on the inner join (to left).

Yes.. there are a few. I have tried left joins like the following but I run into the same problem. When I group (in the report) by the rep, I only get rows where the rep has sales. I also want to include rows where the rep does not have sales: SELECT drdl01 category ...

I am trying to create a report where I am grouping sales data by a sales rep for a special promotion items. Under the sales rep, I want to show the complete list of promo items, even though the rep may not had had any sales. Here is sample of the query that returns the sales but when I group by...