dcsimg
Results 1 to 10 of 10

Thread: Is this posible?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    80

    Is this posible?

    Hi everyone, I have a SQL local database that has many records on predators (animal kind) that go's back to year 2002. The records are entered sequentially, IE "Rats", "Weasels"," Stoats", Possums".

    What I want to know is: Is it possible to create a report for each year for each Predator?

    I have not much experience using reports, but have watched a few you-tube videos, but have not found anything like I need.

    Attached is an XL spreadsheet of the kind of data that I need.

    Thanks
    Attached Images Attached Images  

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: Is this posible?

    What database are you using and what reporting medium? And could you provide us with a sample of the data as it exists in the database?


    Basically, I think you're looking for a PIVOT. The syntax for that will change from database to database and you co it either in the SQL query or in the report itself. So we really need the above two questions answered before we can direct you.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    80

    Re: Is this posible?

    Quote Originally Posted by FunkyDexter View Post
    What database are you using and what reporting medium? And could you provide us with a sample of the data as it exists in the database?


    Basically, I think you're looking for a PIVOT. The syntax for that will change from database to database and you co it either in the SQL query or in the report itself. So we really need the above two questions answered before we can direct you.
    OK, the datafile type is a local database ".sdf". I could send you the database in it's entirety, but I only use one part of it for the yearly report that I am trying to write.
    I am trying to use the Microsoft Report-viewer. I do have Crystal Reports as well, but are Not really that familiar with either.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: Is this posible?

    The file extension isn't particularly useful because it can apply to several different formats. From the options on the other end of that link my guess is that you're using SQL Server Compact. Does that sound right?

    I'm afraid Compact doesn't formally support a Pivot so that would leave you with 2 choices:-
    1. You can do the equivalent of a pivot using SUM and CASE. See the answer given here.
    2. You can shape the data in your report rather than in your SQL. I think this is probably the option I'd recommend. See below.

    I haven't touched Crystal for at least 2 decades so I can't speak for that. Report Viewer isn't a technology for designing reports, it's a technology for displaying them. I assume that, if you were using the Report Viewer, you would probably be designing an SSRS report using either Visual Studio or Report Builder. If so the output you're looking to achieve is reasonably easy:-
    1. Add a Matrix (not a Tablix ) to your report.
    2. Give it a Row Grouping something like "Year(RecordDate)"
    3. Give it a Column Grouping something like "PredatorName"
    4. Give the text cell a function something like "Sum(Quantity)"

    Those details may be subtly incorrect because I don't know your table structure, field names etc. but hopefully it'll get you pointing in the right direction. If you want more specific help you'll need to provide sample data.


    edit>
    I only use one part of it for the yearly report that I am trying to write
    That's the bit we'd need to see. Just type some sample data into the forum so we can see what it looks like. What would be really useful would be if you could provide some appropriate CREATE TABLE and INSERT statements we could use to create the equivalent of your real data.
    Last edited by FunkyDexter; Aug 1st, 2019 at 05:02 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    80

    Re: Is this posible?

    Quote Originally Posted by FunkyDexter View Post
    The file extension isn't particularly useful because it can apply to several different formats. From the options on the other end of that link my guess is that you're using SQL Server Compact. Does that sound right?

    I'm afraid Compact doesn't formally support a Pivot so that would leave you with 2 choices:-
    1. You can do the equivalent of a pivot using SUM and CASE. See the answer given here.
    2. You can shape the data in your report rather than in your SQL. I think this is probably the option I'd recommend. See below.

    I haven't touched Crystal for at least 2 decades so I can't speak for that. Report Viewer isn't a technology for designing reports, it's a technology for displaying them. I assume that, if you were using the Report Viewer, you would probably be designing an SSRS report using either Visual Studio or Report Builder. If so the output you're looking to achieve is reasonably easy:-
    1. Add a Matrix (not a Tablix ) to your report.
    2. Give it a Row Grouping something like "Year(RecordDate)"
    3. Give it a Column Grouping something like "PredatorName"
    4. Give the text cell a function something like "Sum(Quantity)"

    Those details may be subtly incorrect because I don't know your table structure, field names etc. but hopefully it'll get you pointing in the right direction. If you want more specific help you'll need to provide sample data.


    edit>That's the bit we'd need to see. Just type some sample data into the forum so we can see what it looks like. What would be really useful would be if you could provide some appropriate CREATE TABLE and INSERT statements we could use to create the equivalent of your real data.

    OK, sometimes a picture is better than words (hope this is the case here)
    Attached Images Attached Images    

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: Is this posible?

    OK, there's a few ways to skin it but here's what I'd do (assuming an SSRS report).

    1. create a dataset in your report using the following query:-
    Code:
    Select DatePart(year, [Date]) as [Year], Critter, Sum(QTY) as QTY From FlxLastTime Group By DatePart(year, [Date]), Critter
    2. Add a Matrix to your report.
    3. Set its datasource property to your data set
    4. Set its Row Group to Year
    5. Set its Column Group to Critter
    6. Set its cell formula to QTY

    That should be everything you need.



    NB, You might spot that I did the grouping and summing in sql. This is to minimise the amount of data that is passed across your network. The pivoting is taken care of automatically by the column Grouping on the Matrix.
    Last edited by FunkyDexter; Aug 1st, 2019 at 06:03 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    80

    Re: Is this posible?

    Thank you very much, it's very late here where I live, so I will give a go in the morning, hopefully you are right, I look forward to trying it tomorrow.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    80

    Re: Is this posible?

    Thank you very much, it's very late here where I live, so I will give a go in the morning, hopefully you are right, I look forward to trying it tomorrow.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: Is this posible?

    You'll probably have to fiddle a little. I don't have a copy of Visual Studio in front of me so can't try it out. It should be pretty close to what you need though.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    80

    Re: Is this posible?

    Thanks, will give it a good try in the morrow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width