Can I query my report dataset to populate a textbox?

My report is a series of textbox fields that all will show data from an
Activity table. The report dataset is SELECT * FROM Activity and is named
"Activities".
Textbox1 = count of all activities
Textbox2 = count of all activities where type = prospect
Textbox3 = count of all activities where type = demo
I know I can create separate datasets for each subsequent query but since
they are all queries from the Activity table, I was wondering if I could set
the value for Textbox2 by querying the dataset named "Activities" instead of
creating a new dataset. For example, SELECT Count(*) FROM Activities Where
Type = 'prospect'
Thank you,
MarkOn Apr 6, 11:01=A0am, Markw911 <Markw...@.discussions.microsoft.com>
wrote:
> My report is a series of textbox fields that all will show data from an
> Activity table. =A0The report dataset is SELECT * FROM Activity and is nam=ed
> "Activities".
> Textbox1 =3D count of all activities
> Textbox2 =3D count of all activities where type =3D prospect
> Textbox3 =3D count of all activities where type =3D demo
> I know I can create separate datasets for each subsequent query but since
> they are all queries from the Activity table, I was wondering if I could s=et
> the value for Textbox2 by querying the dataset named "Activities" instead =of
> creating a new dataset. =A0For example, SELECT Count(*) FROM Activities Wh=ere
> Type =3D 'prospect'
> Thank you,
> Mark
Is there a particular reason you're using a series of textboxes
instead of a table? With a table you could just draw what you need
directly from your dataset.|||I am trying to mimic the layout of an existing report. I had a table but did
not think it could handle some of the more detailed results like:
Count column meetingcanceled if meetingcanceled = 'yes'
I still am not sure how I would handle this in a table:
> > Textbox2 = count of all activities where type = prospect
> > Textbox3 = count of all activities where type = demo
since I am trying to return multiple totals from the same column.
From your comments, it sounds like I could have multiple sums per column
with different criteria. If that is true, how do I go about doing it? Use
the activities type column above to give an example if possible.
Thanks.
"toolman" wrote:
> On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> wrote:
> > My report is a series of textbox fields that all will show data from an
> > Activity table. The report dataset is SELECT * FROM Activity and is named
> > "Activities".
> > Textbox1 = count of all activities
> > Textbox2 = count of all activities where type = prospect
> > Textbox3 = count of all activities where type = demo
> >
> > I know I can create separate datasets for each subsequent query but since
> > they are all queries from the Activity table, I was wondering if I could set
> > the value for Textbox2 by querying the dataset named "Activities" instead of
> > creating a new dataset. For example, SELECT Count(*) FROM Activities Where
> > Type = 'prospect'
> > Thank you,
> > Mark
> Is there a particular reason you're using a series of textboxes
> instead of a table? With a table you could just draw what you need
> directly from your dataset.
>|||On Apr 8, 7:54=A0pm, Markw911 <Markw...@.discussions.microsoft.com>
wrote:
> I am trying to mimic the layout of an existing report. =A0I had a table bu=t did
> not think it could handle some of the more detailed results like:
> Count column meetingcanceled if meetingcanceled =3D 'yes'
> I still am not sure how I would handle this in a table:> > Textbox2 =3D co=unt of all activities where type =3D prospect
> > > Textbox3 =3D count of all activities where type =3D demo
> since I am trying to return multiple totals from the same column.
> From your comments, it sounds like I could have multiple sums per column
> with different criteria. =A0If that is true, how do I go about doing it? ==A0Use
> the activities type column above to give an example if possible.
> Thanks.
>
> "toolman" wrote:
> > On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> > wrote:
> > > My report is a series of textbox fields that all will show data from a=n
> > > Activity table. =A0The report dataset is SELECT * FROM Activity and is= named
> > > "Activities".
> > > Textbox1 =3D count of all activities
> > > Textbox2 =3D count of all activities where type =3D prospect
> > > Textbox3 =3D count of all activities where type =3D demo
> > > I know I can create separate datasets for each subsequent query but si=nce
> > > they are all queries from the Activity table, I was wondering if I cou=ld set
> > > the value for Textbox2 by querying the dataset named "Activities" inst=ead of
> > > creating a new dataset. =A0For example, SELECT Count(*) FROM Activitie=s Where
> > > Type =3D 'prospect'
> > > Thank you,
> > > Mark
> > Is there a particular reason you're using a series of textboxes
> > instead of a table? =A0With a table you could just =A0draw what you need=
> > directly from your dataset.- Hide quoted text -
> - Show quoted text -
You could insert the following expressions into your table cells in
the table footer row. Hide or remove the detail row.
Table Header Row: All Activities_____________/
Prospects______________________________/Demos
Table Footer Row: =3DCount(Fields!Type.Value) =3DSUM(IIF(Fields!
Type.Value =3D "Prospect",1,0)) =3DSUM(IIF(Fields!Type.Value =3D "Demo",
1,0))
OR
Substitute =3DCOUNT(IIF(Fields!Type.Value =3D "Prospect",Fields!
Type.Value,Nothing)) for =3DSUM(IIF(Fields!Type.Value =3D "Prospect",1,0))
Either expression works
Good luck|||On Apr 9, 3:31=A0pm, toolman <t...@.infocision.com> wrote:
> On Apr 8, 7:54=A0pm, Markw911 <Markw...@.discussions.microsoft.com>
> wrote:
>
> > I am trying to mimic the layout of an existing report. =A0I had a table =but did
> > not think it could handle some of the more detailed results like:
> > Count column meetingcanceled if meetingcanceled =3D 'yes'
> > I still am not sure how I would handle this in a table:> > Textbox2 =3D =count of all activities where type =3D prospect
> > > > Textbox3 =3D count of all activities where type =3D demo
> > since I am trying to return multiple totals from the same column.
> > From your comments, it sounds like I could have multiple sums per column=
> > with different criteria. =A0If that is true, how do I go about doing it?= =A0Use
> > the activities type column above to give an example if possible.
> > Thanks.
> > "toolman" wrote:
> > > On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> > > wrote:
> > > > My report is a series of textbox fields that all will show data from= an
> > > > Activity table. =A0The report dataset is SELECT * FROM Activity and =is named
> > > > "Activities".
> > > > Textbox1 =3D count of all activities
> > > > Textbox2 =3D count of all activities where type =3D prospect
> > > > Textbox3 =3D count of all activities where type =3D demo
> > > > I know I can create separate datasets for each subsequent query but =since
> > > > they are all queries from the Activity table, I was wondering if I c=ould set
> > > > the value for Textbox2 by querying the dataset named "Activities" in=stead of
> > > > creating a new dataset. =A0For example, SELECT Count(*) FROM Activit=ies Where
> > > > Type =3D 'prospect'
> > > > Thank you,
> > > > Mark
> > > Is there a particular reason you're using a series of textboxes
> > > instead of a table? =A0With a table you could just =A0draw what you ne=ed
> > > directly from your dataset.- Hide quoted text -
> > - Show quoted text -
> You could insert the following expressions into your table cells in
> the table footer row. =A0Hide or remove the detail row.
> Table Header Row: All Activities_____________/
> Prospects______________________________/Demos
> Table Footer =A0Row: =3DCount(Fields!Type.Value) =A0 =3DSUM(IIF(Fields!
> Type.Value =3D "Prospect",1,0)) =3DSUM(IIF(Fields!Type.Value =3D "Demo",
> 1,0))
> OR
> Substitute =3DCOUNT(IIF(Fields!Type.Value =3D "Prospect",Fields!
> Type.Value,Nothing)) for =3DSUM(IIF(Fields!Type.Value =3D "Prospect",1,0))=
> Either expression works
> Good luck- Hide quoted text -
> - Show quoted text -
That's really ugly...
Hopefully, this is more clear
Table Header Row:
Column 1: All Activities Column 2: Prospects Column 3: Demos
Table Footer Row:
Column 1: =3DCount(Fields!Type.Value)
Column 2: =3DSUM(IIF(Fields!
Type.Value =3D "Prospect",1,0))
Column 3: =3DSUM(IIF(Fields!Type.Value =3D "Demo",1,0))