Saturday, 9 July 2016

SSRS - Custom Period Filters

Problem:

Just wondering if the reports published on SQL Server Reporting Services can be customized to have a predefined period filters like Last Week, Last Month, Last Quarter, etc.

By: Anonymous

Solution:

You have few options to work around this, which are given below:

1. Dynamic Parameter

Basically, your underlying query or stored procedure needs the values for the From and To paramteres to apply in the where clause of the query. You can do this by first creating a mutli valued Period parameter, with values like, Last Week, Last Month, etc specified as available values. The From and To parameters can then be set up in such a way that it fetches the default value from a query, which takes the value from the Period parameter and then return the corresponding From and To values respectively. In these, the Period Parameter shall be positioned above the From and To parameters. The query can be as simple as

SELECT 
CASE 
WHEN @Period = 'LW' THEN DATEADD(D,((DATEPART(dw, GETDATE())-1)*-1)-7,GETDATE())
WHEN @PEriod = 'LM' THEN DATEADD(M,-1,DATEADD(D,(DAY(GETDATE())-1)*-1, GETDATE()))
END AS FROM_DATE

for the default values of the From parameter. You may extend this idea and use it different ways to make a parameter dynamically derive a value based on a previous parameter. Note that the order of the parameter is important as the parameters are constructed sequentially and there is nothing like an onChange event that you can think of.

2. Let the SQL Query / Procedure do the work

The second alternative is to put in a logic similar to the above within the Report Query or Stored Procedure, so that the user just selects the period filter and the SQL Server interprets it appropriately and returns the appropriate results. The only difference with this option is that the From and To Filters need not be designed in the report and the user won't see it on screen.

3. Wrap the report within an Application

Just in case, you are rendering the reports within an Application, like a ASP.NET application or a Windows Forms Application, then you can have the complete control on the Filters in the presentation layer of your application. For instance in this case, you accept a Period Filter from the user in your front end application and then transform it to an appropriate From and To dates to the ReportViewer component and get the report rendered accordingly.





Sunday, 3 July 2016

Issues in Signing a .NET Assembly using .pfx file

Problem:

I have procured a code signing certificate with a view to ensure that one of application that we intend to distribute to external users is signed so that the same can be trusted by the end users. I imported the certificate into the certificate store and used it successfully to sign the Click Once Manifest using the Signing tab in the Projec Properties dialog in Visual Studio 2013. But I could not have the assembly signed. I exported the certificate from the store as a .pfx file and tried using it for signing the assembly, but am getting issues like "Private Key not Found", while the Private key is very much present in the .pfx file.

By: Rajkumar David

Solution:

Visual Studio 2013 has known issues in handling PKCS# 12 certificate files as it cannot handle files with multilpe certificates in the CA certificate chain. Visual studio may not still consider such certificates for signing the assembly because of the KeySpec Parameter, which is usually set as AT_KEYEXCHANGE(1), whereas Visual Studio expects this to be AT_SIGNATURE(2). It is possible that while requesting the certificate, the KeySpec is set as 1 and as such the certificate is generated with the value as 1. You may verify CSR that you have submitted to the CA to check this.

OK, now what do you with that certificate? You have option to import such certificates with KeySpec set as AT_SIGNATUR. The Windows Servers from 2003 onwards have a commandline certificate import utility - certutil.exe. This command allows you to import the certificate with the right KeySpec parameter. Use the following command to do this:

certutil -importPFX -user <pfxfilename> AT_SIGNATURE

After importing the certificate using the above command into the certificate store and then export it back as .pfx file from the store for assembly signing within Visual Studio. This should resolve the given problem.

More on the CertUtil command can be found here.

Sunday, 29 November 2015

How to Create '|' (Pipe) Delimited Files in Excel

Problem:

Microsoft Excel is a very useful tool for data analysis. It supports import of data from various sources and formats into it for analysis. But, though it supports export of data in various common formats, it does not natively support export of data in a delimited text format with the delimiter other than Comma and Tab characters. This post explains how to get the excel data exported into a "|" (pipe) delimited text file.

Solution:

Excel does not directly support export or saving the data delimited with a pipe or such other characters. It supports comma delimited files (.csv) or tab delimited files though. The csv file generator however uses the List Separator as set in the windows Regional Settings as the delimiter. With this you can change this value in the regional settings and the csv file option will now produce a delimited file with the delimiter of your choice as set in the regional settings. For those not familiar with the regional settings, here is how to get this accomplished:

If you are using Windows 8 or 8.1, you will find the Regional Settings option under the "Clock, Language and Region"  category.



Click on the Additional Settings button in the Formats tab of the Regional Settings dialogue box. In the Numbers tab of the resulting dialogue box, you will find the field List Separator (Highlighted in the image. By default, it displays ","(comma). Now set it to a character that you need the files to be delimited with. For instance, if you want export a pipe delimited text file from excel, enter the charcter "|" in this field and apply the change.



You are done. Close and re-open Excel with the data that you want to export. Now use the Save As option to save the sheet as a csv file (.csv). This will now produce a text file delimited with the pipe character. Unless you regularly use pipe as the delimiter, you may want to set it back to comma, so that it does not impact any other operations that dependent on the List Separator field.



Sunday, 20 September 2015

Powershell Script for Cleaning up Old Files

Poblem:

I am working on a project that creates 100s of files every hour which has led to creation of over few thousand files in a week. Due to the very high number of files, Explorer takes so long to list the files in the folder. As this could fill up the storage pretty fast, I need to delete files older than 7 days. Please suggest me how best to accomplish this, preferably using PowerShell and leveraging the Scheduled Tasks.

By: Anonymous

Solution:


Powershell is the way to go for these kind of tasks. For deleting old files, the following script will do the job:

Get-ChildItem –Path <Base Folder> –Recurse | Where-Object CreationTime –lt (Get-Date).AddDays(-7) | Remove-Item

Replace the <Base Folder> with the actual base folder  that need to be looked up for this task. Simiarly, you may observe the from the above script, this will delete the files based on the time of creation of the file. If you want to use the last modified time, replace the CreationTime with LastWriteTime

The above script can be executed from the PowerShell prompt or can be scheduled to run automatically using the Windows Task Scheduler. There are couple of things that you should take care of while scheduling:

  • By default, the deletion will fail as the execution policy may not permit the deletion. To override this set the parameter ExecutionPolicy to bypass
  • You may want to add the parameters - noninteractive. to enable to run automatically without needing user action. 
  • Do not place the script itself in the program field, Instead save the script as a .ps1 file and pass the script file with fully qualified path as a parameter in the parameter field.

Typically, the following should be added to the parameter field:

-noninteractive –nologo -ExecutionPolicy Bypass -command "& 'c:\datafiles\cleanup.ps1'"

Needless to mention that the progrm / script field shall contain the PowerShell executable with its fully qualified path, which will be like this:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
You may also want to have a look at the clean up script on the TechNet site, which has many more capabilities, like crating a log of files deleted and so on.

Friday, 26 June 2015

SSRS Report Builder 3.0 - How to avoid page breaks in Report Viewer

Problem:

The Report Viewer displays the tabular reports with pagination. This is inconvenient to users as they have to click on the page navigator to browse through the report. Is there a way to have the report viewer display all the rows in one view, so that users will find it convenient to browse through the report by just scrolloing up and down?

By: Muthu Saravanan

Solution:

Use the Interactive Size property of the report to overcome this. This property will not however show up in the Report Properties dialog. This will be available on the Properties Pane, which shows up ususally on the right side of the screen. If it is not showing up, check the options under the View menu. To have all records in single view without pagination, set the Height under Interactive Size to 0 cm.


This setting is effective for display in the report viewer only. Page breaks in print and other export formats will be based on the Page Size rather than the Interactive Size property.

Friday, 6 March 2015

Whitelisting email or domain in Gmail

Question:

How to whitelist or blacklist an email domain in gmail?

By: Radhakrishnan Ravi


Answer:

Google has a might spam handler and does a very decent job. However, if you want to put a rule to either whitelist or blacklist, use the filter options provided under settings menu. It is simple and the following steps will take you through in implementing a filter of your choice.

Choose the Settings menu from the Settings drop down menu on the top right beneath your profile picture.


Select the Filter Tab, which will list the filters and will have a link at the bottom captioned ''Create a New Filter". As you may observe, you have an option to import filters as well using the "Import Filter" link.







Clicking the create filter will take you to the advanced search window as in the image on the right. Alternatively this search window can be accessed using the tiny drop down icon provided on the right side of the search box within gmail. Basically, you will have to decide on the search criteria to apply the filter for. i.e, if you want to whitelist the domain kannan-subbiah.com, enter the domain name in the From field of the search window. Upon entering a valid search criteria, you will find the link "Create a filter with this search" enabled.


Click on this and you will see a Filter Dialogue window as in the image on the left. The options provided are self explanatory and choose appropriate action you want to apply for the chosen search criteria. For instance, if you want to white list the entered domain, select the option "Never send it to Spam" and then click on the Create Filter button at the bottom. Similarly, if you want to permanently delete emails from a specific sender, you may choose the delete option here.

The filters so added will appear in under the Filter tab of the settings screen. You will then have the option to remove or modify the filters  as well.

Sunday, 9 November 2014

Microsoft Expression Encoder

This post is not in response to a specific problem or question, but could be a response to a potential question from some one.

I was looking for a cost effective and easy to use tool for capturing screens and produce videos. There are many tools, in the freeware, shareware and commercial category for the purpose. But the one that I find interesting is the one from Microsoft, the Expression Encoder. While Microsoft has stopped supporting the commercial version of the product, the same is available as a free download. I am sure, many of you might know about this, but for those who are not, this post will give a glimpse, so that one can start using it and see its advantages.

Expression Encoder Pro is a component of the Expression Studio, which is currently not sold as a product. In addition to Expression Encoder Pro, the studio also bundles with it Expression Web, Expression Blend and Expression Design. Expression Web is intended for creation of responsive websites leveraging CSS3 and HTML5. Expression Blend is intended to build interactive UI for windows store applications. Expression Design, combined with Expression Blend and Visual Studio helps building innovative, user-centric, fast and fluid applications. Check out more about the changes and availability of Expression products on the microsoft site.

Let us look at what Expression Encoder can offer us. Expression Encoder 4 Screen Capture is also installed along with the Expression Encoder 4. The Screen Capture component offers you to record videos using the camera attached to the device / PC, record audio using the microphone, and to record the selected area of the screen.



On clicking the Record button, you will be prompted to select a rectangle area to capture and once done, you can continue with your actions on the screen as the recording continues.Once, done, you stop the recording and the output is produced as a .xesc file, which can be further edited using the Encoder.

To get your captured video encoded into a video output, you will perform the following using the Expression Encoder

  • Choose your project. 
  • Import your source video. 
  • Modify your video. 
  • Set preview and encoding options. 
  • Choose an output format. 
  • Render your video.
Getting used to was not very difficult and the product comes with a useful help document. The tool allows you to overlay visual still and moving image file, edit the sub titles, captions and descriptions, add audio streams, apart from allowing you to insert and cut parts of the source video.



Few of the limitations include, inabitlity to add more than one overlay, the supported output format limited to Windows Media Video (.wmv) and IIS smooth streaming. While the tool has much more capabilities, for the specific purpose of creating product demo or tutorial videos, this suits very well. As you know there are many tools out there to convert .wmv to other video formats, like .mp4.

Try it out and share your feedback here.