Is it possible to create a parameter and use different datasets?

  • Hello,

    I have an SSRS report, version 2019,  that has two parameters, which will call for three different datasets.

    1.  Parameter:  Continent >  3 values (Label)  = ASIA, EUROPE, SOUTH_AMERICA

      Parameter: Country -> cascading from the first parameter. Values are generated from the following datasets.

    2. Datasets  - DS_Asia;  Fields:  Continent, Country;  DS_Europe: Fields: Continent, Country; DS_South_America: Fields: Continent, Country

      Sample values for DS_Europe ->  EUROPE, Germany; EUROPE, France... etc.

    Situations:

    1. When selecting a drop-down from the parameter, (Continent), the values will be pointed to a specific dataset.  For example, if the parameter drop down is EUROPE, the value shown in the country box or field will be European countries from the DS_Europe dataset.

    2. Unfortunately, there are no DB links between the datasets to combine them into a single one. They are from different sources. We can gather the name of the countries.

    3. I am not sure whether I can use the lookup function, since there are no common fields or values on the datasets.

    4. I am not sure whether I can create a query to combine all three datasets since I can pick only one datasource on the dataset.

    5. On the Report Parameter Properties -> Available Values -> Specific Values;  (See attchment)

    Try to add the Value in the "Label" such as ASIA.  However, I am not able to write the Value Expression.  When selecting the datasets items that I wanted, i.e. DS_Europe, the values available are all aggregated, such as First or Sum.  Not sure of how to put the values in there.

    I do wonder whether it can be done in SSRS 2019.  Need some suggestions. Thanks.

    Attachments:
    You must be logged in to view attached files.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Not sure if I understand your problem, but couldn't you use a simple UNION for the Country drop-down list? Something like

    SELECT Country
    FROM DS_Asia
    WHERE Continent = @Continent
    UNION ALL
    SELECT Country
    FROM DS_Europe
    WHERE Continent = @Continent
    UNION ALL
    SELECT Country
    FROM DS_South_America
    WHERE Continent = @Continent

    This is on the assumption that DS_Asia etc. are views.

  • The sources for the datasets are different, so I don't think that will work.


  • Phil Parkin wrote:

    The sources for the datasets are different, so I don't think that will work.

    Yes, I saw that as well, but how are the sources different? The OP didn't specify that. I'm assuming that some sort of underlying query is still in play with each of the data sets.

    And the attachment seemed to indicate that the OP would like to use scripting to specify individual values for the Country drop-down, when it would be so much more logical to use a query to provide the values.

  • kaj wrote:

    Phil Parkin wrote:

    The sources for the datasets are different, so I don't think that will work.

    Yes, I saw that as well, but how are the sources different? The OP didn't specify that. I'm assuming that some sort of underlying query is still in play with each of the data sets.

    And the attachment seemed to indicate that the OP would like to use scripting to specify individual values for the Country drop-down, when it would be so much more logical to use a query to provide the values.

    Agreed, but if query 1 is from server 1 and query 2 from server 2, how do you do the UNION?

    I suspect that the OP was hoping to be able to combine the results from the two datasets in code, on the basis that a single UNION query was not feasible/practical, for whatever reason. I investigated, but could not see any way of doing that.

    Hopefully, they will post back to clarify.


  • Phil Parkin wrote:

    Agreed, but if query 1 is from server 1 and query 2 from server 2, how do you do the UNION?

    I suspect that the OP was hoping to be able to combine the results from the two datasets in code, on the basis that a single UNION query was not feasible/practical, for whatever reason. I investigated, but could not see any way of doing that.

    Hopefully, they will post back to clarify.

    I see your point. I was making assumptions about the sources possibly being collected from different places, but through Linked Server queries and the report server therefore using only one connection. But if the datasets are retrieved directly by report server from different places, then I can definitely see the problem.

  • Hello Kaj,

    Thank you for your reply.

    1. UNION query is not possible because each dataset comes from different sources. For example,  DS_ASIA comes from the Teradata database; DS_EUROPE comes from Oracle instance and DS_SOUTH_AMERICA comes from our MS SQL Table.

    2. We can write a query on each dataset to generate the result on each continent.

    We are still contemplating on how to work on this one.

    Look forward to suggestions. Thank you.

  • One solution would be to copy the data to a centralized database/warehouse and report from that.

  • Ken McKelvey wrote:

    One solution would be to copy the data to a centralized database/warehouse and report from that.

    This sounds like the best idea, though obviously it will take some time to implement.


Viewing 10 posts - 1 through 9 (of 9 total)

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