Real new person question here but during my query practise I created some
parameter queries in the enterprise manager however theses will not work when
I open query analyser. Can anyone tell where I’ve boobed?
Many thanks
Sam
What did you create?
Stored procedures?
Which database - are you in the correct database?
Easiest to create them using query analyser using create proc then you can
save the script.
"Sam" wrote:
> Real new person question here but during my query practise I created some
> parameter queries in the enterprise manager however theses will not work when
> I open query analyser. Can anyone tell where I’ve boobed?
> Many thanks
> Sam
>
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Friday, March 30, 2012
Parameter Prompt List & Filter selection
I have created a parameter for a report that displays a drop-down list of
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper
Parameter Problem
Hello:
RS Newbie here.
I have created a report with a parameter that has an ALL value that should
display all records when that particular parameter is selected.
Here is the code for my dataset:
SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
dbo.BuilderWO.Address,
dbo.BuilderWO.City, dbo.BuilderWO.State,
dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
dbo.BuilderWO.WalkDate,
dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
dbo.Employees.LName, dbo.Status.Status_Status
FROM dbo.Status RIGHT OUTER JOIN
dbo.BuilderWO ON dbo.Status.Status_StatusID = dbo.BuilderWO.Status LEFT OUTER JOIN
dbo.Locations ON dbo.BuilderWO.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Employees ON dbo.BuilderWO.TSHRep1 = dbo.Employees.EmployeeID
WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID = @.location)
Here is the code for the parameter:
SELECT LocationID, Location AS Community FROM Locations WHERE Community = 1
UNION SELECT
-1, 'ALL'
My problem is that when I select ALL, no records are returned. The report
runs but just returns the Page Header items. If I select any other
location, the report runs fine.
It seems like I'm missing something. Any advice would be appreciated.
ThanksWHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID =@.location or @.location = -1)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> Hello:
> RS Newbie here.
> I have created a report with a parameter that has an ALL value that should
> display all records when that particular parameter is selected.
> Here is the code for my dataset:
> SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> dbo.BuilderWO.Address,
> dbo.BuilderWO.City, dbo.BuilderWO.State,
> dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> dbo.BuilderWO.WalkDate,
> dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> dbo.Employees.LName, dbo.Status.Status_Status
> FROM dbo.Status RIGHT OUTER JOIN
> dbo.BuilderWO ON dbo.Status.Status_StatusID => dbo.BuilderWO.Status LEFT OUTER JOIN
> dbo.Locations ON dbo.BuilderWO.LocationID => dbo.Locations.LocationID LEFT OUTER JOIN
> dbo.Employees ON dbo.BuilderWO.TSHRep1 => dbo.Employees.EmployeeID
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location)
>
> Here is the code for the parameter:
> SELECT LocationID, Location AS Community FROM Locations WHERE Community => 1
> UNION SELECT
> -1, 'ALL'
> My problem is that when I select ALL, no records are returned. The report
> runs but just returns the Page Header items. If I select any other
> location, the report runs fine.
> It seems like I'm missing something. Any advice would be appreciated.
> Thanks
>
>|||Great!
Thanks that worked.
"Bruce L-C [MVP]" wrote:
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location or @.location = -1)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brennan" <Brennan@.discussions.microsoft.com> wrote in message
> news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> > Hello:
> >
> > RS Newbie here.
> >
> > I have created a report with a parameter that has an ALL value that should
> > display all records when that particular parameter is selected.
> >
> > Here is the code for my dataset:
> >
> > SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> > dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> > dbo.BuilderWO.Address,
> > dbo.BuilderWO.City, dbo.BuilderWO.State,
> > dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> > dbo.BuilderWO.WalkDate,
> > dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> > dbo.Employees.LName, dbo.Status.Status_Status
> > FROM dbo.Status RIGHT OUTER JOIN
> > dbo.BuilderWO ON dbo.Status.Status_StatusID => > dbo.BuilderWO.Status LEFT OUTER JOIN
> > dbo.Locations ON dbo.BuilderWO.LocationID => > dbo.Locations.LocationID LEFT OUTER JOIN
> > dbo.Employees ON dbo.BuilderWO.TSHRep1 => > dbo.Employees.EmployeeID
> > WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => > @.location)
> >
> >
> > Here is the code for the parameter:
> >
> > SELECT LocationID, Location AS Community FROM Locations WHERE Community => > 1
> > UNION SELECT
> > -1, 'ALL'
> >
> > My problem is that when I select ALL, no records are returned. The report
> > runs but just returns the Page Header items. If I select any other
> > location, the report runs fine.
> >
> > It seems like I'm missing something. Any advice would be appreciated.
> >
> > Thanks
> >
> >
> >
> >
>
>
RS Newbie here.
I have created a report with a parameter that has an ALL value that should
display all records when that particular parameter is selected.
Here is the code for my dataset:
SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
dbo.BuilderWO.Address,
dbo.BuilderWO.City, dbo.BuilderWO.State,
dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
dbo.BuilderWO.WalkDate,
dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
dbo.Employees.LName, dbo.Status.Status_Status
FROM dbo.Status RIGHT OUTER JOIN
dbo.BuilderWO ON dbo.Status.Status_StatusID = dbo.BuilderWO.Status LEFT OUTER JOIN
dbo.Locations ON dbo.BuilderWO.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Employees ON dbo.BuilderWO.TSHRep1 = dbo.Employees.EmployeeID
WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID = @.location)
Here is the code for the parameter:
SELECT LocationID, Location AS Community FROM Locations WHERE Community = 1
UNION SELECT
-1, 'ALL'
My problem is that when I select ALL, no records are returned. The report
runs but just returns the Page Header items. If I select any other
location, the report runs fine.
It seems like I'm missing something. Any advice would be appreciated.
ThanksWHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID =@.location or @.location = -1)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> Hello:
> RS Newbie here.
> I have created a report with a parameter that has an ALL value that should
> display all records when that particular parameter is selected.
> Here is the code for my dataset:
> SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> dbo.BuilderWO.Address,
> dbo.BuilderWO.City, dbo.BuilderWO.State,
> dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> dbo.BuilderWO.WalkDate,
> dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> dbo.Employees.LName, dbo.Status.Status_Status
> FROM dbo.Status RIGHT OUTER JOIN
> dbo.BuilderWO ON dbo.Status.Status_StatusID => dbo.BuilderWO.Status LEFT OUTER JOIN
> dbo.Locations ON dbo.BuilderWO.LocationID => dbo.Locations.LocationID LEFT OUTER JOIN
> dbo.Employees ON dbo.BuilderWO.TSHRep1 => dbo.Employees.EmployeeID
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location)
>
> Here is the code for the parameter:
> SELECT LocationID, Location AS Community FROM Locations WHERE Community => 1
> UNION SELECT
> -1, 'ALL'
> My problem is that when I select ALL, no records are returned. The report
> runs but just returns the Page Header items. If I select any other
> location, the report runs fine.
> It seems like I'm missing something. Any advice would be appreciated.
> Thanks
>
>|||Great!
Thanks that worked.
"Bruce L-C [MVP]" wrote:
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location or @.location = -1)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brennan" <Brennan@.discussions.microsoft.com> wrote in message
> news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> > Hello:
> >
> > RS Newbie here.
> >
> > I have created a report with a parameter that has an ALL value that should
> > display all records when that particular parameter is selected.
> >
> > Here is the code for my dataset:
> >
> > SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> > dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> > dbo.BuilderWO.Address,
> > dbo.BuilderWO.City, dbo.BuilderWO.State,
> > dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> > dbo.BuilderWO.WalkDate,
> > dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> > dbo.Employees.LName, dbo.Status.Status_Status
> > FROM dbo.Status RIGHT OUTER JOIN
> > dbo.BuilderWO ON dbo.Status.Status_StatusID => > dbo.BuilderWO.Status LEFT OUTER JOIN
> > dbo.Locations ON dbo.BuilderWO.LocationID => > dbo.Locations.LocationID LEFT OUTER JOIN
> > dbo.Employees ON dbo.BuilderWO.TSHRep1 => > dbo.Employees.EmployeeID
> > WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => > @.location)
> >
> >
> > Here is the code for the parameter:
> >
> > SELECT LocationID, Location AS Community FROM Locations WHERE Community => > 1
> > UNION SELECT
> > -1, 'ALL'
> >
> > My problem is that when I select ALL, no records are returned. The report
> > runs but just returns the Page Header items. If I select any other
> > location, the report runs fine.
> >
> > It seems like I'm missing something. Any advice would be appreciated.
> >
> > Thanks
> >
> >
> >
> >
>
>
Wednesday, March 28, 2012
Parameter not working
I am pretty new to reporting services and hope some of you experts can help.
I created a report from AS cube that has a column "Description" with 30 distinct values. The report has a multi-value parameter "Description" with the following details:
Data Type : String
Value Field: Description
Label Field : Description
The filter that i have on the table is =(Fields!Description.Value) = Cstr(Parameters!Description.Value(0))
When i preview the report i only see the top 1 item from the values that i select from the parameter list.
What am i doing wrong?
Any help is appreciated.
Thanks
Rookie,
Try
=(Fields!Description.Value) in Join(Parameters!Description.Value,", ")
because you have a multi field value this would be a better approach.
Ham
|||
Thanks for replying.
I just tried that. Now when i select more than one item in the parameter list, nothing displays. It works only if i select one value from the parameter list.
Any ideas?
|||Rookie,
I was able to return results with setting my filters to
Expression =Cstr(Fields!Income1.Value)
Operator = In
Value = Join(Parameters!Report_Parameter_0.Value,"', '")
My parameter values were 1,2, 3, 4,
my Income was 1
when I selected 1 parameter, I recieve the expected results.
Ham
|||
Yeah it works for me only when i select one value, if i select two values in the parameter, nothing displays.
Have no clue whats happening.
|||Rookie,
I played around with this for a bit, I was able to return multiple value with this expression in my filter.
Split(Join(Parameters!Inc.Value,","),",")
Ham
|||Rookie,
Try this is in your filter expression Split(Join(Parameters!Inc.Value,","),",")
Ham
|||
Thanks a lot hammer2. This made it work.
Thanks
Parameter missing value after deployment
Hi Friends,
I'm using MSRS 2005. I have created a drillthrough report using parameter. If I try to execute locally it works fine. I am able to call sub report from the master report by passing parameter. But, If I rey to access the report after deploying on to reporting server, I am getting error patameter xxxx is missing a value. What could be wrong? Please help
Thanks & Regards,
Naveen J V
This may be something to do with deployment. I've sometimes found that after deploying my report parameters don't behave as expected. The solution in the past has either to try to re-deploy (sometimes several times) or delete the reports from the report server and deploy again. A warning would be that the second approach the reportID's (GUIDs) wi8ll be re-created so usage stats will get screwed up.
To test whether it's a deployment issue, I suggest deploying to a different target directory to see if the reports work from there.
|||Hi Adams
Thanks for the quick turnaround. I have tried deploying it into different servers many times. Everywhere, it throws the same error.
Thanks & Regards,
Naveen J V
parameter is missing a value.
Hi Friends,
I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.
From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.
How to overcome this?
Thanks & Regards,
Naveen J V
Have you set any default value for the parameter in your subreport?
|||Hi,
No default value has been set.
Regards,
Naveen J V
|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||Hi,
I already tried this option, but I'm still getting the same error.
Thanks & Regards,
Naveen
|||All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.
By the way what is the expression that you are passing? And how many parameters you have?
|||
Hi Rohit,
My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.
It is working for one of my report which uses a different cube.
Thanks & Regards,
Naveen
|||Can you give the expression that you are passing?
|||Rohit,
While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.
Thanks & Regards,
Naveen
|||what is the expression you are writting in the navigation properties for the parameter of your sub report?
|||Hi,
Jump to Report ->Sub_rpt (sub report name)
Parameter name -> DLVLTM
Parameter value -> =Fields!TM.Uniquename
Regards,
Naveen J V
|||Actually everything seems to be fine. It may be a data issue though.
anyways again give it a try:
Go to Sub report and in the report parameter window try by making available values non-queried.
|||Hi Rohit,
If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.
I dont understand wht could be the problem?
Regards,
Naveen
|||Hi Rohit,
I'm getting the same error. Please refer to my rprevious mail.
Regards,
Naveen J V
parameter is missing a value.
Hi Friends,
I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.
From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.
How to overcome this?
Thanks & Regards,
Naveen J V
Have you set any default value for the parameter in your subreport?
|||Hi,
No default value has been set.
Regards,
Naveen J V
|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||Hi,
I already tried this option, but I'm still getting the same error.
Thanks & Regards,
Naveen
|||All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.
By the way what is the expression that you are passing? And how many parameters you have?
|||
Hi Rohit,
My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.
It is working for one of my report which uses a different cube.
Thanks & Regards,
Naveen
|||Can you give the expression that you are passing?
|||Rohit,
While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.
Thanks & Regards,
Naveen
|||what is the expression you are writting in the navigation properties for the parameter of your sub report?
|||Hi,
Jump to Report ->Sub_rpt (sub report name)
Parameter name -> DLVLTM
Parameter value -> =Fields!TM.Uniquename
Regards,
Naveen J V
|||Actually everything seems to be fine. It may be a data issue though.
anyways again give it a try:
Go to Sub report and in the report parameter window try by making available values non-queried.
|||Hi Rohit,
If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.
I dont understand wht could be the problem?
Regards,
Naveen
|||Hi Rohit,
I'm getting the same error. Please refer to my rprevious mail.
Regards,
Naveen J V
Monday, March 26, 2012
parameter is missing a value.
Hi Friends,
I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.
From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.
How to overcome this?
Thanks & Regards,
Naveen J V
Have you set any default value for the parameter in your subreport?
|||Hi,
No default value has been set.
Regards,
Naveen J V
|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||Hi,
I already tried this option, but I'm still getting the same error.
Thanks & Regards,
Naveen
|||All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.
By the way what is the expression that you are passing? And how many parameters you have?
|||
Hi Rohit,
My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.
It is working for one of my report which uses a different cube.
Thanks & Regards,
Naveen
|||Can you give the expression that you are passing?
|||Rohit,
While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.
Thanks & Regards,
Naveen
|||what is the expression you are writting in the navigation properties for the parameter of your sub report?
|||Hi,
Jump to Report ->Sub_rpt (sub report name)
Parameter name -> DLVLTM
Parameter value -> =Fields!TM.Uniquename
Regards,
Naveen J V
|||Actually everything seems to be fine. It may be a data issue though.
anyways again give it a try:
Go to Sub report and in the report parameter window try by making available values non-queried.
|||Hi Rohit,
If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.
I dont understand wht could be the problem?
Regards,
Naveen
|||Hi Rohit,
I'm getting the same error. Please refer to my rprevious mail.
Regards,
Naveen J V
parameter entry / view report
I'm fairly new to RS, but have created some reports and deployed them.
I have parameter value list that is generate from a SELECT statement.
To be a bit more user-friendly, I'd like the user to be able to click on an
entry in the parameter list and have the sytem run the report without having
to click the View Report .
Is there a way to avoid having the user click the View Report ? It would
save a step and is something that can become annoying as the user moves from
one report to another based on the parameter chosen.
TIA,
DougIf you use the enter key the View Report button is the default. I do this
instead of clicking on it sometime.
RS does not know when you are on the last parameter, you need to either
click on view report or use the enter key.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:5F6FC4F0-F033-4B3D-9565-DE61F0FD0E34@.microsoft.com...
> I'm fairly new to RS, but have created some reports and deployed them.
> I have parameter value list that is generate from a SELECT statement.
> To be a bit more user-friendly, I'd like the user to be able to click on
> an
> entry in the parameter list and have the sytem run the report without
> having
> to click the View Report .
> Is there a way to avoid having the user click the View Report ? It would
> save a step and is something that can become annoying as the user moves
> from
> one report to another based on the parameter chosen.
> TIA,
> Doug
I have parameter value list that is generate from a SELECT statement.
To be a bit more user-friendly, I'd like the user to be able to click on an
entry in the parameter list and have the sytem run the report without having
to click the View Report .
Is there a way to avoid having the user click the View Report ? It would
save a step and is something that can become annoying as the user moves from
one report to another based on the parameter chosen.
TIA,
DougIf you use the enter key the View Report button is the default. I do this
instead of clicking on it sometime.
RS does not know when you are on the last parameter, you need to either
click on view report or use the enter key.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:5F6FC4F0-F033-4B3D-9565-DE61F0FD0E34@.microsoft.com...
> I'm fairly new to RS, but have created some reports and deployed them.
> I have parameter value list that is generate from a SELECT statement.
> To be a bit more user-friendly, I'd like the user to be able to click on
> an
> entry in the parameter list and have the sytem run the report without
> having
> to click the View Report .
> Is there a way to avoid having the user click the View Report ? It would
> save a step and is something that can become annoying as the user moves
> from
> one report to another based on the parameter chosen.
> TIA,
> Doug
Parameter Default value
Since the Select all feature was removed from drop downs I have created my
own value to represent that and union it to the dataset for one of my
parameters. I'm trying to make that the default value, but it won't let me
reference the top of the dataset because the parameter uses the same
dataset.
Hmm... that sound confusing, I'll try again but leave that in case someone
understands it.
Basically I have a parameter of "Owners" which is just a string collection
of names. This box is populated by a dataset named the same. I fill the
Value and the Label of the parameter with Owners.ownername. That dataset is
just a simple table field but I union the string "(SELECT ALL)" to the top
of it so that it's the first record returned and appears at the top of the
parameter drop down selection.
I then try to set the default value of the Parameter. If I use a
non-queried value I can use an expression and selection the first of the
field. The problem is that non-queried can not use fields. If I try to
use a queried value it selects everything.
Is there a way to just fill that first record?> The problem is that non-queried can not use fields. If I try to
> use a queried value it selects everything.
> Is there a way to just fill that first record?
Why is it a problem that you can not use fields. If you use the non-
queried default value "(SELECT ALL)" then this is what should be
selected by default.
Rowen|||Ofcourse you can use it in the expression of non-queried with hard coded
"select All" so whenever the first record or the parameter is "select All"
then it should be "Select All" string.
Amarnath
"Ryan" wrote:
> Since the Select all feature was removed from drop downs I have created my
> own value to represent that and union it to the dataset for one of my
> parameters. I'm trying to make that the default value, but it won't let me
> reference the top of the dataset because the parameter uses the same
> dataset.
> Hmm... that sound confusing, I'll try again but leave that in case someone
> understands it.
> Basically I have a parameter of "Owners" which is just a string collection
> of names. This box is populated by a dataset named the same. I fill the
> Value and the Label of the parameter with Owners.ownername. That dataset is
> just a simple table field but I union the string "(SELECT ALL)" to the top
> of it so that it's the first record returned and appears at the top of the
> parameter drop down selection.
> I then try to set the default value of the Parameter. If I use a
> non-queried value I can use an expression and selection the first of the
> field. The problem is that non-queried can not use fields. If I try to
> use a queried value it selects everything.
> Is there a way to just fill that first record?
>
>
own value to represent that and union it to the dataset for one of my
parameters. I'm trying to make that the default value, but it won't let me
reference the top of the dataset because the parameter uses the same
dataset.
Hmm... that sound confusing, I'll try again but leave that in case someone
understands it.
Basically I have a parameter of "Owners" which is just a string collection
of names. This box is populated by a dataset named the same. I fill the
Value and the Label of the parameter with Owners.ownername. That dataset is
just a simple table field but I union the string "(SELECT ALL)" to the top
of it so that it's the first record returned and appears at the top of the
parameter drop down selection.
I then try to set the default value of the Parameter. If I use a
non-queried value I can use an expression and selection the first of the
field. The problem is that non-queried can not use fields. If I try to
use a queried value it selects everything.
Is there a way to just fill that first record?> The problem is that non-queried can not use fields. If I try to
> use a queried value it selects everything.
> Is there a way to just fill that first record?
Why is it a problem that you can not use fields. If you use the non-
queried default value "(SELECT ALL)" then this is what should be
selected by default.
Rowen|||Ofcourse you can use it in the expression of non-queried with hard coded
"select All" so whenever the first record or the parameter is "select All"
then it should be "Select All" string.
Amarnath
"Ryan" wrote:
> Since the Select all feature was removed from drop downs I have created my
> own value to represent that and union it to the dataset for one of my
> parameters. I'm trying to make that the default value, but it won't let me
> reference the top of the dataset because the parameter uses the same
> dataset.
> Hmm... that sound confusing, I'll try again but leave that in case someone
> understands it.
> Basically I have a parameter of "Owners" which is just a string collection
> of names. This box is populated by a dataset named the same. I fill the
> Value and the Label of the parameter with Owners.ownername. That dataset is
> just a simple table field but I union the string "(SELECT ALL)" to the top
> of it so that it's the first record returned and appears at the top of the
> parameter drop down selection.
> I then try to set the default value of the Parameter. If I use a
> non-queried value I can use an expression and selection the first of the
> field. The problem is that non-queried can not use fields. If I try to
> use a queried value it selects everything.
> Is there a way to just fill that first record?
>
>
Parameter default not updating on deploy
I've got an issue with a report of mine where the default parameter isn't being cleared. Let me explain...
I created a report with a parameter on it. While I'm working on the cosmetics of the report, I didn't want to have to keep typing in a value just to see it run, so I set the default value on the parameter. I finish the report and deploy it and everything is ok. Then when I go to the page to view it, I notice that the default is still there, so I go back and edit my report to take out the default and just use NULL. In the preview tab, it looks ok, so I deploy the report again. Now when I go to the report, it still has the default value in the parameter box.
Does anyone have any idea as to why this is happening? The only way I could get around it is to drop the parameter from the report, deploy it, then re-add the parameter and deploy it again.
Thanks in advance,
Jarret
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=960767&SiteID=1parameter cannot set the command text for dataset
Hello
I get the following error in a very quick test system i created:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot set the command text for data set 'test'.
Error during processing of the CommandText expression of dataset 'test'.
My sql is:
= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)
There is nothing obviously wrong in the code as far as i can see
The parameter 'test1' is of type 'string'
The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'
I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine
Thanks
t.test from test?
You tried running this in sql?
|||Yes, it works fine in sql if you strip out the bits that obviously wont work in query analyser
this works:
= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = 1" )
this does not:
= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = " & Parameters!test1.Value)
why?
there is a parameter called test1
|||Found the answer!
You have to have another table with a different name into which the parameter links (in the section 'available values' / 'from query')
I created another table called test2, et voila! it works
Friday, March 23, 2012
parameter
Hi,
I have created several parameters in my report.
These parameters are drop down controls which are populated from stored
procedures.
How is it possible to populate one of these parameters based on the
selection on another parameter drop down control?
ThanksOn May 29, 9:03 am, farshad <fars...@.discussions.microsoft.com> wrote:
> Hi,
> I have created several parameters in my report.
> These parameters are drop down controls which are populated from stored
> procedures.
> How is it possible to populate one of these parameters based on the
> selection on another parameter drop down control?
> Thanks
Yes it is. I'll give you a quick example and you can base your report
of it hopefully:
DataSet1 {This is your main dataset}
Select *
from cars_inventory
where make=@.make and model=@.model {here are you two parameter filters}
DataSet2
select distinct make
from cars_inventory
DataSet3
select distinct model
from cars_inventory
where make=@.make
Make sure in your main dataset that @.make comes first in the where
clause otherwise you will run into problems. Under the menu
Report>Report Parameters go to configure your parameters. Make both
of them queried (@.make from dataset2 and @.model from dataset3). That
should work. When you preview, model will be grayed out until you
select a make, then it will only pull back makes based on that model.
So for example you select Toyota for make, then under the model drop
down you will get a list {Camry, Corolla, etc}. If you select Nissan
you'll get a different list {Altima, Maxima, etc}.
I hope that helps.
I have created several parameters in my report.
These parameters are drop down controls which are populated from stored
procedures.
How is it possible to populate one of these parameters based on the
selection on another parameter drop down control?
ThanksOn May 29, 9:03 am, farshad <fars...@.discussions.microsoft.com> wrote:
> Hi,
> I have created several parameters in my report.
> These parameters are drop down controls which are populated from stored
> procedures.
> How is it possible to populate one of these parameters based on the
> selection on another parameter drop down control?
> Thanks
Yes it is. I'll give you a quick example and you can base your report
of it hopefully:
DataSet1 {This is your main dataset}
Select *
from cars_inventory
where make=@.make and model=@.model {here are you two parameter filters}
DataSet2
select distinct make
from cars_inventory
DataSet3
select distinct model
from cars_inventory
where make=@.make
Make sure in your main dataset that @.make comes first in the where
clause otherwise you will run into problems. Under the menu
Report>Report Parameters go to configure your parameters. Make both
of them queried (@.make from dataset2 and @.model from dataset3). That
should work. When you preview, model will be grayed out until you
select a make, then it will only pull back makes based on that model.
So for example you select Toyota for make, then under the model drop
down you will get a list {Camry, Corolla, etc}. If you select Nissan
you'll get a different list {Altima, Maxima, etc}.
I hope that helps.
parameter
Hi,
I have created several parameters in my report.
These parameters are drop down controls which are populated from stored procedures.
How is it possible to populate one of these parameters based on the selection on another parameter drop down control?
Thanks
I have created several parameters in my report.
These parameters are drop down controls which are populated from stored procedures.
How is it possible to populate one of these parameters based on the selection on another parameter drop down control?
Thanks
keep your parameters in the order they are dependent. refer the parameter some thing like
select src_parameter2 where src_param1=@.param1
Thanks
Raj Deep.A
sqlparam prob
hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.
WITH
MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'
SET [FilteredBUList] AS strtoset(@.BU)
SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows
FROM Profitability
my second dataset query is like following,
SET [FilteredBUList] AS strtoset(@.BU)
SET [customtimeset] AS.....
SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows
FROM Profitability
i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH
MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'
SET [FilteredBUList] AS strtotuple(@.BU)
SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows
FROM Profitability
Wednesday, March 21, 2012
ParallelPeriod MDX function not working
I created a calculated measure called [Previous Year Percent Rejected]. I used the following expression:
(ParallelPeriod([Year],1,[Time].[Time Hierarchy].CurrentMember),[Measures].[Percent Rejected])
The syntax checks out fine. However, when I try to use the calculation, I get an error message -- with a #VALUE! in the column.
The error message reads " The parallel period expression expects a level expression for the argument. A hierarchy expression was used."
I tried many different variations but I'm always receiving the same message. Any suggestions?
David
If you're using AS 2005, the problem could be that there is also an attribute hierarchy associated with [Year]. So, you could try fully specifying the [Year] level, like:
(ParallelPeriod([Time].[Time Hierarchy].[Year],1,[Time].[Time Hierarchy].CurrentMember),[Measures].[Percent Rejected])
|||Thank you Deepak. Your solution fixed the problem.
David
Tuesday, March 20, 2012
Pakage running in Sql server 2005
hi friends,
i created the one package for import the data from excel to server 2005
in intellegence services i add the package after deploy
if we run the package (by giving the connection working fine)
BUT I WANT TO RUN THE PACKAGE BY DAILY AUTOMATICALLY(just like a scheduler)
is it possible if possible please give me the coding for auto run a packages
regards
koti
Hi ya,
You would need to deploy it first to file system or Ms Sql Server and then place it as a job in sql server agent. Schedule that job to run daily.
Hope that helps
Cheers
Rizwan
Paging, Soring, Filtering Already-written SPs Result
Hi guys,
we developed a large-scale web-application which uses sqlserver 2005,
and we created all of SPs for geting list of particular records or
geting a record info.
i.e Products_GetList | Products_GetInfo & etc.
now we need to implement paging,sorting or filtering on these SPs
results...
& we couldn't do this at webapplication level because of huge number of
records... also it's not possible to change all of these SPs(we have
more than 300 SPs already)
in other words i'm searching for a way to create a generic method(SP or
UDF) which do the paging & other operations on result of all SPs if
required.
i.e :
MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
[Generic SP (do sorting)] --> Specific SP
ThanksHi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Khafancoder" <khafancoder@.gmail.com> wrote in message
news:1168506782.577051.145700@.k58g2000hse.googlegroups.com...
> Hi guys,
> we developed a large-scale web-application which uses sqlserver 2005,
> and we created all of SPs for geting list of particular records or
> geting a record info.
> i.e Products_GetList | Products_GetInfo & etc.
> now we need to implement paging,sorting or filtering on these SPs
> results...
> & we couldn't do this at webapplication level because of huge number of
> records... also it's not possible to change all of these SPs(we have
> more than 300 SPs already)
> in other words i'm searching for a way to create a generic method(SP or
> UDF) which do the paging & other operations on result of all SPs if
> required.
> i.e :
> MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
> [Generic SP (do sorting)] --> Specific SP
>
> Thanks
>|||IMO the solution is to redesign, rather than attempt a workaround. You might
think you could use wrapper procedure calls for each stored proc which
caches the results in a temp table and sorts them but the syntax for this is
"insert into #yourtable exec yourproc", so the temp table definition must
already exist and each wrapper proc will therefore be different. Whichever
way you look at it, this will require widespread changes. The sorted column
will need to be provided, and as you are using paging, the page size and
page number will be provided. So, new parameters will get added to each
stored proc call from the application and each stored proc needs editing.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks,
yes, as u said this wrapper method (i.e for sorting) should get
SortColumnName, Asc|Desc and SP name...
if we ignore the temp-table structure problem, other problems will
solved, for example if all of these SPs return a resultset in a
specific format then the wrapper method could insert them in a generic
temp-table and sort them and finally return them.
in this approach i can call "insert into #generictemptable exec SPName"
dynamically by using execute cmd, but i think it will affect on
application performance...
how about CLR Integration ? could we use it to write the wrapper ?
Paul Ibison wrote:
> IMO the solution is to redesign, rather than attempt a workaround. You might
> think you could use wrapper procedure calls for each stored proc which
> caches the results in a temp table and sorts them but the syntax for this is
> "insert into #yourtable exec yourproc", so the temp table definition must
> already exist and each wrapper proc will therefore be different. Whichever
> way you look at it, this will require widespread changes. The sorted column
> will need to be provided, and as you are using paging, the page size and
> page number will be provided. So, new parameters will get added to each
> stored proc call from the application and each stored proc needs editing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||My understanding of CLR integration is that it is great for iterative tasks
and pattern matching but this is pretty standard SQL so I don't see any
benefit there. In the code-behind you could use some paging code applied to
the recordset as mentioned in URI's link, but this performs badly compared
to a where clause run on the server. Essentially I'd still personally go
down the rewrite route unless your tables are incredibly generic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
we developed a large-scale web-application which uses sqlserver 2005,
and we created all of SPs for geting list of particular records or
geting a record info.
i.e Products_GetList | Products_GetInfo & etc.
now we need to implement paging,sorting or filtering on these SPs
results...
& we couldn't do this at webapplication level because of huge number of
records... also it's not possible to change all of these SPs(we have
more than 300 SPs already)
in other words i'm searching for a way to create a generic method(SP or
UDF) which do the paging & other operations on result of all SPs if
required.
i.e :
MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
[Generic SP (do sorting)] --> Specific SP
ThanksHi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Khafancoder" <khafancoder@.gmail.com> wrote in message
news:1168506782.577051.145700@.k58g2000hse.googlegroups.com...
> Hi guys,
> we developed a large-scale web-application which uses sqlserver 2005,
> and we created all of SPs for geting list of particular records or
> geting a record info.
> i.e Products_GetList | Products_GetInfo & etc.
> now we need to implement paging,sorting or filtering on these SPs
> results...
> & we couldn't do this at webapplication level because of huge number of
> records... also it's not possible to change all of these SPs(we have
> more than 300 SPs already)
> in other words i'm searching for a way to create a generic method(SP or
> UDF) which do the paging & other operations on result of all SPs if
> required.
> i.e :
> MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
> [Generic SP (do sorting)] --> Specific SP
>
> Thanks
>|||IMO the solution is to redesign, rather than attempt a workaround. You might
think you could use wrapper procedure calls for each stored proc which
caches the results in a temp table and sorts them but the syntax for this is
"insert into #yourtable exec yourproc", so the temp table definition must
already exist and each wrapper proc will therefore be different. Whichever
way you look at it, this will require widespread changes. The sorted column
will need to be provided, and as you are using paging, the page size and
page number will be provided. So, new parameters will get added to each
stored proc call from the application and each stored proc needs editing.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks,
yes, as u said this wrapper method (i.e for sorting) should get
SortColumnName, Asc|Desc and SP name...
if we ignore the temp-table structure problem, other problems will
solved, for example if all of these SPs return a resultset in a
specific format then the wrapper method could insert them in a generic
temp-table and sort them and finally return them.
in this approach i can call "insert into #generictemptable exec SPName"
dynamically by using execute cmd, but i think it will affect on
application performance...
how about CLR Integration ? could we use it to write the wrapper ?
Paul Ibison wrote:
> IMO the solution is to redesign, rather than attempt a workaround. You might
> think you could use wrapper procedure calls for each stored proc which
> caches the results in a temp table and sorts them but the syntax for this is
> "insert into #yourtable exec yourproc", so the temp table definition must
> already exist and each wrapper proc will therefore be different. Whichever
> way you look at it, this will require widespread changes. The sorted column
> will need to be provided, and as you are using paging, the page size and
> page number will be provided. So, new parameters will get added to each
> stored proc call from the application and each stored proc needs editing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||My understanding of CLR integration is that it is great for iterative tasks
and pattern matching but this is pretty standard SQL so I don't see any
benefit there. In the code-behind you could use some paging code applied to
the recordset as mentioned in URI's link, but this performs badly compared
to a where clause run on the server. Essentially I'd still personally go
down the rewrite route unless your tables are incredibly generic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Monday, March 12, 2012
Paging Reports
Hi All
I created a report with a matrix on the form and managed to get the results "not" to page when viewed on the web by selecting the "Fit matrix to one page if possible" checkbox.
I created a new report looking at the same data this time using a table and no matter what i do the report keeps paging!!!
Anyone got any advice on how to stop this? Why does it handle a table differently than a table.
Thanks very much
KeepTogether is not currently supported in interactive renderers (HTML and preview). You can try changing the InteractiveHeight property on the report to 0 in order to get the entire report on one page.|||Setting the Interactive Height to 0in did the trick
aka
<InteractiveHeight>0in</InteractiveHeight>
Cheers
Paging query
I have created a stored proc for paging on a datalist which uses a objectDataSource.
I have a output param itemCount which should return the total rows. Them I am creating a temp table to fetch the records for each page request. My output param works fine if I comment out all the other select statements. But returns null with them. Any help would be appreciated.
CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]
(
@.categoryID int,
@.pageIndex INT,
@.numRows INT,
@.itemCount INT OUTPUT
)
AS
SELECT @.itemCount= COUNT(*) FROM CMRC_Products whereCMRC_Products.CategoryID=@.categoryID
Declare @.startRowIndex INT;
Declare @.finishRowIndex INT;
set @.startRowIndex = ((@.pageIndex -1) * @.numRows) + 1;
set @.finishRowIndex = @.pageIndex * @.numRows
DECLARE @.tCat TABLE (TID int identity(1,1),ProductID int, CategoryID int, SellerUserName varchar(100), ModelName varchar(100), Medium varchar(50),
ProductImage varchar(100),UnitCost money,Description varchar(1500), CategoryName varchar(100), isActive bit,weight money)
INSERT INTO @.tCat(ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight)
SELECT CMRC_Products.ProductID, CMRC_Products.CategoryID, CMRC_Products.SellerUserName, CMRC_Products.ModelName, CMRC_Products.Medium,CMRC_Products.ProductImage,
CMRC_Products.UnitCost, CMRC_Products.Description, CMRC_Categories.CategoryName, CMRC_Products.isActive,CMRC_Products.weight
FROM CMRC_Products INNER JOIN
CMRC_Categories ON CMRC_Products.CategoryID = CMRC_Categories.CategoryID
WHERE (CMRC_Products.CategoryID = @.categoryID) AND (CMRC_Products.isActive = 1)
SELECT ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight
FROM @.tCat
WHERE TID >= @.startRowIndex AND TID <= @.finishRowIndex
GO
spawned:
My output param works fine if I comment out all the other select statements. But returns null with them.
Strange! Other select statements should not effect the output param as non of them refereneces the output param. How did you get the output param's value after executing the sp? Will the output param work fine if you directly execute the stored procedure in SQL via Query Analyzer (or Management Studio) without commenting out some statements?
|||You are correct, I tested in Query Analyser and the Output was returned OK.
The issue was that the method was returning a DataReader along with a output param. The two don't get along! You have to close the DataReader before the output param is visible. This is 'by design' accourding to MS.http://support.microsoft.com/?id=308051 (See resolution section). Moving to the end of the recordset didn't seem to work but close it does.
This was a real pain as I had to change the method to Read the data into a list<Product> then close the Reader, then set reference to my output param.
Anyway all done now and works a treat. Thanks for you advice.
|||Then how about returning the count as a result set instead of putting it in the output parameter? I mean you can write your stored procedure this way:
CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]
(
@.categoryID int,
@.pageIndex INT,
@.numRows INT,
)
AS
SELECT COUNT(*) FROM CMRC_Products whereCMRC_Products.CategoryID=@.categoryID
//do other things
go
And then in the code you can get the count from the first result set using SqlDataReader.
Ok, I thought only DataSets alowed that (ie Tables[0], Table[1] etc, or does the Reader just return it at the end of the recordset.
I'll have a play around with it. Thanks for the tip.
Cheers,Shaun.
|||
I am having exa ctly the same problem.
You mentioned that you used a method to Read the data into a list<Product> then close the Reader, then set reference to my output param. This is exactly what i am trying to do now could you tell me the code for this.
many thanks
martin
|||In my Product.cs I have the following new method:
//Used for paged results in catagory searchpublicvoid ProductList(int ProductID,string Medium,string ModelName,string ProductImage,double UnitCost,bool IsAdult){
_ProductID = ProductID;
_Medium = Medium;
_ModelName = ModelName;
_ProductImage = ProductImage;
_UnitCost = UnitCost;
_IsAdult = IsAdult;
}
***************************
In my CatalogManager .cs (I didn't put it in the provider project as it is a pain in the ar*e to maintain) I have the following:
public
staticList <Product> GetProductsByCategoryPaging(int categoryID,int pageIndex,int numRows,outint itemCount){
using (SqlConnection connection =newSqlConnection(ConfigurationManager.ConnectionStrings["CommerceTemplate"].ConnectionString)){
using (SqlCommand command =newSqlCommand("CMRC_PRODUCTS_GetListByCategoryPaging", connection)){
command.CommandType =
CommandType.StoredProcedure;command.Parameters.Add(
"@.itemCount",SqlDbType.Int, 4);command.Parameters[
"@.itemCount"].Direction =ParameterDirection.Output;command.Parameters.Add(
newSqlParameter("@.categoryID", categoryID));command.Parameters.Add(
newSqlParameter("@.pageIndex", pageIndex));command.Parameters.Add(
newSqlParameter("@.numRows", numRows));connection.Open();
//populate listList<Product> list =newList<Product>();using (SqlDataReader rdr = command.ExecuteReader()) {while (rdr.Read()) {Product temp =newProduct();temp.ProductList(
(
int)rdr["ProductID"],rdr[
"Medium"].ToString(),rdr[
"ModelName"].ToString(),rdr[
"ProductImage"].ToString(),Convert.ToDouble (rdr["UnitCost"]),(
bool)rdr["IsAdult"]);list.Add(temp);
}
rdr.Close();
}
itemCount = (
int)command.Parameters["@.itemCount"].Value;return list;}
}
}
You'll need to include the this declaration in the CatalogManager:
using System.Collections.Generic;
Good luck I feel you pain :-)
|||
really, many thanks for that it was driving me crazy
cheers
martin
|||Once again many thanks for your help with this. Everhting is working fine.
Just one question did you use querystrings and if you did, how did you validate
them. I am having a lot of problems trying to figure out how to do this.
many thanks for all your help
martin
|||
Yes I did use query strings. My catalog page handles many queries.
I had a switch statement in the Page Load.
switch (SearchType) <== I have a param for query type.{
case"c"://category searchcid =
Convert.ToInt32(Request.QueryString["cid"]);dlCatalog.DataSource = objDSCategory;
dlCatalog.DataBind();
break;case"a"://artist searchdlCatalog.DataSource = objDSArtist;
dlCatalog.DataBind();
break;etc
}
in the obj DataSource in aspx:
<
asp:ObjectDataSourceID="objDSGallery"runat="server"OldValuesParameterFormatString="{0}"SelectMethod="GetProductsByGalleryPaging"TypeName="CatalogManager"OnSelected="objDSGallery_Selected"OnSelecting="objDSGallery_Selecting"><SelectParameters><asp:QueryStringParameterName="GalleryID"QueryStringField="gid"Type="Int32"/><asp:QueryStringParameterName="pageIndex"QueryStringField="PageIndex"DefaultValue="0"/><asp:QueryStringParameterName="numRows"QueryStringField="NumRows"DefaultValue="6"/><asp:ParameterName="itemCount"Direction="Output"Type="Int32"/></SelectParameters></asp:ObjectDataSource>Let me know if you have any more dramas, I can email you the code I'm using for reference.
The OnSelected and Selecting methods are used for updating the paging links.
I can email you the code if you like.
Cheers,Shaun.
|||thanks for your reply
i would be grateful for the code my address istbcmartingharvey@.yahoo.co.jp
many thanks
martin
|||
Is your Yahoo account still active? got a return reply saying:
554 delivery error: dd This user doesn't have a yahoo.co.jp account (tbcmartingharvey@.yahoo.co.jp)
Is that your correct mail address?
Cheers,Shaun
ps I'll be away for several days (biz not pleasure :-( ) so will not get the code to you till Tues. Probably best I provide it as a download from my server so other users can access it too.
|||thanks for your reply shaun
must be the summer heat
itstbcmartinharvey@.yahoo.co.jp
thank you
martin
Subscribe to:
Posts (Atom)