posted 9/14/2010 by TomLannen - Views: [3891]
I solved this using the AdventureWorks2008R2 Cube so some of the field names will obviously have to be changed to fit your environment.
1. I kept it pretty simple when building my dataset. I added Month, Date, and Sales Amount.
2. Add a Smooth Line Chart, or whichever you prefer, into the body of your report.
a. Add Month to the “Category Group”
b. Add “Sales Amount” or in your case I believe it was “Value” to the Values Group
c. Add whatever field you feel necessary if any to the “Series Group” (I believe you were going to pull product in here)
3. Right click on the Horizontal Axis Label and select “Show Major Gridlines”
4. Right click on one of the Major Gridlines and select “Major Gridline Properties” This is where you will set the grid line intervals by writing an expression, and set the color and the width of the gridlines. In my example I set the color to Red and the width to 2pt
5. Cut and paste this expression into the expression builder. *Note* you may have to edit the fields to match your environment.
a. =CDate(year(Fields!Date.Value) & "/" & month(Fields!Date.Value) & "/01").AddDays(-1)
b. When you select a value for your date parameter this will give you the last day of the previous month.
6. Click OK. You should now have vertical lines of your specified color on your line chart for the last day of every month.
7. Now we are going to set the tick marks and the labels for the tick marks to match the intervals for the grid lines
a. Right click on the Horizontal Axis Label and select “Horizontal Axis Properties”
b. Select the Major Tick Marks Tab
c. At the bottom of that text box is where we are going to use a custom expression to set the interval for the Major Gridlines. To the right of the interval drop down menu select the fx button
8. Cut and paste this expression into the expression builder. *Note* you may have to edit the fields to match your environment.
9. From the Chart Data pane right click on Month in the Gategory Groups and select Properties
10. Select the fx button next the Label dropdown menu
11. Cut and paste this expression into the expression builder. *Note* you may have to edit the fields to match your environment.
a. =Left((CDate(year(Fields!Date.Value) & "/" & month(Fields!Date.Value) & "/01").AddDays(-1)),10) and click “OK”
b. *Note* the Left(x,x) may not be necessary in your environment. In the AW cube the date field returned the mm/dd/yyyy and hh:mm:ss which is way too big for the label on the Horizontal axis.
12. With the Category Group Properties window still open select the “Sorting” tab and then “Add” and then click “fx” button.
a. Insert the same statement as the one above including any edits you might have done to it and click OK
b. Click OK to close the Category Group Properties window
15. Run the report.