Calculated measures in report builder

Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.

Any help is appreciated.

You can't. The Report Builder doesn't fetch dimension calculated members. For this reason, we abandoned dimension calculated members and defined them as calculated measures in the cube. I wasn't successful also in trying to define the calculated dimension members as regular members by implementing an utility dimension because cells that do not exists in the cube cannot be written to.|||

Thanks for your response, Teo.

Are you saying that you couldn't do it at all, or that you were able to convert the time calcs to calculated measures and you were then able to use them in Report Builder? At the moment I'm not sure how I would go about converting the time calcs to calculated measures.

BTW, I read your book. Nice work!

|||

Thanks. The calculated mesures approached worked but you will end up with as many calculated measures as the number of the time-based calculations (QTD, YTD, etc.). Another issue you will discover if you decide to take this road is that a date filter in the RB report won't overwrite the default member fo the Date dimension. This will force you to always bring the Date dimension in the report if the report uses the time calculated measures.

What I wasn't able to do is implement the Time intelligence dimension calculated members as regular members of an utility dimension.

It will be great if you could log this issue to connect.microsoft.com. The more it is asked for the more likely will be to get implemented.

|||

Hi,

I want to generate Ad-Hoc reports using report builder with cube created. But am not able to see any calculated measures in the report. Can anyone tell me how to solve this.

|||You need to assign the calculated measures to display folders using the Properties window on the Calculations tab.sql

CAL Licensing for SQL Servers

Hi,
I am not responsible for licensing issue. There are 5 SQL 2000 Standard
Servers in our sites. 3 of them are running applications like - Finance /
Payroll / CRM. When I set up each of them, I was told the license to be
entered is 20 CALs.
We also have another 2 SQL Server Standard for small application. I was
told to use 5 CALs when I set up the Server.
I would like to know for our cases, there are 5 SQL Servers, if we bought 20
CALs, is it possible for us to access all of the SQL Servers simultaneously
? OR 20 CALs for each server ?
If we have bought 20 CALs at our site, does it mean that I should enter 20
CALs for every SQL Server (Instead of 5 in 2 of them) ?
Thanks
JasonUnder Server / CAL licensing you need to purchase a CAL for each end user or
client device who will be connecting to SQL Server. A CAL is valid for any
number of servers so a user with a single CAL can access multiple servers.
As far as I'm aware, entering different CAL quantities for different servers
won't affect the status of your licensing in any material way. That's my
understanding. The best place to look for answers on licensing issues is:
http://www.microsoft.com/sql/howtobuy/default.mspx
--
David Portas
SQL Server MVP
--|||you are right.
1 CAL give access to all your SQL Server instance.
so 1 CAL give access to your 5 servers.
tools like the SQL agent consumme 1 CAL.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:3JadnaHYaJDNv2LfRVn-2w@.giganews.com...
> Under Server / CAL licensing you need to purchase a CAL for each end user
> or client device who will be connecting to SQL Server. A CAL is valid for
> any number of servers so a user with a single CAL can access multiple
> servers. As far as I'm aware, entering different CAL quantities for
> different servers won't affect the status of your licensing in any
> material way. That's my understanding. The best place to look for answers
> on licensing issues is:
> http://www.microsoft.com/sql/howtobuy/default.mspx
> --
> David Portas
> SQL Server MVP
> --
>|||Dear David,
Does it mean that for all SQL Servers, I can enter 20 as the CALs ?
Jason
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:3JadnaHYaJDNv2LfRVn-2w@.giganews.com...
> Under Server / CAL licensing you need to purchase a CAL for each end user
> or client device who will be connecting to SQL Server. A CAL is valid for
> any number of servers so a user with a single CAL can access multiple
> servers. As far as I'm aware, entering different CAL quantities for
> different servers won't affect the status of your licensing in any
> material way. That's my understanding. The best place to look for answers
> on licensing issues is:
> http://www.microsoft.com/sql/howtobuy/default.mspx
> --
> David Portas
> SQL Server MVP
> --
>

CAL Licensing and user limitations?

Hi to everyone, probably it's a faq but I did not find a sure answer.

A customer has a Sql Server 2000 standard installed in 1server/5CAL
licensing mode, in a windows 2000 server.
Does this type of installation limit the further connections (occourred in
same or distinct sql accounts) that exceed the 5 client/user?
And if this connections aren't limited, are these further connections
penalized by the query governor like MSDE does?

In short, is the CAL licensing mode only a legal issue without affecting or
limiting the performance of the exceeding connections?

Thanks in advance,
Pas!Pashkuale (sorry@.nomail.com) writes:
> Hi to everyone, probably it's a faq but I did not find a sure answer.
> A customer has a Sql Server 2000 standard installed in 1server/5CAL
> licensing mode, in a windows 2000 server.
> Does this type of installation limit the further connections (occourred in
> same or distinct sql accounts) that exceed the 5 client/user?
> And if this connections aren't limited, are these further connections
> penalized by the query governor like MSDE does?
> In short, is the CAL licensing mode only a legal issue without affecting
> or limiting the performance of the exceeding connections?

It's only a legal issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Buy Enterprise or Standard Edition

Dear Experts,
Right now i am doing a development using MSDE 7.0 and VB. Soon this
development will be completed (Hopefully!!!) and i will have to recommend to
a prospective client a version of SQL Server. The application is quiet small
for about 6 to 8 users. I would appreciate if you could throw some light on
the version of SQL Server i may need.
If the client wants to buy the latest i.e. Sql Server 2005 then will there
be any conversion problems from 7.0
Thanks for Help and like always thanks for passing your expert knowledge
Regards
Manish Sawjiani
--
Three Cheers to Technet for the Help!Hi Manish
Just Check this. This might be useful to yo
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8ynn.asp
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Manish Sawjiani" wrote:
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!|||Go to www.microsoft.com/sql and read about the editions available. Note that there recently has been
released a "workgroup" edition.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
Are you asking of your code will still work? Probably, but you need to plan for test and possibly
(although unlikely) code changes between 7.0 and 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in message
news:F4CFED90-C7FC-484C-B409-C5A75F990EE8@.microsoft.com...
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Buy Enterprise or Standard Edition

Dear Experts,
Right now i am doing a development using MSDE 7.0 and VB. Soon this
development will be completed (Hopefully!!!) and i will have to recommend to
a prospective client a version of SQL Server. The application is quiet small
for about 6 to 8 users. I would appreciate if you could throw some light on
the version of SQL Server i may need.
If the client wants to buy the latest i.e. Sql Server 2005 then will there
be any conversion problems from 7.0
Thanks for Help and like always thanks for passing your expert knowledge
Regards
Manish Sawjiani
--
Three Cheers to technet for the Help!Hi Manish
Just Check this. This might be useful to you
http://msdn.microsoft.com/library/d...br />
8ynn.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Manish Sawjiani" wrote:

> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend
to
> a prospective client a version of SQL Server. The application is quiet sma
ll
> for about 6 to 8 users. I would appreciate if you could throw some light o
n
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||Go to www.microsoft.com/sql and read about the editions available. Note that
there recently has been
released a "workgroup" edition.

> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
Are you asking of your code will still work? Probably, but you need to plan
for test and possibly
(although unlikely) code changes between 7.0 and 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in messag
e
news:F4CFED90-C7FC-484C-B409-C5A75F990EE8@.microsoft.com...
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend
to
> a prospective client a version of SQL Server. The application is quiet sma
ll
> for about 6 to 8 users. I would appreciate if you could throw some light o
n
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!

Buy Enterprise or Standard Edition

Dear Experts,
Right now i am doing a development using MSDE 7.0 and VB. Soon this
development will be completed (Hopefully!!!) and i will have to recommend to
a prospective client a version of SQL Server. The application is quiet small
for about 6 to 8 users. I would appreciate if you could throw some light on
the version of SQL Server i may need.
If the client wants to buy the latest i.e. Sql Server 2005 then will there
be any conversion problems from 7.0
Thanks for Help and like always thanks for passing your expert knowledge
Regards
Manish Sawjiani
Three Cheers to Technet for the Help!
Hi Manish
Just Check this. This might be useful to you
http://msdn.microsoft.com/library/de...ar_ts_8ynn.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"Manish Sawjiani" wrote:

> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Go to www.microsoft.com/sql and read about the editions available. Note that there recently has been
released a "workgroup" edition.

> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
Are you asking of your code will still work? Probably, but you need to plan for test and possibly
(although unlikely) code changes between 7.0 and 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in message
news:F4CFED90-C7FC-484C-B409-C5A75F990EE8@.microsoft.com...
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

But the SQL Server 2005 optimiser is inconsistent

We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.

However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.

My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.

However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.

CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))

INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')

-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error

DROP TABLE #Problem

I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!

A view is nothing more than a representation of some SQL. The optimiser will not treat the view as a single entity but rather merge the SQL into the main SQL.

Secondly, SQL does not guarentee order of execution therefore you have to assume the worst. (as you've been told) This is core to how the opimser works.

You could create an indexed view and use the noexpand clause.

|||

This is not a bug. You can hit the problem in SQL Server 2000 also depending on your query plan and/or data. Some new changes to the query optimizer causes more chances of this happening in SQL Server 2005. See the older threads below for more information:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299697&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250271&SiteID=1

Note that even though the ANSI SQL standards talks about various parts of the SELECT statement getting evaluated in a specific order like ON, WHERE, GROUP BY, HAVING, SELECT, ORDER BY most relational database systems optimize the query as a whole for performance reasons. For example, the engine might reorder certain predicates based on internal processing logic or evaluate certain parts of the query using an indexed or materialized view or employ other join strategies. So you should not assume any order in the evaluation of predicates. The only way to guarantee it is to rewrite the predicate conditions in cases like this using a CASE expression or dump intermediate results into a temporary table and then perform the filters which may raise errors depending on the data. Hope this helps.

|||

I appreciate that you can never guarantee the order in which expressions get evaluated but surely these two commands should be optimised to the the same plan....they don't.

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

SQL Server 2000 optimiser was consistent in this regard.

|||

The optimiser is cost based. My understanding is that one of the major things that changes between versions is the costs of different operations due to changes in hardware etc.

I suspect that the different versions may have different costs and so different plans are compiled. I would also suspect to get different plans on different machines due to differences in processor memory etc.

Busy Read/Write Disk

HI,
I have a problem with SQL2000, standard, sp3 running on
Win2K server, SP4.
There's a weekly operation of loading data about 30,000
records - letters. In this time my Busy Read/Write Disk
counter pages me for consistant failure and you could
imagine the performance degradation for this moment.
What could I do (I mean settings changed or?) if I could
not move the loading in off hours?
Thanks a lot.30,000 rows is not a lot of data and should not be that big a deal once a
week. What do you mean by pages you for constant failure? There is no
failure point per say with the counter. How are you loading the data and do
you have the log file on a separate disk array?
Andrew J. Kelly
SQL Server MVP
"MJ" <mschwenger@.cca-lv.com> wrote in message
news:034401c37bbb$64e27790$a301280a@.phx.gbl...
> HI,
> I have a problem with SQL2000, standard, sp3 running on
> Win2K server, SP4.
> There's a weekly operation of loading data about 30,000
> records - letters. In this time my Busy Read/Write Disk
> counter pages me for consistant failure and you could
> imagine the performance degradation for this moment.
> What could I do (I mean settings changed or?) if I could
> not move the loading in off hours?
> Thanks a lot.
>