Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

Adding Table in ssrs

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
SamRunyon
Asked:
SamRunyon
  • 9
  • 7
1 Solution
 
SamRunyonAuthor Commented:
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
 
Auric1983Commented:
Sam,

can you share your query, some sample data & what results you are trying to get to?
0
 
SamRunyonAuthor Commented:
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Auric1983Commented:
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
 
SamRunyonAuthor Commented:
Yes, that is what I am doing.
How would I eliminate the dataset 2?

Sam
0
 
Auric1983Commented:
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
 
SamRunyonAuthor Commented:
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
 
Auric1983Commented:
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
 
SamRunyonAuthor Commented:
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
 
SamRunyonAuthor Commented:
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
 
SamRunyonAuthor Commented:
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
 
Auric1983Commented:
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
 
SamRunyonAuthor Commented:
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
 
Auric1983Commented:
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
 
SamRunyonAuthor Commented:
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
 
Auric1983Commented:

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
 
ee_autoCommented:
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now