Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. elsenorjose

    Calculate a sum based on range criteria

    I am creating a Balance Sheet report and need to calculate the sum of a group of accounts based on multiple criteria. I've tried reading up on INDEX and MATCH, but I just can't extend what I've learned to accomplish my task. I my worksheet, I have columns for Company (Col A), GL Account (Col...
  2. elsenorjose

    Macola ES DRM not available?

    Exact Macola 9.6.500 We've reached out to Exact and they say the DRM is no longer available and we need to use the Table List tool in the System tab. Is this true? When I read the Flexibility help file, it says the DRM should be on the install disc, but the problem is our VAR isn't very...
  3. elsenorjose

    GL Trial Balance Report

    Using Exact Macola 9.6.500, I've been asked to create a report that will return a General Ledger trial balance showing the monthly net change for the year along with beginning and ending balances. The attached image shows the output of a SQL query in GP Dynamics that shows what I'm looking for...
  4. elsenorjose

    Add Balance (Total) column to PIVOT

    Using SQL Server 2014, suppose I have a pivot query like so: SELECT * FROM ( SELECT gl.reknr as [GL Account], oms25_0 as [Description], left(datename(month,docdate),13)as [Month], bdr_hfl as Amount FROM gbkmut gl left outer join grtbk acct on gl.reknr = acct.reknr...
  5. elsenorjose

    Spit a field value then concatenate with colon separators

    Hello all, I've searched the forum for this but I might not be using the correct terminology because I'm not finding a solution. Basically, I have a field that stores a HH:MM:SS value but with no colon. So, 12:24:59 is literally 122459. I want to take the value and display it as HH:MM:SS. I'm...
  6. elsenorjose

    Concatenate with comma but don't display only commas

    Hi folks, Using CR XI R2 on Windows 7 Professional. Database is on a SQL Server 2008 server. I'm trying to create a series of formulas to display notes that are stored in multiple rows and concatenate them on one row. In the Group Header I have @InitialiseNote WhilePrintingRecords...
  7. elsenorjose

    Sum invoice rows across multiple tax amounts

    I am trying to create a summary that sums tax amounts for multiple invoice detail lines based on multiple tax amounts. The layout of the invoice detail table is something like: invoice line tax1 tax2 tax3 12345 01 1.00 12345 02 2.00 Total for the...
  8. elsenorjose

    Change field data type for export to Excel

    I am using CR XI R2 against a SQL Server 2000 backend. A field in one of the tables in my report is typed NVARCHAR (5) and can have numeric values, text values, or NULL. The column is a scoring column where a user can enter a 1-10 rating, leave blank, or enter some text such as 'NA'. We export...
  9. elsenorjose

    Administrator receives error Your request was refused because you do not have the right 'Download'..

    Clean install of BO BI 14 on MS Server 2008 throws this error when running in the CMC. I'm an administrator on the system so I have all rights but I keep getting this error. It's intermittent in that if I run the Crystal Report via 'Run Now' I sometimes get the error and if I schedule the report...
  10. elsenorjose

    Migrating from SQL Server 2000 to SQL Server 2008 R2 causes report to fail

    We're running a CR Server (XI R2 SP 2) where we have Crystal Reports running against a SQL Server 2000 (not sure of revision) database. We are migrating the database to a SQL Server 2008 R2 instance. One report in particular runs in about 30 minutes on the 2000 DB but on the 2008 instance, it...
  11. elsenorjose

    Report with multiple optional paramters retrieving more data than expected in certain scenarios

    Hello all, I am trying to modify a report that has multiple optional parameters (CR 2008) to make it work so that the users can filter the report by any of the parameters but I don't think I've written the record selection formula correctly. Basically, given the parameters below, a user should...
  12. elsenorjose

    Comparison of values in CASE statements

    Hello gurus, I am trying to write a query using 3 CASE statements to sum amounts for charges, payments, and adjustments and I want to then compare the values to find records where there is a $0 payments amount and adjustments = charges. My attempt at this results in an error message...
  13. elsenorjose

    Can you drill to details from ANY group level?

    Hello all, I have a report that has 3 group headings, Service Area, Date, and Log ID with a hidden details section. I have been asked to make the report drillable from any group level. Is that even possible? It was my understanding that to get to the details in a grouped report, you have to...
  14. elsenorjose

    "Flatten" query results

    Hello all, I am using Oracle 10.2.0.5.0 and have written the following query which gives me the results I expect, just not in the format I want. The output is this: Log_ID 1stPrimary 2ndPrimary 3dPrimary 123 JONES, MICHAEL NULL NULL 123...
  15. elsenorjose

    Conditional formatting based on multiple rows or fields

    Hello everyone, I'm hoping this is a simple fix but I need to conditionally format fields that don't match another field in my report but based on them showing up ANYWHERE in the fields I'm comparing. Basically, in the attached, for Log #123 (which identifies a surgery), you can see that...
  16. elsenorjose

    PIVOT results from 2 tables

    I have searched for a way to pivot the results of a query so I can take multiple rows and instead display as a single row with multiple columns but most of the examples I have come across are based on a single table query. I have a query that joins 3 tables and I need to pivot the results...
  17. elsenorjose

    Need help converting a CASE statement with NVL from an Oracle SQL query

    Hello everyone, I have a line of code from an SQL query that I am trying to convert into syntax Crystal understands. CASE WHEN SCHEDULE.EVENT_CODE = 190 THEN nvl(to_char(SCHEDULE.TIME_IN,'HH24:mi' ),0) else '9/9/99' end EVENT_CODE is a string (VARCHAR) and TIME_IN is a DATE field. The CASE...
  18. elsenorjose

    Wildcard group sort

    Hello all, I have to group records based on a specific string in a name field and then sort within the grouping based on the records. The string is 'TBD' so in my select formula, I have the following: {SO_Master.NAME_27} startswith 'TBD' Now, within the group, I need to sort in the following...
  19. elsenorjose

    Exclude orders when any line item in the order has a certain attribute

    Hello all, I'm creating a report for sales orders and the requester has asked that certain orders be excluded if any line item has a specific stock location (anything with '2-' at the beginning). As a real-world example, I have an order number 20022036 that has 2 lines, one with a stock...
  20. elsenorjose

    Need to extract date from string and format field as date to group on

    Hello all, I've been searching through the forum looking for the correct formula so I can extract a date from a text field and I have part of it working but I would like to tweak the formula to take into account NULLs and also to convert the results into a date type and also strip any text that...

Part and Inventory Search

Back
Top