Archive Page 2

12
Jan

SharePoint Saturday VA Beach

I attended SharePoint Saturday in VA Beach and had a blast. I am so excited about 2010 that it’s not even funny. The most interesting thing I learned about had to be the Windows Phone 7 integration with SP2010. I’m a disappointed with some of the limitations (can’t connect to MOSS2007 from Office mobile?) but hopefully that will be fixed in an upcoming release.

28
Dec

Getting back on track

I took a little break from my app for one major reason: I think I have ADD. I switched focus to work on a streaming media application for Windows Phone 7, then I had to hurry up and get my Security+ certification for work. Not to mention the holidays were thrown in there. So now I can get back to what I want to do, and hopefully more entries will be forthcoming.

25
Oct

Initial DB Design

I have build an intial domain model.  I am just covering the very basics of what I want to track, and expect to get into tracking foods a little later.

HealthTracker DB Design

I decided to separate out blood sugar & pressure readings due to the nature of the beast.  Everything in Stats is measured at most once a day.  You would never think of taking hemoglobin A1C levels more than once every three months.  However, blood sugar levels are measured multiple times a day.  Also, it’s important to note what type of blood sugar reading it is: morning fasting, before a meal, after a meal, before bed, etc.

Also, the business intelligence guy in me started thinking about how I’d do reporting, and I whipped up a sample blood pressure chart.

Sample Blood Pressure Chart

Next on the agenda I’ll begin looking at actually writing code for these parts of the domain model, and I’ll try to get a working health tracker going.

24
Oct

HealthTracker rough design

So I’ve begun diagramming out HealthTracker.  So far here’s what I’m looking at.

HealthTrackerDiagram

Now I understand this isn’t really groundbreaking stuff, but I like to get my stuff down on “paper”, even if it’s just a rough drawing of the very basics.

I have a database design I’ll post another time.  I’ve gotten the initial “Hello World” app up and running also.  My next step is to get the very basic data entry going and I’ll deploy that out to Azure and see what it looks like.

23
Oct

New Beginnings

I’m starting a new project, affectionately called HealthTracker.  This will be a place that I can track statistics related to my health.  This is not a revolutionary product, and there are many other fitness tracking applications out there.  However, I have not found one that tracks the items I’m interested in, such as triglycerides or  blood sugars.

In addition to wanting to track this data, I also want to learn some new technology.  I tried out ASP.NET MVC back in Nov 2007 when it was first released, but had to move back to web forms because of some IIS 6 incompatibilities, the details of which I don’t remember.  Well, with the release of Visual Studio 2010 and MVC 2, I’m ready to give it another go.  I have a developer account on Azure, so that’s where it will be hosted.  Lastly, I plan on picking up a Windows Phone 7 device when they’re released, so I might as well build a Silverlight client.

That’s quite a series of new stuff for me, especially considering I’m going to try to keep things in accordance with best practices of Dependency Injection, Unit Testing, etc.  But I think it should be fun, and will definitely be a learning experience.

15
Jan

Keeping colors consistent in Reporting Services drill-through reports

I have a series of drill-through column charts, where you click on one column and it passes a parameter through to the next chart and so on, giving the appearance of drill-down.

My graphs consist of 3 types of labor, and have three colors on the main chart. When I drill down to the next chart, some of the categories do not have all three types of labor that the main one has. So the first color in the palette is assigned to the series, even though it was the second color on the previous chart. I’d like to avoid this, if possible.

So a data value is green on the first chart (2nd in the color order) and yellow on the next chart (1st in the color order). I needed to make the graphs “remember” the total number of series groups that were in the first chart.

I was able to solve this because I was using a custom color palette, implemented as a hash table. I basically serialized this information and passed it to a hidden parameter on the subreport and then reinflated the data structure.

It’s not perfect, but it works for now.

' Define some globals, including the color palette  
Private colorPalette As String() = _ 
    {"#FFF8A3", "#A9CC8F", "#B2C8D9", "#BEA37A", "#F3AA79", "#B5B5A9", "#E6A5A4", _ 
     "#F8D753", "#5C9746", "#3E75A7", "#7A653E", "#E1662A", "#74796F", "#C4384F", _ 
     "#F0B400", "#1E6C0B", "#00488C", "#332600", "#D84000", "#434C43", "#B30023"} 
     ' color palette pulled from SAP guidelines 
     ' http://www.sapdesignguild.org/resources/diagram_guidelines/color_palettes.html 
Private count As Integer = 0 
Private colorMapping As New System.Collections.Hashtable() 
' Create a custom color palette 
Public Function GetColor(ByVal groupingValue As String) As String 
    If colorMapping.ContainsKey(groupingValue) Then 
        Return colorMapping(groupingValue) 
    End If 
    Dim c As String = colorPalette(count Mod colorPalette.Length) 
    count = count + 1 
    colorMapping.Add(groupingValue, c) 
    Return c 
End Function 
 
' In custom actions of the data value, set the results of this  
' function to the mapping parameter in the next report 
Public Function PassColorMapping() As String 
    If colorMapping.Count = 0 Then 
        Return Nothing 
    End If 
    Try 
        ' convert the hashtable to an array so it can be serialized 
        Dim objHash As Object()() = ToJaggedArray(colorMapping) 
 
        ' serialize the colorMapping variable 
        Dim outStream As New System.IO.StringWriter() 
        Dim s As New System.Xml.Serialization.XmlSerializer(GetType(Object()())) 
        s.Serialize(outStream, objHash) 
 
        ' move on to the next report 
        Return outStream.ToString() 
    Catch ex As Exception 
        MsgBox(ex.Message) 
    End Try 
End Function 

I ran into an issue where I couldn’t find the equivalent of the onLoad event for the report. Since I wasn’t sure where to put this inflate code, I stuck it in the background color of the plot area. Hence I always return “WhiteSmoke”. I’ll change this if I can find the right place to put it.

' Call this function when the report loads to get the series groups 
' that have already been loaded into the custom color palette 
' Pass in the parameter used to store the color mapping 
Public Function InflateParamMapping(ByVal paramMapping As Parameter) As String 
    Try 
        If paramMapping.Value Is Nothing Then 
            Return "WhiteSmoke" 
        ElseIf colorMapping.Count = 0 Then       
            Dim pXmlized As String = paramMapping.Value 
            ' deserialize the mapping parameter 
            Dim s As New System.Xml.Serialization.XmlSerializer(GetType(Object()())) 
 
            ' get the jagged array and convert to hashtable 
            Dim objHash As Object()() = DirectCast(s.Deserialize(New System.IO.StringReader(pXmlized)), Object()()) 
            ' stick the result in the global colorMapping hashtable 
            colorMapping = ToHashTable(objHash) 
            count = colorMapping.Count 
        End If 
    Catch ex As Exception 
'       MsgBox(ex.Message) 
    End Try 
    Return "WhiteSmoke" 
End Function 

ToJaggedArray() and ToHashTable() are helper functions because a HashTable is not serializable since they implement an IDictionary. I was in a hurry so I just converted them to an array right quick. Code comes from the Collection Serialization in ASP.NET Web Services article written by Mark Richman. I converted the code from C# to VB.NET to use in the report.

Public Function ToJaggedArray(ByVal ht As System.Collections.HashTable) As Object()() 
    Dim oo As Object()() = New Object(ht.Count - 1)() {} 
    Dim i As Integer = 0 
    For EAch key As Object in ht.Keys 
        oo(i) = New Object() {key, ht(key)} 
        i += 1 
    Next 
    Return oo 
End Function 
 
Public Function ToHashTable(ByVal oo As Object()()) As System.Collections.HashTable 
    Dim ht As New System.Collections.HashTable(oo.Length) 
    For Each pair As Object() In oo 
        Dim key As Object = pair(0) 
        Dim value As Object = pair(1) 
        ht(key) = value 
    Next 
    Return ht 
End Function 

Now in the report itself you need to do a couple things.

  • Add a reference to System.Xml in Report Properties in both reports.
  • In the Actions of your parent report, set the Parameter containing your data structure to =Code.PassColorMapping()
  • In the Plot Area section of your report, put this expression for the background: =Code.InflateParamMapping(Parameters!colorMapping)
  • And of course, in the Fill for your data Series Style on both charts put this expression: =Code.GetColor(Fields!Type.Value)

You can continue doing this for as many subreports as you want – I currently have 3 levels of drill-through and it works fine.

09
Oct

Divide by Zero Error in BIDS

I have a budget report in BIDS 2005 where I show three dollar amounts: Required Amount, Funded Baseline, and Shortfall. Then there’s a percentage showing Shortfall/Required. It’s pretty standard stuff. The problem is that I have one budget item that requested no money: their Required Amount is zero. Their shortfall is zero, and so their shortfall % should be zero as well. Here’s what I have:

That formula produces this:

The relevant line is at the bottom, where I get a divide by zero error. This threw me for a loop, so I changed the expression in Shortfall % to this:

This produced the expected results:

Solution:

James Hunter came in with the solution to this problem. Required_Amt was still being evaluated although not used. So he suggested embedding another if statement like so:

25
Sep

Using SharePoint List as Data Source for PivotChart

In Dashboard Designer, I create a new PivotChart report. Then I click the Chart Wizard icon, right beside the help button. This brings up the Commands and Options dialog.

When you click the Connection… button, you switch to the next tab where you choose the type of connection.

When you click on Edit under the Data Details tab, the Select Data Source dialog opens.

Clicking on the New Source… button opens up the Data Connection Wizard.

Select the Data retrieval services from the list box and click Next >. You will then select Windows SharePoint Services lists from the list box.

Click Next > and you will specify your SharePoint site location in the next window.

Click Next > and you will then select the list containing your data.

Now we get to select the fields that we want to query. Notice the pretty names in the Selected Columns list box.

Select your Sort Order and specify a Filter, if desired.

You can limit the number of rows returned, if desired.

Now save the Data Connection File and click Finish.

Now in the Commands and Options window that appears you can see that you are actually running a dsQuery on the site specified in Connection. If you open the uxdc file you created in Notepad, you’ll see that it’s just one big SOAP query, hitting the DspSts.asmx web service for your site. FYI.

Now we’ve reached the meat of our chart creation. I’ve created the chart, and you can see that while the chart is displaying my data perfectly, I have no choice in the names displayed in the legend. There are two glaring issues. The first is that the spaces are replaced with _0020_. The second is that when you drag Funded_0020_Amount to the chart, it automatically aggregates this for you, creating a new Total called Sum of Funded_0020_Amount. You can change the aggregation type to Average, Count, Min, Max, Variance, etc. But you still get Count of Funded_0020_Amount, Variance of Funded_0020_Amount, etc.

You can’t right-click and the field and do anything except delete it.

If you click on the Legend Entry and open the Commands and Options window, you can just set the font properties.

As a last resort I attempted to enter an Alias into the actual dsQuery, but that just reset my chart. The Field List doesn’t pick up my Aliases.

Unfortunately, I haven’t found a way to get pretty names on the chart. If you know a way to change that query to use the Alias, let me know and I’ll be happy to update this blog entry. A workaround is to create very descriptive Axis labels.

21
Sep

PIVOT and UNPIVOT

I finally had the need to use PIVOT and UNPIVOT in SQL Server 2005 while creating a pretty large report for a client.
The report needed to show a number of groups and how much of their budget mapped to certain strategic objectives. This is a pretty standard crosstab report, but the problem is that the numbers don’t really exist anywhere – it’s all percentages of the aggregated sum of the amounts budgeted to different projects.
The previous iteration of the report just had hard-coded textboxes, mimicking a crosstab report but was really just a bunch of textboxes on the page. (The previous developer was under the gun for a proof of concept, and ended up winning us the contract to put this into production.)
The data looked something like this:

GroupName ProjectName BudgetedAmount FundedAmount FiscalYear
Group1 Project1 $500 $450 FY09
Group1 Project2 $300 $300 FY09
Group2 Project3 $1000 $950 FY09
Group3 Project4 $750 $600 FY09

I added a table like this:

FiscalYear GroupName StrategicObjective Percentage
FY09 Group1 Obj1 .50
FY09 Group1 Obj2 .50
FY09 Group2 Obj2 .75
FY09 Group2 Obj3 .25
FY09 Group3 Obj4 1.00

The query to apply the percentage and aggregate this data looks like this:

SELECT tt.GroupName, 
	tt.FiscalYear, 
	tf.StrategicObjective, 
	tf.Percentage * tt.FundedAmount AS Portion
FROM TestTable tt 
JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
AND tt.GroupName = tf.GroupName
WHERE tt.FiscalYear = 'FY09'

Producing:


Then I used the PIVOT query to produce the dataset I needed for my report:

SELECT GroupName, [Obj1], [Obj2], [Obj3], [Obj4]
FROM ( 
	SELECT tt.GroupName, 
		tt.FiscalYear, 
		tf.StrategicObjective, 
		tf.Percentage * tt.FundedAmount AS Portion
	FROM TestTable tt 
	JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
	AND tt.GroupName = tf.GroupName
	WHERE tt.FiscalYear = 'FY09'
) p
PIVOT (
	SUM(Portion)
	FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
) AS pvt

Producing:


If I need to produce another type of graphic showing the aggregate data again (bar charts or something mapped to the objectives) then I can do an UNPIVOT like this:

SELECT StrategicObjective, SUM(Amt) AS Amount
FROM (
	SELECT GroupName, [Obj1], [Obj2], [Obj3], [Obj4]
	FROM ( 
		SELECT tt.GroupName, 
			tt.FiscalYear, 
			tf.StrategicObjective, 
			tf.Percentage * tt.FundedAmount AS Portion
		FROM TestTable tt 
		JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
		AND tt.GroupName = tf.GroupName
		WHERE tt.FiscalYear = 'FY09'
		) p
	PIVOT (
		SUM(Portion)
		FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
	) AS pvt
) p2
UNPIVOT (
	Amt FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
) AS unpvt
GROUP BY StrategicObjective

This produces the following resultset:


In conclusion, this could all be accomplished with CASE statements, but I think this is much cleaner and frankly easier to write and read. That hardcoded ‘FY09′ can just be replaced with a parameter in the report and we’re good to go for future years.

21
Sep

Synchronize Excel 2003 to a SharePoint List

This is for those people who are unfortunate enough to be working with Office 2003. It’s crazy, but some clients are still working with the older versions of things.

  1. Get your data straightened out in your spreadsheet – replace N/A with a blank cell, replace — with 0, only one header, etc.
  2. Remove all spaces and strange characters from your header (Dashboard Designer chokes on some of these)
  3. In Excel 2003, Data –> List –> Create List (+L).

    Make sure that you have all your data selected, and check the box “My list has headers” (if it applies). Click OK.
  4. Data –> List –> Publish List. This will bring up a dialog, which you then use to enter the site collection name other info. Check the box that links the sheet to the SharePoint list.
  5. Next you will make sure that your columns are the proper data type. If they are not right, Cancel and fix the data in the columns (remove “-” from a numeric column, etc).
  6. If you checked the box that links your spreadsheet to the SharePoint list, you can go to Data –> List –> Synchronize List and get the latest updates.