Hi,
I am trying to create a drilthrough report in report designer. I know
ho to do it if I have a single query but I do not know how to do it if
I have multiple. What is happening is that I tried to create a signle
query where I can then create a groupings and then have the report
drill through the groupings but while trying to create my query I
found that I could not create the query that I wanted being that I was
getting duplicate results in my comments column (I wanted to also add
the comments). If the user did not make a comment then it was
automatically added with the first comment made in the for that
grouping.
So I decided to make two queries. One is for selecting all of the
pending items (workflow based system) and the other for all of the
comments made for each workflow (for this report they are called
ECO). The queries work just fine if they run seperatly in the report,
but if I choose to group them so that there is a expand or cllapse
button under the user then I get the following errors:
[rsPostSortAggregateInGroupFilterExpression] A grouping filter
expression for the table =18table1=19 uses aggregate function First, Last
or Previous. These aggregate functions cannot be used in grouping
filters.
[rsAggregateInGroupExpression] A group expression for the table
'table1' includes an aggregate function. Aggregate functions cannot
be used in group expressions.
Build complete -- 2 errors, 0 warnings
The following are my new datasets:
-- dataset 1 selecting all of the ECOs that are in pending status
SELECT dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
dbo.ChangeEntry.Number as [ECO Number],
dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [Full
Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
UserProfile_1.Email AS [Initiator's Email],
dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
dbo.ChangeSignoff.StageOrder,
dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
FROM dbo.ChangeEntry INNER JOIN
dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
INNER JOIN
dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
INNER JOIN
dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
JOIN
dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
JOIN
dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
UserProfile_1.ID INNER JOIN
dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
JOIN
dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
WHERE dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D 1
AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
-- dataset 2 for selecting all of the comments made for each ECO in
pending status
SELECT ChangeType.Type, SignoffStages.StageName AS [Stage Name],
ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
UserProfile.FullName AS [Full Name],
UserProfile.Email, UserProfile_1.FullName AS Initiator,
UserProfile_1.Email AS [Initiator's Email],
ChangeEntry.RaisedOn AS Initiated,
ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
ChangeSignoff.DateSigned, ChangeEntry.Reason,
ChangeEntry.Description, ChangeStatus.Status,
ChangeComments.Comment
FROM ChangeEntry INNER JOIN
ChangePriority ON ChangeEntry.Priority =3D
ChangePriority.ID INNER JOIN
ChangeSignoff ON ChangeEntry.ID =3D
ChangeSignoff.ECOID INNER JOIN
UserProfile ON ChangeSignoff.UserID =3D
UserProfile.ID INNER JOIN
ChangeType ON ChangeEntry.ChangeType =3D
ChangeType.ID INNER JOIN
UserProfile AS UserProfile_1 ON
ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
SignoffStages ON ChangeSignoff.StageID =3D
SignoffStages.ID INNER JOIN
ChangeStatus ON ChangeEntry.Status =3D
ChangeStatus.ID INNER JOIN
ChangeComments ON dbo.ChangeSignoff.ECOID =3D
dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
ChangeComments.UserID
WHERE dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D 1
AND
ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
Thank you for all of your help in advanceOn Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> Hi,
> I am trying to create a drilthrough report in report designer. =A0I know
> ho to do it if I have a single query but I do not know how to do it if
> I have multiple. =A0What is happening is that I tried to create a signle
> query where I can then create a groupings and then have the report
> drill through the groupings but while trying to create my query I
> found that I could not create the query that I wanted being that I was
> getting duplicate results in my comments column (I wanted to also add
> the comments). =A0If the user did not make a comment then it was
> automatically added with the first comment made in the for that
> grouping.
> So I decided to make two queries. =A0One is for selecting all of the
> pending items (workflow based system) and the other for all of the
> comments made for each workflow (for this report they are called
> ECO). =A0The queries work just fine if they run seperatly in the report,
> but if I choose to group them so that there is a expand or cllapse
> button under the user then I get the following errors:
> [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> expression for the table =A0table1 =A0uses aggregate function First, Last
> or Previous. =A0These aggregate functions cannot be used in grouping
> filters.
> [rsAggregateInGroupExpression] A group expression for the table
> 'table1' includes an aggregate function. =A0Aggregate functions cannot
> be used in group expressions.
> Build complete -- 2 errors, 0 warnings
> The following are my new datasets:
> -- dataset 1 selecting all of the ECOs that are in pending status
> SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> dbo.ChangeEntry.Number as [ECO Number],
> =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [=Full
> Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> dbo.ChangeSignoff.StageOrder,
> =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> FROM =A0dbo.ChangeEntry INNER JOIN
> dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
> INNER JOIN
> dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> INNER JOIN
> dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
> JOIN
> dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
> JOIN
> dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> UserProfile_1.ID INNER JOIN
> dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> JOIN
> dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> -- dataset 2 for selecting all of the comments made for each ECO in
> pending status
> SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],
> ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Full =Name],
> UserProfile.Email, UserProfile_1.FullName AS Initiator,
> UserProfile_1.Email AS [Initiator's Email],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Initia=ted,
> ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> ChangeSignoff.DateSigned, ChangeEntry.Reason,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Chang=eStatus.Status,
> ChangeComments.Comment
> FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntry.=Priority =3D
> ChangePriority.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry.I=D =3D
> ChangeSignoff.ECOID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff.U=serID =3D
> UserProfile.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Chan=geType =3D
> ChangeType.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1 O=N
> ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSignoff=.StageID =3D
> SignoffStages.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.St=atus =3D
> ChangeStatus.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.ChangeSi=gnoff.ECOID =3D
> dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> ChangeComments.UserID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND
> ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> Thank you for all of your help in advance
just an idea but unsure how to do it
I was wonder if I could use expressions in the second query that will
link each comment to the user
My report design is basically
1st row =3D header
2nd row =3D grouping it just shows the ECO number and when you click on
the + it show the user title, username and the date they signed the
ECO (if they signed it) - This works
3rd row =3D grouping: it shows the comments when you click on the user
cell (+ symbol). right now it will show the first comment on all
because it does not know where they go. This is the second query
I am not sure how I can link thw 3rd row with the user. I figure
maybe I could use an expression but I am not sure how to do it. I am
new to report builder
Thank you for all of your help|||On Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> Hi,
> I am trying to create a drilthrough report in report designer. =A0I know
> ho to do it if I have a single query but I do not know how to do it if
> I have multiple. =A0What is happening is that I tried to create a signle
> query where I can then create a groupings and then have the report
> drill through the groupings but while trying to create my query I
> found that I could not create the query that I wanted being that I was
> getting duplicate results in my comments column (I wanted to also add
> the comments). =A0If the user did not make a comment then it was
> automatically added with the first comment made in the for that
> grouping.
> So I decided to make two queries. =A0One is for selecting all of the
> pending items (workflow based system) and the other for all of the
> comments made for each workflow (for this report they are called
> ECO). =A0The queries work just fine if they run seperatly in the report,
> but if I choose to group them so that there is a expand or cllapse
> button under the user then I get the following errors:
> [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> expression for the table =A0table1 =A0uses aggregate function First, Last
> or Previous. =A0These aggregate functions cannot be used in grouping
> filters.
> [rsAggregateInGroupExpression] A group expression for the table
> 'table1' includes an aggregate function. =A0Aggregate functions cannot
> be used in group expressions.
> Build complete -- 2 errors, 0 warnings
> The following are my new datasets:
> -- dataset 1 selecting all of the ECOs that are in pending status
> SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> dbo.ChangeEntry.Number as [ECO Number],
> =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [=Full
> Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> dbo.ChangeSignoff.StageOrder,
> =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> FROM =A0dbo.ChangeEntry INNER JOIN
> dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
> INNER JOIN
> dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> INNER JOIN
> dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
> JOIN
> dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
> JOIN
> dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> UserProfile_1.ID INNER JOIN
> dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> JOIN
> dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> -- dataset 2 for selecting all of the comments made for each ECO in
> pending status
> SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],
> ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Full =Name],
> UserProfile.Email, UserProfile_1.FullName AS Initiator,
> UserProfile_1.Email AS [Initiator's Email],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Initia=ted,
> ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> ChangeSignoff.DateSigned, ChangeEntry.Reason,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Chang=eStatus.Status,
> ChangeComments.Comment
> FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntry.=Priority =3D
> ChangePriority.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry.I=D =3D
> ChangeSignoff.ECOID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff.U=serID =3D
> UserProfile.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Chan=geType =3D
> ChangeType.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1 O=N
> ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSignoff=.StageID =3D
> SignoffStages.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.St=atus =3D
> ChangeStatus.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.ChangeSi=gnoff.ECOID =3D
> dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> ChangeComments.UserID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND
> ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> Thank you for all of your help in advance
I was wonder if I could use expressions in the second query that will
link each comment to the user
My report design is basically
1st row =3D header
2nd row =3D grouping it just shows the ECO number and when you click on
the + it show the user title, username and the date they signed the
ECO (if they signed it) - This works
3rd row =3D grouping: it shows the comments when you click on the user
cell (+ symbol). right now it will show the first comment on all
because it does not know where they go. This is the second query
I am not sure how I can link thw 3rd row with the user. I figure
maybe I could use an expression but I am not sure how to do it. I am
new to report builder
Thank you for all of your help|||On Apr 28, 10:36=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> On Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
>
>
> > Hi,
> > I am trying to create a drilthrough report in report designer. =A0I know=
> > ho to do it if I have a single query but I do not know how to do it if
> > I have multiple. =A0What is happening is that I tried to create a signle=
> > query where I can then create a groupings and then have the report
> > drill through the groupings but while trying to create my query I
> > found that I could not create the query that I wanted being that I was
> > getting duplicate results in my comments column (I wanted to also add
> > the comments). =A0If the user did not make a comment then it was
> > automatically added with the first comment made in the for that
> > grouping.
> > So I decided to make two queries. =A0One is for selecting all of the
> > pending items (workflow based system) and the other for all of the
> > comments made for each workflow (for this report they are called
> > ECO). =A0The queries work just fine if they run seperatly in the report,=
> > but if I choose to group them so that there is a expand or cllapse
> > button under the user then I get the following errors:
> > [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> > expression for the table =A0table1 =A0uses aggregate function First, Las=t
> > or Previous. =A0These aggregate functions cannot be used in grouping
> > filters.
> > [rsAggregateInGroupExpression] A group expression for the table
> > 'table1' includes an aggregate function. =A0Aggregate functions cannot
> > be used in group expressions.
> > Build complete -- 2 errors, 0 warnings
> > The following are my new datasets:
> > -- dataset 1 selecting all of the ECOs that are in pending status
> > SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> > dbo.ChangeEntry.Number as [ECO Number],
> > =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as= [Full
> > Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> > =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> > dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> > dbo.ChangeSignoff.StageOrder,
> > =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> > dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> > FROM =A0dbo.ChangeEntry INNER JOIN
> > dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID=
> > INNER JOIN
> > dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> > INNER JOIN
> > dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER=
> > JOIN
> > dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER=
> > JOIN
> > dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> > UserProfile_1.ID INNER JOIN
> > dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> > JOIN
> > dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> > WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required ==3D 1
> > AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> > ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> > -- dataset 2 for selecting all of the comments made for each ECO in
> > pending status
> > SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],=
> > ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Ful=l Name],
> > UserProfile.Email, UserProfile_1.FullName AS Initiator,
> > UserProfile_1.Email AS [Initiator's Email],
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Init=iated,
> > ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> > ChangeSignoff.DateSigned, ChangeEntry.Reason,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Cha=ngeStatus.Status,
> > ChangeComments.Comment
> > FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntr=y.Priority =3D
> > ChangePriority.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry=.ID =3D
> > ChangeSignoff.ECOID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff=.UserID =3D
> > UserProfile.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Ch=angeType =3D
> > ChangeType.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1= ON
> > ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSigno=ff.StageID =3D
> > SignoffStages.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.=Status =3D
> > ChangeStatus.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.Change=Signoff.ECOID =3D
> > dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> > ChangeComments.UserID
> > WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required ==3D 1
> > AND
> > ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> > ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> > Thank you for all of your help in advance
> just an idea but unsure how to do it
> I was wonder if I could use expressions in the second query that will
> link each comment to the user
> My report design is basically
> 1st row =3D header
> 2nd row =3D grouping it just shows the ECO number and when you click on
> the + it show the user title, username and the date they signed the
> ECO (if they signed it) - This works
> 3rd row =3D =A0grouping: it shows the comments when you click on the user
> cell (+ symbol). right now it will show the first comment on all
> because it does not know where they go. This is the second query
> I am not sure how I can link thw 3rd row with the user. =A0I figure
> maybe I could use an expression but I am not sure how to do it. =A0I am
> new to report builder
> Thank you for all of your help- Hide quoted text -
> - Show quoted text -
OK, well I figured it out......
I figured out that you cannot have a report utilize the different
datasets and link them together in a grouping (creating a drillthough
report). Instead what you need to do is create another report that
utilizes the same datasource or a datasource that has data that can
relate the datasource on the main report and set parameters in it. In
the layout view just use textboxes and drag the operated field. From
there you go back to the main report and drag the subreport control in
one of the cells and click properties. Select the second report that
you created and then click the parameters tab. In the parameter name
section use the dropdown menu and the select the parameters (the
dropdown menu will auto fill with the parameters from the subreport).
In the parameters value field select the column that you want to pass
to the subreport.
=46rom there is it all the same for creating a drillthough report
Everything should work out perfectly; although there migh be another
way.
Thank You
No comments:
Post a Comment