Solved

SQL 2005 reporting services Previous function, scope parameter not working.

Posted on 2009-04-13
8
1,857 Views
Last Modified: 2012-05-06
I am trying to write a report that takes a value from the previous row and subtracts it from the value in the current row. I used the previous function to get the value from the previous row. Previous(Fields!num.Value). I soon found that becuse of my grouping the first row in the next group was getting the value of the last row in the previous group. The documentation shows that previous has 2 contructors. you can pass just the value as object or the object and scope as string. So I put in =Previous(Fields!Num.Value,"tabel1_Name") It doesn't show any errors when I save it, but when I preview the report it does not compile saying he Value expression for the textbox textbox9 has an incorrect number of parameters for the function Previous. I'm guessing the documention is wrong. Does anyone now how to correctly pass the scope to the previous function? Is there an additional parameter I need to pass?
0
Comment
Question by:lyptus
  • 4
  • 4
8 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24133590
I'm not an expert on SQL reporting services, but let me see if I can lead you in the right direction.  First, I would say that the documentation is 'probably' not wrong.  Secondly, as you saw you can't access the previous item in the table when you are grouping.

You could consider either using a subquery that isn't grouped where you can add the field you need.  Or you could just create a temporary table that adds the field you need.  

However, what you are asking for doesn't sound like it makes sense.  When you group something you are getting one row for the entire GROUP, so it wouldn't make sense to take data for the entire group and modify it by a single, previous record.  If your calculations are recursive (I'm using this term liberally as I don't have a better word at the moment.  But what I mean is that each calculation on a row is using the calculation from the last row), then you probably need an aggregate function.

Tell me the bigger picture of what you're trying to accomplish and I'll see if I can help further.
0
 

Author Comment

by:lyptus
ID: 24133928
Here is what the report renders.

Name(maxtime)         DateTimeOfCheck         PreviousRowDatetime.  
C4 - Brett (30)      
      04/13/2009 13:36:36  
      04/13/2009 13:42:41     04/13/2009 13:36:36                    
      04/13/2009 15:05:51     04/13/2009 13:42:41                    
     
CELL D-Brett (15)    
      04/13/2009 13:36:43     04/13/2009 15:05:51                  
      04/13/2009 13:42:29     04/13/2009 13:36:43                
      04/13/2009 15:05:43     04/13/2009 13:42:29
      04/13/2009 15:05:57     04/13/2009 15:05:43
     
CELL E-Ken  (30)  
                                            04/13/2009 15:05:57

The grouping is on the cell name. The first row of dates is the date that cell was checked. the 2nd row of dates is the date from the previous row. C4 was checked 3 times so it has 3 dates. the 2nd column then shows the previous date. The 2nd column of dates is blank in the first row because there is no previous date. Cell E at the bottom has never been checked so it does not have a date in the first column. But you can see it is grabbing the previous date from the Cell D group. I was hoping I could use the scope parameter to limit the scope to each group of names. My ultimate goal is to subtract the date time from the previous date time and verify that it is within the time frame of the MaxTime field for that location.
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24134605
You're looking at documentation for 2008, but you're using 2005.  That's why it doesn't work.  Can you upgrade to 2008 or do we need to find another solution?
0
 

Author Comment

by:lyptus
ID: 24137387
I can probably upgrade to 2008 if I have to, but I would rather not. I currently don't have a copy so I would have to buy one. The documentation I am referring to is the pop up that comes up when I type the parenthesis after Previous. So I type Previous( and a window pops up saying what can be passed. I have attached a screen shot. If there is a solution that will work for 2005, that would be great.
previous.png
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 5

Accepted Solution

by:
brandonvmoore earned 500 total points
ID: 24142435
Are you using SQL 2k5 express, developer, or standard/enterprise?  If express, I would definitely recommend upgrading since it's free to download.  If developer then it's only 45 bucks on amazon, and possibly you could find it cheaper on ebay.  If standard/enterprise then yeah, definitely not worth upgrading over this.

I looked at the docs online and for 2005 it didn't have the overloaded function, while for 2008 it did.  My first question whether you installed SQL 2005, management studio, and the docs from the same location.  Possibly you downloaded them at different times and got the newer docs instead of 2005 docs?  Other than that I don't know, but the online docs don't have it and you're obviously getting an error so I'd say that's a strong indication that you can't do it in 2005 :(

Ok, so do you have much or any experience writing TSQL?  If you're not opposed to spending a few hours studying  you could buy a book, (or read about it online for free).  There are various ways you could solve this problem with TSQL.  Namely, it gives you the ability to traverse the table one record at a time and do whatever processing you need to do as you go.  You have a lot more control this way b/c you're not having to apply functions equally to everything (ie. instead of blindly checking the previous row 'every' time, you could perform a check to see whether the cell has changed first).

My gut feeling is that there probably is a simpler solution, but I don't know what it is unfortunately.  The fact that sql 2008 has that scope parameter added also tells me that maybe there isn't a simpler solution though.

Two more places you should search and post this question:
SQLServerCentral.com
The appropriate microsoft newsgroup.  Just google Microsoft SQL newsgroups and then find the one that fits.

Hope that helps.
0
 

Author Comment

by:lyptus
ID: 24143371
I am using Visual Studio 2005 Professional to build the report and SQL 2005 standard for sql server. would I have to upgrade VS or SQL or both to 2008? I am only somewhat familiar with TSQL. I will look into that as well.
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24143506
You would only have to upgrade SQL Server.  If you've got an action pack subscription or can otherwise obtain a licensed copy w/o having to pay full price for it that would be ideal, but at almost a thousand dollars I'd say it's not worth it just for this.

However, SQL 2008 Express (which is free) does include the reporting capabilities and will run side by side with SQL 2005.  If you aren't using any features that are exclusive to the licensed versions of SQL Server then you could move your whole project to the free version of 2008.  Alternatively, you could attach a copy of the database (or just the tables you need for reporting) to 2008 and use replication to keep it synched up with the main database.  One cool thing about doing this is that you could have your SQL 2008 Express engine on a seperate computer so that when people run reports the report processing doesn't affect performance on the main SQL Server engine.  I'm guessing this probably isn't a big deal for you, but I thought I would point out that it is a common solution that people who use reporting features use to increase performance.
0
 

Author Closing Comment

by:lyptus
ID: 31569707
Thanks for your help. After looking at TSQL I think this is the route I will take. Thanks again.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

27 Experts available now in Live!

Get 1:1 Help Now