Search the Community
Showing results for tags 'SQLSearch and Report'.
Hello, I am looking for help on a solution I am hoping to implement. I will do my best to be clear and include all necessary information but please let me know if you have any questions. I believe this can be accomplished with SQL but am not familiar enough with SQL to begin this on my own. I have a search and report datapage (tabular) that pulls information from Table A. Table A has columns including Client, Project, Asset, Task, and Status where a client can have multiple projects, a project can have multiple assets, assets can have multiple tasks and each task has a status (See Table A.png). Each Project in Table A can have several thousand records beneath it. I would like to use SQL to display a Percentage of all records within a particular Project (lets say project 1) that have a Status of "Complete". "1" will be passed as a parameter into the filter (Column=Project) using @Project_ID. I would like to display just the percentage of complete records and possibly a count of total records, both filtered by @Project_ID. I do not wish to actually display a full table. My ideal product would look something like the second attachment (Table A Completion Percentage). Further notes: I am absolutely open to changing the Datapage in order to accomplish this goal. I am not however looking to display this information in a chart as I really am only looking for the Percentage and do not need to display the percentage of other status' as well, as a pie chart would. Also I want to make sure that this only displays information from a single Project_ID at a time as this percentage will be a Client facing data page and I don't want them to see other clients or projects. I was able to accomplish this solution previously by adding a field to the table that entered a 1 if a record was updated with a Complete status and a 0 if updated with something else but this made the data quite messy and it complicated things if the data was updated via an import as the calculation field was never submitted or calculated. The calculation as I'm sure you guys know, only exists on the Datapage level and not within the database itself. Anyways, I am redoing this to avoid going down that road again. Thank you all so much in advance for any help you can provide. Cooper