Sql Activity log backup: lines

Showing posts with label lines. Show all posts

Tuesday, March 27, 2012

Address layout-no blank line

I'm using SQL Server Reporting Services and need to generate a report listing
addresses. I have to accomodate two address lines. How can I set it up so
that if there is no second address no blank line appears on the reportOn Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report
I'm assuming you are using a table data region. Set the Hidden
property for the second line/row to somthing like this:
=IIF(addressline2 = Nothing, True, False)
Make sure you set the property for the row and not individual cells
otherwise it will not close the white space left by the hidden row.
HTH
toolman|||I'm VERY NEW to this product. How do you set up a table data region?
Otherwise your reply makes perfect sense.
Thanks!
"toolman" wrote:
> On Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report
> I'm assuming you are using a table data region. Set the Hidden
> property for the second line/row to somthing like this:
> =IIF(addressline2 = Nothing, True, False)
> Make sure you set the property for the row and not individual cells
> otherwise it will not close the white space left by the hidden row.
> HTH
> toolman
>|||I have figured out how to set up a data table, and placed the iif statement
on the address2 line. But when I preview the report I get this message: The
table is in a list that has no group expressions defined for it. The ous a
data region in a list, the list must have group expressions".
Can you advise me which property sets the group expressions?
Thanks!
"Jeannie" wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report|||On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I have figured out how to set up a data table, and placed the iif statement
> on the address2 line. But when I preview the report I get this message: The
> table is in a list that has no group expressions defined for it. The ous a
> data region in a list, the list must have group expressions".
> Can you advise me which property sets the group expressions?
> Thanks!
>
> "Jeannie" wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report- Hide quoted text -
> - Show quoted text -
Jeanie,
This would be much easier if you based your entire report on a table
as opposed to a bunch of text boxes in a list. In my experience with
reporting, tables are the way to go. If you're not sure whether you
can do this, post an example of how you want the report to look and
maybe I can help.
Assuming we can do this in just a table:
Let's start from a blank slate.
In Layout mode, delete the table, then delete the list so that your
report body is empty.
Now, instead of placing your table inside a list, just place a table
directly into the report body.
Drag and drop your fields from the Datasets pane into the table. You
can add additional rows and columns as necessary.
Once you've got all the fields in place, right click the detail row
handle (gray box with three horizontal lines in it on the far left
side of the table) that contains your second address.
>From the drop down, select Properties to open the Properties pane.
Find and expand the Visibility property by clicking the plus sign
box.
Now click the right side of the Hidden property where it says False.
Then click the drop down arrow and select the <Expression> option.
In the text window of the resulting Edit Expression dialog, overwrite
'False' with the IIF statement. Make sure to include the = sign.
Click OK and then preview the report.
With any kind of luck it should perform as we hope.|||I've managed to create the table, but the blank address lines are still
printing.
Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
True, False). I've attached this to the row property, not the field.
I want the report to look like this
ABC Recycling
555 Adams Drive
Marietta, GA 30303 USA
Since there is no partneraddress2, the line is hidden( There is actually a
partneraddress3, but I assume I would follow the same procedure as address2)
Thanks for your help
Jeannie
"toolman" wrote:
> On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I have figured out how to set up a data table, and placed the iif statement
> > on the address2 line. But when I preview the report I get this message: The
> > table is in a list that has no group expressions defined for it. The ous a
> > data region in a list, the list must have group expressions".
> >
> > Can you advise me which property sets the group expressions?
> > Thanks!
> >
> >
> >
> > "Jeannie" wrote:
> > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > addresses. I have to accomodate two address lines. How can I set it up so
> > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > - Show quoted text -
> Jeanie,
> This would be much easier if you based your entire report on a table
> as opposed to a bunch of text boxes in a list. In my experience with
> reporting, tables are the way to go. If you're not sure whether you
> can do this, post an example of how you want the report to look and
> maybe I can help.
> Assuming we can do this in just a table:
> Let's start from a blank slate.
> In Layout mode, delete the table, then delete the list so that your
> report body is empty.
> Now, instead of placing your table inside a list, just place a table
> directly into the report body.
> Drag and drop your fields from the Datasets pane into the table. You
> can add additional rows and columns as necessary.
> Once you've got all the fields in place, right click the detail row
> handle (gray box with three horizontal lines in it on the far left
> side of the table) that contains your second address.
> >From the drop down, select Properties to open the Properties pane.
> Find and expand the Visibility property by clicking the plus sign
> box.
> Now click the right side of the Hidden property where it says False.
> Then click the drop down arrow and select the <Expression> option.
> In the text window of the resulting Edit Expression dialog, overwrite
> 'False' with the IIF statement. Make sure to include the = sign.
> Click OK and then preview the report.
> With any kind of luck it should perform as we hope.
>|||On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> I've managed to create the table, but the blank address lines are still
> printing.
> Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> True, False). I've attached this to the row property, not the field.
> I want the report to look like this
> ABC Recycling
> 555 Adams Drive
> Marietta, GA 30303 USA
> Since there is no partneraddress2, the line is hidden( There is actually a
> partneraddress3, but I assume I would follow the same procedure as address2)
> Thanks for your help
> Jeannie
>
> "toolman" wrote:
> > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > wrote:
> > > I have figured out how to set up a data table, and placed the iif statement
> > > on the address2 line. But when I preview the report I get this message: The
> > > table is in a list that has no group expressions defined for it. The ous a
> > > data region in a list, the list must have group expressions".
> > > Can you advise me which property sets the group expressions?
> > > Thanks!
> > > "Jeannie" wrote:
> > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> > > - Show quoted text -
> > Jeanie,
> > This would be much easier if you based your entire report on a table
> > as opposed to a bunch of text boxes in a list. In my experience with
> > reporting, tables are the way to go. If you're not sure whether you
> > can do this, post an example of how you want the report to look and
> > maybe I can help.
> > Assuming we can do this in just a table:
> > Let's start from a blank slate.
> > In Layout mode, delete the table, then delete the list so that your
> > report body is empty.
> > Now, instead of placing your table inside a list, just place a table
> > directly into the report body.
> > Drag and drop your fields from the Datasets pane into the table. You
> > can add additional rows and columns as necessary.
> > Once you've got all the fields in place, right click the detail row
> > handle (gray box with three horizontal lines in it on the far left
> > side of the table) that contains your second address.
> > >From the drop down, select Properties to open the Properties pane.
> > Find and expand the Visibility property by clicking the plus sign
> > box.
> > Now click the right side of the Hidden property where it says False.
> > Then click the drop down arrow and select the <Expression> option.
> > In the text window of the resulting Edit Expression dialog, overwrite
> > 'False' with the IIF statement. Make sure to include the = sign.
> > Click OK and then preview the report.
> > With any kind of luck it should perform as we hope.- Hide quoted text -
> - Show quoted text -
Now I'm perplexed. I know it works, having done it in most of my
address list reports. I even copied and pasted your expression
directly into one of my existing reports and it worked (all I changed
was the field name). You might try ="" instead of =Nothing but if
your field is truly empty, this shouldn't make a difference. Beyond
that I'm stumped. Sorry|||Sory about the delay in responding. But i didn't return to the client's site
until today. I am happy to say that with your help, I figured it out. The
reason it didn't work is because the cells, that appeared to be null,
actually had 40 spaces in them! Today I ran a query and looked at the data
and noticed that all the 'empty" cells had a block highlighted when I
positioned my cursor on the cell. So I changed the Iif statement to
acknowledge the spaces and it worked.
Thank you so much for your help!
"toolman" wrote:
> On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> > I've managed to create the table, but the blank address lines are still
> > printing.
> >
> > Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> > True, False). I've attached this to the row property, not the field.
> >
> > I want the report to look like this
> > ABC Recycling
> > 555 Adams Drive
> > Marietta, GA 30303 USA
> >
> > Since there is no partneraddress2, the line is hidden( There is actually a
> > partneraddress3, but I assume I would follow the same procedure as address2)
> >
> > Thanks for your help
> > Jeannie
> >
> >
> >
> > "toolman" wrote:
> > > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > > wrote:
> > > > I have figured out how to set up a data table, and placed the iif statement
> > > > on the address2 line. But when I preview the report I get this message: The
> > > > table is in a list that has no group expressions defined for it. The ous a
> > > > data region in a list, the list must have group expressions".
> >
> > > > Can you advise me which property sets the group expressions?
> > > > Thanks!
> >
> > > > "Jeannie" wrote:
> > > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Jeanie,
> > > This would be much easier if you based your entire report on a table
> > > as opposed to a bunch of text boxes in a list. In my experience with
> > > reporting, tables are the way to go. If you're not sure whether you
> > > can do this, post an example of how you want the report to look and
> > > maybe I can help.
> > > Assuming we can do this in just a table:
> > > Let's start from a blank slate.
> > > In Layout mode, delete the table, then delete the list so that your
> > > report body is empty.
> > > Now, instead of placing your table inside a list, just place a table
> > > directly into the report body.
> > > Drag and drop your fields from the Datasets pane into the table. You
> > > can add additional rows and columns as necessary.
> > > Once you've got all the fields in place, right click the detail row
> > > handle (gray box with three horizontal lines in it on the far left
> > > side of the table) that contains your second address.
> > > >From the drop down, select Properties to open the Properties pane.
> > > Find and expand the Visibility property by clicking the plus sign
> > > box.
> > > Now click the right side of the Hidden property where it says False.
> > > Then click the drop down arrow and select the <Expression> option.
> > > In the text window of the resulting Edit Expression dialog, overwrite
> > > 'False' with the IIF statement. Make sure to include the = sign.
> > > Click OK and then preview the report.
> > > With any kind of luck it should perform as we hope.- Hide quoted text -
> >
> > - Show quoted text -
> Now I'm perplexed. I know it works, having done it in most of my
> address list reports. I even copied and pasted your expression
> directly into one of my existing reports and it worked (all I changed
> was the field name). You might try ="" instead of =Nothing but if
> your field is truly empty, this shouldn't make a difference. Beyond
> that I'm stumped. Sorry
>

Thursday, March 8, 2012

Adding new lines into results in the

I used to be able to enter new lines into the result pane cell for text
(and varchar) data in Enterprise Manager, but now that I am using SQL
2005 Management Studio, this feature is gone.

Is there any way to do this?

Also, copying to/from excel chops off part of the text in a cell and is
very infuriating.

Any help would be appreciated.

Dan(monkeyboydan@.gmail.com) writes:
> I used to be able to enter new lines into the result pane cell for text
> (and varchar) data in Enterprise Manager, but now that I am using SQL
> 2005 Management Studio, this feature is gone.
> Is there any way to do this?
> Also, copying to/from excel chops off part of the text in a cell and is
> very infuriating.

Time to learn to write INSERT and UPDATE statements, I see!

There are plenty of differences between the tools in SQL 2000 and SQL 2005.
Keep in mind that Open table is intended to be a fairly simple tool to
view and edit data. For more heavy-duty stuff, you would use an application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.

Your point is noted and I am quite happy to do insert and update
statements but there are occasions where a little ad-hoc editing and
copying and pasting is useful and this isn't possible any more and it
seems a bit silly 'cause it makes the 'open table' functionality
vritually pointless is a lot of occasions.

Friday, February 24, 2012

Adding extra rows to Matrix

Hello, I am creating a report with a matrix in it, and I need to add a couple
extra lines at the end, so the user can print and add in info as needed. Is
there any way to do this? thanks
ChrisOn Jan 18, 9:55 am, Chris <Ch...@.discussions.microsoft.com> wrote:
> Hello, I am creating a report with a matrix in it, and I need to add a couple
> extra lines at the end, so the user can print and add in info as needed. Is
> there any way to do this? thanks
> Chris
The easiest way to do this is to either add a couple textbox controls
below the matrix control -or- add a table control below the matrix
control. Make sure to not overlap the controls and place the matrix
control and textbox controls/etc inside a rectangle. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique, the problem with that is the matrix is Dynamic, so I don't
know how many columns it is going to be until runtime. If I add textboxes or
a table underneath, the columns will not match up. Any other ideas? Is there
a way to tie the table columns to the columns in the matrix'
"EMartinez" wrote:
> On Jan 18, 9:55 am, Chris <Ch...@.discussions.microsoft.com> wrote:
> > Hello, I am creating a report with a matrix in it, and I need to add a couple
> > extra lines at the end, so the user can print and add in info as needed. Is
> > there any way to do this? thanks
> >
> > Chris
> The easiest way to do this is to either add a couple textbox controls
> below the matrix control -or- add a table control below the matrix
> control. Make sure to not overlap the controls and place the matrix
> control and textbox controls/etc inside a rectangle. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jan 21, 8:10 am, Chris <Ch...@.discussions.microsoft.com> wrote:
> Thanks Enrique, the problem with that is the matrix is Dynamic, so I don't
> know how many columns it is going to be until runtime. If I add textboxes or
> a table underneath, the columns will not match up. Any other ideas? Is there
> a way to tie the table columns to the columns in the matrix'
> "EMartinez" wrote:
> > On Jan 18, 9:55 am, Chris <Ch...@.discussions.microsoft.com> wrote:
> > > Hello, I am creating a report with a matrix in it, and I need to add a couple
> > > extra lines at the end, so the user can print and add in info as needed. Is
> > > there any way to do this? thanks
> > > Chris
> > The easiest way to do this is to either add a couple textbox controls
> > below the matrix control -or- add a table control below the matrix
> > control. Make sure to not overlap the controls and place the matrix
> > control and textbox controls/etc inside a rectangle. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Not unless you use a second matrix control below the
first one that is tied to the same dataset and hide the data. Hope
this helps further.
Regards,
Enrique Martinez
Sr. Software Consultant

Monday, February 13, 2012

Adding blank lines to a report -2nd request

I am trying to add additional blank lines to a report based on how much room
is left on a page. If there are 6 lines of datarows, I need there to be 15
lines of blank lines. if there are 15 lines of datarows I need 6 blank lines
added.
I am currenly using a table object with a static number of header rows with
the bottom lined. Any suggestions would be appreciated.Hello Eric,
I would like to suggest you to add another table under your table.
Also, you need to add a dataset which contain 21 lines records. And you
could use a filter to control how many rows to show.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||That is how I am currently doing the extra lines I have now. I have a table
that is simply all header rows. But I havent figured out how to do a
'filter' when there is not a dataset attached to it.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:XQ4UvMzHHHA.1220@.TK2MSFTNGHUB02.phx.gbl...
> Hello Eric,
> I would like to suggest you to add another table under your table.
> Also, you need to add a dataset which contain 21 lines records. And you
> could use a filter to control how many rows to show.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Eric,
You could create a table with just one column "ID"
Then, you could insert just 21 rows in the table.
After that, you could use this statement to get the new dataset which
contain only 2 column:
SELECT id, ' ' as Text from <table name>
The filter could be setted like this:
ID <= 21-Rownumber('your first dataset name')
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||You don't even need a table. Have your dataset be on generic designer mode
(button is to the right of the ...) and use a Union statement
select 1 as ID, ' ' as Text union
select 2 as ID, ' ' as Text union
select 3 as ID, ' ' as Text union
select 4 as ID, ' ' as Text union
select 5 as ID, ' ' as Text union
select 6 as ID, ' ' as Text union
.
.
.
select 25 as ID,' ' as Text
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:oZZxARBIHHA.2024@.TK2MSFTNGHUB02.phx.gbl...
> Hello Eric,
> You could create a table with just one column "ID"
> Then, you could insert just 21 rows in the table.
> After that, you could use this statement to get the new dataset which
> contain only 2 column:
> SELECT id, ' ' as Text from <table name>
> The filter could be setted like this:
> ID <= 21-Rownumber('your first dataset name')
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Bruce,
Yes, you could use the union in the dataset.
The reason why I recommand the table is for some performance concern and
more efficant management.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, February 9, 2012

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
> >
> >