locked
Is it possible to save parametes value, back in database.? RRS feed

  • Question

  • Hi,
    I have requirement like this: I have 10 parameters in a single report and each parametre is having more than 100 values.

    Example:

    Parameter-1->Name
    Parameter-2->Age
    Parameter-3->DOB
    Parameter-4->Address
    Parameter-5->Company
    Parameter-6->Qualification
    Parameter-7->Experience
    Parameter-8->Location
    Parameter-9->Native
    Parameter-10->Country

    So i have selected all 10 parameters value in report and i click on view report and i can see data. What i want now: i want to save these selections as Preference-1 in database. next time when i come to see report and once i select Parameter-1->Name, automatically it will select rest of parameters what i have saved in Preference-1. Like this i want to save parameter selected combination in database.

    Is it possible in SSRS without Dot net or share point?

    If not then is it possible in SSRS with frontend as Dot net or share point? If yes then how? Anyone can explain it step by step.
    Thanks Shiven:)
    Tuesday, October 13, 2009 9:42 AM

Answers

  • Hi S Kumar Dubey,

     

    If I understand correctly, you want to save the selection in a table (Preference) in a database. And next time, when opening the report, all the parameter will be filled based on the Preference.

     

    By default, the Reporting Services is only used for showing information. There does not have option to write data back to the database.

    However, the Report Services has scalability that allows us to embed custom code (or custom assembly) in a report.  With custom code (or custom assembly), we are able to write data back to the database.

    Here are the steps:

    1.       Embed custom code (custom assembly) to write data back to database. The values of the parameters and the current userId are being to save.

    We can use the Golbals variable to get current user ID: User!UserID

    2.       While opening the report, get the values from the saved table based on current userId.

     

    Here is a sample about how to execute Stored Procedure from a report which is similar to this case:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/22b99cff-7201-4fe5-8cfd-701475f61e50/

     

    Please feel free to ask, if you have any more questions.

     

    Thanks,

    Jin Chen


    Jin Chen - MSFT
    • Proposed as answer by Isham HEditor Friday, October 16, 2009 8:34 PM
    • Marked as answer by Jerry Nee Friday, October 23, 2009 7:02 AM
    Thursday, October 15, 2009 3:09 AM
  • Jin, that's a great solution, thanks - I'm doing something similar, hadn't thought of it that way.  But your suggestion gives me a further idea...

    Something else that may work for the OP - if you're passing those parameters to a single stored procedure to get the report data, you could put the code for saving that information to the DB within your sp - no custom code required.  Then you just need to get the information back out as in Jin's step 2.
    • Proposed as answer by Isham HEditor Friday, October 16, 2009 8:34 PM
    • Marked as answer by Jerry Nee Friday, October 23, 2009 7:02 AM
    Thursday, October 15, 2009 9:15 PM

All replies

  • Hi S Kumar Dubey,

     

    If I understand correctly, you want to save the selection in a table (Preference) in a database. And next time, when opening the report, all the parameter will be filled based on the Preference.

     

    By default, the Reporting Services is only used for showing information. There does not have option to write data back to the database.

    However, the Report Services has scalability that allows us to embed custom code (or custom assembly) in a report.  With custom code (or custom assembly), we are able to write data back to the database.

    Here are the steps:

    1.       Embed custom code (custom assembly) to write data back to database. The values of the parameters and the current userId are being to save.

    We can use the Golbals variable to get current user ID: User!UserID

    2.       While opening the report, get the values from the saved table based on current userId.

     

    Here is a sample about how to execute Stored Procedure from a report which is similar to this case:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/22b99cff-7201-4fe5-8cfd-701475f61e50/

     

    Please feel free to ask, if you have any more questions.

     

    Thanks,

    Jin Chen


    Jin Chen - MSFT
    • Proposed as answer by Isham HEditor Friday, October 16, 2009 8:34 PM
    • Marked as answer by Jerry Nee Friday, October 23, 2009 7:02 AM
    Thursday, October 15, 2009 3:09 AM
  • Jin, that's a great solution, thanks - I'm doing something similar, hadn't thought of it that way.  But your suggestion gives me a further idea...

    Something else that may work for the OP - if you're passing those parameters to a single stored procedure to get the report data, you could put the code for saving that information to the DB within your sp - no custom code required.  Then you just need to get the information back out as in Jin's step 2.
    • Proposed as answer by Isham HEditor Friday, October 16, 2009 8:34 PM
    • Marked as answer by Jerry Nee Friday, October 23, 2009 7:02 AM
    Thursday, October 15, 2009 9:15 PM
  • I agree with alicorn. All you have to do is write a sproc and in that sproc, accept passing parameters, and do updates to table in that sproc. This give me ideas too, because I was thinking to store user's id, the report id and those parameters that they last ran the report, and the next time they want to run it, wallah, the default parameters are grabbed from the table. Cool .. thanks for the ideas.


    Chicagoan ...
    Friday, October 16, 2009 8:33 PM
    Answerer