Tuesday, March 27, 2012
DistinctCount
that can either be a valid value, an empty string or null - I believe the
nulls are being discarded in the counts, but is there a way to make sure that
the empty strings are not being counted?This may be out of your jurisdiction, but it sounds like the data needs to be
cleansed? If NULL is valid in the column then you probably shouldn't have
empty string. In any case, if you're using stored procedures, which would be
the recommendation, you can perform some data cleanup there so you're left
with valid values. I'd check with the DBA on why there are both empty
strings and NULLS and use one or the other for absence of "valid data"
"Myles" wrote:
> I am running into a problem using DistinctCount - I have values in the report
> that can either be a valid value, an empty string or null - I believe the
> nulls are being discarded in the counts, but is there a way to make sure that
> the empty strings are not being counted?|||Yes J.P., thank you - you hit the nail on the head - on all accounts.
Unfortunately, I am already 'blue' in the face - but suppose the right thing
to do is hit my head again...I am not sure it is going to change anything,
however, and so am still looking for a way to filter this stuff out of the
counts. Thanks for the reply!
"JP.Sklenka" wrote:
> This may be out of your jurisdiction, but it sounds like the data needs to be
> cleansed? If NULL is valid in the column then you probably shouldn't have
> empty string. In any case, if you're using stored procedures, which would be
> the recommendation, you can perform some data cleanup there so you're left
> with valid values. I'd check with the DBA on why there are both empty
> strings and NULLS and use one or the other for absence of "valid data"
> "Myles" wrote:
> > I am running into a problem using DistinctCount - I have values in the report
> > that can either be a valid value, an empty string or null - I believe the
> > nulls are being discarded in the counts, but is there a way to make sure that
> > the empty strings are not being counted?|||Myles,
Try using the COALESCE(fieldname,0) function in your SQL to change Null
into what is more appropriate, or you could use the CASE WHEN trim(fieldname)
= â'â' THEN null ELSE fieldname END statement to change the empty strings to
nulls.
You could also consider using the filter section of the dataset.
HTH
-walter
"Myles" wrote:
> Yes J.P., thank you - you hit the nail on the head - on all accounts.
> Unfortunately, I am already 'blue' in the face - but suppose the right thing
> to do is hit my head again...I am not sure it is going to change anything,
> however, and so am still looking for a way to filter this stuff out of the
> counts. Thanks for the reply!
>
> "JP.Sklenka" wrote:
> > This may be out of your jurisdiction, but it sounds like the data needs to be
> > cleansed? If NULL is valid in the column then you probably shouldn't have
> > empty string. In any case, if you're using stored procedures, which would be
> > the recommendation, you can perform some data cleanup there so you're left
> > with valid values. I'd check with the DBA on why there are both empty
> > strings and NULLS and use one or the other for absence of "valid data"
> >
> > "Myles" wrote:
> >
> > > I am running into a problem using DistinctCount - I have values in the report
> > > that can either be a valid value, an empty string or null - I believe the
> > > nulls are being discarded in the counts, but is there a way to make sure that
> > > the empty strings are not being counted?
Distinct Value of each column !
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
Distinct Value of each column !
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql
Distinct Value of each column !
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1
DISTINCT Value
(not just one columns distinct values - I want to eliminate duplicates from
the entire returned result set) will
Select DISTINCT col1,col2,col3,col4,col5 from mytable order by myfieldname
do the trick?
Do you have people in your band or group that only play by ear?
When transposing songs do those that play by ear struggle and
does it leave vocals and band members without a recording in the
correct key to practice with?
Visit http://www.jerichoband.net/jericho/keychange.htm
where you can have the song's key changed to what you need
without negatively affecting the tempo or instruments in the recording...Select DISTINCT col1,col2,col3,col4,col5 from mytable order by
myfieldname
only elimantes the combination of these columns in the resultsset, is
it that what you want to achieve ?
HTH, jens Suessmeyer.|||Just to state this right:
it only elimantes the duplicate combinations.
-Jens.|||That is exactly what DISTINCT does.
ML
http://milambda.blogspot.com/
Sunday, March 25, 2012
Distinct on only one column, return all columns
only the first record for each distinct value in the salutation column. I
come up with the following:
select c1.* from complainer as c1
join (select distinct salutation from complainer) as c2
on c1.complainerid = c2.complainerid
The code above returns the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'complainerid'.
Suggestions greatly appreciated.
RandyHow do you determine "first"? I'm assuming that you are using an
IDENTITY column as your id, so try something like this:
select c1.* from complainer as c1
join (select complainerid = MIN(complainerid), salutation
from complainer GROUP BY salutation) as c2
on c1.complainerid = c2.complainerid
HTH,
Stu
distinct mvp
i use a query that returns a set of projects and customers for my
report. i am using a multi value parameter to filter the projects in
the data. because the grouping is done in the report itself, when i
try to set the mvp from my query each project apears multiple times on
the dropdown list. is there a way to get the distinct projects from
the query? - to get each project to apear once?.
i dont want to create another dataset to get the distinct projects
becuase the query is quite heavy.
or maybe is there a way to create another dataset to query from my
main dataset?
thanks in advance
offaYou can't do a query on an existing dataset. For my parameter lists I have
dedicated datasets. Perhaps if all the query is doing is getting is the
distinct projects it won't be a compute intensive query. Also, you might
want to see what sort of index you have on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<offa23@.hotmail.com> wrote in message
news:1188265077.769587.273140@.k79g2000hse.googlegroups.com...
> hi all,
> i use a query that returns a set of projects and customers for my
> report. i am using a multi value parameter to filter the projects in
> the data. because the grouping is done in the report itself, when i
> try to set the mvp from my query each project apears multiple times on
> the dropdown list. is there a way to get the distinct projects from
> the query? - to get each project to apear once?.
> i dont want to create another dataset to get the distinct projects
> becuase the query is quite heavy.
> or maybe is there a way to create another dataset to query from my
> main dataset?
> thanks in advance
> offa
>
Thursday, March 22, 2012
DISTINCT Count and NULL Processiong
I recently learned that it's counting a NULL value as 1 and I don't want this. Are there some properties that can stop the NULL value from being counted? I tried playing with a few of the properties that I found, but no luck.
The only solution that I have thought of so far is creating another table in the DSV that explicitly filters out the NULL values in the WHERE clause and then using it as the source for the DISTINCT COUNT. However, I'd like to avoid this solution if possible.
Maybe some MDX in a Calculated Member is needed? If so, please provide the pseudo-MDX as my MDX is definitely weak.
Any thoughts?
By the way, it's a AS2005 cube using SQL 2005 Standard Edition.
How about creating a new, hidden dimension (or attribute on your customer dimension) with two members: one member represents rows in the fact table where your Customer is null, and one for where it's not null. What you could then do is keep your distinct count measure as it is, but then overwrite it in the MDX Script so it only returns the value for the not-null member - something like this:
(Measures.[Customer Count])=(Measures.[Customer Count], [Hidden Dimension].[Hidden Attribute].[Not Null Customers]);
HTH,
Chris
|||Or, even better, make [Not Null Customers] member to be a default member in that dimension, and then you won't need to do anything in MDX Script at all, and it will be better for performance. My preference, however, is to do the DSV change that you want to avoid, I think that is the best solution.Wednesday, March 21, 2012
Dissolve based on common value
Compare each value in [FIELD2] with every other value in [FIELD2]. Then, for all exact matches, perform % calculations on corresponding values in [FIELD3] and [FIELD4] to update [FIELD5]. ex:
[FIELD1] - [FIELD2] - [FIELD3] - [FIELD4] - [FIELD5]
North - China - 20 - 40 - 50.0%
Central - Japan - 30 - 90 - 33.3%
South - China - 10 - 10 - 100.0%
East - Japan - 50 - 200 - 25.0%
The desired output would be like:
Overall China has [100*(20+10)/(40+10)]=60.0% apples
and
Overall Japan has [100*(30+50)/(90+200)]=27.5% apples
...maybe by creating an array for all values in [FIELD2] using PHP for the comparison??
Regards
Accessorselect t1.f1, t1.f2, t1.f3, t1.f4, 100 * ((t1.f3 + t2.f3)/(t1.f4+t2.f4)) As "%"
from table t1
INNER JOIN
table t2 ON
t1.f2 = t2.f2 AND
NOT t1.f1 = t2.f1;
Returns,
F1 F2 F3 F4 %
---- ---- ---- ---- ----
South China 10 10 60
North China 20 40 60
East Japan 50 200 27.5862069
Central Japan 30 90 27.5862069
Displays commas in type FLOAT
column of type FLOAT so that it appears with the commas in the correct
place. It displays now as 10000000.0; I'd like it to display as
10,000,000. Thanks for any help."Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
Your front-end application should really be doing this type of work, not the
database itself.
Rick Sawtell
MCT, MCSD, MCDBA|||Formatting should be done client side, not in SQL.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.|||Do this in the reporting tool / client application, or cast the value to
money (not good) and then use function "convert" to cast it to varchar with
style 1.
Example:
use northwind
go
select
convert(varchar(25), cast(cast(orderid as float) as money), 1)
from
dbo.orders
AMB
"Rick Charnes" wrote:
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
>
Displayong "Empty string" to a textbox on the report
Hi All!
I was checking the value of a field and if it is empty sending empty string to the textbox if not only the first few values and it is working but on the empty field something like "#Error" is being displayed.
here is the code:
=Iif(Fields!Lname.Value <>””, Fields!Lname.Value.ToString().Substring(0,10),"")
What I want to acheve is : If it is not zero to take the first 10 characters and if not to send an epmity string to the textbox.
Any help plz?
Thank you in advance!
In your expression, you are making the assumption that the string will be at least 10 characters. If it isn't 10 characters you will get an error. I am not sure what you are trying to accomplish but see the expression below. It will truncate the field if it is over 10 characters.
=Iif(Fields!Lname.Value.ToString().Length() > 10, Fields!Lname.Value.ToString().Substring(0,10), Fields!Lname.Value)
|||
here is the code:
=Iif(Fields!Lname.Value <>””, Fields!Lname.Value.ToString().Substring(0,10),"")
What I want to acheve is : If it is not zero to take the first 10 characters and if not to send an epmity string to the textbox
Thank you.The one that you send to me is not doing what i was looking for. Thank you very much.
|||My expression does the exact same thing as yours except when there are less than 10 characters it will not attempt to truncate.
Input and output for my expression:
Input and output for your expression
If I am getting it right, the problem I think is that when that field is empty or NULL, it returns an error:
So in your code actually, the first line for input and output would be:
"" --> #Error
I dunno how to resolve this in RS as I tried various things and they didn't work (like length = 0 etc.), only thing I can think of for now is to modify your query itself to return the substring instead of the field and then use this new field..
e,g,
select .....,..,.., substring(ISNULL(OldFieldName,''), 0, 10) as NewFieldName
from TableName
|||Ryan Ackley MSFT wrote:
My expression does the exact same thing as yours except when there are less than 10 characters it will not attempt to truncate.
Input and output for my expression:
"" -> "" "foo" -> "foo" "bar" -> "bar" "SomeReallyLongString" -> "SomeReally"
Input and output for your expression
"" -> "" "foo" -> "#Error" "bar" -> "#Error" "SomeReallyLongString" -> "SomeReally"
Thank you very much. This is exactly what the problem that I am facing now let me try to see some other things and I will do as you suggest. Thank you.If you find anything new plz let me know.
Ephi
Displaying values of a textbox
textbox2.value: =ReportItems!textbox1.value
"Sanjeev" <sanjeev@.microsoft.com> wrote in message
news:<uafhla3qEHA.3700@.TK2MSFTNGP15.phx.gbl>...
> Hi,
>
> I want to display the value of one textbox in another textbox.
> Does anyone know the syntax for this expression.
> Both Textboxes are in a matrix which hopefully does not complicate
matters.
>
> T.I.A.
> Sanjeev
>
>
>
>
>Thanks. That works perfectly for textboxes that are rows or columns of the
matrix
Now i have another problem.
What if the textbox i wish to refer to is the actual subtotal textbox of the
matrix. The syntax below does not work.
Is there any syntax for this type of situation.
This is the error i get : "Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope.
"
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:%23CNBCj3qEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Use
> textbox2.value: =ReportItems!textbox1.value
> "Sanjeev" <sanjeev@.microsoft.com> wrote in message
> news:<uafhla3qEHA.3700@.TK2MSFTNGP15.phx.gbl>...
>> Hi,
>> I want to display the value of one textbox in another textbox.
>> Does anyone know the syntax for this expression.
>> Both Textboxes are in a matrix which hopefully does not complicate
> matters.
>> T.I.A.
>> Sanjeev
>>
>>
>|||I haven't played with this, but perhaps you could look into the more
extensive functionality of reporting services by using the Code Pane or even
a custom assembly
"Sanjeev" <sanjeev@.microsoft.com> wrote in message
news:u%23hX493qEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Thanks. That works perfectly for textboxes that are rows or columns of the
> matrix
> Now i have another problem.
> What if the textbox i wish to refer to is the actual subtotal textbox of
the
> matrix. The syntax below does not work.
> Is there any syntax for this type of situation.
> This is the error i get : "Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
scope.
> "
> "Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
> news:%23CNBCj3qEHA.3848@.TK2MSFTNGP14.phx.gbl...
> > Use
> >
> > textbox2.value: =ReportItems!textbox1.value
> >
> > "Sanjeev" <sanjeev@.microsoft.com> wrote in message
> > news:<uafhla3qEHA.3700@.TK2MSFTNGP15.phx.gbl>...
> >
> >> Hi,
> >
> >>
> >
> >> I want to display the value of one textbox in another textbox.
> >
> >> Does anyone know the syntax for this expression.
> >
> >> Both Textboxes are in a matrix which hopefully does not complicate
> > matters.
> >
> >>
> >
> >> T.I.A.
> >
> >> Sanjeev
> >
> >>
> >
> >>
> >
> >>
> >
> >>
> >
> >>
> >
> >
>
Displaying value of a variable during runtime
Greetings all,
Apologies if this question has been asked in the past but how I display the valuw of a variable during runtime?
Thanks for your help in advance.
Running in debugmode; you can set a break point and when the excution breaks; you can go to the locals window (Ctrl + Alt + V, L)to see the value of the variables at that point|||Dear Rafael, I am fallin in love with you LOL
dreameR.78 wrote:
Dear Rafael, I am fallin in love with you
LOL
Glad you got it!
Displaying value from another dimension
I'm relatively new to MDX and have what should be a simple problem to
solve.
I have a Hotel dimension and a hierarchial Geography
(Region/Market/SubMarket) dimension. There's a join between the two on
Geography_ID.
All I'm trying to do is display a few measures for the Hotel as well as
its Market value. I can't get it to display the Market.
SELECT
{[Measures].[CountUserReview], NonEmptyCrossJoin({[Hotel]},
{[Geography]})} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
I've tried several different things with the NonEmptyCrossJoin (among
others), please help.
A dimension can only appear on one axis, so I suspect your sample is
giving you a syntax error.
The following should give you the top 10 hotels in the rows, and the
markets in the columns, with the CountUserReview in each cell. Is this
the sort of result you are after? If not it would help to have a sample
of how you would like the output to appear.
SELECT
NON EMPTY {[Geography].Market.Members} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE
(
[Measures].[CountUserReview]
)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1129766281.949669.289920@.g43g2000cwa.googlegroups .com>,
query_squidier@.speakeasy.net says...
> Hi.
> I'm relatively new to MDX and have what should be a simple problem to
> solve.
> I have a Hotel dimension and a hierarchial Geography
> (Region/Market/SubMarket) dimension. There's a join between the two on
> Geography_ID.
> All I'm trying to do is display a few measures for the Hotel as well as
> its Market value. I can't get it to display the Market.
> SELECT
> {[Measures].[CountUserReview], NonEmptyCrossJoin({[Hotel]},
> {[Geography]})} ON COLUMNS,
> {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
> BDESC), 10, [Measures].[CountUserReview])} ON ROWS
> FROM [UserReview]
>
> I've tried several different things with the NonEmptyCrossJoin (among
> others), please help.
>
|||Thanks for the response Darren and yes I was getting an error.
Each Hotel has only one Market value.
I'm hoping to display each Hotel name ON ROWS and the following ON
COLUMNS:
HotelID, HotelName, Market, and Count.
The trouble I'm having is figuring out how to display stuff from the
Hotel dimension and the Geography dimension both ON COLUMNS.
Thanks for your help, this is very very different than T-SQL.
|||This is my latest attempt:
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
--MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentMember'
--SET [Market] AS '[Geography].[Market].MEMBERS'
--MEMBER [Measures].[Market] AS '[Market].CurrentMember'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview], [Geography].[Market].CurrentMember} ON
COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
|||The problem is that your geography dimension needs to be given a context
by being placed on one of the axis or in the slicer (where clause)
The other thing you were doing was mixing members of different
dimensionality in a set on the columns.
In order to get the market for each hotel I have crossjoined the
geography dimension (using the * operator). This will generate every
market for every hotel and I then exclude the empty ones with the NON
EMPTY clause.
Note: I would have thought that the HotelName and Market measures were
redundant as these names should appear in the rows of the returned
cellset. As such I have commented them out of the query below, if the
names of your dimension members are different you can remove the comment
dashes to include these measures again.
[vbcol=seagreen]
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentMember.Name'
SELECT
{[Measures].[Hotel_ID]
--, [Measures].[HotelName]
, [Measures].[CountUserReview]
--, [Measures].[Market]
} ON COLUMNS,
NON EMPTY {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].
[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} * {[Geography].
[Market].Members} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])[vbcol=seagreen]
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||Thank you very much, Darren. This helps bunches.
The only thing, though, I'm getting a syntax error near axis
definition: " * {[Geography]. [Market].Members}".
Thoughts?
|||Got it.
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview]} ON COLUMNS,
{ NonEmptyCrossJoin ({TOPCOUNT(ORDER([Hotel].[Name].MEMBERS,
[Measures]. [CountUserReview], BDESC), 10,
[Measures].[CountUserReview])}, {[Geography].[Market].Members})} ON
ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
The only other question I might have is if I did want to show the
Market value ON COLUMNS, how to do that. It didn't recognize
[Geography].[Market].CurrentMember.Name and [Geography].[Market].Name
just returns "Market".
Thanks so much, Darren, I've got lightbulbs going off all over.
|||> Thanks so much, Darren, I've got lightbulbs going off all over.
That's great to hear, glad I could help.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
Displaying value from another dimension
I'm relatively new to MDX and have what should be a simple problem to
solve.
I have a Hotel dimension and a hierarchial Geography
(Region/Market/SubMarket) dimension. There's a join between the two on
Geography_ID.
All I'm trying to do is display a few measures for the Hotel as well as
its Market value. I can't get it to display the Market.
SELECT
{[Measures].[CountUserReview], NonEmptyCrossJoin({[Hot
el]},
{[Geography]})} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
I've tried several different things with the NonEmptyCrossJoin (among
others), please help.
giving you a syntax error.
The following should give you the top 10 hotels in the rows, and the
markets in the columns, with the CountUserReview in each cell. Is this
the sort of result you are after? If not it would help to have a sample
of how you would like the output to appear.
SELECT
NON EMPTY {[Geography].Market.Members} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE
(
[Measures].[CountUserReview]
)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1129766281.949669.289920@.g43g2000cwa.googlegroups.com>,
query_squidier@.speakeasy.net says...
> Hi.
> I'm relatively new to MDX and have what should be a simple problem to
> solve.
> I have a Hotel dimension and a hierarchial Geography
> (Region/Market/SubMarket) dimension. There's a join between the two on
> Geography_ID.
> All I'm trying to do is display a few measures for the Hotel as well as
> its Market value. I can't get it to display the Market.
> SELECT
> {[Measures].[CountUserReview], NonEmptyCrossJoin({[H
otel]},
> {[Geography]})} ON COLUMNS,
> {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[C
ountUserReview],
> BDESC), 10, [Measures].[CountUserReview])} ON ROWS
> FROM [UserReview]
>
> I've tried several different things with the NonEmptyCrossJoin (among
> others), please help.
>|||Thanks for the response Darren and yes I was getting an error.
Each Hotel has only one Market value.
I'm hoping to display each Hotel name ON ROWS and the following ON
COLUMNS:
HotelID, HotelName, Market, and Count.
The trouble I'm having is figuring out how to display stuff from the
Hotel dimension and the Geography dimension both ON COLUMNS.
Thanks for your help, this is very very different than T-SQL.
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
--MEMBER [Measures].[Market] AS '[Geography].[Market].Curren
tMember'
--SET [Market] AS '[Geography].[Market].MEMBERS'
--MEMBER [Measures].[Market] AS '[Market].CurrentMember'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview], [Geography].[Market].CurrentMe
mber} ON
COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])|||The problem is that your geography dimension needs to be given a context
by being placed on one of the axis or in the slicer (where clause)
The other thing you were doing was mixing members of different
dimensionality in a set on the columns.
In order to get the market for each hotel I have crossjoined the
geography dimension (using the * operator). This will generate every
market for every hotel and I then exclude the empty ones with the NON
EMPTY clause.
Note: I would have thought that the HotelName and Market measures were
redundant as these names should appear in the rows of the returned
cellset. As such I have commented them out of the query below, if the
names of your dimension members are different you can remove the comment
dashes to include these measures again.
[vbcol=seagreen]
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentM
ember.Name'
SELECT
{[Measures].[Hotel_ID]
--, [Measures].[HotelName]
, [Measures].[CountUserReview]
--, [Measures].[Market]
} ON COLUMNS,
NON EMPTY {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures
].
[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} * {[Geography].
[Market].Members} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])[vbcol=seagreen]
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Thank you very much, Darren. This helps bunches.
The only thing, though, I'm getting a syntax error near axis
definition: " * {[Geography]. [Market].Members}".
Thoughts?|||Got it.
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview]} ON COLUMNS,
{ NonEmptyCrossJoin ({TOPCOUNT(ORDER([Hotel].[Name].MEMBER
S,
[Measures]. [CountUserReview], BDESC), 10,
[Measures].[CountUserReview])}, {[Geography].[Market].M
embers})} ON
ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
The only other question I might have is if I did want to show the
Market value ON COLUMNS, how to do that. It didn't recognize
[Geography].[Market].CurrentMember.Name and [Geography].[Mar
ket].Name
just returns "Market".
Thanks so much, Darren, I've got lightbulbs going off all over.|||> Thanks so much, Darren, I've got lightbulbs going off all over.
That's great to hear, glad I could help.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
Monday, March 19, 2012
Displaying seconds value as [mm]:ss
I am returning a int value from a database which is for an elasped
time in seconds. I want to convert this to mm:ss, however I dont want
it roll over to hours or days.
I trying something along the lines of:
=String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
However this still allows values to go to hours/days, just doesn't
display them
So for example a value returned from the database of 19257 (seconds)
should be displayed as 320:57 ([mm]:ss)
This is achived in excel by
19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
Then change the format on the cell to [mm]:ss
Then 320:57 is displayed.
Any ideas would be great
Thanks
DavidYou could create custom function, something like this:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = seconds - ss * 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
and then call it from textbox expression:
=Code.Seconds2mmss(Sum(Fields!Airtime.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <david.glasgow@.unison.co.nz> wrote in message
news:9f43b964.0408112015.79b5c9b9@.posting.google.com...
> Hello
> I am returning a int value from a database which is for an elasped
> time in seconds. I want to convert this to mm:ss, however I dont want
> it roll over to hours or days.
> I trying something along the lines of:
> =String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
> However this still allows values to go to hours/days, just doesn't
> display them
> So for example a value returned from the database of 19257 (seconds)
> should be displayed as 320:57 ([mm]:ss)
> This is achived in excel by
> 19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
> Then change the format on the cell to [mm]:ss
> Then 320:57 is displayed.
> Any ideas would be great
> Thanks
> David|||Thanks for that, the final code that returned the result was:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Thanks again for your help
Displaying report parameters in report header or body
I like to see in the generated report what the parameters value are for the
current report like startdate and enddate
Thanx WimmoYou can do this by adding a textboxes to the page header and using
expressions like:
=Parameters!startdate.Value
=Parameters!enddate.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wimmo" <Wimmo@.discussions.microsoft.com> wrote in message
news:A1F341D0-9A5A-463E-9974-18A0232F9072@.microsoft.com...
> Hi,
> I like to see in the generated report what the parameters value are for
the
> current report like startdate and enddate
>
> Thanx Wimmo
Sunday, March 11, 2012
Displaying member name instead of key in query
In the tools that I have used for getting generated MDX queries (Query Analyzer, Proclarity, Excel) the query always seems to show the key value instead of the member name.
Is it possible or is there a query tool that will generate queries with the member name instead?
See example:
[Organization].&[152551]
Should read
[Organization].[By Division]
thanks,
Andrew
Set "MemberNamesUnique" to True for a particular attribute. That should do it|||
Thanks! Didn't even have to reprocess!
cheers,
Andrew
Friday, March 9, 2012
Displaying images in a report
Hi all
I have a report and I display specific images based on the parameter selected. i.e. If parameter value = "1" then I display "image1.jpg". The images were added as a part of the project so I was able to display the images by setting the value property on the images to the image file name. So if I want to display image1.jpg then I would set the value property of the image to "image1.jpg".
Now I have decided to create a folder called "images" in which I want to keep all the images. So I have removed the images from the project. In the value property of the image when I put "/images/image1.jpg" it doesnt work. I have also tried "images/image1.jpg" When I run the report in the Preview mode, the image is not shown. How do i fix this?
The directory struncture is so:
Reports folder
--images folder
-image1.jpg
-image2.jpg
--report1.rdl
--report2.rdl
Thanks guys!!
One thing you could do is embed the images so they're available without being published separately. But if you have a very large number of alternative images, I guess this might be cumbersome.
For dynamic images, you can create a parameter with the location/path of these reports, such as file://.... or /. finishing up with the final path slash. Now your values are something like
Code Snippet
IIF(paramvalue = "1",paramlocation & "image1.jpg", paramlocation & "image2.jpg")... or maybe if the "1" represents a part of the image filename, it's really just something like the following (no IIF):
Code Snippet
paramlocation & "image" & paramvalue & ".jpg"... IOW, you can fully-qualify the path, and this is one way I know for sure it works.
I'm not entirely clear on where you put the images and whether, therefore, the relative path you're using is going to work as you think it is. If it's on the Report Server you can try using Globals!ReportFolderand Globals!ReportServer to build up the image path but I haven't personally had much luck doing it that way.
You may also be using ReportViewer and trying to set the folder according to where your EXE is (or your webapp if it's a webform ReportViewer)? I think you still need to fully-qualify that path.
>L<
|||I have several images that I need to change based upon a parameter being passed in. I put the images in my images folder on my website and changed the image names to each parameter being passed. I then placed a web image on the report and changed the value of the image in properties to something like the following. "=http://server/images/" & Fields!parameter.Value & ".jpg" This way it changes the image based upon a parameter. Basically changing the image property to a string. Hope this might help.Jason
Displaying different text
text here. How to do that if Somename is a value of a field and it's
width changes? As it can't be done with a single textbox, can it be
done with a table? Can a table have dynamical width?The only dynamic sizing property in reporting services is the "CanGrow" property. However, this only applies to the height of objects. The width cannot be dynamically adjusted for larger values.|||So basically that type of formatting can't be done or is there another alternative i haven't thinked of?