cancel
Showing results for 
Search instead for 
Did you mean: 

Grouping but leaving data available for other groups

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

Yep, that is the way it works.

You could create a subreport and do your second grouping there. Another way to access the data twice is to do a crosstab for the second set (group the report on weeks and the crosstab on months, )

Former Member
0 Kudos

Debi is right you can only access data once.

Another possibility is to build a command and union the data sets together in the way you want. If your SQL is up to it this is probably the easiest solution as it takes all the hard work out of Crystal.

Ian

Former Member
0 Kudos

Ian, how does this work. I let crystal do all my linking and don't write SQL statements

Former Member
0 Kudos

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

Former Member
0 Kudos

Rather than stealing this thread, I will start a new one regarding this subject and return this one to Listerman

Answers (3)

Answers (3)

Former Member
0 Kudos

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?

Former Member
0 Kudos

On second thought that is not the way to do it.

create a subreport simular to your original report (or even use your original report) and organize it by month rather than week and put it in the report footer.

Or use Ian's UNION solution

Edited by: DebiHerbert on Sep 23, 2010 3:49 PM

Former Member
0 Kudos

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)

Former Member
0 Kudos

Have you thought about creating a shared variable in a subreport and passing it back to the container report?

Former Member
0 Kudos

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