Boolean Parameter

Hi,

I have a Summary report and a Detail (drillthrough) report.

the summary report displays.

Summary report

Code Snippet

Adult | Male | Count

--

Yes | Yes | 50

Yes | No | 9

No | Yes | 20

No | No | 50

| 129

When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.

The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.

Any ideas?

Hi,

This may be due to the way you pass parameters to the sub-report.

you may not get answers if you are not more specific.

How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.

This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.

Please provide some more details.

Philippe

|||

Hi,

The report queries against a database field that is a boolean but can accept a NULL value.

So i configured the recieving report to accept a boolean value "True, False & also Null".

If i used LIKE as the where criteria it obviously wouldn't be as efficent.

Regards

Ian

|||

What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).

One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...

The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.

>L<

|||

Hi Lisa,

Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.

i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol

cheers everyone.

ian

|||>> would like a rant about the SRS report and boolean parameter

I have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.

In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".

>L<

Boolean Parameter

Hi,

I have a Summary report and a Detail (drillthrough) report.

the summary report displays.

Summary report

Code Snippet

Adult | Male | Count

--

Yes | Yes | 50

Yes | No | 9

No | Yes | 20

No | No | 50

| 129

When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.

The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.

Any ideas?

Hi,

This may be due to the way you pass parameters to the sub-report.

you may not get answers if you are not more specific.

How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.

This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.

Please provide some more details.

Philippe

|||

Hi,

The report queries against a database field that is a boolean but can accept a NULL value.

So i configured the recieving report to accept a boolean value "True, False & also Null".

If i used LIKE as the where criteria it obviously wouldn't be as efficent.

Regards

Ian

|||

What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).

One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...

The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.

>L<

|||

Hi Lisa,

Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.

i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol

cheers everyone.

ian

|||>> would like a rant about the SRS report and boolean parameter

I have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.

In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".

>L<

Boolean Parameter

Hi,

I have a Summary report and a Detail (drillthrough) report.

the summary report displays.

Summary report

Code Snippet

Adult | Male | Count

--

Yes | Yes | 50

Yes | No | 9

No | Yes | 20

No | No | 50

| 129

When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.

The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.

Any ideas?

Hi,

This may be due to the way you pass parameters to the sub-report.

you may not get answers if you are not more specific.

How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.

This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.

Please provide some more details.

Philippe

|||

Hi,

The report queries against a database field that is a boolean but can accept a NULL value.

So i configured the recieving report to accept a boolean value "True, False & also Null".

If i used LIKE as the where criteria it obviously wouldn't be as efficent.

Regards

Ian

|||

What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).

One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...

The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.

>L<

|||

Hi Lisa,

Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.

i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol

cheers everyone.

ian

|||>> would like a rant about the SRS report and boolean parameter

I have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.

In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".

>L<

Boolean formatting in report

I have a data source with a column that is a boolean data type. When
I render this data on the report the value of 0 displays as "False"
and 1 displays as "True". I would like 0 to display as "No" and 1 to
display as "Yes". I am assuming I have to set up an IIF statement
that evaluates the value and changes the display based on that. I was
wondering if anyone knew of a better way, such as a Custom Format
value that would be a better route?
Thanks in advance for any assistance!On Sep 20, 2:03 pm, midas <mcneeley1...@.yahoo.com> wrote:
> I have a data source with a column that is a boolean data type. When
> I render this data on the report the value of 0 displays as "False"
> and 1 displays as "True". I would like 0 to display as "No" and 1 to
> display as "Yes". I am assuming I have to set up an IIF statement
> that evaluates the value and changes the display based on that. I was
> wondering if anyone knew of a better way, such as a Custom Format
> value that would be a better route?
> Thanks in advance for any assistance!
The way you mentioned is quick and the effort is negligible.
=iif(Fields!SomeField.Value = True, "Yes", "No") -or- =iif(Fields!
SomeField.Value = 1, "Yes", "No")
You could also try using a case statement in the query/stored
procedure that is sourcing the report.
select case when SomeField = 'True' then 'Yes' else 'No' end as
SomeField, ... from table_x ...
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, I went with the case. I was just trying to be lazy. ;)|||On Sep 21, 2:57 pm, midas <mcneeley1...@.yahoo.com> wrote:
> Thanks, I went with the case. I was just trying to be lazy. ;)
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql

boolean fields displayed as true or false

I have a bit field in my data source that displays as true/false on my
report. I can't see how to set this so that it displays as 1 or 0. In
Visual Studio, can someone help?=iif (Fields!yourData.value = "True", 1, 0)
"beeyule" wrote:
> I have a bit field in my data source that displays as true/false on my
> report. I can't see how to set this so that it displays as 1 or 0. In
> Visual Studio, can someone help?

Body BackgroundImage not displayed in browser

I have been trying to set up reports with a background image. The background displays correctly on the preview tab in Visual Studio, but is not visible when deployed to the report server and rendered in Internet Explorer.

However, if the report is printed, or exported to PDF or Excel, the background displays correctly.

It does not seem to make any difference if the image is external or embedded. Also, the problem only seems to affect the Body properties; a background image attached to a table, for example, seems to display correctly.

Has anybody else seen this behavior? I have not done extensive testing, but every report that I have tried seems to have the same problem. Is there a setting someplace that I am missing, or is this perhaps a bug in IE?

This sounds like a known issue in RS 2005 SP1 where the background images does not show up when rendering with the ReportViewer control. This was addressed in a Hotfix and will also be fixed in Visual Studio 2005 SP1.

-Chris

|||

I am running RS 2005 with SP2. When I render a RS report to Internet Explorer the background image still is not displaying, though the image does print. Exactly as described in the origional message. Any ideas?

Thanks!

Body BackgroundImage not displayed in browser

I have been trying to set up reports with a background image. The background displays correctly on the preview tab in Visual Studio, but is not visible when deployed to the report server and rendered in Internet Explorer.

However, if the report is printed, or exported to PDF or Excel, the background displays correctly.

It does not seem to make any difference if the image is external or embedded. Also, the problem only seems to affect the Body properties; a background image attached to a table, for example, seems to display correctly.

Has anybody else seen this behavior? I have not done extensive testing, but every report that I have tried seems to have the same problem. Is there a setting someplace that I am missing, or is this perhaps a bug in IE?

This sounds like a known issue in RS 2005 SP1 where the background images does not show up when rendering with the ReportViewer control. This was addressed in a Hotfix and will also be fixed in Visual Studio 2005 SP1.

-Chris

|||

I am running RS 2005 with SP2. When I render a RS report to Internet Explorer the background image still is not displaying, though the image does print. Exactly as described in the origional message. Any ideas?

Thanks!

Bizarre behavior rendering Web Archive format

I have a report that displays summary information, I've setup a subscription
on the report that attaches a web archive copy of the report to the
distribution list. When the report is rendered for the attachment in the
middle of the result set for no apparent reason it randomly displays the
values in the report using a different format, like larger font, left
justification where right justification should be. I can send an example of
this for anyone would like to help but the data is sensitive so I'll not post
it publicly..Hi,
Thanks for using Microsoft MSDN Managed Newsgroup.
I would like to work with you on this issue.
Could you please mail me (changliw@.microsoft.com) your example so that I
can reproduce your issue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Danny,
We have recieved your test html and rdl files. I've view the html copy, and
I only see some bold text in the summary/total rows, while the text in
other rows are of the same layout(size , font). Would you tell me the
locatio of the problem text in it? Since the datasources are specific to
your environment, I can not try running it on my local test machine. Is it
possible that you try generate a simplified report with some test data(xml
or a test database file) that can reproduce the same error behavior? if so,
I can perform a complete test on my side. You can reach me through the
email in my signature(remove "online" )
Please feel free to let me know if you have any further concerns.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Danny,
Have you got any further progress on this issue? As mentioned in the last
reply, the problem may need more info to repro, and due to the complexity
and specific nature of the problem, I would suggest you consider contact
CSS for further troubleshooting if this is an urgent issue.
http://msdn.microsoft.com/subscriptions/support/default.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Birthday Query

Hi, i have a table users which contains a birthday field, i would like to have a query that displays the users birthdays for the coming 7 days. Can someone help me with this.
Thanx allot.
Wimmoselect birthday,username from table where convert(datetime,convert(varchar(10),birthday,101) ,101) between convert(datetime,convert(varchar(10),getdate(),101 ),101) and convert(datetime,convert(varchar(10),dateadd(dd,7, getdate()),101),101)

A very rough cut .. but i think it will work|||if this column is datetime, then you can use difference of getdate and datediff function for wanted result.|||Hi Enigma thanx for your reply, your query works but only when the year of birth is the current year|||My Bad ...|||http://www.dbforums.com/showthread.php?threadid=985381 for a similar thread and reference.

HTH|||http://www.dbforums.com/showthread.php?threadid=985381 for a similar thread and reference.

HTH
I tried that solution but it didn't work for me|||How can i change your query so that it dont look @. the year but only to the day & month|||WHERE DATEDIFF(dd,GetDate(),Birthdate) <= 7?|||Ooopps...

DECLARE @.x datetime, @.y datetime
SELECT @.x = GetDate(), @.y = '05/6/2004'
SET @.x = '1900/'+CONVERT(varchar(2),DATEPART(mm,@.x))+'/'+CONVERT(varchar(2),DATEPART(dd,@.x))
SET @.y = '1900/'+CONVERT(varchar(2),DATEPART(mm,@.y))+'/'+CONVERT(varchar(2),DATEPART(dd,@.y))

SELECT DATEDIFF(dd,@.x,@.y)

Did you look at the function?

That should definetly work for you...|||Thanx for your response but i do not completely understand what you are saying here|||I am trying to get the function work but i do not know what i am doing wrong|||Try using:-- ptp 20040427 Compute days to next anniversary

CREATE FUNCTION dbo.fDaysToAnniversary(
@.pdAnn DATETIME -- Base Date
, @.pdNow DATETIME -- Target date, usually today
) RETURNS SMALLINT AS BEGIN

DECLARE @.dNext DATETIME -- Next anniversary

SET @.dNext = DateAdd(year, DateDiff(year, @.pdAnn, @.pdNow), @.pdAnn) -- First guess
IF @.dNext < @.pdNow SET @.dNext = DateAdd(year, 1, @.dNext) -- Bump if already past

RETURN DateDiff(day, @.pdNow, @.dNext)
END
GOThis function takes two arguments, one is a date you wish to project an anniversary for, and the other is the date you want to figure the days from. You'd typically use it something like:SELECT dbo.fDaysToAnniversary('2001-09-11', GetDate())-PatP|||http://r937.com/sql.cfm|||Cut and paste this...I think there was a type-o in the original code...should execute no proble in QA

USE Northwind
GO

CREATE FUNCTION udf_DaysToBDay(@.Bday datetime, @.Dateuntil datetime)
RETURNS int
AS
BEGIN
DECLARE @.x int, @.ModifyBDay datetime

SET @.ModifyBDay = CONVERT(datetime,
CONVERT(char(4),year(@.Dateuntil))
+ '/'
+ CONVERT(char(5),@.Bday,101)
)

SET @.x = datediff(dd, @.Dateuntil, @.ModifyBDay)

RETURN @.x
END

GO

SELECT dbo.udf_DaysToBDay('10/24/1960',GetDate())

SELECT dbo.udf_DaysToBDay(BirthDate,GetDate()) FROM Employees
GO

DROP FUNCTION udf_DaysToBDay
GO|||Just an observation, but my function looks at the problem differently than Brett's does. My function returns the number of days (0-366) until the next anniversary of a given date. Brett's function returns the number of days (-365 to 365) to/from the anniversary of that data this year.

You'll need to decide which works better for you. If you need to be able to work across year boundaries to find upcoming birthdays, I think you'll be better off using mine.

-PatP|||Thanx Guys to all of you, i tried the things that you guys proposed, the thing that workes for me is the 1 from Blindman, I really appreciate the time that all of you spend on my problems.

Thanx Wimmo|||Good point...

They year however is based on whatever the date supplied is...just did that because getdate() is non determenistic...

And yes, I would assume (based on the fact they wanted 7 days) that for Christmas day and forward, mine would be incorrect... :eek: