April 4, 2012 at 12:02 pm
I am trying to use SSRS for a multi shema reporting solution for my SaaS application.
The solution so far is to use a parameter as a query then pass the schema name as a parameter.
All works well until I add another parameter, like startDate
Here is my code:
"SELECT Party.LastName, Party.FirstName, Party.Birthdate, Party.Gender,olunteer.SwornDate,
Ethnicity.EthnicityDesc, EmploymentStatus.EmploymentStatusDesc, EducationType.EducationTypeDesc,
LanguageType.LanguageTypeDesc FROM " +Parameters!schema.Value+".LanguageType INNER JOIN "+Parameters!schema.Value+".Language ON
Parameters!schema.Value+".LanguageType.LanguageTypeID = "+Parameters!schema.Value+".Language.LanguageTypeID
RIGHT OUTER JOIN "
+Parameters!schema.Value+".Education INNER JOIN "
+Parameters!schema.Value+".EducationType ON
+Parameters!schema.Value+".Education.EducationTypeID = "+Parameters!schema.Value+".EducationType.EducationTypeID RIGHT OUTER JOIN "
+Parameters!schema.Value+".Party INNER JOIN "
+Parameters!schema.Value+".Volunteer ON "+Parameters!schema.Value+".Party.PartyID = "
+Parameters!schema.Value+".Volunteer.PartyID LEFT OUTER JOIN "
+Parameters!schema.Value+".Employment ON "+Parameters!schema.Value+".Party.PartyID = "
+Parameters!schema.Value+".Employment.PartyID ON "+Parameters!schema.Value+".Education.PartyID = "
+Parameters!schema.Value+".Party.PartyID ON "+Parameters!schema.Value+".Language.PartyID = "
+Parameters!schema.Value+".Party.PartyID LEFT OUTER JOIN "
+Parameters!schema.Value+".EmploymentStatus ON "
+Parameters!schema.Value+".Employment.EmploymentStatusID = "
+Parameters!schema.Value+".EmploymentStatus.EmploymentStatusID LEFT OUTER JOIN "
+Parameters!schema.Value+".Ethnicity ON "
+Parameters!schema.Value+".Party.EthnicityID = "
+Parameters!schema.Value+".Ethnicity.EthnicityID "
+"WHERE "
+Parameters!schema.Value+".Volunteer.SwornDate < "
+Parameters!date1.Value
the Parameters!schema.Value works.
I get an error saying date1 not found when I add it to my query parameters.
I have tried both datetime and text.
Any help is very greatly appreciated.
Andy
April 5, 2012 at 7:05 am
Andy,
It sounds like the issue is that the parameter is not named date1. Have you verified the name and case of the parameter?
Something I like to do in a situation like this is take the concatenated query string and put it in a text box on the report itself (run the report with a query the will work, but return no rows). Then I can see what is actually being created.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2012 at 8:33 am
Thanks Jack,
I did find out that I needed to add the parameter names to the dataset even though I was using the parameter "query" instead of the query in the dataset.
I found it to work well when I just added the parameters in the query designer the copied that to the parameter "query".
That way they are added automatically.
Thanks for you help!
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply