Generic SSRS (RDL) Report - Check Your Fields 🎉

Kinetic ERP
,
21

When I went through your process (using your supplied .zip)… I get this error when trying to run the report (against OrderAck):

An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source ‘IHaveSecrets’. —> System.ArgumentException: The connection string is not valid.

Not sure I understand this since IHaveSecrets is an embedded data source.

1 Like
22

It’s the Data Source properties that looks off in Kevin’s screenshots. It should I imagine be set to “use a shared connection or report model” the same as the other standard reports. BUT, you can’t set this in Report Builder for a new report because you don’t have a connection to the cloud SSRS server.

@klincecum does the process of uploading the RDL via the UI do some magic to fix up the shared connection?

3 Likes
23

I dunno, I may have screwed up the instructions. Not where I can look right now.

1 Like
24

I love this. I’ve done exactly this before on reports for cloud instances to see what’s being passed to make sure my logic is working properly in the report.

@aosemwengie1 - I second your request. When I’ve done this in the past, I have created a new dataset of just each table that I know to be in the report and plop them down in the report. It’s very tedious. If only there were a way to do this exact same thing with the values. Like a big table with a column for the table name, a column for the column name, and then a third column for the value of the column. That doesn’t sound that crazy… right?

5 Likes
25

So I was able to get this to work by starting out with a copy of the standard report style (since that already has the data source) and just adding the new dataset to output the field names to it. I don’t see how it can work with a blank report since there isn’t a way to know what the connection string is supposed to be.

But yeah getting the actual field values and not just the name would be gold.

3 Likes
26

I tried to write a function in the Code Editor of an SSRS report that looks up the values based on the table and column names… but I’m not sharp enough with SQL and Vb to combine the two to make a working function. Similar to how there is a function in there that will pull out the parameter values that are input to the report from Epicor.

1 Like
27

I finally looked at this again and it works like I said. Not sure what the problem is for those having issues.

Make sure the first time you upload you make a new style.

1 Like
28

Of course I say that, and I can’t get it to work with an exact duplicate report. WTF.

3 Likes
29

I think you are on to something, and I can make it work now for everyone. The odd thing is, my original way works fine for me.

This new file linked → ShowMeYourSecretsFixed.zip (2.2 KB)

has the DataSourceReference blank. When you upload it, it will fill in the proper value.
(or you can just figure out yours if you want)

  <DataSources>
    <DataSource Name="IHaveSecrets">
      <DataSourceReference></DataSourceReference>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>bb3095bd-d2b3-47be-9b22-8aa573add66d</rd:DataSourceID>
    </DataSource>
  </DataSources>

Happy playing.

Edit: After you upload it, redownload it with the proper value to keep report builder from screwing it up.

2 Likes
30

I am not a coder (and i am lazy) :slight_smile:
I just change the report style output from Database to XML and then use Server Files to download the XML.
Done!

DaveO

2 Likes
31

Happy Married At First Sight GIF by Lifetime

3 Likes
32

Then you would make an exceptional coder.

3 Likes
33

I found the coolest tool to search all my SSRS RDLs and Crystal RPT files for field name changes. Not only am I using it for schema changes (few really affect us) but mostly b/c we’re going back through and eliminating the standard UD field names for appropriately named fields - so this is a BIG help. And it’s cheap!

(I am in no way affiliated or promoting this product - link provided as FYI)

Search Crystal Reports and SQL Databases

2 Likes
34

I don’t think I understood what you said when I first read this. But I now realize what you’re saying. I can dump that data file to an XML file that I can then pull off the server (similar to logs) and I have my dataset in XML format. What do you do with that file? Reading XML files is “possible” but not very user friendly. Do you convert it into tables or have a utility you use to parse through it to make it more user friendly? Or just muddle through it?

35

Mr. Dan: I just use the Internet Explorer to pull up the XML.

You will be able to collapse or expand the nodes.

This just allows you to all of the Fields and names and the data that goes along with it.
If you are trying to debug why an SSRS Select Query is failing this can be a good method to make sure all of your fields are named properly i.e. OrderHed (NOT OrderHead).

Dave+

36

I use VSCode with the Prettier extension. It works with XML, JSON, and many other file types. 251M downloads.

3 Likes