Here I sit before a mostly empty screen. Mostly because on the left hand side are a bunch of objects I created on SQL 2008 R2. The data warehouse side of the system I’m working on. Why access, why not .Net? Granted, behind the scenes I am working on a SharePoint 2010 BCD project regarding the same data. The reason is simple, I need to create a web-able application that can be used right now. Having used Access for a long time keeping up skills with the new version is also important. Viva la multi-task! Or was that Multi-Pass? Can you say the 5th Element?
The One Liner Report and other requirements
For this project to meet the minimum functionality requirements it needs to produce a one liner report and a detail report. Report in this sense is not necessary an actual report (though with Access that structure is certainly within bounds) but of a data grid and detail view of a single record.
And the Detail form similar to:
Ok, before we can get to the One Liner and all the goodness there, some background notes on the data warehouse staging database is needed. First, notice that this is the staging database that is being used. I wish time allowed for the DW to be fully vetted into a cube but that definition and design effort is still a ways off. The mandate is that the data needs to start producing results now. I can only dream about the eventual cube. The basic design around data collection into the staging database is a forward moving dataset. What I mean by that is that records are only appended so that the historical state of a given transaction is preserved. The requirement for this report is that it is a snapshot in time reflecting the current state of all transactions at that given point. This is easily facilitated through using the primary key on the data table that combines the unique id of the transaction plus the date of the transaction .
The One Liner Definition
There are three sources of data for this report. Public records, and records from two internal systems. Step one, develop a one liner form that matches the top diagram. So I start with this…
I already have a view from SQL I want to use so that will be my data source.
Since this is a wide view, I’ll use a query within access to filter down to just the data required. The view has already been flattened out to some degree so it will be necessary to restrict the data down to only the master list. Easy to accomplish through the query property view.
The query now returns only about 2k records instead of the 27k returned the first time. Pretty close to the one liner definition. A little formatting and then I’ll create the detail form before finishing the one liner report. Saved off the query with a distinctive name and it is now an available object to use. I can use the Unique Values property in this query against the query that I set up in SQL because I already scoped the data using only the latest values for each distinct master record.
This means that the query that is set up only returns a list restricted to the Master Data Record dataset.
The Detail Report…
Next an additional query needs to be set up that takes a parameter and returns…
I test the query first. I expect that even though I only put in a single office number, I should get more than one row back. This is expected and the properties should be changed as above so that only unique data comes back.
The parameter box pops up as expected;
Using a known Office number that will have multiple entries, the query is executed.
Setting properties to Unique changes 1 of 24 to;
The query is saved and now there are two. Notice the original query, One Liner Source name has been changed to One Liner Source View for consistency.
Still no forms yet.
The first form to be created will be the form for the one liner report. The concept for this form is a table with some formatting.
By simply having the dataset selected and clicking on the form Multiple Items form selection, Access automatically builds the form.
For the Detailed view, the Form Wizard will be used…
Using all the fields and a Justified form template…
And giving it a nice name…
Clicking on Finish results in the challenge box…
The wizard will make it’s best guess on how to set up the form but you’ll probably need to go into design mode to set it up how you want it to look.
Make sure to grab the move box if you want to separate the data field from the label field.
Depending on your Mouse to Hand to Eye coordination, the Size and Order features really help cleaning up the form.
Applying an input mask can help format the data.
Long Story Short
The current prototype has been well accepted. Next article will be utilizing the same prototype and pushing it up to SharePoint.