Might anyone have had previous success with creating a BAQ report using a CTE-based BAQ?
General problem I’m having: The CTE BAQ behaves as expected when entering a part number as a BAQ parameter - but - the BAQ report seems to maintain the parameter option value even when running through the various part assembly levels. In addition, the BAQ report appears to recognize the assembly levels, but the grouping of that data only seems to run one-level higher.
Instead of constructing a top-down BOM-level view, the requirement here is opposite of that – when I enter a part number as the parameter, I need to see if it exists in an upper-level assembly… then see if that assembly exists in another upper-level assembly, and so on. Reason? The Sales Department wants to know all of the sales orders attributed to the original part (which could be a sub-component of a larger assembly that we sell to customers).
I’m satisfied with the results of the CTE and have tested it on several part numbers (here’s one simple example):
From above… part #100-821-4223 is a sub-component of #100-821-4200 - but #100-821-4200 is not a member of any other parent assembly. That is expected. Notice that you cannot find #100-821-4200 as a material part in level 1.
I merely used this exact CTE to feed a new BAQ report. I can pull data into the report, but it certainly doesn’t follow the progression of level 0 to level 1 to level 2 that the CTE follows.
I’m guessing it is because of my misunderstanding of grouping on the SSRS report - but - if I construct the report with NO grouping and just pull in the exact 3 fields above in the “Details” row (so they can simply repeat as displayed records until the recordset is exhausted), the material part value is repeated through all levels.
Any special considerations for using CTE in a BAQ report? If “grouping” is inherent in the CTE level value, how is this maintained in SSRS grouping? What might your suggestions be for displaying the simple matrix of results illustrated above in an SSRS report?
Unsolicited opinion: that seems like the perfect thing for a dashboard… Do you need it on paper? Maybe just tell them it can’t be done on a report? makes your life easier, and then they have up to date information.
It doesn’t solve your problem, but that’s what I would do.
@Banderson – I’m definitely leaning towards your suggestion at this point. Nah - we don’t really need this solution to be report based. At my company, the default is to provide reporting, rather than dashboards. However, in this case, since we also have to link the part list back to historical sales orders, the dashboard approach would truly be best (especially to present multi-sourced data in different data grids).
I’ll wait for a bit to see if there might be additional suggestions on this, but I think I might be reloading the chamber to do exactly what you’re talking about!
Just reporting back here on my progress so that you know what path I took in this case.
Brandon - I went ahead and scrapped the SSRS report idea because the solution seemed to fit more into the realm of a dynamic dashboard. Sometimes, it’s good to get slapped with the “Sensibility Stick™” to get a clearer vision on what might work best. Thanks for your feedback.
Haso - I took your suggestion to maintain the top-level columns and to add in the hierarchy to help with the top-to-bottom and the bottom-to-top sorting, should the user need that capability. It also helped to allow the user a way to “visualize” the hierarchy, as well. I added in a separate Sales Order BAQ to populate and filter when a record with the upper assembly is selected in the “part grid”. The bottom right panel merely maintains the sales orders (using the same BAQ) for the originally searched part number.
There were probably easier ways to address this, I’m sure, but this seems to work and the user who requested it was rather pleased.
Thanks again to both of you for getting me over the hump and helping me to provide a workable solution.
I want to give the “mark this as the solution” to both of you, but I’m not sure how to do that in the forum UI – but I’m going to attribute it to @Banderson, because he broke me away from the report-based avenue and got me realigned for a proper dashboard solution here (taking nothing away from @hkeric.wci’s contributions here )
Glad you got it working. I usually find that the dynamic-ness of a dashboard has a lot of value that paper doesn’t (which is one of the reasons I still don’t know hardly anything about SSRS reports ). And looking at what you had as example, that doesn’t look that it would do well on paper at all!
Oh, believe me, it didn’t. I thought I could piece together everything with the sales orders all in one shot - but it just wasn’t working. The dashboard was the right call. Besides, it’s easier for the user to employ the “group by” panel and assess the data in any manner he’d like. Thanks again, @Banderson!
Sometimes its best to give yourself the Solution when unsure hehehe. No Worries mate.
Note for anyone reading this… In Order to make my Hierarchy Searchable in a Dashboard… I did add a JobNum: textbox via a Customization and when the User fills it out and Text Changes it populates the Actual Hidden Hierarchy Search field with
*\word\*
Since its a Matches Field and my Hierarchy is split by Slashes
And if Blank then it sets the Hierarchy Field to “”
Now that you have a BAQ if they ever wanted a Report, you have a better structure to support filtering and sorting and grouping in SSRS.
– but I’m going to attribute it to 
)
). And looking at what you had as example, that doesn’t look that it would do well on paper at all!