May 23, 2011 at 5:02 am
Hi,
Greetings !
I have a task to create a chart report in SSRS and for that I have to include a count of a field(CREATE_OFFICE_TEAM_CD) and CREATE_DT field within one select statement and then do a group by with CREATE_OFFICE_TEAM_CD.
this is what I need to achieve:
-------------------------------------------------------------------------------------
Select count(CREATE_OFFICE_TEAM_CD), CREATE_DT as create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
group by CREATE_OFFICE_TEAM_CD
-------------------------------------------------------------------------------------
however, i get an error when i try to run the above sql query:
'Error2[rsFieldReference] The FontFamily expression for the chart ‘chart1’ refers to the field ‘CREATE_OFFICE_TEAM_CD’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.'
Could someone please help me in this regard ?
Thanks and Regards,
Paul
May 23, 2011 at 6:50 am
The issue seems to be that you are grouping by the field which shows the aggregated value
Try Select count(CREATE_OFFICE_TEAM_CD), CREATE_DT as create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
group by CREATE_DT
If you want to see the count for each CREATE_DT
or
Select CREATE_OFFICE_TEAM_CD, COUNT(CREATE_DT) as create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
group by CREATE_OFFICE_TEAM_CD
If you want to see the total CREATE_DT for each OFFICE_TEAM
May 23, 2011 at 7:24 am
Davin21 (5/23/2011)
The issue seems to be that you are grouping by the field which shows the aggregated valueTry Select count(CREATE_OFFICE_TEAM_CD), CREATE_DT as create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
group by CREATE_DT
If you want to see the count for each CREATE_DT
or
Select CREATE_OFFICE_TEAM_CD, COUNT(CREATE_DT) as create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
group by CREATE_OFFICE_TEAM_CD
If you want to see the total CREATE_DT for each OFFICE_TEAM
Thanks a lot for your reply. However, what I wish to achieve is count of CREATE_OFFICE_TEAM_CD and also group by CREATE_OFFICE_TEAM_CD. Is that possible to accomplish ? I have tried with MIN(create_dt) but it doesnt get the desired result. Do you have any other idea ?
Many thanks,
Paul
May 23, 2011 at 7:40 am
Please read this link[/url] for how to best get answers to your questions.
If you set-up and post create table scripts plus sample data and expected results then you'll get back tested correct solutions.
Thanks
May 23, 2011 at 9:41 am
WIthout seeing some sample data, I would guess that something like this would work then:
Select CREATE_OFFICE_TEAM_CD, COUNT(*) as number_of_office_team from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
group by CREATE_OFFICE_TEAM_CD
If not, as said before, sample data should make it a bit clearer!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply