Crystal Reports Question for BABU/any GURU

Can some one please help me with the below.

I need to write a formula in crystal reports to give the birth dates for this week. For Example: If I am running the report on a Tuesday I need to see the results from Tuesday to Saturday. I know that we can acheive this in 7 conditional steps, but I am looking for a precise formula. Can some one please help me with this.

Thanks
BTry this logic

Date=Date+5

CR.selectionFormula="{DateField=Date}"

Crystal Reports Images

I am currently using CrystalReports 9.0 and I was wondering if there is a way to make a picture appear if certain parameters are met. For example, if an employee ID = John Smith, a picture appears. I know how to put a picture on a report as well as how to use a blob field, but I can't seem to find a syntax that when a certain parameter is met, the image appears. The image is a system file. Any help would be appreciated! Thank you!Right click on the image field
Goto Format object and in the suppress click x-2 button and write this code

Parametefield="yourvalue"

Crystal Report Question

I'm designing a report and as an example, I'll say that I am using a field called COLORS. And during 2005 the Color Red was not used for any purchases. However, I still want the color RED to appear on the report with a zero in the monthly columns. I've tried everything to get this to work and I can't. Any suggestions, please?? Using an Oracle database.Do you want to show the field with red color if it is 0?|||No, I do not want to show a red color background if a value is zero. I want the COLOR field to show the name RED even if there is no data for that field up to this point in time.

So, for example, even if no RED color purchases were made I would still like RED to appear on the output of the report with a value of zero such as an example displayed below;

COLOR JAN FEB MAR APR

BLUE 10 10 10 10
GRAY 10 10 10 10
RED 0 0 0 0
BLACK 5 5 5 5|||I think you need to use Left Outer join in your SQL Query
What is your BackEnd?|||By BackEnd do you mean where does our data come from - it is an ORACLE database.|||If you use Joins then Use Left Outer Join

Crystal Report - How to add a page break?

Hi! I use Crystal Report 8 and I've created like an Invoicing Form, I need four copies of that form changing only the title for example: One saying "Original", second saying "Duplicated", etc...

I think to create the same page four times but I don't know how to add a page break as I need the same header, same body and same foot page than the first one...

Thank you for your help....I think you need to write a query like

Select * from TableName
Union All
Select * from TableName
Union All
Select * from TableName
Union All
Select * from TableName

Design the report using that query
Goto Section Expert and in the "New Page After" option, click the button labelled x-2 and write this code

If RecodNumber mod 20 = 0 then True|||Madhi,

Thank you.. but an error message appears saying "A number, currency amount, boolean, date, time, date-time, or string is expected here." I've tried to click "Page Break After" in Report Footer's Section Expert but the option is not available so I've click on the Group Footer's Section Expert...

Thank you again for your help|||Where did you get that error?|||Madhi,
Thank you again... That error occurs when I click on the Accept button on the Section Expert. I press the "x-2" button to add the formula that you've sent to me yesterday...

Regards, Guada

crosstable

Hi,
Could someone provide me the syntax or a procedure how to create a
alternative for a crosstable like in access.
For example i have a 2 tables, orders and orderdetails. What i want is to
display the data as a crosstable like view.
Here's a example in sheetmode in which i would like to view the data. The
productname comes from the orderdetails.
orderid | productname1 | productname2 | productname3
Is this possible?HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
Dynamic Crosstab Queries
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=15608&DisplayTab=Article[
/url]
AMB
"Jason" wrote:

> Hi,
> Could someone provide me the syntax or a procedure how to create a
> alternative for a crosstable like in access.
> For example i have a 2 tables, orders and orderdetails. What i want is to
> display the data as a crosstable like view.
> Here's a example in sheetmode in which i would like to view the data. The
> productname comes from the orderdetails.
> orderid | productname1 | productname2 | productname3
> Is this possible?
>
>|||The RAC utility is the closest thing to the Access crosstab
query in S2k.Like Access it's easy to use, no complex coding required for
dynamic crosstabs, and it's much more powerful with many
features/options.
www.rac4sql.net|||For your problem check out the RAC @.rank parameter
in the Help file.
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:OLOCGy$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> The RAC utility is the closest thing to the Access crosstab
> query in S2k.Like Access it's easy to use, no complex coding required for
> dynamic crosstabs, and it's much more powerful with many
> features/options.
> www.rac4sql.net
>

Crosstab query(intersection table)?

I can use crosstab query(intersection table) in sql server.

for example :

CREATE TABLE [Test] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike,N'math,60)

INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'English',70)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'france',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'jane',N'English',75)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mary',N'france',57)

INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'math',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'Englist',100)

Go

--

declare @.sql varchar(8000)

set @.sql = 'select name,'

select @.sql = @.sql + 'sum(case subject when ''+subject+''

then source else 0 end) as ''+subject+'','

from (select distinct subject from test) as a

select @.sql = left(@.sql,len(@.sql)-1) + ' from test group by name'

exec(@.sql)

But I cannot use it in sql server mobile editon.

HOw do I? thank you very much!!!

Suggest you review the SQL Mobile Books OnLine - there is a complete "SQL Reference for SQL Mobile" reference chapter there that will outline your options. The support for the CASE function in SQL Mobile is more basic than what you are trying to do here.

Darren

|||

Thank you, Darren Shaffer.

I do review SQL Reference for SQL Mobile. But I cannot find the method in sql mobile.

Yes , It can work well use case function . But the colums is not determinate. so I have to use :

declare @.sql varchar(8000)

set @.sql = 'select name,'

select @.sql = @.sql + 'sum(case subject when ''+subject+''

......////

The query can work well in sql server , but it cannot work in sql server mobile.

How do I ?

我非常困惑，请帮助解决好吗？谢谢了。

|||

I try to use crosstab query(intersection table) by sql server mobile in .netCF in my application. But write crosstab query is very hard for me in sql mobile editon.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277223&SiteID=1&mode=1

Must it be not carried out throuth sql query.May be it can do throught programming ？If that , How can I do it ? By the way ,I can only use C#.

|||

My advice would be to make the determination of the appropriate query to run in your application code (not in SQL) and then submit the appropriate query string to SQL Mobile.

Darren

|||

Moving this thread to the SQL Mobile forum.

I am also going to merge it with the other thread.

Cross dimension security filtering

If i have for example dimensions like Country and City (both containing a huge list). If i don't set a hierarchy for this and i create a report and i filter on Country and my second filter is City than i will get ALL city's listed instead of only the city's in the Country i've selected in my first filter.

We can easily fix this by making a hierarchy.

But my problem is now that my cube contains 15 dimensions and i want them all to filter each other in the dimension list in my reports. I cannot build one hierarchy that covers everything. Isn't there an alternative way to make dimensions filter each other as parameters ?

I've researched this for a while now and i'm starting to think there is no other way which is a huge sitback for analysis services as now people who only have access to the country Netherland can see all SalesRepresentatives from all over the world. I would have to add a hierarchy to filter them out but its not that easy. Certain SalesReps are in multiple country's and versa vi. This is only one of my problem dimensions i have alot of them like this. There should be a way that they automaticly filter each other without hierarchy building. But that is impossible right ?

Take a look at the NON EMPTY keyword. I think you'll find what you are looking for there.

There is also a NONEMPTY function. (Note the lack of a space between the two words.) That function, the EXISTS function, and the EXISTING keyword have behaviors you might also find helpful in different scenarios.

Good luck,

Bryan