Use case:-<\/strong><\/em> \t\t\t=DATE(YEAR([Date of Sale]),MONTH([Date of Sale]),1)<\/p>\n EndofMonth<\/strong> —— Calculated (DateTime-Date only)\u00e2\u20ac\u201c This field is calculated using the following formulae to give the first day of the month this order was dated in. (See below for an explanation of why we use a calculated column)<\/p>\n \t\t\t=DATE(YEAR([Date of Sale]),MONTH([Date of Sale])+1,1)-1<\/p><\/blockquote>\n Be careful with the creation of the column Date Of Sale as this will be used in a CAML query later on. I didn\u00e2\u20ac\u2122t create this column and therefore the spaces get encoded in the CAML string (Date_x0020_of_x0020_Sale<\/strong>).<\/p>\n Best practice when creating columns is to create them with a concatenated name first, then edit and expand the name later. E.g. DateOfSale is created, then renamed Date Of Sale. The internal name remains DateOfSale, rather than Date_x0020_of_x0020_Sale!<\/strong><\/em><\/p><\/blockquote>\n The following data has been populated for testing (Note: I\u00e2\u20ac\u2122m using this test in September, so adjust the date of sale to match the calendar month that you\u00e2\u20ac\u2122re testing in.)<\/p>\n The test data for our chart.<\/p><\/div>\n Once you’ve got that small amount of data, we’ll also need to upload the three colour images used by the CSS in the creation of the colour coded bars for the chart.<\/p>\n These small files can be taken from here using right click and save as. (Don’t worry about the size as they are actually only 1 pixel wide.)<\/p>\n Red Yellow and Green bars for the chart.<\/p><\/div>\n Once you’ve downloaded these images, save them into the \/12\/TEMPLATES\/IMAGES\/ directory on your server (All WFE’s if installing this in a farm.)<\/p>\n The next step is to fire up SharePoint Designer and open up your website (Sandbox environment please if this is the first time!). In order to keep the look and feel nice and similar, I decided to copy the default.aspx in the root of the site, and repasted it as monthlyreports.aspx (all in SPD). Open this page in the browser and delete (not close) any webparts that are on the page. This should leave us with a nice clean page with the left nav and top chrome all visible.<\/p>\n Back to SPD and it’s time to add the DataViewWebPart. Click on the ‘left’ webpart zone in the design canvas to select it, then click on Insert\/SharePoint Controls\/DataView to place a new data view on the page.<\/p>\n SPD should look something like this now:-<\/p>\n DataViewWebPart<\/p><\/div>\n Now we have the DVWP inserted, you should now see the Data Source pane on the right hand side. Look in the library for the list we created earlier. Right click on it and choose show data. this will display the list of available fields. For our Query we’re going to need the first five columns, Title, Value, Date of Sale, Account Manager and Sales Type.<\/p>\n Fields<\/p><\/div>\n Once these are highlighted, click ‘Insert selected fields as – Multiple Item View.’<\/p>\n Once done, you should see our test data displayed in tabular form in the design view. The only problem here is that it’s all of our data, unfiltered and unsorted. At this point, you could use a filter in the data view itself, but what I want to achieve is to restrict the amount of data actually being returned by the data query in the background. To this end, we’re going to change the select command used by the DVWP to only return data for the current month.<\/p>\n Switch to the code view in SPD and find the top of the DVWP declaration in code. It should look something like this:-<\/p>\n We\u00e2\u20ac\u2122re going to replace this with our own bit of CAML query language to return just the data that we want.<\/p>\n (Note: When I originally designed this solution, I came up with the calculated columns idea that results in the query below. When I delved further into CAML date filtering, it was suggested that I look at using a So, using the StartofMonth and EndofMonth columns, we have a query that looks like this:-<\/p>\n And returns only those entries that exist within the month we\u00e2\u20ac\u2122re currently in (That\u00e2\u20ac\u2122s using the So, paste that query into the select command, save the file (don\u00e2\u20ac\u2122t worry about the ghosting message, we\u00e2\u20ac\u2122ll sort that out later if performance is a concern for you.), and you should now see a view similar to the following. (notice that I haven\u00e2\u20ac\u2122t bothered encoding the <> as SPD will do that for you when the file is saved.<\/p>\n Query in Code & Results in Design View<\/p><\/div>\n So if we now browse to our web\/monthlyperformance.aspx page, you should see a nice data form with 4 items for the current month visible. If you can see any items that shouldn\u00e2\u20ac\u2122t be there, then you may need to test your query is working using U2U\u00e2\u20ac\u2122s CAML builder.<\/p>\n At this point we now have the data that we want to represent in Bar chart format, but represented in list form. This is the point where it starts looking like code as we delve into the wonders of XSL to transform the raw list data into a nicely styled view for our director.<\/p>\n To make this easier to manage, we\u00e2\u20ac\u2122ll use an external XSL file to transform the data and point the DVWP at it. Here\u00e2\u20ac\u2122s the XSLT that we\u00e2\u20ac\u2122re going to use. Cut and paste it into notepad, save it as something useful like \u00e2\u20ac\u02dcSalesGraphsByAccountManagerForMonth.xsl\u00e2\u20ac\u009d and then drag and drop it into the root of your site in SPD.<\/p>\n The xsl file is commented heavily so should explain whats happening to enough of a degree that you can change this to sort your own ends.<\/p>\n
\n\u00e2\u20ac\u00a2\tData should be displayed as a single bar for each sales manager.
\n\u00e2\u20ac\u00a2\tData should also be broken down against the targets for Consultancy and Managed services.
\n\u00e2\u20ac\u00a2\tSales Data will be entered as Sales order numbers are generated on receipt of a firm order.
\n\u00e2\u20ac\u00a2\tSales target will be 1\/12 of the yearly target. No adjustment will be made for time of year.
\n\u00e2\u20ac\u00a2\tBar colour should change depending on % of target achieved. <50,50-99,100 will be the bands used.\n\nFirst we create a custom list called Sales that will be used to track every sale, this is created with the following fields.\n\n\n\nTitle<\/strong> ———– String – Sale comment
\nValue<\/strong> ———– Currency \u00e2\u20ac\u201c Value of the sale
\nAccount Manager<\/strong> – Lookup \u00e2\u20ac\u201c A lookup to the list of Sales Managers
\nSales Type<\/strong> —— Lookup \u00e2\u20ac\u201c A lookup to the list of Sales Types
\nClient<\/strong> ———- BDC Lookup to SalesForce (Use a string for now as it makes no difference to this example)
\nDate of Sale<\/strong> —- DateTime (Date only)
\nStartofMonth<\/strong> —- Calculated (DateTime-Date only) \u00e2\u20ac\u201c This field is calculated using the following formulae to give the first day of the month this order was dated in. (See below for an explanation of why we use a calculated column)<\/p>\n![\"The](\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2009\/10\/TestData1.jpg\" "\\"TestData\\"")
<\/a><\/a><\/a><\/a>