Do not render report with no data

Hi!
I would like to stop processing of a report that has no data in a specific
dataset. Is this somehow possible?
In Access its possible to not process a report if there is no data behind
it.
Or is the only way to write an application that checks if the data exist and
if not just skips the Render part?
Thanks for any hints!
rgds,
tomOr can I somehow throw an excpetion inside of the report if a specific
dataset has no data?
"Thomas Kern" <tomiknocker@.hotmail.com> wrote in message
news:OUgDeGX$EHA.3372@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I would like to stop processing of a report that has no data in a
> specific dataset. Is this somehow possible?
> In Access its possible to not process a report if there is no data behind
> it.
> Or is the only way to write an application that checks if the data exist
> and if not just skips the Render part?
> Thanks for any hints!
> rgds,
> tom
>|||Thomas Kern wrote:
> Or can I somehow throw an excpetion inside of the report if a specific
> dataset has no data?
You can use the rowcount-property of the dataset and that the
report-visibility or your dataregion or elements to true or false.
regards
Frank
www.xax.de|||Where is the RowCount property of a dataset?
I need to limit mine...
thanks,
trint
Frank Matthiesen wrote:
> Thomas Kern wrote:
> > Or can I somehow throw an excpetion inside of the report if a
specific
> > dataset has no data?
> You can use the rowcount-property of the dataset and that the
> report-visibility or your dataregion or elements to true or false.
> regards
> Frank
> www.xax.de|||Use the CountRows aggregate function. E.g. =CountRows("DatasetName")
See also:
http://msdn.microsoft.com/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"trint" <trinity.smith@.gmail.com> wrote in message
news:1106067185.185429.115960@.z14g2000cwz.googlegroups.com...
> Where is the RowCount property of a dataset?
> I need to limit mine...
> thanks,
> trint
>
> Frank Matthiesen wrote:
> > Thomas Kern wrote:
> > > Or can I somehow throw an excpetion inside of the report if a
> specific
> > > dataset has no data?
> >
> > You can use the rowcount-property of the dataset and that the
> > report-visibility or your dataregion or elements to true or false.
> >
> > regards
> >
> > Frank
> > www.xax.de
>|||how can I set the report visibility to false?
I really want to prevent to report from beeing generated in this case.
thanks.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:354qr8F4969gvU1@.individual.net...
> Thomas Kern wrote:
>> Or can I somehow throw an excpetion inside of the report if a specific
>> dataset has no data?
> You can use the rowcount-property of the dataset and that the
> report-visibility or your dataregion or elements to true or false.
> regards
> Frank
> www.xax.de
>
>|||I found the following solution but its more database-centric:
-) Check the @.@.rowcount of the query inside the Stored Procedure.
-) If @.@.rowcount = 0, RAISERROR
here we go: this is becomes an exception in the report and it is not
rendered!
tom
"Thomas Kern" <tomiknocker@.hotmail.com> wrote in message
news:us3ocLa$EHA.2984@.TK2MSFTNGP09.phx.gbl...
> how can I set the report visibility to false?
> I really want to prevent to report from beeing generated in this case.
> thanks.
> "Frank Matthiesen" <fm@.xax.de> wrote in message
> news:354qr8F4969gvU1@.individual.net...
>> Thomas Kern wrote:
>> Or can I somehow throw an excpetion inside of the report if a specific
>> dataset has no data?
>> You can use the rowcount-property of the dataset and that the
>> report-visibility or your dataregion or elements to true or false.
>> regards
>> Frank
>> www.xax.de
>>
>

Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)