Anyone that unsderstands Crystal Reports edit Department Total Rep

What is the query that you are using to get your data?

Rob

Rob,

I will explain my purpose for your understaning of what I am trying to achieve. I want this report to link to my excel workbook that I use everyday to audit all my closed register. I then use this information for my import of journal entries into my accounting package in Simply Account.

This is the query I think. I basically tried to convert an old Quicksell

2000 file to RMS format. To do want I want.

I have re done the RegAnaly Report and revised to Match Z Reading since not all the Items listed on Z Reading Appear in RegAnaly. I call It Consolidated Register Analysis Report.

I have created a Sales Tax Summary Report for Crystal.

I want the All departments to list the sames way they do on Z Reading. My Report works right now but only the departments names and dollar amount with sales greater than $0.00 appear in the list on the report.

I how this was what you were referring too

SELECT TransactionEntry."Cost", TransactionEntry."Price", TransactionEntry."Quantity", Transaction."Time", Department."Name" FROM ((CupoloRMS.dbo.PUBLIC_TransactionEntry TransactionEntry INNER JOIN CupoloRMS.dbo.Item Item ON TransactionEntry."ItemID" = Item."ID") INNER JOIN CupoloRMS.dbo.Transaction Transaction ON TransactionEntry."TransactionNumber" Transaction."TransactionNumber") INNER JOIN CupoloRMS.dbo.Department Department ON Item."DepartmentID" = Department."ID" ORDER BY Department."Name" ASC, Transaction."Time" ASC

Sorry for taking a while to get back but I just got back this morning from a

2 overnight, 13 hours each, installations of a couple of fast food chains. I spent most of the day trying to wash the smell of fried food out of my skin.

To answer your question as to why you don't see those Departments that have $0 is because of how you joined the Department column. For a quick lesson in joins go here:

of an INNER try using a RIGHT join.i.e. RIGHT JOIN Department ON Item.DepartmentID = Department.ID

Rob

Rob

I tried a "RIGHT JOIN Department ON Item.DepartmentID = Department.ID" still getting same results.

Do you think you call look at it for me a see if something else is causing this result?

I can email you DEF file and and REPORT File?

You can email me at snipped-for-privacy@hotmail.com not to post your address in the group. Let me know.

Thanks

"Rob" wrote:

Fabiano,

My email address is in my profile, if you are using the web to post then click on my name, if you are using OE right click on any of my posts and left click on properties.

Rob

Rob

Just checking to see if you got my email.

Thanks

"Rob" wrote:

Yes I will take a look at it tonight or tomorrow.

Rob

Hi Rob

Was wonder if you got a chance to look at My Crystal Report. Please let me know if you did. I do not want to be a pain and I understand that you may be busy.

The results for you help this report may help me finish my project that I 'm trying to finish.

Thanks aga> Yes I will take a look at it tonight or tomorrow.

Sorry, I have had little chance to look at the report. But what I have found is that to do what you want we will have to rethink the way the report works. Reason is when a Z and X Report is generated it does those calculations then and there but it does not store the information in any specific table in the database. So we will have to do the same and the way your report is setup right it will not work.

What we need to do is generate Department Sum Totals for each Batch Number or each ClosingTime(per day). Then run a for statement on Department that puts a 0 if there are no totals for the given group.

So in the end there is a little more work that needs to be done for this report to work in CR. This should give you a start on where to look at but in the mean time I will still try on figuring it out myself.

Rob

Rob

I send you an email with some Crystal Reports that have data Saved in them as I was a little confused with your response.

Please view the Reports as they may help to clarify the response you gave me.

Thanks aga> Sorry, I have had little chance to look at the report. But what I have found