Changing color based upon rownum

[davidq]

I am trying to add alternating line color to my reports. I added the following formula to the columns
IIF( recordnumber mode 2 = 0 , crteal, crwhite)
which works great except on grouped reports. Is there some way I can include 'whileprinting' logic to force CR to recogize each row after grouping?

 

[rosemaryl]

Dear Davidq,

I am assuming that what you are saying is that you want Crystal to "restart" the alternating color at each group.

I do this by creating a running total to count each "row" and reset it on change of group.

Insert/Field Object/Running Total/New

Summary:
Field To Summarize (choose a field in the detail section)
Type of Summary - Count

Evaluate:

For Each Record

Reset:

On change of Group - Choose last group!

Click OK.

Then in the detail section, I use this formula:

If Remainder({#R_Counter},2) <> 0 then crwhite else crteal

//your formula also works:

IIF( {#R_Counter} mod 2 = 0 , crteal, crwhite)


Hope this was helpful,

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[davidq]

Thanks, I used a variation of what you suggested and got it to work.

 

[IanWaterman]

I am using CR 10 on Oracle 8

Within Oracle SQL there is a reserved function called rownum, this returns the database row number.

I need to be able to get at this through Crystal as I need to sort data through about 4 levels and at the very end sort by the Oracle RowNum. Has I want to list data inthe order it was entered. When you sort via crystal you lose the data entry order.

Can this be done?

My only other alternative seems to be building a view of the table and adding Rownum as an additional column, and then adding this to my final sort order in Crystal.

Thank you

Ian

 

[rosemaryl]

Dear Ian,

If you post your thread as a new one, you are likely to get more answers.

However, one thing you can try is a sql expressions.

If you can get the rownum in a query through plsql then you should be able to get it through an expression.

I don't really do a lot of oracle but syntactically when calling functions in a sql expression it would be similar to how you would do it in plsql just wrap it in parens.

regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[IanWaterman]

I had searched the forum for possible previous threads but there was nothing suitable, and thought I was creating a new string.

Could not find anyway of deleting once I had started.

Also posted on Data Access.

If using a sql expression how do you relate it to specific data. I have only ever managed to bring back a data set which I can not relate to the data in my report. So not sure how I could group/sort. For example I could do a select statement

Select rownum, accounttransaction_no from accountransaction

That will give me a list of Row numbers and corresponding Transaction numbers, but If I try to relate the transaction number in the SQL Expression to the tranaction no in my report the I get an error Field not found.

Ian

 

[rosemaryl]

Dear Ian,

My investigation shows that the Oracle Rownum simply numbers the records in the order they are returned from the database ....

When you say I need to sort by 4 levels you are choosing to change the sort of how the records came through. Is there no field in your records that indicates the date the data was entered? Or, there may be a primary key that is a number. Most of the tables I work with have a sequential primary key, so if after all my other sorts I wanted to sort by order entered (within the other sorts) I would sort by the primary key.

I would look at the data and see if there is any field(s) that will do that for you.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762