Recent content by elsenorjose

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

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...

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...

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...

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.

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...

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

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...

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) +...

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...

Not sure but I think Skip doesn't care for reports in Excel, or am I reading too much into his post?

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...

Hi Olaf, I found the answer. http://www.codeproject.com/Articles/232181/SQL-Pivot-with-Grand-Total-Column-and-Row Jose

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...

Found the solution. The formula is as follows: CTime(Picture({field}, "xx:xx:xx")) Thanks.