February 15, 2008 at 6:48 am
Hello All,
Am implementing wildcard functionality for reports. Am giving option to select the DB field name, WildCard and value for the selected field. I am able to pass the where clause to SP, but am not getting results in the report. Is there any other way to do the wildcard functionality in UI?
Below is my SP and ASp.NET code:
@DefApp nvarchar(255)= '' ,
@DefBusFunction nvarchar(255)= '' ,
@DefImpact nvarchar(255) = '',
AS
Begin
declare @sql nvarchar(4000)
declare @whereClause nvarchar(4000)
DECLARE @return_value int
declare @sqlWhere nvarchar(4000)
select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)'
if @DefImpact <> ''
Set @whereClause = ' where DefImpact '
SET @whereClause = @whereClause + ' = ''' + @DefImpact + ''''
set @sqlWhere=@sql + @WhereClause
EXEC @sqlWhere
End
and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like:
sqlCmd = new SqlCommand("subbusample", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString()));
sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString()));
sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString()));
RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");
RptViewer.ShowParameterPrompts = false;
RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";
when i execute it, i am not able to fill the report with data.
Please let me know what i am doing wrong.
Thanks,
SR.
February 15, 2008 at 7:22 am
Subba,
Try this. At the top of your stored procedure, just after the AS clause put in:
set concat_null_yields_null on
Also, for your IF statement, try:
if @DefImpact <> ''
begin
set @whereClause = ' where DefImpact '
set @whereClause = @whereClause + ' = ''' + @DefImpact + ''''
end
February 15, 2008 at 7:26 am
Sorry,
Made a mistake there, that's supposed to be:
set concat_null_yields_null OFF
SQLZ (2/15/2008)
Subba,Try this. At the top of your stored procedure, just after the AS clause put in:
set concat_null_yields_null on
Also, for your IF statement, try:
if @DefImpact <> ''
begin
set @whereClause = ' where DefImpact '
set @whereClause = @whereClause + ' = ''' + @DefImpact + ''''
end
February 15, 2008 at 7:43 am
Have you verified that you are getting an empty string from the app? Are you sure youare getting data back to the app? I would change the if in the sp to use nullif(value,'') is not null.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 15, 2008 at 8:20 am
Hi,
I tried in that way. No luck, is there anything i missed in ASP.NET code.
Please let me know.
Thanks,
SR.
February 15, 2008 at 8:43 am
I am not sure what you are trying to do here based on your SP.
If you are trying have Blank Parameter to be passed into your stored Procedure ignored, you can use the following syntax
AND ((@DefImpact IS NULL) OR (Def.DefImpact = @DefImpact )) --If null is passed to variable clause will be ignored.
Can i ask why you are using dynamic SQL
February 15, 2008 at 9:26 am
WHere are you setting the report data source to the sqlcommand you are executing?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 15, 2008 at 9:35 am
why because we are implementing wildcard functionality in UI. user can select any DB field, any wildcard and can give any value to the field.
for this reason i need to build the sql dynamically.
February 16, 2008 at 7:38 am
For server report, we will be assigning the datasource at design time. when we execute the report, it automatically execute the Datasource.
Is there anyway can we implement this functionality?
February 16, 2008 at 8:15 am
To accomplish what you are trying to accomplish you do not need to create the connection and command in the ASP.NET page. You just need pass the parameter values to the report. This link gives examples in C# and VB on how to do it http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.serverreport.setparameters(VS.80).aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply