Selecting vars 1

[EFREE]

I am attemping to build a report that show the average talk time for each project. The data looks like this

projectid actionid type duration
1 33 47 34
1 22 47 43
1 33 47 55

I want to take all of the same project id's where actionid = 33 and type = 47 and average their duration.

any thoughts?

 

[JohnDTampaBay]

SELECT
ProjectID,
ActionID,
Type,
Avg(Duration) AS Avg_Duration
FROM
YourTable
WHERE
ActionID = 33
AND Type = 33
GROUP BY
ProjectID,
ActionID,
Type

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[EFREE]

OK but now where do i put that so i can implement it?

I am trying to set it up as a new data set and then call it into my report. Is that right or is there a better way?

 

[EFREE]

maybe that wasnt so clear, now that i have it in what is the best way to implement it into my report? I already have the groups set up, but when i go into the edit expression of the box i want, there is no choice to put that dataset in

 

[JohnDTampaBay]

Sounds like your report is designed to drill-down from ProjectID to ActionID to Type showing duration. You should be able to add a group footers and use the Avg function within Reporting Services to calculate the average. Forget the second dataset. If you only want to average certain values, use the IIf function within the Avg function as a filter. Is this closer to the solution you're looking for?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[EFREE]

John,

Yes that is exactly what I need. I was told we were going to lose a lot of functionality when moving from crystal and I guess this book I bought by WROX doesn’t go into any detail about writing simple code, so I wasn’t sure if you could even do that.

Essentially I have already set up a report with drill downs form project id and then inside interaction type and then actionid. I want to only average actionid = 33 within those drill downs. What would this if statement look like and more importantly how and where would I put it to implement it into my report?

 

[JohnDTampaBay]

Hmmm. I think my comment about an IIF function may have been incorrect. I was thinking of using it like a T-SQL case statement. Anyway, here's another possible solution which may do the trick.

In the SELECT statement for the dataset you are using, add a column that's populated with only the values you want to average.

SELECT ...
,(CASE ActionID WHEN 33 THEN Duration ELSE NULL END) AS AvgData
FROM ...

Use that column in the Avg function for the group footer in Reporting Services.

=AVG(AvgData)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[EFREE]

John

That totally solved my problem ...


THANKS!!