Changing Column and Row Heading in MDX

I am trying to change the default row and column heading to make it more meaningful to the user in MDX. Can you please help me.

This is done easily in SQL like

SELECT the_actual_cost AS cost......

Thanks in advance.

Riju

You cannot use aliases in MDX. There are a couple of options though:
1) Select a "friendlier" member property to display instead of the name of the member.
2) Calculated members can be named on the fly.

.. there may be others in 2005 that im not aware of, but these are the ones i've traditionally used.

|||Hi,

Please try WITH SET/ WITH MEMBER option to name column headers.

Niyas|||AFAIK its impossible to retrieve the name of the named set (at least in adomd).|||Thank you all for responding to my question. I was able to rename the column header using calcualted member. However, i am little concerned about the efficiency of this technique. For example i have a measure [date of transaction] and if i want it to be named simply as [date] i have to do [date] as '[date of transaction]'. I am wondering if this will result in slower queries response time?

Thanks
Riju
|||

Why dont you just rename your measure (provided you dont already have a lot of stuff running against this measure)?

|||Hi Peter,

I am using one measure in multiple reports and trying to give them different names in different reports to make it more meaningful.

Thanks
Riju|||

Then calculated members may be the only way..

Changing Column and Row Heading in MDX

I am trying to change the default row and column heading to make it more meaningful to the user in MDX. Can you please help me.

This is done easily in SQL like

SELECT the_actual_cost AS cost......

Thanks in advance.

Riju

You cannot use aliases in MDX. There are a couple of options though:
1) Select a "friendlier" member property to display instead of the name of the member.
2) Calculated members can be named on the fly.

.. there may be others in 2005 that im not aware of, but these are the ones i've traditionally used.

|||Hi,

Please try WITH SET/ WITH MEMBER option to name column headers.

Niyas|||AFAIK its impossible to retrieve the name of the named set (at least in adomd).|||Thank you all for responding to my question. I was able to rename the column header using calcualted member. However, i am little concerned about the efficiency of this technique. For example i have a measure [date of transaction] and if i want it to be named simply as [date] i have to do [date] as '[date of transaction]'. I am wondering if this will result in slower queries response time?

Thanks
Riju
|||

Why dont you just rename your measure (provided you dont already have a lot of stuff running against this measure)?

|||Hi Peter,

I am using one measure in multiple reports and trying to give them different names in different reports to make it more meaningful.

Thanks
Riju|||

Then calculated members may be the only way..

changing all fonts

Any idea how come selecting a matrix and changing the font size does not
affect all of the text? It seems like I have to select each row and to make
this type of change.
Do other people have this same problem?
TIA
DeanOn Jul 27, 6:10 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> Any idea how come selecting a matrix and changing the font size does not
> affect all of the text? It seems like I have to select each row and to make
> this type of change.
> Do other people have this same problem?
> TIA
> Dean
This seems to be a common occurrence. This is also true for table
controls. Sorry that I cannot offer any further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Change value displayed in last row in table?

I have a table which displays years (2007, 2006, 2005, etc.) as one of its fields. I need to have it display Year + " and prior" for the last row. Any thoughts on how I might do this? Thanks!

Hello,

Try this in your expression:

=Iif(CountRows("DataSetName") = RunningValue(1=1, Count, "DataSetName"), cStr(Fields!Year.Value) & " and prior", cStr(Fields!Year.Value))

Hope this helps.

Jarret

|||Great, that did it!

change the background color in table in report

I have a table that i need to display data
for each row I want the background color to be diff
anybody knowhow to do it ?

color
blank
color
blank

i am not at work right now but if iam not mistaken the syntax is:

iif(rowcount(nothing)=2, " GRAY " , nothing)

|||thank you|||

I tried using this property but it gives me error RowCount not declared

where should I write this event ?

|||

Try using RowNumber instead of RowCount.

The code is placed in the background color property. Choose the color dropdown and look for the <Expression> option. Select that, and add your code there.

|||

If I need to see one yes one no like

1111 [visible]

2222 [invisible]

3333 [visible]

4444 [invisible]

......

I change the property Visibility-Initial visibility = expression = IIF(parameter="SOMETHING", FALSE,TRUE) with this I can hide the DATA but I need to hide the Field too (with border and everything), in preview it looks very good but If I deploy the report change and show the field with borders.

MARCOS

|||

I do not see alternate rows gray .

In that expression what does "nothing" stand for .

I just copied that statement in my code

Do I need to replace it with some values

change the background color in table in report

I have a table that i need to display data
for each row I want the background color to be diff
anybody knowhow to do it ?

color
blank
color
blank

i am not at work right now but if iam not mistaken the syntax is:

iif(rowcount(nothing)=2, " GRAY " , nothing)

|||thank you|||

I tried using this property but it gives me error RowCount not declared

where should I write this event ?

|||

Try using RowNumber instead of RowCount.

The code is placed in the background color property. Choose the color dropdown and look for the <Expression> option. Select that, and add your code there.

|||

If I need to see one yes one no like

1111 [visible]

2222 [invisible]

3333 [visible]

4444 [invisible]

......

I change the property Visibility-Initial visibility = expression = IIF(parameter="SOMETHING", FALSE,TRUE) with this I can hide the DATA but I need to hide the Field too (with border and everything), in preview it looks very good but If I deploy the report change and show the field with borders.

MARCOS

|||

I do not see alternate rows gray .

In that expression what does "nothing" stand for .

I just copied that statement in my code

Do I need to replace it with some values

Change tablerow height at runtime

Hi,
I need to change the height of a row at runtime, anyone knows how can
I do it?
Thanks for your help.On Oct 17, 5:26 am, BPiano <bernardo.pi...@.sapo.pt> wrote:
> Hi,
> I need to change the height of a row at runtime, anyone knows how can
> I do it?
> Thanks for your help.
There's not really a way to directly control this; however, if you are
wanting to increase the row/cell size across the board you can set the
'Textbox Height' property to: 'Can increase to accommodate
contents' (via: selecting a table cell in Layout view >> right-click
>> select Properties >> select Format tab). Something that could
possibly work is to set the BorderWidth: Top or Bottom property (via:
selecting a table cell in Layout view >> select F4/Properties Window
>> select drop-down to the right of item >> select <Expression...>) to
an expression. I'm not sure of your particular scenario; but you might
use an expression like:
=iif(Fields!SomeField.Value > 50, "5pt", "1pt")
Where if SomeField's value is greater than 50 set the border width for
the top to 5pt otherwise, set it to 1pt. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

change row size

Hi everybody!!
I have a table with some nvarchar columns. When I try to put the size I can't to put it greater than 4000.
How can i change the row size?
Thanks!!That's the limit for that data type. Internally SQL Server uses 2*n bytes to store it. If you want to go up to 8000 bytes and don't need to use unicode character you should switch to varchar.

Change row background color when column value changes

I am looking for a way to alternate the row color when a value in a
column changes. I am using a simple table report. The output of which
looks like
Application Name Version Usages
MicroStation 8.1.2.1 10
MicroStation 8.9.2.1 4
MicroStation 9.1.1.1 1
MS Word 8.0.0.0 1
MS Word 9.1.2.1 2
Excel 9.1.2.1 2
What I want to do is have the background color change when the
Application Name value changes. In this case the rows for MicroStation
and Excel would be the same color and MS Word would have another color.
In ASP.NET I would do this by creating some temp variables to keep
track of the previous row value and the current color. When the row
value changes I would flip the current color value. But I am not sure
how to have variables in Reporting ServicesUse the Previous function to compare the previous row:
Previous(Fields!ApplicationName.Value)
--
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"mkellerm" wrote:
> I am looking for a way to alternate the row color when a value in a
> column changes. I am using a simple table report. The output of which
> looks like
> Application Name Version Usages
> MicroStation 8.1.2.1 10
> MicroStation 8.9.2.1 4
> MicroStation 9.1.1.1 1
> MS Word 8.0.0.0 1
> MS Word 9.1.2.1 2
> Excel 9.1.2.1 2
> What I want to do is have the background color change when the
> Application Name value changes. In this case the rows for MicroStation
> and Excel would be the same color and MS Word would have another color.
> In ASP.NET I would do this by creating some temp variables to keep
> track of the previous row value and the current color. When the row
> value changes I would flip the current color value. But I am not sure
> how to have variables in Reporting Services
>|||It isn't that simple, as I also need to know what the previous rows
background color was. and I can't find a way to pull that value into
the expression
Steve Mann wrote:
> Use the Previous function to compare the previous row:
> Previous(Fields!ApplicationName.Value)
> --
> RDA Corp
> Business Intelligence Evangelist Leader
> www.rdacorp.com
>
> "mkellerm" wrote:
> > I am looking for a way to alternate the row color when a value in a
> > column changes. I am using a simple table report. The output of which
> > looks like
> >
> > Application Name Version Usages
> > MicroStation 8.1.2.1 10
> > MicroStation 8.9.2.1 4
> > MicroStation 9.1.1.1 1
> > MS Word 8.0.0.0 1
> > MS Word 9.1.2.1 2
> > Excel 9.1.2.1 2
> >
> > What I want to do is have the background color change when the
> > Application Name value changes. In this case the rows for MicroStation
> > and Excel would be the same color and MS Word would have another color.
> >
> > In ASP.NET I would do this by creating some temp variables to keep
> > track of the previous row value and the current color. When the row
> > value changes I would flip the current color value. But I am not sure
> > how to have variables in Reporting Services
> >
> >|||mkellerm,
I don't know if this will meet your needs, but perhaps it will spark an
idea. Also, this function works in SQL Server 2005, but probably not in
SQL Server 2000.
SQL Statement:
SELECT rowID, DENSE_RANK() OVER (ORDER BY applicationName) AS groupID,
applicationName, version, usages FROM applications
Results:
rowID groupID applicationName version usages
-- -- -- -- --
6 1 Excel 9.1.2.1 2
1 2 MicroStation 8.1.2.1 10
2 2 MicroStation 8.9.2.1 4
3 2 MicroStation 9.1.1.1 1
4 3 MS Word 8.0.0.0 1
5 3 MS Word 9.1.2.1 2
Then make your background color an expression like:
=Iif(Fields!groupID.Value Mod 2 = 0,"Transparent","LightYellow")
That will alternate the row color when the value changes. If you need 3
colors, use a Switch like:
=Switch(Fields!groupID.Value Mod 3 =0,"Transparent",Fields!groupID.Value Mod 3 =1,"LightYellow",Fields!groupID.Value Mod 3 = 2,"Gainsboro")
Hope that helps a little.
-Josh
mkellerm wrote:
> It isn't that simple, as I also need to know what the previous rows
> background color was. and I can't find a way to pull that value into
> the expression
>
> Steve Mann wrote:
> > Use the Previous function to compare the previous row:
> >
> > Previous(Fields!ApplicationName.Value)
> >
> > --
> > RDA Corp
> > Business Intelligence Evangelist Leader
> > www.rdacorp.com
> >
> >
> > "mkellerm" wrote:
> >
> > > I am looking for a way to alternate the row color when a value in a
> > > column changes. I am using a simple table report. The output of which
> > > looks like
> > >
> > > Application Name Version Usages
> > > MicroStation 8.1.2.1 10
> > > MicroStation 8.9.2.1 4
> > > MicroStation 9.1.1.1 1
> > > MS Word 8.0.0.0 1
> > > MS Word 9.1.2.1 2
> > > Excel 9.1.2.1 2
> > >
> > > What I want to do is have the background color change when the
> > > Application Name value changes. In this case the rows for MicroStation
> > > and Excel would be the same color and MS Word would have another color.
> > >
> > > In ASP.NET I would do this by creating some temp variables to keep
> > > track of the previous row value and the current color. When the row
> > > value changes I would flip the current color value. But I am not sure
> > > how to have variables in Reporting Services
> > >
> > >|||Josh,
Thanks, this looks promissing, but where does the DENSE_RANK function
come from SQL 2005 says it is unrecognized function name,
-Marcus
Josh wrote:
> mkellerm,
> I don't know if this will meet your needs, but perhaps it will spark an
> idea. Also, this function works in SQL Server 2005, but probably not in
> SQL Server 2000.
> SQL Statement:
> SELECT rowID, DENSE_RANK() OVER (ORDER BY applicationName) AS groupID,
> applicationName, version, usages FROM applications
> Results:
> rowID groupID applicationName version usages
> -- -- -- -- --
> 6 1 Excel 9.1.2.1 2
> 1 2 MicroStation 8.1.2.1 10
> 2 2 MicroStation 8.9.2.1 4
> 3 2 MicroStation 9.1.1.1 1
> 4 3 MS Word 8.0.0.0 1
> 5 3 MS Word 9.1.2.1 2
> Then make your background color an expression like:
> =Iif(Fields!groupID.Value Mod 2 = 0,"Transparent","LightYellow")
> That will alternate the row color when the value changes. If you need 3
> colors, use a Switch like:
> =Switch(Fields!groupID.Value Mod 3 => 0,"Transparent",Fields!groupID.Value Mod 3 => 1,"LightYellow",Fields!groupID.Value Mod 3 = 2,"Gainsboro")
> Hope that helps a little.
> -Josh
>
> mkellerm wrote:
> > It isn't that simple, as I also need to know what the previous rows
> > background color was. and I can't find a way to pull that value into
> > the expression
> >
> >
> > Steve Mann wrote:
> > > Use the Previous function to compare the previous row:
> > >
> > > Previous(Fields!ApplicationName.Value)
> > >
> > > --
> > > RDA Corp
> > > Business Intelligence Evangelist Leader
> > > www.rdacorp.com
> > >
> > >
> > > "mkellerm" wrote:
> > >
> > > > I am looking for a way to alternate the row color when a value in a
> > > > column changes. I am using a simple table report. The output of which
> > > > looks like
> > > >
> > > > Application Name Version Usages
> > > > MicroStation 8.1.2.1 10
> > > > MicroStation 8.9.2.1 4
> > > > MicroStation 9.1.1.1 1
> > > > MS Word 8.0.0.0 1
> > > > MS Word 9.1.2.1 2
> > > > Excel 9.1.2.1 2
> > > >
> > > > What I want to do is have the background color change when the
> > > > Application Name value changes. In this case the rows for MicroStation
> > > > and Excel would be the same color and MS Word would have another color.
> > > >
> > > > In ASP.NET I would do this by creating some temp variables to keep
> > > > track of the previous row value and the current color. When the row
> > > > value changes I would flip the current color value. But I am not sure
> > > > how to have variables in Reporting Services
> > > >
> > > >|||Marcus,
Not sure what is going on there... The DENSE_RANK function works just
fine for me. This MSDN page has a sample query that uses the
AdventureWorks database. Does this change anything?
http://msdn2.microsoft.com/en-us/library/ms173825.aspx
-Josh
mkellerm wrote:
> Josh,
> Thanks, this looks promissing, but where does the DENSE_RANK function
> come from SQL 2005 says it is unrecognized function name,
> -Marcus
> Josh wrote:
> > mkellerm,
> >
> > I don't know if this will meet your needs, but perhaps it will spark an
> > idea. Also, this function works in SQL Server 2005, but probably not in
> > SQL Server 2000.
> >
> > SQL Statement:
> > SELECT rowID, DENSE_RANK() OVER (ORDER BY applicationName) AS groupID,
> > applicationName, version, usages FROM applications
> >
> > Results:
> > rowID groupID applicationName version usages
> > -- -- -- -- --
> > 6 1 Excel 9.1.2.1 2
> > 1 2 MicroStation 8.1.2.1 10
> > 2 2 MicroStation 8.9.2.1 4
> > 3 2 MicroStation 9.1.1.1 1
> > 4 3 MS Word 8.0.0.0 1
> > 5 3 MS Word 9.1.2.1 2
> >
> > Then make your background color an expression like:
> > =Iif(Fields!groupID.Value Mod 2 = 0,"Transparent","LightYellow")
> >
> > That will alternate the row color when the value changes. If you need 3
> > colors, use a Switch like:
> > =Switch(Fields!groupID.Value Mod 3 => > 0,"Transparent",Fields!groupID.Value Mod 3 => > 1,"LightYellow",Fields!groupID.Value Mod 3 = 2,"Gainsboro")
> >
> > Hope that helps a little.
> >
> > -Josh
> >
> >
> > mkellerm wrote:
> > > It isn't that simple, as I also need to know what the previous rows
> > > background color was. and I can't find a way to pull that value into
> > > the expression
> > >
> > >
> > > Steve Mann wrote:
> > > > Use the Previous function to compare the previous row:
> > > >
> > > > Previous(Fields!ApplicationName.Value)
> > > >
> > > > --
> > > > RDA Corp
> > > > Business Intelligence Evangelist Leader
> > > > www.rdacorp.com
> > > >
> > > >
> > > > "mkellerm" wrote:
> > > >
> > > > > I am looking for a way to alternate the row color when a value in a
> > > > > column changes. I am using a simple table report. The output of which
> > > > > looks like
> > > > >
> > > > > Application Name Version Usages
> > > > > MicroStation 8.1.2.1 10
> > > > > MicroStation 8.9.2.1 4
> > > > > MicroStation 9.1.1.1 1
> > > > > MS Word 8.0.0.0 1
> > > > > MS Word 9.1.2.1 2
> > > > > Excel 9.1.2.1 2
> > > > >
> > > > > What I want to do is have the background color change when the
> > > > > Application Name value changes. In this case the rows for MicroStation
> > > > > and Excel would be the same color and MS Word would have another color.
> > > > >
> > > > > In ASP.NET I would do this by creating some temp variables to keep
> > > > > track of the previous row value and the current color. When the row
> > > > > value changes I would flip the current color value. But I am not sure
> > > > > how to have variables in Reporting Services
> > > > >
> > > > >

Change NULL values to default in SELECT statement

I have a stored procedure with a SELECT statement, that retrieves 1 row.

SELECT name FROM tblNames WHERE nameID = "1"

I want all the NULL values in that row to be change in some default values.

How do I do this?

You can use the IsNull Statement

SELECT isNull(name,'someDefaultValue') FROM tblNames WHERE nameID = "1"

|||

Perfect, perfect, this works great, so simple, but effective.

Now I can go celebrating Christmas.....thx.

|||

Next problem:

I call the stored procedure in the Data Access Layer via a typed dataset.

My stored procedure SELECTs 11 columns. The execute test in the stored procedure works perfect.

But when I preview the data in the DAL.xsd, I see 22 columns, with the first 11 columns having the right name, but no values and the second 11 columns having wrong names, but the right values.

What goes wrong?

|||

You will have to rename the derived columns in your select statement...

SELECT ISNULL(FirstName,'No Name') [FirstName], ISNULL(LastName,'No Last Name') [LastName]

It also sounds like you may be using SELECT *, remove the star so you don't end up with duplicate column names

change null row of roll up query

hi all

i m using rollup statement in my query. it gives me total field with null caption.

is it possible that i can change this null field with caption "total"

thanks & regards

ganesh

If I understand you question you could use following statement:

<your total field expression> as "Total"

Please, show your query

|||

Yes you can.. use the reference as the bellow query

Code Snippet

Declare @.data Table
(
Region Varchar(100),
Country Varchar(100),
Sales float
);

Insert Into @.data Values ('EMEA', 'France', 100);
Insert Into @.data Values ('EMEA', 'France', 200);
Insert Into @.data Values ('EMEA', 'Germany', 56);
Insert Into @.data Values ('EMEA', 'Germany', 12);
Insert Into @.data Values ('EMEA', 'UK', 1256);
Insert Into @.data Values ('EMEA', 'UK', 1212);
Insert Into @.data Values ('APJ', 'Japan', 130);
Insert Into @.data Values ('APJ', 'Japan', 32);
Insert Into @.data Values ('APJ', 'China', 256);
Insert Into @.data Values ('APJ', 'China', 212);
Insert Into @.data Values ('APJ', 'India', 23);
Insert Into @.data Values ('APJ', 'India', 232);
Insert Into @.data Values ('America', 'US', 23);
Insert Into @.data Values ('America', 'US', 23432);
Insert Into @.data Values ('America', 'Canada', 223256);
Insert Into @.data Values ('America', 'Canada', 2122);
Insert Into @.data Values ('America', 'Brazil', 23232);
Insert Into @.data Values ('America', 'Brazil', 223232);

Select
isnull(Case When Country is NULL Then Region + ' Total' Else Region End,'Grand Total') Region,
isnull(Country,'') Country,
Sum(Sales)
From
@.Data
Group By
Region,Country With Rollup