Formulas In RS Like In Crystal

Jan 24, 2008

ive come from a crystal background and im having difficulty writing reports.

An example of the problem im having is with saved formulas (crystal calls them formulas)

eg

i would create a forumula called Difference

it would be something like this

=DateDiff("d",Field1, Field2) save that and then it was stored

then in a text box i would reference it like so.


=iif( Difference <= 23,"Too New","Just Right")

is this possible in RS??? as i have reports i need to convert from crystal to RS, and the iif statements are becoming huge, especially when you reference the same formula in the expression multiple times.


bit long winded i know, and i hope this makes sense, but i am witts end with this issue

thanks in advance

Jonny

View 3 Replies


ADVERTISEMENT

Formulas & Precision

Sep 21, 2006

I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?

Thanks.

View 4 Replies View Related

Column Formulas

Jul 20, 2005

Looking for a decent source that has some examples of column formulas aswell as a list of column formula functions that can be used and how to usethem.Hard time finding something online. Any suggestions or references would begreatly appreciated.Fairly new to this.Thanks

View 1 Replies View Related

Custom Formulas

Sep 4, 2007

im moving over from a crystal background, in crystal i was able to create custom formulaes and use them somewhere in the report, can this be done in RS, if so how?

thanks

Jonny

View 1 Replies View Related

Inserting Formulas In Tables

Dec 9, 2005

Sorry, I made a mistake. How can I delete this message?

View 3 Replies View Related

Help - Formulas With Functions Across Databases

Apr 28, 2004

I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?

---- Jake

View 4 Replies View Related

Formulas For Column Properties

Jun 25, 2006

I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.

Thanks

View 1 Replies View Related

Creating Excel Formulas In SRS

Jan 16, 2008

I'm creating a report that is designed to be exported to Excel so that the end user can manipulate the data. There are two main columns that I'm concerned with - TimeTaken and OTTimeTaken (for overtime).

Our application does not track OTTimeTaken so it's default will just be 0, but I need the excel file to have a formula that automatically adds the TimeTaken to the OTTimeTaken in a third cell for the total number of hours as that value will be added by the A/R department for invoicing.

I thought adding this would give me what i need:


="=SUM(C" & RowNumber(Nothing) & ": D" & RowNumber(Nothing) & ")"


when exporting to excel, it dumps it in as plain text, requiring that the user double click on the cell in order to "activate" the formula.

Is there a way to have SRS create this formula so that it works in excel rather than requiring the additional user interaction?


Thanks!

View 4 Replies View Related

Report Builder Formulas

Dec 27, 2007

Trying to get the blank out of my report and replace with (0). My formula is SUM(Loan Amount). If there is nothing to sum there is a blank space. Would like it to have a (0) in it.

View 1 Replies View Related

Dynamic Formulas Driven By Table

Jul 23, 2005

I'm working with a DB design that seems to me to be rather complex.This is a very slimmed down version of what I'm doing, but I believe itis enough to get my question resolved.Here is my layout.These 4 tables are used to generate a questionaire.Survey OrderID========= ==========SurveyID OrderIDOrderID QuestionGrpIDQGrp Questions============= =============QuestionGrpID QuestionIDQuestionID QuestionTextThe following two tables are used to calculate a report that is sent tothe customer.RawData=========================OrderIDQuestionIDValue is string but is Cast as decimal for numeric formulasMetrics==============================================QuestionGroupID | ReportText | Formula | MetID==============================================2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1The OP references are questionIDsNow to calculate the result for the report we programatically parse theformula creating a temp table (table name = Temp & orderID & _ &QuestionID) with OrderID and OPxx as the field names. We create onetable for each question.We then use dynamic SQL again to calculate the result using the aboveformulaSELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERETemp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =Temp5_23.OrderIDThis select is used to create a single table of calculated values.This table is in turn used to tell the customer how they compare toother customers. Percentile, Mean, Median, Std Dev, and a few others. Idon't claim this part of the project, but I'm not sure how I might havedone it, had it been assigned to me.MY PROBLEM!!!Sometimes a 0 is valid data and is the denominator of a devisioncalculation. Since this is so dynamic and it might be difficult todetermine when division is used. I need a way to default divide by 0execptions to NULL. This DB is on a hosted server.Thanks for bearing with me,Greg Kelley

View 2 Replies View Related

Creating Mathematical Formulas And Calculations

Aug 5, 2007

I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.

For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given

2
4
3
6
0

Average: 5

What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?

I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.

Any help will be appreciated.

View 1 Replies View Related

How To Handle NULL Dates In Formulas?

Mar 19, 2008

Re: SQL Server Reporting Services

I have welders who have active dates and inactive dates, and I need to create an "isactive" column in the report. My formula will be pretty obvious to most of you. However, if the user does not input an inactive date, that means none has been given and therefore the welder is currently active. The problem is that there seems to be no way to check if inactive date is null or not in the formula.

Please help! Thanks!

View 11 Replies View Related

Export To Excel - Rare Bug With Formulas?

Apr 27, 2007

Hi,



I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.



Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:






Code Snippet

=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value



This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.



99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...



I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.



Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;

- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)

- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)



Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.



And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.



Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.



*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...



Thanks in advance for any advice

View 4 Replies View Related

Reporting Services :: Excel Formulas In SSRS 2008

Jul 20, 2010

I need to have a report that will have formulas in excel. I understand that the limited support for exporting excel formulas has been dropped in SSRS 2008. Is there any way to work around this and still output formulas?obvioulsy the way to do it in previous versions of SSRS are not working (using report items). I tried to add my formulas in my dataset (I can know which excel cells I need in the formulas) but they end up being displayed as text. I then have to get in the excel file and enter and exit each cell so that it "becomes" a formula.

View 11 Replies View Related

Custom Rollup Formulas On Analysis Services 2005

Aug 29, 2007

Hello all, I´m a beginner on AS2005 (but I know pretty well AS2000), I migrated a cube from AS2000 to AS2005 but in this cube I have a Custom Rollup Formula on two levels of my Time dimension (month and week), an example of the week level formula is the next:
iif( [Time].CurrentMember.Name = [Time].CurrentMember.NextMember.Name,
null,
iif( [Time].CurrentMember.Name = [Time].CurrentMember.PrevMember.Name,
Sum({[Time].CurrentMember.Children,[Time].CurrentMember.PrevMember.Children}),
Sum([Time].CurrentMember.Children)
)
)
<<this formula overrides the week aggregations>>

and I really need this but I cannot find on AS2005 where to do the same thing, someone can tell me where can I define Custom Rollup Formulas and how?.
Thanks in advance!

View 10 Replies View Related

Power Pivot :: Aggregating Time Periods In Cube-member / Cube-value Formulas?

Aug 23, 2015

I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like

=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.

Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than

=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))

I haven't tested this but have assumed it works but a bit long and clumsy.

View 5 Replies View Related

Crystal Reports/SQL 7

May 3, 2001

A long shot but someone may have experienced same problem. We are using Crystal Info to run reports against a SQL 7 database. Reports that usually take 1 or 2 minutes to run are taking up to 15 minutes to run. The database is fine ie you run the query through the query analyser and it runs in 1 or 2 minutes. Any ideas... I've spoken to Seagate but not making much progress.
Thanks in advance..

View 1 Replies View Related

Crystal Reports

Sep 6, 2000

Can any one tell me where can I find good online help for Crystal Reports for learning?
Thanks!

View 1 Replies View Related

For You Crystal Experts

Feb 2, 2007

I just started using Crystal, and I have to say, its a pain in the arse.

Can I just run a simple SQL query and get the results I want, without having to use Crystal Syntax?

Here is the query I want to use:


Code:

select * from esmpvald, esmprmtr
where esmpvald.permit_id = esmprmtr.permit_id and
esmprmtr.issue_date >= ('01/01/06') and
esmprmtr.issue_date <= ('12/31/06') and
esmprmtr.permit_id in (select esmvardd.permit_id
from esmvardd, esmudfvr
where esmvardd.var_fld_id = esmudfvr.var_fld_id and
esmvardd.permit_id in
(select esmvardd.permit_id from esmvardd, esmudfvr where esmvardd.var_fld_id = esmudfvr.var_fld_id and
(esmvardd.var_fld_value = 'a') and
(esmudfvr.var_fld_name = 'Commercial Type')) and 1=1)



I will be looping through the "esmvardd.var_fld_value = 'a'" part, going a through f, but for now, I just want to start with the 'a'.

I could do this manually, and throw it in a Word doc, but I'm trying learn this thing. Ideas?

View 1 Replies View Related

Crystal Reports

Apr 19, 2006

I am working on creating Crystal Reports from a SQL database.

In CR you can define a multiple range parameter i.e. 1-10, 100-200, 300-400 and Cr will filer records that satisfy this criteria.

How can I transfer this functionality using a stored procedure?

View 1 Replies View Related

Crystal Reports

Mar 6, 2006

Hi,
I am new to crystal reports.

I need to change the database connection in the reports to different sql server database and use the same report layout.

Any help is appreciated.

View 4 Replies View Related

SQL In Crystal Reports

Mar 20, 2007

I'm new to SQL but have quite happily been using Crystal Reports without it until now.

I am creating a letter template and it has the name at the top then the address

<name>

<address1>
<address2>
<address3>
<address4>

There is always an address 1, but maybe not a 3 or a 4, and aparently we dont want any line gaps to show, so I figured i should use an isnull to eliminate the line and to move the other address line up?

Not sure what to put though, any ideas?

Thanks in advance for any help :o)

View 2 Replies View Related

CRYSTAL REPORTS 8.5

Sep 27, 2007

THE TIME PORTION OF THE DATE/TIME IS NOT DISPLAYING IN CRYSTAL 8.5
IT SEES THE DATE...BUT NOT THE TIME


ANY SUGGESTIONS

View 3 Replies View Related

Crystal Reports &&amp; Pdf

May 17, 2007

Hi,



I have a question. How can i fill a report made in CrystalReports in .net



Thanks in advance

View 1 Replies View Related

Crystal Report Using SQL CE

Aug 10, 2007

I've created an app that connects to a SQLCE database. I need to create Crystal Reports that connect to the database. There is no ODBC driver for SQLCE. How can I do this?

Thanks,
Kathy

View 9 Replies View Related

Crystal Reports

Mar 14, 2008

what are the good resources for crystal reports.
Also is it something which is difficult to work with ? Suggestion, experiences and notes please.

View 4 Replies View Related

CRYSTAL REPORT 8.0 PROBLEM

Apr 26, 2002

Hi,
I am trying to run the crystal report from asp.
its says SQL Server connection failed. But i could connect SQL server using the same DSN. Any idea???


Set crp = Server.CreateObject("Crystal.CRPE.Application")
iReturn = crp.LogOnServer("p2sodbc.dll", "DSNNAME","USERID","PASSWORD")
Set oRep = crp.OpenReport("C:inetpubwwwrootpdftestest.rpt")
oRep.Preview

Set crp = nothing

Thanks
Ilango

View 1 Replies View Related

Output Variable To Crystal From Sp

May 27, 1999

hey folks...
i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how....
any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management
:-)

take care.....kim

View 2 Replies View Related

Crystal Report 8.5 Vs SQL Server 2K

Jan 12, 2004

Does Crystal Report 8.5 work OK with SQL Server 2K ?

Anyone encoutered problems for with this couple of programs ?

View 3 Replies View Related

Connecting With Crystal Reports

Feb 2, 2006

Hi,
I'm very new to SQL Server, I'm coming from an Oracle background.
I've set up a server with windows authentication but I'm having no luck connectiong to it with Crystal Reports XI.
Can anybody go through the basics with me?
Regards
Sean

View 6 Replies View Related

Convert Crystal Report To Pdf

Sep 27, 2007

How can I convert a crystal report to a pdf format.ASP.net is not loaded in my machine.pls help me...

View 3 Replies View Related

Sql Server && Crystal Reports

Jul 23, 2005

Hi,I'm semi-new to crystal reports & sql server and what i want is toextract data from a sql server file. I've been able to connect to sqlserver directly and get the info. i need but is there a way to just openthe data file (in crystal reports) without making a server connection?TIA

View 1 Replies View Related

Crystal Reports And Sql Server 2k

Jul 23, 2005

Has anyone had any luck getting Crystal Reports 8.5 to work with theSql Server 2000?I keep getting a "Unable to open Database" error. I've tried openingwith either the .Connect property and the LogOnServer method and getthe same message from either approach.I am using the OCX control.Does this version work with Ms Sql?Any thoughts and suggestions would be most appreciated.Thanks.-Dave

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved