Is it possible to control start color when doing alternating background
colors on a details group in a table?
If alternating between White and Green, I want the first detail row to be
white, and the next green, regardless of row number.
I've tried using both this expression:
=iif(RowNumber(Nothing) Mod 2, "White", "WhiteSmoke")
and this:
= iif(RunningValue( Fields!MyField.Value, CountDistinct, Nothing) mod 2,
"White", "WhiteSmoke")
They both work, but they both seem to count row numbers, regardless of
grouping. If I try using the detail group name instead of nothing, I don't
get any color.
= iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_DG") mod 2,
"White", "WhiteSmoke")
Is it possible to control this? Any advise appreciated!
Kaisa M. Lindahl LervikFigured it out, silly misunderstanding from my side.
I needed to specify the group one level higher than the details group. So
when I did
= iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_Group1") mod
2, "White", "WhiteSmoke")
instead of
= iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_DG") mod 2,
"White", "WhiteSmoke")
it worked very well.
Kaisa M. Lindahl Lervik
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%238BagFzUGHA.224@.TK2MSFTNGP10.phx.gbl...
> Is it possible to control start color when doing alternating background
> colors on a details group in a table?
> If alternating between White and Green, I want the first detail row to be
> white, and the next green, regardless of row number.
> I've tried using both this expression:
> =iif(RowNumber(Nothing) Mod 2, "White", "WhiteSmoke")
> and this:
> = iif(RunningValue( Fields!MyField.Value, CountDistinct, Nothing) mod 2,
> "White", "WhiteSmoke")
> They both work, but they both seem to count row numbers, regardless of
> grouping. If I try using the detail group name instead of nothing, I don't
> get any color.
> = iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_DG") mod 2,
> "White", "WhiteSmoke")
> Is it possible to control this? Any advise appreciated!
> Kaisa M. Lindahl Lervik
>
Showing posts with label control. Show all posts
Showing posts with label control. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
How to make a page break...correctly
I am creating a report that uses a list control and have the break at end of
list. This does not work on all pages. I have some sections that spill over
to another page. This would not be a problem if the list control would insert
a page break prior to starting the next section. Also, it seems that some
reports that are shorter, the next section starts on the same page. Is there
some trick to making a report insert a page break where you want it to
everytime? I am using SSRS 1 w/SP2.On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> I am creating a report that uses a list control and have the break at end of
> list. This does not work on all pages. I have some sections that spill over
> to another page. This would not be a problem if the list control would insert
> a page break prior to starting the next section. Also, it seems that some
> reports that are shorter, the next section starts on the same page. Is there
> some trick to making a report insert a page break where you want it to
> everytime? I am using SSRS 1 w/SP2.
I have not used SP2 yet; however, prior to that, if you right click
the list control and go to Properties on the General tab below Page
Breaks you should be able to select 'Insert a page break after this
list.' Also, if you are using a table inside the list you should be
able to insert a page break after the table; as well, as after any
groups defined in the table. That said, sometimes the grouping in SSRS
is a little funny when it comes to lists and tables. Hope this is
helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On the list control, I do have the property checked to break after the list.
Just it does not always seem to work. I have one list control in the body of
the page. In this list control, I have many textbox controls. The list
control is tied to a datasource and I have ten records in this datasource, so
I expect 10 pages from my report. But what happens, is that records one and
two will display on a page by themselves, but record three will have part of
record four at the bottom of it, then record four finishes, and record five
will be back to a page of its own. This happens every so often and I do not
see a pattern. Hope this makes sense. Thanks for looking into this.
"EMartinez" wrote:
> On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> > I am creating a report that uses a list control and have the break at end of
> > list. This does not work on all pages. I have some sections that spill over
> > to another page. This would not be a problem if the list control would insert
> > a page break prior to starting the next section. Also, it seems that some
> > reports that are shorter, the next section starts on the same page. Is there
> > some trick to making a report insert a page break where you want it to
> > everytime? I am using SSRS 1 w/SP2.
>
> I have not used SP2 yet; however, prior to that, if you right click
> the list control and go to Properties on the General tab below Page
> Breaks you should be able to select 'Insert a page break after this
> list.' Also, if you are using a table inside the list you should be
> able to insert a page break after the table; as well, as after any
> groups defined in the table. That said, sometimes the grouping in SSRS
> is a little funny when it comes to lists and tables. Hope this is
> helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 1, 9:01 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> On the list control, I do have the property checked to break after the list.
> Just it does not always seem to work. I have one list control in the body of
> the page. In this list control, I have many textbox controls. The list
> control is tied to a datasource and I have ten records in this datasource, so
> I expect 10 pages from my report. But what happens, is that records one and
> two will display on a page by themselves, but record three will have part of
> record four at the bottom of it, then record four finishes, and record five
> will be back to a page of its own. This happens every so often and I do not
> see a pattern. Hope this makes sense. Thanks for looking into this.
> "EMartinez" wrote:
> > On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> > > I am creating a report that uses a list control and have the break at end of
> > > list. This does not work on all pages. I have some sections that spill over
> > > to another page. This would not be a problem if the list control would insert
> > > a page break prior to starting the next section. Also, it seems that some
> > > reports that are shorter, the next section starts on the same page. Is there
> > > some trick to making a report insert a page break where you want it to
> > > everytime? I am using SSRS 1 w/SP2.
> > I have not used SP2 yet; however, prior to that, if you right click
> > the list control and go to Properties on the General tab below Page
> > Breaks you should be able to select 'Insert a page break after this
> > list.' Also, if you are using a table inside the list you should be
> > able to insert a page break after the table; as well, as after any
> > groups defined in the table. That said, sometimes the grouping in SSRS
> > is a little funny when it comes to lists and tables. Hope this is
> > helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
Unfortunately, SSRS is a bit flaky sometimes when it comes to grouping
and page breaks. The only other thing I can think of is to try to put
the textbox controls inside a rectangle and then inside a list
control.
Regards,
Enrique Martinez
Sr. SQL Server Developer
list. This does not work on all pages. I have some sections that spill over
to another page. This would not be a problem if the list control would insert
a page break prior to starting the next section. Also, it seems that some
reports that are shorter, the next section starts on the same page. Is there
some trick to making a report insert a page break where you want it to
everytime? I am using SSRS 1 w/SP2.On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> I am creating a report that uses a list control and have the break at end of
> list. This does not work on all pages. I have some sections that spill over
> to another page. This would not be a problem if the list control would insert
> a page break prior to starting the next section. Also, it seems that some
> reports that are shorter, the next section starts on the same page. Is there
> some trick to making a report insert a page break where you want it to
> everytime? I am using SSRS 1 w/SP2.
I have not used SP2 yet; however, prior to that, if you right click
the list control and go to Properties on the General tab below Page
Breaks you should be able to select 'Insert a page break after this
list.' Also, if you are using a table inside the list you should be
able to insert a page break after the table; as well, as after any
groups defined in the table. That said, sometimes the grouping in SSRS
is a little funny when it comes to lists and tables. Hope this is
helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On the list control, I do have the property checked to break after the list.
Just it does not always seem to work. I have one list control in the body of
the page. In this list control, I have many textbox controls. The list
control is tied to a datasource and I have ten records in this datasource, so
I expect 10 pages from my report. But what happens, is that records one and
two will display on a page by themselves, but record three will have part of
record four at the bottom of it, then record four finishes, and record five
will be back to a page of its own. This happens every so often and I do not
see a pattern. Hope this makes sense. Thanks for looking into this.
"EMartinez" wrote:
> On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> > I am creating a report that uses a list control and have the break at end of
> > list. This does not work on all pages. I have some sections that spill over
> > to another page. This would not be a problem if the list control would insert
> > a page break prior to starting the next section. Also, it seems that some
> > reports that are shorter, the next section starts on the same page. Is there
> > some trick to making a report insert a page break where you want it to
> > everytime? I am using SSRS 1 w/SP2.
>
> I have not used SP2 yet; however, prior to that, if you right click
> the list control and go to Properties on the General tab below Page
> Breaks you should be able to select 'Insert a page break after this
> list.' Also, if you are using a table inside the list you should be
> able to insert a page break after the table; as well, as after any
> groups defined in the table. That said, sometimes the grouping in SSRS
> is a little funny when it comes to lists and tables. Hope this is
> helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 1, 9:01 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> On the list control, I do have the property checked to break after the list.
> Just it does not always seem to work. I have one list control in the body of
> the page. In this list control, I have many textbox controls. The list
> control is tied to a datasource and I have ten records in this datasource, so
> I expect 10 pages from my report. But what happens, is that records one and
> two will display on a page by themselves, but record three will have part of
> record four at the bottom of it, then record four finishes, and record five
> will be back to a page of its own. This happens every so often and I do not
> see a pattern. Hope this makes sense. Thanks for looking into this.
> "EMartinez" wrote:
> > On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> > > I am creating a report that uses a list control and have the break at end of
> > > list. This does not work on all pages. I have some sections that spill over
> > > to another page. This would not be a problem if the list control would insert
> > > a page break prior to starting the next section. Also, it seems that some
> > > reports that are shorter, the next section starts on the same page. Is there
> > > some trick to making a report insert a page break where you want it to
> > > everytime? I am using SSRS 1 w/SP2.
> > I have not used SP2 yet; however, prior to that, if you right click
> > the list control and go to Properties on the General tab below Page
> > Breaks you should be able to select 'Insert a page break after this
> > list.' Also, if you are using a table inside the list you should be
> > able to insert a page break after the table; as well, as after any
> > groups defined in the table. That said, sometimes the grouping in SSRS
> > is a little funny when it comes to lists and tables. Hope this is
> > helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
Unfortunately, SSRS is a bit flaky sometimes when it comes to grouping
and page breaks. The only other thing I can think of is to try to put
the textbox controls inside a rectangle and then inside a list
control.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Monday, March 19, 2012
How to log uses of SELECT
Hello,
Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or entered) their data.
-So I need to be able to keep a log of which users have seen which patient files, but I don't control the app code (and wish to god there were such a thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure into the app?
Thanks,
JohnThere is no trigger on SELECT, and Profiler is probably not valuable enough
for the cost of it running constantly, but you should try it out. You might
investigate some of the auditing tools listed in http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:3F88A456-3DFF-4362-A1F8-E130E1A546E4@.microsoft.com...
> Hello,
> Here's my scenario:
> -I have a vendor application running sql2k.
> -I do not control the code and the vendor isn't currently interested in
implementing what I want.
> -I keep medical data in this app.
> -Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
> -So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
> Can I do this without necessarily being able to write a logging procedure
into the app?
> Thanks,
> John
>|||> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
Yikes, so if username does SELECT * FROM patients they're going to get a row
in your audit table for every row in your data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in
implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
-So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure
into the app?
Thanks,
John
--
It would be difficult because selects are not logged. You can turn on
profiler trace and capture all selects on specific tables. But reviewing
this output is not as straightforward as filtering by a certain patient_id.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Take a look at Lumigent Entegra (www.lumigent.com), which
is an audit tool for SQL Server. I'm not affiliated with
them, but I'm currently evaluating the product. It does
audit queries.
Linchi
>--Original Message--
>Hello,
>Here's my scenario:
>-I have a vendor application running sql2k.
>-I do not control the code and the vendor isn't currently
interested in implementing what I want.
>-I keep medical data in this app.
>-Patients, by law, are entitled to know who's seen (not
just changed or entered) their data.
>-So I need to be able to keep a log of which users have
seen which patient files, but I don't control the app code
(and wish to god there were such a thing as a select
trigger).
>If Joe comes to me soon, and says "I need to know who's
seen my data." I'd like to do something like:
>select username
>from audit_selects
>where patient_id = XXX
>and get a list of people who've seen this.
>Can I do this without necessarily being able to write a
logging procedure into the app?
>Thanks,
>John
>.
>
Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or entered) their data.
-So I need to be able to keep a log of which users have seen which patient files, but I don't control the app code (and wish to god there were such a thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure into the app?
Thanks,
JohnThere is no trigger on SELECT, and Profiler is probably not valuable enough
for the cost of it running constantly, but you should try it out. You might
investigate some of the auditing tools listed in http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:3F88A456-3DFF-4362-A1F8-E130E1A546E4@.microsoft.com...
> Hello,
> Here's my scenario:
> -I have a vendor application running sql2k.
> -I do not control the code and the vendor isn't currently interested in
implementing what I want.
> -I keep medical data in this app.
> -Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
> -So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
> Can I do this without necessarily being able to write a logging procedure
into the app?
> Thanks,
> John
>|||> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
Yikes, so if username does SELECT * FROM patients they're going to get a row
in your audit table for every row in your data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in
implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
-So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure
into the app?
Thanks,
John
--
It would be difficult because selects are not logged. You can turn on
profiler trace and capture all selects on specific tables. But reviewing
this output is not as straightforward as filtering by a certain patient_id.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Take a look at Lumigent Entegra (www.lumigent.com), which
is an audit tool for SQL Server. I'm not affiliated with
them, but I'm currently evaluating the product. It does
audit queries.
Linchi
>--Original Message--
>Hello,
>Here's my scenario:
>-I have a vendor application running sql2k.
>-I do not control the code and the vendor isn't currently
interested in implementing what I want.
>-I keep medical data in this app.
>-Patients, by law, are entitled to know who's seen (not
just changed or entered) their data.
>-So I need to be able to keep a log of which users have
seen which patient files, but I don't control the app code
(and wish to god there were such a thing as a select
trigger).
>If Joe comes to me soon, and says "I need to know who's
seen my data." I'd like to do something like:
>select username
>from audit_selects
>where patient_id = XXX
>and get a list of people who've seen this.
>Can I do this without necessarily being able to write a
logging procedure into the app?
>Thanks,
>John
>.
>
Wednesday, March 7, 2012
how to leave the focus in a control when click in any part of the form.
Hi there,
I have a form with textbox, combos, ect... I'd like to know how I can make to leave the focus of one of this control when i click in any part of the form that is not a control, and so it's not focusable.
cheers.
Is this SQL Server Reporting Services related? You may want to try one of the .NET forums.Friday, February 24, 2012
how to know status of previous control flow
Hi,
I have a package which has 3 data flows and a script component to write log file. Those 3 data flows are in sequence (mean upon success, next will run). If the first 2 data flows fails, control will go to script and upon completion of 3rd, control will go to script to create log file.
Now, my question is, within my script component, is there any way to find out what was the last ran data flow and its status? What I know is, adding a 'activex task' between each data flow and script component which updates a variable and then I can use that variable in the script component to know the last ran data flow. But what I want to know is, is there any system variable (or something) which will do the samething without any need to add additional task?
Thx.
Where you say script component I assume you mean script task? (the two are different you see)
There is no way of knowing what the previous task is unless you tell it. You are proposing to do this with an ActiveX Script task. I recommend you don't do that as the are only there for backward compatibility. Use a script task instead.
-Jamie
|||If you mean a script task, you can do this as follows:
Create a string variable at the scope of the package - call it something like LastTask. http://msdn2.microsoft.com/en-us/library/ms141670(SQL.90).aspx
Create a sequence container and build your control flow inside the sequence - you can drag in existing objects, or copy and paste them in. http://msdn2.microsoft.com/en-us/library/ms139855.aspx
Create an OnPostExecute EventHandler for the sequence container. http://msdn2.microsoft.com/en-us/library/ms139744(SQL.90).aspx
Add a Script Task to the EventHandler. Configure the Script Task to use your LastTask variable as a ReadWrite variable and the system variable SourceName. (For detailed info on using variables with the script task see http://msdn2.microsoft.com/en-us/library/ms135941.aspx)
In the ScriptTask you could use the following line to set LastTask to the name of the last task to execute within the sequence ...
Dts.Variables("LastTask").Value = Dts.Variables("SourceName").Value
If you need to insert this value into a data flow, you could use the DerivedColumn component to insert the value of LastTask into a new column in each row passing.
hth
Donald
|||Hi Donald,
Thx for giving me the perfect solution. I have couple of questions to that. The above method will tell me which is the last task. But how do I know the status of the task? Also in case of failure how do I know the error message?
Thx again.
Subscribe to:
Posts (Atom)