Solved

Adding Table in ssrs

Posted on 2009-05-04
18
919 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now