Pico

Thanks for the updates about the IP addresses. Our sync was not working because the new IPs weren't listed in our whitelist for the API. It's now working

Is anyone else using iSAMS? Their status page says that the sync process is now working for all MISs, but it's still failing for us in the Parents Evening System.

Data Manager at The Manchester Grammar School This new role provides an exciting opportunity for someone with a methodical mind set to work in one of the country’s leading independent day schools. The Manchester Grammar School is one of the oldest, largest and most successful boys’ schools in the country, with a vibrant and diverse community of some 1600 pupils and 300 staff. As Data Manager you will enjoy working with people, providing first-line support to users of our management information system (iSAMS) and engaging in back-end problem solving. The management information system (MIS) is integral to many aspects of the School’s day-to-day functioning, including attendance, admissions and timetabling, as well as pastoral, medical and academic information. It also serves as a key tool for communication between staff, pupils and parents. A knowledge of SQL and/or Power BI is a desirable requirement for the post, but not essential. Further information about the role and an application pack are available here: https://www.mgs.org/544/vacancies The closing date for applications is 9am, Monday 31 October 2022. Feel free to PM if you have any questions about the role.

** Final bump **

** Bump! ** Come and work for MGS!

The Manchester Grammar School has created a new role to help take forward the school's use of its new MIS, iSAMS. This is a full time, all year round role. Further details and an application form are available here: https://www.mgs.org/vacancies Please feel free to PM if you would like further information. This is a great opportunity for data managers with strong technical skills or for someone looking to move up into a role with more responsibility.

Director of ICT Full-time All Year Round An opportunity has arisen for an experienced individual to provide strategic leadership on all aspects of teaching and non-teaching IT provision across Berkhamsted Schools Group. Experience of managing a large infrastructure with VMWare as a hypervisor is essential. Further details and an application form can be found on the school website at http://www.berkhamstedschool.org/director-of-ict. Completed application forms should be emailed to [email protected]. Berkhamsted School is committed to safeguarding and promoting the welfare of children and expects all staff and visitors to adopt a child centred and coordinated approach to child protection and safeguarding. Registered Charity No. 310630.

Yeah, you can see the effect of the Gulf Stream on the Dymaxion map (because it displays mean low annual temperature using colour gradients). Unfortunately it's not obvious from the image in the link I posted that there's a distinct temperature range for north-western Europe.

Nice map! I have a somewhat faded poster of Buckminster Fuller's stunning Dymaxion Airocean World on my office wall. I've seen maps that show the arctic ice cap, but this one doesn't. (I had a geographer come into my office one time who explained that some of his students got confused when they found out you could send submarines under it.) And, yes, this is the same Buckminster Fuller of Buckminsterfullerene fame

In Part One I gave an overview of why and how you might implement tab strips in SSRS reports using tables in your database to manage the strips and formatting. In Part Two I gave you the necessary SQL. There is now just a little bit more hard work to do in this final step to get the required SSRS report template set up in BIDS/Visual Studio. We will create a single report to test the effect of what we've set up and then turn this into a template for all future work. At the end of Part One I made some suggestions as to how you can build upon this method, so if you like this approach (you might not!), you should refer back to that before finalising your template. Create a New Reporting Services Project First off, create a new Reporting Services project and add a shared data source to the project for the database you've added your tables to. We will also add a shared dataset called TabConfiguration.[1] This dataset will pull data from the view you created in Part Two: [color=blue]SELECT[/color] TabConfiguration.TabStripID , TabConfiguration.TabStripName , TabConfiguration.TabNameID , TabConfiguration.TabName , TabConfiguration.ReportName , TabConfiguration.TabPositionInTabStrip , TabConfiguration.TabDescription , TabConfiguration.ActiveBackgroundColour , TabConfiguration.InactiveBackgroundColour , TabConfiguration.ActiveBorderColour , TabConfiguration.InactiveBorderColour , TabConfiguration.ActiveBorderStyle , TabConfiguration.InactiveBorderStyle , TabConfiguration.ActiveForegroundColour , TabConfiguration.InactiveForegroundColour , TabConfiguration.ActiveFontWeight , TabConfiguration.InactiveFontWeight , TabConfiguration.ActiveFontName , TabConfiguration.InactiveFontName [color=blue]FROM[/color] dbo.TabConfiguration; Getting Started with the Template Now we are ready to create the report. There are many steps in the following, but hopefully they are all individually straightforward. To begin with, we won't worry about formatting everything nicely. Create a new report and call it "Tab Home - Home Page". This will become the basis for our template. Add to this report an internal integer parameter called TabNameID. There's no need to enforce available values (it's internal). Give it a default value of 1. Add a reference to the shared data source you created and then add a data set that references the shared data set you created. Call it TabConfiguration to match the name of the shared dataset. Add a table report item to your report and attach the TabConfiguration data set to it. Right-click to delete the row group. When the dialog comes up, confirm to delete the row and associated groups: Delete two of the remaining columns, so there is only one left. Now add a new column group adjacent right to the remaining cell by right-clicking the cell. Select TabNameID as the Group By option. In the cell itself, select TabName as the field data to display. Add a column Inside Group to the right, but don't put any value in the cell. This will provide a spacer to the right of each tab. (See the screenshots in Part One to see the effect we are looking for.) Add a new row to the table. This will be filled with the same colour as the active tab and will help to visually reinforce the currently selected tab. Here is what your report should currently look like: Restricting the Data to the Appropriate Tab Strip Let's take a preview of how the report works so far. At this stage we've pulled through all the data, but we're not restricting it to just the tabs we're interested in and the tabs are not being displayed in the right order: Sorting the tabs is easy as this is already determined by the TabPositionInTabStrip column in the data set. Right-click the Column Group named TabNameID and select the Group Properties dialog: Under the sorting options select TabPositionInTabStrip: Now our tabs should display in the correct order. But, we still need to restrict the list of displayed tabs. The data set we have created pulls through all the tabs defined in our database. But, we only want to display those tabs that appear on the same tab strip as our current report. For example, our current report has a TabNameID of 1. We therefore need to filter the dataset to all those tabs that are part of the same tab strip as the current report (in this case TabStripID 1). SSRS has a Lookup function that works in a similar way to VLOOKUP in Excel. We will use it to do a little bootstrapping as follows: lookup up the TabStripID of the current report by matching the report parameter TabNameID to the field value TabNameID in the TabConfiguration data set and retrieve the value for TabStripID from that row of the data set. The resulting TabStripID will then be used for filtering the TabConfiguration data. Go to the Filters page on the Group Properties dialog and click Add. Apply the following filter: Select TabStripID under Expression. Next to where you enter the Value is an expression button (marked fx). Click the expression button and enter the following expression: =Lookup(Parameters!TabNameID.Value, Fields!TabNameID.Value, Fields!TabStripID.Value, "TabConfiguration") When you have added the expression, the filter dialog should look like this: Click OK to apply the changes to the group. Now when we re-view the report, we get the expected tabs displayed in the correct order: Apply Formatting to the Tab Strip We have now got the right data displayed; now we need to format everything so it looks reasonably smart and takes into account the options we have set up in our database. Here are our initial steps: Let's hide all the borders, i.e. BorderStyle = None. Set the left and right most columns to have a width of 0.2cm. Set the middle column to have a width of 4cm. Center align the text in the middle column. Here's what our report should now look like: Already this looks better than before, but we still need to add in the colour scheme. In the following we will just use the Properties window. To begin with, we want to set the correct background colour for the TabName cell. We have already filtered the data set in the table to a single tab strip. All we now need to know about each cell in the column group is whether or not it has the same TabNameID as the report parameter. If it does, then this column of the table represents the active tab of the tab strip. Select the cell that displays the TabName (by default this should be called TabName). In the Properties window (F4 if it is not displayed), select BackgroundColor. Select Expression... Enter the following expression: =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveBackgroundColour.Value, Fields!InactiveBackgroundColour.Value) To see the effect: Hopefully, you can now see how all the different elements will come together, but let's first finish off the rest of the formatting. To sort out the borders: Expand the BorderColor option Under Default, select Expression... and enter the following: =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveBorderColour.Value, Fields!InactiveBorderColour.Value) Expand the BorderStyle option Under Default, select Expression... and enter the following: =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveBorderStyle.Value, Fields!InactiveBorderStyle.Value) To sort out the font colours: Select Color from the Properties window and add the following expression =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveForegroundColour.Value, Fields!InactiveForegroundColour.Value) To sort out the font options: Expand the Font options in the Properties window Under Font Family, select Expression... and add the following expression =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveFontName.Value, Fields!InactiveFontName.Value) Under Font Weight, select Expression... and enter: =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Fields!ActiveFontWeight.Value, Fields!InactiveFontWeight.Value) Preview the report again to see the effect: Let's add in some colour to the bottom row to make the selected tab stand out more. For each of the three cells in the bottom row of the table, use the Expression... option for the BackgroundColor and use the following expression: =Lookup(Parameters!TabNameID.Value, Fields!TabNameID.Value, Fields!ActiveBackgroundColour.Value, "TabConfiguration") Finally, position the table so that it sits flush against the left edge of the page. Checking it all Works There are still a few more things to take care of, but we are already in a position where we can see how this works in practice. Change the value of the TabNameID parameter to one of the others in your database, e.g. 7. This is a tab on a different tab strip and therefore the colour scheme needs updating, along with the change in display of text. Preview the report with this new parameter: This means that once this report is ready to be used as a template, we only need to ensure the that TabNameID parameter and the report name have been set correctly. Change the TabNameID parameter back to 1 and save your report. Now we just need to add the links in so the tabs have drill-through functionality. Right-click the TabName text box and select TextBox Properties. Select the Action page from the dialog. Select the option "Go to report" In the screenshot below, select the fx (expression) button: Add the following code as your expression: =Fields!ReportName.Value Click Ok to accept all your changes and save your report. We are now going to test this works by quickly creating a second report which we will link to. Copy the current report in your project with a new name: "Tab Home - Academic". The easy way to do this: Select the current report in Solution Explorer Press CTRL+C Press CTRL+V Right-click the new report and rename it to "Tab Home - Academic" Double-click the newly-created report to open it up and change the TabNameID parameter in this report to 2 Preview the report to make sure it displays as expected. Preview the report "Tab Home - Home Page". Whilst still in Preview mode click on the Academic Groups tab (the other tabs won't work yet because you haven't created the reports). You should now be viewing the Academic Groups tab in BIDS/Visual Studio. If you get an error, you've missed a step in the above. Click on the Home tab to go back to your original page. Go back to Design view. Adjust the expression in the Action dialog that you previously set: =IIf(Parameters!TabNameID.Value = Fields!TabNameID.Value, Nothing, Fields!ReportName.Value) You will need to do this in the "Tab Home - Academic report" too. Test the effect in Preview mode. Once you are happy with what you have done, rename the report to something sensible (e.g. "Template - Tab Strip") and copy the report to your templates directory. (You do not need to copy across data sets or data sources, but when you use the template, you do need to ensure these are set up in your project.) I am using Visual Studio 2013 on a 64 bit machine, so my path looks like this: C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject The path for you will probably be slightly different, but the key part is highlighted in bold: Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject Now when you select Add >> New Item, you will see your template listed as a report type. (You may need to restart BIDS/Visual Studio before it appears in the list.) All the hard work has been done in the template, so you just need to remember to name the new report correctly and to update the TabNameID parameter. I recommend that you put a text box on the template with instructions on how to use it. Footnotes Shared data sets were introduced in SSRS 2008 R2. If you update the fields used in the shared data set, you still need to manually refresh the fields in the reports that use it.

In Part One I described one approach to creating tab strips in SSRS. In this part I will go over the necessary table design in the database. Nothing hinges on the fact that I use SQL Server, so the following T-SQL code should be adaptable for other DBMSs. We need two tables and one view to set up our tab strips. I've given some code towards the end that will populate these tables with some sample data that we will use in Part Three. Tab Strips Table A tab strip, in this context, is a grouping construct and the primary key for each tab strip is an arbitrary numerical identifier. In the following SQL, I have indicated background and foreground colours for both the active (selected) tabs and for inactive (unselected) tabs, as well as a number of other features. You could have far more extensive formatting information than included here. Or, you could decide not to implement the formatting columns at all. You could also add in a column containing a description of the tab strip. This could be displayed on reports as useful information or guidance to the user about the area of the reporting system they are in. If you want to make use of soft deletion, you would need to include a column to be used for flagging whether the tab strip has been deleted. Although I've not included that here, I do recommend this as this makes life easier once you have gone live with this set up. [color=blue]CREATE TABLE[/color] [dbo].[TabStrips]( [TabStripID] [[color=blue]int[/color]] [color=blue]IDENTITY[/color](1,1) [color=blue]NOT NULL[/color], [TabStripName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ActiveBackgroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [inactiveBackgroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ActiveForegroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [inactiveForegroundColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ActiveFontWeight] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [inactiveFontWeight] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ActiveBorderColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [inactiveBorderColour] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ActiveBorderStyle] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [inactiveBorderStyle] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ActiveFontName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [inactiveFontName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [color=blue]CONSTRAINT[/color] [PK_TabStrips] [color=blue]PRIMARY KEY[/color] CLUSTERED ( [TabStripID] [color=blue]ASC[/color] )[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]] ) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]] ; [color=blue]GO[/color] Tab Names Table A tab name is a single tab that appears on a tab strip. The primary key for each individual tab is just an arbitrary numerical identifier, but there is a required foreign key reference to the tab strips table that indicates which tab strip each tab is to appear on. I have added a constraint that the tab name is unique for any particular tab strip. (The name of the tab is the text that is displayed to the user on the report, so this prevents two tabs appearing on the same tab strip with the same text.) We will be using column groupings in the SSRS report to display the tab strip. When a user clicks on a cell, that cell will have a particular TabNameID associated with it. But it also requires an Action property to control which report gets displayed when it is clicked on. So, this table also stores the name of the associated report. Depending upon how you organise your reports on the server, you may also require a uniqueness constraint on the report name. If you are making use of a folder structure, then the report name needs to be unique relative to its path. In the following table, I've placed a uniqueness constraint directly on the report name as I'm assuming all the reports are in the same folder, which simplifies the drill-through links. If your links needed to take into account a folder structure, that would need adding to the table too, and you would also need to adjust the expression used in the SSRS template. I've included a tab description column as this might be something that you want to display on reports in several places. I haven't included a column for flagging a soft delete, but I do recommend it as this aids the overall management of the tab strips. [color=blue]CREATE TABLE[/color] [dbo].[TabNames]( [TabNameID] [[color=blue]int[/color]] [color=blue]IDENTITY[/color](1,1) [color=blue]NOT NULL[/color], [TabStripID] [[color=blue]int[/color]] [color=blue]NOT NULL[/color], [TabName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [ReportName] [[color=blue]varchar[/color]](50) [color=blue]NOT NULL[/color], [TabPositionInTabStrip] [[color=blue]int[/color]] [color=blue]NOT NULL[/color], [TabDescription] [[color=blue]varchar[/color]](1000) [color=blue]NOT NULL[/color], [color=blue]CONSTRAINT[/color] [PK_TabNames] [color=blue]PRIMARY KEY[/color] CLUSTERED ( [TabNameID] [color=blue]ASC[/color] )[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]], [color=blue]CONSTRAINT[/color] [CK_TabNames_UniqueReportName] [color=blue]UNIQUE[/color] NONCLUSTERED ( [ReportName] [color=blue]ASC[/color] )[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]], [color=blue]CONSTRAINT[/color] [CK_TabNames_UniqueTabNameForTabStrip] [color=blue]UNIQUE[/color] NONCLUSTERED ( [TabStripID] [color=blue]ASC[/color], [TabName] [color=blue]ASC[/color] )[color=blue]WITH[/color] (PAD_INDEX = [color=blue]OFF[/color], STATISTICS_NORECOMPUTE = [color=blue]OFF[/color], IGNORE_DUP_KEY = [color=blue]OFF[/color], ALLOW_ROW_LOCKS = [color=blue]ON[/color], ALLOW_PAGE_LOCKS = [color=blue]ON[/color]) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]] ) [color=blue]ON[/color] [[color=blue]PRIMARY[/color]] ; [color=blue]GO[/color] [color=blue]ALTER TABLE[/color] [dbo].[TabNames] [color=blue]WITH CHECK ADD CONSTRAINT[/color] [FK_TabNames_TabStrips] [color=blue]FOREIGN KEY[/color]([TabStripID]) [color=blue]REFERENCES[/color] [dbo].[TabStrips] ([TabStripID]) ; [color=blue]GO[/color] [color=blue]ALTER TABLE[/color] [dbo].[TabNames] [color=blue]CHECK CONSTRAINT[/color] [FK_TabNames_TabStrips] ; [color=blue]GO[/color] A View to Construct All the Tab Strips The following view fills out the information for each individual tab so that we know its associated colour scheme. If you make use of soft deletion, you would need to include that as part of the view's logic. [color=blue]CREATE VIEW[/color] [dbo].[TabConfiguration] [color=blue]AS[/color] [color=blue]SELECT[/color] TabStrips.TabStripID , TabStrips.TabStripName , TabNames.TabNameID , TabNames.TabName , TabNames.ReportName , TabNames.TabPositionInTabStrip , TabNames.TabDescription , TabStrips.ActiveBackgroundColour , TabStrips.InactiveBackgroundColour , TabStrips.ActiveBorderColour , TabStrips.InactiveBorderColour , TabStrips.ActiveBorderStyle , TabStrips.InactiveBorderStyle , TabStrips.ActiveForegroundColour , TabStrips.InactiveForegroundColour , TabStrips.ActiveFontWeight , TabStrips.InactiveFontWeight , TabStrips.ActiveFontName , TabStrips.InactiveFontName [color=blue]FROM[/color] dbo.TabStrips [color=blue]INNER JOIN[/color] dbo.TabNames [color=blue]ON[/color] TabNames.TabStripID = TabStrips.TabStripID ; [color=blue]GO[/color] Some Starting Data In Part Three I will go through the creation of an SSRS report template in BIDS/Visual Studio, but in order to illustrate how it works we will need some data to work with. The following gives us more than enough to get started with. First, let's set up two tab strips: [color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabStrips] [color=blue]ON[/color]; [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabStrips] ([TabStripID], [TabStripName], [ActiveBackgroundColour], [inactiveBackgroundColour], [ActiveForegroundColour], [inactiveForegroundColour], [ActiveFontWeight], [inactiveFontWeight], [ActiveBorderColour], [inactiveBorderColour], [ActiveBorderStyle], [inactiveBorderStyle], [ActiveFontName], [inactiveFontName]) [color=blue]VALUES[/color] (1, [color=red]N'Home Page'[/color], [color=red]N'DarkBlue'[/color], [color=red]N'WhiteSmoke'[/color], [color=red]N'White'[/color], [color=red]N'Black'[/color], [color=red]N'Bold'[/color], [color=red]N'Normal'[/color], [color=red]N'Black'[/color], [color=red]N'Black'[/color], [color=red]N'None'[/color], [color=red]N'Solid'[/color], [color=red]N'Trebuchet MS'[/color], [color=red]N'Trebuchet MS'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabStrips] ([TabStripID], [TabStripName], [ActiveBackgroundColour], [inactiveBackgroundColour], [ActiveForegroundColour], [inactiveForegroundColour], [ActiveFontWeight], [inactiveFontWeight], [ActiveBorderColour], [inactiveBorderColour], [ActiveBorderStyle], [inactiveBorderStyle], [ActiveFontName], [inactiveFontName]) [color=blue]VALUES[/color] (2, [color=red]N'Pastoral Groups'[/color], [color=red]N'DarkGreen'[/color], [color=red]N'Honeydew'[/color], [color=red]N'White'[/color], [color=red]N'Black'[/color], [color=red]N'Bold'[/color], [color=red]N'Normal'[/color], [color=red]N'Black'[/color], [color=red]N'Black'[/color], [color=red]N'None'[/color], [color=red]N'Solid'[/color], [color=red]N'Trebuchet MS'[/color], [color=red]N'Trebuchet MS'[/color]); [color=blue]GO[/color] [color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabStrips] [color=blue]OFF[/color]; [color=blue]GO[/color] Now let's associate some individual tabs with these two tab strips. It doesn't really matter what order we add these tabs to the database because the TabPositionInTabStrip column explicitly controls in what position a tab is to appear in a tab strip. However, when it comes to Part Three, please note the first tab name added here. It has a TabNameID of 1 and its associated report is called "Tab Home - Home Page": [color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabNames] [color=blue]ON[/color]; [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (1, 1, [color=red]N'Home'[/color], [color=red]N'Tab Home - Home Page'[/color], 1, [color=red]N'Home Page!'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (2, 1, [color=red]N'Academic Groups'[/color], [color=red]N'Tab Home - Academic'[/color], 2, [color=red]N'Reports for Academic Groups.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (3, 1, [color=red]N'Pastoral Groups'[/color], [color=red]N'Tab Home - Pastoral'[/color], 3, [color=red]N'Reports for Pastoral Groups.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (4, 1, [color=red]N'SLT'[/color], [color=red]N'Tab Home - SLT'[/color], 4, [color=red]N'Reports just for SLT.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (5, 1, [color=red]N'Miscellaneous'[/color], [color=red]N'Tab Home - Miscellaneous'[/color], 5, [color=red]N'Other reports of interest.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (6, 2, [color=red]N'Tracking Analysis'[/color], [color=red]N'Tab Pastoral - Analysis'[/color], 1, [color=red]N'Tracking analysis for Pastoral Groups.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (7, 2, [color=red]N'Registration'[/color], [color=red]N'Tab Pastoral - Registration'[/color], 2, [color=red]N'Attendance history.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (8, 2, [color=red]N'Detentions'[/color], [color=red]N'Tab Pastoral - Detentions'[/color], 4, [color=red]N'Detentions statistics.'[/color]); [color=blue]GO[/color] [color=blue]INSERT[/color] [dbo].[TabNames] ([TabNameID], [TabStripID], [TabName], [ReportName], [TabPositionInTabStrip], [TabDescription]) [color=blue]VALUES[/color] (9, 2, [color=red]N'Academic Reports'[/color], [color=red]N'Tab Pastoral - Reports'[/color], 3, [color=red]N'Academic reports sent home to parents.'[/color]); [color=blue]GO[/color] [color=blue]SET IDENTITY_INSERT[/color] [dbo].[TabNames] [color=blue]OFF[/color]; [color=blue]GO[/color] We are now ready to create our reporting template in BIDS/Visual Studio. See Part Three for details.

Reports created in SSRS don't have an out-of-the-box tab strip control, so report designers have to find ways to work around this. Here is one possible workaround.[1] Is it the best or even an advisable approach? I withhold judgement on that, but I make a couple of comments on this right at the end, along with suggestions as to how to build upon this technique to create other effects. In Part One of this blog post, I will describe the motivation for coming up with this solution and give an overview of the solution design. In Part Two I describe the SQL needed in the database to implement the solution and in Part Three I describe what you need to do in BIDS/Visual Studio to make use of this method. Motivation A tab strip on a report is a convenient way of organising data and reports for the end user. For example, you might have the following report page loaded up for an academic tracking system [2]: The user of the report can then click on one of the tabs to find the area they wish to view more detail on: Two things happen when a tab is clicked on: The tab that was clicked on becomes the highlighted tab; Whatever else is displayed on the page is updated to reflect the new selection. There are a number of different ways in which the pre-existing SSRS controls can be used to create this effect. What's clear is that we will need either separate reports or a re-loaded version of the current report to create this effect: there is only very limited event handling in SSRS reports. The following two considerations led me to the solution that I'm going to describe: Should each tab point to its own individual report or should the current report just be reloaded? If we are going to have different reports that use the same set of tab strips, then we may end up with a number of maintenance issues. If you are using a tab strip to show essentially the same analysis, but with different parameters (e.g. each tab is just the name of a particular subject and the same analysis is being shown, just for a different subject), then I think a single report that is reloaded would work very well and you could even adapt the solution I give here for this purpose. You would just need a parameter, with an appropriate default value, that controlled which subject was being analysed at any particular time. However, in my case, each tab is organising qualitatively different sets of reports: we are organising the user's navigation through the various types of analysis available; we haven't yet reached the stage where we are re-using the same analysis over and over again. To put it another way: there are a rather large number of reports that have drill-through inter-relationships. Tabs provide a visually convenient way for the end user to navigate those inter-relationships. But, if we are going to have different reports that use the same set of tab strips, then we may have the following maintenance issues: We may change our minds about the colour schemes. This affects both the colouring of highlighted and lowlighted tabs. We may change our minds about the text to be displayed on each of the tabs. We may change our minds about the order in which to display the tabs. We may decide to remove a tab. We may decide to add a new tab. We may decide to re-organise the reports in a more radical way by changing up the tab strips. If we are using separate reports for each tab (and not just re-loading the current report), then these changes are going to affect several reports at once. I prefer to come up with solutions that make maintenance straightforward, not necessarily because it saves me time overall [3], but because the next time I need to make a change, I know I don't need to worry about a lot of the details because they have already been taken care of. I can then concentrate on other issues. My solution is to devote tables in my database [4] to laying out the various categories of tab strips, each with their own colouring scheme. Individual tabs are then associated with particular tab strips: this indicates which tab strip a particular tab appears on. If desired, reports can drill through to other reports that make use of tab strips. For example, the reports available from the blue Pastoral Groups tab may take us to another collection of reports: Because I don't know how design multiple tab strips side-by-side so that a user can easily drill backwards through the path they took, I only use one tab strip on each report. Instead, I use breadcrumb trails for backwards navigation. (The gap above the tab strip is where the breadcrumb trail would appear, but I’m not describing how to do that in this blog post.) Overview of the Solution The solution comes in two parts: amendments to the database and the creation of a template report in BIDS/Visual Studio. As I'm using SQL Server, my amendments use T-SQL, but this isn't essential to the solution. Database Amendments Two tables are needed to describe tab strip data: One table contains the details for each category of tab strip. For example, in the screenshots we have seen two types of tab strip, one of which we can call Home (because it's the first tab strip you see when you access the tracking system) and one of which we can call Pastoral. The Home tab strip has a dark blue colouring scheme, whereas the Pastoral tab strip has a dark green colouring scheme.[5] One table contains the details for every individual tab. Every tab needs to be associated with a tab strip. Without that association, there is no way to know when a tab is supposed to appear. Each tab has a title (which is the text to be displayed on the tab) and is also associated with a named report. This table will also specify the position of the tab on the tab strip. We'll use IDENTITY to give an arbitrary primary key to each tab name (called TabNameID) which will be used when we create a new report in BIDS/Visual Studio.[6] In addition to these tables, we need a view that reconstructs all the tab strips. The details will be given in Part Two. Report Template in BIDS/Visual Studio Since this is all about making maintenance easier, we will do the following: Create a template for a tab strip report; Create shared datasets that contain all the SQL required to construct and format the tab strips; Use the data to provide drill through links to other reports. The report template will require an internal parameter that identifies the tab it represents. This value will be the primary key value - TabNameID - mentioned above. All we need to do is name the report to match what has been specified in the database for the given internal parameter. Everything else is set up in the template and draws on information given in the database. The tab strip itself is just a table tablix. It has no row groups, only column groups. The column group draws its values from all the tabs that come from the same tab strip as the internal parameter (TabNameID) of the current report. The highlighted tab is just that cell in the column group that has the same TabNameID as the current page. The action property for each cell is just to go to the report that has the TabNameID of that cell. More details about how this works are in Part Three. How Does This Solve The Issues Mentioned Above? I mentioned some maintenance issues above, so now let's see how the above solution outline helps: We may change our minds about the colour scheme. This affects both the colouring of highlighted and lowlighted tabs. How this is solved: colour schemes are properties of the type of tab strip and these properties are stored in the database. To change the colour scheme, simply update the relevant row in the tab strip table. Once updated here, all reports that display tabs from this category will use the new colour scheme. (This is the same principle as using CSS to control web page formatting.) We may change our minds about the text to be displayed on each of the tabs. How this is solved: the text is a property of the individual tab given in the database. Update the database and all the reports will update too. We may change our minds about the order in which we want to display the tabs. How this is solved: update the position column in the relevant table. We may decide to remove a tab. How this is solved: we remove the tab from the table. N.B. we don't need to remove the report itself. If we are worried about removing rows from the database, we could implement this as a soft deletion. See the next bullet point for why soft deletion is probably a good idea. We may decide to add a new tab. How this is solved: we add the new tab to the table, specifying which strip it is to appear on and in which position. We create a new report and ensure that its ID and name matches what appears in the database. The two together ensure that once the report has been uploaded, it will be automatically offered as a tab option on other reports. Some small print: we'd have to make sure that the report had been uploaded before adding the row to the table, otherwise users would see the new tab on reports before there was a report that they could drill-through to. However, if we have made use of soft deletion on the tabs table, we could insert the new row as deleted and only undelete it when we are ready to go live. Each individual report displays certain kinds of data or analysis. The TabNameID of the report indicates the type of analysis available. When and where this analysis is available in the total system is controlled in the database through (a) the particular tab strip that TabNameID is associated with and (b) its position on the tab strip. So, changing the way in which the reports are organised is a matter of updating these arrangements in the database, without having to touch the reports themselves. Is This a Good Idea? I always think it is worth asking this question even if we’re sure we’re sure. The point is to think about *why* we have chosen a particular path and to understand the implications of not going down another route. For example, you might decide that sub reports will do the equivalent work, or that what's really required here is either a custom interface designed outside of Reporting Services (e.g. ASP) or a custom control to add to our toolbox in BIDS/Visual Studio. You might also prefer to go with one of the solutions mentioned in [1]. Here are two pros and two cons for the solution given here (not intended to be a complete list): Pro: maintenance and iterative refinement are now very straightforward. We can think much more clearly about how we are trying to organise the reports, without having to worry about the details of how to implement possible changes: each report serves a distinct role, but how we arrange those reports can be treated independently. This is the key benefit of separating concerns. Pro: one might regard this solution as providing, via the database, partial documentation for your SSRS reports. In this case, the database is indicating some of the drill-through relationships between your reports. Con: we need access to a database in which we can store and update details of the tab strips. In principle this needn't be the same database that we're drawing our analyses from. But someone – you or the DBA – needs to set up and maintain the tables and the queries. Con: this method requires the use of several expressions for formatting the report. The more expressions there are, the more processing that needs to take place before the report can be rendered. This increases the overall rendering time for the report. In addition, we are making calls to the database to work out what formatting and tab structure is to be applied to the report. As we're using shared datasets, this concern could be mitigated by caching these on the report server. Caching the shared datasets could, in certain situations, also be used for controlling when changes to tab strip structure and design become live for end users. And in conclusion? Reader, I implemented it. To see how, see Part Two for details of the database tables and Part Three for details about the report template. Useful Extensions Breadcrumb trails are complementary in approach to the use of tab strips: whereas tab strips indicate which tabs are adjacent to one another ("horizontal" drill-through relationships), breadcrumb trails indicate how to navigate back from one tab strip to another ("vertical" drill-through relationships, with the "home" tab strip at the top). There may be more than one way to access the same report through drill through relationships. For example, a Head of Subject may access a report for a particular teaching set from a specially-designed "Head of Subject" tab strip. An ordinary teacher may access a teaching set report because that’s the set they teach and therefore they have accessed it through a different tab strip. But as the relationship is always functional when working backwards - relative to a particular breadcrumb trail, there is only ever one possible report that could have provided access to a given report - this does not present any special problems. In other words, breadcrumb trails are implemented with their own IDs (just like tab strips), but these need to be passed as parameters between reports as each trail indicates a possible path from one report to another. Staff roles can be associated with tab strips and tab names to control access to reports. This does provide some locking down of reports, but I tend to think of this as a usability feature: it stops staff from seeing reports that are irrelevant to their role ("just show me what I need to know"). Using the data in the tables (including the descriptions of the individual tabs), a single tab might be reserved to act as a home page for each tab strip. By listing all of the tabs accessible on this tab strip, this would act as a descriptive table of contents for the current tab strip. Again, using the data in the tables, where a tab or link takes you to a report which uses a different tab strip, a preview table of contents could be displayed giving an overview of what can be accessed. This could save staff from randomly clicking on tabs in an attempt to find what they are looking for. But, if that’s really an issue, there’s probably a problem with the high-level organisation of the reports. No worries: this can now be easily changed! In the solution I’ve described, we have tables that explicitly set out the arrangement of our tab strips. What if we want to dynamically create those tabs? For example, suppose a Head of Department is responsible for several subjects (e.g. Biology, Chemistry, and Physics) and now we want individual tabs for each of these? The underlying principle to be applied is exactly the same, except that instead of using base tables, we use views to list the available tabs. You would also need a separate report parameter to identify which "tab" is being viewed at any particular time. Footnotes Although it's been a while since I came up with this solution, this approach was partly inspired by thinking about the approaches given in the following two blog posts: http://blogs.infosupport.com/creating-tabs-in-ssrs/ and https://bistuffwithdigven.wordpress.com/2013/01/20/tabbed-ssrs-reports/. Both of these blogs helped me clarify what I wanted to do and also what I didn't want to do. In particular, the tabs that I discuss here are for managing inter-report drill-through relationships and not intra-report page navigation. The tabs I've displayed on the screenshots are just examples I created for this blog post. In addition, in the real system, the tab strips are associated with staff roles so that only those with the relevant permissions can view the reports listed. The chances are it *won't* save me time overall. Cautionary xkcd: Automation. But, as I say, saving time is not the primary motivation here. The database I'm editing is my own and sits alongside the MIS that contains all the basic student data, report grades etc. Obviously this solution requires that you are able to add the relevant tables to the database you are querying (or that your DBA agrees to do this on your behalf). As I mentioned in footnote 2, in practice there are staff roles associated with the tabs to control the visibility of the reports. I have arranged staff roles into high-level categories and these categories are then mapped on to the various categories of tab strip. For example, member of staff who lacks academic responsibilities will not see the Academic tab. Continuing with how permissions are handled: the individual tabs are assigned restrictive permissions depending upon the staff roles. Staff categories and staff roles are arranged hierarchically, so, e.g. a Head of Subject can view all the teaching sets data in their subject, but an ordinary set teacher can only see data for their own sets.

Yes, that's how I understand it too.

My school uses iSAMS. When we moved to iSAMS we switched our admissions process from RS Admissions to iSAMS at the same time as we wanted to reduce the number of independent databases. For reporting and analysis we use SQL Server Reporting Services, which we use pretty much across the board in all of the modules. (iSAMS offer training on this.) We've been able to deal with all our reporting and analysis requirements this way.

Just as an alternative route to achieving the same effect: if you are using named paragraph styles in your document (e.g. Heading 1, Heading 2), you can pull the text from these paragraphs automatically into your headers using the StyleRef field. This produces a nice effect when combined with heading styles that are set to start on a new page and reduces the amount of manual management.

The only times I've seen problems of this kind with reports data has been related to pop up windows. Because these windows are not modal windows, they can get tucked behind other windows and out of sight. Two such situations I am aware of: If a member of staff is entering a report and a window pops up concerning spell checking, then iSAMS needs to have this window closed (confirmed). If the window gets tucked away, the staff member might not realise it's there to be clicked on. If a lot of pop up windows (not necessarily related to spell check) have been generated, then IE can struggle. This can effectively cause iSAMS to freeze up. If either or both of these are happening, this might explain the randomness. HTH

@NormalDistribution I've now finally gotten permission to go ahead and implement a version of your report design in our academic tracking from September, so very many thanks for that.

Thanks for posting the link. That thread was a very interesting read!

Invisible, as in Invisible Web?

Hi, Mark, I've just signed up for it, but I won't actually be starting it until April. For what's it worth, what swung it for me were @Ecclesbury's comments in the Any other Data Officers/Managers here? thread, particularly post #31. May be that's what you've been looking at anyway! I emailed SSAT for some info about their syllabus and they were very helpful, so I'd certainly recommend that. Sorry I can't be more specific at this time, but I'd be happy to give more information in a few months' time when I've actually (hopefully!) made some progress in the award.

It's official... I am going crazy. This feature was removed in Office 2010! Just goes to show, you don't miss a feature until you want to use it.

Background I use a lot of keyboard shortcuts in MS Office, many of them built-in. I've just installed Office 2013 and come across a bizarrely frustrating problem in Excel. According to this page on Excel 2013, for example, Keyboard shortcuts in Excel, all the old shortcuts using CTRL should still work: Since at least Office 97, the shortcut CTRL+O has brought up the Open dialog. However, in Office 2013, this takes you to Backstage View, where you are presented with options to open a recently used file or to browse for a file on your file system. Clicking on the latter button takes you to further options to select which location you wish to search or, finally, to open up a dialog. That's a somewhat more convoluted process! So, they've changed the meaning of CTRL+O to mean "Open Backstage View". Okay, so all I need to do is change it to what I want. The Problem In Office 2010, if you wanted to customise the keyboard shortcuts, you went to Backstage View -> Customize Ribbon and clicked the button to customise the keyboard. You can do this in Word 2013 using the same process. However, it appears you can only do this in Word 2013. Does anyone have any ideas as to how I might be able to do this? I looked at the "All Commands" option under Customize Ribbon and, unlike Word, there is no entry for Customize Keyboard. But I'm not sure whether this isn't just some weirdness with my installation, so could someone confirm whether they have the same limitation? It just seems odd that the feature is available in Word, but not elsewhere. I'm aware that in this case, I could just re-learn to use CTRL+F12 instead, but this isn't really just about this one shortcut.

Microsoft's Customer Experience Improvement Program (or whatever they did back in the day) is probably where these features originated from. The data from that program probably indicates that when someone types in 05-09 they want to use it as a date. If you're typing data directly into Excel, you can force Excel to display what you type as text by adding an apostrophe, as in '05-09. The downside is that you can't perform numeric calculations on such textual data. The reason why 28:17 is rendered as 01-01-1900 04:17 derives from what I said above, once you take into account the fact that there are 24 hours in a day. 28:17 means 28 hours and 17 minutes, so, as CESIL says, you could have 00:28:17 in your CSV to ensure that Excel reads the data correctly. If you want to do calculations on your times, then this is probably the best solution. The way Excel is set up makes it look like there is a fairly clean separation between the data stored in a cell and the way it is presented (i.e. formatted), but there are evidently limitations to this. I guess Microsoft made the reasonable assumption that there are times when the semantics of some piece of data has been indicated at the point of data entry, e.g. 28:17:00 means 28 hours, 17 minutes and 0 seconds, and that in most cases it will save people time if Excel can recognise that fact.

I guess Crystal Reports are much like SSRS? I'm using SSRS and staff access the reports online via the MIS. The reports use SQL as the query language. The reports use drill-down and drill-through, have breadcrumb trails to aid navigation and use the staff login to work out what data they should be allowed to see. (Any parameters in reports are adjusted accordingly.) Overall, I'd say this has worked pretty well. I used do the analysis in Excel using a custom-built add-in. That was pretty powerful, but the code had to both process the data and format the spreadsheets. With SSRS, the presentation side is handled in a drag-and-drop environment and I can pretty much think about the data and calculations independently of this. Much more convenient! Here are the main areas I found myself concerned with. I apologise for the length, but I hope you find it useful for thinking about what you want to do with your reports. I guess that really depends upon the scope of your project. * Advanced planning is essential. You'll want to classify the types of reports you need and whether apparent differences can be handled by varying report parameters. You can't do this without knowing who your stakeholders are and what they want to be able to use the reports for. * Types of calculation required. If you can create views or share data sets between reports, then you can share calculations and data between reports. The same principle applies to custom code in your reports. In Reporting Services you can create assemblies that can be shared between different reports. * Availability of data required. In my case, I had to supplement the MIS with a database that contained additional data. I needed to do this because although there is scope within the MIS to add custom fields and groups, I needed to define internal relationships within that custom data and between that custom data and other data already held in the MIS. (Synonyms took care of cross-database queries.) * Report data access restrictions. You'll need to ensure that stakeholders only see the appropriate kinds of data (if any!). You may also need to think about server load, e.g. your report might use a data snapshot, rather than pulling live data. (Alternatively, you may be able to set up subscriptions that save the report to a location according to a set schedule.) On the subject of server load: the report format may affect whether or not the rendering is processor or memory intensive. * Report design. I regard this as pretty important, because I'm not good at this. If the reports are designed to be viewed on screen, they need to work within the restrictions of the screen. Modulo whether staff understand what the data and analysis means, the way in which that information is presented can make it easier or harder to understand what the report is telling them. * Report interactivity. If you want to have drill through reports, you'll need to think about which parameters need to be exchanged between reports. If reports have user-selectable parameters, then the back button on the browser might not work as expected. (I got round this by placing a breadcrumb trail in the report itself.) You'll also need to make sure that your parent report passes sufficient parameters to the drilled-to report so that when the user does go back, those parameters are returned back to the parent report so that it displays correctly. You may also need to think about how to control such links if your reports can be exported to different formats. I made the decision to only allow the links to be usable if the reports are viewed on screen, e.g. if the report gets exported to Excel, they are either removed or become plain text. You'll also need to check whether report parameters (internal or otherwise) get exposed, e.g. via URLs if you are providing direct URL access, and are manipulable by the user. * Future requirement changes. There'll be some things that you will work on now, leaving others until later, and other things that nobody has suggested yet, but might do in the future. You'll need to approach things so that you have that flexibility without having to keep starting from scratch. Omniscience would be a desirable skill

Short Answer As far as I can see, the answer is no, unfortunately. You have to enter the colon as well. Long Answer Excel handles dates and times as numbers, with 1 representing the 1st of January 1900, 2 representing the 2nd of January 1900 etc., with times represented by fractions, e.g. 1.2 represents 4:48 am on 01/01/1900. Dates (i.e. whole numbers) are dates as at midnight exactly. This underlies Excel's handling of date and time calculations. Although you've entered 14 into the cell and instructed Excel to format it as a time, this is displayed as 14/01/1900 00:00:00 (or similar). If you want Excel to display just the time portion and ignore the date, you have to enter it as 14:00. Without the colon portion, Excel will treat your number as a date and time combination. Entering the colon tells Excel it's just the time portion you've entered.