Selecting vars and then using them in groups 1

[EFREE]

Someone please put me out of my misery!!!

Ok i have a report where i have a group for projects and then inside that group is the interaction types for each project. Each row then has a value called actionid which is the type of action and duration the length. All i want to do is get the average duration first by interaction type, then project, and finally all. The select statement looks as follows:

 SELECT     dbo.historyactions.companyid, dbo.historyactions.actionid, dbo.historyactions.interactiontype, dbo.historyactions.projectid, 
                      dbo.historyactions.duration, dbo.projects.name, (CASE dbo.historyactions.actionid WHEN 70001 THEN Duration ELSE NULL END) AS TalkTime, 
                      (CASE dbo.historyactions.actionid WHEN 90007 THEN Duration ELSE NULL END) AS WrapTime
FROM         dbo.historyactions INNER JOIN
                      dbo.projects ON dbo.historyactions.companyid = dbo.projects.companyid AND dbo.historyactions.projectid = dbo.projects.projectid
WHERE     (dbo.historyactions.companyid = 156)
GROUP BY dbo.historyactions.companyid, dbo.historyactions.projectid, dbo.historyactions.interactiontype, dbo.historyactions.duration, dbo.projects.name, 
                      dbo.historyactions.actionid
ORDER BY dbo.historyactions.companyid, dbo.historyactions.projectid, dbo.historyactions.interactiontype, dbo.historyactions.duration, dbo.projects.name, 
                      dbo.historyactions.actionid

I thought by putting "= Avg(Fields!TalkTime.Value)" in the footers of my groups would give me the total for those groups but it just totals it for the whole report!!!

All ideas are greatly appreciated!

 

[Catadmin]

The footers are actually Page Footers, not group footers.

If you're looking for a group footer, try using a Table, setting up a Group in the Table and then putting your expression in the Group footer. This way, it will calculate each time the Group footer rolls over.

You can also nest groups, so you can do a sub-group footer total instead of a main-group footer total.

Does that help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[EFREE]

I have nested groups currently. When i highlight the row and put the cursor on the end, it pops up table1_group2 footer, and the same thing for group 1.

One thing it could be is i get a build error warning that reads as follows:
"Cannot deploy data source cce7_LIVE to the server because it already exists and OverwriteDataSources is not specified"

Is this build error messing up all my calculations?

 

[Catadmin]

Whether or not the datasource could be a problem depends on what permissions the account of said datasource has. Doublecheck the previously deployed one and make sure it's set up the same (though I doubt it given your error).

Before you overwrite a datasource (and yes, Virginia, Santa Clause does allow you to change that little "switch" when deploying, but you have to jump through hoops to find it), doublecheck with everyone to find out who created it and why it is set up differently than the way you want it.

My other suggestion is to rethink your Select statement. Is there a way you can get rid of the Order By clause? I believe report Grouping overrides that, anyway.

As far as why your group footers are totaling the whole report instead of the individual groups, I'm not sure. Maybe you need to break your groups from 2 to 4 and literally have the groups based on what fields you currently have your Order By clause listed in.

Did that make sense or am I rambling?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[EFREE]

Well the only thing i didn’t understand was

"Before you overwrite a datasource (and yes, Virginia, Santa Clause does allow you to change that little "switch" when deploying, but you have to jump through hoops to find it), doublecheck with everyone to find out who created it and why it is set up differently than the way you want it."

Im not sure where you were going with that but i would like to know what the OverwriteDataSource means.


The rest of what you said got me thinking, I took out the group by and order by which fixed my calculation problems, and then in the call statement "= Avg(Fields!TalkTime.Value, "scope")" i took out the scope, and totally fixed my problem!!!!

Thanks for pointing me in the right direction; i love your rambling lol...

 

[EFREE]

Well the only thing i didn’t understand was

"Before you overwrite a datasource (and yes, Virginia, Santa Clause does allow you to change that little "switch" when deploying, but you have to jump through hoops to find it), doublecheck with everyone to find out who created it and why it is set up differently than the way you want it."

Im not sure where you were going with that but i would like to know what the OverwriteDataSource means.


The rest of what you said got me thinking, I took out the group by and order by which fixed my calculation problems, and then in the call statement "= Avg(Fields!TalkTime.Value, "scope")" i took out the scope, and totally fixed my problem!!!!

Thanks for pointing me in the right direction; your rambling rocks! lol...

 

[EFREE]

wow it printed that twice huh thats strange...

 

[Catadmin]

OverWriteDataSource means exactly what it says. It's a little switch that allows you to modify your datasource in development, then, when you deploy your report to production (or whereever), it will overwrite the existing data source with your current data source.

If all the information is the same in both, this isn't a big deal. But if you've changed your connection string, even by one character, that can totally wreck your previously deployed report(s), especially if you're using a shared datasource that more than one report relies on.

And I'm glad you enjoyed my ramble. @=)

Did what I just say make sense?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???