Friday, March 30, 2012

Report Parameters - Display Boolean Type As CheckBox

I have created a Report Parameter, and set the type of this to "Boolean".

This is displayed as a RadioButton with the options of True or False.

Is there anyway to change this to be displayed as a CheckBox?

Thanks,

Kate

See this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=562091&SiteID=1

Report Parameters - Decimal vs. Float

My dataset returns data from a range of effective dates for a single
month. In the database, these dates are unfortunately not of type
datetime, but are decimal and are in the YYYYMMDD format.
I'm having trouble getting the report to execute because at the query
level, the datatype has to be decimal. I need to parameterize these
dates, but there is no decimal datatype for parameters.
I have a dataset that generates the necessary dates based on the type
of report the user wants (60/90/120 day) (if they want a 60-day report,
the effective date = 2 months from the first day of the current month,
etc).
SELECT DISTINCT
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '01') +
@.period_additive AS effdte_low,
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '31') +
@.period_additive AS effdte_high
FROM dbo.t_policy
WHERE YEAR(GETDATE()) - 1 = LEFT(CAST(effdte AS varchar), 4) AND
MONTH(GETDATE()) = SUBSTRING(CAST(effdte AS varchar), 5, 2) AND
LEFT(policy, 3) LIKE @.dept_cd AND LEFT(policy, 3) <> 'LPA'
What would the appropriate code be to do this within the Report
Parameters dialog instead? It seems like I'd have a better chance of
success if it were done that way.
Thanks!
MikeNo matter what, I get this error at runtime (Preview mode):
--
Processing Errors
--
An error has occurred during report processing.
Cannot read the next data row for the data set ds_main.
Arithmetic overflow error converting expression to data type datetime.
--
OK
--|||I think I fixed it...
Even though the database type is decimal, when searching by that range,
I have to put single quotes around it. Therefore, I converted the date
type back to a varchar and changed the parameter data types to string.
Now the report runs.
But there should be a more straightforward way to do this within the
Report Parameters dialog box, shouldn't there?sql

Report Parameters - can you export to Excel

When I export my report to Excel I would also like it to carry the
report parameters with it - put it up the top or on a separate sheet.
Is this possible?
PhilYou can in your report designer.
Add a Table header.
And then go to Expressions -> parameters => and place them|||Hi Phil,
what we do is put a table of the parameters at the top of a report and
hide it with a toggle button at the top...our standard report designs
include a set of toggle buttons at the top to toggle visible/invisible
sections of the reports...so we might have 2 or 4 charts and then 2 or
4 tables on the report which are all rendered but some are
hidden...then by pressing the tabs they appear/disappear...this closely
emulates the excel worksheets for reports and the business objects
worksheets......if we went further we could appear/disappear sets of
reports based on toggle buttons...
We wanted the parameters on the top of the report because when it is
printed you need the parameters to tell you what you are looking
at....and of course, when you export to excel all this stuff goes into
the workbook.
Though I have noticed the colours do not translate very well into
workbook..red turns into a horrible grey colour so far...though I am
sure htat will be fixed...
Peter|||thanks. Not ideal because then they effectively show twice in the main
report viewer but at least they go into the report.
thanks again|||good idea - i might try that
thanks|||sorry, not clear who i was replying to - first reply to first one,
second to second etc|||Hi Phil,
yes, we default them hidden on the report so the user does not see them
unless he/she specifically opens the parameters as he/she would do if
he/she wanted to print the report.
The interesting thing was that when sent to excel the hidden portions
of the report are always sent....I mean, that;s the most sensible
thing to do, but it is not always the case that programmers do the most
sensible thing..LOL!!
We quite like this style of solution or presenting the parameters...if
you come up with something better let us know!!!
Best Regards
Peter Nolan
www.peternolan.com

Report Parameters

Is there a way to force report designer overlook parameter dependencies, so that a report could be displayed by either of the two parameters although one parameter accepts but not requires the other?

For Example:

In my report I have two parameters, DeptID and ProjectID. The two drop downs display Departments and Projects respectively but only if the department is selected, the Projects list becomes active as its expecting a DepartmentID in the stored proc but accepts a null value as well. So, what I would like to achieve is display ALL projects in the Projects list, even if no department is selected. Any ideas how to do that?

Thanks.

Essentially what I need is to be able to filter the Projects List and not necessarily depend on the DepartmentID of the selected value in Department List. Kind of like filtering on Report Parameters without using Cascading Parameters?|||

Hello,

why can't you have all Departments selected as default. The the user does not have to select departments id actively in order to select projects.

Report Parameters

In the report parameters dialogue box I have set up a Non Queried list, when
someone selects Grand Rapids(label) I want to return multiple values, 21 OR
22 OR 23, these are the Grand Rapids Department ID's. Can this be done? If
so what is the correct syntax?
Label Value
Grand Rapids 21 OR 22 OR 23
WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)Why not make it a string parameter that returns "21,22,23"
and make the where clause like:
WHERE (Acclaim.Staff.DepartmentID in @.DepartmentID)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sampson" <Sampson@.discussions.microsoft.com> wrote in message
news:F361919F-A515-4AE2-B7FA-0D68B6842542@.microsoft.com...
> In the report parameters dialogue box I have set up a Non Queried list,
when
> someone selects Grand Rapids(label) I want to return multiple values, 21
OR
> 22 OR 23, these are the Grand Rapids Department ID's. Can this be done?
If
> so what is the correct syntax?
> Label Value
> Grand Rapids 21 OR 22 OR 23
>
> WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)
>|||That will not work.You would think it would but it doesn't (most likely on
purpose to prevent injection attacks). You can do a dynamic query. Go into
generic query mode and do this:
= "Select blah from sometable where (Acclaim.Staff.DepartmentID in (" &
Parameters!DepartmentID.value & ")"
Of course if it is not a list of integers you then need to add parse this
(you could write code behind to do this) and put in the single quotes.
The other alternative is pass the parameter to a stored procedure and do it
from there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uAOOxf2RFHA.2528@.TK2MSFTNGP10.phx.gbl...
> Why not make it a string parameter that returns "21,22,23"
> and make the where clause like:
> WHERE (Acclaim.Staff.DepartmentID in @.DepartmentID)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Sampson" <Sampson@.discussions.microsoft.com> wrote in message
> news:F361919F-A515-4AE2-B7FA-0D68B6842542@.microsoft.com...
> > In the report parameters dialogue box I have set up a Non Queried list,
> when
> > someone selects Grand Rapids(label) I want to return multiple values, 21
> OR
> > 22 OR 23, these are the Grand Rapids Department ID's. Can this be done?
> If
> > so what is the correct syntax?
> >
> > Label Value
> > Grand Rapids 21 OR 22 OR 23
> >
> >
> > WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)
> >
>

Report Parameters

I am using report parameters that are non-queried. I want one parameter
label to be "All" and I want it to act as a wildcard. I tried giving it a
value of % and setting the Data Type to String instead of integer. It didn't
work though. What value will act as a wildcard and include all integers
pulled from my database.Chandler,
If it is only one parameter that you want to act as "all" only, then you can
just not use it at all... By default all records will be selected.
If it is a parameter of type integer that can have
- either integer values, which will filter by that specific value
- or something that will mean "all" - here, you could use blank or null
for that purpose.
And the where clause could look like that:
where [...] and YourIntField like (case when Isnull(@.YourIntParam,0) = 0
then '%' else @.YourIntParam end)
This will work, of course, if there are no values of zero in your field (I
mean if you don't pass on purpose the value zero for the parameter).
Otherwise, just choose a different value to compare to, instead of 0.
HTH,
Andrei.
"Chandler" <Chandler@.discussions.microsoft.com> wrote in message
news:137E11DC-3EE7-4DC8-B6D4-BF4607A7E585@.microsoft.com...
> I am using report parameters that are non-queried. I want one parameter
> label to be "All" and I want it to act as a wildcard. I tried giving it a
> value of % and setting the Data Type to String instead of integer. It
didn't
> work though. What value will act as a wildcard and include all integers
> pulled from my database.

Report Parameters

I have a report with a parameter set up for Account name. When I use
the drop down I do display the 5 accounts associated with the 5 rows
data. (One shows in the parameter drop down 3 times because it has
data for 3 different rows.)
Account 1
Account 1
Account 1
Account 2
Account 3
My question is, what else do I need to make the parameter at the top
work, currently my report is not filtering the data by Account. I
still see all 5 rows of data. Do I need a report filter as well?On Oct 1, 10:25 am, BLAW <brad...@.gmail.com> wrote:
> I have a report with a parameter set up for Account name. When I use
> the drop down I do display the 5 accounts associated with the 5 rows
> data. (One shows in the parameter drop down 3 times because it has
> data for 3 different rows.)
> Account 1
> Account 1
> Account 1
> Account 2
> Account 3
> My question is, what else do I need to make the parameter at the top
> work, currently my report is not filtering the data by Account. I
> still see all 5 rows of data. Do I need a report filter as well?
If I understand you correctly, you will want to set your dataset from
the Data tab to a variable. Something like:
select x, y, z, ... from table_x where AccountName = @.AccountName
Then in the Parameters tab (select via Edit Selected Dataset [...] ->
Parameters tab) set the variable @.AccountName (below Parameters:Name)
to Value: =Parameters!AccontName.Value
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql