Configuration Error on Encryption Key

I came across an issue while configuring SQL Server 2005 Reporting Services. In the reporting services configuration tool, there is a step the either Backup, Restore, or Change the encryption key for the new Reporting Services instance. I backed up the key since this is the first installation of SQL Server and Reporting Services on this machine.

I had expected that the configuration would then allow me to progress to the next step in the configuration, "Initialization". But regardless of how I handle the Encryption Key step, the option to move "Initialization" does not become available.
Has anyone had this happen to them? Or better yet, does anyone have a solution to this?

The report server only needs to be manually initialized if you are connecting the server to a web farm. It is needed to ensure that all nodes in the farm are using the same encryption key. In your case, since you have backed up the key, the report server is already initialized. The option is likely disabled because you are using an edition of SQL Server that does not support web farms (it is only available in enterprise and developer editions).|||Yep, I found that to be the case yesterday while trying to troubleshoot this.
I was checking on another server trying to find where the setup went wrong and determined that the issue is actually that the Initialization step didn't become available as it should. There must have been an error in one of the preceding steps that didn't show up as it should have.
At this point I am going to try and run the command-line initialization to complete the setup. And if that doesn't work, I'll probably have to uninstall and re-install Reporting Services.

Config error

I get this error. Does any body came across this and found some solution?

Compiler Error Message: CS0433: The type 'Microsoft.Web.UI.ScriptManager' exists in both 'c:\WINDOWS.000\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\websites_hrdbms\3acda2bb\a63d28c5\assembly\dl3\32deaeca\005606e1_3759c601\Microsoft.Web.Atlas.DLL' and 'c:\WINDOWS.000\assembly\GAC_MSIL\Microsoft.Web.Extensions\1.0.61025.0__31bf3856ad364e35\Microsoft.Web.Extensions.dll'

I have created the application with ATLAS July CTP . currently upgraded to ASP.NET AJAX BETA 1. I face the above problem

Ramanan wrote:

I get this error. Does any body came across this and found some solution?

Compiler Error Message: CS0433: The type 'Microsoft.Web.UI.ScriptManager' exists in both 'c:\WINDOWS.000\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\websites_hrdbms\3acda2bb\a63d28c5\assembly\dl3\32deaeca\005606e1_3759c601\Microsoft.Web.Atlas.DLL' and 'c:\WINDOWS.000\assembly\GAC_MSIL\Microsoft.Web.Extensions\1.0.61025.0__31bf3856ad364e35\Microsoft.Web.Extensions.dll'

I have created the application with ATLAS July CTP . currently upgraded to ASP.NET AJAX BETA 1. I face the above problem

Wrong forum.

Conditional statements in Views

Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?

> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!

Conditional Count in Detail Row - Count results across columns

Hi,
I am trying to establish a way to count the number of True/False entries
across a number of columns. That is I have a dataset returning results of
organisations (rows) and whether they have opted for a particular criteria
(columns). I want to know how many of the criteria a particular organisation
has opted for and display that on the end of the detail row for each
organisation.
Org Name Criteria1 Criteria2 Criteria3 Total Selected
ABC Ltd Yes No Yes 2
LMO Ltd No No Yes 1
XYZ Ltd Yes Yes Yes 3
Totals 2 1 3
I have managed the Totals in the footer using a conditional count.
eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
Fields!cf_subindicator111name.Value, Nothing))
I am unsure of how to do the same thing at the end of the detail line (Total
Selected) for a conditional count of the discreet fields in the dataset. I
have 89 Criteria in all to count.
I have a feeling that this needs to be done at the SQL query level creating
a running total for each organisation returned where they have opted Yes for
a Criteria but I don't know where to start with this.
Hope this is clear.
Any ideas?
SimonHi,
You were right :). You should do it in your stored procedure. Maybe you
should use PIVOT operator ans aggregation function, like COUNT. Also, you
should try the RunningValue function in SSRS in worst case scenario.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> Hi,
> I am trying to establish a way to count the number of True/False entries
> across a number of columns. That is I have a dataset returning results of
> organisations (rows) and whether they have opted for a particular criteria
> (columns). I want to know how many of the criteria a particular
> organisation
> has opted for and display that on the end of the detail row for each
> organisation.
> Org Name Criteria1 Criteria2 Criteria3 Total Selected
> ABC Ltd Yes No Yes 2
> LMO Ltd No No Yes 1
> XYZ Ltd Yes Yes Yes 3
> Totals 2 1 3
> I have managed the Totals in the footer using a conditional count.
> eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> Fields!cf_subindicator111name.Value, Nothing))
> I am unsure of how to do the same thing at the end of the detail line
> (Total
> Selected) for a conditional count of the discreet fields in the dataset. I
> have 89 Criteria in all to count.
> I have a feeling that this needs to be done at the SQL query level
> creating
> a running total for each organisation returned where they have opted Yes
> for
> a Criteria but I don't know where to start with this.
> Hope this is clear.
> Any ideas?
> Simon|||Thanks Janos.
The issue here is I am unclear on the specifics of how to do this, where it
sits in the select statement and what the format would be. I am only just
managing to get the select, from and where statements working at the moment
:) New to Reporting Services and SQL and could do with some pointers. I will
look up the Pivot operator and see if that gets me anywhere. Any pointers
would be appreciated.
Simon
"BERKE Janos" wrote:
> Hi,
> You were right :). You should do it in your stored procedure. Maybe you
> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> should try the RunningValue function in SSRS in worst case scenario.
> Regards,
> Janos
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> > Hi,
> >
> > I am trying to establish a way to count the number of True/False entries
> > across a number of columns. That is I have a dataset returning results of
> > organisations (rows) and whether they have opted for a particular criteria
> > (columns). I want to know how many of the criteria a particular
> > organisation
> > has opted for and display that on the end of the detail row for each
> > organisation.
> >
> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >
> > ABC Ltd Yes No Yes 2
> > LMO Ltd No No Yes 1
> > XYZ Ltd Yes Yes Yes 3
> >
> > Totals 2 1 3
> >
> > I have managed the Totals in the footer using a conditional count.
> >
> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> > Fields!cf_subindicator111name.Value, Nothing))
> >
> > I am unsure of how to do the same thing at the end of the detail line
> > (Total
> > Selected) for a conditional count of the discreet fields in the dataset. I
> > have 89 Criteria in all to count.
> >
> > I have a feeling that this needs to be done at the SQL query level
> > creating
> > a running total for each organisation returned where they have opted Yes
> > for
> > a Criteria but I don't know where to start with this.
> >
> > Hope this is clear.
> >
> > Any ideas?
> >
> > Simon
>|||Hi Simon,
Post here a similar table structure to me, and I'll write the code for you
;). I will add some comments to my code as well.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> Thanks Janos.
> The issue here is I am unclear on the specifics of how to do this, where
> it
> sits in the select statement and what the format would be. I am only just
> managing to get the select, from and where statements working at the
> moment
> :) New to Reporting Services and SQL and could do with some pointers. I
> will
> look up the Pivot operator and see if that gets me anywhere. Any pointers
> would be appreciated.
> Simon
> "BERKE Janos" wrote:
>> Hi,
>> You were right :). You should do it in your stored procedure. Maybe you
>> should use PIVOT operator ans aggregation function, like COUNT. Also, you
>> should try the RunningValue function in SSRS in worst case scenario.
>> Regards,
>> Janos
>> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
>> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
>> > Hi,
>> >
>> > I am trying to establish a way to count the number of True/False
>> > entries
>> > across a number of columns. That is I have a dataset returning results
>> > of
>> > organisations (rows) and whether they have opted for a particular
>> > criteria
>> > (columns). I want to know how many of the criteria a particular
>> > organisation
>> > has opted for and display that on the end of the detail row for each
>> > organisation.
>> >
>> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
>> >
>> > ABC Ltd Yes No Yes 2
>> > LMO Ltd No No Yes 1
>> > XYZ Ltd Yes Yes Yes 3
>> >
>> > Totals 2 1 3
>> >
>> > I have managed the Totals in the footer using a conditional count.
>> >
>> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
>> > Fields!cf_subindicator111name.Value, Nothing))
>> >
>> > I am unsure of how to do the same thing at the end of the detail line
>> > (Total
>> > Selected) for a conditional count of the discreet fields in the
>> > dataset. I
>> > have 89 Criteria in all to count.
>> >
>> > I have a feeling that this needs to be done at the SQL query level
>> > creating
>> > a running total for each organisation returned where they have opted
>> > Yes
>> > for
>> > a Criteria but I don't know where to start with this.
>> >
>> > Hope this is clear.
>> >
>> > Any ideas?
>> >
>> > Simon|||Janos,
Hi, not sure what you mean but my current query is as follows.
SELECT CRMAF_FilteredIncident.customeridname,
CRMAF_FilteredIncident.incidentid,
CRMAF_FilteredCF_Assessment.cf_casenumberid,
CRMAF_FilteredCF_Assessment.cf_applicationsubtypename,
CRMAF_FilteredCF_Assessment.cf_applicationtypename,
CRMAF_FilteredCF_Assessment.cf_subindicator111name,
CRMAF_FilteredCF_Assessment.cf_subindicator112name,
CRMAF_FilteredCF_Assessment.cf_subindicator121name,
CRMAF_FilteredCF_Assessment.cf_subindicator122name,
CRMAF_FilteredCF_Assessment.cf_subindicator123name,
CRMAF_FilteredCF_Assessment.cf_subindicator131name,
CRMAF_FilteredCF_Assessment.cf_subindicator141name,
CRMAF_FilteredCF_Assessment.cf_subindicator211name,
CRMAF_FilteredCF_Assessment.cf_subindicator221name,
CRMAF_FilteredCF_Assessment.cf_subindicator231name,
CRMAF_FilteredCF_Assessment.cf_subindicator232name,
CRMAF_FilteredCF_Assessment.cf_subindicator233name,
CRMAF_FilteredCF_Assessment.cf_subindicator241name,
CRMAF_FilteredCF_Assessment.cf_subindicator242name,
CRMAF_FilteredCF_Assessment.cf_subindicator243name,
CRMAF_FilteredCF_Assessment.cf_subindicator244name,
CRMAF_FilteredCF_Assessment.cf_subindicator245name,
CRMAF_FilteredCF_Assessment.cf_subindicator251name,
CRMAF_FilteredCF_Assessment.cf_subindicator252name,
CRMAF_FilteredCF_Assessment.cf_subindicator311name,
CRMAF_FilteredCF_Assessment.cf_subindicator312name,
CRMAF_FilteredCF_Assessment.cf_subindicator313name,
CRMAF_FilteredCF_Assessment.cf_subindicator321name,
CRMAF_FilteredCF_Assessment.cf_subindicator322name,
CRMAF_FilteredCF_Assessment.cf_subindicator323name,
CRMAF_FilteredCF_Assessment.cf_subindicator331name,
CRMAF_FilteredCF_Assessment.cf_subindicator332name,
CRMAF_FilteredCF_Assessment.cf_subindicator341name,
CRMAF_FilteredCF_Assessment.cf_subindicator342name,
CRMAF_FilteredCF_Assessment.cf_assessmenttypename,
CRMAF_FilteredCF_Assessment.cf_subindicator411name,
CRMAF_FilteredCF_Assessment.cf_subindicator421name,
CRMAF_FilteredCF_Assessment.cf_subindicator422name,
CRMAF_FilteredCF_Assessment.cf_subindicator423name,
CRMAF_FilteredCF_Assessment.cf_subindicator431name,
CRMAF_FilteredCF_Assessment.cf_subindicator441name,
CRMAF_FilteredCF_Assessment.cf_subindicator511name,
CRMAF_FilteredCF_Assessment.cf_subindicator512name,
CRMAF_FilteredCF_Assessment.cf_subindicator513name,
CRMAF_FilteredCF_Assessment.cf_subindicator514name,
CRMAF_FilteredCF_Assessment.cf_subindicator521name,
CRMAF_FilteredCF_Assessment.cf_subindicator522name,
CRMAF_FilteredCF_Assessment.cf_subindicator523name,
CRMAF_FilteredCF_Assessment.cf_subindicator524name,
CRMAF_FilteredCF_Assessment.cf_subindicator525name,
CRMAF_FilteredCF_Assessment.cf_subindicator6110name,
CRMAF_FilteredCF_Assessment.cf_subindicator6111name,
CRMAF_FilteredCF_Assessment.cf_subindicator611name,
CRMAF_FilteredCF_Assessment.cf_subindicator612name,
CRMAF_FilteredCF_Assessment.cf_subindicator613name,
CRMAF_FilteredCF_Assessment.cf_subindicator614name,
CRMAF_FilteredCF_Assessment.cf_subindicator615name,
CRMAF_FilteredCF_Assessment.cf_subindicator616name,
CRMAF_FilteredCF_Assessment.cf_subindicator617name,
CRMAF_FilteredCF_Assessment.cf_subindicator618name,
CRMAF_FilteredCF_Assessment.cf_subindicator619name,
CRMAF_FilteredCF_Assessment.cf_subindicator621name,
CRMAF_FilteredCF_Assessment.cf_subindicator622name,
CRMAF_FilteredCF_Assessment.cf_subindicator623name,
CRMAF_FilteredCF_Assessment.cf_subindicator624name,
CRMAF_FilteredCF_Assessment.cf_subindicator625name,
CRMAF_FilteredCF_Assessment.cf_subindicator626name,
CRMAF_FilteredCF_Assessment.cf_subindicator631name,
CRMAF_FilteredCF_Assessment.cf_subindicator632name,
CRMAF_FilteredCF_Assessment.cf_subindicator633name,
CRMAF_FilteredCF_Assessment.cf_subindicator634name,
CRMAF_FilteredCF_Assessment.cf_subindicator641name,
CRMAF_FilteredCF_Assessment.cf_subindicator642name,
CRMAF_FilteredCF_Assessment.cf_subindicator643name,
CRMAF_FilteredCF_Assessment.cf_subindicator651name,
CRMAF_FilteredCF_Assessment.cf_subindicator652name,
CRMAF_FilteredCF_Assessment.cf_subindicator711name,
CRMAF_FilteredCF_Assessment.cf_subindicator712name,
CRMAF_FilteredCF_Assessment.cf_subindicator713name,
CRMAF_FilteredCF_Assessment.cf_subindicator714name,
CRMAF_FilteredCF_Assessment.cf_subindicator721name,
CRMAF_FilteredCF_Assessment.cf_subindicator731name,
CRMAF_FilteredCF_Assessment.cf_subindicator741name,
CRMAF_FilteredCF_Assessment.cf_subindicator742name,
CRMAF_FilteredCF_Assessment.cf_subindicator743name,
CRMAF_FilteredCF_Assessment.cf_subindicator811name,
CRMAF_FilteredCF_Assessment.cf_subindicator812name,
CRMAF_FilteredCF_Assessment.cf_subindicator821name,
CRMAF_FilteredCF_Assessment.cf_subindicator911name,
CRMAF_FilteredCF_Assessment.cf_subindicator921name,
CRMAF_FilteredCF_Assessment.cf_subindicator931name,
CRMAF_FilteredCF_Assessment.cf_subindicator941name,
CRMAF_FilteredCF_Assessment.cf_subindicator951name,
CRMAF_FilteredCF_Assessment.cf_subindicator961name,
CRMAF_FilteredIncident.cf_casesubtypename
FROM FilteredCF_Assessment AS CRMAF_FilteredCF_Assessment INNER JOIN
FilteredIncident AS CRMAF_FilteredIncident ON
CRMAF_FilteredCF_Assessment.cf_casenumberid =CRMAF_FilteredIncident.incidentid
WHERE (CRMAF_FilteredIncident.cf_casesubtypename = 'Assessment')
What I want to do is a conditional count where the subindicator fields are
equal to yes and be able to total those at the end of each row.
Thanks.
"BERKE Janos" wrote:
> Hi Simon,
> Post here a similar table structure to me, and I'll write the code for you
> ;). I will add some comments to my code as well.
> Regards,
> Janos
>
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> > Thanks Janos.
> >
> > The issue here is I am unclear on the specifics of how to do this, where
> > it
> > sits in the select statement and what the format would be. I am only just
> > managing to get the select, from and where statements working at the
> > moment
> > :) New to Reporting Services and SQL and could do with some pointers. I
> > will
> > look up the Pivot operator and see if that gets me anywhere. Any pointers
> > would be appreciated.
> >
> > Simon
> >
> > "BERKE Janos" wrote:
> >
> >> Hi,
> >>
> >> You were right :). You should do it in your stored procedure. Maybe you
> >> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> >> should try the RunningValue function in SSRS in worst case scenario.
> >>
> >> Regards,
> >>
> >> Janos
> >>
> >> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> >> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> >> > Hi,
> >> >
> >> > I am trying to establish a way to count the number of True/False
> >> > entries
> >> > across a number of columns. That is I have a dataset returning results
> >> > of
> >> > organisations (rows) and whether they have opted for a particular
> >> > criteria
> >> > (columns). I want to know how many of the criteria a particular
> >> > organisation
> >> > has opted for and display that on the end of the detail row for each
> >> > organisation.
> >> >
> >> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >> >
> >> > ABC Ltd Yes No Yes 2
> >> > LMO Ltd No No Yes 1
> >> > XYZ Ltd Yes Yes Yes 3
> >> >
> >> > Totals 2 1 3
> >> >
> >> > I have managed the Totals in the footer using a conditional count.
> >> >
> >> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> >> > Fields!cf_subindicator111name.Value, Nothing))
> >> >
> >> > I am unsure of how to do the same thing at the end of the detail line
> >> > (Total
> >> > Selected) for a conditional count of the discreet fields in the
> >> > dataset. I
> >> > have 89 Criteria in all to count.
> >> >
> >> > I have a feeling that this needs to be done at the SQL query level
> >> > creating
> >> > a running total for each organisation returned where they have opted
> >> > Yes
> >> > for
> >> > a Criteria but I don't know where to start with this.
> >> >
> >> > Hope this is clear.
> >> >
> >> > Any ideas?
> >> >
> >> > Simon
> >>
>

concatinating string values from multiple rows

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:

CREATE TABLE [dbo].[TransactionTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MaskValue] [int] NOT NULL) ON [PRIMARY]

CREATE TABLE [dbo].[HDResponse](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[LoggedDateTime] [datetime] NULL,
[ResponseTypeTripID] [int] NULL,
[ResponseTypeID] [int] NULL,
[ResponseTypeObjectID] [int] NULL,
[ObjectID] [int] NULL,
[IDHolderID] [int] NULL,
[TransportCode] [int] NULL,
[CardID] [int] NULL,
[IssueCode] [smallint] NULL,
[EventDateTime] [datetime] NULL,
[Response] [bit] NULL,
[TranTypeID] [int] NULL)
ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

What version of SQL Server are you using? SET CONCAT_NULL_YEILDS_NULL is ON by default unless you have changed the settings after connecting to SQL Server. This is true for any connections made via ODBC/OLEDB. You can use the PIVOT operator in SQL Server 2005 to solve this problem. You can also do this using a standard SQL technique like below:

select max(case p.trantype when 1 then p.description else '' end) +
max(case p.trantype when 2 then ' ' + p.description else '' end) +
max(case p.trantype when 4 then ' ' + p.description else '' end) +
max(case p.trantype when 8 then ' ' + p.description else '' end) +
max(case p.trantype when 16 then ' ' + p.description else '' end) as "Transaction Type"
from (
select t.description, (t.MaskValue & h.TranTypeID) as trantype
from HDResponse as h
join transactiontypes as t
on (t.MaskValue & h.TranTypeID) in (1, 2, 4, 8, 16)
) as p