Solved

Report Model Design for specific transactional data

Posted on 2010-09-16
19
282 Views
Last Modified: 2012-05-10
Hi,
I was wondering if some experts could give any advice on how to design a report model, or even if it is actually possible to design a report model to satisfy the below reporting requirement:

It is required to get the following data in a single matrix like the below(this example is for just one asset, but the report will have to work for numerous assets)  if the user passes in a start date parameter of 31/07/2010 and an end date parameter of 25/08/2010.
That is to say, the report must return the meter read record exactly on the start date passed in, or if no meter read record exists for an asset, for the start date passed in, it should return the very last meter read record, on a date just before the start date passed in
---------------------------------------------------------------------------------------------------------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
---------------------------------------------------------------------------------------------------------
| PAQ23487       |   30/07/2010         |  20730        |  25/08/2010        |  20893      |   163     |
---------------------------------------------------------------------------------------------------------

and the underlying raw data exists in a SQL Server database table like the following

Asset_Number      Read_Date      Meter_Read
PAQ23487                            30/07/2010      20730
PAQ23488                           02/08/2010      20743
PAQ23489                           03/08/2010      20749
PAQ23490                           04/08/2010      20750
PAQ23491                           05/08/2010      20765
PAQ23492                           06/08/2010      20766
PAQ23493                           09/08/2010      20780
PAQ23494                           10/08/2010      20794
PAQ23495                           11/08/2010      20800
PAQ23496                           12/08/2010      20805
PAQ23497                           13/08/2010      20819
PAQ23498                           16/08/2010      20826
PAQ23499                           17/08/2010      20829
PAQ23500                           19/08/2010      20851
PAQ23501                           20/08/2010      20868
PAQ23502                           23/08/2010      20872
PAQ23503                           24/08/2010      20881
PAQ23504                           25/08/2010      20893

Any advice appreciated

0
Comment
Question by:Barry Cunney
  • 8
  • 6
  • 5
19 Comments
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33691104
Sounds like a good use of the MAX statement.
SELECT StartData.Asset_Number, StartData.Read_Data, StartData.Meter_Read,
    EndData.Asset_Number, EndData.Read_Data, EndData.Meter_Read,
from <your table> StartData
    INNER JOIN (
        SELECT Asset_Number, MAX(Read_Date) Read_Date
        FROM <your table> 
        WHERE Read_Date < @StartDate 
        GROUP BY Asset_Number) MaxStartDate ON StartData.Asset_Number = MaxStartDate.Asset_Number
            AND StartData.Read_Date = MaxStartDate.Read_Date
    INNER JOIN <your table> EndData ON StartData.Asset_Number = EndData.Asset_Number
        INNER JOIN (
            SELECT Asset_Number, MAX(Read_Date) Read_Date
            FROM <your table> 
            WHERE Read_Date < @StartDate 
            GROUP BY Asset_Number) MaxEndDate ON EndData.Asset_Number = MaxEndDate.Asset_Number
                AND EndData.Read_Date = MaxEndDate.Read_Date

Open in new window

0
 
LVL 13

Expert Comment

by:p_nuts
ID: 33691129
it would be a query like

select top 1 asset_number, read_date, meter_read from table where read_date <= @userDate and asset_number = @assetNumber order by read_date desc

that would give the reading on that date or the reading before that..

if you want it for more than 1 asset it would be

select asset_number, max(read_date) as ReadDate, max(meter_read) as reading from table
where read_date <= @userdate
group by asset_number
0
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33691131
I missed the volume column in your report sample, but that would be just subtracting the StartData.Meter_Read from the EndData.Meter_Read:
SELECT StartData.Asset_Number, StartData.Read_Date Start_Date, StartData.Meter_Read Start_Meter_Read, EndData.Read_Date End_Date, EndData.Meter_Read End_Meter_Read, EndData.Meter_Read  - StartData.Meter_Read Volume
FROM <your table> StartData
    INNER JOIN (
        SELECT Asset_Number, MAX(Read_Date) Read_Date
        FROM <your table> 
        WHERE Read_Date < @StartDate 
        GROUP BY Asset_Number) MaxStartDate ON StartData.Asset_Number = MaxStartDate.Asset_Number
            AND StartData.Read_Date = MaxStartDate.Read_Date
    INNER JOIN <your table> EndData ON StartData.Asset_Number = EndData.Asset_Number
        INNER JOIN (
            SELECT Asset_Number, MAX(Read_Date) Read_Date
            FROM <your table> 
            WHERE Read_Date < @StartDate 
            GROUP BY Asset_Number) MaxEndDate ON EndData.Asset_Number = MaxEndDate.Asset_Number
                AND EndData.Read_Date = MaxEndDate.Read_Date

Open in new window

0
 
LVL 13

Expert Comment

by:p_nuts
ID: 33691149
sorry more easy with the min date too


select asset_number, min(read_date) as firstdate, min(meter_read) as first reading, max(read_date) as ReadDate, max(meter_read) as reading from table
where read_date <= @userdate
group by asset_number
0
 
LVL 13

Expert Comment

by:p_nuts
ID: 33691168
I've missed the volume too..

select asset_number, min(read_date) as firstdate, min(meter_read) as first reading, max(read_date) as ReadDate, max(meter_read) as reading
, max(meter_read) - min(meter_read) as volume
 from table
where read_date <= @userdate
group by asset_number
0
 
LVL 13

Expert Comment

by:p_nuts
ID: 33691188
I don't see why you need to do the 2 joins..

0
 
LVL 13

Expert Comment

by:p_nuts
ID: 33691221
fixed a small typo and added the select asset

just take out the -- if you want to be able to select on 1 or more asset_numbers


select 
      asset_number
      , min(read_date) as firstdate
      , min(meter_read) as firstreading
      , max(read_date) as ReadDate
      , max(meter_read) as reading 
      , max(meter_read) - min(meter_read) as volume
from table 
where read_date <= @userdate 
 -- and asset_number in (@assetNumber) 
group by asset_number

Open in new window

0
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33691261
There are two reasons for the joins:
1.) There are two date parameters, a Start Date and an End Date, so you would need two different sets of data.
2.) There is no guarantee that the Max Reading will occur on the Max Date. The sample data may appear that way, but there was nothing stated that the meter reading can never be lower (though it would be assumed as such).

I noticed I missed the End Date parameter and corrected my SQL.
SELECT StartData.Asset_Number, StartData.Read_Date Start_Date, StartData.Meter_Read Start_Meter_Read, EndData.Read_Date End_Date, EndData.Meter_Read End_Meter_Read, EndData.Meter_Read  - StartData.Meter_Read Volume
FROM <your table> StartData
    INNER JOIN (
        SELECT Asset_Number, MAX(Read_Date) Read_Date
        FROM <your table> 
        WHERE Read_Date < @StartDate 
        GROUP BY Asset_Number) MaxStartDate ON StartData.Asset_Number = MaxStartDate.Asset_Number
            AND StartData.Read_Date = MaxStartDate.Read_Date
    INNER JOIN <your table> EndData ON StartData.Asset_Number = EndData.Asset_Number
        INNER JOIN (
            SELECT Asset_Number, MAX(Read_Date) Read_Date
            FROM <your table> 
            WHERE Read_Date < @EndDate 
            GROUP BY Asset_Number) MaxEndDate ON EndData.Asset_Number = MaxEndDate.Asset_Number
                AND EndData.Read_Date = MaxEndDate.Read_Date

Open in new window

0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 33691411
Hi Guys,
I already have a SQL statement written(see attached)that gives me what is required if I run it on the records in the SQL Server database table, but how does one design a report model so that people who are Report Developers can design reports in BIDS/Report Builder 2.0 to achieve the same result as the attached SQL statement?
DECLARE @parameter_Start_Date datetime;

DECLARE @parameter_End_Date datetime;



SET @parameter_Start_Date = '2010-07-31'

SET @parameter_End_Date = '2010-08-25'



-- Get Start Meter Read, End Meter Read and Volume based on dates above

SELECT DISTINCT Asset_Number,

(SELECT MAX(Read_Date) FROM tbl_meter_read_hist m1 WHERE m1.Asset_Number = m2.Asset_Number AND m1.Read_Date <= @parameter_Start_Date) Start_Read_Date,

(SELECT Meter_Read FROM tbl_meter_read_hist m3 WHERE m3.Asset_Number = m2.Asset_Number AND m3.Read_Date = (SELECT MAX(Read_Date) FROM tbl_meter_read_hist m1 WHERE m1.Asset_Number = m2.Asset_Number AND m1.Read_Date <= @parameter_Start_Date)) Start_Read, 

(SELECT MIN(Read_Date) FROM tbl_meter_read_hist m4 WHERE m4.Asset_Number = m2.Asset_Number AND m4.Read_Date >= @parameter_End_Date) End_Read_Date,

(SELECT Meter_Read FROM tbl_meter_read_hist m5 WHERE m5.Asset_Number = m2.Asset_Number AND m5.Read_Date = (SELECT MIN(Read_Date) FROM tbl_meter_read_hist m4 WHERE m4.Asset_Number = m2.Asset_Number AND m4.Read_Date >= @parameter_End_Date)) End_Read, 

(

	(SELECT Meter_Read FROM tbl_meter_read_hist m5 WHERE m5.Asset_Number = m2.Asset_Number AND m5.Read_Date = (SELECT MIN(Read_Date) FROM tbl_meter_read_hist m4 WHERE m4.Asset_Number = m2.Asset_Number AND m4.Read_Date >= @parameter_End_Date)) 

	-

	(SELECT Meter_Read FROM tbl_meter_read_hist m3 WHERE m3.Asset_Number = m2.Asset_Number AND m3.Read_Date = (SELECT MAX(Read_Date) FROM tbl_meter_read_hist m1 WHERE m1.Asset_Number = m2.Asset_Number AND m1.Read_Date <= @parameter_Start_Date))

) Volume

FROM

tbl_meter_read_hist m2

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33691570
If your report developers are using BIDS, you can create a stored procedure for your code and the developers can use your stored procedure as the data source of their report. The Report Models use table and view objects from your database, which will make it difficult to use your code as part of the Report Model (mostly because of the need for start and end date parameters.)
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 33691832
Thanks Shaun,
So I would have to create a stored procedure which resides in my SQL Server database?

What would have to be then done so as when the developers are using BIDS, they could then see this stored procedure as a data source?

Could they also use the stored procedure if using Report Builder 2.0 and using a Report Model or can we categorically say that such a stored procedure(with my T-SQL above), or what the stored procedure does could not actually be implemented in a report model(Entities etc.)?
0
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33692568
If the developers use the wizard to build the report, they will be presented with a screen titled "Design the Query" (at least that is what it is titled in 2005) asking for T-SQL statement to use as the data source. They should enter the T-SQL statement just like how you would enter it in the Query editor in SSMS.

Because the Report Model only uses Tables and Views, and because your stored procedure would require two parameters (so it cannot be re-written as a view), I do not believe it can be included in the Report Model.
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 33693306
Thanks Shaun,
Is there a way to include the same table twice in a Report Model so that there are 2 linked Entities for the same table? The reason I am asking is I wondering if this was possible, that developers could then use the first entity to get the start meter read and use the second entity to get the end meter read and link them in a single dataset and therfore get in a single matrix what is required
---------------------------------------------------------------------------------------------------------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
---------------------------------------------------------------------------------------------------------
| PAQ23487       |   30/07/2010         |  20730        |  25/08/2010        |  20893      |   163     |
---------------------------------------------------------------------------------------------------------
0
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33693413
If you create a view on the table, you would then be able to add the view to the model.
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 33699929
Thanks Shaun,
Do you think would it be possible to create 2 views - Both views would look at the meter_read table?
Then could these 2 views be added to the report model but that they could be linked by asset_number and so then it would be possible tp get the result when designing a report?

---------------------------------------------------------------------------------------------------------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
---------------------------------------------------------------------------------------------------------
| PAQ23487       |   30/07/2010         |  20730        |  25/08/2010        |  20893      |   163     |
---------------------------------------------------------------------------------------------------------
I should have qualified in the beginning that I am new totally new to Report Models so if I am asking a totally invalid question, let me know.

I have good SQL Server/T-SQL expertise and expertise in Report Builder on the client side but not Report Models or Reporting services, but the above is a real-life requirment that I have encountered and I am trying to get an idea on any possibilities on how to approach it - but I do appreciate it, that it is kind of a big question I am asking, but any advice appreciated - Thanks
0
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 33700651
Though you would be able to create the two (or more) views and add them to the Report Model, I believe it would be difficult (maybe even impossible) to use the model to generate the report that you require. The Report Model, in conjunction with Report Builder, allows general users to quickly build adhoc reports. I do not believe that Report Builder has the capability to find the first meter_reading that occurs prior to a date. (To put into T-SQL terms, I do not believe you can do the correlated queries that you used in your T-SQL or the sub-queries that I used in my T-SQL with Report Builder/Report Model.)
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 33701995
Thanks very much Shaun,
I was sort of starting to think that may be the case(that this just is not possible in a Report Model) even with my limited expertise, but it's great to get an expert opinion on it.
So really the only way to go is to create a stored procedure with the T-SQL I already have and which I know gives the result required and allow developers using Report Builder 2.0 to use Stored Procedures as a Query type and be able to select this Stored Procedure I create, when creating datasets?
0
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 33906385
Sorry for the delay in responding. To answer your question, if when you refer to Report Builder you mean BIDS (SQL Server Business Intelligence Development Studio) then yes. You cannot add stored procedures to a Data Model, therefore you would not be able to use the stored procedure using the Report Builder available through Reporting Services 2005.
0
 
LVL 17

Author Closing Comment

by:Barry Cunney
ID: 33907767
Thanks Shaun
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

14 Experts available now in Live!

Get 1:1 Help Now