Report Parameters
in [Reports]
|
From: James on 29 Apr 2010 16:43 I am familiar with other reporting tools like crystal and access but am new to ssrs. It seems like I have everything setup right but the report is only returning one record. I am passing three parameters to a report. The three parameters are @as_of_date, with available values set to none, default values set to none, advanced is always refresh. The next parameter is @mad_flag, data type text, available values set to specify, I have two values, Y and N for Yes and No, Advance set to never reset. The third parameter is @org_level_two_id, which is getting its value from a dataset named ds_distinct_org_two_level. I have the value field set to the id, and the label field set to the name. When I view the report all the parameters are displayed and I am able to enter data. For the as of date a calendar control displays, for the mad flag a drop down list with yes/no and for the org level two and drop down list with all the org level two names. The ds_distinct_org_two_level dataset has the following for the query: select distinct OrganizationalLevel2Id, OrganizationalLevel2Name from dbo.vw_CM_T_ORG_HIER_EOM order by OrganizationalLevel2Name This is populating the drop down list correctly. The other data set I have is named ds_usp_trial_balance and is based on a stored procedure in my data source named usp_trial_balance.This stored procedure takes the same three parameters. If I execute this stored procedure I get the desired result. If I go into the dataset properties and run the query, passing the same parameter values it returns the same number of records. I dragged a couple of field onto the report and the report runs, but only one record is ever returned. Am I missing something simple? I would like to confirm the report is a detail report, the fields look like they are in the body of the report. is there a way to see the sql code that is generated for the report? any help is appreciated. Thanks.
From: Bruce L-C [MVP] on 29 Apr 2010 17:20 You need to put either a list or a table object onto the report design surface and then drag your fields onto that. Or use the report wizard. If you have a query that you have developed that takes parameters then just put that in for the query in the wizard and RS will create automatically the report parameters with the same name and bind them to the query parameters. For instance, select somefields from some table where field1 > @as_of_date and field2 = @mad_flag and field3 = @org_level_two_id Then the report wizard will create everything for you and you can see what it has done. Then you can manually (I don't always let the wizard do its thing, I sometimes do it all manually) -- Bruce Loehle-Conger MVP SQL Server Reporting Services "James" <James(a)discussions.microsoft.com> wrote in message news:C6BF92F1-8B73-4C1E-9296-279C4CB195E5(a)microsoft.com... > I am familiar with other reporting tools like crystal and access but am > new > to ssrs. > It seems like I have everything setup right but the report is only > returning > one record. > I am passing three parameters to a report. > The three parameters are @as_of_date, with available values set to none, > default values set to none, advanced is always refresh. > The next parameter is @mad_flag, data type text, available values set to > specify, I have two values, Y and N for Yes and No, Advance set to never > reset. > The third parameter is @org_level_two_id, which is getting its value from > a > dataset named ds_distinct_org_two_level. I have the value field set to the > id, and the label field set to the name. > When I view the report all the parameters are displayed and I am able to > enter data. For the as of date a calendar control displays, for the mad > flag > a drop down list with yes/no and for the org level two and drop down list > with all the org level two names. > The ds_distinct_org_two_level dataset has the following for the query: > select distinct > OrganizationalLevel2Id, > OrganizationalLevel2Name > from dbo.vw_CM_T_ORG_HIER_EOM > order by OrganizationalLevel2Name > This is populating the drop down list correctly. > The other data set I have is named ds_usp_trial_balance and is based on a > stored procedure in my data source named usp_trial_balance.This stored > procedure takes the same three parameters. If I execute this stored > procedure > I get the desired result. If I go into the dataset properties and run the > query, passing the same parameter values it returns the same number of > records. > I dragged a couple of field onto the report and the report runs, but only > one record is ever returned. Am I missing something simple? I would like > to > confirm the report is a detail report, the fields look like they are in > the > body of the report. is there a way to see the sql code that is generated > for > the report? any help is appreciated. Thanks. > >
From: James on 30 Apr 2010 08:46 Adding the table object did the trick. Thanks! "Bruce L-C [MVP]" wrote: > You need to put either a list or a table object onto the report design > surface and then drag your fields onto that. > > Or use the report wizard. If you have a query that you have developed that > takes parameters then just put that in for the query in the wizard and RS > will create automatically the report parameters with the same name and bind > them to the query parameters. > > For instance, > > select somefields from some table where field1 > @as_of_date and field2 = > @mad_flag and field3 = @org_level_two_id > > Then the report wizard will create everything for you and you can see what > it has done. Then you can manually (I don't always let the wizard do its > thing, I sometimes do it all manually) > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "James" <James(a)discussions.microsoft.com> wrote in message > news:C6BF92F1-8B73-4C1E-9296-279C4CB195E5(a)microsoft.com... > > I am familiar with other reporting tools like crystal and access but am > > new > > to ssrs. > > It seems like I have everything setup right but the report is only > > returning > > one record. > > I am passing three parameters to a report. > > The three parameters are @as_of_date, with available values set to none, > > default values set to none, advanced is always refresh. > > The next parameter is @mad_flag, data type text, available values set to > > specify, I have two values, Y and N for Yes and No, Advance set to never > > reset. > > The third parameter is @org_level_two_id, which is getting its value from > > a > > dataset named ds_distinct_org_two_level. I have the value field set to the > > id, and the label field set to the name. > > When I view the report all the parameters are displayed and I am able to > > enter data. For the as of date a calendar control displays, for the mad > > flag > > a drop down list with yes/no and for the org level two and drop down list > > with all the org level two names. > > The ds_distinct_org_two_level dataset has the following for the query: > > select distinct > > OrganizationalLevel2Id, > > OrganizationalLevel2Name > > from dbo.vw_CM_T_ORG_HIER_EOM > > order by OrganizationalLevel2Name > > This is populating the drop down list correctly. > > The other data set I have is named ds_usp_trial_balance and is based on a > > stored procedure in my data source named usp_trial_balance.This stored > > procedure takes the same three parameters. If I execute this stored > > procedure > > I get the desired result. If I go into the dataset properties and run the > > query, passing the same parameter values it returns the same number of > > records. > > I dragged a couple of field onto the report and the report runs, but only > > one record is ever returned. Am I missing something simple? I would like > > to > > confirm the report is a detail report, the fields look like they are in > > the > > body of the report. is there a way to see the sql code that is generated > > for > > the report? any help is appreciated. Thanks. > > > > > . >
|
Pages: 1 Prev: Word page templates in SSRS, and creating a summary page in SSRS Next: Start report on Page 2 |