on 09-22-2010 7:16 PM
I'm attempting to create a report that shows the average turnaround time of tasks closed within the last 30 days minus the weekends and a trend line for the last 13 weeks.
My first thought was I need to query 121 days worth of data so the first week of the 13 weeks (91 days) has 30 days worth of data. Create a group on the closed date for each week and then a sub group on the closed date by month.... I then realized that the subgroup wont have the actual 30 days worth of data because its a subgroup of a group that's grouped by week.
In the end, one data entry could be reported on in 4 weeks of data but I can't figure out how to accomplish this.
Any suggestions/ideas would be great.
This grouped my data into 4 groups (30, 60, 90, 120 days) and then the subgroup broke those individual groups down into weeks which didn't work because week 1 of the parent group only contained one week of data instead of the previous 30 days of data.
I'm stuck on creating groups without actually grouping the data. When it groups the data it makes it unavailable for other weeks but some data will overlap into other groups. Has to be a simple way of leaving the data for other groups to query
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sometimes Crystal can not manipulate the data as you want. Building a query and using that as a command gives you greater flexibility.
In the case of needing to access data twice you can union data as required
Select Field1, field2, field3 from table1
Union all
Select Field1, field2, field4 from table2
Number of fields and data types must be the same in each query.
Ian
A sub report can only return a shared variable for one date right? I can't have it return a value for week 1, week 2, week 3, etc can I?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I was hoping this would be simple. Is there anyway to create a variable and set its value equal to a SQL query? I could then set the variable equal to a select statement calculating the value I need for tickets closed 30 days before current week - N (number of weeks)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try reversing your groups and put the monthly one first. You can do it in your report by grabbing one and moving it up or down or changing the group in the group expert
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.