Solved

Adding Table in ssrs

Posted on 2009-05-04
18
933 Views
Last Modified: 2013-11-10
I am want to add to separate that I want to add together and get the sum.
seems like this will work.

= sum(Fields!Volume.Value,"DataSetGeoMetInlet" + Fields!Volume.Value, "DataSetClassicInlet")

Here is the error.
[rsInvalidAggregateScope] The Value expression for the textbox textbox10 has a scope parameter that is not valid for an aggregate function.  The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
[rsInvalidAggregateRecursiveFlag] The Value expression for the textbox textbox10 uses an aggregate expression with an invalid recursive/simple flag.  The valid values for this flag are Recursive and Simple.
Build complete -- 2 errors, 0 warnings


or

=(Fields!Volume.Value,"DataSetGeoMetInlet" + Fields!Volume.Value, "DataSetClassicInlet")

Here is the error.
[rsCompilerErrorInExpression] The Value expression for the textbox textbox10 contains an error: [BC30198] ')' expected.
Build complete -- 1 errors, 0 warnings

nope don't work.

Driving me Nutt's this should easy :)

thanks, Sam
0
Comment
Question by:SamRunyon
  • 9
  • 7
18 Comments
 

Author Comment

by:SamRunyon
ID: 24298173
Let me make things a little more clear.. I have a date/time pick so the total needs to be a daily total the of the tables.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24298836
Sam,

can you share your query, some sample data & what results you are trying to get to?
0
 

Author Comment

by:SamRunyon
ID: 24298885
Sure I have two querys, one for each ssrs table.

Table 1

SELECT     FlowDaily.Volume, FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime, DeviceSummary.MdbDeviceID
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (DeviceSummary.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

Table 2

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) AND (DailyFlowDateTime >= @StartDate) AND
                      (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY TheDate DESC

I am trying to add both querys for one table in ssrs.. but having a problem getting the data i want.

thanks for the help.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 16

Expert Comment

by:Auric1983
ID: 24298936
SamRunyon,

a Table in SSRS requires you to assign it a dataset so in this case it would be one or the other.  

It looks like you are querying from the same table & just using the second table to make your running total for each day is that correct?

If so we can eliminate dataset 2 and do all of the grouping & manipulation right in SSRS
0
 

Author Comment

by:SamRunyon
ID: 24299066
Yes, that is what I am doing.
How would I eliminate the dataset 2?

Sam
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24299091
Setup your table to group on the DailyFlowDateTime by right clicking on the table insert Group use the dailyflow date as your group field.

You will then be able to report by day, the transactions (in the detail line) and the summary flow information in the group header or footer.

0
 

Author Comment

by:SamRunyon
ID: 24299204
Not sure if you understand what I need and I am sorry for not getting the information clear.

This is what I am trying to do.

Table 1              Table 1  Table 2  Table 3
  05/05/09             10          10    =    20
  05/06/09               5            5    =   10

Table 1 is a query by it self. Each other table is separate.
is there no way to just add the tables?

again thanks for your help.
Sam
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24299288
No, the "Table" reporting object uses a single dataset to manage the rows.  The reason being if you had two datatables with different recordcounts how would you relate them so that the reporting tool knew that May 5 in Table 1 is row # X in Tables 2 and 3?

My question would be how are Tables 1 2 and 3 related? is there a way we could normalize the information into one query so that you can do row operations on it?

0
 

Author Comment

by:SamRunyon
ID: 24300742
This is the query.

Table 1

SELECT     FlowDaily.Volume, FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime, DeviceSummary.MdbDeviceID
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (DeviceSummary.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

Table 2

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) AND (DailyFlowDateTime >= @StartDate) AND
                      (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY TheDate DESC

I can figure out how to get my totals (table 2) with and add the other table.
So I can do the math in SSRS.

Sam
0
 

Author Comment

by:SamRunyon
ID: 24300748
Sorry I meant to say

I can't figure out how to get my totals (table 2) with and add the other table.
So I can do the math in SSRS.

Sam
0
 

Author Comment

by:SamRunyon
ID: 24300914
I got it work with this query but I know this is not the right way.

SELECT     CONVERT(varchar, FlowDaily.DailyFlowDateTime, 111) AS TheDate, SUM(FlowDaily.Volume) AS Volume, SUM(FlowDaily.Energy) AS Energy,
                      CONVERT(varchar, FlowDaily_1.DailyFlowDateTime, 111) AS TheDate2, AVG(FlowDaily_1.Volume) AS Volume2, AVG(FlowDaily_1.Energy) AS Energy2
FROM         FlowDaily INNER JOIN
                      FlowDaily AS FlowDaily_1 ON FlowDaily.DailyFlowDateTime = FlowDaily_1.DailyFlowDateTime
WHERE     (FlowDaily.MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) AND (FlowDaily.DailyFlowDateTime >= @StartDate) AND
                      (FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily_1.MdbDeviceID = 1) AND (FlowDaily_1.DailyFlowDateTime >= @StartDate) AND
                      (FlowDaily_1.DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, FlowDaily.DailyFlowDateTime, 111), CONVERT(varchar, FlowDaily_1.DailyFlowDateTime, 111)

any help.

Sam
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24303930
Sam, whats the difference between the devices? are you trying to get a summary for certain devices or what?

Can you give me a sample of the records returned? I have an idea.
0
 

Author Comment

by:SamRunyon
ID: 24304124
Same database What I am doing here is getting a total of the all the meters.


(FlowDaily.MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244))

Now I need the Value of Meter 1

(FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily_1.MdbDeviceID = 1)

then I have to add them together in a ssrs table.

thanks, Sam

0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24304145
Sam,

Can you get the dataset into a form similar to this

MeterID, Date,Volume, Energy

If you can get the "list"  of entries, we can easily do all the manipulation in the SSRS report.

0
 

Author Comment

by:SamRunyon
ID: 24304249
Not sure what you need as a "list"?

Let me show the querys that I need as one Query.
I have two databases.

Data Source=GMWWDB;Initial Catalog=Runtime

SELECT TagName, DateTime, Value = convert(decimal(38, 2), Value), vValue
 FROM History
 WHERE TagName IN ('duke-btu')

now this database is
Data Source=WWSCADA;Initial Catalog=VaTfLt

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY CONVERT(varchar, DailyFlowDateTime, 111) DESC

And I need to get this meter in the query.

SELECT     FlowDaily.MdbDeviceID, DeviceSummary.MdbDeviceID AS Expr1, DeviceSummary.MeterID, DeviceSummary.Location, FlowDaily.Volume,
                      FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

hope this helps.
Sam


0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24304582

Sam,

Sorry for the abundance of questions, I'm just trying to get you the best answer...

The way I'm understanding it is

This query

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY CONVERT(varchar, DailyFlowDateTime, 111) DESC is

is giving you a sum for particular Devices (or all the devices?)

and this one

SELECT     FlowDaily.MdbDeviceID, DeviceSummary.MdbDeviceID AS Expr1, DeviceSummary.MeterID, DeviceSummary.Location, FlowDaily.Volume,
                      FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

Is giving you a list of all entries for the date range.

How is the top query related to this table? does TagName = The DeviceID or MeterID?

The middle query, is probably redundant because you can do the Math operations in the SSRS table.

0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 26405874
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can i use insert and update together by merging two tables? 9 32
TSQL XML Namespaces 7 24
SQL USE DATABASE VARIABLE 5 31
Get Next number from Stored Procedure 8 23
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question