Placeholder Question...Simple (I think)

OK...all you gurus of SSRS I have a question that I think is simple but who knows...I have asked other questions that I thought were simple and it turned out it is very hard or impossible to do in SSRS 2005 version that I have.

Question: I have a Matix report that has a start date and an enddate parameter (among others). I need to do the following:

* Need to fill all columns that have no data with a value of zero

How in the world do you do this in Reporting Services 2005?

HELP!!!!

You could build this into your expression in your report using IIf() and IsNull() similar to the following:

IIf(IsNull(Fields!myfield.value), 0, Fields!myfield.value)

OR

You could build it into your SQL query similar to the following:

SELECT CASE WHEN IsNull(MyField) THEN 0 ELSE MyField END AS MyField FROM MYTABLE WHERE ......

|||

If I do this in SSRS...how do I nest a sum function?

Sum(Fields!Responses.Value)

|||

I would think you could do this:

=IIf(IsNull(Sum(Fields!myfield.value)), 0, Sum(Fields!myfield.value))

That is assuming that the value of the sum is in fact null.

|||Thank You!!!

|||You're welcome.

placeholder for uniqueidentifier

Hi,
I need help with a SQL SELECT statement. I am using a UNION to merge four
tables together. For the tables that did not include a particular field, I
have used a placeholder of 'N/A' in the SELECT statement as follows:
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_lot_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_subdivision_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
current_retail_price, current_expiration_date, plan_availability_pricing_id,
plan_id
FROM hb_view_business_unit_plan
UNION
SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A',
'N/A', 'N/A', plan_number, plan_name, current_retail_price,
current_expiration_date, plan_availability_pricing_id, plan_id
FROM hb_view_organization_plan
I am having a problem with using the 'N/A' placeholder in lieu of a
uniqueidentifier. It works for varchar field types but apparently not
uniqueidentifier type fields. I receive the error message "error converting
from character string into uniqueidentifier". Thanks for your help.
DonYou could cast the top uniqueidentifier to a varchar(36) and it will work.
It seems a bit wierd though, because are you going to display the guid to
the user? Pretty hideous if you are.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
<dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
> I have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A',
> 'N/A', 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error
> converting from character string into uniqueidentifier". Thanks for your
> help.
>
> Don
>
>|||When you use union, first query defines column data types for all other
queries.
So, the problem is (probably, you did not provide DDl, so I cannot tell for
sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id which
can be of GUID data type.
Solution can be to replace 'N/A' with "empty" guid
'{00000000-0000-0000-0000-000000000000}' or with null.
Regards,
Marko Simic
"dbj" wrote:

> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Sorry I forgot to write the most important part :)
SQL try to convert 'N/A' to uniqueidentifier data type which is not
possible. you may try this to see what will happen:
select newid()
union
select 'N/A'
Regards,
Marko Simic
"dbj" wrote:

> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Oops, no it won't (d'oh!) you will have to cast them all to char:
select cast( 'na' as varchar(36))
union
select cast(newId() as varchar(36))
Sorry,
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:u%238on1uPFHA.532@.TK2MSFTNGP09.phx.gbl...
> You could cast the top uniqueidentifier to a varchar(36) and it will work.
> It seems a bit wierd though, because are you going to display the guid to
> the user? Pretty hideous if you are.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> <dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
>|||Marko,
Thank you very much. Your answer worked on the first try.
Don
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:CA618990-5DFD-4753-B3CC-13FA859F136C@.microsoft.com...
> When you use union, first query defines column data types for all other
> queries.
> So, the problem is (probably, you did not provide DDl, so I cannot tell
> for
> sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id
> which
> can be of GUID data type.
> Solution can be to replace 'N/A' with "empty" guid
> '{00000000-0000-0000-0000-000000000000}' or with null.
> Regards,
> Marko Simic
> "dbj" wrote:
>