January 28, 2025 at 5:05 pm
Hello,
I have an SSRS report, version 2019, that has two parameters, which will call for three different datasets.
Parameter: Country -> cascading from the first parameter. Values are generated from the following datasets.
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.
January 29, 2025 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 4, 2025 at 9:46 am
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.
February 4, 2025 at 9:55 am
The sources for the datasets are different, so I don't think that will work.
February 4, 2025 at 10:12 am
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.
February 4, 2025 at 10:27 am
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.
February 4, 2025 at 10:54 am
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.
February 4, 2025 at 2:34 pm
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.
February 4, 2025 at 2:50 pm
One solution would be to copy the data to a centralized database/warehouse and report from that.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy