Post reply

Lost and confused in ssrs and sql

  • February 6, 2010 at 1:20 pm

    #220418

    Hello everyone.

    I am using Sql 2000 with SSRS 2005. I have a table, SO.MAIN which is an order listing containing many columns of data. Including:

    ENTRY.DT (entry date)

    SO.ID (sales order number)

    DIST.ID (distributor number)

    ORD.NET (order value).

    I need to create an SSRS report that counts the unique orders, unique distributors, and the sum of the order value; and displays this information for a period that is selected by the user. The requirement is also to show the Last Month to date, Year to Date, and Last year to date based on whatever timeframe they chose originally.

    The caveat is that it cannot be a specific month, the user has to be able to enter a start and end date that varies in format. One manager users calendar dates, another fiscal dates, and another catalogue live dates.

    I can do the simple queries, but I cant figure out the parameters, or how to get this all in one query. I tried doing a query in management studio, using set parameters and union all, and this worked. When I copy the query to SSRS it tells me there's invalid syntax at union, and it fails.

    I am new, and I realize this is alot to ask for in a newbie post. But I believe that if I can get this one report created, plus, understand the mechanics and logic of it; that it will set me in full motion to complete a series of other reports utilizing the same time logic.

    Any help is appreciated.

    Thank-you

    Tammy

  • February 7, 2010 at 11:54 am

    #1115234

    I'm not 100% how to do this in SSRS, but if you have things working in SSMS, you could create a view with your unions and then query that from SSRS.

  • February 7, 2010 at 2:02 pm

    #1115251

    Write the query in a Stored Procedure and pass the relevant parameters to it from the report.

    Our reports are written either through SPs or Views to the data.

  • February 7, 2010 at 2:11 pm

    #1115255

    You said the dreaded SP word to a newbie! 🙂

    I've never done a stored procedure before, I'll google and see if I can make sense of it. My worry is that if I cant make it work in a sql query, I'm not sure I can make it work in a SP.

    Thanks

    Tammy 🙂

  • February 7, 2010 at 2:39 pm

    #1115261

    http://www.sqlservercentral.com/articles/Video/63805/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply