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 it
is enough to get my question resolved.

Here is my layout.

These 4 tables are used to generate a questionaire.

Survey OrderID
========= ==========
SurveyID OrderID
OrderID QuestionGrpID

QGrp Questions
============= =============
QuestionGrpID QuestionID
QuestionID QuestionText


The following two tables are used to calculate a report that is sent to
the customer.

RawData
=========================
OrderID
QuestionID
Value is string but is Cast as decimal for numeric formulas


Metrics
==============================================
QuestionGroupID | ReportText | Formula | MetID
==============================================
2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1

The OP references are questionIDs

Now to calculate the result for the report we programatically parse the
formula creating a temp table (table name = Temp & orderID & _ &
QuestionID) with OrderID and OPxx as the field names. We create one
table for each question.

We then use dynamic SQL again to calculate the result using the above
formula

SELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERE
Temp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =
Temp5_23.OrderID

This select is used to create a single table of calculated values.
This table is in turn used to tell the customer how they compare to
other customers. Percentile, Mean, Median, Std Dev, and a few others. I
don't claim this part of the project, but I'm not sure how I might have
done it, had it been assigned to me.

MY PROBLEM!!!
Sometimes a 0 is valid data and is the denominator of a devision
calculation. Since this is so dynamic and it might be difficult to
determine when division is used. I need a way to default divide by 0
execptions to NULL. This DB is on a hosted server.
Thanks for bearing with me,

Greg Kelley

View 2 Replies


ADVERTISEMENT

SSRS -- Data Driven Subscription And Pivoting For Dynamic Parameter Mapping

Feb 12, 2007

Hi,

For the Data Driven Subscription in SSRS we are using the following stored procedure

In Step 3 - Create a data-driven subscription



create procedure spRSGetReportSettings

(

@ReportID as integer

) as

begin

set nocount on

declare @t as table(y int not null primary key)

declare

@cols as nvarchar(max),

@y as int,

@sql as nvarchar(max)

set @cols=stuff(

(select N',' + quotename(y) as [text()]

from (select ParameterName as y from Reportsettings where reportid=1) as Y

order by y

For XML Path('')),1,1,N'');

set @sql=N'select * from

(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D

pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'

exec sp_executesql @sql

end



Basically the idea is to maintain a single report parameter setting table for multiple reports.

Structure of the table is as given below

ReportID, ParameterName, ParameterValue.

Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)

But, in SSRS it is giving any results.

In Step 4 - Create a data-driven subscription,

Get the value from the database drop down, I am not getting any database columns.

Please help.

Kumar

View 3 Replies View Related

Reporting Services :: Data Driven Subscription With Dynamic Email Attachment File Name

Feb 11, 2010

I have report which accepts a card number and fromdate and todate as parameters to the report. This report needs to be sent on a quarterly basis to each of the customer mail id to which their card number is linked. I am getting all this information from a database and sending as an attachment to the customer. Now I would need making these filename's which are attached to be dynamic based on the input parameters.

In datadriven subscriptions, the option of include report has only true or false values and another option was to take from a database. I tried putting the dynamic file name in the database and getting the value from the database but no success, the subscription itself is failing here. I guess I am doing something wrong here by binding the report name from the value which I am getting from DB to the actual report name.

If report name = Mytransactions, and the parameters passed are Card = 123,fromdate = 1/1/2010,todate = 31/3/2010.

Now in the attachment the file name should be something like "Mytransactions_123_January1st2010_March31st2010".
How to make the filename dynamic.

View 9 Replies View Related

Metadata Driven SSIS Package--table Variable

Aug 16, 2005

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

View 12 Replies View Related

Query Performance Hard Coded Value Versus Table Driven

Nov 6, 2014

I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)

select * from A where ReceiptTS > '2014-09-30 00:00:00.000'

select * from A where ReceiptTS > (select ReferenceTS from Reference)

View 5 Replies View Related

SQL Server 2012 :: Replacing CASE Statement In Update With Table-driven Logic

Oct 20, 2014

I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):

UPDATE dbo.target_table
set target_column =
case
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'

[Code] ....

The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.

The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.

What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.

View 9 Replies View Related

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

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 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

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

Reporting Services :: Table Data Types For Data Driven Subscriptions

Jun 11, 2015

I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better. 

View 4 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

Dynamic Table Name (was Please Help !! SQL)

Mar 2, 2005

Hi--

I am new to T-SQL i got this code from some website but its not working can anyone let me know why
@cur_tab_name varchar(30) is not decleared while i have decleared
I want to use this for dynamic name of the table.

Thanks.






create table temp_tab
(
tab_name varchar(30),
no_of_rows INTEGER,
)

DECLARE
curREVIEW

CURSOR FOR
select name
from sysobjects
where xtype = 'U'


DECLARE @cur_tab_name varchar(30)

OPEN curREVIEW

FETCH curREVIEW INTO @cur_tab_name

WHILE (@@FETCH_STATUS =0)
BEGIN
DECLARE @count integer

select @count = count(*) from @cur_tab_name
INSERT INTO temp_tab
(@cur_tab_name, @count)

FETCH curREVIEW INTO @cur_tab_name

END

CLOSE curREVIEW

DEALLOCATE curREVIEW

View 1 Replies View Related

Dynamic Table Name

May 16, 2007

Hi..,

I want to know how to create a DTS for dynamic tables..,
Details:
1)I have some tables which created every month(Like [Name][2 char of month][2 char of year] eg: name0206 this table is for feburary 2006),i want to create dts for those tables

View 3 Replies View Related

Dynamic Table Name And Fields

Jan 13, 2007

I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName.  Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name.  So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop  that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email

View 3 Replies View Related

Dynamic Table Creation

Apr 19, 2007

Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
  This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks. 

View 1 Replies View Related

Dynamic Table Name Creation...

Jun 19, 2007

Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot  declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here 

View 6 Replies View Related

Dynamic Table Name With GetDate()

Jun 17, 2008

I'm trying to work on an Integration Services project, and want to have a table copied into another table, but this destination table needs a datetimestamp in the name, like tbl061708547pm.
I can get this to work, but if I try to use it with an into statement and a "+", I get an error telling me something is wrong around the "+":
DECLARE @Date char(23)SET  @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)SELECT colA, @Date  FROM myTBL
*****Error with:DECLARE @Date char(23)SET  @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)SELECT * into @Date  FROM myTBL
...and error with:DECLARE @Date char(23)SET  @Date = CONVERT(char(23), GETDATE(), 14)SELECT * into 'myTBL_ + @Date FROM myTBL;

View 2 Replies View Related

Dynamic SQL And Table Variables...?

Aug 23, 2004

Hello, all. I'm attempting to insert data into a table variable using dynamic SQL created at runtime.

However, with a Table variable, SQL server will not allow the EXEC method to be used in an INSERT statement.

How do I go about this?

View 2 Replies View Related

Dynamic Sql - Inserting Into A Table

Jun 22, 2001

Hi There,
I am trying to write dynamic sql which will insert a row into a table.
However, I cannot seem to get the syntax correct.

I will be passing the name of one of the fields to be added and the value
for that field.

If anyone has any suggestions, they would be appreciated.

CREATE PROCEDURE dbo.Add_Daily_Activity_Stats(
@Field_NameVARCHAR(25),
@Team_IdINTEGER,
@TotalINTEGER
)
AS

EXEC ("INSERT INTO Daily_Activity_Stats
(
Date,
Team_Id,
'+@Field_Name+'
)
VALUES
(
GETDATE(),
@Team_Id,
@Total
)
)"

View 3 Replies View Related

Dynamic SQL- Create Table

Sep 26, 2001

Is it possible to create a temp table using exec. e.g

exec('create table #temp1 (xyz int, abc int)')

when I run the above statement, I dont get an error but nothing happens.

The reason I need to create it dynamically is that in my stored proc, I am passed a number. This number determines how many columns my temp table should have.
thanks
Zoey

View 2 Replies View Related

Dynamic Table Naming From Sp

Sep 22, 2000

Does anyone know if it is possible to create dynamically named tables from within a stored procedure? The goal is to append a unique identifier on the end of an otherwise static table name to allow for multiple incarnations of the table to exist concurrently while not interfering with each other. For example, we would like to create and use a temp table that is suffixed with a login name to or a timestamp to make it unique.

Is this possible?

View 6 Replies View Related

Temp Table Using Dynamic Sql?

May 19, 2005

Does TSQL limits us on creating temp table using dynamic sql? If so any workaround... Here's the sample code that doesn't let me run second exec because it looks like first exec is not able to create a temp table.

declare @str1 varchar(80),@str2 varchar(80)

set @str1='create table #tmp(col1 int)'
set @str2='insert into #tmp values (10)'

exec (@str1)
exec (@str2)

View 2 Replies View Related







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