locked
Report Server passing multiple ids RRS feed

  • Question

  • User-309740890 posted

    HI I created a report in Report Server 2005, that requires to pass a member id to retrieve the rest of the data. it works.

    I got a question, lets say I have an excel that has 100 member ids and I need to retrieve the rest of the data, Is it possible to some how import the excel data into a temp table and run the report once and pass in the member ids? to avoid doing 100 times..

    or what would be a solution, please advice, also if you know a good example please share.

    Thanks, Good day,

    Monday, August 15, 2011 12:58 PM

All replies

  • User-830595639 posted

    Hi,

    You need to create a SSIS package which will convert the Excel data to Tabular data, and then you can query with this table. Also you can have parameter based on the Excel sheet. You can make your DataSource based on the Excel sheet.

    Thanks

    Nishant

    Tuesday, August 16, 2011 3:50 AM
  • User567269486 posted

    is your requirement to show all the 100 member ids data on one report  or on seperate reports ?

    Tuesday, August 16, 2011 7:56 AM
  • User-309740890 posted

    thanks for the help,

    [You need to create a SSIS package which will convert the Excel data to Tabular data, and then you can query with this table.]

    -> I have done that before, I imported data from excel to a table. But what if my report is already deployed, Is there a function on the report server that a e.g, sales department can import an excel and run the report? I am a bit confused how to make it automated ....

    //somehow pass in a list of MIDs to Report Server and extra into excel.....

    TP, excel /table that is imported will look like this: // one report. so later, It will be able to export the result into an excel file.

    MID

    1

    2

    3

    and the result will look something like this: //After the report was run.

    MID  | Company Name

    1      | AAAA

    2      | BBBB

    3      | CCCC

    Tuesday, August 16, 2011 12:39 PM
  • User567269486 posted

    The best way to do it would be capture all the id's at one using odbc connection or a textreader if it's a CSV file.

    After doing that convert all the ids into one comma seperated string eg. 1,2,3

    You can now passing this string into a SP or a query and do something like select * from company where id in (1,2,3) and get all the data from a single query.

    Bind that dataset to the report and it's done. Hope this helps,

    Tuesday, August 16, 2011 5:31 PM
  • User-309740890 posted

    Thanks TP, It makes sense. Can you please explain how would I do the first two steps

    The best way to do it would be capture all the id's at one using odbc connection or a textreader if it's a CSV file.

    After doing that convert all the ids into one comma seperated string eg. 1,2,3

    If you know a tutorial that explains the steps, please share

    The rest I got it.

    Thanks agian!

    Tuesday, August 16, 2011 6:33 PM
  • User567269486 posted

    Read the csv file using the function here:

    http://www.akamarketing.com/blog/256-csv-datatable.html

    It's simple and easy to use and understand. After you have the dataset loop through each datarow and create the string 1,2,3. You can again make a new function like:

    string GetCommaSeperatedValues(DataSet ds,string ColumnName) which will return the string 1,2,3.

    Wednesday, August 17, 2011 5:11 AM
  • User-309740890 posted

    Hi TP, so you are saying I need to create a Asp.net web/app to read the file and create the right string? if yes, after i create the string in the web app, how do I pass it to the report ? I m srry for asking so many questions, I am new to reporting....

    and there is no way to add like a browse button on report to attach the files? and run it?

    Thanks for your help,

    Wednesday, August 17, 2011 3:18 PM
  • User567269486 posted

    I assumed you have a web app to run the report.

    Yes create the string in the app and pass the app to the query or stored procedure.

    After you get the results from the query / stored procedure bind the dataset to the report.

    ref : http://www.codeproject.com/KB/cs/reportdisplay.aspx

    Wednesday, August 17, 2011 3:51 PM
  • User-309740890 posted

    final question, I created the web that captures MIDs, when passing them via redirect link

    e.g. Response.Redirect("..../Parameters=true&CID=101) it works,

    but if i try to pass multiple MIDs e.g. Response.Redirect("..../Parameters=true&CID=101,102) it doesn't, shows blank report.

    in the report, the query is select * from customers where mid IN (@CID)..... any ideas? Thanks

    Wednesday, August 17, 2011 4:45 PM
  • User-309740890 posted

    I realized that I need to create a function that splits the values,

    found this url; http://www.bidn.com/blogs/mikedavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs

    When I use it, it stll doesnt work

    I set the @CID paramter value to =JOIN(Parameters!CID.Value,",")

    It works when I run the report in the Preview tab.

    .....any ideas what the format needs to pass when doing response.redirect???

    Wednesday, August 17, 2011 6:03 PM
  • User567269486 posted

    Is the parameter type set to multi-value and is the column type of ID a integer or varchar ? The way you are passing the parameter looks correct.

    Refer this for multi values parameters:

    http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

    Thursday, August 18, 2011 6:05 AM
  • User-309740890 posted

    The @CID parameter (multi-value) is set to string, because I will be passing in this format CID=101,102

    I used the same where clause at in the url:

    WHERE  @CID IS NULL OR CustomerID IN (SELECT * FROM dbo.fn_Split (@CID, ','))

    It works in the preview tab. The type CustomerID in the table is integer, not sure if that matters. Thanks,

    Thursday, August 18, 2011 11:28 AM