Can I GROUP BY aggregate Function (Like SUM)

Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yetyes, with a derived table, but why would you want to?

please show an example, using sample data to illustrate|||this is my case:

i have a table with a column datetime (I use to record time of calls) , i want to create table that hold data for each hour, so i round the column and want to group by this column in the same time|||you can round a datetime? please show your query|||This is the Query of round datetime

DATEADD(Hour, DATEDIFF(Hour, 0, cdrCallDate), 0)

You can change [hour] to day , minutes , second , etc...|||yes, you can GROUP BY that expression :)

and you cannot use second, it causes an overflow

:)|||thanks man for this information

Can Calculated Measures be in specific Measure Group?

I have two measure groups in my cube, and a calculated measure that only uses measures from one of the measure groups. Is there a way to assign that calc measure to one of my measure groups, so in client applications, users will find in in a logical place, rather than sitting all by itself where it might be confusing?

Thanks

Kory

Yes they can. The feature is well hidden away though:
In BIDS select your cube and the calculations tab. Then select the Cube-> Calculation Properties menu item.|||

Yes, certainly. In the Cube Editor in BIDS, go to the Calculations tab and click on the 'Calculation Properties' button on the toolbar (it's next to the buttons which switch between form view and script view). You can associate calculated measures with measure groups in the resulting dialog.

HTH,

Chris

Calulated Fields vs UDF's

Does anyone have any suggestions on the use of Calculated fields over scalar
User Defined functions? I would think that the calulated field may be faster
than a scalar UDF but I don't know much about the mechanisms SQL uses to run
each method. Also, where might Table-valued UDF's possibly fit into this
scenario?
thank you for your time.Scalar UDFs (in SQL Server 2000) can be fairly slow, depending on the
implementation. If the same code can be placed in-line in a Select, for
example, the select will run faster than the version calling the UDF. Often
much, much faster.
I have not done a performance comparison, but with that in mind I would
expect a calculated field to run faster than a scalar UDF.
Table-valued UDFs return tables, so they are not a good fit for
high-performance operations on a column. The performance of table-valued
UDFs can be summed up this way:
In-line table valued UDF = View with parameters, therefore the UDF is
'compiled into' the plan if used in a join.
Multi-statement table valued UDF = Stored Procedure that returns a table.
If included in a join, it executes first and returns a result set.
RLF
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:44F255E8-8DE8-4449-9104-C3463EEFD3F4@.microsoft.com...
> Does anyone have any suggestions on the use of Calculated fields over
> scalar
> User Defined functions? I would think that the calulated field may be
> faster
> than a scalar UDF but I don't know much about the mechanisms SQL uses to
> run
> each method. Also, where might Table-valued UDF's possibly fit into this
> scenario?
>
> thank you for your time.|||Scalar UDFs are slow when they must do a lookup (i.e., they contain a
SELECT statement). When this happens it serializes your reads such that
your queries that contain the scalar UDF behave like cursor operations.
If your UDF simply performs a calculation without any lookups, the
performance should be similar to computed columns. I think you can
create indexes on computed columns though, but I'm not totally certain.
-Alan|||Thank you RLF and Alan for your explanations. My column is indeed a general
computation on other columns in the table so it sounds like they may be
similar in performance between both methods. The one advantage of using a
scalar UDF that I have figured out is that if I have multiple tables using
this similar computation, then I simply have to make the change in one place
to effect both table queries. I suppose I could simply call the UDF in the
calculated field as well to centralize the definition.
I typically would have created a veiw with a call tothe UDF with in it and
on top of the base table columns but I wanted to careful not to spread my
data access out all over the place for columns that might naturally seem to
be contained in the base table.
Maybe I will do a little playing with this today and post some varied
results using these different approaches. It might be interesting.
"Alan Samet" wrote:

> Scalar UDFs are slow when they must do a lookup (i.e., they contain a
> SELECT statement). When this happens it serializes your reads such that
> your queries that contain the scalar UDF behave like cursor operations.
> If your UDF simply performs a calculation without any lookups, the
> performance should be similar to computed columns. I think you can
> create indexes on computed columns though, but I'm not totally certain.
>
> -Alan
>|||A few more details.
SQL Server's Query Optimizer does not understand the output distributions of
UDFs. As such, it can cause cardinality estimates in query plan generation
to be sub-optimal.
(Eventually, we may be able to improve this story in a future release)
What I tell customers now - if you can write it as scalar logic, please do
so. If you need to use a UDF, please consider a computed column over the
result of this as well so that the optimzier can create the statistics it
needs to do a good job in plan generation.
I would specifically *not* recommend the use of UDFs to perform "singleton
(index) lookups" into another table. I've seen this pattern used in a
number of deployments. While it "works", it is very hard for the query
optimizer to pick a proper join order (which means that sometimes it picks a
poor order and plan performance can become very slow). If you can represent
these as joins, I recommend that you do so.
Best of luck to you,
Conor Cunningham
SQL Server Query Optimization Development Lead
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:0940C18A-9E28-4F12-B926-95B00B336D42@.microsoft.com...
> Thank you RLF and Alan for your explanations. My column is indeed a
> general
> computation on other columns in the table so it sounds like they may be
> similar in performance between both methods. The one advantage of using a
> scalar UDF that I have figured out is that if I have multiple tables using
> this similar computation, then I simply have to make the change in one
> place
> to effect both table queries. I suppose I could simply call the UDF in the
> calculated field as well to centralize the definition.
> I typically would have created a veiw with a call tothe UDF with in it and
> on top of the base table columns but I wanted to careful not to spread my
> data access out all over the place for columns that might naturally seem
> to
> be contained in the base table.
> Maybe I will do a little playing with this today and post some varied
> results using these different approaches. It might be interesting.
> "Alan Samet" wrote:
>

Calulated Fields in DataSet

Does anyone know if Reporting Services 2005 support calculated fields
in the dataset with RunningValue and Previous functions? Everytime I
trie it gives me an error?
Here's what I did, on the dataset I added a calulated field and put the
following expression in "=RunningValue(fields!xxxx, Sum)" and when I
hit preview it returned an error?
Is this a limitation of reporting services?Amarnath,
Thanks for the reply, I know this function works in the tables but I
wanted to use the output in a chart. Do you have any suggestions on
doing that? Can I use table calculated data in a chart?
- tuong
On Jan 22, 2:29 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Dont give this in a calculated field instead insert say a "Sr.No" column in
> the left side of your table and just in the properties put this syntax and it
> will work.
> Amarnath
>
> "tuong.k...@.gmail.com" wrote:
> > Does anyone know if Reporting Services 2005 support calculated fields
> > in the dataset with RunningValue and Previous functions? Everytime I
> > trie it gives me an error?
> > Here's what I did, on the dataset I added a calulated field and put the
> > following expression in "=RunningValue(fields!xxxx, Sum)" and when I
> > hit preview it returned an error?
> > Is this a limitation of reporting services... Hide quoted text -- Show quoted text -|||Infact to give running value you need to give scope which you are not and you
cant because it is like global definition for the table.
Amarnath
"tuong.k.lam@.gmail.com" wrote:
> Amarnath,
> Thanks for the reply, I know this function works in the tables but I
> wanted to use the output in a chart. Do you have any suggestions on
> doing that? Can I use table calculated data in a chart?
> - tuong
> On Jan 22, 2:29 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > Dont give this in a calculated field instead insert say a "Sr.No" column in
> > the left side of your table and just in the properties put this syntax and it
> > will work.
> >
> > Amarnath
> >
> >
> >
> > "tuong.k...@.gmail.com" wrote:
> > > Does anyone know if Reporting Services 2005 support calculated fields
> > > in the dataset with RunningValue and Previous functions? Everytime I
> > > trie it gives me an error?
> >
> > > Here's what I did, on the dataset I added a calulated field and put the
> > > following expression in "=RunningValue(fields!xxxx, Sum)" and when I
> > > hit preview it returned an error?
> >
> > > Is this a limitation of reporting services... Hide quoted text -- Show quoted text -
>