Create Telerik Report with MsSql Stored Procedure

[Rose]

Hello,

Am very new to Telerik software and so I need your help please. I have a project to create different reports using telerik reporting but all I am given is the MsSql stored procedure codes from an existing database. When I look read the telerik reporting tutorial, it shows steps on how to populate and assign datasource and to drag and drop the fields from a table into the detail section of the designer (middle section) of the tab. But when using stored procedure codes, there are no actual table to drag and drop just codes to carry out a command when data is put on the front-end (website). How can this code be added to the report? Thanks!

[Peter]

Hi Rose,

We will need additional information. What do you mean by "there are no actual table to drag and drop just codes to carry out a command when data is put on the front-end (website)"? Do you mean that you don't see the data fields? If this is the case make sure that you have provided correct design time parameter values in the SqlDataSource wizard. Additionally you may find useful the Temporary Table in Stored Proc forum thread.

All the best,
Peter
the Telerik team

[Rose]

I created my design page (.aspx) and dragged the 3 text boxes, the labels and the submit button. Two of the three text boxes is to enter date range. The third text box is to select from a dropdown list. I connected this dropdown list to my datasource (the applicable column in my table) then I tried to also connect the submit button to my stored procedure codes directly from my database to execute the command but neither datasources work. Your tutorial says I have to create a "viewer" page as well but am not sure how to link it and why I need another "viewer" page. Also, I setup the parameters for the dates to be entered on the two textboxes on the .aspx page but can't seems to put all these things together. Any assistance will be helpful, thanks.

[Stef]

Hi Rose,

Following the described practices from our online help, you need to:

1. Create a Class Library project and add a report to it - Create the Class Library for the Reports. Notice that in your case you need to specify a valid .Net provider to your MySql database, then create a connection string and test the connection(Steps 7 and 8 from the article);
2. Following the SqlDataSource Wizard you would need to select a stored procedure from the list or write a valid SQL select statement( Step 3 from the wizard how-to). On step 4 you can link any sql parameters to report parameters and at step 5 you can set some actual values just to preview the result from the query (if there are multiple select statements in the query, only the first will be returned);
3. Drag some fields from the Data Explorer to the report surface at design time to create a sample report.
4. The so created report can be displayed in an application using a ReportViewer control depending on the selected technology - ASP.Net ReportViewer control in your case, which is a standard control which can be added to an aspx page. The report is passed to the viewer control via report source instance, which passes report parameters values to the report document if needed.

You can check the attached sample project illustrating the described. We have used AdventureWorks database for MSSQL in the example.

In addition, if you have selected to install the demos, they can be found under C:\Program Files (x86)\Telerik\Reporting Q1 2013\Examples(the default installation directory for 64x machines).

Kind regards,
Stef
the Telerik team

[Rose]

I have attached a screen shot of what I have done. The steps you sent to me, I have done most of those. I have my .aspx screen where there are text boxes in which parameters were passed already. I also have a .vb (class) page where the fields generated the report correctly (see screen shot of it). I then created a reportviewer.aspx page as well which is where I don't know how to put it together. Trying to follow the link you sent me, I can't find the screen shown under properties. I tried going back to the tutorial, I changed "Action" in the properties to point to my datasource but no choice to pass the parameters since it is a  stored procedure and not a select statement. Help please...

[Stef]

Hello Rose,

This video tutorial may make more clear the mapping of SQL parameters to report parameters: Using Stored Procedures in Telerik Reporting with the SqlDataSource Component.
Beside this Actions are on report level and allows you to:

• Navigate to a URL, where parameters can be passed through the query string;
• Drillthrough Report within the same page and viewer, where parameters can be set using the report source instance passed to the action;

If we assume you have set the data, prepared the report document and have the ReportViewer in a page, please, elaborate on the scenario - contained pages, needed navigation and required parameters to be passed. Additionally we will appreciate if you open a support thread and send us a runnable sample to review on our end.

Greetings,
Stef
the Telerik team

[Rose]

Here is the scenario:

I have a database that contains  e.g. 4 tables and a stored procedure that prompts user to certain info: For example

Table 1: personId, firstName, lastName, address, unitId
Table 2: unitId, unitName
Table 3: caseId, caseType, personId
Table 4: eventId, event, startDate, endDate, caseId


Stored procedure looks something like this:
The three parameters are: @unitId int, @startDate datetime and @endDate datetime

The code is like.... SELECT table1.personId, table2.unitId, table3.caseType, table4.startDate, table4.endDate from table 3
inner join (I do all the joins here blablabla)
WHERE table2.unitId = @unitId, table4.startDate = @startDate, table4.endDate = @endDate


As you can see, on the front end the user has to TYPE INTO text boxes, the unit, a start date and end date to get a report. No drop down list to choose from (which makes it very different from the examples you have on your demos). After the unit and dates have been typed in, they click a submit button which executes the stored procedure above to give a report showing the personID, unitId., caseType, startDate and endDate for that specific unit with the date range typed in.

Hope this clarifies what I need. Thanks.

[Stef]

Hello Rose,

You can find attached a sample project illustrating two approaches:

1. Having the data and parameters in the report definition;
2. Passing parameters and data from outside the report;

I hope this points you to the right direction.

Kind regards,
Stef
the Telerik team

[Rose]

Do you have these examples in other codes except c#? Also can you point me in the direction of where the codes are to be placed? That is, is code written in .vb or .aspx page. Am not even sure. Do you have a tech support that can work me through using your software please? Thanks.

[Stef]

Hi Rose,

Since the reports were created with the designer any code writing can be avoided. Only in the web application part of the example there are few lines loading some data and assigning it to the Report.DataSource property. In VB the these lines look as follows:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


       If Not IsPostBack Then
           Dim myReport As New Report1()

           Dim sql = "[dbo].[uspGetManagerEmployees]"
           Dim connectionString = "Data Source=(local)\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True"
           Dim command As New SqlCommand(sql, New SqlConnection(connectionString))
           command.CommandType = CommandType.StoredProcedure

           Dim sqlparam As New SqlParameter("@ManagerID", SqlDbType.Int)
           sqlparam.Value = 3
           command.Parameters.Add(sqlparam)

           Dim adapter As New SqlDataAdapter(command)
           Dim dataSet As New DataSet()
           adapter.Fill(dataSet)

           myReport.DataSource = dataSet.Tables(0)

           Dim reportSourceInstance1 As New InstanceReportSource()
           reportSourceInstance1.ReportDocument = myReport
           ReportViewer1.ReportSource = reportSourceInstance1

           Dim reportSourceInstance2 As New InstanceReportSource()
           reportSourceInstance2.ReportDocument = New Report2()
           reportSourceInstance2.Parameters.Add("ManagerID", 158)
           ReportViewer2.ReportSource = reportSourceInstance2

       End If
   End Sub

You may find interesting our consulting service providing training and mentoring according to your needs.

I hope this helps.

Greetings,
Stef
the Telerik team

[Rose]

Thanks. I was able to customize most of the codes you gave me to mine but can you explain the last few lines please?
           Dim reportSourceInstance1 As New InstanceReportSource()

           reportSourceInstance1.ReportDocument = myReport

           ReportViewer1.ReportSource = reportSourceInstance1

 

           Dim reportSourceInstance2 As New InstanceReportSource()

           reportSourceInstance2.ReportDocument = New Report2()

           reportSourceInstance2.Parameters.Add("ManagerID", 158)

           ReportViewer2.ReportSource = reportSourceInstance2

I am not sure if each parameter is to be listed as shown on the second to the last line above OR each parameter starts from "Dim" through all four lines. I replaced these 7 lines here with the following: (Is that how it's done?)

        Dim reportSourceInstance As New InstanceReportSource()
        reportSourceInstance.ReportDocument = NewCasesReport()

        ReportSourceInstance1.Parameters.Add("unit_id", 0)
        ReportSourceInstance2.Parameters.Add("dte_from", 0)
        ReportSourceInstance3.Parameters.Add("dte_to", 0)

        ReportViewer1.ReportSource = InstanceReportSource
        ReportViewer1.RefreshReport()

[Stef]

Hello Rose,

"unit_id","dte_from","dte_to" are names of report parameters existing within CasesReport report definition. Setting values to these report parameters happens through a single instance of report source object, InstanceReportSource in this case. Thus your code should look as follows:

Dim reportSourceInstance As New InstanceReportSource()
reportSourceInstance.ReportDocument = New CasesReport()

reportSourceInstance.Parameters.Add("unit_id", 0)
reportSourceInstance.Parameters.Add("dte_from", 0)
reportSourceInstance.Parameters.Add("dte_to", 0)

ReportViewer1.ReportSource = reportSourceInstance
ReportViewer1.RefreshReport()

The above code snippet creates new instance of CasesReport report definition, sets for each of the listed report parameters("unit_id","dte_from","dte_to") values of 0, and then passes the reportsource to the ReportViewer control to display the report.

Let us know if you need any further help.

Greetings,
Stef
the Telerik team

[Rose]

I keep getting this error code (unknown server tag) when I run my report:

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately. 

Parser Error Message: Unknown server tag 'telerik:RadDatePicker'.

Source Error: 

Line 20: <br /> Line 21: <strong>Date Range:&nbsp; </strong>From&nbsp; Line 22: <telerik:RadDatePicker ID="dteFrom" Runat="server" Culture="en-US"> Line 23: </telerik:RadDatePicker> Line 24:

I  followed a response from this link on your forum: http://www.telerik.com/community/forums/aspnet-ajax/general-discussions/unknown-server-tag.aspx

But I couldn't get report to run. Any ideas?

[Stef]

Hi Rose,

Please check RadControls for ASP.NET AJAX Documentation: Design-time Troubleshooting help article.

If you have further questions on the matter, please post your question into the appropriate forum, where it will be more likely to get an accurate answer from community members encountered the same issue. 

Regards,
Stef
the Telerik team

[Rose]

I am trying to resolve this issue, please help. I have Telerik reporting  2011 & 2013 on my Toolbox (as shown on left of picture) but when I go to add new item, I only see 2011 (right side of picture below) but I need 2013 Telerik reporting:

[Stef]

Hi Rose,

Please check Telerik Reporting Item Template is missing blog article. Notice in your case you are looking for Telerik_Reporting_Q1_2013.zip

I hope this helps.

Regards,
Stef
Telerik

[Rose]

Following the steps on the link you provided, Q1_2013 is present in ItemTemplates AND ItemTemplatesCache (see pictures below) but it is still not on the "add new item" list.

[Stef]

Hi Rose,

Notice the Telerik Reporting item template is not available for Web Site Projects. Please either migrate to Web Application project or create a class library for the reports that is then referenced in your project.

If this is not the case, please try to uninstall Telerik Reporting and install again as described in the KB article. You can upload a video of the installation process and post link here. Any additional details about the machine on which the installation is running, Visual Studio version, installed framework, application target framework will be helpful.

Regards,
Stef
Telerik