Adding additional lines to the report

Hello,
I've created a report using SQL Server reporting services that looks
approximately like this:
MONTH Non-Operational DRs Operational DRs Monthly Total
01/2006 6 32 38
02/2006 18 25 43
04/2006 19 41 60
05/2006 6 27 33
09/2006 14 14
10/2006 11 5 16
11/2006 2 2
TOTAL 69 153 222
This is my query behind this report (Oracle):
SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
FROM RPT_DRS_BY_MONTH_VU
WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
:pm_lid)
GROUP BY IMPACT, ADDDATE, YEAR, MONTH
ORDER BY YEAR, MONTH
This report doesn't have any data for 03/2006, 06-08/2006. I need to
add these rows with the values 0, so the report would look like this:
MONTH Non-Operational DRs Operational DRs Monthly Total
01/2006 6 32 38
02/2006 18 25 43
03/2006 0 0 0
04/2006 19 41 60
05/2006 6 27 33
06/2006 0 0 0
07/2006 0 0 0
08/2006 0 0 0
09/2006 14 14
10/2006 11 5 16
11/2006 2 2
TOTAL 69 153 222
What would be the best way to do this?
I think I should use a UNION query. I know couple ways to do this (like
creating a table with all values then selecting missing values and
adding them using UNION), but they are bulky and not very efficient.
What would be the best way to achieve this?
I would appreciate your help.
Thank you,
PeterThere are so many ways you can do, you can create a table with all 12 months
in a column and do a outer join with the other table to get even 0 values,
probabily you need to used isnull to make it 0, because it returns null for
values not existing.
Or create a temp table with all 12 months values and insert all the values
you can put this in a stored proc.
These are some of the way
Amarnath
"Peter" wrote:
> Hello,
> I've created a report using SQL Server reporting services that looks
> approximately like this:
> MONTH Non-Operational DRs Operational DRs Monthly Total
> 01/2006 6 32 38
> 02/2006 18 25 43
> 04/2006 19 41 60
> 05/2006 6 27 33
> 09/2006 14 14
> 10/2006 11 5 16
> 11/2006 2 2
> TOTAL 69 153 222
> This is my query behind this report (Oracle):
> SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
> FROM RPT_DRS_BY_MONTH_VU
> WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
> 'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
> LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
> :pm_lid)
> GROUP BY IMPACT, ADDDATE, YEAR, MONTH
> ORDER BY YEAR, MONTH
> This report doesn't have any data for 03/2006, 06-08/2006. I need to
> add these rows with the values 0, so the report would look like this:
> MONTH Non-Operational DRs Operational DRs Monthly Total
> 01/2006 6 32 38
> 02/2006 18 25 43
> 03/2006 0 0 0
> 04/2006 19 41 60
> 05/2006 6 27 33
> 06/2006 0 0 0
> 07/2006 0 0 0
> 08/2006 0 0 0
> 09/2006 14 14
> 10/2006 11 5 16
> 11/2006 2 2
> TOTAL 69 153 222
> What would be the best way to do this?
> I think I should use a UNION query. I know couple ways to do this (like
> creating a table with all values then selecting missing values and
> adding them using UNION), but they are bulky and not very efficient.
> What would be the best way to achieve this?
> I would appreciate your help.
> Thank you,
> Peter
>|||Thank you, Amarnath,
Yes, I think I can. I tried to avoid creating table, but I guess it's
an easiest way.
Peter
Amarnath wrote:
> There are so many ways you can do, you can create a table with all 12 months
> in a column and do a outer join with the other table to get even 0 values,
> probabily you need to used isnull to make it 0, because it returns null for
> values not existing.
> Or create a temp table with all 12 months values and insert all the values
> you can put this in a stored proc.
> These are some of the way
> Amarnath
> "Peter" wrote:
> > Hello,
> >
> > I've created a report using SQL Server reporting services that looks
> > approximately like this:
> >
> > MONTH Non-Operational DRs Operational DRs Monthly Total
> > 01/2006 6 32 38
> > 02/2006 18 25 43
> > 04/2006 19 41 60
> > 05/2006 6 27 33
> > 09/2006 14 14
> > 10/2006 11 5 16
> > 11/2006 2 2
> > TOTAL 69 153 222
> >
> > This is my query behind this report (Oracle):
> >
> > SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
> > FROM RPT_DRS_BY_MONTH_VU
> > WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
> > 'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
> > LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
> > :pm_lid)
> > GROUP BY IMPACT, ADDDATE, YEAR, MONTH
> > ORDER BY YEAR, MONTH
> >
> > This report doesn't have any data for 03/2006, 06-08/2006. I need to
> > add these rows with the values 0, so the report would look like this:
> >
> > MONTH Non-Operational DRs Operational DRs Monthly Total
> > 01/2006 6 32 38
> > 02/2006 18 25 43
> > 03/2006 0 0 0
> > 04/2006 19 41 60
> > 05/2006 6 27 33
> > 06/2006 0 0 0
> > 07/2006 0 0 0
> > 08/2006 0 0 0
> > 09/2006 14 14
> > 10/2006 11 5 16
> > 11/2006 2 2
> > TOTAL 69 153 222
> >
> > What would be the best way to do this?
> >
> > I think I should use a UNION query. I know couple ways to do this (like
> > creating a table with all values then selecting missing values and
> > adding them using UNION), but they are bulky and not very efficient.
> >
> > What would be the best way to achieve this?
> >
> > I would appreciate your help.
> >
> > Thank you,
> >
> > Peter
> >
> >