How do I add a static column to a matrix?

Folks,
I'm getting data back from the database in the following format:
row_id | col_Name | col_Value
0 | ID | 10
0 | Name | First
0 | Jan | 1
0 | Feb | 2
1 | ID | 20
1 | Name | Seconds
1 | Jan | 10
1 | Feb | 20
...
We are grouping based on the row_id db column, thus creating dynamic columns
for ID, Name, Jan, Feb, etc.
However, I need to customize the ID and Name column which is, to my
understanding, not something that can be done if they are dynamic columns in
the matrix.
So, what I was thinking of doing was to make two static columns, ID and
Name, and then filter those rows out of the dynamic columns. However, when I
make a second ColumnGrouping in the RDL file for these, and define the two
static columns, what happens is that they appear set up such that these
columns would be in a separate row, rather than off to the left of the rows
of dynamic columns (hopefully I'm explaining this well enough).
What would be the best way to accomplish this, presuming that we can't
simply have the returned data changed to a better format?
Thanks!
randyAssuming you want to transform your table to:
ID | Name | Jan | Feb
10 | First | 1 | 2
20 | Seconds | 10 | 20
etc.
The easiest is to tweak your SQL, but I am assuming this is the result of a
SP that you can't since you said:
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
I'm not sure if you can, but you might have to do this with a custom code
module to transform your data and then bind the report to the dataset from
code. Maybe someone else knows way to modify the underlying dataset
structure.
"Randy Chapman" <RandyChapman@.discussions.microsoft.com> wrote in message
news:5F87A271-7591-4453-975F-06394CC8E2A8@.microsoft.com...
> Folks,
> I'm getting data back from the database in the following format:
> row_id | col_Name | col_Value
> 0 | ID | 10
> 0 | Name | First
> 0 | Jan | 1
> 0 | Feb | 2
> 1 | ID | 20
> 1 | Name | Seconds
> 1 | Jan | 10
> 1 | Feb | 20
> ...
> We are grouping based on the row_id db column, thus creating dynamic
> columns
> for ID, Name, Jan, Feb, etc.
> However, I need to customize the ID and Name column which is, to my
> understanding, not something that can be done if they are dynamic columns
> in
> the matrix.
> So, what I was thinking of doing was to make two static columns, ID and
> Name, and then filter those rows out of the dynamic columns. However,
> when I
> make a second ColumnGrouping in the RDL file for these, and define the two
> static columns, what happens is that they appear set up such that these
> columns would be in a separate row, rather than off to the left of the
> rows
> of dynamic columns (hopefully I'm explaining this well enough).
> What would be the best way to accomplish this, presuming that we can't
> simply have the returned data changed to a better format?
> Thanks!
> randy
>

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format data in single MDX expression rather than MDX statement?

Hi, experts,

Thanks for your kind attention.

How can we format data by a single MDX expression rather than MDX statement? (e.g. I want to format measure member A with 2 decimal places. How can we achieve this in a single MDX expression?, not in complex MDX statement).

Hope it is clear for your help.

Thanks and I am looking forward to hearing from you shortly.

With kind regards,

Yours sincerely,

Hi,

Not to sure what you mean by expression rather than MDX Statement.

In a query it might look like:

with member [measures].[Percentage Of Region Total] as

[Measures].[Charge Out]/

([Measures].[Charge Out],[Campaign].[Campaign Region Hierarchy].[All Campaign Regions]),

format_string ='#.##%'

In the calculation script another might look like

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Rev Variance]

AS ([Measures].[Sales Revenue])-([Measures].[Sales Revenue],[Revenue Recognition Date].[Financial Year].currentmember.prevmember),

FORMAT_STRING = "￡#,#",

NON_EMPTY_BEHAVIOR = { [Sales Revenue] },

VISIBLE = 1;

Hope that helps

Matt

|||

Or in the MDX Script of the cube with

Code Snippet

format_string([Measures].[Discount])="#,#.00";

HANNES

|||

Hi, Thanks very much, Matt and HANNES.

With kindest regards,

Yours sincerely,

how can Unzip file text file using SSIS

Hi,

I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.

Run your favorite unzip utility (there are plenty of gzip-compatible archivers out there, including gzip itself) using Execute Process task.

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:

I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:

i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim