BIML – Importing Text Files

Here’s a BIML snippet for reading data from a text file.  If you’ve read the prior post about pushing data (from a table) to a text file, you’ll see a lot of the same constructs.  There is;

  1. A connection object (ie the FlatFileConnection) that specifies a path to a file.  The connection requires a FileFormat.
  2. A FileFormat which specifies the nature of the file (ie columns, data types, delimiters etc)
  3. The data flow object (ie the Source in the DataFlow which is a FlatFileSource).

I do not think there is too much complexity in the Connection or [DataFlow] Source.  There are really only a few attributes that need to be defined and, if you think about the nature of the task we are trying to achieve, there are all required.

What is the most interesting is the specification of the FlatFileFormat.  This defines the nature of the data that we are connecting to.  Both at a header and detail level.  For example consider the delimiter constructs for the header (HeaderRowDelimiter).  You would think that the delimiter is applicable to the entire file but this is not the case and it also needs to be specified on a column by column basis.  Finally, we also need to specify the new line on the last column.  Perhaps that’s not so intuitive since we specify the row delimiter in the header section of the Format.

So here is what we are trying to import.

image

And here is the BIML snippet to import it.


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="my_import" FileFormat="my_values" FilePath="C:\temp\my_values.csv" />
    </Connections>
    <FileFormats>
        <FlatFileFormat Name="my_values" ColumnNamesInFirstDataRow="true" RowDelimiter="CRLF" HeaderRowDelimiter="Comma" CodePage="1252" IsUnicode="false">
            <Columns>
                <Column Name="ID" DataType="Int32" Delimiter="Comma" />
                <Column Name="Name" DataType="AnsiString" Length="255" Delimiter="Comma" />
                <Column Name="Value" DataType="Double" Delimiter="CRLF"  />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Packages>
        <Package Name="import_MyValue" ConstraintMode="Linear" ProtectionLevel="EncryptAllWithUserKey">
            <Tasks>
                <Dataflow Name="load_values">
                    <Transformations>
                        <FlatFileSource Name="src_values" ConnectionName="my_import" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

SSRS – Add a little Pizzazz to SSRS Reports with Row Numbers and Pop-Out Windows

Canned reports are the bread and butter of information delivery – I have read that up to 70% of users can be classified as consumers or the type of person that gets a report and is happy with that. There is no desire to analyse additional datasets, examine tweeter sediment or speculate correlation.  Consumers just need data/information presented them for their day to day activity in an intuitive and navigation friendly and the report needs to be built for their indented use.

In SSRS, I have found 2 common requirements for features that do not come out of the box and this post looks at 2 snippets to create better reports which enhance the user experience. These are Row Numbers to Grids and the creation of Popup windows.

Adding Row Numbers

Row numbers help to provide scale.  As a simple example, imagine you’ve got a report stating that there are some number of products sold and you provide an action to drill through and see what those products are.  If you’ve got a column for row numbers, you can easily reconcile the quantity between the first report and the detail in the second.

You may (naturally enough) think that you can simply use a RowNumber formula and set the scope of the function to the Data Set that drives the control (that is, the table or matrix).  Consider a data set (category_summary) that summarises invoice data by category, product and year.  If we add our expected formula as a row counter (like this);

=RowNumber("category_summary")

Using RowNumber() is only applicable when the grain (detail grouping for rows) is the same as the dataset.  For example, I add a matrix with a row group as { Year, Category, Product } as below.  Here RowNumber works just fine

image

image

Unfortunately if that detail grouping grain is broken (say for example I use the same data set in a matrix with Category groupings on rows, Year on columns), the RowNumber returns unwanted results showing the last value at the controls grain (as below).  Note that there are 36 rows products in the 1st category in 1996.

image

In order to get a incremental value for each row, we need to determine the running value over the distinct elements that we are after (Category in our case).  This can be done using the following formula.

=RunningValue(Fields!CategoryID.Value, CountDistinct, "category_summary")

More information about RunningValue can be found here

Using POPUP Windows instead of SSRS Actions

Using an action is a very flexible way to redirect focus.  We can identity coordinates of a cell and then pass those coordinates to another report as parameters.  If we reconsider our matrix of Categories on rows and Years on columns, we may want to the user to drill through to a new report that shows the products and their quantities that make up that value.

The simplest way to to that is specify the report and assign parameter values as those in the matrix (as below). image

Unfortunately, the end user experience for this rather poor and for any action in a report.  When the user clicks on the cell in the published report, the current report is replaced with the new report.  If they want to return (to the original starting position), they are forced to navigate using the browser buttons.  Even when you specify the URL (as opposed to a Go to report action), the behaviour is the same 😦

If we want to open the report in a new window, the best way to do this is to ‘trick’ the action into running javascript that does this using window.open method.  You can read more about that function here, but for now, lets look at the basics.

In my report, I’ve created a function that determines the report server URL to open the report that I want.  This includes the ability to pass paramaters based on the position of the cell in the matrix (or table) control.  The function is;

Public Function WindowURL(byval OrderYear as String, ByVal CategoryID as String) as String

return "<a href="http://localhost/reportserver?/reports/ssrs_pizzas/reports/CategoryYearDetail&OrderYear="+ OrderYear + "CategoryID=" + CategoryID  

End Function  

You can see its pretty simple and just appends creates the URL based on the provided OrderYear and CategoryID.  Secondly, i want to specify the javascript to use that URL and open a new window.  The ‘base’ java script takes the form javascript:void window.open(“URL”);  I’ll add a second function (as below) to build the full snippet.  You’ll note that I am still planning the pass the OrderYear and CategoryId to the function since it will be called from the cell in the matrix.

Public Function OpenReport(ByVal OrderYear as String, ByVal CategoryID as String) as String  <p>&nbsp; return "javascript.void window.open( """ + WindowURL(OrderYear, CategoryID) +""" );"  <p>End Function  <p> <p>

The function (OpenReport) is then called from the matrix cell

image

Now when we click on a cell, a new window will open.  There only be one new window regardless of the cell that you click.  If you want more control over the pop up window, we can address that in other ways (as below).

Extending Window.open

One of the great things about using the window.open method is that you can get very fine control over how the window is managed and appears.  The full syntax can be found here.  For me, the most important arguments are the name of the window and its specs. The format for window.open is

window.open(URL, name, specs, replace )

The windows name is interesting because a window can only have a single name (forget about the target attribute for the window).  If we recap on the Category, Year example, the name can be used to define how many windows will open (and the conditions for opening a new window).  For example, if our name was simply “Category” the content of popup window would always be replaced (since there is only one ‘Category’ window.

However, we could derive a concatenated name of “Category” and [Category ID].  If this were the case, a new window would be created for each new instance of category… (mind you, you’d still have to click on each unique category) however a new window would open for each category.  The single report can spawn multiple popup windows.  We could take it to a higher level of granularity and include the year (in the name) and have even more windows (potentially) open.

One thing to keep in mind is that the windows name can not contain some special characters.  Hyphens are out 🙂

Finally, we can also control, the size, position and functionality of the popup window.  For example, the following string opens a small window in the top left corner of the screen without menu or toolbars.  To my way of thinking its just a data driven information window that we want our users to see.

 
'width=700,height=500,toolbar=0,menubar=0,location=0,status=1,scrollbars=1,resizable=1,left=0,top=0'

Conclusion

That’s about it.  Out of the box, functionality could be considered unfriendly.  But we can manage and control its functionality using some javascript.