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!

Register Log in
  • Congratulations derfloh 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

    Data conversion question

    Try this...http://www.tek-tips.com/viewthread.cfm?qid=288901
  2. elsenorjose

    Calculate a sum based on range criteria

    Hi Skip, Thanks again for your help and direction. Here's the formula that worked. This is using actual cell references so it doesn't match the sample sheet I uploaded, but this formula did the trick. Note that it's an array formula so you have to do the whole CTRL+SHIFT+ENTER to make it work...
  3. elsenorjose

    Calculate a sum based on range criteria

    Hi Skip, The data is coming in using the "From SQL Server" option in the "From Other Sources" selection in the "Data" tab in the ribbon. The main reason is there are so many transactions per month, 1 year's worth of data across 7 companies and 400+ GL accounts per company, resulted in tens of...
  4. 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...
  5. elsenorjose

    Disallow printing of report if no parameters entered... record select formula?

    I'll need to look at it more closely when I'm in front of Crystal. In the meantime, hopefully someone can offer a solution. If not, I'll try to get back to you early next week.
  6. elsenorjose

    Disallow printing of report if no parameters entered... record select formula?

    Pretty sure it's the same thing basically, just use AND between params in your select. Both would have to evaluate to TRUE for the report to print records. I don't have a report in front of me right now that uses this, but I've used it in the past and it should work. You can use this for as many...
  7. elsenorjose

    Disallow printing of report if no parameters entered... record select formula?

    Create a new parameter value 'All' then use this in your select. IF {?Param} <> "(All)" THEN {TABLE.FIELD} = {?Param} ELSE TRUE If no parameter is entered or selected, the report shouldn't print anything. Jose
  8. elsenorjose

    Export to excel without excel creating blank rows

    I agree Kray. The approach I started taking was to either create a small SSIS package and email the results of a query in an Excel file, or link a spreadsheet to the SQL server back end and let the user refresh and do whatever they want in the spreadsheet. I've always felt it kind of defeated...
  9. elsenorjose

    GL Trial Balance Report

    In case anyone is interested, here's what I eventually came up with. It only shows the ending balance for the year in the Total column but it works for now. SELECT [Company], [GL Account], [Description], [Year], ISNULL([January],0) + ISNULL([February],0) + ISNULL([March],0) +...
  10. 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...
  11. elsenorjose

    Export to excel without excel creating blank rows

    Not sure but I think Skip doesn't care for reports in Excel, or am I reading too much into his post?
  12. 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...
  13. elsenorjose

    Add Balance (Total) column to PIVOT

    Hi Olaf, I found the answer. http://www.codeproject.com/Articles/232181/SQL-Pivot-with-Grand-Total-Column-and-Row Jose
  14. 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...
  15. elsenorjose

    Spit a field value then concatenate with colon separators

    Found the solution. The formula is as follows: CTime(Picture({field}, "xx:xx:xx")) Thanks.
  16. 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...
  17. elsenorjose

    Concatenate with comma but don't display only commas

    Hi Ido, Would I put that in my display formula?
  18. elsenorjose

    Concatenate with comma but don't display only commas

    Thanks. Checking for blank strings helped. One last question. I have a trailing comma whenever there is only one comment. How can I remove that?
  19. 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...
  20. elsenorjose

    Sum invoice rows across multiple tax amounts

    Thanks COSpringsGuy but the formula is doubling the invoice detail amounts. Using a real world example: invce_32 linnum_32 tax1_32 tax2_32 tax3_32 00945408 01 170 0 0 00945408 02 2.975 0 0 I am getting 345.96 as my total instead of 172.98

Part and Inventory Search

Back
Top