ansaurus

Question

crystal reports query

Answer 1

+1 A:

Create new parameter fields {?Start Date} and {?End Date} in the Crystal Reports Designer.
Remove the date selection criteria from your query.
Either group by submit date and complete date in your query, or select * from master.
Use the select expert in the Crystal Reports Designer to set up similar record selection conditions to the one above, based on the parameters {?Start Date} and {?End Date}.
Add a summary field (either sum of logs, if you grouped in step 3, or a record count if you selected * from master) to the report footer.

Mark Bannister 2010-07-14 09:58:54

@depictureboy, after reading your response, I was going to suggest this, but Mark beat me to it.

PowerUser 2010-07-14 14:27:44

I understand this and its gotten me further than I was...Thanks. But I guess i am not making myself clear...or something....Since the 2 dates are the same, in the query and I want to stretch it out over a whole year grouped on the first day of each month..Just those logs that are open on those days...Instead of in record selection would I do it as a group selection? I wish I had a picture to show you...You both have been really helpful. If I wanted to make this a crosstab would that be easier to explain to me?

ecathell 2010-07-14 19:51:31

@depictureboy, I haven't addressed the need to report a year's worth of open logs as at the end of each month, as I thought the simplest way to do it would be to report the previous month at the start of each new month, make a note of that month's figures and maintain a rolling year's values that way. The problem with trying to report an entire year's figures in one go is that a log that was open at the end of one month may still be open at the end of the next month, and should therefore be reported in *both* months. As I said, running one month at a time is the simplest approach, but -

Mark Bannister 2010-07-15 10:45:17

[continued] there are other approaches - do you have access to (or can you create) a dummy table or view, populated with sequential numbers or dates? (I haven't used Access for a *long* time, so I'm not sure what you can do with it these days.)

Mark Bannister 2010-07-15 10:47:56

I try to avoid using Access as much as possible. If it makes the logic any better, I only need a snapshot of what logs are open on that day. The boss just wants to know how many logs we start with as open each month...does that make more sense? It may be impossible to do what I want...but I figured this was the place to find out :)

ecathell 2010-07-15 12:43:46

@depictureboy, as I mentioned earlier, the problem with trying to report an entire year's figures in one go is that a log that was open at the end of one month may still be open at the end of the next month, and should therefore be reported in both months. You **can't** report the same single row in two separate groups. You could try using the running total features of Crystal to simulate this, but it might be simpler to select the data in a subreport a month at a time, then link to the subreport 12 times from the main report (once per month). (It really would be easier to do in the query.)

Mark Bannister 2010-07-15 13:20:58

i am going to mark this correct. It doesnt answer what I was trying to do, but i see now that what I was trying was probably an impossiblity and this works for what I need. Thanks

ecathell 2010-07-21 12:54:55

ansaurus

tags:

views:

answers:

crystal reports query

related questions