Calculated measure's MDX formula in (regular) Non-calculated measure

Hi,

This is sort of an extension to my previous question: converting seconds to HH:MM:SS format. I got the MDX formula for that from deepak. but for that i have to create a Calculated measure!!!!

I already have the measures which display seconds, now i have to create a calculated measure which will display the seconds in HH:MM:SS. but I dont want the extra seconds measure to be displayed. I know i can make it visible=false. but this is cumbersome for me because all this is done programmatically using AMO in a program.

so is there a way to provide the MDX formula in the regaular measure itself without having to create a calculated measure.

Thus display/convert the seconds to the HH:MM:SS format in the original measure?

Regards

I forgot one more thing, can we use the "MeasureExpression" and "FormatString" properties of the measure itself? I tried puttin the MDX formula here but it gives a error.|||

Using the MeasureExpression property is the preferred technique to accomplish what you want. It works for the cases I have used it in. What error do you receive, and what is the MDX you're providing as an expression?

PGoldy

|||Only multiplication and division are allowed for measure expression.|||

Hi,

Thanks for the reply.

Here is the scenario with the MDX formula:

Consider that I have a regular measure M1. The measure M1 contains seconds as data. (eg: 400 seconds, 1000 seconds..etc.) currently what I do is whenever I have a measure which contains seconds and has to be displayed in the HH:MM:SS format:

1. I rename the M1 measure as "_M1" and make it visible=false,
2. Then I create a calulated measure with the name M1 and with the formula TimeSerial(0,0,Measures.[_M1]) and FormatString as 'hh:mm:ss'
3. Now the calculated measure M1 contains the the values of seconds formatted to the HH:MM:SS format. example: (TimeSerial(0,0, 37895) = 10:37:35 ; TimeSerial(0,0,1023) = 00:17:03)

What I require is not to create the extra calculated measure M1, but use the TimeSerial(0,0,MeasureName) formula in the regular measure M1. When I put the TimeSerial(0,0,MeasureName) formula in the MDXExpression property and the FormatString as 'hh:mm:ss', I get the following error : "The Cube xxxx cannot be saved because of the following errors: Errors in the metadata manager. The measure expression of the M1 measure is not of the form [measure1]*[measure2] or [measure1]/[measure2]"

Is there a way out for this? what other alternative is possible?

Regards

Calculated field with Percent

Hi,

I have a calculated field with percent format. However, the value of this field aggregates at every attribute i place on the browser. i want only to show this calculated field in the detail cell on. if the user tries to roll up, i must not be able to see any data in it.

Example

Problem: (View 1)

Service Job Discount Amount

1 200% 3000

2 40% 500

3 100% 400

As i drag the line number to the browser, i saw the right discount value:

(View 2)

Service Job LineNumber Discount Amount

1 1 100% 1500

1 2 100% 1500

2 1 40% 500

3 1 60% 100

3 1 40% 300

How can I make the discount blank on View 1 and only exists if i drag the line number? The problem in View 1 is it sums up the discount which is wrong.

cherriesh

Check whether you are in the right level:

CASE WHEN
[Dimension].CurrentMember.Level.Ordinal > 0
THEN [Measures].[Discount]
ELSE
NULL
END

More info and better recommendations here:
http://www.mosha.com/msolap/articles/mdxcomparinglevels.htm

Hope that helps,
Ibrahim

Calculated DateTime Parameters Format Problem

Hi
I am having a lot of trouble getting reporting services to accept the
format of my datetime parameters. I keep getting a cannot convert char
string to small datetime errors either in visual studio when I preview
the report or if builds correctly there then it errrors out when run
through the report manager. I am developing these reports on a New
Zealand server which follows the British DateTime Format.
Initially I calculated the dates using datasets that calculated
various dates to populate a dropdown list by running the following
SQL statement, excuse the mess, This statement runs in query analyser
and in the reporting dataset editor without problems.
set DATEFIRST 1
select "StartDate"= Convert(VARCHAR,GetDate(),112), "Label" = 'Today',
"Sort" = 1
UNION
Select Convert(VARCHAR,GetDate()-1,112),'Yesterday',2
UNION
select CONVERT(VARCHAR,DATEADD(dd, 1 - DATEPART(dw, getdate()),
getdate()),112),'Current Week', 3
UNION
select (Convert(VarChar, (DATEADD(dd, 1 - (DATEPART(dw, getdate())+7),
getdate())),112)),'Previous Week', 4
UNION
Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate()),
0),112),'Current Month',5
UNION
Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,
0),112),'Previous Month',6
UNION
Select Convert(DateTime,DATEADD(yy, DATEDIFF(yy,0,getdate()),
0),112),'Current Year',7
Order By Sort
It even runs in preview but when run from the report manager I get the
cannot convert varchar to small datetime error.
I retreated from this method and thought I would just use .net
calculated default values for the dates ie =DateTime.Today , this also
works in visual studio but falls over in the report manager on the
previously mentioned error.
All the dates are stored in tables in standard sqlserver datetime
format.
Could someone please provide a list of suggestions/bestpractices for
working with date parameters in SQL Server that are not in US format?
Thankyou
Michael BlackI would not convert the dates to varchar in your SQL. Instead I would format
them in the report in the format that you want.
"Mike" wrote:
> Hi
> I am having a lot of trouble getting reporting services to accept the
> format of my datetime parameters. I keep getting a cannot convert char
> string to small datetime errors either in visual studio when I preview
> the report or if builds correctly there then it errrors out when run
> through the report manager. I am developing these reports on a New
> Zealand server which follows the British DateTime Format.
> Initially I calculated the dates using datasets that calculated
> various dates to populate a dropdown list by running the following
> SQL statement, excuse the mess, This statement runs in query analyser
> and in the reporting dataset editor without problems.
> set DATEFIRST 1
> select "StartDate"= Convert(VARCHAR,GetDate(),112), "Label" = 'Today',
> "Sort" = 1
> UNION
> Select Convert(VARCHAR,GetDate()-1,112),'Yesterday',2
> UNION
> select CONVERT(VARCHAR,DATEADD(dd, 1 - DATEPART(dw, getdate()),
> getdate()),112),'Current Week', 3
> UNION
> select (Convert(VarChar, (DATEADD(dd, 1 - (DATEPART(dw, getdate())+7),
> getdate())),112)),'Previous Week', 4
> UNION
> Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate()),
> 0),112),'Current Month',5
> UNION
> Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,
> 0),112),'Previous Month',6
> UNION
> Select Convert(DateTime,DATEADD(yy, DATEDIFF(yy,0,getdate()),
> 0),112),'Current Year',7
> Order By Sort
> It even runs in preview but when run from the report manager I get the
> cannot convert varchar to small datetime error.
> I retreated from this method and thought I would just use .net
> calculated default values for the dates ie =DateTime.Today , this also
> works in visual studio but falls over in the report manager on the
> previously mentioned error.
> All the dates are stored in tables in standard sqlserver datetime
> format.
> Could someone please provide a list of suggestions/bestpractices for
> working with date parameters in SQL Server that are not in US format?
> Thankyou
> Michael Black
>

Calculate Totals for Groups??

This is a multi-part message in MIME format.
--=_NextPart_000_015E_01C572BF.645C0780
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I have two groupings in my table. one based of employee_id and the other = based on employee region. I am showing some values in the detail row of = that table.
Now, I need to show the total of one field in employee_id group1 footer = and employee_region group2 footer as well. the value that I display in = detail field can be type a or b. so I need to show 2 totals for both the = groups. How can I do this.
Group1:Header Employee_ID 1
Group2:Header Employee_Region Madrid
Detail Value:10 Type: B
Detail Value:10 Type: A
Detail Value:10 Type: B
Detail Value:10 Type: B
Detail Value:10 Type: A
Group2:Footer Total for Type A: 20
Group2:Footer Total for Type B: 30
Group2:Header Employee_Region Moscow
Detail Value:20 Type: B
Detail Value:20 Type: A
Detail Value:20 Type: B
Detail Value:20 Type: B
Detail Value:20 Type: A
Group2:Footer Total for Type A: 40
Group2:Footer Total for Type B: 60
Group1:Footer Total for Type A: 60
Group1:Footer Total for Type B: 90
My problem is in calculating Totals for Type A and B based on EmployeeID = and EmployeeRegion.How can I do this
Any help on this will be appreciated
Thanks in Advance
Kiran
--=_NextPart_000_015E_01C572BF.645C0780
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I have two groupings in my table. one based of employee_id and = the other based on employee region. I am showing some values in the detail = row of that table.

Now, I need to show the total of one field in employee_id group1 = footer and employee_region group2 footer as well. the value that I display in = detail field can be type a or b. so I need to show 2 totals for both the groups. How = can I do this.

Group1:Header Employee_ID 1

Group2:Header Employee_Region Madrid
Detail Value:10 Type: B
Detail Value:10 Type: A
Detail Value:10 Type: B
Detail Value:10 Type: B
Detail Value:10 Type: A
Group2:Footer Total for Type A: = 20
Group2:Footer Total for Type B: = 30

Group2:Header Employee_Region Moscow
Detail Value:20 Type: B
Detail Value:20 Type: A
Detail Value:20 Type: B
Detail Value:20 Type: B
Detail Value:20 Type: A
Group2:Footer Total for Type A: = 40
Group2:Footer Total for Type B: = 60

Group1:Footer Total for Type A: = 60
Group1:Footer Total for Type B: = 90

My problem is in calculating Totals for Type A and B based on EmployeeID and EmployeeRegion.How can I do this

Any help on this will be appreciated

Thanks in Advance
Kiran
=
--=_NextPart_000_015E_01C572BF.645C0780--For EmployeeID group - totals for type A
=Runningvalue(IIF(Reportitems!Type ="A",Reportitems!Detailvalue,0),SUM,employeeID groupname)
For EmployeeRegion group -
=Runningvalue(IIF(Reportitems!Type ="B",Reportitems!Detailvalue,0),SUM,employeeRegion groupname)|||Thanks Sonali.
Kiran
"Sonali" <Sonali@.discussions.microsoft.com> wrote in message
news:2694E290-3E42-4420-AD55-15D498A661EE@.microsoft.com...
> For EmployeeID group - totals for type A
> =Runningvalue(IIF(Reportitems!Type => "A",Reportitems!Detailvalue,0),SUM,employeeID groupname)
> For EmployeeRegion group -
> =Runningvalue(IIF(Reportitems!Type => "B",Reportitems!Detailvalue,0),SUM,employeeRegion groupname)

Calculate Age

Hi,
I have a 'date of birth' of a person and a date of an event and need to
calculate the persons age at that event (both datetime format). Is there a
function available I can use for this ?
Any advice appreciated
Nichttp://www.aspfaq.com/2233
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eZ2TS%23tkGHA.1936@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a 'date of birth' of a person and a date of an event and need to
> calculate the persons age at that event (both datetime format). Is there a
> function available I can use for this ?
> Any advice appreciated
> Nic
>|||datediff(yy,dob,eventdate)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This approach is not always good one
select datediff(yy,'20061231','20070101')
--
Should be
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN MONTH('20061231') > MONTH('20070101')
OR MONTH('20061231') = MONTH('20070101')
AND DAY('20061231') > DAY('20070101') THEN 1
ELSE 0
END
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9F8F5152-FBA9-4BDF-B358-01C9AEDB70C4@.microsoft.com...
> datediff(yy,dob,eventdate)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thanks for poitning that out Uri.. looks like you are out to take me down
today :)
Juz joking.. u made me understand a few things.. usually I look at the holes
in the solution.. Wonder how I missed the crater :)
Anyways.. guess this should do..
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
ELSE 0
END
Juz Rephrasing ur solution :)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||That doesn't quite work because leap years have one more day than normal
years. So
SELECT DATEDIFF(year, '20071231', '20081231')
- CASE
WHEN datepart(dy,'20071231') > datepart(dy, '20081231') THEN 1
ELSE 0
END
correctly gives age as 1, but
SELECT DATEDIFF(year, '20081231', '20091231')
- CASE
WHEN datepart(dy,'20081231') > datepart(dy, '20091231') THEN 1
ELSE 0
END
gives age as 0.
Tom
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BA42D051-F0AF-4971-8B42-6B83FE2757CC@.microsoft.com...
> Thanks for poitning that out Uri.. looks like you are out to take me down
> today :)
> Juz joking.. u made me understand a few things.. usually I look at the
> holes
> in the solution.. Wonder how I missed the crater :)
> Anyways.. guess this should do..
>
> SELECT DATEDIFF(year, '20061231', '20070101')
> - CASE
> WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
> ELSE 0
> END
> Juz Rephrasing ur solution :)
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Absolute murder.. better to keep my mouth shut for a while :)
So what happens if that guy was born on feb 29th?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Haven't you ever seen any Gilbert and Sullivan?
People born on Feb 29th are only about one fourth the age that we might
think they are.
In Pirates of Penzance, which my son just performed in as the Modern Major
General, a young man was indentured to the pirates until his 21st birthday.
But it turned out he was born Feb 29, so the 21st year after he had been
born, he had only had 5 birthdays, so his servitude had to continue.
HTH
Kalen Delaney, SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:53EC76A6-9297-4057-ACD4-6FD499DEC5EE@.microsoft.com...
> Absolute murder.. better to keep my mouth shut for a while :)
> So what happens if that guy was born on feb 29th?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Tracy,
As you probably know, a non-leap year has 365 days, and a leap year has 366
days:
Following returns 365:
SELECT DATEDIFF(day, '20030101', '20040101')
Following returns 366:
SELECT DATEDIFF(day, '20040101', '20050101')
Your expression would incorrectly return 1 for the following:
SELECT DATEDIFF(day, '20040102', '20050101') / 365
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23kwfIJxkGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Niclas wrote:
> I *think* this should work, I can't seem to think of a case where it
> wouldn't:
> SELECT DATEDIFF(day, birthdate, eventdate) / 365
>|||Itzik Ben-Gan wrote:
> Tracy,
> As you probably know, a non-leap year has 365 days, and a leap year has 36
6
> days:
> Following returns 365:
> SELECT DATEDIFF(day, '20030101', '20040101')
> Following returns 366:
> SELECT DATEDIFF(day, '20040101', '20050101')
> Your expression would incorrectly return 1 for the following:
> SELECT DATEDIFF(day, '20040102', '20050101') / 365
>
Ahh, there's the case I didn't think of...

button to export to excel

Hi, I would like to have a button on my report to export it to excel
(instead of having to choose the format from the toolbar and then press
"export").
Is there a way to do it? I know that there is a parameter that I can
add to the url for this but i don't know exactly how to add it to the
current url from my report).
Thanks.Yes it can be done.
Put a text box with text something like "Save as Excel" you can Underline
the text to look more like a hyperlink in the "Action" option of the text box
give the URL as
http://servername/reportserver?/SampleReports/Employee Sales
Summary&rs:Command=Render&rs:format=EXCEL. You can pass the parameters as
well.
PS: You need to create the replica of the original report and point in the
URL which takes it to excel otherwise you will have "Save as Excel" text will
also get exported.
Just to avoid that you can create the same replica (copy paste the report)
and remove the "save as Excel" text from the copy of the report.
Amarnath
"nicknack" wrote:
> Hi, I would like to have a button on my report to export it to excel
> (instead of having to choose the format from the toolbar and then press
> "export").
> Is there a way to do it? I know that there is a parameter that I can
> add to the url for this but i don't know exactly how to add it to the
> current url from my report).
> Thanks.
>|||Hi Amarnath,
Thanks for your reply.
As i understand, The only solution is to have two reports (for example
ORIGINAL and COPY_ORIGINAL).
1) My original report with a 'button' that have a URL of the
COPY_ORIGINAL and in that url i'll add the "rs:format=3DEXCEL" at the
end).
2) My copy that is the same as the first one (just copy&paste) but
without the 'button'.
Is that right?
Its look like it my be a solution exept for the problem that every time
i'll make a change in the original report i'll have to delete the copy
and create it again.
Did I got it right?
Thank.
Amarnath =D7=9B=D7=AA=D7=91:
> Yes it can be done.
> Put a text box with text something like "Save as Excel" you can Underline
> the text to look more like a hyperlink in the "Action" option of the text= box
> give the URL as
> http://servername/reportserver?/SampleReports/Employee Sales
> Summary&rs:Command=3DRender&rs:format=3DEXCEL. You can pass the parameter=s as
> well.
> PS: You need to create the replica of the original report and point in the
> URL which takes it to excel otherwise you will have "Save as Excel" text =will
> also get exported.
> Just to avoid that you can create the same replica (copy paste the report)
> and remove the "save as Excel" text from the copy of the report.
> Amarnath
>
> "nicknack" wrote:
> > Hi, I would like to have a button on my report to export it to excel
> > (instead of having to choose the format from the toolbar and then press
> > "export").
> >
> > Is there a way to do it? I know that there is a parameter that I can
> > add to the url for this but i don't know exactly how to add it to the
> > current url from my report).
> > > > Thanks.
> > > >

Bulkadmin role (BULK INSERT)

Hello,

I am trying to load a simple tab-delimited data file to SQL Server. I
created a format file to go with it, since the data file differs from
the destination table in number of columns.

When I execute the query, I get an error saying that only sysadmin or
bulkadmin roles are allowed to use the BULK INSERT statement. So, I
proceeded with the Enterprise Manager to grant myself those roles.
However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?

2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.

3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?

Thanks!> So, I proceeded with the Enterprise Manager to grant myself those
> roles. However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?

The roles are there but you need to be a sysadmin role member or a member of
that fixed server role in order to add members. Ask your DBA to do this.

> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.

The main security implication is that BULK INSERT accesses external data
under the security context of the SQL Server service account rather than the
invoking user's account.

> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?

Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
BCP access data under the security context of the invoking user.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0406052020.16b6b4e6@.posting.google.c om...
> Hello,
> I am trying to load a simple tab-delimited data file to SQL Server. I
> created a format file to go with it, since the data file differs from
> the destination table in number of columns.
> When I execute the query, I get an error saying that only sysadmin or
> bulkadmin roles are allowed to use the BULK INSERT statement. So, I
> proceeded with the Enterprise Manager to grant myself those roles.
> However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?
> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.
> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?
> Thanks!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<bRGwc.3644$uX2.3489@.newsread2.news.pas.earthlink.n et>...
> > So, I proceeded with the Enterprise Manager to grant myself those
> > roles. However, I could not find sysadmin or bulkadmin roles using the
> > Enterprise Manager. From what I read from my books, I thought these
> > were fixed server roles and that they would be there.
> > So I have a few questions:
> > 1) How do I create a user account/role that can issue BULK INSERT
> > commands?
> The roles are there but you need to be a sysadmin role member or a member of
> that fixed server role in order to add members. Ask your DBA to do this.

Hello Dan,

This was for personal use, so that makes me the DBA. I believe I
disabled the "sa" account when I first installed SQL Server (based on
some suggestions due to security risks). Perhaps that has something
to do with it. I will look into it.

> Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
> BCP access data under the security context of the invoking user.

Thanks! This clarifies the risk implications of BULK INSERT vs. bcp
that was not in the books. It looks like Bcp is the sure way to go
for most users.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP