Parameter and CrossTab 1

[Kmccormic]

Good Afternoon:

Having problems getting cascade parameters to work. The parameter doesn't seem to filter at all and I can't think why. Possibly I'm approaching this the wrong way for what I want to accomplish?

I'm trying to do trend analysis on call volume, so I'm using cross-tabs in Crystal Reports 2008. I want hourly volume during business hours monday-friday only, and to be able to average by day of week (and potentially later by the 1st weeks of each month vs 2nd/3rd/4th) so I can look at the monthly average in August for Mondays, then Tuesdays, etc., 8AM-5PM. I also want a quarterly average for each day and overall. My data comes in by the quarter hour each day. In rows is a date/time field {Timestamp} which I have used CDATE to convert to a date field. In columns is field {Time} grouped hourly in specified order so I could group together 8:00/8:15/8:30/8:45 as 8:00, etc. The report is grouped by {Timestamp} by month. A select expert/filter on {Timestamp} selects only Mon-Fri using formula [DAYOFWEEK({Timestamp}) in 2 to 6]. I thought about trying to do a filter using the parameter as well as the Mon-Fri filter but get errors when adding it to the formula. So far I successfully have output showing Monday-Friday dates grouped by hour, and then by month.

I set up a parameter on {Timestamp}, static and only True for allowing a range. I then preview it, enter a range, and nothing gets filtered out, I get a full year's worth of data. I thought maybe it was related to my setting it as a Date field parameter, but when I set the parameter as Date/Time I get the same thing. I had wanted to be able to set the parameter to pull from formulas, so I could specify using the word "Monday", "Tuesday", etc but parameters only have fields to choose from, not formulas or other parameters.

I thought of maybe setting the bottom total line in the crosstab report as an average instead of a sum function but I can't figure out how to make that happen; the main data is sum of the volume of specific queues and there doesn't seem to be a way to change the total without removing the formula summing the queues together.

Help?

Regards,
Kara

 

[lbass]

I don't see any cascading parameters here. Please show how you incorporated the parameter into the record selection formula, by pasting the selection formula into the thread. It appears that you are identify multiple issues here. It would be better to present these in different threads in the future.

If you want help with crosstab summaries, etc., please show a sample of how the crosstab currently displays.

-LB

 

[Kmccormic]

Thank you LBass:

I will try to separate my concerns out in the future. I'm happy to focus on the Parameter or filtering issue for now. I was thinking it was best to use a cascade parameter to filter {Timestamp} by date range then by day of week. I couldn't get the first pass to work, reducing the date range, so I didn't get to filtering by day of week. Could you tell me how to display the SQL code for a parameter? I do have this SQL from the database:
SELECT iCDNStat.Time, iCDNStat.Timestamp, iCDNStat.CallsOffered, iCDNStat.CDNName
FROM blue.dbo.iCDNStat iCDNStat

For the parameter options chosen, I used the below:
Type: Date
List of Values: Static
Value Field: {Timestamp}
Show on Viewer Panel: Editable
Prompt Text: Enter Date Range
Prompt with Description Only: False
Optional Prompt: False
Default Value:
Allow Custom Values: True
Allow Multiple Values: True
Allow Discrete Values: True
Allow Range Values: True
Start:
End:

When previewing I get prompted to enter the date range, I enter 8/1/2009 to 8/31/2009, and I get output dates from 2/20/09 - to 9/10/09.

 

[lbass]

Go to report->selection formula->record and copy what you see there into this thread.

-LB

 

[Kmccormic]

Here it is:

DayOfWeek ({iCDNStat.Timestamp}) in 2 to 6

 

[lbass]

You have to reference the parameter in the selection formula. Change it to:

DayOfWeek ({iCDNStat.Timestamp}) in 2 to 6 and
{iCDNStat.Timestamp} = {?DateRange}

...using your date parameter where I put {?DateRange}

-LB

 

[Kmccormic]

Thank you LBass! That worked perfectly.

I notice you post many solutions here, thank you for your time and patience! I, and I'm sure everyone else here, really appreciate it.