Drilling 2 steps in Reports Viewer 2005

Imagine a situation where you have a report that has a link to another report. The other report also has a link to another third report. This sounds logical and easy, and it works perfectly in the report server viewer. But when you try to deploy your reports and view them using the report viewer you have in Visual Studio 2005, it throws an exception:

The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)

As far as I know, this problem is only available in the report viewer of Visual studio 2005. I worked out a work around for this issue.

The Idea:

You have to handle the event of drilling and tell the report viewer to cancel the event, collect the parameters of the new report and set them, and finally set the report path of the report viewer to the new path. Now you are done. Have a look at the code below.

   1: protected void ReportViewer_Drillthrough(object sender, DrillthroughEventArgs e)

   2: {

   3:     /// This version of the report viewer doesnt support the nested reports

   4:     /// this section is used to rerender the whole thing.

   5:     string[] values = null;

   6:     

   7:     // Get All possible parameters that this report takes

   8:     ReportParameterInfoCollection reportParms = e.Report.GetParameters();

   9:     // Create an empty list of parameters that i will send

  10:     List<ReportParameter> parms = new List<ReportParameter>();

  11:     foreach (ReportParameterInfo i in reportParms)

  12:     {

  13:         // if the parameter value in nullable, and I didnt choose to send a value

  14:         if (i.Values.Count == 0)

  15:             continue;

  16:  

  17:         else if (i.Values.Count > 1)

  18:         {

  19:             values = new string[i.Values.Count];

  20:             i.Values.CopyTo(values, 0);

  21:             parms.Add(new ReportParameter(i.Name, values));

  22:         }

  23:         else

  24:         {

  25:             parms.Add(new ReportParameter(i.Name, i.Values[0]));

  26:         }

  27:     }

  28:  

  29:     // Now reset the viewer, as if its a new request

  30:     ReportViewer.Reset();

  31:     // Cancel the drilling action

  32:     e.Cancel = true;

  33:     // Reset the ReportServerURL, this is optional.

  34:     // If you dont know what to write here, try removing it.

  35:     ReportViewer.ServerReport.ReportServerUrl = new Uri(Session["SSRS Server"].ToString());

  36:     // Set the new report that will be displayed, and set its parameters

  37:     ReportViewer.ServerReport.ReportPath = e.ReportPath;

  38:     ReportViewer.ServerReport.SetParameters(parms);

  39: }

One draw back of this solution, is that the back button of the report viewer is not effective anymore. On the other hand, the browser's back navigation button is effective, and can take you to the previous parent report. However, if you clicked any other link in the parent report, it will throw an exception:

Execution 'vzlnjkfdkub0oan53dig5g55' cannot be found

I thought of a solution, which I don't know is acceptable for you or not, which is disabling the browser's back button using JavaScript (see below), and adding a navigation expression in my report. i.e. adding a textbox in the report, with text something like "<< Back" which jumps to the parent report, and sending the needed parameters.

   1: <script language="javascript" type="text/javascript" >

   2:     history.forward();

   3: </script>

Setting A report's Data Source to a Shared Data Source Programmatically

One of the things that you will surely need when adding reports to the reporting service programmatically, is setting its data source to the shared data source you have in the reporting server. I personally had this problem and I thought that "I can create a new DataSource Object, then use the SetItemDataSources method to attach this data source to the report". That's what I actually did, but I always had an exception telling me that, The data source ABC is not found, although am sure that the data source name is the same like the shared data source's name.

What I found out later is, the SetItemDataSources method searches the report's data sources for the name you have set, i.e this name is not the shared data sources' name, but the data sources that is in the rdl file (in the <DataSources> tag).
So in order to fix that, we need to tell the reporting service that the report's data source with the name X (which we will see how to find it out) is still there, but it will reference to the shared data source.
so now lets get our hands dirty with some code.

How to

//Get the Shared Data Source
DataSource[] ds = GetSharedDataSource(service, reportsFolder, reportName);
// Set Report's DataSource
service.SetItemDataSources(@"/" + reportsFolder + @"/" + reportName, ds);

public static DataSource[] GetSharedDataSource(ReportingService2005 service, string reportsFolder, string reportName)
        {
            DataSourceReference reference = new DataSourceReference();
            DataSource ds = new DataSource();
            reference.Reference = "/" + reportsFolder + "/" + "SharedDataSource";
            ds.Item = (DataSourceDefinitionOrReference)reference;
            // Get original report Data Source Name
            DataSource[] reportDataSource = service.GetItemDataSources(@"/" + reportsFolder  + @"/" + reportName);
            ds.Name = reportDataSource[0].Name;
            return new DataSource[] { ds };
        }

Building your ASP.NET MVC application with Subsonic 3.0

Subsonic really rocks... I loved Subsonic 2.0 and took the risk of starting a huge application using Subsonic a year ago while it wasn't that popular when comparing it to other DAL generators like .NetTiers template of codesmith. But to be honest, I loved subsonic since then very much, being so easy, light, straight forward, not complex, and what is the most important, getting things done just the way I want them.

Suddenly, Rob Conery introduced the new Subsonic 3.0 which is targeting the ASP.NET MVC technology. This sounds really great, and I liked the idea so much, but the question is.. Where can I learn it from? So far, all screen casts that Rob did are kind of "showing off" the new subsonic. Nothing published so far showing you how to create an application from scratch using Subsonic on Database called XYZ for instance like he used to do with earlier versions. Maybe he is waiting for the final release of Subsonic 3.0 to do so.

I liked Rob Conery's comment that he added in Preview 2's package. It was something like "This is a Preview version.. so BE GENTLE".

Anyway, I am really waiting for this version of subsonic, I guess it will be a good plus for using ASP.NET MVC.

Now lets talk about Subsonic, and how to get your application working using subsonic 3.0. Please note, that Subsonic 3.0 is a brand new tool, and am not that good in it. I learnt that from trial errors, so don't hesitate to correct me if am wrong in anything am posting.

Prerequisites

• Install MVC into your visual studio from here.
  
• Install Subsonic Template from here.

Installation of the MVC on Visual Studio is a straight forward issue. You have to run the msi file and you are done.

For subsonic, Place the zipped file you downloaded into the following directory (My Documents\Visual Studio 2008\Templates\ProjectTemplates\Visual Web Developer\Web). Don't extract the zip file, but keep it there as it is.

Starting your application

Open Visual Studio. Then from the files menu, Create a new Project.

As shown below. Choose the Subsonic MVC Template, then press ok.

The solution structure will look exactly like that:

Now, open the web.config file, and edit the connection strings. This is the main part, as everything is based upon the DB you are using. The whole DAL will be generated according to the Database. Dont forget to change the Database name as well. We will later make the subsonic get to know the new name.

<connectionStrings>     <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>     <add name="Chinook" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Chinook.mdf;User Instance=true" providerName="System.Data.SqlClient"/> </connectionStrings>

Now here we come to the tricky part, that caused me a lot of confusions. I used to miss this part and end up all the time hitting my head against the wall.

Open the Model/_settings.tt file. This is one of the T4 templates subsonic is using. Edit this part:

const string Namespace = "SubSonic.Web.Models"; const string ConnectionStringName="Chinook";

Now you can change the ConnectionStringName to the name you defined in the web.config section, then save the file.

Creating the DAL

Right click on the _settings.tt file, and select the "Run Custom tool" option as shown below. Repeat this step with the 4 Template files in the Models directory.

You will find some warning messages in the error list while running the template files; just neglect them. Always try to remember Rob's phrase I stated earlier; "Be Gentle, this is a preview version" ;) So I guess it's better to choose not to show the warning messages in the error list :)

Now time to build. Oops, 3 errors? Let me tell you that now you can say "I successfully generated my DAL using Subsonic!!" They are as simple as adding this line "using SubSonic.Web.Models;" into HtmlExtensions.cs and SubsonicRepository.cs files.

Now we are done.

You can learn how to create the Models, Views, and controllers using subsonic's tools, I recommend watching Rob's screencast. Although he showed everything quickly and without any explanations, but I think it doesn't need any.

Correct me if am wrong in any of the tips I stated. I told you, no one knows better than the other when it comes to beta editions of software ;)

Creating your own site's search Engine

Setting up you search page for your web application is as easy as having a DB table where you retrieve data from by a simple query. I will illustrate the steps of setting up the configurations needed in this post, and will write another one for detailed stuff in custom search page. So lets start.

Setting up your Search Index

1. start>run> type mmc
   
2. in the new window, File> Add Remove Snap-in, or press CTRL + M as a shortcut
   
3. Press Add, and choose Indexing Service from the List box as shown below
   
   Press the Add button.
   
4. A dialog box will appear asking you about the Computer name you want to apply the Indexing on, choose the local computer for this Demo.
   
   
5. Close all dialogs you have on your screen until you reach your Console Window, with Indexing Service running.
   
6. Right Click on the Indexing Service node under the Console Root, and choose New>Catalog.
   Make sure that the Start option in the menu is activated before doing this step, otherwise, you have to stop the service first.
   
   
7. Give the new Catalog any Name. I will call it PP4.
   Browse to a directory where the Catalog file will be stored in. This is not the directory which you want to Index.
   
   
8. You will find a new node called PP4 just appeared under the Indexing Service node.
   Now we want to tell the new Catalog to search in your site. Right click on the PP4 Catalog, and choose New>Directory
   
   
9. Now fill in the Data as below
   
   The Path, is the Physical Path of the website you'd like to Index. Finally press OK.
   
10. Now right click on the PP4 Catalog, and choose Properties. Go to the tracking tab, and change the WWW server to your Default Website.
    
    
11. Now go to the Generation tab, and uncheck the checkbox "Inherit above settings from Service".
    
    It Enables by default the "Generate abstracts" checkbox. This tells the search index, to get some text from the searched pages, just like Google.com for instance when they get you a sample text under each item in the search result. The default is not generate any abstracts. This text is by default the first 320 characters in the page. You can customize that just by adding some text in the "description" meta tag in the HTML's head section.
    
12. Now start the Service.
    

Now we are done with setting up the search index configurations. Next you have to create a page that calls this catalog for searching. But before doing this, lets try to figure out whether everything is working fine or not.

 

Querying your Search Index through the built in page

Under the PP4 Catalog we have just created, you will find a node called "Query the Catalog". Press on that node and a page will load up at the right part of the window as shown below. Here you can try by typing "Partners" for instance, and have a look at the query result. As you can see, the query includes .cs, .vb, .css files and many other types of unwanted files. you can control that by creating your own page, using your own code.

 

Querying your Search Index through a custom page using Query Language

I will go through the main parts of the query, and you can figure out the rest. It's as if you are querying a simple SQL Database and binding the results to a Repeater.

Connection:

    OleDbConnection odbSearch = new OleDbConnection( "Provider=\"MSIDXS\";Data Source=\"PP4\";");

    Please note that the Data Source is the Catalog name you specified in the Search Index configurations.

Command:

    cmdSearch.Command Text = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '"+ searchText +"') order by  rank desc ";

Where the "searchText" is the text you typed in the textbox for searching.

The rest is as easy as executing the query and binding the results to a repeater. I have made one on my own and took a snap shot of the running program below: