[sims] Edit Excel Output

[Dancer]

The SIMS analysis reports are similar to excels pivot tables.

We’re not on the June release yet so not sure about net points but certainly works separately for behaviour and achievement reports.

 

e.g. I have set a report to calculate achievement points like so

 

I’ve filter the achievement subreport on incidents after a date

You need to ensure suppress duplicates box is unchecked.

 

Run to an analysis report and changed the settings to-

 

You can change the row to reg or year group to show total points for the groups (we have rewards for best tutor group as well as students here)

 

We have just changed our reward/behaviour policy to include points so it not fully up and running yet but these are reports I’m design for next term and seem to work fine.

Ralph

[simon37]

I can definitely see the purpose of those reports Dancer, but there's no option to have pupils as rows, so I think for that purpose the only way is to export into a user defined excel (maybe rtf too, I don't know) report. I put a few reports on my googlesite last night if anyone wants to import them. sjgknight - School Data ICT

[Sivadam]

Dancer,

That screenshot looks remarkedly familiar!

 

Simon

Mine has pupils in Rows!

And is filtered by Reg Group!

Just change the Column to Total and see what happens! (Mine I mean!)

Edited July 16, 2009 by Sivadam

[Dancer]

Dancer,

That screenshot looks remarkedly familiar!

 

If only for my own professional pride I will point out that I did devise the solution independently of Sivadam's report. Which I have now looked at and yes they are remarkedly similar.

[Sivadam]

Great minds think alike eh Dancer?

 

Or is it something about fools ........................... ?

[blackwill]

I believe we are in the same flock so you decide which it is .......

[Sivadam]

I reckon that everybody knows already .............

[Paula_Whiteley]

I've followed these instructions to the letter to get Sims report data to export into an Excel Pivot Table and subsequently a graph, but something is going whacky as when I run my report I get an error message saying "error 1004 : Unable to get the PivotFields property of the Pivot Table Class". Any idea how I overcome this?

 

Thanks

[simon37]

I had issues like this most of which were related to problems in my code, so:

 

1) make sure where you refer to cells, you've selected the correct area, and you've referred in such a way that the area only ever uses 'active' cells (from row 1 to xl.end function or whatever it is so its looking at the last used row).

2) the columns have headers/titles

3) I had a problem when a-c had data in, pivot table was only looking at a-c but d also had bits of data in (but no header), so either delete unnecessary columns or make sure they have headers or something

4) the pivot tables seem not to like having rows with empty data, which when exporting from SIMS can be a bit of a problem because you often have empty cells. One way to get around this is to include in your code a replacement value, so record a macro where you select your data area and use 'replace' (ctrl+h) leave 'find what' blank and change 'replace with' to some value (probably a letter, numbers might affect formulae).

 

Hope that helps, post back if you solve/want further suggestions/help

[Paula_Whiteley]

Thanks Simon....

 

I've moved a step forward with the Excel output now showing part of my pivot table graph. However, one of the pivot table fields is missing and the error has now changed to

"Error 1004 : Method 'Cells of Object'_Global' failed.

 

Any idea what may be wrong now?

 

 

Here's the Macro that I'm trying to run (MacroX at the bottom being the problem):

 

' Excel Macro to prepare Tab-separated file ReportData.txt for printing

' written by David Stott

'

' Parameters are read from first line of report

 

' New parameters must be added to function GetParameters

' for Strings use readValue, for Booleans use readToken

 

Dim ReportTitle As String 'Title eg My Report

Dim RepeatCols As String 'FCols eg 1,2,3

Dim HorizBars As String 'HBars eg 5

Dim ShowPreview As Boolean 'Preview

Dim Landscape As Boolean 'Landscape

Dim DisplayCount As String 'DisplayCount

Dim SplitCol As String 'SplitCol eg 7,8

 

Dim RowCount As Integer

Dim ColCount As Integer

 

Function GetParameters()

 

Range("1:1").Select

Dim data As String

Do While ActiveCell.value <> ""

data = ActiveCell.value

 

ReportTitle = readValue(data, "Title", ReportTitle)

HorizBars = readValue(data, "HBars", HorizBars)

ShowPreview = readToken(data, "Preview", ShowPreview)

Landscape = readToken(data, "Landscape", Landscape)

RepeatCols = readValue(data, "FCols", RepeatCols)

SplitCol = readValue(data, "SplitCol", SplitCol)

DisplayCount = readValue(data, "DisplayCount", DisplayCount)

 

ActiveCell.Offset(0, 1).Select

Loop

 

RepeatCols = ConvertCol(RepeatCols)

SplitCol = ConvertCol(SplitCol)

 

End Function

 

' Main subroutine of Macro starts here ***********************

Sub Auto_Open()

 

On Error GoTo ErrorHandler

 

Application.Visible = False

 

ThePath = ThisWorkbook.Path

Workbooks.Open FileName:=ThePath + "\ReportData.txt"

 

' Copy the workbook, and close the source file (having marked it as saved)

Set Wbook = ActiveWorkbook

ActiveSheet.Copy

Wbook.Saved = True

Wbook.Close

 

Set ReportSheet = ActiveSheet

 

' Set Default parameters

RepeatCols = ""

SplitCol = ""

ReportTitle = ""

HorizBars = "5"

ShowPreview = False

Landscape = False

 

' Read parameters from first line of report

GetParameters

 

' Delete first line now that its work is done

Rows("1:1").Select

Selection.Delete Shift:=xlUp

 

' Calculate number of rows in report

RowCount = Range("A1").SpecialCells(xlCellTypeLastCell).Row

'if not split sheets then take the Column title into account

If SplitCol = "" Then RowCount = RowCount - 1

 

ColCount = Range("A1").SpecialCells(xlCellTypeLastCell).column

 

'Rule the columns

RuledColumns

 

' Delete top line now that its work is done

Rows("1:1").Select

Selection.Delete Shift:=xlUp

 

' Page Settings

With ReportSheet.PageSetup

.LeftFooter = "Page &P of &N"

.RightFooter = "&D &T"

.CenterHeader = "&14 " + ReportTitle

.PrintTitleRows = "$1:$1"

If RepeatCols >= "A" Then .PrintTitleColumns = "$A:$" + RepeatCols

If Landscape Then .Orientation = xlLandscape Else .Orientation = xlPortrait

If DisplayCount <> "" Then .RightHeader = DisplayCount + " " + Str(RowCount - 2)

End With

 

' Excel doesn't autofit address block properly so do a hack

FixAddressColumn

 

MacroX

 

' Set Automatic column widths

Cells.Select

Selection.HorizontalAlignment = xlLeft

Selection.VerticalAlignment = xlTop

Selection.Columns.AutoFit

 

' Embolden top line of report (column headings)

Rows("1:1").Select

Selection.Font.bold = True

 

' Add Grid Lines (Horizontal lines are added in "MakeSheets" if separate lists are requested)

If RepeatCols >= "A" Then VerticalLine (RepeatCols)

If SplitCol = "" Then HorizontalBars first:=1, cycle:=Val(HorizBars), last:=RowCount

Range("A1").Select

 

' Split list up if required

If SplitCol > "" Then MakeSheets col:=SplitCol

 

 

' Display Preview

Application.Visible = True

 

' Mark the active workbook as saved

ActiveWorkbook.Saved = True

 

If ShowPreview Then ActiveWindow.SelectedSheets.PrintPreview

'close this workbook

ThisWorkbook.Close

Exit Sub

 

' Error-handling routine

ErrorHandler:

Application.Visible = True

MsgBox "Error " & Err.Number & " : " & Err.Description

 

End Sub

 

Function readValue(data, name, store)

 

namelen = Len(name) + 1

If UCase(Left(data, namelen)) = UCase(name) + "=" Then store = Right(data, Len(data) - namelen)

readValue = store

 

End Function

 

Function readToken(data, name, store)

 

If UCase(data) = UCase(name) Then store = True

readToken = store

 

End Function

 

Sub HorizontalBars(first, cycle, last)

x = first

Do While x < last

HorizontalLine (x)

If cycle <= 0 Then x = last Else x = x + cycle

Loop

End Sub

 

Sub VerticalLine(Idx)

Columns(Idx + ":" + Idx).Select

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.weight = xlMedium

End With

End Sub

 

Function RuledColumns()

 

Range("1:1").Select

Dim data As String

Do While ActiveCell.value <> ""

data = ActiveCell.value

If Left(data, 1) = "*" Then

 

ActiveCell.EntireColumn.Select

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.weight = xlThin

End With

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.weight = xlThin

End With

End If

 

ActiveCell.Offset(0, 1).Select

Loop

 

End Function

Function FixAddressColumn()

 

Range("1:1").Select

Do While ActiveCell.value <> ""

If Left(ActiveCell.value, 7) = "Address" Then

ActiveCell.EntireColumn.ColumnWidth = 50

End If

ActiveCell.Offset(0, 1).Select

Loop

 

End Function

 

Sub HorizontalLine(Idx)

i = Trim(Str(Idx))

Rows(i + ":" + i).Select

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.weight = xlThin

End With

 

End Sub

 

Function ConvertCol(Idx)

If Idx = "" Then ConvertCol = "" Else ConvertCol = Chr(Idx + 64)

End Function

 

Sub MakeSheets(col)

 

Range(col + "1:" + col + "1").Select

caption = ActiveCell.value

ActiveCell.Offset(1, 0).Select

Dim data As String

frow = 0

value = ""

For r = 2 To RowCount

data = ActiveCell.value

If data <> value And frow <> 0 Then

If value <> "" Then

MakeSheet first:=frow, last:=r - 1, caption:=caption, descr:=value, col:=col

End If

frow = 0

End If

If frow = 0 Then

frow = r

value = data

End If

ActiveCell.Offset(1, 0).Select

Next r

 

Application.DisplayAlerts = False

ActiveSheet.Delete

Application.DisplayAlerts = True

Sheets.Select

 

End Sub

 

Sub MakeSheet(first, last, caption, descr, col)

 

Sheets("ReportData").Copy After:=Sheets(Sheets.Count)

SetSheetName (descr)

Chop first:=last + 1, last:=RowCount

Chop first:=2, last:=first - 1

With ActiveSheet.PageSetup

.CenterHeader = .CenterHeader + Chr(13) + caption + ": " + descr

If DisplayCount <> "" Then .RightHeader = DisplayCount + " " + Str(last - first + 1)

End With

 

HorizontalBars first:=1, cycle:=Val(HorizBars), last:=last - first + 3

 

Range(col + ":" + col).Select

Selection.Delete Shift:=xlRight

 

fstr = Trim(Str(last - first + 3))

lstr = Trim(Str(RowCount))

Rows(fstr + ":" + lstr).Select

Selection.Style = "Normal"

 

Columns(col + ":" + ConvertCol(ColCount)).Select

Selection.Style = "Normal"

 

Range("A1").Select

Sheets("ReportData").Select

 

End Sub

 

Sub SetSheetName(value)

For x = 1 To Len(value)

If InStr("[]?/\'", Mid(value, x, 1)) > 0 Then value = Left(value, x - 1) + "." + Mid(value, x + 1)

Next x

On Error Resume Next

ActiveSheet.name = value

End Sub

 

Sub Chop(first, last)

If last >= first Then

fstr = Trim(Str(first))

lstr = Trim(Str(last))

Rows(fstr + ":" + lstr).Delete

End If

 

End Sub

 

Sub MacroX()

'

' MacroX Macro

' Macro recorded 30/11/2009 by .

'

 

'

Cells.Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

"ReportData!A1:F220").CreatePivotTable TableDestination:="", TableName:= _

"PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Provision type")

.Orientation = xlColumnField

.Position = 1

End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

"PivotTable1").PivotFields("Provision type"), "Count of Provision type", _

xlCount

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Surname")

.Orientation = xlRowField

.Position = 1

End With

Charts.Add

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")

ActiveChart.Location Where:=xlLocationAsNewSheet

End Sub

[MattMitchell]

You're calling Cells(...) without specifying which cells you want to select! Not tested, but I'd bet good money that's what's causing the error - specify the range that you want, and then hopefully it'll work.

[mcalpine89]

I am trying to do a report in SIMS which show the Top 20 students who have most behaviour or achievement points in a specified period of time, any ideas?

[Sivadam]

See the Report that I uploaded to File Sharing on SupportNet - number 956. Edited June 1, 2012 by Sivadam

[schizoidman]

Dear all, I just want to help a member of staff in their quest to embrace SIM/Excel Macros, but we do not have the example template file ReportData.xls anywhere at all! Is anyone able to PM me a copy please?

 

Thanks, Jon.

[vikpaw]

Dear all, I just want to help a member of staff in their quest to embrace SIM/Excel Macros, but we do not have the example template file ReportData.xls anywhere at all! Is anyone able to PM me a copy please?Thanks, Jon.

 

If you have SIMS, it should be on the computer somewhere, but you might need to have created a report on the system. Or edit / import one from somewhere.

 

ReportDataWithPivotGradesMacro.xls.zip

 

No guarantees and usual caveats apply, but I found this, which already has a sub added for some kind of pivotting of grades, so you might have to remove it. Follow the guide attached earlier.

Good luck.