I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..
simple sort the data by company name|||I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..
Just sort the data by company name|||what do you mean by sort data by company name?
Showing posts with label display. Show all posts
Showing posts with label display. Show all posts
Tuesday, March 27, 2012
change HH from 24 hrs to 12 hrs
I have a time value that I want to display in 12 hours instead of 24
hours(military time)
DATEPART(HH,@.ACTTIME)
How do I get 1 - 12 and instead of 0-23?Client side? Wouldn't it be nicer to let the end user choose how he/she
prefers to read the time?
Otherwise lookup the style parameter options on the CONVERT function.
David Portas
SQL Server MVP
--|||I hope the following helps you.
Declare @.ActTime DateTime
Set @.ActTime = '01/01/2005 11:15 PM'
Select Case
When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
ELSE DatePart(hh,@.ACTTIME) - 12
End
"LP" wrote:
> I have a time value that I want to display in 12 hours instead of 24
> hours(military time)
> DATEPART(HH,@.ACTTIME)
> How do I get 1 - 12 and instead of 0-23?
>|||It is called ISO-8601 and not "Military Time" and it is used in a ton
of ISO standards. Why do you wish to destroy the portability of your
data and violate standards?
The basic priniciple of a tiered architecture is that the applications
do the display work, not the database.|||No server side. It's for a static dimension table. I don't see the utility
in it but the client request pays the bills. I have most of it compete.
I'll include it tomorrow.
Thanks
"David Portas" wrote:
> Client side? Wouldn't it be nicer to let the end user choose how he/she
> prefers to read the time?
> Otherwise lookup the style parameter options on the CONVERT function.
> --
> David Portas
> SQL Server MVP
> --
>
>|||No destruction in mind. Simply fulfilling a client request for a static
dimension table. The utility of this field is a bit in question...but it
pays the bills.
Thanks
"--CELKO--" wrote:
> It is called ISO-8601 and not "Military Time" and it is used in a ton
> of ISO standards. Why do you wish to destroy the portability of your
> data and violate standards?
> The basic priniciple of a tiered architecture is that the applications
> do the display work, not the database.
>|||Thanks for getting back with me. I found another way around the issue and
I'll post it tomotrrow because it might be helpfull for others.
Thanks again.
LP
"satheeshks" wrote:
> I hope the following helps you.
> Declare @.ActTime DateTime
> Set @.ActTime = '01/01/2005 11:15 PM'
> Select Case
> When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
> ELSE DatePart(hh,@.ACTTIME) - 12
> End
> "LP" wrote:
>
hours(military time)
DATEPART(HH,@.ACTTIME)
How do I get 1 - 12 and instead of 0-23?Client side? Wouldn't it be nicer to let the end user choose how he/she
prefers to read the time?
Otherwise lookup the style parameter options on the CONVERT function.
David Portas
SQL Server MVP
--|||I hope the following helps you.
Declare @.ActTime DateTime
Set @.ActTime = '01/01/2005 11:15 PM'
Select Case
When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
ELSE DatePart(hh,@.ACTTIME) - 12
End
"LP" wrote:
> I have a time value that I want to display in 12 hours instead of 24
> hours(military time)
> DATEPART(HH,@.ACTTIME)
> How do I get 1 - 12 and instead of 0-23?
>|||It is called ISO-8601 and not "Military Time" and it is used in a ton
of ISO standards. Why do you wish to destroy the portability of your
data and violate standards?
The basic priniciple of a tiered architecture is that the applications
do the display work, not the database.|||No server side. It's for a static dimension table. I don't see the utility
in it but the client request pays the bills. I have most of it compete.
I'll include it tomorrow.
Thanks
"David Portas" wrote:
> Client side? Wouldn't it be nicer to let the end user choose how he/she
> prefers to read the time?
> Otherwise lookup the style parameter options on the CONVERT function.
> --
> David Portas
> SQL Server MVP
> --
>
>|||No destruction in mind. Simply fulfilling a client request for a static
dimension table. The utility of this field is a bit in question...but it
pays the bills.
Thanks
"--CELKO--" wrote:
> It is called ISO-8601 and not "Military Time" and it is used in a ton
> of ISO standards. Why do you wish to destroy the portability of your
> data and violate standards?
> The basic priniciple of a tiered architecture is that the applications
> do the display work, not the database.
>|||Thanks for getting back with me. I found another way around the issue and
I'll post it tomotrrow because it might be helpfull for others.
Thanks again.
LP
"satheeshks" wrote:
> I hope the following helps you.
> Declare @.ActTime DateTime
> Set @.ActTime = '01/01/2005 11:15 PM'
> Select Case
> When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
> ELSE DatePart(hh,@.ACTTIME) - 12
> End
> "LP" wrote:
>
Sunday, March 25, 2012
Change display of subtotal row in Matrix report
I'd like to display the subtotal and Grand total rows of my Matrix report in
grey, but I can't find a proprties box to change. I've changed the area
that displays the text ie. "Grand Total", but there are not separate boxes
for the subtotal values like you'd see with a table. I also seem to be
stuck with my subtotal text in the second column when I'd like to print it in
the first column. I'd like to see
SubTotal xxxxx
Grand Total
I can't define in the same box.
I'm really "winging it" with this Matrix report. All my other report
experiences have been with tables.
Thank you in advance for any help you can provide.... PBOn Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I'd like to display the subtotal and Grand total rows of my Matrix report in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB
In the Layout view/tab, click on the matrix cell that has the
subtotals/grandtotal and select F4 (to get the Properties window).
Below Appearance, to the right of BackgroundColor select
'<Expressions...>' from the drop-down list and enter something like
the following:
=iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value ="Grand Total", "LightGrey", "White")
Where LightGrey is the value if true and White is the color otherwise.
If you create the subtotal in your stored procedure or query that
sources the report, you can set the column using an expression
(similar to above); however, I believe. if you use the one created in
the report, it will force you to a specific column. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. You have been a great help! I have 60 Finance reports to be
created with Visual Studio and no formal training. I am so grateful for
your answers to my posts! Can you recommend any other resources/discussion
groups that would be helpful? I did purchase the book "Hitchhikers Guide to
SQL Server 2000 Reporting Services". It has been helpful with the basics,
but not with the more kind of detailed "coding" that you have been helping me
with. I'd love to have a reference for creating "code" (I've created some
.code to check for division by zero, but it's been copied from other
references) and for the ".expressions" that can be used with a field names
(ie. .substring, .value, etc.)
Regards,
PB
"EMartinez" wrote:
> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I'd like to display the subtotal and Grand total rows of my Matrix report in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
> In the Layout view/tab, click on the matrix cell that has the
> subtotals/grandtotal and select F4 (to get the Properties window).
> Below Appearance, to the right of BackgroundColor select
> '<Expressions...>' from the drop-down list and enter something like
> the following:
> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => "Grand Total", "LightGrey", "White")
> Where LightGrey is the value if true and White is the color otherwise.
> If you create the subtotal in your stored procedure or query that
> sources the report, you can set the column using an expression
> (similar to above); however, I believe. if you use the one created in
> the report, it will force you to a specific column. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Mr Martinez will probably give you some more tips, but I just want to add a
link to William Pearson's articles on Reporting Serivces
http://www.databasejournal.com/article.php/1459531
You need to scroll down almost to the bottom, but there you find 38
tutorials. A lot of them are focused on OLAP based reports, but most of the
things you can do with OLAP based reports you can do with database based
reports as well.
Direct Reports (Brian Welcker's Weblog) at
http://blogs.msdn.com/bwelcker/default.aspx is good too. I also read Bob's
SQL Reporting Services Blog at http://blogs.msdn.com/bobmeyers/default.aspx
As for discussion groups, I usually end up with this news group, either
following it through a news reader, or searching with Google groups.
Can't give you a lot of tips on the code bit, though, haven't done too much
of that myself.
Kaisa M. Lindahl Lervik
http://blogs.spipp.net/blogs/kaisa
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> Thank you. You have been a great help! I have 60 Finance reports to be
> created with Visual Studio and no formal training. I am so grateful for
> your answers to my posts! Can you recommend any other
> resources/discussion
> groups that would be helpful? I did purchase the book "Hitchhikers Guide
> to
> SQL Server 2000 Reporting Services". It has been helpful with the basics,
> but not with the more kind of detailed "coding" that you have been helping
> me
> with. I'd love to have a reference for creating "code" (I've created
> some
> .code to check for division by zero, but it's been copied from other
> references) and for the ".expressions" that can be used with a field names
> (ie. .substring, .value, etc.)
> Regards,
> PB
>
> "EMartinez" wrote:
>> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>> In the Layout view/tab, click on the matrix cell that has the
>> subtotals/grandtotal and select F4 (to get the Properties window).
>> Below Appearance, to the right of BackgroundColor select
>> '<Expressions...>' from the drop-down list and enter something like
>> the following:
>> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value =>> "Grand Total", "LightGrey", "White")
>> Where LightGrey is the value if true and White is the color otherwise.
>> If you create the subtotal in your stored procedure or query that
>> sources the report, you can set the column using an expression
>> (similar to above); however, I believe. if you use the one created in
>> the report, it will force you to a specific column. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>|||On Mar 14, 8:04 am, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> Mr Martinez will probably give you some more tips, but I just want to add a
> link to William Pearson's articles on Reporting Serivces
> http://www.databasejournal.com/article.php/1459531
> You need to scroll down almost to the bottom, but there you find 38
> tutorials. A lot of them are focused on OLAP based reports, but most of the
> things you can do with OLAP based reports you can do with database based
> reports as well.
> Direct Reports (Brian Welcker's Weblog) athttp://blogs.msdn.com/bwelcker/default.aspxis good too. I also read Bob's
> SQL Reporting Services Blog athttp://blogs.msdn.com/bobmeyers/default.aspx
> As for discussion groups, I usually end up with this news group, either
> following it through a news reader, or searching with Google groups.
> Can't give you a lot of tips on the code bit, though, haven't done too much
> of that myself.
> Kaisa M. Lindahl Lervikhttp://blogs.spipp.net/blogs/kaisa
> "ppbedz" <ppb...@.discussions.microsoft.com> wrote in message
> news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> > Thank you. You have been a great help! I have 60 Finance reports to be
> > created with Visual Studio and no formal training. I am so grateful for
> > your answers to my posts! Can you recommend any other
> > resources/discussion
> > groups that would be helpful? I did purchase the book "Hitchhikers Guide
> > to
> > SQL Server 2000 Reporting Services". It has been helpful with the basics,
> > but not with the more kind of detailed "coding" that you have been helping
> > me
> > with. I'd love to have a reference for creating "code" (I've created
> > some
> > .code to check for division by zero, but it's been copied from other
> > references) and for the ".expressions" that can be used with a field names
> > (ie. .substring, .value, etc.)
> > Regards,
> > PB
> > "EMartinez" wrote:
> >> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> >> > I'd like to display the subtotal and Grand total rows of my Matrix
> >> > report in
> >> > grey, but I can't find a proprties box to change. I've changed the
> >> > area
> >> > that displays the text ie. "Grand Total", but there are not separate
> >> > boxes
> >> > for the subtotal values like you'd see with a table. I also seem to
> >> > be
> >> > stuck with my subtotal text in the second column when I'd like to print
> >> > it in
> >> > the first column. I'd like to see
> >> > SubTotal xxxxx
> >> > Grand Total
> >> > I can't define in the same box.
> >> > I'm really "winging it" with this Matrix report. All my other report
> >> > experiences have been with tables.
> >> > Thank you in advance for any help you can provide.... PB
> >> In the Layout view/tab, click on the matrix cell that has the
> >> subtotals/grandtotal and select F4 (to get the Properties window).
> >> Below Appearance, to the right of BackgroundColor select
> >> '<Expressions...>' from the drop-down list and enter something like
> >> the following:
> >> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => >> "Grand Total", "LightGrey", "White")
> >> Where LightGrey is the value if true and White is the color otherwise.
> >> If you create the subtotal in your stored procedure or query that
> >> sources the report, you can set the column using an expression
> >> (similar to above); however, I believe. if you use the one created in
> >> the report, it will force you to a specific column. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
You are very welcome. Glad to be of assistance. I can email you a kind
of 'how tos' list (that I have created along the way), if you would
like. This news group is the most active one (for Reporting Services),
so it usually gets the most responses and assistance. A couple of
lower activity ones are: http://groups.google.com/group/ReportingServices
and http://groups.google.com/group/RS2005. I tend to help out on those
as well. These Microsoft links are pretty handy (but not too easy to
navigate): http://msdn2.microsoft.com/en-us/library/ms159106.aspx and
http://msdn2.microsoft.com/en-us/library/ms170246.aspx. Also, if you
know of anyone that would be interested, I will be offering a SQL
Server 2005 Reporting Services class in the Nashville, TN area
starting the end of April.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi all!
I'm using the function SubToal built in RS.
How can I change the row's background for the row's handling the subtotals
infos?
Is there a way to find the value of a textboxX instead of the value of a
field?
Thanks, Julien
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> I'd like to display the subtotal and Grand total rows of my Matrix report
> in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate
> boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it
> in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB|||Hi Julien,
I had the same problem and I solved it using the InScope() function.
You will find an example on this blog :
http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
Best regards,
Jean-Baptiste
"Julien Bonnier" wrote:
> Hi all!
> I'm using the function SubToal built in RS.
> How can I change the row's background for the row's handling the subtotals
> infos?
> Is there a way to find the value of a textboxX instead of the value of a
> field?
> Thanks, Julien
>
> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> > I'd like to display the subtotal and Grand total rows of my Matrix report
> > in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate
> > boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it
> > in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
>
>|||FABOULOUS!!!
Thanks alot! I'm gonna add this to all my reports!
Thanks again!
Julien
"jbb92" <jbb92@.discussions.microsoft.com> wrote in message
news:905BD939-9C3E-4EBE-B002-7FFBC064752B@.microsoft.com...
> Hi Julien,
> I had the same problem and I solved it using the InScope() function.
> You will find an example on this blog :
> http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
> Best regards,
> Jean-Baptiste
> "Julien Bonnier" wrote:
>> Hi all!
>> I'm using the function SubToal built in RS.
>> How can I change the row's background for the row's handling the
>> subtotals
>> infos?
>> Is there a way to find the value of a textboxX instead of the value of a
>> field?
>> Thanks, Julien
>>
>> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
>> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report
>> > in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it
>> > in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>>
grey, but I can't find a proprties box to change. I've changed the area
that displays the text ie. "Grand Total", but there are not separate boxes
for the subtotal values like you'd see with a table. I also seem to be
stuck with my subtotal text in the second column when I'd like to print it in
the first column. I'd like to see
SubTotal xxxxx
Grand Total
I can't define in the same box.
I'm really "winging it" with this Matrix report. All my other report
experiences have been with tables.
Thank you in advance for any help you can provide.... PBOn Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I'd like to display the subtotal and Grand total rows of my Matrix report in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB
In the Layout view/tab, click on the matrix cell that has the
subtotals/grandtotal and select F4 (to get the Properties window).
Below Appearance, to the right of BackgroundColor select
'<Expressions...>' from the drop-down list and enter something like
the following:
=iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value ="Grand Total", "LightGrey", "White")
Where LightGrey is the value if true and White is the color otherwise.
If you create the subtotal in your stored procedure or query that
sources the report, you can set the column using an expression
(similar to above); however, I believe. if you use the one created in
the report, it will force you to a specific column. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. You have been a great help! I have 60 Finance reports to be
created with Visual Studio and no formal training. I am so grateful for
your answers to my posts! Can you recommend any other resources/discussion
groups that would be helpful? I did purchase the book "Hitchhikers Guide to
SQL Server 2000 Reporting Services". It has been helpful with the basics,
but not with the more kind of detailed "coding" that you have been helping me
with. I'd love to have a reference for creating "code" (I've created some
.code to check for division by zero, but it's been copied from other
references) and for the ".expressions" that can be used with a field names
(ie. .substring, .value, etc.)
Regards,
PB
"EMartinez" wrote:
> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I'd like to display the subtotal and Grand total rows of my Matrix report in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
> In the Layout view/tab, click on the matrix cell that has the
> subtotals/grandtotal and select F4 (to get the Properties window).
> Below Appearance, to the right of BackgroundColor select
> '<Expressions...>' from the drop-down list and enter something like
> the following:
> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => "Grand Total", "LightGrey", "White")
> Where LightGrey is the value if true and White is the color otherwise.
> If you create the subtotal in your stored procedure or query that
> sources the report, you can set the column using an expression
> (similar to above); however, I believe. if you use the one created in
> the report, it will force you to a specific column. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Mr Martinez will probably give you some more tips, but I just want to add a
link to William Pearson's articles on Reporting Serivces
http://www.databasejournal.com/article.php/1459531
You need to scroll down almost to the bottom, but there you find 38
tutorials. A lot of them are focused on OLAP based reports, but most of the
things you can do with OLAP based reports you can do with database based
reports as well.
Direct Reports (Brian Welcker's Weblog) at
http://blogs.msdn.com/bwelcker/default.aspx is good too. I also read Bob's
SQL Reporting Services Blog at http://blogs.msdn.com/bobmeyers/default.aspx
As for discussion groups, I usually end up with this news group, either
following it through a news reader, or searching with Google groups.
Can't give you a lot of tips on the code bit, though, haven't done too much
of that myself.
Kaisa M. Lindahl Lervik
http://blogs.spipp.net/blogs/kaisa
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> Thank you. You have been a great help! I have 60 Finance reports to be
> created with Visual Studio and no formal training. I am so grateful for
> your answers to my posts! Can you recommend any other
> resources/discussion
> groups that would be helpful? I did purchase the book "Hitchhikers Guide
> to
> SQL Server 2000 Reporting Services". It has been helpful with the basics,
> but not with the more kind of detailed "coding" that you have been helping
> me
> with. I'd love to have a reference for creating "code" (I've created
> some
> .code to check for division by zero, but it's been copied from other
> references) and for the ".expressions" that can be used with a field names
> (ie. .substring, .value, etc.)
> Regards,
> PB
>
> "EMartinez" wrote:
>> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>> In the Layout view/tab, click on the matrix cell that has the
>> subtotals/grandtotal and select F4 (to get the Properties window).
>> Below Appearance, to the right of BackgroundColor select
>> '<Expressions...>' from the drop-down list and enter something like
>> the following:
>> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value =>> "Grand Total", "LightGrey", "White")
>> Where LightGrey is the value if true and White is the color otherwise.
>> If you create the subtotal in your stored procedure or query that
>> sources the report, you can set the column using an expression
>> (similar to above); however, I believe. if you use the one created in
>> the report, it will force you to a specific column. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>|||On Mar 14, 8:04 am, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> Mr Martinez will probably give you some more tips, but I just want to add a
> link to William Pearson's articles on Reporting Serivces
> http://www.databasejournal.com/article.php/1459531
> You need to scroll down almost to the bottom, but there you find 38
> tutorials. A lot of them are focused on OLAP based reports, but most of the
> things you can do with OLAP based reports you can do with database based
> reports as well.
> Direct Reports (Brian Welcker's Weblog) athttp://blogs.msdn.com/bwelcker/default.aspxis good too. I also read Bob's
> SQL Reporting Services Blog athttp://blogs.msdn.com/bobmeyers/default.aspx
> As for discussion groups, I usually end up with this news group, either
> following it through a news reader, or searching with Google groups.
> Can't give you a lot of tips on the code bit, though, haven't done too much
> of that myself.
> Kaisa M. Lindahl Lervikhttp://blogs.spipp.net/blogs/kaisa
> "ppbedz" <ppb...@.discussions.microsoft.com> wrote in message
> news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> > Thank you. You have been a great help! I have 60 Finance reports to be
> > created with Visual Studio and no formal training. I am so grateful for
> > your answers to my posts! Can you recommend any other
> > resources/discussion
> > groups that would be helpful? I did purchase the book "Hitchhikers Guide
> > to
> > SQL Server 2000 Reporting Services". It has been helpful with the basics,
> > but not with the more kind of detailed "coding" that you have been helping
> > me
> > with. I'd love to have a reference for creating "code" (I've created
> > some
> > .code to check for division by zero, but it's been copied from other
> > references) and for the ".expressions" that can be used with a field names
> > (ie. .substring, .value, etc.)
> > Regards,
> > PB
> > "EMartinez" wrote:
> >> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> >> > I'd like to display the subtotal and Grand total rows of my Matrix
> >> > report in
> >> > grey, but I can't find a proprties box to change. I've changed the
> >> > area
> >> > that displays the text ie. "Grand Total", but there are not separate
> >> > boxes
> >> > for the subtotal values like you'd see with a table. I also seem to
> >> > be
> >> > stuck with my subtotal text in the second column when I'd like to print
> >> > it in
> >> > the first column. I'd like to see
> >> > SubTotal xxxxx
> >> > Grand Total
> >> > I can't define in the same box.
> >> > I'm really "winging it" with this Matrix report. All my other report
> >> > experiences have been with tables.
> >> > Thank you in advance for any help you can provide.... PB
> >> In the Layout view/tab, click on the matrix cell that has the
> >> subtotals/grandtotal and select F4 (to get the Properties window).
> >> Below Appearance, to the right of BackgroundColor select
> >> '<Expressions...>' from the drop-down list and enter something like
> >> the following:
> >> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => >> "Grand Total", "LightGrey", "White")
> >> Where LightGrey is the value if true and White is the color otherwise.
> >> If you create the subtotal in your stored procedure or query that
> >> sources the report, you can set the column using an expression
> >> (similar to above); however, I believe. if you use the one created in
> >> the report, it will force you to a specific column. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
You are very welcome. Glad to be of assistance. I can email you a kind
of 'how tos' list (that I have created along the way), if you would
like. This news group is the most active one (for Reporting Services),
so it usually gets the most responses and assistance. A couple of
lower activity ones are: http://groups.google.com/group/ReportingServices
and http://groups.google.com/group/RS2005. I tend to help out on those
as well. These Microsoft links are pretty handy (but not too easy to
navigate): http://msdn2.microsoft.com/en-us/library/ms159106.aspx and
http://msdn2.microsoft.com/en-us/library/ms170246.aspx. Also, if you
know of anyone that would be interested, I will be offering a SQL
Server 2005 Reporting Services class in the Nashville, TN area
starting the end of April.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi all!
I'm using the function SubToal built in RS.
How can I change the row's background for the row's handling the subtotals
infos?
Is there a way to find the value of a textboxX instead of the value of a
field?
Thanks, Julien
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> I'd like to display the subtotal and Grand total rows of my Matrix report
> in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate
> boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it
> in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB|||Hi Julien,
I had the same problem and I solved it using the InScope() function.
You will find an example on this blog :
http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
Best regards,
Jean-Baptiste
"Julien Bonnier" wrote:
> Hi all!
> I'm using the function SubToal built in RS.
> How can I change the row's background for the row's handling the subtotals
> infos?
> Is there a way to find the value of a textboxX instead of the value of a
> field?
> Thanks, Julien
>
> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> > I'd like to display the subtotal and Grand total rows of my Matrix report
> > in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate
> > boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it
> > in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
>
>|||FABOULOUS!!!
Thanks alot! I'm gonna add this to all my reports!
Thanks again!
Julien
"jbb92" <jbb92@.discussions.microsoft.com> wrote in message
news:905BD939-9C3E-4EBE-B002-7FFBC064752B@.microsoft.com...
> Hi Julien,
> I had the same problem and I solved it using the InScope() function.
> You will find an example on this blog :
> http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
> Best regards,
> Jean-Baptiste
> "Julien Bonnier" wrote:
>> Hi all!
>> I'm using the function SubToal built in RS.
>> How can I change the row's background for the row's handling the
>> subtotals
>> infos?
>> Is there a way to find the value of a textboxX instead of the value of a
>> field?
>> Thanks, Julien
>>
>> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
>> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report
>> > in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it
>> > in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>>
Monday, March 19, 2012
Change Crystal Database in vba
hi,
I have to use vba to display a report.
But this report should work on multiple database without having to change the report each time.
So I would like to change the report on runtime.
I think i can use LogOnServer (method of the application Object) or something.
Now i don't know how to use it. Looked for examples on the kb of crystal and this forum but wasn't succesfull to make it work.
Can somebody help me.
This is the code i use to call the report:
Set crxReport = crxApplication.OpenReport(MyReportFile)
crxReport.ParameterFields(1).AddCurrentValue (DocNumber)
Me!Crviewer1.ReportSource = crxReport
Me!Crviewer1.ViewReport
Crviewer1.Zoom (100)
While Me!Crviewer1.IsBusy
DoEvents
Wendreport.Database.Tables(1).SetLogOnInfo ServerName, DBName, UserID, Pwd
This will set the logon info for the table used in the report.
I have to use vba to display a report.
But this report should work on multiple database without having to change the report each time.
So I would like to change the report on runtime.
I think i can use LogOnServer (method of the application Object) or something.
Now i don't know how to use it. Looked for examples on the kb of crystal and this forum but wasn't succesfull to make it work.
Can somebody help me.
This is the code i use to call the report:
Set crxReport = crxApplication.OpenReport(MyReportFile)
crxReport.ParameterFields(1).AddCurrentValue (DocNumber)
Me!Crviewer1.ReportSource = crxReport
Me!Crviewer1.ViewReport
Crviewer1.Zoom (100)
While Me!Crviewer1.IsBusy
DoEvents
Wendreport.Database.Tables(1).SetLogOnInfo ServerName, DBName, UserID, Pwd
This will set the logon info for the table used in the report.
Sunday, March 11, 2012
Change Color of Textbox Depending on Value
He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:
=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))
They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?
Any Idea's ?|||if your statement:
=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))
You are evaluting the First Fields value "Literally" if you change it to
=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.
|||Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!
=Iif(Fields!Days.Value is Null, "AliceBlue", "White")
|||I tried this code for my returntime field: Do I need to do a data conversion?
=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")
Build complete -- 0 errors, 0 warnings
[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.
Preview complete -- 0 errors, 1 warnings
|||Humm,
I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.
Wednesday, March 7, 2012
Change "from" portion of the email when calling sp_send_dbmail?
I want to be able to call sp_send_dbmail and be able to change the "from" portion of the email (the email and the display name) programmatically/data-driven as a sproc constructs and sends the email.
Is this possible?
I believe the only way to do this is to use different profiles|||Take a look at xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm.|||To enhance Euan's reply refer to - http://www.sql-server-performance.com/da_email_functionality.asp & http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp links.|||
Your answer is not very practical when you have hundreds of users. 
In the older SQL Server 2000 we used
Code Snippet
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.in_FROM_ADDRESS,
@.FROM_NAME = @.in_FROM_NAME,
@.TO = @.in_TO_ADDRESS,
@.CC = @.in_CC,
@.BCC = @.in_BCC,
@.priority = @.in_priority,
@.subject = @.in_subject,
@.type = @.in_type,
@.message = @.in_message
Which had the first paramater @.From and the second one was @.From_Name.
Both of which are now gone in the new send dbmail
Code Snippet
exec @.rc = msdb.dbo.sp_send_dbmail
@.profile_name = 'MIS',
@.recipients = @.in_TO_ADDRESS,
@.copy_recipients = @.in_CC,
@.blind_copy_recipients = @.in_BCC,
@.importance = @.in_priority,
@.subject = @.in_subject,
@.body = @.in_message
The resulting emails are coming from whatever you set the Profile name to be. As you would by default set the profile to be generic, something like 'MIS Server Mailer' or whatever your application name is. Hense all the mails now come from this generic user. 
This is a problem, and MS should extend the DB Mail to include this rather simple option.
Change "from" portion of the email when calling sp_send_dbmail?
I want to be able to call sp_send_dbmail and be able to change the "from" portion of the email (the email and the display name) programmatically/data-driven as a sproc constructs and sends the email.
Is this possible?
I believe the only way to do this is to use different profiles|||Take a look at xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm.|||To enhance Euan's reply refer to - http://www.sql-server-performance.com/da_email_functionality.asp & http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp links.|||
Your answer is not very practical when you have hundreds of users.
In the older SQL Server 2000 we used
Code Snippet
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.in_FROM_ADDRESS,
@.FROM_NAME = @.in_FROM_NAME,
@.TO = @.in_TO_ADDRESS,
@.CC = @.in_CC,
@.BCC = @.in_BCC,
@.priority = @.in_priority,
@.subject = @.in_subject,
@.type = @.in_type,
@.message = @.in_message
Which had the first paramater @.From and the second one was @.From_Name.
Both of which are now gone in the new send dbmail
Code Snippet
exec @.rc = msdb.dbo.sp_send_dbmail
@.profile_name = 'MIS',
@.recipients = @.in_TO_ADDRESS,
@.copy_recipients = @.in_CC,
@.blind_copy_recipients = @.in_BCC,
@.importance = @.in_priority,
@.subject = @.in_subject,
@.body = @.in_message
The resulting emails are coming from whatever you set the Profile name to be. As you would by default set the profile to be generic, something like 'MIS Server Mailer' or whatever your application name is. Hense all the mails now come from this generic user.
This is a problem, and MS should extend the DB Mail to include this rather simple option.
Subscribe to:
Posts (Atom)