Setting Comparison Operator Depending On Parameter Value?

Apr 6, 2006

Hello,

Is it possible to set a comparison operator using a parameter value?

The code below shows what I'm after;

declare @co char(1)
declare  @date datetime

set @co = '<'
set @date = '02/02/2002'

select * from recipe where date @co @date

I would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.

thanks

View 2 Replies


ADVERTISEMENT

Comparison Operator In Select List

Jun 22, 2006

I want to create a column alias to represent the comparison of twocolumns (ie a boolean result of True or False). A simple example is:Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit[color=blue]>From VehicleFinance[/color]but I get an error 'Incorrect syntax near >'Books online states that the select_list can contain column_name orexpressionAn expression is a column name, constant, function, any combination ofcolumn names, constants and functions connected by an operator[color=blue]> is a binary operator[/color]So why do I get this error.Incidentally, if I use an arithmetic operator such as +, there is noproblem.

View 5 Replies View Related

Like Comparison Operator In An Expression To Ignore Case

Aug 31, 2007

How do you make the "Like" comparison operator ignore case in an expression??

Expression looks like this:




Code SnippetParameters!CompanyFilterOp.Value = "%",Fields!company.Value like "*" & Parameters!Company.Value & "*"





BTW, the expression above is part of a switch expression, and is at the table level.

Data contains "General Industry" in Company column from database.

User enters "indust" in Company Parameter text box, and result is no data found.

User enters "Indust" in Company Parameter text box, and data is returned.

Thanks in advance for your time in responding.

View 3 Replies View Related

Dynamically Setting Connection String Depending On Server_name?

Apr 18, 2008

I need to make my site aware of which server_name it is loading from so it uses a different connection string. (have dev + prod servers for web/sql)Currently my connection string is in web.config as follows:  <connectionStrings>      <!-- Development and Staging connection string -->          <add name="myconnection" connectionString="server=myserver; user id=mysuer; password=mypassword; database=mydatabase" />   </connectionStrings> I need to make sure the 'name' is the same for both connection strings since that is how the rest of my site looks for it.  However, I'm not sure how to get both in here with some sort of 'if/then' statement to determine which one to use.I've heard it could be done in global.asax with something similar to the code below, but I dont know how to assign a 'name' to a connection string for that type of setup. Sub
Session_OnStart  ServerName =
UCase(Request.ServerVariables("SERVER_NAME"))  IF ServerName = "prod.server.com" THEN   ...Set Prd string...  ELSE   ...Set Dev string...  END IF  End
Sub

View 8 Replies View Related

Getting Error While Setting Up Operator Using DBArtisan Tool

Mar 3, 2015

[Microsoft][SQL Server Native Client 11.0][SQL Server]INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.(42000,1934) Procedure(trgDDLChangeLog).

View 3 Replies View Related

Conditional Where Clause Depending On Input Parameter

May 5, 2008

I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.

My simple QueryA

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL

My simple QueryB

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL

I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.

Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?

I know it can be done using dynamic SQL construction. But any other ways ?

Also can someone also give in the solution in PL/SQL.

Thanks a bunch.

Jaffery.

View 7 Replies View Related

WHERE Clause Boolean - Different Results Depending On Entered Parameter

Jan 13, 2015

This is for Microsoft Server SQL 2012.

I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.

So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.

WHERE IF ID=0, THEN do this
ELSE do this AND ID=#

View 2 Replies View Related

Sql Help - Passing An Operator As A Parameter Maybe?

Sep 14, 2006

My data table contains a column (a foreign key) with numbers from 0 and up.  (it could be null or a number.  I used ISNULL to convert it.)  I want to be able to select in three ways.  1) equals zero (or is null)2) greater than zero (or is not null)3) equal to a specific number.How do I do this?My application setup is Presentation Layer, BLL, DAL and Sql Server.I don't want to create another Query because this is only part of a larger query with other parameters.If I could just do something like    @IDOperator @IDValue     in the Filter of the Sql it would be great.

View 2 Replies View Related

Problem With Multi-value Parameter (IN Operator)

Jan 16, 2008

I am using SQL server 2005 SP2.

I have a view created in Sql database by name vw_CETARIS_Suppliers.


CWS_fn_MVParam: This function takes a parameter for cities (xx,yy,zz)
and returns a table with the values as individual cities. - If I
execute this from t-sql, it is working perfectly. Also I created a dataset with the query
SELECT Param FROM CWS_fn_MVParam(@pCity,', ' )
This is executing properly and the dataset is getting all the vlaues from the concatenated parameter (for example 'xx,yy,zz' is returning a table of values
xx
yy
zz.



My SRS report has a multi-value parameter called @pCity. The
following query is given under the data tab in SRS report.



IF @pCity=' All'


SELECT CetarisSupplierCode, CetarisSupplierName, CetarisAddress1,
CetarisAddress2, CetarisCity, CetarisProvState, CetarisPostalZip,
CetarisCountry,
CetarisCurrency, CetarisCompany
FROM vw_CETARIS_Suppliers


ELSE
begin
SELECT CetarisSupplierCode, CetarisSupplierName, CetarisAddress1,
CetarisAddress2, CetarisCity, CetarisProvState, CetarisPostalZip,
CetarisCountry,
CetarisCurrency, CetarisCompany
FROM vw_CETARIS_Suppliers
WHERE CetarisCity IN (SELECT Param FROM CWS_fn_MVParam(@pCity,', ' ))
end



When I refresh the dataset and run for a All,single city or multiple
cities, it returns data. No problem.

When I preview the report for single city and 'All' value it returns data perfectly.
But when I select two or more cities I
am getting an error.
An expression of non-boolean type is specified in a context where a
condition is expected, near ','
Incorrect syntax near the keyword ELSE



Can anybody help me please?. I tried different ways but was not successful in finding out the problem.
Thank you for all your help in advance,

View 15 Replies View Related

Fail To Change The Value Of Parameter With Operator 'range'

Jan 24, 2007

Hi all,

I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).

...

From date To Date

...

From age To Age

...

From Salary To Salary



@salary is the last one.

when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)

Range operator ( : ) operands have different levels; they must be the same.

BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.

Any idea and suggestion will be appreciated!

Jone



View 1 Replies View Related

Setting A Parameter Value To Select All.

Nov 27, 2007

I searched this forum however I could not find an answer to my question. I'm sure it has been asked before but...

In my parameter if the user does not select anything, null, then it returns all the records.

Second question if he selects "All" then it returns all records.

If this is 1st grade stuff please forgive me. I'm pretty new, learning as the needs arise.

Thanks
CardGunner

View 12 Replies View Related

Setting Formview Datasource Parameter

May 23, 2007

I am trying to use the code below to set a formview datasource parameter in the page_load section of a user control (ascx file): public void Page_Load(object sender, EventArgs e){     formview_datasource.SelectParameters.Add("@department_id", "e62bbc7d623f44a68e101cba90e839s3");} However I am getting the following error: Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@department_id'.
So it would seem that page_load in my user control isn't being called? I'm not sure why or how to work around it. Has anyone else experienced anything like this or can give me some pointers on where I am going wrong?
ThanksBrad

View 3 Replies View Related

How To Combine Result Set With Parameter Setting?

Feb 20, 2008

I have a question regarding Execute SQL Task as I combined one statement like select count(*) from destination table, and insert into error table (source count, error count, destination count) values (?,?,?).

If I use two Execute SQL Task, it should work. I was wondering that is it possible to combine select and insert into one Execute SQL Task direct input. How to approch this?

View 6 Replies View Related

Setting Parameter Value Based On Matrix Column

Apr 10, 2007

Hello all.

I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....


I have a record of individual sales with the state, and quarter of the sale.

sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.


I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.

Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:

SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter

Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.

This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.

Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.

cmk

View 3 Replies View Related

Setting Defaults On A Multi-valued Parameter

Jan 27, 2006

I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.

Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.

I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...

Trent

View 3 Replies View Related

Peculiar Behavior In The Act Of Setting RS Parameter Values...

Jan 21, 2008

we're trying to get a better understanding of how RS behaves when parameters are being set. We see quirky behavior that is a little difficult to describe. Right now we assume that if the revolving green circle (with the phrase "Report is being generated" beneath it) doesnt appear, the report really wasnt rendered properly, even if the report region changes.

One peculiarity that seems pretty consistent is on reports we've prototyped with "from" and "to" date parameters. It seems that when we set one date (doesnt matter which is 1st) things progress normally, ie no "report clearing event" occurs as a result of setting cursor focus in the calendar control and changing its value. The report region doesnt change from what showed previously. But trying to set focus on 2nd (doesnt matter if its "from" date or "to" date, just that its the 2nd date being set) always seems to trigger some kind of event that 1) doesnt allow focus to be on that text box, 2) blanks out the report region including headings. Only after this "event" occurs, can we set focus on the 2nd date, change the value and click the "view report" button for rerendering.

We see similar types of behavior with other types of parameters that include multi value dropdowns and booleans. The toughest part of this is trying to explain it to our users. On some parameters, the event always occurs every time they are changed. On other parameters, it appears that the event only occurs if another parameter was changed beforehand.

I believe we've even seen headings with no data rendered, thinking temporarily that no rows were returned, just to find out that by clicking the "view report" button there really was data to be reported based on current filters. Unfortunately I cant reproduce this scenario when I want to.

View 3 Replies View Related

Setting Default Value Of A Report Parameter Dynamically

Jan 26, 2007

Hi all,

Does sombody have experience on dynamically set or change the default value of a report parameter?

Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube

What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.

What I did is:

Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");

ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });

what I got when reveiw the report:

P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.

Any idea and suggestion will be pre-appreciated!

Jone

View 1 Replies View Related

Setting Select Parameter Based On Config File

Jun 17, 2008

Hi everybody,
Is there a way to set SelectParameter for SQLDataSource in ASPX file using System.Configuration.ConfigurationManager.AppSettings["SiteID"]) ?
Thanks a lot in advance. 

View 8 Replies View Related

Setting The Default To &&<Select All&&> In Multivalue Parameter List

Aug 21, 2007

How can I set the multivalue parameter list's default option to <Select All> ?

View 2 Replies View Related

Setting Empty Text Box Input On Form To Null Parameter For Sql

Feb 19, 2007

Hi,
 I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields.
I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is:
 SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept)
so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful.
Cheers,
Tom

View 3 Replies View Related

Reporting Services :: Available Parameter Values And Setting Defaults On Report Server

Jun 3, 2015

I have a report with a subscription enabled and the default values that are selected for the report frequently change.  I have our report server locked down so that the users can't change the defaults, but I now want to empower them to maintain this on their own.  Here is my dilemma.  When you have the available parameters set up to pull from a query, the defaults on the report server have to be keyed in manually, which is not an option.  The only way to get a check box there, is to explicitly specify the available values.I need my available values to be database driven and I need to be able to select my defaults on the report server using check boxes.

View 5 Replies View Related

Binding Report/Field 'language' Setting To Query/Parameter Result.

Oct 24, 2007

Hello,

I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.

The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.

The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.

However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).

I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.

Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?

View 3 Replies View Related

Reporting Services :: SSRS 2012 - MultiValue Parameter Throws Error On Setting Up Default Value

Aug 3, 2015

I have a multi value  parameter called "Location" and this depends on another multi valued  parameter value. The default value for the parameter "Location" comes from the another another multi valued  parameter. Now say when the default value is set for the parameter Location like the below:

The Location parameter data set has values from the Query and default values has been set as shown below:

=Iif(array.IndexOf(Parameters!Program.Value,"A")>-1,nothing,"N/A")

I get an error on preview saying that . The Default Expression for the report parameter "Location" contains error:

Unable to cast object of type 'System.String' to type 'System.Array'.

View 2 Replies View Related

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Nvarchar

Jun 2, 2004

I get this error when I attempt to read from a datareader using the following sql statement:

Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"

my reader command list as follows:

Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader


Any ideas on where I am going wrong?

Thanks in advance

View 3 Replies View Related

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Datetime.

May 18, 2004

I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.

I traced the pointer to @gdo and @gd, they are both dates!

INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)


If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:

View 3 Replies View Related

How Not To Display A Row Depending On Other Rows.

Oct 8, 2007

Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)

What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:

SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));

But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.

I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy

I really appreciate your help

View 8 Replies View Related

COUNT Depending On Data

Jul 20, 2005

Hello.I have one serious problem with COUNT in TSQL.I use MS SQL Server 2000.I would like to count rows depending on data in it.What I meat is:I have fields like:region | month | year | some_count |-------|-------|------|-------------|LA | 1 | 2003| 4 |LA | 2 | 2003| 2 |LA | 3 | 2003| 1 |LA | 4 | 2003| 6 |VV | 1 | 2003| 3 |VV | 2 | 2003| 7 |VV | 4 | 2003| 20 |VV | 6 | 2003| 3 |BB | 2 | 2002| 1 |etc...And what I would like to get from it is:region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...-------|----------|---------|-----------|----------|----------|-----------|--LA | 4 | 2 | 1 | 6 |0 | 0 | 0VV | 3 | 7 | 0 | 20 | 0| 3 | 0BB | 0 | 1 | 0 | 0 |0 | 0 | 0etc...Is it possible to do it in views? if yes then how?I could use temp tables for each month but it is not solution when I have 3yearsand more later then it will be round 40 temporary tables.Thanks for any response.Regards,Casper

View 3 Replies View Related

Adding Two Parameters With One Depending On The Value Of The Other

Feb 25, 2008



hello

sorry to distub you again

in fact i'm a beginner and i don't really see how i can do some tasks

when i display a report i want the reader to choose a client, and when he chooses a client, he can choose in the second parameter list, the missions that correspond to THIS client

so i have 3 datasets

one in which i get all my data needed
and i have a filter in where part of query




Code Snippet
where client=@client
and mission=@mission




and i filter on these 2 values

in the second dataset , i gather all the clients i have by doing this query







Code Snippet
select distinct name from client





and i have this third dataset where i gather all the missions corresponding to one client

and here i write




Code Snippet
select mission_label from client
where name=@client


the two fields are in the same time and in this third dataset i apply a filter on Parameters!client.value

but when i execute the report, the second parameter doesn't work, it gives me no choice of mission for a client, it doesn't return anything while when i execute the query in the dataset i have these values

how can i do this please?

thanks a lot in advance

and sorry or bothering with such questions



View 4 Replies View Related

T-SQL: Different SELECT Executed Depending On Condition.

Apr 30, 2004

I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.


IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable

View 1 Replies View Related

Insert Identity Depending On The Value Of A Column

Apr 23, 2008

Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT

At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?

View 13 Replies View Related

Insert The Rows Depending On The Cell Value

Jun 4, 2008

I want to insert the rows automatically depending on the cell value in column from another table.
Like if the value of cell "blabla" is 4 it automatically insert the 4 rows in my table with values.

Is it possible in TSQL?

View 13 Replies View Related

How To Apply An Inflation Factor Depending On The

Oct 5, 2007

Anybody knows how to apply an inflation factor depending on the date to a measure



The Inflation Factor is (Current Month Rate / X Month Rate)



So lets say for current year the rates are



Jan 121.64

Feb 121.98

March 122.244

April 122.171



For example



If I want to calculate for March I would show a column for January February and March, the column for January would be multiplied by (122.244/121.64) the column for February would be multiplied by (122.244/121.98) and march by one (122.244/122.244)



But in April the factors would be different I would have 4 columns, January February March and April, January amounts would be multiplied by (122.171/121.64) February by (122.171/121.98) March by (122.171/122.244) and April by one (122.171/122.171)


Im using SSAS 2005

Thanks in advance



Isaac

View 1 Replies View Related

T-SQL Create Self-reference In Depending Table

Jul 23, 2005

Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?

View 7 Replies View Related







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