Passing many unique ids to a linked report

(11 posts) (2 voices)
  1. nsepetys, Member

    Hi myDBR Representative,

    How are we doing? Just wanted to see if there is a way, that myDBR supports in its framework, that allows one to pass a large number of unique ids (integer type) to a linked report? I know the normal convention is to pass the ids as a parameter in the URL, however, the 2,000 character limit imposed by browsers will potentially cause an issue as I want to be able to pass at least a thousand integer values. Even if I compress we're talking about 3,000+ characters. Unfortunately I don't think we can share temp tables between sessions and creating a permanent table for each report generated is not a scalable solution.

    Is there a way that you can think of that would allow me to pass those integers without re-querying the data (which can be very time consuming because of the amount of data being queried)?

    Thanks,
    Noah

  2. myDBR Team, Key Master

    Noah,
    so basically instead of passing the query parameters, you would like to pass the result set ID's in order to avoid re-executing a heavy query?

    Passing thousand integer numbers as parameter will bumb you against maximum URL length of some browsers (notably IE).

    The only viable solution would be to use a link table (normal table), with separate key for identifying the result set ID's (same table could be used with number of users/reports).

    You should also see if you really need to pass thousand parameters to a report, mabye there is another way of doing things.

    --
    myDBR Team

  3. nsepetys, Member

    Yes, that is what I am trying to do. It seems like the only options are creating a table on disk or going back to the drawing board. Thanks for taking a look at this though.

    - Noah

  4. nsepetys, Member

    I'm dredging this up again because I had another thought- would it be possible to store the unique id's in an array in the session cookie and then pass only one id through a URL parameter to call the specific location in the cookie array that contains those, potentially thousands, of integers (needing 1 - 5 MB of storage total)? Let me know if you think this is possible or if I just have a wild imagination.

  5. myDBR Team, Key Master

    Noah,
    what is the use case where you need to pass thoudsands of numbers into a report? Where do these numbers come from and what do they represent?

    --
    myDBR Team

  6. nsepetys, Member

    I am building a large dashboard with several tables. Each table has up to a hundred or so cells that each can have hundreds of unique identifiers behind one rolled-up number. I want most every cell to be expandable so that the user can understand the who/where/what behind the number. The issue is that most of the data is connected to each other so I have to re-use most all queries to generate the data for any cell. The total query time for the dashboard takes a few minutes so I'm trying to avoid re-querying the data for the linked reports that I want to pass the unique identifiers to.

    If storing the unique ids off the database is not possible and I cannot get them without completely re-querying the database then I'll have to kill this feature until I have more resources. Let me know if you have any ideas for storing the ids other than in the db.

    Thanks,
    Noah

  7. myDBR Team, Key Master

    So where is the actual data stored initially, in the database you are reporting from or somewhere else?

    --
    myDBR Team

  8. nsepetys, Member

    The mysql database is where we're querying from to begin with.

  9. myDBR Team, Key Master

    So instead of querying the data, you are trying to pass it as a parameter?

    --
    myDBR Team

  10. nsepetys, Member

    Yes, although I cannot pass all the unique IDs as a URL parameter because the URL string has a character limit which I know I cannot get my IDs under. I even tried compressing the URL parameter string and it didn't cut it. So we need a smart solution to passing all those IDs...

  11. myDBR Team, Key Master

    You might want to take a look at ways to optimize the dashboard queries. Pulling the data from database and sending it back to report will not be a good idea.

    --
    myDBR Team


Reply

You must log in to post.