Showing posts with label cant. Show all posts
Showing posts with label cant. Show all posts

Tuesday, March 20, 2012

Query Error

i cant run the following query on the reporting(2005) surface . but it is perfectly running on the 2005 sql environment. can some one correct it?


select {[Date—VEPLock].[VEP Lock].members as status} on rows,

{[Measures].[Amount Usd]} on columns
from Profitability

where [Date—Fiscal].[Year].[2005].[Quarter 4].lastchild:[Date—Fiscal].[Year].[2006].[Quarter 4].lastchild

Error: measure hierarchi already appear in the axis 0.

Hello. You are using the MDX report designer in SSRS2005? If you enter your own MDX in the generic query designer you cannot use the graphical MDX report designer.

Another issue is that you cannot control where the dimensions will be placed on each axis with the graphical designer.

Third. If you use the generic query designer in SSRS2005 I still think you will have to make the MDX an expression

Code Snippet

="select {[Date—VEPLock].[VEP Lock].members as status} on rows,

{[Measures].[Amount Usd]} on columns
from Profitability

where [Date—Fiscal].[Year].[2005].[Quarter 4].lastchild:[Date—Fiscal].[Year].[2006].[Quarter 4].lastchild"

HTH

Thomas Ivarsson

|||I am using the MDX report designer in SSRS2005.

Monday, February 20, 2012

Query as stored procedure?

I've read a few articles about this, and trauled the net but I cant quite get one to fit my query.

I need to make this as a stored procedure,

select records_tbl.place_id, who_tbl.person_id, place_tbl.place_name, records_tbl.record_datetime, who_tbl.who_name, thing_tbl.thing_name
from records_tbl, place_tbl, who_tbl, thing_tbl
where records_tbl.place_id=place_tbl.place_id
and thing_tbl.thing_id='TH0001'
and who_tbl.person_id='WH000005'

But where I can simply specify on execute the thing_id and the person_id. ie Execute query1 ('TH0001', WH000005')?

So this would involve removing thos two id's and changing them to parameter names? defined at the top of the query? in theory I understand but in practise I do not.

Can anyone help?Create PROCEDURE MyQuery1
(
@.thing_id VARCHAR(20)
,@.person_id VARCHAR(20)
)
AS

SELECT records_tbl.place_id,
who_tbl.person_id,
place_tbl.place_name,
records_tbl.record_datetime,
who_tbl.who_name,
thing_tbl.thing_name
FROM records_tbl,
place_tbl,
who_tbl,
thing_tbl
WHERE records_tbl.place_id = place_tbl.place_id
AND thing_tbl.thing_id = @.thing_id
AND who_tbl.person_id = @.person_id

GO

and Now call
exec MyQuery1 @.thing_id='TH0001' , @.person_id= 'WH000005'

that what you want??|||thats excellent! thanks a lot! :) :)

Query Analyzer-Result to Text

Hi, I am trying to print the query result directly from the query analyzer. I
am having two probles. One - I can't find a way of changing the <untitled>
title on the result, so I have to put it by hand. Is there a way of changing
the title? Two - Some of the fields on the tables were created too wide,
more than the should have been, so the result column are too wide for the
data. Is there a way of narrowing the result columns? I would really
appreciate any help
I don't know how to change the title. I never print directly. I typically
use the grid and copy and paste to Excel. You can issue a CAST() in the
select on the wide columns to make them as small as you want.
Andrew J. Kelly SQL MVP
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:338E2DC7-46DD-4E06-A0CB-BB89B8C5C343@.microsoft.com...
> Hi, I am trying to print the query result directly from the query
> analyzer. I
> am having two probles. One - I can't find a way of changing the
> <untitled>
> title on the result, so I have to put it by hand. Is there a way of
> changing
> the title? Two - Some of the fields on the tables were created too wide,
> more than the should have been, so the result column are too wide for the
> data. Is there a way of narrowing the result columns? I would really
> appreciate any help
|||Thank you. CAST() works fine for some columns but not for all, specially
calculated columns. I tried (cast(columnA ....)*cast(columnB....)) but it
didn't work.
"Andrew J. Kelly" wrote:

> I don't know how to change the title. I never print directly. I typically
> use the grid and copy and paste to Excel. You can issue a CAST() in the
> select on the wide columns to make them as small as you want.
> --
> Andrew J. Kelly SQL MVP
> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:338E2DC7-46DD-4E06-A0CB-BB89B8C5C343@.microsoft.com...
>
>
|||You should do the calculation first and then cast it. I thought you were
talking about character columns anyway.
Andrew J. Kelly SQL MVP
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:061E730D-C6D1-4F64-BC0B-2F105F7E3203@.microsoft.com...[vbcol=seagreen]
> Thank you. CAST() works fine for some columns but not for all, specially
> calculated columns. I tried (cast(columnA ....)*cast(columnB....)) but
> it
> didn't work.
> "Andrew J. Kelly" wrote: