Report parameters

Hi everybody.
I have one dataset with parameter 'UserID' and second Dataset which fill
UserID parameter. When i run my report i can choose value from second
Dataset, but i wont that in my Parameter will be present value as 'All' which
mean all records from query. It is possible? if yes how? any sample?
P.S.
I know how add this value but i don't know how to write my query.
Thank you.Hello Dmitri,
Here is how to write the query to have 'All' included in your list of
users:
SELECT UserNo, UserName FROM Users
UNION
SELECT -1, 'All'
Take care,
Michelle|||Hi,
SELECT col1,col2,...
FROM table1
where col1 = CASE WHEN @.Param = 'All' THEN col1
ELSE @.Param
END
Eric|||oops, I forgot the second half - to check for the 'All' in your second
query:
SELECT something1, something2
FROM someTable
WHERE (User = @.User OR @.User = 'All')
or
SELECT something1, something2
FROM someTable
WHERE (UserNo = @.UserNo OR @.UserNo = -1)
Michelle|||Hi,
select col1,col2,col3
from table1
where col1 = case when @.param = 'all' then col1
else @.param
end
hth,
Eric|||Thank you all.
"Aiwa" wrote:
> Hi,
> select col1,col2,col3
> from table1
> where col1 = case when @.param = 'all' then col1
> else @.param
> end
> hth,
> Eric
>

Report not returning all the records

Hello EveryBody,
I am designing report using report designer...I have defined two dataset..
First Dataset is just returning all the Category(i.e select CategoryName
from Category)
and in the second dataset i am passing that CategoryName to get all the
products belongs to that category...
So basically CagegoryName is my dropdown list to select category... Now
whiile is designing the report in the report body i have drag and drop all
the fields of second dataset...with their lebels.. but when i preview the
report i am just able to see first record even though each category has
atleast 10 products...
my sample textbox value is as =First(Fields!ProductName.Value, "Product_Set")
Eventhough i removed First and tried still it is returning one record...
Pls help
thxWhat you want in this case is to have one report with another report
embedded as a subreport. You will want to read up on subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> Hello EveryBody,
> I am designing report using report designer...I have defined two dataset..
> First Dataset is just returning all the Category(i.e select CategoryName
> from Category)
> and in the second dataset i am passing that CategoryName to get all the
> products belongs to that category...
> So basically CagegoryName is my dropdown list to select category... Now
> whiile is designing the report in the report body i have drag and drop all
> the fields of second dataset...with their lebels.. but when i preview the
> report i am just able to see first record even though each category has
> atleast 10 products...
> my sample textbox value is as =First(Fields!ProductName.Value,
> "Product_Set")
> Eventhough i removed First and tried still it is returning one record...
> Pls help
> thx|||It is not a subreport.. It is just selecting a CategoryName from the dropdown
list on the top bar and passing that value to second dataset..
"Bruce L-C [MVP]" wrote:
> What you want in this case is to have one report with another report
> embedded as a subreport. You will want to read up on subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> > Hello EveryBody,
> > I am designing report using report designer...I have defined two dataset..
> > First Dataset is just returning all the Category(i.e select CategoryName
> > from Category)
> > and in the second dataset i am passing that CategoryName to get all the
> > products belongs to that category...
> >
> > So basically CagegoryName is my dropdown list to select category... Now
> > whiile is designing the report in the report body i have drag and drop all
> > the fields of second dataset...with their lebels.. but when i preview the
> > report i am just able to see first record even though each category has
> > atleast 10 products...
> >
> > my sample textbox value is as =First(Fields!ProductName.Value,
> > "Product_Set")
> >
> > Eventhough i removed First and tried still it is returning one record...
> >
> > Pls help
> >
> > thx
>
>|||It sounds to me like one dataset is just for the dropdown for the parameter
to list. Then that gets passed as a parameter to the second dataset that you
want to display. My guess is that you are dropping textboxes on the report
design surface. What you want to do is drop a table (it will default to 3
fields). Then drag and drop fields onto it. Add additional columns by doing
a right mouse click on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> It is not a subreport.. It is just selecting a CategoryName from the
> dropdown
> list on the top bar and passing that value to second dataset..
> "Bruce L-C [MVP]" wrote:
>> What you want in this case is to have one report with another report
>> embedded as a subreport. You will want to read up on subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "mvp" <mvp@.discussions.microsoft.com> wrote in message
>> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
>> > Hello EveryBody,
>> > I am designing report using report designer...I have defined two
>> > dataset..
>> > First Dataset is just returning all the Category(i.e select
>> > CategoryName
>> > from Category)
>> > and in the second dataset i am passing that CategoryName to get all the
>> > products belongs to that category...
>> >
>> > So basically CagegoryName is my dropdown list to select category... Now
>> > whiile is designing the report in the report body i have drag and drop
>> > all
>> > the fields of second dataset...with their lebels.. but when i preview
>> > the
>> > report i am just able to see first record even though each category has
>> > atleast 10 products...
>> >
>> > my sample textbox value is as =First(Fields!ProductName.Value,
>> > "Product_Set")
>> >
>> > Eventhough i removed First and tried still it is returning one
>> > record...
>> >
>> > Pls help
>> >
>> > thx
>>|||Yes that was the problem.. It is working now... Thanks
"Bruce L-C [MVP]" wrote:
> It sounds to me like one dataset is just for the dropdown for the parameter
> to list. Then that gets passed as a parameter to the second dataset that you
> want to display. My guess is that you are dropping textboxes on the report
> design surface. What you want to do is drop a table (it will default to 3
> fields). Then drag and drop fields onto it. Add additional columns by doing
> a right mouse click on the table.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> > It is not a subreport.. It is just selecting a CategoryName from the
> > dropdown
> > list on the top bar and passing that value to second dataset..
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What you want in this case is to have one report with another report
> >> embedded as a subreport. You will want to read up on subreports.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> >> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> >> > Hello EveryBody,
> >> > I am designing report using report designer...I have defined two
> >> > dataset..
> >> > First Dataset is just returning all the Category(i.e select
> >> > CategoryName
> >> > from Category)
> >> > and in the second dataset i am passing that CategoryName to get all the
> >> > products belongs to that category...
> >> >
> >> > So basically CagegoryName is my dropdown list to select category... Now
> >> > whiile is designing the report in the report body i have drag and drop
> >> > all
> >> > the fields of second dataset...with their lebels.. but when i preview
> >> > the
> >> > report i am just able to see first record even though each category has
> >> > atleast 10 products...
> >> >
> >> > my sample textbox value is as =First(Fields!ProductName.Value,
> >> > "Product_Set")
> >> >
> >> > Eventhough i removed First and tried still it is returning one
> >> > record...
> >> >
> >> > Pls help
> >> >
> >> > thx
> >>
> >>
> >>
>
>sql

Report Layout after deploy

Hi everybody,

I have problem to creating the report using report server. After report degining as report project, the report appeared proper in the layout . But it doesn't show with proper alignment, After deploy this report in the reporting server . How to rectify this.

Please anybody is in, share with your idea.

Good day

What do you mean by alignment ? Make sure that Reporting Services tries to guess (also in the designer) how you want to data to be displayed in the report, meaning that like in excel numeric values are aligned right and string values are aligned left by default.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Try viewing the report as a PDF - sometimes there are differences in the way it is displayed as a web page and PDF.

The PDF may be closer/the same as the way you designed it.

99

|||

It sounds like you have overlapping report items (rather than e.g. an item being contained within another item).

Overlapping items are only supported in PDF, image rendering, and preview, but not in HTML.

-- Robert

|||

Yes, You are right. The controls are overlapped in my report degin . After rectify this, the report display properly. After that, i m facing another problem for using the subreport. it hasn't display properly. If you have any solution, please provide to me

Thanks

Report Header is not displaying

Hi Everybody,

I have a strange problem. I'm using CR 9. My report works fine. The problem is it if there is no records found, then it is not displaying anything including Report Header, Report Footer.

Report Header section contains just a heading, sys date and parameter values.

Have any idea? pls help me?

ThanksGot it! Any way thanks!|||Hi harmonycitra,
How did you get the solution? Did you use suppress event in the Report header?

Madhivanan|||No, Actually I have checked the "Suppress Printing if No Records" in Report Options dialog. (accidentally, and I simply forgot)

After checking all the possibilities, I noticed that one. So I removed it. It's working now.