ssrs , wcf service problem

When you are consuming wcf service in SSRS reports, make sure the following points.
1) WCF Service must be having 'basicHttpBinding' configuration
2) number of parameters, their names, order must be followed as it is.
3) Do not press the 'Refresh' button
4) Write the Query command correctly
5) Enable the following web.config setting in service. This will allow you to view the actual exceptions raised.


even a small mistake will make your life miserable. So, Please take extra care.

Enjoy!! :)

The underlying provider failed on Open

In WCF service if the following error occurs. "The underlying provider failed on Open." Resolution : comment the transaction code. and Process.Complete() statement.

generate scripts in SQL Server

Generating SQL Scripts in sql server.

1. open the sql server management studio
2. you can find list of database schemas, select the required database
3. right click and select the generate scripts option
4. select views, stored procedures, user defiend functions you will get them in new window.
   

exec sp_msforeachdb @command1='select ''?'' as databasename, * from ?.dbo.help'

wcf service endpoint problem

In WCF service project end point information, binding configuration information is provided for each service in the project. If these information is not present in the web.config file, we can create proxy class for the service. But it gives runtime error saying "dual configuration present in the system". To avoid this problem verify the end point, binding configuration sections for the service.

edmx file problem with views

If we add new views to the existing edmx file, the file ".Designer.cs" is updated with the default primary key entries. but the ".edmx" file has the manually updated values. Because of this conflict, it will show compilation errors during building.
To avoid this problem we have to make changes to the ".Designer.cs" file. and it should be in-sync with the edmx file. make sure "Nullable" attribute of the column should have "false" as the value.

Pareto analysis and Pareto chart

Pareto analysis and Pareto chart
How to draw chart:-

1) take values for "cause" in first column.

2) take percentage for "problems" in second column.

Note:- this column should be in descending order of values.

3) enter cumulative percentage values in third column.

4) insert a chart

5) select chart type "Line - column of 2 Axes"

6) provide the three columns data as inputs for the chart

7) place it in a proper position in the sheet.
How to edit the options in the chart:-

1) You can enter "Title", "X-Axis", "Y-Axis" headings. Font, color etc.. you can change

2) you can modify the text orientations.

3) double click on the x-axis, y-axis values for modfiying the range, min, max etc.. values.

To display methods in wcf service

When you open the wcf service in browser, you will get the following url in the address bar
http://localhost:3711/OutgoingMoneyService.svc?wsdl here you will not find any methods or metadata.
Modify the url of the wcf service as follows to get the list of methods available in the service.
http://localhost:3711/OutgoingMoneyService.svc?wsdl=wsdl1

ToString( ) conversion option in LINQ to entity coding

When you write any coding like ToString() in linq code will fail. for example
var query = from p in ctx.Employee where p.empno == intEmpno.Tostring() select p;
this will give error because of the tostring() conversion. so, avoid this type of conversion coding while writing linq to entity. alternatively you can modify the above code as
string strEmpno = intEmpno.Tostring();
var query = from p in ctx.Employee where p.empno == strEmpno select p;

SSRS Report Parameters Order

While developing ssrs reports using wcf services as datasource. please make sure that
class, members are decorated with
[MessageContract(IsWrapped = true)]
public class AccountingTaxReportRequest
{
[MessageBodyMember]
public int ProcessMonth { get; set; }
}
so, create the respective number of parameters in the ssrs file. Now in the query string the order of the parameters is important.

best way is to consume the wcf service in a sample application. observe the order of parameters in which they appear there. Follow the same order of parameters, datatype in the ssrs report too.

It saves lot of R & D for you.

Best of luck.

Multiple columns Group by option with LINQ

Group by clause is very common while writing T-SQL queries. How can we do the same in LINQ using c# and EDMX files.

Here is the code snippet for your reference wherein InvoiceNo, InvoiceRowNo are the two group by columns.

// Step 4: Extract amount paid for each InvoiceNo,InvoiceRowNo

var querymoneyFinal = from ml in moneyList
group ml by new { ml.InvoiceNo, ml.InvoiceRowNo } into g
select new MatchedPaymentForStockReportDTO
{
InvoiceNo = g.Key.InvoiceNo,
InvoiceRowNo = g.Key.InvoiceRowNo,
Amount = g.Sum(w => w.Amount)
};
List MoneyFinalList = querymoneyFinal.ToList();

the equivalent T-SQL code will be some thing like the following:
Select InvoiceNo,InvoiceRowNo, sum(Amount)
from moneyList group by InvoiceNo,InvoiceRowNo;

Hope you enjoy it !!

Bye Bye come up with more code snippets.

SSRS sub totals using WCF services

SQL Server 2005 reporting services can communicate with services developed using WCF like connecting to SQL Server databases.
Sub totals can be added to the columns by adding the expression to the textbox placed at the table footer.
=Sum(Fields!Salary.Value,"DataSet1")
This works fine in case of report trying to retrieve values from the sql server database using sql queries or stored procedures. But the same expression will give error in case of wcf service.
Make the following change to display the sub totals.
=Sum(CInt(Fields!Salary.Value),"DataSet1")