10 Common SSRS Challenges
Check out my Article on Simple-Talk.com: http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/
Create application Shortcut in .Net the easy way
Have you ever needed to create an application shortcut on the fly in your .Net application? Well I have and this is what I did. The easiest way to create a shortcut is to create a url file which contains a reference to your file instead of a web address. A url file is basically like an ini file. To create a real desktop shortcut requires the use of a COM object, which for my case was not needed.
A url file is a plain text file with the “url” file extension. Below is an Example of the required content of a url file:
[InternetShortcut]
URL=http://rduclos.wordpress.com
There are other options that can be added to the url file as well, but all you need to specify is the URL. Rather than using a url to a internet location you can create one for a file on your system by setting URL to file:///c:/Temp/ReferenceFile.txt. Example of a url file content with additional options to specify the icon:
[InternetShortcut]
URL = file:///c:/Temp/ReferenceFile.txt
IconIndex = 0
IconFile = C:\Temp\application.ico
Here is an example of how to create the url file in C# using .Net 2.0:
using System;
using System.IO;
using System.Text;
public static class Program {
public static void Main() {
CreateDesktopShortcut();
}
private static void CreateDesktopShortcut() {
String appName = "Application.exe";
String appDirectory = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
String shortcutName = "Application";
String shortcutPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
StringBuilder sb = new StringBuilder();
sb.AppendLine("[InternetShortcut]");
sb.AppendLine(String.Format(@"URL=file:///{0}/{1}", appDirectory, appName).Replace(@"\", @"/"));
File.WriteAllText(String.Format(@"{0}\{1}.url", shortcutPath, shortcutName), sb.ToString());
}
}
Reporting Services 2005 – Dynamic Parameter List
I have seen developers writing reports in Reporting Services and taking a lot of time adding a section to the report to list all the parameter selection(s) to be displayed in the report. To address this issue dynamically (my way) you will need your reports deployed to the reporting services server, and have access to the reporting services database. Here you will find some template reports which include the Dynamic Parameter List (http://www.box.net/shared/zi7m4z62tg). Reporting services stores everything about the report in the database back-end. Gain access to the back-end and you will be able to do all sorts of things. For instance maybe create a report of your complete catalog of reports or even write some report metrics by looking at the report Execution log.
Here is my Query for obtaining the report parameter information, from the reporting services back-end:
SELECT
[Order] = ROW_NUMBER() OVER (ORDER BY tmp.Name),
ParameterName = Params.p.value('Name[1]','varchar(255)'),
ParameterLabel = Params.p.value('Prompt[1]','varchar(255)'),
DefaultValue = Params.p.query('DefaultValues/Value'),
ParameterXml = Params.p.query('.'),
ReportName = tmp.Name,
ReportPath = tmp.Path
FROM
(SELECT Name, Path, ReportParams = CONVERT(XML,Parameter) FROM dbo.Catalog WHERE Path = @ReportPath) tmp
CROSS APPLY ReportParams.nodes('(Parameters/Parameter)') AS Params(p)
WHERE NOT Params.p.value('Name[1]','varchar(255)') IN ('ReportPath','ReportNumber')
The parameter “@ReportPath” is defaulted to “=Globals!ReportFolder + “/” + Globals!ReportName” this is how the report is referenced in the catalog.
Now that I have the parameter collection, I have a matrix control at the top of the report to display the parameters. I am using some custom code to get my parameters to be displayed horizontally in a pre-determined number of columns:
Shared Function SetColumn(ByVal row As Integer, ByVal column As Integer) As String
While row > column
If column >= row Then Exit While
row = row - column
End While
Return row
End Function
Shared Function SetRow(ByVal row As Integer, ByVal column As Integer) As String
Dim i As Integer = 1
While row > column
i = i + 1
If column >= row Then Exit While
row = row - column
End While
Return i
End Function
I group my rows using the two methods mentioned above, for instance if i want a 2 column list the columns group contains expression “=Code.SetColumn(Fields!Order.Value,2)” and the rows group contains expression “=Code.SetRow(Fields!Order.Value,2)”
The Templates in the zip at http://www.box.net/shared/zi7m4z62tg contain multiple features:
- Horizontal Record display
- Using Images in Page Header without causing Merged Cell issues in Excel
- Templates for Landscape and Portrait you can build from
- Dynamic Parameter List
- Using Reporting Services database to your advantage
- Using Custom Code in RDLs
- Using Parent Groups and Padding based on levels
- Using rectangles to create Page breaks so different section of the report when exported to excel display on separate worksheets
Reporting Services 2008 – Report Authoring
I just did my first Presentation for the LANUG group in Mobile, AL on November 18th, 2008! I started out a little nervous but things overall went well. Now that I got my first presentation out o the way I plan to do more in the coming years.
