Reporting based upon a search for a Company

  • Hi,

    I have searched this forum and the web in general for answers to this issue, and came up empty.

    In our legacy web application the users have the ability to use a lookup to search for a Customer company to generate a report on. The lookup is a text field that causes a separate process to fire (in an iframe) and search for company names that contain the search string. Once the user selects this company from the search results, they can then click Run to generate the report and it passes the company id to the report stored procedure.

    I have started to look into subreports as a way to do this in SSRS but am wondering if there is some functionality already built into SSRS the provides an equivalent search experience as a way to load parameters without having to reinvent the wheel.

    To clarify, i'm not expecting to duplicate the iframe functionality. Just the ability to type a partial company name into a text field and have a recordset display that the user can select one company from which populates the company parameter in the report and then the search results go away.

    Thanks in advance.

  • An easy solution would be 2 reports.

    The first report is your company search that results in a list of companies matching the search criteria. Then add an action to the textbox of the result set on this report that will take the chosen company and push it to the second report as an input parameter.

  • Daniel, Thanks very much for responding. I'm wondering now if this is a normal way reporting gets done? It works for a web application, but doesn't appear to translate very well to SSRS.

    To me it seems logical that a system could have a series of standard reports not based upon a specific account that would allow the reporting user to "look up" the account to report on, i.e., a Customer Monthly Sales Summary. If we have hundreds of Customer companies, then prepopulating a parameter dropdown or something like that doesn't make a whole lot of sense.

    Do you have experience with, or know this is accomplished without having to build another report?

  • John-150025 (1/29/2012)


    Daniel, Thanks very much for responding. I'm wondering now if this is a normal way reporting gets done? It works for a web application, but doesn't appear to translate very well to SSRS.

    To me it seems logical that a system could have a series of standard reports not based upon a specific account that would allow the reporting user to "look up" the account to report on, i.e., a Customer Monthly Sales Summary. If we have hundreds of Customer companies, then prepopulating a parameter dropdown or something like that doesn't make a whole lot of sense.

    Do you have experience with, or know this is accomplished without having to build another report?

    I do consider reporting services to be a web application. The most common deployment is to the Report Manager which is accesses via a browser, or SharePoint which is also a browser based application.

    I agree, that the best approach is to have reports that are not account specific, and use some sort of parameter to feed the report. The form that parameter feed could take has several possibilities.

    The first possibility I mentioned originally is a search report where you put in a key word or part of a company name that results in a simple tabular list of matches where you could then click on the appropriate company and run the report you like. If you have multiple reports per company, the "lookup/search" report could have an expanded table that would offer a selection of reports for each company across the columns. You could also do this with a sub report, but personally I think a separate report is cleaner.

    Another possibility would be to have a report that is essentialy a directory of companies sorted alphabetically with an alphabet document map to make jumping to the proper area easier. Again, in this situation you will be dependant on a report action to jump over to the specific company report.

    If you only have hundreds of customers, you might find that a parameter with a drop down list is not as unweildy as you expect. I have a few reports that have some rather long drop down lists (many hundreds and perhaps one or two in the low thousands) that the users navigate with reasonable ease. This also prevents misspellings. Although this is rarely my first preference for long lists sometimes it is the most practical.

    Without knowing anything about your data I don't know if this is practical but you might also consider a set of cascading parameters that filter down the company choices to a more reasonable drop down length. For example, if your companies are geographical in nature, perhaps a regional parameter, with a subsequently dependant state parameter and then finally a dependant company parameter that shows only those companies in that region in that state.

  • Daniel,

    Thanks so much for the information. I will develop this with the look up functionality in a separate report.

  • Create a text box parameter called searchstring so the user can enter the search string they want.

    Create a dataset with the following condition:

    select * from <yourtable> where <yourfield> like '%' + @searchstring + '%'

    Create one further parameter based upon that dataset which will return a filtered list of companies based upon what you entered as a search string.

    From there you can use the company selected to run the rest of the report.

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

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