Display name of months in a matrix report

Question

Display name of months in a matrix report

pwalter83

SSChampion

Points: 14575
More actions
January 7, 2011 at 4:40 am

#232740

Hello,
I wish to display the name of months on a matrix report depending on the data available for each month. It should display every month name on the table header if the data for that month is available.
Does anyone know how to do that ?
Kind Regards,
Paul

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

I would set up your dataset query to not return zero or null results, that way you won't even get those months returned.

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Hi Daniel,

Thanks for your reply. However, I did not understand your soluton, could you please elaborate on what you have mentioned ?

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

Sorry, you have a matrix, my initial answer is incorrect.

Go to the text box properties >> Visibility >> choose Show or Hide based on an expression. Then write an expression that evaluates the field for data.

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Hi,

Thanks for your reply. I think I am not able to explain my question. I wish to display the name of months- January, February, March etc in the matrix report... to populate automatically in the table header based on whether the data exists for that particular month.

Along with the functionality to display name of months as per the data availability, I also need to write an expression so that the name of the months show up on the table header.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I can't help you in SSRS matricies because I don't use SSRS. I can, however, help you with a T-SQL script to do the job. Please see the following article on creating such dynamic reporting requirements...

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durai nagarajan SSCoach Points: 15778 More actions · Answer 6

Hi,

the data you are pulling from sql server should have month as a field.

then populate the month in matrix header side and the data as desired.

hope this helps.

Regards
Durai Nagarajan

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

durai nagarajan (1/11/2011)
Hi,
the data you are pulling from sql server should have month as a field.
then populate the month in matrix header side and the data as desired.
hope this helps.

Do you mean the month "field" you're talking about could be in the format of MON YYYY where MON is the 3 letter abbreviation for the month name? If so, in what order will the matrix operation sort the month?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pwalter83 SSChampion Points: 14575 More actions · Answer 8

Hi Jeff,

Thanks for your reply. What I want to know is how can I change the format of the date on the SSRS matrix report. I wish to display months name on the report in order of January, February...December.

For e.g.- convert 201001(yyyymm) to January

I have tried various syntax including monthsname etc but without any success.

Thanks,

Paul

Linda Claes Old Hand Points: 308 More actions · Answer 9

Hi Paul,

I suppose your query result will contain at least a date. What you have to do in this case is add a group expression to your matrix to group by month. Sample of such an epxression is = format(Fields!dateresult.Value, "MMM"). This will however sum the values from different years. To avoid this you better add the year as well to this expression like = format(Fields!dateresult.Value, "MMM-yyyy").

You can use the same expression to display the month name in your matrix. The format "MMM" will show the month's abbreviation eg. Jan, Feb,...

Hope this helps.

Kind regards,

Linda

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

pwalter83 (1/12/2011)
Hi Jeff,
Thanks for your reply. What I want to know is how can I change the format of the date on the SSRS matrix report. I wish to display months name on the report in order of January, February...December.
For e.g.- convert 201001(yyyymm) to January
I have tried various syntax including monthsname etc but without any success.
Thanks,
Paul

My apologies, Paul. I don't know what the syntax would be in SSRS. I normal provide such information to our report writers using a dynamic T-SQL Query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chuck-803874 SSC Veteran Points: 249 More actions · Answer 11

If you click on the texbox that contains the "Date" data you can change the Format to "XXX" by typing MMM in the "Format" field. This will change 12/31/2009 12:00:00 AM to Dec.

chuck-803874 SSC Veteran Points: 249 More actions · Answer 12

To dislpay the full name of the month use MMMM in the Format line of the textbox properties.